Migrating a vCenter Database from Local SQL 2005 to Dedicated SQL 2008

February 19, 2015 Jordansphere MS SQL

Scenario: I needed to upgrade vSphere from 5.1 to 5.5. Microsoft SQL 2005 is no longer supported so I needed to move the vCenter database from the local machine to dedicated SQL 2008 server before the upgrade. I also had the SSO and Update Manager Database on this local machine. Instructions for moving the SSO DB can be found here.  In this example I am moving from Windows Authentication to SQL Authentication and also upgrading the Native SQL Client to 10.0

 

Here are the steps I used

PreReqs) Check the SQL native client 10.0 driver is installed on vCenter Server machine  by using this link


1) Take a snapshot of vCenter server

2) Stop all vCenter services (you can leave SSO and VUM working)

3) Backup then Restore DB on new DB 2008 Server

4) Install Native Client Driver 10.0

5) Delete DSN (VMware VirtualCenter)

6) Create DSN  called "VC-DSN"

Example:
U: vcsql
p: MyPassword
Hostname: dhost.jordansphere.co.uk

7) Backup Registry items before attempting next step

8) Edit Registry 

HKEY_LOCAL_MACHINE > SOFTWARE > VMware, Inc > VMware VirtualCenter.
i)     Modify DbInstanceName – remove current value (SQLEXP_VIM)
ii)     Modify DbServerType – change from Bundled to Custom


HKEY_LOCAL_MACHINE > SOFTWARE > VMware, Inc > VMware VirtualCenter > DB
i)    Modify key 1 from VMware VirtualCenter to VC-DSN (or leave DSN Name if you named the DSN as the previous name in step 6 )
ii)   Modify Key 4 from SQL Native Client to SQL Server Native Client 10.0
iii)   Modify key 2 from NULL to vcsql

9) Alter DB password
c:\Program Files\VMware\Infrastructure\VirtualCenter Server\vpxd.exe -p
MyPassword

10) Recreate rollup jobs.
Copy the sql roll up scripts (as seen below) to the new DB server and double click each script. This will open up a new query in Management Studio.  Ensure that the VC DB is selected before executing.

rollup1


11) Check C:\ProgramData\VMware\VMware VirtualCenter
SQL Authentication should be: 
url=jdbc:sqlserver://{NEW-DB-HOST}\\;databaseName\={DBNAME};integratedSecurity\=false

12) Restart vCenter Services

As I was using vCloud Director I needed an extra step
13) Update chargeback Data Collector

migrating moving sql vcenter db database,

Powered by WordPress. Designed by elogi.