732
Part V: Enterprise Data Management
the publisher’s values for the row with a primary key (PK) of 1,000 on the publisher
and keep the subscriber’s values for the row with a PK of 1,000 on the subscriber.
Merge replication has a rich set of features to handle confl icts, including one that
skips changes to different columns occurring on the same row between publisher and
subscriber. This is termed column-level conflict tracking. For example, a change to John
Smith’s home phone number occurring on the publisher and his cell phone number
occurring on the subscriber would be merged to have both changes persisting on both
the publisher and subscriber. By default, merge replication uses row-level confl ict
tracking that might result in the change to John Smith’s home phone number updat-
ing on both the publisher and the subscriber, but his cell phone change rolling back,
with this confl ict and the confl icting values logging to the confl ict tables.Best Practice
A single server can serve as both the publisher and distributor, and even as the subscriber. An excel-
lent confi guration for experimenting with replication is a server with multiple SQL Server instances.
However, when performance is an issue, a dedicated distributor server is the best plan. This remote
distributor can act as a distributor for multiple publishers; you can confi gure this remote distributor
to have a separate distribution database for each publisher.The publisher server organizes multiple articles (an article is a data source: a single table,
view, function, or stored procedure) into a publication. You may fi nd that you get better
performance by grouping large or highly transactional articles (tables) into their own pub-
lication. The distributor server manages the replication process. The publisher can initiate
the subscription and push data to the subscriber server, or the subscriber can set up the
subscription and pull the subscription from the publisher.Transactional Consistency
The measure of transactional consistency is the degree of synchronization between two
replicated servers. As the lag time between synchronizations increases, transactional con-
sistency decreases. If the data is identical on both servers most of the time, transactional
consistency is said to be high. Conversely, a replication system that passes changes every
two weeks by e-mail has low transactional consistency.Confi guring Replication
Using wizards is the simplest way to implement replication. Developers and DBAs generally
avoid wizards because they have limited features, but implementing replication without
wizards requires numerous calls to arcane stored procedures and is a tedious and painful
process prone to user errors. However, in some cases it is necessary to use the replication
stored procedures, for example, if you develop and test replication in several environments.c28.indd 732c28.indd 732 7/31/2012 9:51:11 AM7/31/2012 9:51:11 AM
http://www.it-ebooks.info