Options
What is the correct way to apply schema changes to a replicated database?
westcountrybusiness
Posts: 1 New member
in SQL Compare
In our company, we have a development SQL server where Schema changes are made. Once code is tested against these changes, it is deployed to 5 production sites, each with their own SQL Server.
Each server has two versions of the database (production and test). The production databases are all replicated to a remote SQL server using Transactional Replication. Reports are run against the replication server to offload work from the production servers.
When schema changes are made, we use SQL Compare to compare the Dev DB with a production Test DB to create a script for the changes needed. This script is then applied to all servers, against both Test and Production DBs.
We can not apply the script to the production DBs with they have replication enabled. To temporarily disable replication on each server to apply changes, we stop snapshot and log agents, amend the publication properties so only one table (not affected by changes) set to replicate, run the script, amend publication again to include all tables, mark snapshot for re-initialisation and re-start the agents.
This appears to have worked for some time and is quicker than completely disabling replication and then having to reconfigure it on each server each time. However recently, we have found a couple of missing updates on the replication server. It appears transactions have been lost as a result of the way we do this.
I've checked and can't find a 'best practice' for this. Is there a better way to temporarily disable replication to deploy schema changes? Or even a way to do it without having to disable replication at all? Or should we be doing this completely differently anyway?
Each server has two versions of the database (production and test). The production databases are all replicated to a remote SQL server using Transactional Replication. Reports are run against the replication server to offload work from the production servers.
When schema changes are made, we use SQL Compare to compare the Dev DB with a production Test DB to create a script for the changes needed. This script is then applied to all servers, against both Test and Production DBs.
We can not apply the script to the production DBs with they have replication enabled. To temporarily disable replication on each server to apply changes, we stop snapshot and log agents, amend the publication properties so only one table (not affected by changes) set to replicate, run the script, amend publication again to include all tables, mark snapshot for re-initialisation and re-start the agents.
This appears to have worked for some time and is quicker than completely disabling replication and then having to reconfigure it on each server each time. However recently, we have found a couple of missing updates on the replication server. It appears transactions have been lost as a result of the way we do this.
I've checked and can't find a 'best practice' for this. Is there a better way to temporarily disable replication to deploy schema changes? Or even a way to do it without having to disable replication at all? Or should we be doing this completely differently anyway?
Tagged:
Answers
For merge replication, we have 'Ignore Not For Replication' and 'Ignore Replication triggers' - which ignore the NOT FOR REPLICATION flag on constraints (which suppresses the checking of those constraints when rows are added by replication) and the triggers that are automatically added to tables on setting up merge replication, letting you compare databases with merge replication set up to databases that should be the same but don't have merge replication set up.