Data sync is the task which needs to be done very diligently. It should work effectively. In this post, you’ll see how we did data synchronization between the two SQL Server instances.
Challenge:
We had to capture the transaction in a few tables and move data to another database which is located on the other side of the world. There is no direct connection between the two databases. There is an integer primary key in the few tables. We had the flexibility to add a column but we couldn’t modify the existing columns because that is used by other applications.
Solution:
SQL Table Dependency – To monitor table change.
NServiceBus – A reliable data persistent storage where messages can be stored and that can be polled by the individual nodes.
We started with the SQL Table Dependency application which will track DB Changes. We followed the article Monitor Table Change with SQL Table Dependency.
With this application, we were able to track Insert/Update/Delete changes in the table. It gives the notification message which has a complete record and we can play with it.
We were then suggested to use NServiceBus. It will persist the message until the message is read by the receiver. The advantage of using NServiceBus is it gives consistency guarantees till the message is read and processed by the receiver. The receiver will notify the Sender for the transaction processed successfully. You can start with NServiceBus Step by step to understand the architectural concept behind the software. The best about NServiceBus is it provides a lot of sample code which you can download and run the application to see how it works and understand. All the sample code should be working as you run the application. I personally tried with three sample codes.
We initially started with the Publisher-Subscriber application and used MSMQ for a data persistence. In this case, the sender and receiver both should run on the same server and can use the MSMQ to transfer the data. Because of some security norms, there wouldn’t be a direct connection between the source and target database. So we changed the approach and decided to go with NServiceBus Sender-Receiver application with NHibernate Persistence Usage. We did a POC keeping in mind the required business objectives, which worked very well and then we added the required business logic in SQLTableDependency, Sender, and Receiver to transfer the data from one server to another. It takes as little as 3-5 seconds for the data to be transferred and processed. The NServiceBus application has a mechanism to retry sending data if any issues, you can go through with the recoverability article to know more about it.
One SQLTableDependency application can track one table, we had to track other 5 tables as well. Data in the tables are associated with the Users table which the SQLTableDependency is monitoring. In SQLTableDependency, it’s not easy to monitor 5 tables in one application, there is some way but we didn’t invest time into that. We followed another approach. Users table is monitored by SQLTableDependecy, we added GUID column to track the record and push the same GUID on the target server tables. The operations on the Target Database is done based on the GUID.
We created triggers for 5 tables and one audit table to track the changes. In trigger we did two things, first, log the changes in the audit table and then update the user’s table which is monitored by SQLTableDependency with the GUID of the audit table. A business logic is in the SQLTableDependency to fetch the table changes using the Audit table and pass on the data to NServiceBus sender application.
I’m sure, you would have a question of how the application get notified about the data change?
We used MSMQ for storing the data from SQLTableDependency. NServiceBus sender will monitor the MSMQ. Any record added in MSMQ will be processed by the sender application and then removed from the MSMQ. NServiceBus NHibernate Sender-Receiver application uses SQL database internally for processing. It creates a new database. Sender and Receiver both should have access to the database.
So we have three applications – SQL ServiceBroker (SQLTableDependency), Sender and Receiver. These applications will be running as a Windows Service and deployed on the servers. Receiver application will be running somewhere in the world and has access to Source and Target Databases using the secured VPN.
We created:
- Triggers
- Triggers for Insert, Update and Delete operations for the required tables. Triggers will add details about the transaction in the Audit Table and update the Users table with the SyncID.
- Audit Table
- SyncOperation table in SQL DB to track the changes in the above tables
- SQL Service Broker with SQL Table Dependency application
- This will track the users’ table.
- On Insert/Update – It will take the values from the Users table and Sync Operation Table (if any) and push them to the Messaging Queue.
- NServiceBus Sender
- This will keep track on the MSMQ
- Any data added in the MSMQ will be sent to the receiver and it will be cleared from MSMQ.
- NServiceBus Receiver
- A receiver will receive the data sent by the Sender.
- It will then process the data by syncing it with the target Database.
- Once the data is updated, a receiver will then notify the sender about the successful processing of the given data.
- Stored Procedure
- Stored procedure on the target database for each table to process the data
There are other ways as well to solve this business problem, We used this approach based on the requirement and suggestion from the client. Thanks to Senthilvel for helping me in this.
If you are going to use SQLTableDependency/NServiceBus and having any concern you can reach out to me.