New replication features in SQL Server 2008 and what they mean to you
Hilary Cotter, Contributor
04.13.2009
Rating: -4.50- (out of 5)
![]() | |
![]() | |
![]() ![]() ![]() ![]() |
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:
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:
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 Figure 2 - 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.
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.
|