What is the best way to move 20+ databases to a new database server? SQL 2005

by kmc   Last Updated June 12, 2019 08:00 AM - source

Current database server: SQL Server 2005 - Windows Server 2003 New destination database server: SQL Server 2005 - Windows Server 2003 Enterprise - VM Ware image

Current database server has 20+ databases on it, some application databases...others infastructure type databases (Citrix). We want to move all these databases to a new freshly built box that is virtualized.

So in further summary - yes, this is physical to virtual. - 20+ databases transfered to this new virtual SQL 2005 box. - applications on this box require minimal downtime.

A few approaches I can think of (all would be tested): 1. Third party physical to virtual converters - then shut down the old box.
- concerns = SID associations, Windows or SQL Server not liking this.

  1. Move over all databases at once to the new server - Shut down the old server, change hostname on the new virtual box to the old hostname.

  2. Move over all at once but use a different hostname for the new box - this allows parallel running in case something breaks - challenge = must change hostname within each application - could have problems.

  3. Move over each databases in stages - this woudl mean a new hostname as well and a longer more drawn out project.

Anybody else have a similar scenario?



Answers 6


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.

Instantsoup
Instantsoup
August 17, 2009 19:53 PM

Running in parallel risks data changing between when you made the copy & updating the copy accordingly. Updating applications to point to a new hostname can cause grief as well.

I would recommend using a parallel setup for testing each application, but once satisfied with testing I would probably use Detach/Attach: How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

OMG Ponies
OMG Ponies
August 17, 2009 19:54 PM

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:

  1. Create the new server and move jobs/logins/SSIS, etc.
  2. Set up source database for log shipping and start shipping.
  3. Stop application(s) and set the DB to read-only.
  4. Back-up the last tran log for the database.
  5. Restore the last tran log on new server, set to no-recovery.
  6. Set the new DB to back into read/write.
  7. 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 -

Anon246
Anon246
August 17, 2009 20:40 PM

From my experience p2v is an excellent & fast option, but not ideal if you want to minimize down time. I'd use it only when existing servers are not a mess & virtualizing is only for hardware rationalization. (i.e. your not renaming the box, putting it in a new AD ect.)

SQL Server & Windows will be ok if you p2v but you'll need to stop SQL Server services before you start the p2v. Windows SID's ect will all remain unchanged, what windows wont like is the physical & the virtual servers being connected to the same network.

If you go for the attach/detach method then make sure you also copy:

  • sql server logins
  • sql server agent jobs (including backup jobs)
  • linked servers
  • extended stored procedures

setting up new infrastructure & doing a cut-over means less down-time but requires more work. As discussed, logshipping for a server 'cut-over' is the quickest way to do this, especially if you have big databases.

Nick Kavadias
Nick Kavadias
August 18, 2009 01:17 AM

If you have a few dollars to spend, like 300.00 or so, check out idera admin toolset. An excellent piece of software. I used it on a recent project. It moved the databases and any relating objects, including users. It was worth it. In 3 clicks I moved all my databases. I still use it to move databases back and forth. I believe they have a trial version. Also you get many other tools, like moving users or objects across databases etc.

Saif Khan
Saif Khan
August 26, 2009 20:18 PM

Well this appears to be very late but I am writing this as it can help in future. First of all dealing with databases requires full attention and while migrating them manually there are always chances of data loss, moreover, while exporting more than 20 databases would not be simple enough and you will get errors. So, to avoid all these situations and to achieve an error-free database, the best way to move the database is a toolkit. You do not want to disturb the tables, schemas, keys of existing database. Thus move more than 20 databases with ESF database migration toolkit. It is designed with algorithms that support 256 migration methods. Just add the source database and destination database to move databases. Get it from here https://www.filerepairtools.com/esf-database-migration-toolkit.html

Kumar Sandeep
Kumar Sandeep
June 12, 2019 07:03 AM

Related Questions




Exchange 2007 P2V NSPI Proxy error appearing

Updated April 03, 2015 03:00 AM

P2V converting windows server 2008 Failure

Updated April 13, 2017 17:00 PM