Good morning dear reader.
Today I am writing this blog while sitting at a trampoline park. My six-year-old son loves this place, and we tend to find ourselves here at least one day, most weekends.
The discussion on tap for today is about SQL Transactional Replication. Recently, I had a request in my work queue to add replication from a SQL 2019 on-premises server to an Azure SQL Database. Sounds easy enough, right!!!
Problem
Use transactional replication to replicate similar data from four databases to one Azure DB. This sounds pretty straightforward for anyone who has done any replication work.
However, once I had everything setup and working, things stopped working, and it was a head-scratcher as to why. I made the proper settings configurations on each article, or so I thought. Let me show you the scenario in more detail.
Scenario
First, I restored the WorldWideImporters database four times on my local SQL Server 2019 instance to demonstrate this anonymously so no innocent parties are identified.
My example shows WWI_1 through WWI_4 to show a scenario similar to what I was asked to setup. We will be taking the Sales.Invoices table from each database and replicate that data to the Azure DB subscriber.
Notice how the subscriber database is empty before starting the replication configuration.
The next step is to setup a Transactional Replication Publication with our first database, WWI_1. This includes adding the Sales.Invoices table.
Here you can see I selected my article from the list of available objects to publish. From this point, since we will combine multiple sources into one destination, I will choose Article Properties and change the Destination Object Owner to WWI_1 to signify the data source. When I save this publication and perform the initial snapshot, my data for this database and table will begin flowing to my subscriber.
The novice person to replication might not know that stored procedures are created on the subscriber, facilitating the data movement between the publisher and subscriber.
You see that the WWI_1.Invoices table has been replicated. Also, notice that there are Delete, Insert and Update stored procedures created for the reason mentioned above.
Things are looking in great shape to duplicate my efforts for databases two, three, and four.
Like magic, we have four publications and four replicated tables on the Azure DB subscriber. (Don’t focus on my publication names as I was not consistent during the creation of number 2 😊😊😊)
To verify everything is working as expected, let’s open Replication Monitor and see that there are no glaring errors.
In replication, green is good, and these four publications are currently healthy.
New records will be replicated as advertised, and nothing is left to do. Unfortunately, the next day I received word from the end-users that records are not getting updated on the subscriber.
I start to troubleshoot the issue, and below is what I found.
Troubleshooting
I started by opening SSMS to do a quick query on each destination table to get the counts. We can see that two new records have been inserted into the WWI_4.Invoices table since enabling replication. The end-user states that they have only inserted new invoices into WWI_2.Invoices. We would expect WWI_2.Invoices to increase, but the WWI_4.Invoices are getting the new records.
As mentioned earlier, there are stored procedures used to move data around. Something I did not notice earlier in my build was that there were only three stored procedures created once I was done setting up all four databases. This should have signaled to me that something was not correct.
After finishing the setup for WWI_4, this is the code in the insert stored procedure. Notice how the schema is WWI_4 for this stored procedure. Keep in mind there are only three stored procedures listed.
What has happened is that even though I changed the destination schema during the configuration, SQL Server Management Studio did not make that same change to the stored procedure names being used. Everything is working when new records are being inserted. However, the last schema saved in the stored procedure is where the data is being inserted.
If we look back at our Article Properties screen, you will see another section that we should have scrolled down to update.
You can give them a different name for each stored procedure being created. In this example, I have added the WWI_1 schema to each stored procedure name. Then you can perform the same action on the other three publications and create new snapshots.
Once I refreshed my subscriber, voila, four different insert, update and delete stored procedures.
Now, if we look at the record counts flowing, we will see records inserted in the correct tables.
Conclusion
I hope this demonstration helps you identify and correct a similar situation if you have to setup replication this way.
