Managing a distributed database system presents many challenges, not the least of which is the need to quickly synchronize updates. Transactional replication and merge replication are well-known, effective replication techniques, but each has limitations and can limit performance. If you're frustrated by the limitations of these replication methods, you might be overlooking another solution: bidirectional transactional replication. In some cases, implementing this replication technique provides a remarkably fast, geographically dispersed, multisite update solution—if you're willing to jump some hurdles. Microsoft's SQL Server Development Customer Advisory Team helps customers architect bidirectional transactional replication in markets such as finance, stock trading, and telecommunications. Most of these customers use the technology to create a database-reporting solution that they can quickly and easily convert into a failover or disaster-recovery site.
SQL Server has supported this fast, powerful form of transactional replication since SQL Server 7.0. However, few DBAs are aware of it or have harnessed its power, largely because of inadequate documentation and a general lack of understanding about its functionality. SQL Server 7.0 Books Online (BOL) includes a scanty section about bidirectional transactional replication, which Microsoft regrettably omitted from early SQL Server 2000 BOL editions. Microsoft has since included some documentation in the latest SQL Server 2000 BOL edition, which you can download at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp.
Web Table 1 (which you can download at InstantDoc ID 42858) compares the features of four replication methods: bidirectional transactional, transactional queued, transactional immediate updating, and merge. Most DBAs use transactional replication to disseminate a flow of logged transactions from one source, the Publisher, to one or more non-updated, usually read-intensive target servers (e.g., reporting servers), the Subscribers. DBAs typically use this type of replication to increase aggregate read performance. Immediate updating subscriptions, which Microsoft introduced in SQL Server 7.0, lets transactional subscribers modify data on the Subscriber. However, subscribers can modify data only through a network connection to the Publisher (i.e., the Subscriber must be online). In SQL Server 2000, queued updating subscribers can modify data on the Subscriber without being connected to the Publisher. Both of these methods require you to add a column to user tables. Plus, these methods don't support updating of text or image columns and track changes by using automatically generated triggers.
Although merge replication (which Microsoft introduced in SQL Server 7.0) supports modification of text and image columns, it also uses triggers and requires that you add to tables a replication-specific column of data type uniqueidentifer (i.e., a GUID), which is an extra 16 bytes and is globally unique. Adding these triggers and columns can cause trouble for some applications, and many DBAs frown on the practice. Microsoft designed merge replication specifically for offline mobile users (e.g., users of sales-force automation applications), providing extensible change- and conflict-tracking and management capabilities as well as highly flexible partitioning and filtering functionality. However, to get these benefits, you have to accept the loss of transactional consistency, the addition of a GUID column and triggers, and storage of extra metadata (for example, the merge-generated triggers populate tracking tables to identify data modifications and possible conflicts).
If you want a high-performance, non-triggerbased way to keep two updateable but transactionally consistent sites in sync without adding extra columns, bidirectional transactional replication might be your solution. As the name implies, bidirectional transactional replication provides replication between two servers by using transactional replication agents: the Logreader Agent and the Distribution Agent. The Logreader Agent finds transactions for replicated articles in the database log, builds a corresponding SQL INSERT, UPDATE, or DELETE statement, and writes these statements as transactions to the distribution database. The Distribution Agent finds replicated transactions in the Distributor server database and writes them to the subscribing database. In this form of replication, the Publisher is also a Subscriber and the Subscriber is also a Publisher of the same data. For example, as Figure 1 shows, server LONDON transactionally publishes the Customer table as Customer_Publication_A and server CAPETOWN transactionally subscribes to server LONDON's Customer_ Publication_A. Server CAPETOWN now transactionally republishes the same Customer table as Customer_Publication _B, and server LONDON subscribes to Customer_Publication_B. Note that this method establishes these relationships without requiring you to add a replication-specific column to the Customer table, and you don't have to add triggers on either server to track changes.
You've probably figured out that the relationships I described won't work because any change on either server would cause an endless barrage of data between servers. If a user updates a Customer row on server LONDON, the LONDON Logreader Agent would detect the transaction and write it to its distribution database. The Distribution Agent belonging to server CAPETOWN would pick up the transaction from the distribution database on LONDON and write it to the Customer table on server CAPETOWN. However, the Logreader Agent on server CAPETOWN, which is monitoring the published database log, would also pick up the transaction and put it in its distribution database. The Distribution Agent on server LONDON would find the transaction in the distribution database on CAPETOWN and reapply it to server LONDON. Because neither server knows which server initiated the change, both servers would continually propagate the update. You wanted replication; you got Agent Smith from The Matrix Revolutions!
Prev. page
[1]
2
3
next page 
