Troubleshoot SQL P2P replication doesn't replicate DDL schema change.

,

We have two SQL Server clusters (ServerA and ServerB). Both are installed with SQL Server 2008 R2 (Enterprise). The two SQL instances have an identical database called “MyDatabaseA”, and Peer-to-Peer replication is configured between those two SQL instances, for some of tables within “MyDatabaseA”. This allows records to be replicated from one server to the other, and vice versa. This cofiguration has been working properly for quite awhile.

Due to new application development, we needed to add extra columns on one of the replicated tables (For example: ALTER TABLE MyDatabaseA.dbo.MyTable ADD NewColumn int). Because of Peer-to-Peer replication, we are supposed to only initiate this and add the new columns on only one of SQL instances. The schema change should then be replicated to the other SQL Server instance automatically. This should be very straight-forward, and we have done it many times in the past without any problem. However, for some reason, it didn’t work as expected this time. Here was the situation:

If we added the new columns for table “MyDatabaseA.dbo.MyTable” on ServerA, the new columns CANNOT be replicated to the “MyDatabaseA.dbo.MyTable” on ServerB. However, if we added the new columns for the same “MyDatabaseA.dbo.MyTable” on ServerB, the new columns CAN be replicated to ServerA’s “MyDatabaseA.dbo.MyTable” successfully. By the way, data can be replicated from both sides without any problem, and there were no errors in both replication monitor and SQL Server error logs. The problem seemed to be only with the schema replication.

We had no idea what the problem it was. Accordingly to Microsoft Book Online, Peer-to-Peer replication must be able to replicate schema changes on both sides, and that can’t be disabled. Just to make sure, we then double checked the publication properties, and the Replicate Schema Changes setting is set to “True” on both SQL Server publications, which is the required setting for Peer-to-Peer replication (shown in Figure 1 below).

Firgure 1

Because this is a non-production environment, we first tried the easy way; we dropped and recreated the Peer-to-Peer publication and subscription for “MyTable”, hoping the issue could be resolved quickly, but it wasn’t.

Is it possible there was something wrong with that specific “MyTable”? We tried to add other columns on another table within the same database, but the result was still same as that of “MyTable”. That is the schema changes COULD NOT be replicated to ServerB if we initiated the changes on ServerA, but COULD replicate successfully to ServerA if we initiated the same changes on ServerB.

This is weird. Why do the Peer-to-Peer schema change only work in one direction? We decided to have another try on a table within a different database, in this case “MyTableB” within “MyDatabaseB”. Replication worked. The schema changes were replicated from both ServerA to ServerB and from Server B to ServerA.

So, we concluded there must be something wrong with “MyDatabaseA”. We then tried to compare all the database settings on MyDatabaseA between ServerA and ServerB, but saw no differences. The settings were all the same. We were kind of stuck. We also tried to google for similar issues on the internet, but had no luck.

Suddenly, some vague idea came into my mind: Is it possible there are some triggers under Server A’s MyDatabaseA that are preventing or rolling back the schema changes? Although this sounds weird, I decided to compare all the triggers under MyDatabaseA between ServerA and ServerB. We found there are 4 triggers created under MyDatabaseA on both SQL instances. These were:

  • tr_MStran_alterschemaonly
  • tr_MStran_altertable
  • tr_MStran_altertrigger
  • tr_MStran_alterview.

What are those triggers? From the names, they look like they are related to SQL Server replication. We opened one of them and , yes, the trigger is created by SQL Server for replication, and the triggers are all the same between ServerA and ServerB. However, we found the triggers on ServerA were all DISABLED, while on ServerB they were all ENABLED.

Could that be the reason? We enabled those 4 triggers on ServerA, and the schema replication started to work immediately from ServerA to ServerB. We then did some research on those triggers, and concluded they are indeed required for SQL Server replication. However, although we solved the schema replication issue on ServerA, we don’t know why those triggers were disabled. Anyway, the issue is resolved for now. The triggers are shown in Figure 2 below.

Figure 2

Rate

4.7 (10)

Share

Share

Rate

4.7 (10)