Moving high transactional DB to a new server

Question

Post reply

Moving high transactional DB to a new server

DBABC

SSCarpal Tunnel

Points: 4813
More actions
July 19, 2010 at 10:18 am

#237799

Hello fellas,

We have a very high transactional DB that we want to move to a new DB server with ABSOLUTELY zero data loss and as minimal down time as possible.

I should add that Dettach/Attach is not an option for us.

Your ideas, strategies will be highly appreciated.

Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Set up log shipping to the new server. Once every five minutes would be better since when you have to cut over, you dont have a big transaction log. Make sure your logins are ready, transfer all jobs using SSIS.

At the time of cut over, stop all traffic to the old server, take the last transaction log and apply it to the new server. If you have a bunch web servers and application servers, then it will be easier to rename your new server to the old server name and give the new server the IP from the old server. make sure you run sp_dropserver and sp_addserver to give the SQL instance the same name as that of the old Server. And then you are good to go. When we migrated from 2000 to 2008 this is what we used and our down time was 30 min.

Please test it and double test it. Make scripts ready before the day of switching over, test it and double test it again.

-Roy

Tara Kizer SSCommitted Points: 1789 More actions · Answer 2

We use database mirroring for such a scenario. You can prep the mirroring a few days in advance, even do it async to avoid the performance hit. And then when you are ready to take the downtime, switch it to sync and fail it over. It should take just a few seconds.

We can switch servers in just a few seconds due to mirroring. We do this regularly actually (I support hundreds of databases).

If you also intend to switch the server name over, you can do that after the failover. We use clustering, so when we switch the name, it is very quick. A standalone server would take longer though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog