Bi-directional SQL Server database replication

This article will cover using a 3rd party transaction log reader for setting up a bi-directional SQL Server replication implementation

Replicating transaction log files from a primary production database to the secondary replica is a fairly straightforward process. In How to set up a DDL and DML SQL Server database transactional replication solution article, we showned how to use ApexSQL Log to feed all DDL and DML changes made on the primary database (Publisher) into a secondary database replica (Subscriber) on a different SQL Server instance. In this article, we are going to examine a case when SQL Server replication needs to run in both directions, and both databases need to hold roles of primary feeder and recipient of data and structure changes – bi-directional replication. Simply put, the two databases will simply exchange all DML and DDL changes with each other.

Challenges

Primary key violations and duplicates

Primary key violations and duplicating existing primary keys are major setbacks and concerns which can lead to irregularities or even stop the SQL Server replication process or permanently desynchronize databases. Due to the fact that both databases will be in the roles of a Publisher and Subscriber and that both will be written to directly by users and/or applications, some primary key rules need to be set in order to avoid primary violations which would lead to further issues mentioned above.

With the above in mind, a different seed needs to be used to increment each primary key.

A typical example would be to set seed ‘1’ for ‘Database 1’ and to set seed ‘0’ for ‘Database 2’. Since there are two databases, incrementing by 2 will allow us to have guaranteed different values for primary keys between these two databases. While Database 1 typical values will be 1, 3, 5, 7, 9, 11 and so on, values for Database 2 would start with 0, 2, 4, 6, 8 and continue incrementing by two.

Simply put, Database 1 will have primary keys with odd values, while Database 2 will have primary key values only with even numbers – and this guarantees that there will be no primary key violations or duplicates.

Never-ending circle of replicating same operations repeatedly

Never-ending replication of same operations over and over again is another challenge which needs to be taken into consideration and must be addressed before setting up SQL Server replication tasks. In order to ensure that all changes are being replicated only from Publisher to Subscriber (in both cases), and not returned back once the roles are changed (on inverse replication). This endless circle would not only duplicate the data each time it completes and would increment number of data copies by one, each time the SQL Server replication process is performed. With this in mind, it is essential to ensure that replicating already replicated data must be avoided at all cost.

Sure, if the replicated table already has primary key set, or if we are replicating some DDL change which already took part and cannot be duplicated (specific SQL Server replication fails with ‘data/object already exists’ error/warning), in cases when there are no primary keys set on the table, duplicates would still be possible, and must be stopped – which can easily be done by creating unique SQL Server users which will be used to execute replicated changes on the subscriber databases.

In this article, we are going to demonstrate how to use ApexSQL Log, a SQL Server transaction log reader which allows replication of all DML/DDL operations found in native transaction log files, in order to configure two-way SQL Server replication between two databases, while ensuring changes and data remain true and no duplication or violations prevent these databases from remaining in perfect synchronization.

Environment setup:

Before showing how ApexSQL Log is going to allow us this bi-directional SQL Server replication, let’s just define the environment we’ll use in our example:

  • SQL Server 1 hosts Database_1
  • On SQL Server 1 we’ve created a user Replicator_1 with full write access to Database_1
  • SQL Server 2 hosts Database_2
  • On SQL Server 2 we’ve created a user Replicator_2 with full write access to Database_2

Solution

As mentioned above, in order to setup a two-way bi-directional SQL Server replication, we are going to use ApexSQL Log to setup two separate, yet identical, jobs that will read transaction log files of each database and create a replay script which will be executed on each database using unique users with write privileges to apply the changes to the Subscriber database, while these changes will be ignored in the following process, when the database switches from the Subscriber mode to the Publisher. Additionally, we are going to use a ‘continuous auditing’ feature in ApexSQL Log which will automatically remember where the previous replication job ended (by remembering exact LSN value of the last operation) and use this information to start the next replication job at the exact same moment, ensuring that no operations are ignored or overlooked, as well as there are zero duplicates in the SQL Server replication job.

Here is a detailed guide of the solution. We are going to use ApexSQL Log GUI to configure the task and automatically generate a PowerShell script for replication which can be easily automatized:

  1. Start ApexSQL Log, and click on the New button to initiate new session

  2. Choose a SQL Server instance and authentication

  3. Select a database from the drop menu

  4. Click Next to proceed
  5. In the next step of the wizard, ApexSQL Log will automatically locate all available data sources for the selected database (transaction log backups). If we intent to read only from the online transaction log files, checking the Online transaction log file option will suffice. In case we are taking regular or occasional transaction log backups, the ‘Add pattern’ option can be used to instruct ApexSQL Log to automatically read from all available backups from the specific folder which match specified naming pattern. Click Next to proceed.

  6. In the next step of the wizard, opt for ‘Undo/Redo’ option, since the output for this task will be a redo (replay) script, since the replay script is the source of our SQL Server replication job.

  7. In the Filter setup step of the wizard, choose a Continuous auditing option. This option will create an .axtr file which will keep track on the last LSN value audited

  8. In the Operations tab, choose both DML and DDL operations to be included – this is not a mandatory, but recommended setup. If due to any requirements SQL Server replication process should ignore DDL changes, and replicate only changes on the data, then Operations filter can be set up accordingly

  9. Next, into the Users tab, it is important to exclude our SQL Login which is used to replicate data into this database, which is in this example Replicator_1. Note that different SQL Server users can also be excluded from the process here, based on the replication requirements, by simply unchecking those checkboxes next to the user names.

  10. In the final step of the wizard, choose to create a Redo script, which finalizes the task of auditing configuration. By clicking on the Create button, ApexSQL Log will create an automation script, available as batch, or as PowerShell. Clicking on the Save button will create a PowerShell file which will trigger ApexSQL Log replication as per configured information

Now that we’ve created a SQL Server replication task #1, we need to repeat this process for the second SQL Server (database). Just make sure to include the other replication user (Replicator_2 in our example) in step 9.

With this, we have 2 PowerShell files which can be manually executed in order to replicate between these two databases on demand. The only remaining step is to automate the process by scheduling execution of generated PowerShell scripts in order to trigger ApexSQL Log replication on both servers. Additionally, generated scripts need to be executed on the appropriate ‘Subscriber’ servers, and both these tasks can be achieved using SQL Server Agent Job in SQL Server Management Studio by creating job which will first trigger ApexSQL Log to generate Redo script, and then execute the script on the subscriber database. We’ve already wrote on automation via SQL Server Agent in the How to set up a DDL and DML SQL Server database transactional replication solution article and would suggest checking the presented solution when auditing your bi-directional replication.

Once the job automation is configured on both SQL Server instances, bi-directional SQL Server replication job will be set and ensure that both databases are up to date and synchronized on a regular notice.

FAQs

Q: Can this solution work for three-way SQL Server database replication

A: Indeed, it is quite possible to use this approach to perform three-way replication where all three databases will have publisher and subscriber roles.

In case of three databases, the following changes should be made:

  • Primary Key rules must be changed to have seeds 1, 2 and 3 between the databases, and have an increment of 3 (db1 – 1, 4, 7… db2 – 2, 5, 8… db3 – 3, 6, 9, 15…) in order to ensure each PK value is unique
  • Using the guide above, a third unique user will be required for the third SQL Server node, in order to ensure changes made by that particular user are taken into consideration only when replicating on other 2 nodes

 

February 4, 2019