Using SQL Compare to deploy schema changes on replicated DB
Alby
Posts: 2
Hi,
Would like to start off by clarifying that this is not about using SQL compare as a replacement for schema replication or comparing replication differences, but a question on the approach of using SQL compare to deploy schema changes on a database that is under SQL Server 2005/2008 merge/transactional replication.
SQL server applies additional schema adjustments (triggers, columns, not for replication constraints etc, counted around 6 changes) to tables as part of the replication to keep track of data changes.
The replication specific schema adjustments are often not reflected in the source control scripts as the development database do not have a replication setup. Running a comparison against the production database would cause it to generate scripts to drop those adjustments.
Depending on the change, replication would require items to be dropped from the replicated articles and have snapshots retaken. This is not as ideal as it can be quite slow depending on the size. Changes are made on the publisher and replicated to subscribers.
Does anyone have a standard strategy/method of deploying Red Gate generated scripts on replicated databases that they could share?
Thanks,
Alby
Would like to start off by clarifying that this is not about using SQL compare as a replacement for schema replication or comparing replication differences, but a question on the approach of using SQL compare to deploy schema changes on a database that is under SQL Server 2005/2008 merge/transactional replication.
SQL server applies additional schema adjustments (triggers, columns, not for replication constraints etc, counted around 6 changes) to tables as part of the replication to keep track of data changes.
The replication specific schema adjustments are often not reflected in the source control scripts as the development database do not have a replication setup. Running a comparison against the production database would cause it to generate scripts to drop those adjustments.
Depending on the change, replication would require items to be dropped from the replicated articles and have snapshots retaken. This is not as ideal as it can be quite slow depending on the size. Changes are made on the publisher and replicated to subscribers.
Does anyone have a standard strategy/method of deploying Red Gate generated scripts on replicated databases that they could share?
Thanks,
Alby
Comments
There isn't a huge amount of support for replicated databases, but there are a few project options that might help.
Have you tried the ignore options:
'NOT FOR REPLICATION'
'Replicationtriggers'
I have added this post as another vote to support replication properly in a future version. The feature tracking code for this is SC-2437.
If you have more specific problems that these options don't address, then you might be able to manually edit the script to get round them, but other then filtering out the objects, there isn't anything built into the tool you could use.