1

I am trying to apply schema changes using T-SQL (using Ready-Roll database source control tool with Octopus Deployment) to a Publication database however it is forcing me stop Replication for the script to run. If it matters, I am running Transactional replication.

This challenges the very purpose of automating my deployment as well as it is very costly by requiring replication to reset.

Kenny Rasschaert
  • 9,045
  • 3
  • 42
  • 58
Daniel
  • 11
  • 1
  • 1
    Well... some schema changes are totally okay to do with replication (and they'll propagate to the subscribers) and some aren't. What are you trying to do and what error are you getting? – Ben Thul Mar 17 '13 at 15:04
  • A few other question: What version of SQL Server are you using? How is Ready-Roll making the change (TSQL? RMO?) – shiitake Mar 18 '13 at 14:45

1 Answers1

0

A Transact-SQL (TSQL) is a search and acquire tool. It can be used to change the schema of a database. It does so by replicating commands. If the TSQL is not working, then another step may be required. You may have to restart the process by taking a different a approach.

If you are being forced to stop replication while in process of changing the schema of a publication database, you must make one important consideration. Does the database have a security tag that automatically freezes any replication process. If it does, then you must take the necessary steps in discovering the unique database qualities. That will enable you to discern any security tags.

One other option to consider is that of database variety. This option comes with a question; is this the only publication database on which the TSQL tool will not work? This question opens a variety of new possibilities and avenues of solving the problem. First, if you have stumbled across a database that gives you the "stop replication" message, then the problem is not the TSQL but an issue in that database.

If the "stop replication" message occurs on any publication database, then you have to consider the steps in your process. TSQL is designed as replicator, and it should change the schema of a database. However, there are particular steps that must be taken.

Be sure to consider the distribution parameters. Does the TSQL match those parameters. What is the installation value? Does it have to be at zero in order for your replication process to have the proper platform for operation? Does your transaction process take to long? Some publication databases have a maximum transactional retention period. And, finally, do your TSQL script files follow a logical sequence in performing the designated tasks? One last quick consideration. Transact-SQL script makers should consider whether or not the subject database script was configured manually.

matt smith
  • 21
  • 2