4

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?

Chopper3
  • 100,240
  • 9
  • 106
  • 238

5 Answers5

10

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
  • 315
  • 1
  • 4
  • 11
  • +1 for moving manually. I would do one at a time, which would result in just the one application being offline whilst you move the DB. Once verified it works on the new server, move onto the next. –  Aug 17 '09 at 21:11
  • Also remember to run sp_updatestats on all databases after a migration. To quickly run it against all the databases try this: exec sp_msforeachdb @command1 = 'exec sp_updatestats' – SQLChicken Aug 18 '09 at 01:46
  • I'd test with a few in different network zones if you have them, to make sure connections to the vm box work beforehand. – Sam Aug 19 '09 at 16:02
1

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
  • 256
  • 1
  • 2
  • +1:I have used this approach on a number of occasions. You can script out the majority of the steps required, for both the source and target servers, so that when the actual switch over needs to be made it's almost a matter of a few simple clicks. – John Sansom Aug 17 '09 at 21:04
0

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
  • 396
  • 1
  • 7
0

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
  • 10,758
  • 7
  • 36
  • 47
0

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
  • 1,935
  • 2
  • 20
  • 25