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 |
|
franco
Constraint Violating Yak Guru
255 Posts |
 Posted - 2012-03-09 : 09:00:35
|
| The new data center is almost ready to install new OS (Windows Server 2008 R2) and new SQL Server version (SQL Server 2008 R2 EE).Right now we have a box (MSCS Cluster) with almost 100 databases on SQL Sever 2000 EE and Windows Server 2003.Due to the fact that this old HW is out of maintenance, we would like to temporary move this box to the new data center without changing anything.The idea is to create a new VM (same Windows Server 2003) in the new data center, install on it the same SQL Server 2000 EE with exactly the same parameters of the old one including database and log path, and then:1. shutdown SQL Server on the old box2. copy all users databases + system databases master, model and msdb to the new data center in the correct disk path3. shutdown the new installed SQL Server 20004. substitute system db master model and msdb of this new installation with the old ones copied from the old box to maintain logins, jobs, alerts....5. start up SQL Server on the new data centerDo you think that this can be a practical way?Any suggestions?Thank you very much.Frank |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-03-09 : 09:14:53
|
| How long is your downtime window?In my experience "2. copy all users databases + system databases master, model and msdb to the new data center in the correct disk path" takes a long time (assuming the databases are "big-ish"), and is longer than the Client will allow for downtime.To work around this we do RESTOREs instead.BACKUP LOG on old machine (to clear it / reduce its size)BACKUP FULL on old machineRESTORE on New machine (using NORECOVERY)If significant delay until copy-over is ready then also do:BACKUP LOG on old machine (to clear it / reduce its size)BACKUP DIFF on old machineRESTORE the DIFF on New machine (using NORECOVERY)Then when ready to cut over to new machine:Set the database to DBO ONLY / READ ONLY and prevent all remote accessBACKUP "Final" LOG on old machineRESTORE the LOG on New machine (using NORECOVERY)(There might be earlier logs taken after last FULL/DIFF backup, but before this final LOG backup, restore any such logs first)Once all restored are done / have been successful then use:RESTORE DATABASE MyDatabaseName WITH RECOVERYSet database on New Machine to READ_WRITE and MULTI_USERAllow users access to the new databaseYou can still use the copy-over method to set up the machine initially, just some databases (i.e. copied whilst "in use") may not actually "start" until thy have been restored (i.e. if the COPY was whilst the database was in inconsistent state). |
 |
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2012-03-09 : 09:52:41
|
| Thank you very much for your reply.In fact we have only 50GB to move from old HW to the new one.We can also do the operation during week-end so we have time.Do you think we can go with the "copy" option?Kind regards.FrankFranco |
|
|
|
Jake Shelton
Yak Posting Veteran
74 Posts |
Posted - 2012-03-12 : 10:15:01
|
| Kristen, how have you found this method for migration of DTS packages? When last I tried this (MANY moons ago!) I found the packages had all sorts of broken links and essentially had to be recreated at the destination. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-03-12 : 10:41:55
|
| If you have many DTS packages, you should be using a config file for server references. This way, when you move things, you only update the config file. |
|
|
|
|
|
|
|
|
Move all databases to a new datacenter