August 18, 2017 at 3:15 am
Hi all
We have a 3rd-party software vendor who puts data into some SQL tables.
We want to replicate the databases across to various other servers but have a potential issue.
The vendor has said that replication will need to be switched off for certain tables while they make various changes (please, don't ask!!).
My question is this, if we switch of replication for tables x,y and z (out of the full alphabet) and let the rest continue replicating, will we need to completely reinitialise the entire replication or can we just switch on replication for tables x,y and z?
If we can just start replication for those few tables (using the same publisher/distributor settings so everything is in one place), how would we do that?
I'm not sure how to start replication for just a few tables if replication for others is already running.
Any advice/help on this would be greatly appreciated.
August 18, 2017 at 7:30 am
richardmgreen1 - Friday, August 18, 2017 3:15 AMHi allWe have a 3rd-party software vendor who puts data into some SQL tables.
We want to replicate the databases across to various other servers but have a potential issue.The vendor has said that replication will need to be switched off for certain tables while they make various changes (please, don't ask!!).
My question is this, if we switch of replication for tables x,y and z (out of the full alphabet) and let the rest continue replicating, will we need to completely reinitialise the entire replication or can we just switch on replication for tables x,y and z?
If we can just start replication for those few tables (using the same publisher/distributor settings so everything is in one place), how would we do that?
I'm not sure how to start replication for just a few tables if replication for others is already running.Any advice/help on this would be greatly appreciated.
Create two publications - one publication would be tables x,y,z, the ones you need to turn off. The other publication would be tables a - w, the ones you can leave on. You can just stop and start the replication agents for the publication.
I would guess based on what the vendor is saying that you would need to reinitialize the publication with x,y,z. And then again, he may not know why replication needs to be turned off and is just saying that for whatever reason. You'd want to find out what those changes are that they are doing. In any case, if reinitializing is workable based on the size of the publication, then just reinitialize the x,y,z publication and start the agents.
Sue
August 18, 2017 at 11:55 pm
Unfortunately it's not quite that simple.
The vendor might change tables x, y and z this time around but it could be tables a, b and c next time.
I don't relish the thought of creating a publication per table on the system.
August 21, 2017 at 4:37 am
The question is do you really need to stop/drop replication for these specific changes that vendor will be doing? The only thing (that I know of) that can't be done to the table that is published is PK modification as PK is used by transactional replication. Other changes like column modification, addition etc. can be made to tables that are published. You can also adjust settings on articles if you want these changes to be applied to your subscribers.
If you still want to stop replicating some of the tables, you can do it and u wont need to reinitialize all other tables/articles. We did that during PK modifications. However this includes generating snapshot for this modified tables and all articles in your publication should have sync_Type =automatic (sync_Type column in syssubscription on publisher should be 1)
1. Drop articles from publication that will be modified by your vendor (you need to drop given article from active subscriptions first if u using T-sql - sp_dropsubscription and then sp_Droparticle)
2. Vendor will modify the tables
3. add back tables into publications (with setting that on destination tables will be recreated)
4. refresh subscriptions (sp_refreshsubscriptions)
5. Run snapshot agent (it will generate new snapshot only for table newly added)
6. Tables will be delivered to your subscribers.
You can add articles without fully syncing it with subscribers (no snapshot will be generated). However there could be a problem with missing data (no data will be flowing for given tables between step 1 and 3) and also schema differences between publisher and subscriber (as Im assuming vendor will be changing schema of the tables). Going this route it means that you running sp_Addarticle first and then sp_Addsubscriptions with @sync_type='replication support only' instead of refreshing subscriptions.
BartL
Replication Blog
August 29, 2017 at 4:24 am
From what we've been told by the vendor, we need to stop replication when they make a change or the change will fail (they're using home-grown transfer and change methodologies and not ODBC).
I'm not sure if they use sp_rename to change table/column names but I doubt it.
From what they've said, if their change routine looks to see if replication is in place and, if it is, it just stops.
August 31, 2017 at 3:44 am
Then I think the only option will be dropping articles from replication and adding these back after the change and sending snapshot down again (just tables that been dropped). However it may be worth chatting to your vendor that most of the changes done to replicated tables will be fine and they could remove that check for replication that stops the change. The only real problem is an alteration on PK..
BartL
Replication Blog
August 31, 2017 at 4:01 am
Thanks for that.
Looks like we'll be switching off replication, allowing the vendor to make changes and then reinstating replication.
That will add around 4-5 hours downtime to any changes which will then need careful handling.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply