7

I have a production server running SQL Server 2008 Web Edition, and it has to keep running 24x7 with almost no down time, and it's the time for us to move to a more advanced server hardware, also it's a good chance to move to SQL Server 2012 Enterprise.

Now my question, how can I do this migration without taking the database down at all? Like for example setting something between the two servers as replication or so, so I have exactly identical database up-to-second on both servers, then I can just take the main services down for 1 minute, changing the IP address of the database connection from old server to new server and I be sure that the new server has identical copy of the old one.

I can't go with backup/restore approach as it will take time more than acceptable period, that's why I need something which guarantee identical databases exactly as if I am doing backup/restore. 5 minutes could be the max affordable downtime.

The database size is about 100GB.

New server is running 2012 Enterprise, and old server is running 2008 Web

I can exceptionally archive records from the database and minimize its size down to 20GB like.

I have the option to upgrade my SQL Server 2008 Web edition to Enterprise edition if this will help with more solutions, like replication maybe?

Improve this question
1
  • @Abed you realize that upgrading from web to enterprise will incur a downtime and as per your question it has to keep running 24x7 with almost no down time ? If you go for enterprise, then I would suggest you go for mirroring (first async) and then during the switchover, switch the mirroring to SYNC. This will give you minimum downtime.
    – Kin Shah
    May 13, 2015 at 14:39

1 Answer 1

Reset to default
11

since you are using SQL Server 2008 Web edition, your best bet is to use LOG SHIPPING.

Your database size is 100GB, so initializing the secondary from a backup should be considered. Dont use GUI to set up initial logshipping.

SO your steps should be :

  • Backup 2008 Web edition database and restore it on 2012 enterprise WITH NORECOVERY.

  • Launch Logshipping wizard and make sure to select secondary is already initialized. Configure logshipping to be every 1 min(this is the minimum you can get).

    enter image description here

  • Move all the logins, Jobs, SSIS packages.

During the switch to new server :

  • stop all the webservers connecting to the database

  • stop all the backup, copy and restore jobs of logshipping.

  • take a final Tail log backup and restore it on secondary WITH RECOVERY.

  • Sync the logins.

  • Change the web.config of the application to point it to the new server. This way you dont have to change the IP address of the new server to match the old one.

I have given the post restore steps in more detailed manner here.

Improve this answer
2
  • the database has to move from Holland DC to Houston DC, quite long trip. the database is processing more than 5000 transaction per minute, all are billing and financials. a combination of thousands of update and insert commands per minute. I need something with zero tolerance, it's a disaster if the new server had any tiny issue with log file shipping.
    – Abed
    May 5, 2015 at 19:25
  • @Abed you dont have any other options and IMHO logshipping when configured correctly (hard to get it wrong !) and running for few days will ensure and give you a confidence. Test it on a test server with a smaller database and once you are confident implement and replay the same on PROD.
    – Kin Shah
    May 5, 2015 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.