December 17, 2012 at 5:25 am
Hello Masters,
If I will add any new table or database to existing configured Replication, is it needed any downtime ?
December 17, 2012 at 11:18 am
hi jitendra.padhiyar,
as so many times the answer is: "it depends"
If you want to add new databases you have to create a new publication. As far as I know it is not possible to put tables of different databases in the same publication. If the subscribers don't have these tables already there will be no downtime as you can copy the tables via the snapshot.
If you want to add tables to an existing publication it depends on the configuration of your publication. If you set the "immediate update"-option right (I always forget the "correct" setting so please search for it) you can add a table to the publication and only the new added table is transferred by the snapshot.
Again: if the table does not exist on the subscriber there is no downtime as this will not affect the replication. The only thing is: if the Snapshot is very very large and needs, just to say any value, 30 minutes, you have to wait the 30 minutes before other transactions will be replicated.
If the table(s) already exist on the subscriber you have to decide what happens with it when transferring the snapshot. You could truncate the data, drop the table or do nothing else but insert the data from the subscriber. This is a configuration you find on the article. Depending on what you want you will have some downtime or not.
This all is quite general but replicatio is a topic with lot's of possibilities and configuration options. Sill hope it helps a little bit.
Greetings, Wolf
December 18, 2012 at 8:39 pm
Like already noted, "it depends" however, you can pretty easily add/drop articles from replication (I am assuming you are referring to transactional replication) if the immediate sync option is set to zero. The biggest thing to be wary of is "if the publication was set up using the defaults", adding/dropping an article will cause a new snapshot for the entire publication which, depending on your environment could be a very bad thing. This is because the sync option defaults to 1 :crazy:
Please refer to this article on how you can check to see if you can easily add a new article:
http://www.sqlservercentral.com/Forums/Topic1388718-391-1.aspx#bm1388866
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply