This week I’ve been working on getting some lab infrastructure fitted with much needed updates. One of those components was an aging Microsoft SQL Server 2008 R2 server on Windows Server 2008 R2 which I had been using to host databases for various projects. Since I had chosen to build the new SQL server in parallel, I’m benefiting with fresh and problem free builds of Microsoft SQL Server 2012 on Windows Server 2012 R2. The downside is that I’m responsible for dealing with all of the SQL databases and logins and potentially scheduled jobs that must be migrated to the new SQL server.
vCloud Director is one of the last databases left to migrate and fortunately VMware has a KB article published which covers the step required to migrate a back end SQL database for vCloud Director. The VMware KB article is 2092706 Migrating the VMware vCloud Director SQL database to another server.
Looking at the steps, the migration looks like it will be fairly simple. VMware even provides the SQL queries to automate many of the tasks. I’ll migrate my vCloud Director database using these steps in the following video. I did run into a few issues which mostly boil down to copy/paste problems with the SQL queries as published in the KB article but I’ve provided the necessary corrections and workarounds in the video.
As shown in the video, I ran into a syntax issue with step four.
The SQL query provided by the KB article was:
USE master;
GO
EXEC sp_attach_db @dbname = N’vCD_DB_Name‘,
c:\Program Files\Microsoft SQL Server\MSSQL\Backup\vCD_DB_Name.mdf
c:\Program Files\Microsoft SQL Server\MSSQL\Backup\vCD_DB_Name.ldf
GO
The corrected SQL query syntax according to the Microsoft SQL Server Management Stuido appears to be:
USE [master] GO CREATE DATABASE [vCD_DB_Name] ON ( FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL\Backup\vCD_DB_Name.mdf' ), ( FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL\Backup\vCD_DB_Name.ldf' ) FOR ATTACH GO
Another issue I’ll note that wasn’t captured in the video deals with step seven where the vCloud Director cell server is reconfigured to point to the new database. The first time I ran that step, the process failed because the cell attempted to locate the SQL database in its original location which it actually found. When this occurred, the cell configuration script doesn’t prompt me to point to a new SQL instance. In order for step seven to work correctly, I had to drop or delete the database on the SQL 2008 R2 server and rerun the vCloud Director configuration script. What happens then is that the cell doesn’t automatically ‘find’ the old instance and so it correctly prompts for the new back end database details. VMware’s KB article provides most of the steps required to migrate the database but it does need a step inserted prior to step seven which calls for the deletion of the original database instance. Step two places the vCloud database in READ_ONLY mode but the vCloud cell configuration was still able to ‘see’ which causes step seven to fail.
Blake Garner (@trodemaster on Twitter) provided a helpful tip which will also work with step seven in lieu of dropping or deleting the database on the original SQL server:
You could also clear DB config from the /opt/vmware/vcloud-director/etc/global.properties and run configure again.
Overall the process was still fairly simple and painless thanks to VMware’s published documentation.
