October 27, 2013 at 3:29 pm
Hi
We are using transactional replication. If we need to add new columns to a table that is involved in replication, will the new columns get replicated or do we need to create a new initial snapshot of the publication?
October 28, 2013 at 2:24 am
PearlJammer1 (10/27/2013)
will the new columns get replicated?
No
PearlJammer1 (10/27/2013)
do we need to create a new initial snapshot of the publication?
Yes. Transactional replication only replicate DML operation. see http://technet.microsoft.com/en-us/library/ms151706(v=sql.105).aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 28, 2013 at 2:52 am
You would need to add the new column to the publication articles and reinitialize the subscriptions
If it was Log Shipping then everything would go across. Even events like adding more datafiles 😉
Regards
October 31, 2013 at 4:55 am
hI everyone thanks for the replies. I'm still a little unsure about some of your replies as I have added new columns on our dev environment and the replication picks it up without any issues. I wouldn't have expected that and would have thought your answers where more apropiate - anyway it works so that's the main thing. I'll try and figure out 'how come' though !:-)
October 31, 2013 at 6:06 am
Yes, and that's because your replication was setup to Replicate schema changes !
Just take a look to the properties of the replicated table and under Subscription Options you will be able to see that.
October 31, 2013 at 9:02 am
r.dragoi-1095738 (10/31/2013)
Yes, and that's because your replication was setup to Replicate schema changes !Just take a look to the properties of the replicated table and under Subscription Options you will be able to see that.
r.dragoi-1095738 is right.
With replicate schema changes enabled, adding new columns will replicate.
November 1, 2013 at 3:50 am
Yep I have checked what you have said and sure enough it is enabled - all makes sense now. This must be the default setting then as i do not recall adding this setting.
So the initial answers to this post about recreating the snapshot where incorrect then ? - but that begs the question 'when do you have to do a 'fresh' snapshot if changes are picked up automatically anyway ?
November 1, 2013 at 5:36 am
Normally after you add a new subscriber which needs initialising or you add additional articles to the publication. In the second case, only the new articles will be in the snapshot.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply