Moving SQL merge replication database

I have two SQL servers running a merge replication. The data is around 38GB in size and both sites are a high distance apart. The latency between the two sites is high. Both the publisher and subscriber are running SQL 2005. I am looking to upgrade the publisher to SQL 2012 and then a couple of months later I will upgrade the subscriber to SQL 2012. I am trying to avoid having to re-initialise the database. What is the best process for this?

I have setup a lab with two SQL 2005 servers with merge replication. I have then backed up the SQL on the publisher(ServerA) and shut the server down. I have then created a new server with the same name(ServerA) and enabled the distribution database. I then restore the publisher database with the 'With Keep_replication' flag. I can see the local publications and subscription created. But it is complaining that the publication does not exist in the distribution database. The replication will not run. Am I missing a step? Is there something else I need to restore from the SQL 2005 server.

I need to avoid having to re-initialise the merge replication.
AnthonyWaldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
However I never done Replication upgrade this way....... But is guess

You have to restore Distribution database and MSDB database ( this is for agents job and metadata) as well.
0
AnthonyWaldAuthor Commented:
Do you have a suggestion on a better way to upgrade my replication setup?

The thing I am worried about is restoring the distribution and MSDB from a SQL 2005 into a SQL 2012. I am worried that this will cause issues as it is changing SQL versions.
0
Deepak ChauhanSQL Server DBACommented:
From microsoft:   You are in testing phase so you can test it by changing the compatblity level.

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).

A Publisher can be any version as long as it less than or equal to the Distributor version.

A Subscriber to a merge publication can be any version less than or equal to the Publisher version.

https://technet.microsoft.com/en-us/library/ms143699(v=sql.110).aspx

Other options:-

You can perform inplace upgrade but it would require downtime. All database will be upgrade to same version after upgrade.

Other way you already know.
Resetup merge replication.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AnthonyWaldAuthor Commented:
Thanks for your help. Sorry I have not got back to you sooner. I will play around with compatibility levels first and see how it goes. I was also thinking about the inplace upgrade path which you have mentioned.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.