Migrate vCloud Director Database from SQL 2008 to 2014

Posted on by Jordansphere

In order to upgrade vCloud Director 8.0.2 -> 8.2.0 a system requirment arose where SQL2008 was not supported. Checking the VMware compatibility matrix, SQL 2014 was on the list so I provisioned a new Windows 2012 server with the view to shutting down the old server. As this was the only DB left on the server  I will eventually be deleting the VM and SQL server.

In this scenario the database is called vcloud, username is vcloud and password is helloworld

This was mainly taken from VMware KB2092706  with a few minior tweaks for my specific environment:

 

1) Its always prudent to take backups and snapshots before any maintanence work: 

- Backup DB in SQL Studio Management
- Snapshot the vCloud Director Cell(s)
- Snapshot the vCloud Director Database Server

 

2) Shutdown the vCloud Director Service 

service vmware-vcd stop

 

3) On the OLD (SQL2008) database server, log into the SQL Studio Management: 

USE master;
GO
ALTER DATABASE vcloud
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE vcloud
SET READ_ONLY;
GO
ALTER DATABASE vcloud
SET MULTI_USER;
GO

 

4) Copy the data and transaction logs from the OLD SQL 2008 server to  NEW SQL 2014 server

OLD server location: 

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\vcloud.mdf
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\vcloud.ldf

NEW server location: 

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\vcloud.mdf
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\vcloud.ldf

Note: In my case the default locations are different from the VMware KB.

 

5) After reading a very good blog about issues occurring if the old DB is online (creidt to boche.net), I decided to close access by simply disabling the NIC on the OLD server

 

6) On the NEW server open SQL Studio Management : 


USE [master]
GO
CREATE DATABASE [vcloud] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\vcloud.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\vcloud.ldf' )
 FOR ATTACH
GO

 

7) Create a SQL login user 

USE master
GO
exec sp_addlogin ‘vcloud‘, ‘helloworld’, ‘vcloud‘

 

7) Set permissions for the user: 

USE [vcloud];
GO
exec sp_change_users_login ‘Report’
EXEC sp_change_users_login ‘Update_One’, 'vcloud', 'helloworld'
GO

 

8) As I am running vCloud Director 8, there is a tool to update the db connection settings. I used the following command from the VCD cell:

  

cd /opt/vmware/vcloud-director/
./cell-management-tool reconfigure-database --database-host '192.168.3.245' --database-name  'vcloud' --database-user 'vcloud' --database-pwd 'helloworld'

 

9) When complete restart the VCD service 

service vmware-vcd start