two-way replication in SQL Server? Piece of cake

Two-way replication, bi-directional replication -call it anything you like- is a very nice feature of SQL Server that allows you to take full advantage of SQL Server’s Transactional replication. And in both directions!

If you find yourself in a situation where you need to have a Subscriber that can update data and these changes should also be applied to the Publisher, don’t worry. You can setup a new transactional replication that originates from the Subscriber and targets the Publisher.

– But isn’t there the risk of transactions entering a vicious circle and never finding their way out?
– Do not worry about that. It has been taken care of.

SQL Server has (enabled by default) a very nice option called “Loopback detection”. This option makes sure that transactions never travel back to their Originator through replication!¬†In other words, if table ServerA.T1 is replicated to ServerB.T1 and also table ServerB.T1 is backwards replicated to ServerA.T1, a change on either table T1 (be it Insert/Update/Delete) will not return to the Server that originaly executed it.

Did I mention that this option is enabled by default?¬†However I like to specify it explicitely, just for reference when I read the Replication script. (cause you’re going to do it with a script!)

There are however, some points to take into account when setting up such a replication topology.

Do whatever you want with the first replication (let’s call it Primary) regarding the initialization of replicated articles on the Subscriber. I personally prefer the snapshot initialization for small to medium sized databases and the initialize-from-backup option for bigger to huge databases.

Don’t forget however that you can accomplish painless additions of new articles by creating small-sized snapshots only for the new articles (relevant post will follow), rather than having to re-initialize the entire subscription!

So, during the creation of the recond replication (let’s call it Loopback) you should select:

@pre_creation_cmd = N'none' -- so that the original object is dropped/truncated/deleted. After all you have made your choices while setting up the first publication
@schema_option = 0x000000010203008F -- or something similar, as you don't want to alter the original object (by copying indices, constraints etc)

Of course you don’t need any Snapshot to transfer data to the original Publisher which is now the Subscriber. You expect that, as soon as the replication is setup, data should start travelling on both directions. To accomplish this, you have to use the following option for the Loopback subscription:

@sync_type = N'replication support only' -- which assumes that data is already synched and you want synchronization to start instantly

Of course don’t forget two other crucial options

@update_mode = N'read only' -- which of course means that you are setting up a simple transactional replication
@loopback_detection = 'true' -- for easier understanding as it is a default value after all

With those notes in mind you should be able to setup a two-way replication quickly and easily. Of course you should choose to do everything with T-Sql to have full control.

Hope you find this post helpful.

Leave a Reply

Your email address will not be published. Required fields are marked *