Sql-server – Move live SQL Server database to a new server

migrationreplicationsql serversql-server-2008sql-server-2012

I have a production server running SQL Server 2008 Web Edition, and it has to keep running 24×7 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?

Best Answer

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.

Related Question