Transactional Replication Alter Table Best Practices, calling all replication experts
Posted on 2003-11-17
We have a database application that we distribute to all of our customers. We are using Red Gate to create the database script changes from one version to the next (and its worked great for years). Now, we are introducing transactional replication on this db and need some help from someone who has been there (preferably).
The problem is that the migration scripts utilize the ALTER TABLE statement which will NOT work on replicated tables. The tool doesn't know how to generate the correct statements (sp_msrepladdcolumn and sp_msrepldropcolumn). The data is being replicated to a db that contains triggers and stored procedures that transform the data and send it on to a reporting db. Dropping and recreating the publication seems like it will have two problems: one is too much time, the other is that the data will probably be duplicated (not on the subscriber side, but on the reporting db side due to the triggers and sps.)
What we need is a workable solution or the name of a tool that will create migration scripts for replicated databases. It seems like knowledge of replication here and elsewhere is fairly limited. Btw, if anyone has the name of a book that they recommend on replication, I would appreciate it. I looked on Amazon and Barnes and Noble and only found one book that is dedicated to replication and its basically a copy of BOL (SQL Server 2000 Reference Library - Iseminger).