May 26, 2011 at 6:08 pm
I am running a SQL 2008 DB and using transactional replication. The DB is kind of large, i.e. 350GB.
Schema replciation is turned off.
I would like to add a column to the replication process with minimal fuss and want to avoid any downtime/performance hit. I have tried to do this with SSMS but this forces me to do a reinitialization. On a 350GB DB I think this will cause some downtime.
What is the best way to add the column?
May 27, 2011 at 1:40 pm
If the publication was set up with the native sync_method, you should be able to drop the article, add the column and then add the article back to the publication. If you do that and run the snapshot agent, only the new article should be part of the snapshot. If you add an article to a publication that uses the concurrent sync_method, the snapshot agent will run a snapshot for the entire publication, not just the new article. The native setting locks tables when the snapshot runs, but that's usually prefereable later on when you want to make changes to articles. I recently encountered this and ended up adding the article to a different publication that was set to use native to avoid generating a full snapshot. I then had to add subscriptions to that article.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply