December 20, 2022 at 7:17 pm
Hello all,
I am trying to configure a merge replication between servers and getting an error when starting setting up the subscriber.
The publication and distributor are set up but when configuring the subscription on another server I get this error after connecting and browsing the publication.
SQL Server could not retrieve information regarding the publication 'xxxxxx'. (New Subscription Wizard)Invalid object name 'sysservers'. (.Net SqlClient Data Provider)
Any ideas on if I've omitted something somewhere?
Thanks,JR
December 20, 2022 at 7:41 pm
Just a note if not clear as i do get past the login screen up to this point when trying to browse the publications
December 21, 2022 at 8:53 am
This is not possible, replication only works where the publisher and subscriber are within specific versions of each other.
Merge replication, where the publisher is SQL 2008 / SQL 2008 R2 can only replicate to a SQL 2008 / SQL 2008 R2 subscriber.
The full compatibility matrix is found here
Merge replication allows for equal to or lower versions than that the publisher. (Downgrades)
Transactional / snapshot replication allows for upgrading within 2 specific versions of publisher & subscribe
If your goal is to migrate from 2008R2 to 2019, you are going to have to do a number of jumps also, as 2019 doesn't support a direct upgrade from 2008/2008R2.
You would first need to move to SQL 2012, then to move to 2019.
Below is the supported upgrade paths to SQL 2019
That is if you did want to set up merge replication from your "upgraded 2008r2" instance to AWS 2019, otherwise
You could use logshipping or backup/restore to also move from 2008R2 to 2019, but if the DB compatibility level is less than 100, the upgrade process will automatically upgrade it to 100, so if your needing to maintain compatibility level of 90/80 etc, then SQL 2019 isn't going to be a supported upgrade path either.
What is the overall end goal here?
December 21, 2022 at 1:32 pm
Thank you for the tips and it's the first time we try something like this.
The main goal with this setup would be temporary just to keep both versions of databases in sync before moving the lower version to the cloud. Therefore you are saying that a merge replication would not work even if upgrading to 2012 as a jump before 2019 .
So actually if I create the merge replication from 2019 and the publication / distributor would be 2019 and the subscriber 2008 that may work correct ?
December 21, 2022 at 1:42 pm
So for that situation I would be looking at use logshipping, it is backup and restore which will suffice the needs and is a supported upgrade path of 2008R2 to 2019.
The only way you are going to get replication to work is
Transactional replicate from 2008R2 to 2014, then replicate it again from 2014 to 2017, then replicate it again from 2017 to 2019.
Obviously this means each table needs a primary key and you need a lot of infrastructure in place to do this as you need to jump via 2 intermediary versions before you can reach 2019.
2. Upgrade your 2008 to 2012, then upgrade it to 2019.
Once your on-premise is 2019, you can then merge replicate it to AWS 2019
December 21, 2022 at 1:56 pm
Thanks for that !
Although both solutions don't seem possible in our environment are on at the moment
I will try the backward merge solution and see if that works where the distributor would be 2019 and subscriber 2008
December 21, 2022 at 2:17 pm
OK so for that you would need 2 machines in AWS.
One on 2019 for the distributor, one of 2008R2 for the subscriber.
SQL2008Rs Publisher -> SQL2019 Distributor -> SQL2008R2 Subscriber
If you would probably be better just creating a 2008R2 machine in AWS then doing a sideways migration and saving the cost on another machine.
SQL2008Rs Publisher -> SQL2008R2 Distributor & Subscriber
or
SQL2008Rs Publisher & Distributor -> SQL2008R2 Subscriber
Then when you are moved into AWS you can look at upgrades to 2019 via one of the alternative methods instead
In-Place upgrade to 2012, then again to 2019
or build a new 2019 machine and backup restore the DB from AWS2008R2 to AWS2019.
December 21, 2022 at 3:18 pm
If the only way is from AWS for a merge replication "One on 2019 for the distributor, one of 2008R2 for the subscriber."
Then we would have to look at other solutions 🙁
December 21, 2022 at 3:44 pm
Yes you will need a different solution.
Your options are
1 - Logshiping 2008R2 to 2019
2 - upgrade your 2008R2 to 2012/2014/2016/2017, then again to 2019, then use merge replication to merge On-Premise 2019 to AWS 2019 (you must to two in place upgrades as 2008R2 cannot directly upgrade to 2019 in place), (or build new 2019 on premise and backup restore the DB to new On-Premise 2019 then merge replicate to AWS)
3 - upgrade your 2008R2 to 2016/2017 (in place or side by side), then use transactional / snapshot replication to replicate to AWS 2019, this is assuming the schema allows for Transactional replication to work correctly
4 - write your own synchronisation routines in something like SSIS to do the work for you
5 - In AWS Build SQL 2008R2 and merge replicate, then later look at upgrades to 2019
But for all simplistic routes here, logshipping and backup restore methods manually would be my way of doing this for a migration to the cloud given the version changes and potential for risk with all the in place upgrades
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply