New replication features in SQL Server 2008 and what they mean to you

SQL Server 2008 features several replication improvements, and some are more compelling than others. Here you'll learn what's new with snapshot delivery, peer-to-peer topologies, merge replication and more.

Although the changes in replication when moving from SQL Server 2000 to 2005 were radically different, the enhancements

for SQL Server 2008 are much more subtle. The following replication features are new in SQL Server 2008:

  • Radical improvements in snapshot and transactional replication command delivery
  • A new interface for creating and modifying peer-to-peer topologies
  • Peer-to-peer replication, which now supports conflict detection and replicates schema changes without having to go offline
  • Deeper replication integration with database mirroring and log shipping
  • A class that developers can use for database synchronization that requires no DBA administration (Sync Services)
  • Changes to the Replication Monitor

Let's examine each of these changes in more detail.

Improvements in snapshot and transactional replication command delivery

A snapshot is replication data, schema and metadata that can recreate the tables, stored procedures, functions, views and so on from the publisher to the subscriber.

This Microsoft blog post has information on the scalability improvements, but the most interesting part is in the following quote:

"The results are nothing short of eye-popping. A pull replication scenario, which used to run in 223 minutes using [SQL Server 2005] Yukon on Win2K3, takes just under 2 minutes on [Windows 2008 Server] Longhorn server with Katmai. And an 11.3GB snapshot was sent >2K miles in ~23 minutes, a rate of almost 500 MB/minute. That's BYTES, not bits. Talk about 'better together!' "

In addition to snapshot delivery, SQL Server 2008 running on Windows Server 2008 can take advantage of enhancements to the TCP/IP network stack to improve replicating command latency. Windows Server 2008 features improvements to the TCP/IP network stack that allow for adjusting the TCP/IP packet parameters (in some case auto adjusting). The specific parameters in question are:

  • Receive Window Auto-Tuning
  • Send Buffer Scaling
  • Compound TCP

According to this white paper on geo-replication performance gains, Microsoft's Engineering Operations team achieved a staggering 11,345.13% performance increase for pull replication.

A new interface for creating and modifying peer-to-peer topologies

Peer-to-peer replication is a variant of transactional replication, where multiple nodes will publish changes to other nodes in the topology. Should one node disconnect from the network, it can continue working and the other nodes in the topology will replicate to each other. When this node comes back online it will receive changes that occurred while it was offline from one of the other nodes in the topology. The interface can also replicate changes that occurred on it while it was offline. This replication type is a mesh topology contrasting with the publish/subscribe topology of the other replication types, where the publisher is cleaning house for transactions and determines which changes go where.

Figure 1 has an illustration of this new peer-to-peer topology interface, while Figure 2 demonstrates how to add a new node to this topology.

Figure 1 - The peer-to-peer topology wizard
The peer-to-peer topology wizard

Figure 2 - Adding a new node to the peer-to-peer topology
Adding a new node to the peer-to-peer topology

Peer-to-peer replication enhancements

In SQL Server 2005, making changes to your replication topology or to the tables you are replicating is a cumbersome process. DBAs are forced to first stop activity on all nodes, check to verify that all changes have been replicated, then make the change, and once again verify all changes have been replicated to all nodes, and then finally allow users back in.

In SQL Server 2008, you can make changes at any time. You can add or remove nodes and make schema changes without keeping users from accessing the system. SQL Server 2008 also permits conflict detection, which is enabled by default. A conflict occurs when you attempt to update a row that has been deleted on another node, update the same row on different nodes or assign the same primary key value on different nodes simultaneously. In SQL Server 2005, this would cause the distribution agent to fail and you would have to manually fix the problem on the affected nodes. It can be very complex and laborious if there are large numbers of conflicts.

SQL Server 2008 allows you to disable conflict detection, which causes the distribution agents to skip conflicts. Microsoft recommends that you partition your data as much as possible to eliminate any chance of conflicts.

Deeper replication integration with database mirroring and log shipping

Previous versions of SQL Server forced DBAs to reinitialize subscriptions of databases if they were publisher of log shipping. In SQL Server 2008, it is possible to failover to a mirror database or secondary server and have replication continue to replicate to the subscriber. To take advantage of this, you need a remote distributor. You can find detailed instructions in this white paper on providing high availability using database mirroring.

Sync Services

Sync Services is a .NET library that allows you to synchronize changes that occur on a wide variety of data stores with one another. It was designed and developed by the same team at Microsoft that brought you merge replication. The goal of Sync Services is to create a lightweight class that carries out the essential functionality of merge replication without the maintenance or administration. This link breaks down the difference between Sync Services and merge replication, which essentially boils down to your skill set. In other words, developers should use Sync Services, while DBAs should go with merge replication. It should be pointed out that merge replication ships with far more features than Sync Services and is wizard driven.

Changes to the Replication Monitor

There have been a few slight modifications to the Replication Monitor in SQL Server 2008. Let's take a look at how the new Replication Monitor compares to the one in SQL Server 2005.

SQL Server 2005 SQL Server 2008
The SQL Server 2005 Replication Monitor has three tabs for each publisher.
  • Publication
  • Subscription Watch List
  • Common Jobs
The SQL Server 2008 Replication Monitor has three tabs for each publisher.
  • Publication
  • Subscription Watch List
  • Agents
The SQL Server 2005 Replication Monitor Common Jobs tab consists of:
  • Replication Agents Checkup
  • Reinitialize Subscriptions Having Data Validation Failures
  • Distribution Clean Up
  • Agent History Clean Up
  • Replication Monitoring Refresher
  • Expired Subscription Cleanup
The SQL Server 2008 Replication Monitor Agents tab has a drop down list box that allows you to display the snapshot agent, log reader agent, queue reader agent or all maintenance jobs. The maintenance jobs are the same jobs that are displayed in the SQL Server 2005 Replication Monitor Common Jobs tab.
If you drill down on a publication in SQL Server 2005 Replication Monitor, two or three tabs (depending on your replication type) are displayed:
  • All Subscriptions
  • Tracer Tokens (for transactional publications only)
  • Warnings and Agents
SQL Server 2008 has three or four tabs:
  • All Subscriptions
  • Tracer Tokens (for transactional publications only)
  • Warnings
  • Agents

That covers the main changes to replication in SQL Server 2008, the most compelling of which are snapshot and transaction delivery improvements when running SQL Server on Windows 2008.

ABOUT THE AUTHOR
Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied both economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.

This was first published in April 2009

Dig deeper on SQL Server Replication

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close