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.
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.
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.
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?
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.
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
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:
A couple notes:
So, there's a trade-off, but this method works and it is a common enough technique.
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:
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.
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.
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