Click here to monitor SSC
  • Av rating:
  • Total votes: 164
  • Total comments: 14
Robyn Page and Phil Factor

SQL Server Replication Crib Sheet

14 May 2007

SQL Server Replication-
The Crib Sheet

For things you need to know rather than the things you want to know

Contents

Introduction

Replication is intended to be a way of distributing data automatically from a source database to one or more recipient databases. As such, it can have obvious uses in a distributed system. It has also been used to implement high-availability systems. It is not useful for one-off synchronization, or for simply copying data. It is intended as a long-term data relationship between databases. Typical uses are in:

  • Data warehousing and reporting
  • Integrating data from several, possibly only partially connected, sites
  • Improving scalability and availability
  • Integrating heterogeneous data from other databases via OLE DB, integrating data from mobile users, getting data to and from Point-Of-Sale systems
  • Offloading/delegating batch processing tasks.

Examples of the use of replication within an application could be

  • Distributing data 'owned' by a particular application to other applications that are 'consumers' of that data. (For example, sales records to reporting services, manufacturing stock levels to purchasing systems.)
  • Creating several instances of a database to distribute load on it
  • Updating a central database with information from a number of remote Laptops that might be only partially connected, and to resynchronise the laptops.

There are three methods of replication: Snapshot, Transactional, and Merge. These are provided to try to meet the wide range of business requirements for replication.

Replication uses a 'Magazine Publishing' vocabulary. Anything from an 'Article' to an entire database can be replicated. An Article is the smallest component of distribution, and can be a table, procedure or function. If it is a table, then a filter can be applied to it so that only certain rows or columns are replicated. This 'Magazine Publishing' analogy can be confusing because, in replication, a Subscriber can sometimes make updates, and a Publisher usually sends out incremental changes to the articles in a publication.

A 'Publisher' maintains the original copy of the data. It holds the definition of the 'Publication', which defines the 'articles' that are to be 'published'. (The database with the original location of the data determines what is to be distributed).

A 'Subscriber' receives the articles from a publisher. It can subscribe to one or more publications. Any database can take on either role or even both roles at once.

A Distributor is a specialist database that runs the 'Replication agents'.

Replication is not part of the SQL Server engine, but an external application. This makes it much easier to involve other database systems in replication. Any SQL Server database, or other database system with an OLE DB provider, can be a publisher or subscriber in snapshot or transactional replication.

It is essential to plan out the replication in detail as a first stage, and to be very certain of the type of replication you wish to implement. A common mistake is to use replication in cases where a much less complex solution is possible.

A problem with production systems using replication is the difficulty of restoring the topology after a disaster.. This requires a fully documented recovery strategy, which has to be periodically tested and practiced. This means that the whole replication topology and configuration must be scripted so it can be re-created in an emergency, even if the system was originally built using the GUI tools. The Object Browser (or Enterprise Manager) makes the initial deployment relatively simple to do, and there are plenty of step-by-step guides, but it is not the best option when trying to restore an existing topology, and settings, in order to achieve a recovery from major failures.

Problems often follow from developers adding or dropping articles, changing publication properties, and changing schema on published databases. It is therefore best to create the replication once the design of the publisher is relatively stable.

Replication topologies

In most topologies, it makes sense for publishers, distributors, and subscribers to be on separate physical hardware.

Central Publisher

The commonest form of replication is to have a single publisher with the source data, with one or more subscribers. The Distributor database can be on the same, or preferably different, server.

Central Subscriber

Often, where the data from several databases need to be 'warehoused' centrally in an OLAP or reporting database, one will find a single 'reporting' database subscribing to several publications.

One can come across other topologies such as 'bi-directional' and 'peer to peer' which are really special cases of Central Publisher or Central Subscriber and use transactional replication

Publishing Subscriber

The distribution of data can be relayed to other subscribers via a publishing subscriber. This allows replication to be implemented over low-bandwidth WANs to a subscriber that, in turn, distributes it to other servers within its high-bandwidth LAN

Replication Methods

How Replication Works

Replication begins with the initial synchronization of the published objects between the Publisher and Subscribers, using a snapshot. A snapshot is a copy of all of the objects and data specified by a publication. After the snapshot is created on the publisher, it is delivered to the Subscribers via the distributor.

For Snapshot replication, this is sufficient. For other types of replication, all subsequent data changes to the publication flow to the Subscriber as they happen, in a queue, or on request.

Snapshot Replication

The snapshot replication process provides the initial synchronization for transactional and merge publications. However, in several cases, this initial synchronization is all that is necessary. This would include circumstances where data hardly changes, or if the latest version of the data is not essential to the subscriber, where the amount of data is small, or if a large number of changes takes place rapidly.

Snapshot replication involves copying the articles that make up the publication. Normally, if they exist already on the subscriber, they are over-written, though this behavior can be changed. Snapshot replication is more expensive in terms of overhead and network traffic and only takes place at intervals. Because locks are held during snapshot replication, this can impact other users of the subscriber database. It is therefore more suitable for static data and enumerations. In SQL Server 2005, several articles can be processed in parallel, and interrupted snapshots can be recommenced from the point of interruption. Snapshots can be queued or immediate.

Data changes are not tracked for snapshot replication; each time a snapshot is applied, it completely overwrites the existing data.

Transactional Replication

Transactional replication is used if:

  • Changes to the data must be propagated immediately
  • The database application taking out a subscription needs to react to every change
  • The Publisher has a very high volume of insert, update, and delete activity
  • The Publisher or Subscriber is a different database application reached via OLE DB.

Essentially, Transaction replication distributes data in one direction, but transactional replication does offer options that allow updates at the Subscriber. Once a snapshot replication has synchronized the subscribers with the publisher, all committed transactions on the publisher are then propagated to the subscribers in sequence, via distributed transactions. One can select a queued update or immediate, depending on requirements.

Peer-to-peer Replication

This is a special type of transactional replication in which every participant is both a publisher and subscriber (2005 Enterprise only) and is most useful for up to ten databases in a load-balancing or high-availability group.

Bidirectional Replication

This is where two databases replicate the same articles to each other via a distributor. There must be loopback detection. Data conflicts aren't handled and the replication must be implemented in code, since the GUI doesn't support it.

Transactional replication tracks changes through the SQL Server transaction log

Merge Replication

Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result.

Merge Replication is complex, but provides the means to implement part of a high-availability system, as well as its original purpose of serving mobile and disconnected users. It is designed for cases where the publishers are not in constant communication with the subscribers. After the initial snapshot synchronization, subsequent changes are tracked locally with triggers, and the databases are merged when in contact, using a series of rules to resolve all possible conflicts.

Merge replication is used when several Subscribers might need to update the same data at various times and propagate those changes back to the Publisher and thence to other Subscribers. It is also required in applications that involve Subscribers receiving data, making changes offline, and finally reconnecting with the publisher to synchronize changes with the Publisher and other Subscribers.

To make this possible, each Subscriber requires a different partition of data and there has to be a set of rules to determine how every conflict that takes place in the update of the data is detected and resolved. These conflicts occur when the data is merged because there can be no 'locking' and so the same data may have been updated by the Publisher and by more than one Subscriber.

Merge Replication does not use transactions. Merge replication uses a set of conflict-resolution rules to deal with all the problems that occur when two databases alter the same data in different ways, before updating the subscribers with a 'consensus' version. It normally works on a row-by-row basis but can group rows of related information into a logical record. One can specify the order in which 'articles' are processed during synchronisation.

Merge replication tracks changes through triggers and metadata tables.

Replication Agents

Replication is done by several different agents, which are separate applications each responsible for part of the process. The replication agents should not be run under the SQL Server Agent account in a production system. Instead, they need the minimal permissions necessary to perform their function.

SQL Server Agent

This manages the overall replication process via SQL Server Agent jobs.

The Snapshot agent

Snapshot.exe executes on the Distributor. It extracts the schema and data defined by the publication, which is then sent to the subscriber via a 'snapshot folder'. It also updates status information on the distribution database. . It is used in all forms of replication

The Log Reader Agent

LogRead.exe is used in transactional replication to extract relevant committed transactions from the publisher's log, repackage them and send them to the distributor in the correct sequence.

Distribution Agent

Distrib.exe takes the snapshots, and log entries from the agents we've described, and dispatches them to the subscribers.

Merge Agent

ReplMer.exe is used only in Merge Replication to send a snapshot when the subscriber is initialized, and also exchanges transactions between publisher and subscriber

Queue Reader Agent

QrDrSvc.exe is used to queue the updates in transactional or snapshot replication when queuing has been specified.

Monitoring Replication

Many problems associated with replication can be avoided by .regular checks. The most obvious check is to make sure that the data has been transferred as expected. Periodic checks with SQL Compare and SQL Data Compare can be very useful in addition to the tools that come with Replication. Additionally the replication processes and jobs need to be checked to make sure they are working.

Checking throughput

The performance of replication must be regularly monitored, and performance-tuned as necessary.

The Replication Monitor is used to check on the operational state of publications, and inspect the history, and errors. Right-clicking the replication node in Object Explorer will gain access to it.

One of the most important concerns is the time delay, or latency, of transactions from the publications appearing in the subscriber database. At times of high transaction throughput on the publisher database, bottlenecks can occur. Whereas the stored procedure sp_browseReplCmds on the distribution database can tell you how far behind the synchronisation is at any particular time, one cannot determine where the problems lies just from the data. Tracer tokens are now used to measure the actual throughput of the replication architecture at any particular time to help diagnose such bottlenecks.

Validating

There is always an element of doubt as to whether the replication has entirely worked. There are stored procedures provided to compare the 'articles' on the publisher and subscribers to make sure they are the same.

The sp_publication_validation stored procedure validates the data associated with each article by calling sp_article_validation (after the articles associated with a publication have been activated). The sp_article_validation stored procedure invokes sp_table_validation stored procedure, which calculates the number of lines and, optionally, the checksum of the published table. It is considered good practice to perform a daily row-count and weekly checksum. SQL Data Compare is ideal for mending a broken replication.

The Distribution Agent raises the '20574' system message if validation fails, or the '20575' system message if it passes. The Distribution Agent will replicate changes to a subscriber even if the validation shows that the subscriber is out of synchronization. It is a good policy to configure the Replication Alert on the '20574' message so as to send E-Mail, Pager, or Network notification.

This validation approach will only work within certain restrictions. For example, it will not work if certain filters have been applied. They should be used with caution.

Changing the settings

It is best to use the default replication settings unless there are clear performance gains to be made, or if the application design forces the issue. However, one cannot assume that the changes will be generally beneficial to the entire topology without comprehensive testing.

Articles

Articles are the smallest unit of a publication. An article can be a table, view, stored Procedure or function. Where an article is based on a table or view, it can contain all the data or just part of it. These filters of two types.: More common are the static 'WHERE' clauses, but filters can be used dynamically in Merge Replication to publish different 'content' (rows) to different 'subscribers' (databases receiving data). These latter Filters are called 'Dynamic' and can be simple Row Filters, or Join Filters, where the selection of rows to publish is based on a join with other tables, rather than a simple WHERE clause.

Normally, any alteration to an article that is a table is propagated to all the subscribers. You can also opt to propagate schema objects associated with the article such as indexes, constraints, triggers, collation and extended properties.

Updating articles

In Merge replication, the subscriber can update the article. This is, of course, a recipe for conflict, and these have to be resolved automatically. When the Merge Agent comes across a row that might have changed recently, it examines the history or 'lineage' of each site's version of the row to see if there is a conflict. If so, then the update that is finally used. Has to be based on either

  • A "first wins" resolution,
  • a user-specified priority scheme to determine the update to select,
  • a customised resolution, using COM and stored procedures.

The 'lineage' is a history of changes in a table row in MSmerge_contents, which is maintained automatically when a user updates a row. Each column contains one entry for each site that has updated the row.

Conflicts to the data in the base table can occur within a column or a row. Most usual are column-tracked articles this means that, within any row, updates are only recognized as conflicts if the same column is updated by more than one subscriber. Occasionally, however, the business rules of the application may treat simultaneous changes to the any column within the row as a conflict, in which case row-level tracking is used.

Programming Replication Topologies

Replication agents and replication topologies can be administered and monitored remotely via SQL Scripts or RMO scripts. The task of building and maintaining replication topologies is made much easier of all parts of the deployments are scripted, even if this is done after the other methods such as wizards or RMO are used for the initial operation.

There are other uses for a replication script. It will be required in end-user application where, for example, such as s a pull subscription is synchronized when the user clicks a button, or where a routine administration task such as monitoring replication throughput is performed from a custom console. It is also generally used for writing customized business rules that are executed when a merge subscription is synchronized.

One can use the Object Explorer in SSMS, or the Enterprise Manager in earlier versions of SQL Server, to set up replication. BOL provide worked examples. Alternatively, RMO can be used with VB or C# to script the replication. Whatever system you use, it is a good idea to use the Transact SQL script as the reference for the replication topology you create

Ultimately, the functionality in a replication topology is provided by system stored procedures. The easiest approach is to use Transact-SQL script files to perform a logical sequence of replication tasks, because it provides a permanent, repeatable, copy of the steps used to deploy the replication topology that can, for example, be used to configure more than one subscriber. It also provides a measure of documentation and disaster-recovery. A script can be stored as a query object in a SQL Server Management Studio project.

Replication scripts can be created by hand, by the script generation of the replication wizards in SQL Server Management Studio, or by using Replication Management Objects (RMOs) to programmatically generate the script to create an RMO object.

When creating scripts to configure replication, it is best to use Windows Authentication so as to avoid storing security credentials in the script file. Otherwise you must secure the script file

Further reading:

SQL Server Replication
For the details of implementing replication, all the steps are documented here in Implementing Replication
details on configuring and maintaining a replication are here Configuring and Maintaining replication
For details on peer-to-peer replication, read Peer-to-Peer Transactional Replication
Some of the wider issues and dangers of replication are discussed here Data Replication as an Enterprise SOA Antipattern, in the excellent Microsoft Architecture Journal

Robyn Page and Phil Factor

Author profile:


Robyn Page has worked as a consultant with Enformatica and USP Networks with a special interest in the provision of broadcast services over IP intranets. She was also a well known actress, being most famous for her role as Katie Williams, barmaid and man-eater in the Television Series Family Affairs. She is currently having a career break to raise a young family.

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 20 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

Search for other articles by Robyn Page and Phil Factor

Rate this article:   Avg rating: from a total of 164 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Perfect Timing
Posted by: DarthDaddy (view profile)
Posted on: Tuesday, May 15, 2007 at 4:19 PM
Message: Thanks for condensing this material. I just begining a replication project. MSDN library is overyly complicated, not very usable. Thanks again.

Subject: Nice work!
Posted by: Anonymous (not signed in)
Posted on: Tuesday, May 15, 2007 at 5:09 PM
Message: As always...nice work.

Just one thing, if you can add the example scripts..this will make life easy for someone.

Thanks

Subject: Good Timing
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 16, 2007 at 7:13 AM
Message: Other than MSDN do you know any good sites for RMO examples (Mergre via Web)?

Subject: Re: Example scripts
Posted by: Robyn Page (view profile)
Posted on: Wednesday, May 16, 2007 at 8:01 AM
Message: The editor is hoping to do a Replication Workbench. We thought about including scripts into the Cribsheet but really, a Cribsheet is supposed to be a 'helicopter view' over the whole subject which doesn't get into too much detail, so we decided to wait for the Workbench for the scripts.
I forgot to give the main entrypoint into MSDN for its documentation on RMO programming which is http://msdn2.microsoft.com/en-us/library/ms146869.aspx I know of nothing as good as this anywhere else, and I find the MSDN content on RMO curiously lacking in pep. Are there any keen RMO programmers out there wanting to publish on Simple-Talk? There seems to be plenty of interest!

Subject: Good Article
Posted by: NZ-Simon (view profile)
Posted on: Wednesday, May 16, 2007 at 4:20 PM
Message: Thanks Robyn. I'm just coming to the end of a transactional replication project and it's good to see that (as a newbie to replication) I have pretty much followed the points in your article. Certainly, scripting out the process as you set it up via the GUI is invaluable! Having tuned the scripts, I can blow away replication and set it up again on a whim! And know that it'll work! One thing I hadn't found was the replication validation sp, which is good to know.

One area that caused me some problems, though, was the issue of applying snapshots to a working subscriber database. Because this database has user views, functions and procedures on them, these had to be dropped and recreated. SS offers a way of replicating these objects, but I found two issues:
1. When SS recreated the views, sp etc, it would apply them in the wrong order, so the creation of some of them would fail as they depended on other objects that hadn't yet been created.
2. The objects on the subscriber (reporting) database are different to those on the publisher database, so replicating them is of no use anyway!

So, the problem I was left with was how to apply periodic snapshots without a) losing views, functions etc and b) manually reapplying each object individually via scripts from source safe after the snapshot had been applied, given that the objects may change as developers work on them.

My solution was to make use of the pre- and post-snapshot scripting feature. I scheduled a job to generate a T-SQL script every night of all the user-defined objects in the subscriber database. One script had to contain all the DROP statements for the objects (the pre-snapshot script), the second script had to contain the definitions and reapply all the appropriate permissions (the post-snapshot script). The job made use of cursors and sp_helptext and accessed system tables etc.

While the solution was pretty simple in concept, getting it to work properly was quite involving. I'm surprised there isn't an easier way. So, I have a question:

Is there a better way to preserve db objects that refer to the subscriber tables when applying snapshots?

Many thanks

Simon

Subject: Appriciation
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 16, 2007 at 11:18 PM
Message: Good Work

Subject: Keep it up
Posted by: Anonymous (not signed in)
Posted on: Friday, May 18, 2007 at 10:24 AM
Message: Very informative & granular level of content, keep it up.

Subject: Excellent Overview
Posted by: Joe Egan (not signed in)
Posted on: Wednesday, May 30, 2007 at 3:36 PM
Message: I worked with replication, snapshot and merge mostly, for years; but I learned a lot from your article; and it's frankly the first resource to which I'd direct anyone starting with replication, which often proves very intimidating and difficult. Excellent overview!

Joe Egan
http://j0e3gan.blogspot.com

Subject: hope somebody's still checking this thread...
Posted by: jenniebee (view profile)
Posted on: Tuesday, August 07, 2007 at 2:00 PM
Message: I'm trying to establish replication between a SQL 2000 SP4 Publisher and a SQL 2005 Distributor. When I try to publish a single table, I get errors (text varying depending on method attempted: scripting, wizards, etc.) that all fault me for trying to go remote while the session is enlisted in a distributed transaction which has an active savepoint.

I am at a loss as to how to resolve this. Has anybody out there encountered this before?

Subject: hope somebody's still checking this thread...
Posted by: jenniebee (view profile)
Posted on: Tuesday, August 07, 2007 at 2:47 PM
Message: I'm trying to establish replication between a SQL 2000 SP4 Publisher and a SQL 2005 Distributor. When I try to publish a single table, I get errors (text varying depending on method attempted: scripting, wizards, etc.) that all fault me for trying to go remote while the session is enlisted in a distributed transaction which has an active savepoint.

I am at a loss as to how to resolve this. Has anybody out there encountered this before?

Subject: Snapshot Replication
Posted by: Linda (view profile)
Posted on: Thursday, May 01, 2008 at 9:29 AM
Message: I have a situation where part of a database must be transfered in a read-only database on a web server so clients can view some data. The data must be updated every hour. I chose the snapshot replication. To avoid long locks while users are entering data, I transfer the complete database once during the night and every hour, i transfer (try to) only the changes made during the day in 2 tables. For those 2 tables I specified "Delete data in the existing table that matches the row filter statement" but it doesn't seem to do what I expected since I get 2601 errors. Did I choose the right solution ?

Subject: Snapshot Replication
Posted by: Linda (view profile)
Posted on: Thursday, May 01, 2008 at 9:30 AM
Message: I have a situation where part of a database must be transfered in a read-only database on a web server so clients can view some data. The data must be updated every hour. I chose the snapshot replication. To avoid long locks while users are entering data, I transfer the complete database once during the night and every hour, i transfer (try to) only the changes made during the day in 2 tables. For those 2 tables I specified "Delete data in the existing table that matches the row filter statement" but it doesn't seem to do what I expected since I get 2601 errors. Did I choose the right solution ?

Subject: Please sign in to comment.
Posted by: Nadine (view profile)
Posted on: Thursday, May 01, 2008 at 10:06 AM
Message:

Anonymous comments have been disabled on this article due to relentless spamming.

Please do continue to comment -- but you will need to sign in or join in order to do so. It jsut requires a username, email address and password. Simple-talk does not share user details with any third parties, under any circumstances.


Subject: Well Done
Posted by: MACloud (view profile)
Posted on: Monday, November 16, 2009 at 6:21 PM
Message: Thanks for writing this article. It definitely helps me understand better the different parts of the replication process.

"One of the most important concerns is the time delay, or latency, of transactions from the publications appearing in the subscriber database. At times of high transaction throughput on the publisher database, bottlenecks can occur."

Ha, as soon as I read this, I thought of "The Goal".

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.