What is the correct way to apply schema changes to a replicated database?

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?

  

Answers

  • Ben_PBen_P Posts: 233 Silver 2
    For transactional replication, there is not much for them I'm afraid. We have a few options to deal with the objects and options that merge replication leaves in databases, but the problems with transactional replication are mostly that we can't modify tables that we need to modify, and we don't know which tables these are in advance because we don't read the replication settings from the server.

    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.
Sign In or Register to comment.