Many database designs use the IDENTITY data type as a primary key. To effectively use this kind of table design with bidirectional transactional replication and avoid conflicts such as duplicate keys, you must include two steps in your setup process. First, you must define the IDENTITY column with the NOT FOR REPLICATION property. Second, if the design requires insertion of rows independently on both servers, you need to start each IDENTITY column at a different number. For example, you might specify that the Customer table on server LONDON starts at the IDENTITY value 0 and the Customer table on server CAPETOWN starts at the IDENTITY value 1,000,000. You achieve this separation by using the DBCC CHECKIDENT RESEED command directly or by using the sp_addscriptexec functionality to send the commands to replicated servers after the initial setup and synchronization phase.
Alternatively, you can avoid duplicate-key conflicts by designing your tables so that they use UNIQUEIDENTIFIER as a primary key. But this method increases overall storage-space requirements, and not everyone likes having a tongue-twisting report-spaceeating customer ID such as C677EF69 -8645-4C1D-8816-7E629F8B1AA0 (which is the ID SQL Server generates for this data type).
Tracking conflicts after they occur can also be difficult when you're using bidirectional transactional replication. Update conflicts are especially difficult to track because the last update to a specific column for a row overrides the previous result. For example, a conflict would occur if a record is updated on server LONDON, but when it arrives at the destination server CAPETOWN, the record has already been deleted. In this situation, the default behavior for transactional replication is to raise an error and stop processing. At this point, the administrator has a couple of options, including removing or modifying the entry in the replication system tables in the distribution database—thus breaking the commandment "Thou shalt not modify a system table"—or re-initializing the subscription. However, depending on the type of connection you have, re-initializing could be a glacial experience. Another alternative is to rerun the Distribution Agent with the additional parameter Skiperrors %error_number%. To further reduce conflict risk, you can filter publications on a field such as Region_ID. With this filter, server LONDON would publish only data that belongs to Region_ID=1, and server LONDON would subscribe to server CAPETOWN, which publishes only data from Region_ID=2.
You can also use one of the following nonpartitioned methods to track and manage conflicts: modifying the text or body of the replication-generated stored procedures to include custom code; adding tracking columns (e.g., universal datetime, unique identifier, or priority data type columns) or triggers to the replicated tables; selecting the XCALL option, which provides full access to a modified record's current and previous values; or some combination of these actions. For sample code and more information about these methods, see the "Bidirectional Replication" BOL topic at mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\replprog .chm::/rp_replsamp_3ve6.htm. As you can see, handling conflict is the Achilles' heel of bidirectional transactional replication.
Limited extensibility. Another factor to consider when thinking about using bidirectional transactional replication is whether you'll want to extend the bidirectional design to include more than two servers. Adding filters for partitioning purposes to publications can extend bidirectional replication to include multiple servers. However, doing so can dramatically increase complexity, administration costs, and even the potential for performance degradation. Performance is highly dependent on filter complexity. For optimal performance and supportability, design your replication system so that it always has only one central hub and so that communication is always between no more than two servers. For example, as Figure 2 shows, server LONDON can bidirectionally replicate with server CAPETOWN and server NEWYORK, but server CAPETOWN and server NEWYORK can't replicate directly with each other. In this unfiltered design example, all changes would flow among all the servers: Changes in CAPETOWN would eventually arrive in NEWYORK, but they'd always go through the central LONDON server. If necessary, you could implement simple business rules in your user application to enforce modifications only to rows belonging to a specific location or server. This design is advantageous in applications that let users browse all data but modify only local records. However, if users frequently make numerous changes on each server, dramatically scaling out this unfiltered design can be challenging because the high number of transactions flowing between servers could increase the load on network performance.
Further Improving Performance
When you use bidirectional transactional replication, you can further decrease latency between servers by lowering the PollingInterval parameter of the Logreader and Distribution agents for each published and subscribed database. For example, setting PollingInterval to 2 will cause the agents to "sleep" for a maximum of 2 seconds after they find no transactions to replicate. So the Distribution Agent could be a maximum of only 2 seconds behind the transactions the Logreader writes to the distribution database. And the Logreader Agent would be a maximum of 2 seconds behind a transaction committed to the log. Of course, as long as transactions are waiting to be replicated, the agents don't enter into the PollingInterval logic.
The next release of SQL Server, SQL Server 2005, will offer a transactional-replication feature named Peer-to-Peer, which will expand on the current bidirectional offering by providing out-of-box support for multiple bidirectional subscribers. Using the example in Figure 2, Peer-to-Peer's enhanced loop-back detection algorithm will let CAPETOWN directly replicate to and from NEWYORK instead of depending on LONDON. Although this feature offers good scalability (depending on data-change volume, you could support between 10 and 20 peer-to-peer servers) and increased UI integration and supportability, this version still won't support out-of-box conflict management.
Bidirectional transactional replication offers exceptional delivery rates and very low latency, supports modification to text and image data types, maintains transactional consistency, and doesn't require additional columns or triggers. However, it lacks the highly advanced conflict-management and powerful dynamic-filtering support that merge replication offers. Is it for you? If you want high performance, bidirectional transactional replication has no equal.
End of Article
Prev. page
1
2
[3]
next page -->
