ptg
This page intentionally left blank
Download from www.wowebook.com
ptg
CHAPTER 19
Replication
IN THIS CHAPTER
. What’s New in Data Replication
. What Is Replication?
. The Publisher, Distributor, and
Subscriber “Magazine”
Metaphor
. Replication Scenarios
. Subscriptions
. Replication Agents
. Planning for SQL Server Data
Replication
. SQL Server Replication Types
. Basing the Replication Design
on User Requirements
. Setting Up Replication
. Scripting Replication
. Monitoring Replication
There is no such thing as a typical configuration or appli-
cation anymore. Companies now have to support numerous
hardware and software configurations in multitiered,
distributed environments. These diverse configurations and
applications (and users of the applications) come in all sizes
and shapes. And, of course, you need a way to deal with
varied data access requirements for these different physical
locations; these remote or mobile users over a local area

network, wide area network, wireless connections, and dial-
up connections; and any needs over the Internet.
Microsoft’s data replication facility allows for a great
breadth of capability to deal with many of these demands.
However, to build a proper data replication implementation
that meets many of these user requirements, you must have
a thorough understanding of the business requirements and
technical capabilities of data replication. Data replication is
a set of technologies for storing and forwarding data and
database objects from one database to another and then
synchronizing this data between databases to maintain
consistency. With SQL Server 2008, the data replication
feature set offers numerous improvements in manageability,
availability, programmability, mobility, scalability, and
performance.
This chapter does the following:
. Helps you understand what data replication is
. Shows you how to understand and analyze user
requirements of data
. Allows you to choose which replication configuration
best meets these requirements (if any)
Download from www.wowebook.com
ptg
546
CHAPTER 19 Replication
. Demonstrates how to implement a replication configuration
. Describes how to administer and monitor a data replication implementation
What’s New in Data Replication
Much of what’s new for Microsoft SQL Server data replication revolves around simplifying
setup, administration, and monitoring of a data replication topology. This is the result of

years of practical experience and thousands of production replication implementations
around the globe. The overall data replication approach that Microsoft has developed
(since replication’s inception back in SQL Server 6.5 days) has been so solid that competi-
tors, such as Oracle (with its Oracle Streams technology), have tried to mimic this architec-
tural approach.
Among many others, the following are some of the new replications features and
enhancements that make SQL Server 2008 data replication one of the best data distribu-
tions tools on the market:
. Highly available replication node additions—SQL Server 2008 offers the capa-
bility to add nodes to a replication topology without quiescing the topology.
. Topology Viewer—Enhancements have been made to the Peer-to-Peer Topology
Wizard so that you can now visually see what the peer-to-peer topology looks like
with the Topology Viewer.
. Capability to centrally monitor all agents and jobs at the Publisher—You are
able to view information about all the agents and jobs associated with publications
at the selected Publisher.
. Minor Replication Monitor enhancements—Replication Monitor has undergone
slight tweaks to make it easier to monitor your full replication topologies. It allows
you to monitor the overall health of a replication topology and provides detailed
information about the status and performance of publications and subscriptions.
. Capability to replicate switch partition ALTER—Enhanced Transactional
Replication Support for Partitioned Tables is now available, including the capability
to replicate the switch partition
ALTER for Tables.
. Scripting integrated into wizards—You can almost completely script your replica-
tion setup or breakdown during or after wizard executions.
. Conflict Viewer—This feature helps you view and resolve any conflicts that
occurred during the synchronization of a merge subscription or queued updating
subscription.
. Peer-to-peer transactional replication—Further enhancements have been intro-

duced to the peer-to-peer replication model. They allow replication between identi-
cal participants in the topology (a master/master or symmetric publisher concept).
. Peer-to-peer conflict detection—The capability to detect conflicts during synchro-
nization in a peer-to-peer replication topology has been added.
Download from www.wowebook.com
ptg
547
What Is Replication?
Insert “A”
(Store)
Distribute “A”
(Forward)
A
A
A
A
FIGURE 19.1 The store-and-forward data distribution model.
. More replication mobility—Merge replication provides the capability to replicate
data over HTTPS with the web synchronization option, which is useful for synchro-
nizing data from mobile users over the Internet or synchronizing data between
Microsoft SQL Server databases across a corporate firewall.
. Microsoft Sync Framework—This comprehensive synchronization platform
enables collaboration and offline access for applications, services, and devices. It fea-
tures technologies and tools that enable roaming, sharing, and taking data offline.
By using Sync Framework, developers can build sync ecosystems that integrate any
application with any data from any store that uses any protocol over any network.
We mention it here because of its replication-like behavior for “occasionally con-
nected” applications.
Many of these terms and references might be new or foreign to you now, but they are all
explained in this chapter. At the end of this chapter, when you review these new features,

you’ll be able to appreciate much more readily their significance.
What Is Replication?
Long before you ever start setting up and using SQL Server data replication, you need to
have a solid grasp of what data replication is and how it can be used to meet your
company’s needs. In its classic definition, data replication is based on the “store-and-
forward” data distribution model, as shown in Figure 19.1. In other words, data that is
inserted, updated, or deleted in one location (stored) is automatically distributed
(forwarded) to one or more locations.
19
Of course, the data distribution model addresses all the other complexities of updates,
deletes, data latency, autonomy, and so on. It is this data distribution model that
Microsoft’s data replication facility serves to implement. It has come a long way since the
early days of Microsoft SQL Server replication (earlier than 6.5) and is now easily catego-
rized as “production worthy.” Numerous worldwide data replication scenarios have been
implemented for some of the biggest companies in the world without a hitch. These
scenarios fall into five major types:
Download from www.wowebook.com
ptg
548
SQL Server 2008
Primary
OLTP
Reporting/ODS
OLTP DB
SQL Server 2008
Reporting
Server
Rpt DB
SQL Server 2008
North America

Region
Regionalization
(multiple owners)
xyz DB
SQL Server 2008
Europe
Region
xyz DB
SQL Server 2008
Primary
Failover
xyz DB
SQL Server 2008
Hot Spare
(Fail-over)
xyz DB
SQL Server 2008
USA
(Headquarters)
Enabling/Partitioning
xyz DB
SQL Server 2008
Europe
Server
xyz DB
SQL Server 2008
Asia
Server
xyz DB
FIGURE 19.2 Data replication scenarios.

. Offloading—You might need to deliver data to different locations to eliminate
network traffic and unnecessary load on a single server (for example, when you need
to isolate reporting activity away from your online transaction processing). The
industry trend is to create an operational data store (ODS) data architecture that
replicates core transactional data to a separate platform in real-time and delivers the
data to the reporting systems, web services, and other data consumers without
impacting the transactional systems in any way.
. Enabling—You might need to enable a group of users with a copy of data or a
subset of data (vertically or horizontally) for their private use.
. Partitioning—You might need to move data off a single server onto several other
servers to provide for high availability and decentralization of data (or partitioning
of data). This might be the basis of serving customer call centers around the globe
that must service “active” support calls (partitioned on active versus closed service
requests).
. Regionalization—You might have regional ownership of data (for example,
regional customers and their orders). In this case, it is possible to set up data replica-
tion to replicate data bidirectionally from two or more publishers of the same data.
. Failover—You could be replicating all data on a server to another server (that is, a
failover server) so that if the primary server crashes, users can switch to the failover
server quickly and continue to work with little downtime or data loss.
Figure 19.2 illustrates the topology of some of these replication variations.
CHAPTER 19 Replication
Download from www.wowebook.com
ptg
549
The Publisher, Distributor, and Subscriber Magazine Metaphor
19
As you may notice, you can use data replication for many reasons. Many of these reasons
are discussed later in this chapter. First, however, you need to understand some of the
common terms and metaphors Microsoft uses in relationship to data replication. They

started with the “magazine” concept as the basis of the metaphor. A magazine is created
by a publisher, distributed via the mail, and delivered to only those who have a subscrip-
tion to the magazine. The frequency of the magazine publication can vary, as can the
frequency of the subscription (depending on how often the subscriber wants to receive a
new magazine). The publication (magazine) consists of one or more articles. One or more
articles can be subscribed to.
The Publisher, Distributor, and Subscriber Magazine
Metaphor
Any SQL Server can play up to three distinct roles in a data replication environment:
. Publication server—The publication server (or publisher) contains the database or
databases that will be published (the magazine!). This is the source of the data that is
to be replicated to other servers. In Figure 19.3, the Customer table (an article in the
magazine) in the AdventureWorks2008 database is the data to be published. To
publish data, the database that contains the data that will be published must first be
enabled for publishing. Full publishing configuration requirements are discussed
later in this chapter, in the section “Setting Up Replication.”
Customer (Sales)
CustomerID
TerritoryID
AccountNumber
CustomerType
rowguid
ModifiedDate
SQL Server
2008
Publisher
“Magazine” metaphor
Adventure
Works
translog

SQL Server
2008
Distributor
distribution
Customer (Sales)
CustomerID
TerritoryID
AccountNumber
CustomerType
rowguid
ModifiedDate
SQL Server
2008
AdventureWorks
Customer (Sales)
Customer ID
Territory ID
AccountNumber
Customer Type
rowguid
ModifiedDate
SQL Server
AdventureWorks
Subscriber(s)
FIGURE 19.3 The publisher, distributor, and one or more subscribers.
Download from www.wowebook.com
ptg
550
. Distribution server—The distribution server (or distributor) can either be on the
same server as the publication server or on a different server (in which case it is a

remote distribution server). This server contains the distribution database. This data-
base, also called the store-and-forward database, holds all the data changes that are
to be forwarded from the published database to any subscription servers that
subscribe to the data. A single distribution server can support several publication
servers. The distribution server is truly the workhorse of data replication; it is essen-
tially the mail system that picks up the magazine and delivers it to the subscription
holder.
. Subscription server—The subscription server (or subscriber) contains a copy of the
database or portions of the database being published (for example, the
Customer
table in the AdventureWorks2008 database). The distribution server sends any
changes made to this table (in the published database) to the subscription server’s
copy of the Customer table. This is known as store-and-forward. Some data replication
configurations send the data to the subscription server, and then the data is read-
only. It is also possible for subscribers (known as updating subscribers) to make
updates, which are sent back to the publisher. More on this in the Updating
Subscribers Replication Model section.
There are now new variations of this update subscriber option called peer-to-peer replication.
Peer-to-peer allows for more than one publisher of the same data (table) at the same time!
Essentially, each publisher is also a subscriber at the same time (hence, peer-to-peer). This
chapter provides more information on updating subscribers and peer-to-peer configura-
tions in the “The Updating Subscribers Replication Model” section, later.
Along with enabling distinct server roles (publisher, distributor, and subscriber), Microsoft
utilizes a few more magazine metaphors, including publications and articles. A publication
is a group of one or more articles and is the basic unit of data replication. An article is
simply a pointer to a single table, or a subset of rows or columns out of a table, that will
be made available for replication.
Publications and Articles
A single database can contain more than one publication. You can publish data from
tables, from database objects, from the execution of stored procedures, and even from

schema objects, such as referential integrity constraints, clustered indexes, nonclustered
indexes, user triggers, extended properties, and collation. Regardless of what you plan to
replicate, all articles in a publication are synchronized at the same time. Figure 19.4 shows
an example of a publication (named Cust_Orders publication) with three articles (three
tables from the AdventureWorks2008 database). You can also choose to replicate whole
tables or just parts of tables via filtering.
Filtering Articles
You can create articles within a publication in several different ways. The basic way to
create an article is to publish all the columns and rows contained in a table. Although this
is the easiest way to create articles, your business needs might require that you publish
only specific columns or certain rows of a table. This is referred to as filtering vertically or
CHAPTER 19 Replication
Download from www.wowebook.com
ptg
551
SQL Server
2008
Publisher
Publication
Adventure
Works
translog
Customer (Sales)
CustomerID
TerritoryID
AccountNumber
CustomerType
rowguid
ModifiedDate
SalesOrderHeader (Sales)

SalesOrderID
RevisionNumber
OrderDate
DueDate
ShipDate
Status
OnlineOrderFlag
SalesOrderNumber
PurchaseOrderNumber
AccountNumber
CustomerID
ContactID
SalesPersonID
TerritoryID
BillToAddressID
ShipToAddressID
ShipMethodID
CreditCardID
CreditCardApprovalCode
CurrencyRateID
SubTotal
TaxAmt
Freight
TotalDue
Comment
rowguid
ModifiedDate
Cust_Orders
Cust_Orders
SalesOrderHeader (Article)

SalesOrderDetail (Article)
SalesOrderDetail (Sales)
SalesOrderID
SalesOrderDetailID
CarrierTrackingNumber
OrderQty
ProductID
SpecialOfferID
UnitPrice
UnitPriceDiscount
LineTotal
rowguid
ModifiedDate
FIGURE 19.4 The Cust_Orders publication (in the AdventureWorks2008 database).
The Publisher, Distributor, and Subscriber Magazine Metaphor
19
horizontally. When you filter vertically, you filter only specific columns, whereas with hori-
zontal filtering, you filter only specific rows. In addition, SQL Server 2008 provides the
added functionality of join filters and dynamic filters.
As Figure 19.5 shows, you might need to replicate only a customer’s CustomerID,
TerritoryID, and CustomerType to various subscribing servers around your company. In
your company, the other data, such as AccountNumber, may be restricted information that
should not be replicated for general use. For that reason, you simply create an article for
data replication that contains a subset of the Customer table that will be replicated to these
other locations and excludes AccountNumber (and rowguid and ModifiedDate as well).
As another example, you might need to publish only the Customer table data for a specific
customer type, such as “individual” customers ((CustomerType = ‘I’) or customers that
are “stores” (CustomerType = ‘S’). This process, as shown in Figure 19.6, is known as
horizontal filtering.
It is possible to combine horizontal and vertical filtering, as shown in Figure 19.7. This

way, you can weed out unneeded columns and rows that aren’t required for replication
(that is, are not needed by the subscribers). For example, you might need only the
customers that are stores and need only CustomerID, TerritoryID, and CustomerType data
to be published.
Download from www.wowebook.com
ptg
552
CHAPTER 19 Replication
SQL Server
2008
Publisher
Publication
Adventure
Works
translog
Customer (Sales)
CustomerID
TerritoryID
AccountNumber
CustomerType
rowguid
ModifiedDate
AW_Vertical
CustomerV (Article)
1345
1356
2354
3346
7643
7901

8921
1
2
1
2
3
5
4
AW1345
AW1356
AW2354
AW3346
AW7643
AW7901
AW8921
CustomerID
1345
1356
2354
3346
7643
7901
8921
ModifiedDate
I
I
S
I
S
I

I
CustomerTypeTerritoryID
X69G9
W211G
7SQ78K
W12DV
WZ8R4
S2345X
RT66Y
rowguidAccountNumber
1345
1356
2354
3346
7643
7901
8921
1
2
1
2
3
5
4
CustomerID TerritoryID
I
I
S
I
S

I
I
CustomerType
FIGURE 19.5 Vertical filtering creates a subset of columns from a table to be replicated to
subscribers.
SQL Server
2008
Publisher
Publication
Only these Rows!
Adventure
Works
translog
Customer (Sales)
CustomerID
TerritoryID
AccountNumber
CustomerType
rowguid
ModifiedDate
AW_Horizontal
CustomerH (Article)
1345
1356
2354
3346
7643
7901
8921
1

2
1
2
3
5
4
AW1345
AW1356
AW2354
AW3346
AW7643
AW7901
AW8921
CustomerID
120203
051605
106705
022305
122205
041506
0321206
ModifiedDate
I
I
S
I
S
I
I
CustomerTypeTerritoryID

X69G9
W211G
7SQ78K
W12DV
WZ8R4
S2345X
RT66Y
rowguidAccountNumber
2354
7643
1
3
CustomerID TerritoryID
AW2354
AW7643
106705
122205
ModifiedDate
S
S
CustomerType
7SQ78K
WZ8R4
rowguidAccountNumber
FIGURE 19.6 Horizontal filtering creates a subset of rows from a table to be replicated to
subscribers.
Download from www.wowebook.com
ptg
553
The Publisher, Distributor, and Subscriber Magazine Metaphor

19
SQL Server
2008
Publisher
Publication
Only these
Columns and
these Rows!
Adventure
Works
translog
Customer (Sales)
CustomerID
TerritoryID
AccountNumber
CustomerType
rowguid
ModifiedDate
AW_H_and_V
CustomerHV (Article)
1345
1356
2354
3346
7643
7901
8921
1
2
1

2
3
5
4
AW1345
AW1356
AW2354
AW3346
AW7643
AW7901
AW8921
CustomerID
120203
051605
106705
022305
122205
041506
0321206
ModifiedDate
I
I
S
I
S
I
I
CustomerTypeTerritoryID
X69G9
W211G

7SQ78K
W12DV
WZ8R4
S2345X
RT66Y
rowguidAccountNumber
2354
7643
1
3
CustomerID TerritoryID
S
S
CustomerType
FIGURE 19.7 Combining horizontal and vertical filtering allows you to pare down the informa-
tion in an article to only the important information needed by the subscribers.
As mentioned earlier, it is now possible to use join filters. Join filters enable you to use the
values of one article (that is, values from a table) to determine what gets replicated from
another article (that is, what values can be associated with another table) via a join. In
other words, if you are publishing the Customer table data based on the customers that are
stores, you can extend filtering (that is, a join filter) to replicate only those orders for
these types of customers (as shown in Figure 19.8). This way, you replicate only orders for
customers that are stores to a subscriber that needs to see only this filtered data. This type
of replication can be efficient if it is done well.
You also can publish stored procedure executions, along with their parameters, as articles.
This can be either a standard procedure execution article or a serializable procedure execu-
tion article. The difference is that the latter is executed as a serializable transaction; the
serializable option is recommended because it replicates the procedure execution only if
the procedure is executed within the context of a serializable transaction. If that same
stored procedure is executed from outside a serializable transaction, changes to data in

published tables are replicated as a series of DML statements. In general, replicating stored
procedure executions gives you a major reduction in the number of SQL statements being
replicated across the network versus standard DML statements.
Download from www.wowebook.com