question

bondsoft avatar image
bondsoft asked

Merge replication Plan from SQL 2000 to SQL 2012

Greetings All, I have this system that in working on an SQL Server 2000 merge replication environment, having one publisher and 20+ subscribers, all using same version of SQL 2000 SP4. However, in few months from now, and due to the client running the system will be purchasing new servers, it will most probably be furnished with Windows Server 2013 and SQL server 2012. therefore I am required to come up with a plan to handle such transition bearing in mind that it is highly not possible to do the transition in one day due to the number of branches. I know what needs to be done logically, but i would like you to share your thoughts and knowledge if any of you have experienced similar scenario. Thanks in advance.
sql-server-2012sql-server-2000migrationmerge-replication
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This web site runs from voting. For any helpful answer, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
You can't really go from 2000 to 2012 directly, so you have to upgrade to an interim step, 2008 is a good choice, then on to 2012. [Here is an article from Microsoft][1] that relates some of the issues you're likely to hit. [1]: http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
This is quite a project. It's complicated by the fact that you can't upgrade directly to 2012 from 2000, it requires an intermediate upgrade to 2008. Which all sounds very complicated to me. I wouldn't be sure how your 2000 code would run on a 2012 server. It's probably going to be fairly sub-optimal I'd expect. So rather than treat this as a port, If I was in your shoes, I'd be tempted to start with a clean 2012 setup. This would include a full code / object / data review step to ensure that you only port what is worthwhile and that you refactor anything that needs it. Then run a data migration from your old db.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bondsoft avatar image
bondsoft answered
Thanks (iainrobertson), it is quite a project, I've already converted my code to be compatible on SQL 2012, and i've tested all functionalists and everything seems in order. Now my headache is that, how and on what time scale should i implement this transition steps, bearing in mind that non of the subscribers can be offline during the transition, which means i'll have a time where my entire system can have three different versions of SQL's 2000, 2008, and 2012. and Thanks Grant Fritchey for your reply.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
> which means i'll have a time where my entire system can have three different versions of SQL's 2000, 2008, and 2012 Bad idea. Really, really bad. This would be impossible to manage. I can't see how you can manage this transition without a blackout period. You have to get to a fixed data state at both your publisher and each subscriber in order to run the transition. What is driving this decision?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bondsoft avatar image
bondsoft answered
This system is for a dairy industry, and they have branches (subscribers) at almost each state of the country, sales are done mostly during day time, while night time the subscribes replicate their data to the HQ (publisher). the entire system has 20+ subscribers, and the only free day might be Sunday. The actual motive behind this step is that, the company servers are running old, and in not more that 6 months from now, they will purchasing new servers which comes with the latest OS, and SQL Server. I have a plan B which is using a VM on each newly purchased server.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
By not closing down you run a very high level of risk. A fundamental part of your upgrade process has to be to test and verify that your database upgrade has been successful. Simply relying on the upgrade advisor's recommendations and crossed fingers is not advisable. If you do run into problems, rolling back a replicated database after subscribers have replicated is a nightmare (one I've had to deal with, it wasn't pretty and it certainly wasn't fun). In addition, you need to make sure (in a controlled way) that all of your subscriptions can be reconnected. From what you've said, I'm assuming that you have no plans at present to upgrade the subscriber databases? Replicating a 2012 publisher to 2000 subscribers may well have unintended consequences. If you can, it may be advisable to upgrade your subscribers too. Before you even begin to touch your live system you should have a test environment set up that replicates this new structure and you should extensively test that a) you can upgrade in place without loss of connection to subscribers or loss of functionality and b) that post upgrade, data flows are correct and efficient, using loads that are representative of your live environment. At a bare minimum. This isn't a given. Testing is not an activity that you should be responsible for in isolation - the business needs to be fundamentally involved. This means lining up testers, developing test plans and scripts, understanding what your success/fail criteria are, who will determine the outcome and which of your senior business representatives will sign it off as completed. Don't end up carrying the can on your own. Once you are confident that your upgrade process is working, you may be able to squeeze your blackout window down in size. But I would still recommend blacking out. In your circumstances I would do the following: - Implement a data freeze at the subscribers. - Synchronize all subscribers. - Disable all merge agents. - Back up the database. Keep this somewhere **very** safe. - Do your in place upgrade. - Do system testing on the upgraded database. - Start to try to bring a single subscriber back online once system testing is signed off. - Once this subscription is verified as correctly reinstated and replicating properly, start to bring the rest back, **one by one**. - Once you've done them all (and only then), release the freeze. This is a major upgrade. It's far better to negotiate downtime with users before its needed than to have to explain why nothing is available when they expect it.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.