Migrating VMware vCenter Server 4.1 and Update Manager databases from MS SQL 2005 to MS SQL 2008 R2

Migrating VMware vCenter Server 4.1 and Update Manager databases from MS SQL 2005 to MS SQL 2008 R2:

1. Backup the vCenter, Update Manager and system databases
2. Stop the following services on the vCenter server:
VMware vCenter Update Manager Service
VMware VirtualCenter Management Webservices
VMware VirtualCenter Server
3. Detach the vCenter and Update Manager databases, drop connections
4. Copy the databases and their logs to the new SQL server
5. Create a login for the vCenter account (and for the Update Manager account if it is different from the vCenter account) on the new SQL server
6. On the new SQL server, grant the vCenter account the db_owner permission over the MSDB system database
7. Attach the vCenter and Update Manager databases to the new SQL server
8. On the new SQL server, grant the vCenter account the db_owner permission over the vCenter database and the Update Manager account (if it is different from the vCeneter account) the db_owner permission over the Update Manager database
9. Install Microsoft SQL Server 2008 R2 Native Client (v10.50.1600.1) on the vCenter server. The old client can stay, but if you want to remove it, remove the existing DSN first.
10. Create new DSNs using the new driver

- For vCeneter open: C:\windows\system32\odbcad32.exe (this is 64-bit version) and chose SQL Server Native Client 10.0
Specify the new SQL server as the database server
Make sure the default database is set to the vCenter database
Test the connection

- For Update Manager open: C:\windows\SysWOW64\odbcad32.exe (this is 32-bit version) and chose SQL Server Native Client 10.0
Specify the new SQL server as the database server
Make sure the default database is set to the vCenter database
Test the connection

11. Start the services from the step 2
12. Create the SQL Agent rollup jobs. The scripts are located on the vCenter server in C:\Program Files\VMware\Infrastructure\VirtualCenter Server
13. Set the vCenter account to be the owner of all the vCenter jobs
14. For any issues, check the logs under C:\Documents and Settings\All Users\Application Data\VMware\VMware VirtualCenter\Logs


Location of vCenter Server log files
Managing Data Sources
Microsoft® SQL Server® 2008 R2 Feature Pack
Moving the vCenter Server SQL database
Upgrading to ESX 4.1 and vCenter Server 4.1 best practices