Now that VMworld Europe is over I’ve had more time to sit down and look at MTI‘s Solution Centre and decided that I’d take the opportunity to upgrade my company’s primary demo environment to vSphere 6.0. Previously I had held off doing an upgrade because we run a PernixData demo environment on our main ESXi cluster and were waiting for the new FVP to be released. Now that it has (FVP 3.0), there was no reason to stick to an outdated environment!
Turns out the lowest version of Microsoft SQL Server supported is 2008 R2 SP1 and the version I deployed years ago was 2008 R2 RTM (no SPs).
To verify the SQL Server version, compatibility level, and edition you can execute a simple SQL query:
- Open the SQL Server Management Studio and connect to the SQL Server that vCenter Server database resides on.
- Run the this query on the vCenter Server database to verify the version, level and edition:
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
To find out what SQL server build you have, pop along to this great website: http://www.sqlsecurity.com/faqs-1/sql-server-versions/2008-r2
The Database Interoperability Matrix for VMware can be found here: http://www.vmware.com/resources/compatibility/sim/interop_matrix.php
So if you’re in the same position as me, you pretty much have one of two options:
- Do a fresh install and lose all your historical data and other configurations from vCenter.
- Do a database migration to a supported DB.
Fortunately for me, you can easily migrate from SQL Server 2008 to 2012 – and again you have two options on how to do this:
- Do an in-place upgrade where the SQL Server is upgraded where it’s currently installed
- Do a database migration where the old SQL DB is migrated onto a new SQL Server environment.
In my case I decided the second option would be the best option as I also wanted to upgrade the OS to Windows Server 2012. There are a number of migration options available to you, but for me the easiest option was to do a backup of the old database and restore it onto the new database!
I won’t go into how to deploy SQL Server 2012, as there are loads of tutorials online so here’s the process I did to backup and restore my DB:
Note: In order to transfer the backed up database file from the old SQL Server 2008 R2 VM to the new SQL Server 2012 R2 VM I simply added a new vDisk to the 2008 VM, backed up the DB onto that vDisk, then attached it to the 2012 VM.
You will also need to know the user account assigned to the VCDB.
- Before backing up the vCenter Database, ensure the vCenter Server Services are stopped.
- Backup the vCenter Database from within SQL Server Management Studios: Right-click the DB, select Tasks and Back Up.
- Create a Full Backup and choose the destination (in my case a new disk which I will disconnect and add to the new SQL VM).
- Once backup is complete, remove the vDisk from the VM, ensuring you choose the “Remove from virtual machine” option, DO NOT CHOOSE THE “… and delete files from disk”.
- On the new SQL VM, create a new vDisk and select “Use an existing virtual disk”.
- Browse to the datastore containing the old SQL VM and select the vmdk file relating to the vDisk with the database backups.
- Once mounted, open a console to the new SQL VM and check the DB backup files are there. Open up SQL Server Management Studio and right-click Database and select Restore Database.
- Verify options are correct and restore.
Restoring a database automatically creates the database files that are needed by the restoring database. By default, the files that are created by SQL Server during the restoration process use the same names and paths as the backup files from the original database on the source computer.
Optionally, when restoring the database, you can specify the device mapping, file names, or path for the restoring database.
- When a database is restored on another system, the SQL Server login or Microsoft Windows user who initiates the restore operation becomes the owner of the new database automatically.
Once the DB has been restored, there are a number of additional configurations required, one of which is to recreate the DB security users and SQL Agent Jobs.
- Create a new Login to SQL Server 2012 making sure the new login matches the old one from SQL Server 2008. Assign the VCDB as the default DB and ensure the new user is the VCDB owner.
- Finally change the DB compatibility level from 2008 to 2012. This allows the usage of the new SQL Server 2012 features. The following script can be used to automate the change (rather than going into each database property):
ALTER DATABASE [mydatabase] SET COMPATIBILITY_LEVEL = 110
where [mydatabase] is the database to change the compatibility level
- Re-create all the SQL Server Agent jobs, for a complete list of the jobs that should be present, see:
- Configure Microsoft SQL Server TCP/IP for JDBC and create a 64bit ODBC DSN.
- Once the DB has been restored, you can remove the vDisk that was attached with the backup files.
- Complete the vCenter Server 6.0 installation (I won’t go through the process here). For the demo environment, we used an Embedded PSC Deployment and when prompted we chose the DSN to the migrated VCDB and chose to use the data on this DB rather than re-initialising the DB.