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 replicatio...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
n 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:
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
[IMAGE]
Figure 2 - Adding a new node to the peer-to-peer topology
[IMAGE]
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:
[IMAGE] Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a Web and database consultant, and has worked with SQL Server for 11 years. Cotter is Director of Text Mining at RelevantNoise, dedicated to indexing blogs for business intelligence. 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 studied economics at the University of Calgary and computer science at UC Berkeley. He is author of the book A Guide to SQL Server 2000 Transactional and Snapshot Replication and is currently working on books devoted to merge replication and Microsoft search technologies. Hilary Cotter can be contacted at hilary.cotter@gmail.com.
