Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
noneil277
Starting Member
13 Posts |
 Posted - 2009-07-27 : 11:41:08
|
| Customer assurance, no transactions lost, how much downtime, battling against the FUD! (Fear Uncertainty and Doubt)Greetings Team, I'd like to discuss one of the biggest questions or concerns that have come up when database migrations come around. From a customer standpoint the most delicate question to answer is how much downtime, giving an ETA is usually mandatory and 'when it's done' does not generally fly. In addition when migrating larger databases inaccuracies or inconsitencies in a database are often of great concern. I've worked with migrations on terabyte sized databases and one of the most tedious operations is how to guarantee database integrity without having database mismatches or rather, transactions lost. What I usually propose when reaching out to prospects migrating from host A to B is first determine the size of the databases and then factor in network throughput such in if a 100GB database will take 10 hours to transfer from 1 hosting company to another most likely I will want to schedule the least busy operating day of the week, start off that evening around business close, run a full backup, copy the db, import the db, apply the most recent transaction logs 2 or 3 times and then switch the connector string between the apps and the db. Would anyone have any interesting encounters to share when migrating databases? being in the hosting industry and working with dev's, execs and other dba's alike this is generally one of the most common operations. I look forward to hearing responses as well as sharing expertise on the discussion, thank you and have a great week! don't forget friday is july 31st national systems administrator appreciation day (dba's count too).--Nicholas O'NeilSenior Engineer, Logicworks.netMCSA/MCSE/+Messaging/MCTS:SQL2005linkedin.com/in/noneil |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-07-27 : 12:09:06
|
| Using transaction log backups, you should be able to migrate even very large databases with very little down time. I have moved 100 GB+ databases with under 15 minutes of downtime.1. Make a full database backup and then restore it to the new server with NORECOVERY.2. Apply all transaction logs to the new server as they are completed with NORECOVERY.3. When you get to the point in time where you want to cutover, kick all users out of the old database, make one last transaction log backup, and take the old database offline.4. Apply the last transaction log with RECOVRY.CODO ERGO SUM |
 |
|
|
|
|
|
|
|
Database Migrations