Thankfully, the solution is relatively simple. When you use the sp_addsubscription stored procedure to add the subscription to the Subscriber, you need to add the parameter @loopback_detection = 'true' to the stored procedure. (For more information about using sp_addsubscription, see the Microsoft article "HOW TO: Implement Bidirectional Transactional Replication" at http://support.microsoft.com/default .aspx?scid=kb;en-us;820675&Product=sql.) Adding this parameter correctly identifies each transaction so that the Distribution Agent doesn't redeliver it to the original Publisher. Unlike trigger-based merge replication, which requires some complex coding to figure out appropriate data row and column modifications as well as conflicts, this log-based transactional-replication method is relatively simple. Bidirectional transactional replication lets the Publisher read transactions to and apply transactions from Subscribers at a high rate of speed—often thousands of commands per second—and at low latencies (the time lag between when a change occurs on one server and when it's applied to the other server). Of course, these rates depend on network and disk read/write latency and the size of the rows being modified. Nevertheless, on a fast, reliable network, even servers as far apart as New York and Los Angeles could operate at only a few seconds (possibly less than 4 seconds) behind each other. Plus, unlike merge replication, transactional replication lets you replicate the execution of stored procedures. You might want to use stored-procedure replication when you need to modify a large number of rows. Instead of moving the resulting rows across the network, you replicate only the procedure execution statement that the Publisher issued. For example, you might replicate the statement
EXEC
PROC_update_prior_month_value
@month, @status, @value,
@percent
thus saving a lot of I/O, networking, and CPU time.
Is It for You?
You can realize many performance benefits without changing your schema when you use bidirectional transactional replication. But you must also evaluate the costs and disadvantages of this type of replication. The complexity of initial setup, difficulty of conflict management, and limited extensibility are all factors you need to weigh before deciding bidirectional transactional replication is right for your environment.
Complex setup. Because bidirectional transactional replication isn't fully UI-wizard driven, you have to create or modify some replication setup scripts by hand, and Subscriber initialization can be complex. For example, one method of setting up bidirectional transactional replication of a database between the two servers in Figure 1 requires the following steps:
- Detach a database from server LONDON, and attach it to server CAPETOWN.
- Use the Create Publication Replication Wizard to create a transactional publication on server LONDON and server CAPETOWN.
- Because you already have the data you want to replicate, use the no-sync option on server CAPETOWN when transactionally subscribing to the publication on server LONDON.
- Unfortunately, when subscribing, you can't add the @loopback_detection = 'true' parameter by using the Replication Add Subscription Wizard. So on server CAPETOWN, you must use the Generate Script Wizard to generate and save the replication script for the subscription. Drop the subscription from server CAPETOWN, modify the replication script you generated and saved, add the @loopback_detection = 'true' parameter to the call to the sp_addsubscription procedure, then execute the script on and against server CAPETOWN.
- You now modify the replication script again, this time adding the correct server and database names for server LONDON, and execute the script on and against server LONDON.
Voilà—you've installed bidirectional transactional replication! And, no, it wasn't easy.
Complex conflict management. The next factor to consider is the rather sticky area of conflict management. A conflict can arise when two servers modify the same row, causing data to be out of sync. Unlike other Subscriber-updateable forms of replication, bidirectional replication doesn't offer an out-of-box conflict-management solution. Instead, you're left to your own devices. To handle conflicts, merge replication provides rich out-of-box features to manage complex conflict cases (e.g., row-level and column-level conflicts) and offers developers and architects extended flexibility in the form of custom conflict handlers written in T-SQL or other programming languages. Immediate updating replication Subscribers avoid conflicts by using the Distributed Transaction Coordinator (DTC) to perform a two-phase commit. However, this technique requires an online, connected environment. Although the queued-updating solution supports offline data modification, queued updating offers a limited conflict-handling model; either the Subscriber or the Publisher changes will take precedence for the entire transaction batch. Furthermore, as I noted before, neither queued updating nor immediate updating supports modification of the text or image data type, whereas both merge and bidirectional transactional replication do.
Bidirectional transactional replication works best when you don't expect conflicts, when you can avoid conflicts by using application restrictions (e.g., users can modify data based on data ownership), when publications are filtered, or when only one server is modified at a time. (The last condition occurs most often in a high-availability or failover scenario.) You can avoid conflicts in bidirectional transactional replication in several ways.
Prev. page
1
[2]
3
next page 
