Monday, January 7, 2013

Upgrading vCenter Server Database from SQL Express to SQL Standard

One of the projects on my radar last week was the database upgrade of our vCenter Server from SQL Express to the full blown SQL standard version. According to VMware best practices, the SQL Express database is only recommended for environments that have less than 5 hosts or 50 VMs. Unfortunately, due to how fast our environment has grown over the last year, we had gone way beyond this recommended limit.

Environment Assumptions:
  • vCenter 5.0 was initially installed with the SQL Express database instance
  • the SQL Express database will be upgraded to a full blown (standard/enterprise) SQL database
  • VMware Update Manager is also installed and needs to be migrated
The first thing I did prior to the upgrade was to take a snapshot of our vCenter Server. That way, if anything were to go wrong, I could always revert to a working instance. If you tried to take a snapshot of the vCenter Server while your vSphere client is connected to your vCenter, you may experience the error:



Instead, login to the ESXi host where your vCenter VM is located and take a snapshot from there.



Next, console into your vCenter server and start the SQL Server installation. You will want to use the same account as the one that originally installed the vCenter server.


 Select new installation or add shared features:


Select the features. This is what our DBAs wanted. Note: You will need the management tools to manage the databases afterwards.


 Give it a name or use the default.


Add the SQL Server Administrators. You will want to add the user which originally installed the vCenter server as they are most likely the owner of the original SQL Express database (current user) as well as any DBA admins.


Once the installation is done, you want to start up SQL Server Configuration Manager and start the SQL Server agent for that new instance. Also, set the service to automatically start.


Verify that you are logged in as the same user in which the vCenter Server was originally installed. You can do this by connecting to the SQL Express instance and checking the owner of the VIM_VCDB database.


 Stop the vCenter Server services.





Connect back into the SQL Express database and backup the VIM_VCDB database and the VIM_VMUM database.


Check off the verify backup when finished option



Now connect into the new SQL Server Standard database.


Right-click databases and select restore database. Select the backup for the VIM_UMDB that was just created and then select the same database name under the "To database:". Check "restore" and ensure that the backup "Start Date" field is added to the "To a point in time: field.


Under options, select to overwrite the existing database (there isn't any so it will create it). Under the restore as field for both the _dat and _log rows, change the path:

From: C:\Program Files\Microsoft SQL Server\MSSQL10_50.VIM_SQLEXP\..(something here)..
To: C:\Program Files\Microsoft SQL Server\MSSQL10_50.<name of new SQL instance>\..(something here)..


Repeat the same for the VIM_VCDB database.





You should now see the new databases under the new SQL server instance once you refresh the SQL Server mangement studio.

Next, you will need to uninstall the SQL express database.


Uninstall vCenter Server and update manager. This will clean up the installation when you install it afterwards (ie: remove dependencies of vCenter services on the SQL express database). Once that is complete, the ODBC data sources has to be recreated for both the VCDB and the VMUM databases. Open up the 64-Bit ODBC Administrator under the Start>Admin tools. Create a new system DSN to connect to the vCenter server database.


Make sure you change the default database name to VIM_VCDB:


Start the vCenter server installer and install vCenter server. Select the system DSN you just created when prompted.


Make sure you do not overwrite the database.


Next, create the ODBC connection for Update Manager. Update manager uses 32-bit connections, therefore, you will have to create a 32-bit system DSN. Go to C:\Windows\SysWOW64 (sysWOW64 is not a mistake) and launch the odbccad32.exe file to create the ODBC connection.


Make sure you configure the  default database to VIM_UMDB. Test and verify the connection and once successful, run the Update Manager installation.



Once the installation is complete, restart the server. The database should now be completely upgraded. One thing you will want to verify is that the re-installation of vCenter created SQL cleanup jobs. You can do this by connecting to the new SQL instance and then expanding SQL Server Agent>Jobs. These jobs are important because without them, you will lose performance logging past a day. See http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1004382 for more details.








1 comment:

  1. hi John. Thanks for the great infos.
    I have a question, if I uninstall the vCenter and Update Manager what will happen to my object configurations(host, datastores, vms, users)? Is it all kept in the two vCenter database? Thanks

    ReplyDelete