Upgrading Environment from 2005 to 2008-R2 and the old env has merge replication running.

Question

I have an environment that is running 2005. One publisher and two subscribers with merge push replication. I have a new environment identical but 2008-R2. I would like to get the two environments in sync so that all I have to do is flip a switch to move users to the new environment.

I wanted to run merge replication from the old publisher to the new but it can't be done from 2005 to 2008. Then I set up a new publisher (transactional) on the old publishing server and a pull subscriber on the new and that seems to be working. Now my problem is that the publisher on the new server managing the merge replication out to the two new subscribers doesn't seem to recognize the updates coming in via the transactional replication.

Does this make sense? Does anyone have any suggestions.

Answer 1

Why can't you log ship? This way when you want to cut over all you do is stop log shipping and then do a

restore database DatabaseName with recovery

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Not sure how that affects my replication. Do I restore just at the publisher and will it then update the subscribers or do I have to recover each of the subscribers also. It takes over 6 hrs to sync these databases. Also we have a few tables at that are not replicated. By know I guess you can tell I have not used log shipping before.

I do appreciate the assistance.

---

David Young

Answer 3

Bad answer on my part. I think you need to set up your merge publication articles with the setting published_in_tran_pub set to true.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Thanks I'll give it a try as soon as I can find the darn syntax.

---

David Young

Answer 5

Sorry but I have another question. I am the only excuse they have for a DBA and am self taught over the last year.

In using sp_changemergearticle I need to change all articles in the publication (400+) is there a way to do this with a single command? Setting the @article to do all? Then I guess I'll have to force_reinit_subscription and I am assuming that will do both subscriptions.

---

David Young

Answer 6

Unfortunately you will need to do it via a script one article at a time.

Before you do this in test, you should try it in dev to ensure it works. I'll try it as soon as I can. I am pretty sure this works.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941