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 |
|
sonu_bariar
Starting Member
4 Posts |
 Posted - 2012-07-26 : 13:39:59
|
| Hi Guys,I am working on a project to migrate SQL Server 2005 databases from one Datacenter to another. Each SQL Server instance has more than 500 databases with total size of 2TB. I would like to get yours suggestions for the best approach to migrate the databases with minimum down time.Thanks in advance |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-26 : 13:45:25
|
| How much down time are yoou looking for and what sort of connectivity do you have between the datacentres?One option might be to set up replication or mirroring between the two. Stop one then bring up the other - could be done on a database by database basis if you like.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-07-26 : 13:45:50
|
| Unless you can do SAN replication, I'd take backups and restore them at the other data center.Day of migration make sure you have a differential and tran log backups to bring everything right up to the last second.Make sure to practice the migration several times so everything goes right. Script all of the file copies and restores. And login/user migration scripts. Prepare any job migrations etc.Practice the migration as many times as it takes until you're 100% confident you've worked out all the kinks and everything will go right. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-07-26 : 13:46:15
|
| For very little downtime but a lot of work, I would recommend setting up database mirroring so that you can preload the databases on the other server and then the failover when you are ready would just means a few seconds of downtime per database.We very frequently use this method to migrate databases, and it means very, very little downtime which is crucial in the apps that I support.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sonu_bariar
Starting Member
4 Posts |
Posted - 2012-07-26 : 13:56:51
|
| Thank you very much!Everybody for the prompt response. We can have a down time of 6-10 hours. Connectivity between the datacenters is very stable and good. What i read, it is not feasible to create database mirroring between the instances for large number of databases like we have (500 databases on primary instance).I need to discuss with my team about the SAN level replication as we are using different SAN in both Datacenters.Whats are your views on using SQL native logshipping or third party tools like Idera SQL safe to create logshipping.Thanks a lot! every body for the suggestions. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
SQL Server 2005 Database Migration