Expert tips for moving vCenter to a new SQL Server

The vCenter database is the brain of any vSphere installation, and moving it should not be taken lightly. vExpert Rick Vanover shares a few extra tips to ensure success on the move.

We’ve all set up test vCenter installations and used the built-in Microsoft SQL Server Express engine. It is a great way to get up and running to test this or that feature of vCenter before trying it in production. For a production environment, I’ve always preferred to leverage a shared (or centralized) SQL Server to host the database. Primarily, they are easier to manage across all database installations (for topics such as SQL Server updates and service packs). As a secondary benefit, there is less overhead in having a number of virtual machines provisioned with extra memory and CPU resources dedicated to having multiple SQL Server Express installations running around.

The SQL Express engine that comes with a fully default vCenter Server install is ideally suited for a maximum of 50 virtual machines on 5 hosts or so. It isn’t an enforced limit, but things start getting weird if you exceed this range. And should a test vCenter Server environment need to move over to a shared database, there are a few tips to remember. This can also be a good step to do before upgrading to vSphere 5.

The first step is to familiarize yourself with the VMware KB 1028601. While this knowledge base article has most of the critical steps, there are a few additional considerations. First of all, the move of a SQL database is easier than it seems. The KB article recommends the route of copying the files (SQL’s .LDF and .MDF) and attaching them to a new SQL Server system. This is a different approach than a backup and restore of the database.

The other critical thing is to not skip the vCenter installation step. This may seem unnecessary or even risky, but it is critical to recreate the SQL Server Agent jobs and extend the database schema (if required) on the new server. Note that the default SQL Express engine does not have a SQL Server Agent capability, so this step should not be omitted, as outlined in the KB.

If you are using service accounts, the vCenter Server service account should be added as a DBO role to the database on the SQL server. This can be set up with the ODBC connection (running as the service account) and will be used for the database connectivity.

Once the database is moved, another default configuration should be addressed. The SQL Server Express database for vCenter has the database logging configured for a restricted logging file size of 500 MB. Now, depending on how you are backing up the vCenter Server (you are backing it up, correct?), the SQL Server logs may be processed. This can be via SQL Server scripting or via log handling through a technology like Windows Volume Shadow Copy Service (VSS). Think of the log file as a container that is fully allocated, to the default 500 MB. Database activity logging will fill it up, and then backups or other log handling mechanisms will remove the logging within that container. (This also may be the time to switch from the default simple logging option to full logging, common with most SQL DB servers.) As a separate task you could shrink the log files to make the log file smaller, however, depending on the log file processing (backups or otherwise), this 500 MB container for logging may not be enough.

Figure A

The vCenter database with the default 500 MB log size with restricted growth

If the database is moving to a shared SQL Server database, the SQL Server DBA or server administrator may have a policy in place on log file management, but one step to consider is permitting the log files to grow unrestricted to avoid filling up the .LDF file. If the log file fills up, you will have the vCenter Server service stop and the following message will appear in the application log:

An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error:

Error[VdbODBCError] (-1) "ODBC error: (42000) - [Microsoft][SQL Server Native Client 10.0]

[SQL Server]The transaction log for database 'VIM_VCDB' is full.

If the database will permit automatic growth of the log file, this message can be avoided. However, the handling of the log files must be addressed. Generally speaking, the database file should grow over time; but the log file should be managed and pruned.

Once the database has been moved, put in a few other practice points to ensure there are no issues with the vCenter Server having database access. This includes making sure things like Windows Server patching and updating windows are in alignment between the two systems. This will ensure that the vCenter Service has adequate access to the database. Another consideration is to move the database compatibility mode from SQL 2005 to SQL 2008. This is usually the case as vCenter installs SQL 2005 Express, but if a new SQL Server is utilized for a hosted vCenter database, SQL 2008 would be the choice.

Moving the SQL database from a standalone vCenter Server with SQL Express is an important step and in many environments, the simplest configuration has been outgrown. Have you any tips on moving the vCenter database? Share your comments below.