September 27, 2015 at 7:32 pm
Hi All,
I would like to migrate around 15 databases in production server to the new production server. The biggest database is 80 GB .
Wondering is there any fastest way to do that with very low downtime ?
What I can think about is shrink databases files and perform attach – detach .
No idea how long it takes thought
Any feedback are really appreciated
cheers
September 28, 2015 at 12:37 am
Copy time is usually the, relatively, slow part for us so we don't detach / copy / re-attach
Assuming database is in Full Recovery Model then we take Full backup (or use existing one), copy to new server, restore using NORECOVERY.
Repeat with a DIFF backup taken shortly before the cut-over time. Preceding the DIFF backup with a Log Backup may reduce the size of the DIFF.
Then we take a Log backup and restore that (and any Preceding Log backups which have been made by scheduled backup task since the DIFF backup)
Finally we take the source database offline(**), take a final log backup, restore that onto the new server using RECOVERY option and put the new server live. We've done this with a holding page for users which did not even lose their Session (those that waited the couple of minutes the final log copy & restore took)
Gail recommended to me to put the Source database into NORECOVERY instead of OFFLINE as that would allow a backup from the new Server to be restored to the Old in the event that a backup was required (assuming same SQL versions).
September 28, 2015 at 12:41 am
September 28, 2015 at 9:13 am
The process that Kristen describes will work nicely.
To really minimize downtime, I like to set up mirrors from the old environment to the new. Then migration is just failing the mirror over and repointing the application.
It's not always an appropriate solution, though. If you're moving from one geographical location to another and you're on Standard edition, then synchronous mirroring might come with too much overhead.
It also isn't great if you have hundreds of databases and have to do the migration in one piece, since that many mirrors will cause some problems.
If you're just moving between machines in the same location (or have Enterprise and can do asynchronous until migration time), then mirroring is a nice way to minimize downtime.
I'll be sad when mirroring is finally removed, since there's not really a good substitute for that use of mirroring.
Cheers!
September 28, 2015 at 9:26 am
Jacob Wilkins (9/28/2015)
The process that Kristen describes will work nicely.To really minimize downtime, I like to set up mirrors from the old environment to the new. Then migration is just failing the mirror over and repointing the application.
It's not always an appropriate solution, though. If you're moving from one geographical location to another and you're on Standard edition, then synchronous mirroring might come with too much overhead.
It also isn't great if you have hundreds of databases and have to do the migration in one piece, since that many mirrors will cause some problems.
If you're just moving between machines in the same location (or have Enterprise and can do asynchronous until migration time), then mirroring is a nice way to minimize downtime.
I'll be sad when mirroring is finally removed, since there's not really a good substitute for that use of mirroring.
Cheers!
I agree. Mirroring is perfect for this solution. I recently did a data center move with a about 750 GB worth of databases. We set up mirroring, and on the day of the cutover, we failed the server over. The entire process took less than an hour. The database piece was completed in minutes, the biggest wait was for DNS changes, code being aplied, etc.
You may also be able to leverage log shipping for the same purpose.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 28, 2015 at 9:48 am
Michael L John (9/28/2015)
Jacob Wilkins (9/28/2015)
The process that Kristen describes will work nicely.To really minimize downtime, I like to set up mirrors from the old environment to the new. Then migration is just failing the mirror over and repointing the application.
It's not always an appropriate solution, though. If you're moving from one geographical location to another and you're on Standard edition, then synchronous mirroring might come with too much overhead.
It also isn't great if you have hundreds of databases and have to do the migration in one piece, since that many mirrors will cause some problems.
If you're just moving between machines in the same location (or have Enterprise and can do asynchronous until migration time), then mirroring is a nice way to minimize downtime.
I'll be sad when mirroring is finally removed, since there's not really a good substitute for that use of mirroring.
Cheers!
I agree. Mirroring is perfect for this solution. I recently did a data center move with a about 750 GB worth of databases. We set up mirroring, and on the day of the cutover, we failed the server over. The entire process took less than an hour. The database piece was completed in minutes, the biggest wait was for DNS changes, code being aplied, etc.
You may also be able to leverage log shipping for the same purpose.
Yup, something like log shipping will end up being the answer when mirroring's removed. It's just not quite as convenient 🙂
September 29, 2015 at 1:50 am
I've never tried mirroring / log shipping in this scenario, always done it "manually".
For us this pretty much only occurs when we upgrade both hardware and SQL version. Can mirroring / log shipping be used when the destination server has a newer version of SQL?
That said, big issue for us when migrating to a newer SQL version (and trying to obtain minimal downtime) is that when we restore the final log WITH RECOVERY then SQL will iterate to update through all the intermediate versions. We then need some additional processes - such as refreshing STATS to prevent lousy query plans where optimiser has changed etc. and usually a bunch of other "nice to have" processes which we choose to do before putting the new DB live.
September 29, 2015 at 9:14 am
Yes, you can mirror to a newer version of SQL Server. The caveat there is that once you fail over to the new server, you can't fail the mirror back to the old server.
It's basically just the same as the restrictions on restoring DBs. You can restore to a newer version (log shipping would also just be restoring on the destination server), but not to an older version.
Cheers!
September 29, 2015 at 7:40 pm
Jacob Wilkins (9/29/2015)
Yes, you can mirror to a newer version of SQL Server.
Thanks. Is there still the delay when failing over whilst SQL upgrades everything to the newer version (as is the case with restore when you use RECOVERY)? or does mirroring do this differently and apply the Upgrade earlier, so that there is less delay on failover? If that's the case it would be a big win for me when upgrading to new hardware and new SQL version 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply