May 26, 2011 at 4:37 am
I am using transactional replication on SQL2008 of a large database 350GB.
I have 'replicate schema changes' turned off.
I would like to add a column to a table on the publisher and have this replicated across to the subscriber, i.e. add the column [Publisher].[tableA].[colum1] and have this replicated giveing [Subscriber].[tableA].[column1]
I have attempted to make the changes in SSMS. I opened the and got nervous when I saw the alert box with the message similar to "This requires the reinitialization of the subscribers".
I have read the article on msdn and planned to follow the procedures for "To change a column filter to include additional columns for an article published in a snapshot or transactional publication". Again I became a little unstuck at point 4 "Reinitialize subscriptions".
As I am adding 1 column to 1 table, what does a reinitialization involve?
Is the entire subscription going to be reinitialized or just the 1 article that has changes?
What does a reinitialization involve? e.g. A complete database being moved across to the subscriber.
I will continue to make changes to the schema as the applications that use it evolve. If, as I suspect, it will take quite a long time to reinitialize, what is the best way to make the schema changes to a database while reducing the pain of replication.
May 26, 2011 at 5:15 am
if you use the soon to be deprecated sp_repladdcolumn proc it will add the column and propogate this to all subscribers without the need to reinitialise or re-snapshot the table.
I suggest you add the column as NULL so you dont have any issues or locking. Adding a nullable is a metadata change and will cause minimal contention.
The only alternative is to sp_changepublication to enable replicate ddl and add the column using ALTER TABLE. This would essentially do the same thing but will be supported in future versions of SQL.
At no point will you need to reinitialise using either of these methods.
May 26, 2011 at 5:16 am
platzkarl (5/26/2011)
I have attempted to make the changes in SSMS. I opened the and got nervous when I saw the alert box with the message similar to "This requires the reinitialization of the subscribers".
This just shows how unreliable SSMS is when it comes to replication
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply