July 19, 2010 at 10:18 am
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.
July 19, 2010 at 10:42 am
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
July 19, 2010 at 10:44 am
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
July 19, 2010 at 10:45 am
Thanks Roy, appreciate your response. That's the strategy we have planned and tested so i am glad that you have verified it.
July 19, 2010 at 10:51 am
Thanks Tara, i will try this strategy.
July 19, 2010 at 12:33 pm
Everything will have some downtime for connected sessions. Clustering, mirroring, logs, all would require a disconnect, reconnect for users.
I would lean towards mirroring as a way to minimize this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply