What is the best way to move 20+ databases to a new database server? SQL 2005
Solution 1:
We moved from a single SQL server to a new SQL cluster (all new hardware). About 70 databases. The way we did it was to detach the databases, copy the files, and then attach the databases to the new SQL nodes.
We were forced to update the hostnames but I would take the old one offline and use the same hostname. You can always switch right back that way.
Solution 2:
One way to minimize downtime is to use log shipping from one server to the other. This requires repointing the app configs, but it has the benefit of having less downtime. In general, the process is as follows:
- Create the new server and move jobs/logins/SSIS, etc.
- Set up source database for log shipping and start shipping.
- Stop application(s) and set the DB to read-only.
- Back-up the last tran log for the database.
- Restore the last tran log on new server, set to no-recovery.
- Set the new DB to back into read/write.
- Bring repointed application back online.
A couple notes:
- DB Mirroring is a similar solution.
- SAN level replication is also similar, but it requires special SANs (like HP EVAs).
Pros:
- Minimal downtime.
- Log shipping is pretty easy to set up.
- Rollback plan fairly easy.
Cons:
- More manual steps.
- Have to check the app to make sure it is properly repointed (more sys admin/DBA work).
So, there's a trade-off, but this method works and it is a common enough technique.
Eric -