Minimize migration downtime on large DB

Question

Minimize migration downtime on large DB

Y.B.

SSChampion

Points: 11642
More actions
April 20, 2015 at 9:52 am

#299924

Hi Folks,
I'm preparing a checklist for myself before getting ready to migrate from 2005 to 2012. Our largest database is a nice one at over 250GB. As you can guess it's also our most active and mission critical database. I'm thinking my best bet to minimize any downtime would be to Restore the DB (NORECOVERY) on the new server and keep rolling it forward with the transactional logs. Eventually I'll need to bring the old DB offline and do one last backup and apply that one to the new server but that should be a small time frame given the whole process could take several hours.
Just looking for any feedback/advice/gotcha's from people might have gone through this process.
Thanks,
SELECT quote FROM brain WHERE original = 1
0 rows returned

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

yb751 (4/20/2015)
I'm thinking my best bet to minimize any downtime would be to Restore the DB (NORECOVERY) on the new server and keep rolling it forward with the transactional logs.

That should work very well.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

udaynov17 SSC Eights! Points: 832 More actions · Answer 2

How about setting up log shipping and run the scripts for the backup and restore of the last T-Log in the time of cut-over.

Y.B. SSChampion Points: 11642 More actions · Answer 3

udaynov17 (4/20/2015)
How about setting up log shipping and run the scripts for the backup and restore of the last T-Log in the time of cut-over.

This would probably work also but I'm not sure it's something I'd like to setup for a one time use.

SELECT quote FROM brain WHERE original = 1
0 rows returned

sql-lover SSCoach Points: 18530 More actions · Answer 4

I used DBMirroring (SQL2005 and SQL2008), and it works nice! Migrated 500+ GB databases that way in seconds (assuming you have decent network and both are in the same LAN)

You can also restore with no recovery, as mentioned, then the day of the migration take most recent Tlog backup and restore on the target.

Tip

Check for orphaned SIDs. You need to reset those if you moved to a different SQL instance and you use SQL logins.

Y.B. SSChampion Points: 11642 More actions · Answer 5

sql-lover (4/20/2015)
Tip
Check for orphaned SIDs. You need to reset those if you moved to a different SQL instance and you use SQL logins.

You're right about that one...I've been stung by this one before. :pinch:

SELECT quote FROM brain WHERE original = 1
0 rows returned