November 5, 2019 at 8:31 am
Background:
I currently have 1 publisher with 200 articles published (SQL 2012) to 1 subscriber (SQL 2008).
I have a new server SQL 2016 which I wish to set up a replacement subscriber and retire the old 2008 server.
Is this easier enough to have these 2 subscribers running in parallel without regenerating the entire snap shot so i can limit down time on the current subscriber before "failing" over?
thanks
November 5, 2019 at 8:56 am
unfortunately transactional replication is "distributor led" - you will need to move distribution to the 2016 instance (highest version wins) - which means you will need to rebuild the publications, subscriptions and re-snapshot everything
MVDBA
November 5, 2019 at 10:12 am
Not what I wanted to hear but appreciate the response 🙂
November 5, 2019 at 10:21 am
Not what I wanted to hear but appreciate the response 🙂
I feel for you
you can script out your publications and subscriptions nice and easily
depending on whether you can knock distribution offline (but keep the database up) , you could maybe keep the downtime to a minimum. I'm going to be honest, you really need to plan this out thoroughly.. accounts, replication agents, firewall rules.... etc etc
MVDBA
November 5, 2019 at 10:43 am
Yep will test some scripts out now against a copy of the DB make sure its all good regarding accounts and access etc.
Will probably keep the old DB up and running just with no new data coming through.
November 5, 2019 at 3:53 pm
Is it possible to have the distributor on separate server from the publisher? Seems you can but more truicky to set up.
Looking at this replication matrix its seems okay to have Publisher and Distributor on same 2012 box with a 2016 subscriber?
November 5, 2019 at 4:06 pm
yes, but your distributor always has to be the highest sql version compared to publisher and subscriber
MVDBA
November 5, 2019 at 4:12 pm
yes, but your distributor always has to be the highest sql version compared to publisher and subscriber
Just edited my post, seems we can we have 2012 distributor on that matrix?
A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher).
November 5, 2019 at 4:31 pm
my bad... it's only when you create the publication and go to choose the distributor that it checks - but would advise (if possible) to go as high as you can on the distributor.... you never know when some bright spark will give you a sql 2019 instance and you need to replicate 🙂
MVDBA
November 5, 2019 at 4:58 pm
Background:
I currently have 1 publisher with 100 articles published (SQL 2012) to 1 subscriber (SQL 2008).
I have a new server SQL 2016 which I wish to set up a replacement subscriber and retire the old 2008 server.
Is this easier enough to have these 2 subscribers running in parallel without regenerating the entire snap shot so i can limit down time on the current subscriber before "failing" over?
thanks
You can have the 2016 subscriber with a 2012 distributor, publisher as you already figured out. In terms of the second part of your question, you can setup the two subscribers that way but will still need to initialize the added 2016 subscriber. You can initialize it with a backup if you are trying to avoid generating a snapshot.
Sue
November 6, 2019 at 7:59 am
Thanks for the comments, will try set this up with 2 subscribers and intialise one from backup before attempting in production. Will let you know how i get on.
November 18, 2019 at 1:51 pm
When you initiate sub from backup it keeps all other tables in publisher DB even ones that are not replicated. e.g 10 tables replicated 100 tables in DB all 100 will be present in subscriber. Can yo just delete these?
November 18, 2019 at 2:18 pm
I've never used initialise from backup, purely on the grounds that transactional replication allows you to pick 3 or 4 tables from 100.. then choose 5 fields from each table - I can choose not to take indexes or defaults
here's a question... if it's downtime during failover would log shipping be better? - all you have to do is copy, restore the last log and bring online
really depends on your allowed data loss and how you plan to fail back
MVDBA
November 18, 2019 at 2:24 pm
I tested and it works you can delete the objects not used in replicated DB
Now having issues with trying to test this in production just gets suspended! I wonder if its because i already have an existing Subscriber it doesn't like me tampering with the publication
EXEC sp_changepublication @publication = @publication, @property = N'allow_initialize_from_backup', @value = 'true' suspended
Causing blocking
Anyone seen this before i didnt expect in live that updating the publisher setting to wreak havoc! Keep having to kill the process.
November 18, 2019 at 3:20 pm
I'm not using this for DR, i have logshipping in place for that already. The replication is for reporting
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply