Hello Andrew Fryer here, I have hacked into the UK TechNet blog and I notice that this week’s TechNet On is focused on SQL Server upgrade and migration. I think this is a very comprehensive list of resources but I wondered how virtualisation changes things?
The answer depends on where you are starting from and which part of the upgrade process you are working on:
SQL Server is running on a physical server with directly attached storage (DAS) – so the databases are on disks in the physical machine. If it’s only running one database you can simply backup the database and restore to it to a virtual machine to begin testing. If it’s a lot of databases back up the disk(s) using windows backup (the SQL Server needs to be stopped during that process) and you will end up with a .VHD file for each disk which can be attached to a Hyper-V virtual machine (VM) as a second disk. You can than start up the latest version of SQL Server and attach each of those databases one at a time.
Once you have tested everything you can use the same technique to move the database for the last time if you are going to virtualise it. However if you want to stay physical and your base OS is Windows Server 2008 R2 then you can boot to the VHD as though it were a real volume. If not throw away the VM and use the environment for your next bit of dev/test and copy/backup the database to your new server as discussed in the TechNet On resources.
SQL Server is already running on a VM. For testing you simply take a backup of the VHDs (I am assuming here you back up your VM’s with something like System Center Data Protection Manager or an equivalent third party tool), on to a test server and then you can do an in place upgrade on that virtual machine to test, and repeat when you are ready to go into production. If you are using pass through disks ( essentially a point to a LUN on a SAN) then use your SAN to create additional copies of the relevant LUNs which can then be attached to a new VM.
SQL Server Clusters. A SQL Server 2005 cluster can be upgraded to SQL Server 2008 without taking it down by upgrading each node in turn (as described on TechNet here) however you will want to test that and so you can use virtual machines to point to a copy of the shared storage used in the production cluster.
I should also add that one of the key advantages of using virtualisation as part of your upgrade is that you can ensure your new SQL Server has the same server name as the old machine, removing the need for any client configuration works or DNS fixes to see the new server from existing applications.
Which reminds that one problem with SQL Server is that it hasn’t worked with sysprep, the business of making Windows Server anonymous which is used to create copies of servers in virtualisation scenarios. This has been addressed in SQL Server 2008 R2 with the image prepare installation option so you can make a copy of a prepared Windows Server/SQL Server VM and then fire this up repeatedly to make copies of SQL Server installations. The TechNet On guys have used my video of how to do this here.
So as well as checking out all the TechNet On resources for SQL Server upgrades my top tip would be to skill up on what virtualisation can do for SQL Server and how to use it particularly if you are working with or for smaller businesses.
Finally I have quite a few posts on migration and upgrade on my own blog