This is a guest post by Jason Boche, VCDX #34

I’ve encountered a situation many times in the past where the vCenter Server database needs to be migrated from one back end Microsoft SQL Server to another. I’ve also heard this how-to question asked from a number of different people. The scenario can present itself in a number of cases.

• The SQL server has reached capacity.
• The SQL server is performing poorly, impacting applications.
• The SQL server is being repurposed.
• The SQL server is being decommissioned.
• The SQL server is being replaced by a newer hardware platform, OS, or SQL version.
• The SQL server has failed and is being rebuilt.
• The database is being migrated from a local SQL Express instance on the vCenter Server to a dedicated SQL Server.

Whatever the case may be, you’ll typically be served an eviction notice well in advance by your DBA. The process isn’t all that difficult. I’ve been through it several times and to me it’s more an annoyance than anything. I’ll explain why in a bit.

With most client/server applications I’ve worked with back ended by MS SQL, the process to migrate a database is easy:

1. I shut down the application tier.
2. Instruct DBA to migrate the database properly (this is the heavy lifting step, includes a backup, migrating logins, etc.)
3. I modify the ODBC connection.
4. I start the application tier.

vCenter database migration isn’t so straight forward. Where it deviates is step 3 because one cannot simply modify the ODBC configuration to point vCenter at the new location of its back end database. The reason for this is because the vCenter database has associated SQL Agent rollup jobs which are installed on the SQL server by vCenter during its installation process. SQL Agent rollup jobs are not stored inside either of the .mdf or .ldf database files. Rather, they are stored in the MSDB database which is why the dbo role is temporarily needed on MSDB when installing vCenter Server – so that the vCenter installation routine can inject the rollup jobs. While on the surface it may seem like simply changing the ODBC connection does work to migrate a database, you’ll find out later on the rollup jobs aren’t running and you’ll probably wind up reading VMware KB Article 1004382 Updating rollup jobs after the error: Performance data is currently not available for this entity to troubleshoot problems you are experiencing in the vSphere Client.

Getting back to what I said earlier about database migration being an annoyance, I say this because I can’t use the typical ODBC based approach which is usually quick and painless for other client/server applications. Rather, it requires uninstalling/reinstalling vCenter Server (in addition to making the ODBC change) to make sure those SQL Agent rollup jobs end up at the new SQL Server location. That said, the vCenter uninstall/reinstall process today runs clean without issues or pitfalls like we had in the past such as overwriting the database or creating a new parallel set of tables with prefixes. Each of those scenarios lead to a brand new empty datacenter when logging into the vSphere Client for the first time. Not good. This uninstall/reinstall migration process looks like this:

1. Uninstall vCenter Server, preserve SSL cert keys.
2. Migrate .mdf and .ldf database files to new SQL server, attach database, recreate/sync logins.
3. Update ODBC configuration.
4. Reinstall vCenter Server.

While reinstalling vCenter Server is annoyance, it is also the most reliable, trusted, and quickest method for me. There is another option for migrating the vCenter SQL Server database and that is outlined in VMware KB Article 7960893 Moving the vCenter Server SQL database. This article contains several steps with supporting links for each step. It goes like this:

1. Stop vCenter Server services.
2. Migrate .mdf and .ldf database files to new SQL server, attach database, recreate/sync logins.
3. Recreate SQL Agent rollup jobs following VMware KB Article 1004382 Updating rollup jobs after the error: Performance data is currently not available for this entity.
4. Update ODBC configuration.
5. Start vCenter Server services.

While this method doesn’t involve reinstalling vCenter Server, to me it is a bit more error prone and tedious which means potential problems Monday morning and time spent troubleshooting the issue. In the end, an uninstall/reinstall of vCenter may be required to resolve a SQL Agent rollup job related problem anyway. With the importance of vCenter’s role escalating with products such as vCloud Director or VMware View, unscheduled down time of vCenter can be more impactful than it was in the past. If you need to migrate the vCenter Server database, follow the migration plan which is best suited for you and your organization. Above all, back up your database and SSL cert keys before you begin.

Greg W Stuart
Greg is the owner and editor of vDestination.com. He's been a VMware vExpert every year since 2011. Greg enjoys spending time with his wife and 3 kids. He works as a Sr. Consultant at VMware and resides in Northern Virginia, 15 minutes west of Washington DC.

Leave a Reply

Your email address will not be published. Required fields are marked *