Protecting the vCenter SQL Database with Maintenance Plan Backups

Chris Wahl · Posted on2012-04-06

As vCenter becomes more integrated with a number of products and services, the need to keep it protected from failure becomes greater. Often times I am asked on ways to protect vCenter, but in my opinion there are two pieces to this question: the application (vCenter Server) and the database. Protecting the application isn’t all that difficult – just using periodic clones can often be enough to solve this problem, or even a quick deployment of the application onto a blank template.

To me, the more essential piece is the database. It contains a lot of information on the environment (including object annotations, which are often used by administrators to record important values). There are many solutions available to protect SQL databases, but often require additional 3rd party software (Red Gate, Veeam) or licensing (SQL Mirroring / Clustering). In my recent SQL database protection posts, I’m mostly focusing on the “free” methods to protect vCenter using what comes out of the box with SQL 2008 R2 (which is in my lab).

Also, don’t forget to check out my other post on this subject that details how to setup and configure SQL Log Shipping.

SQL Maintenance Plan Wizard

The easiest method to create a SQL Maintenance Plan, which will be used to perform a backup of your database, is through the Maintenance Plan Wizard.

To create a Maintenance Plan, open SQL Management Studio and connect to your instance of SQL that houses the vCenter database. Expand the Management folder, right click on Maintenance Plans, and choose “Maintenance Plan Wizard“.

Within the Plan Properties, enter a name and description. For my example, I’m using a single schedule for the entire plan as it makes the most sense for backing up my environment. You might have noticed the schedule is set to “On Demand” – this is no good for our needs, click Change to create a schedule.

I’m going to set up a daily backup job for 3AM. This requires changing the schedule type to Reocurring, frequency to Daily, and daily frequency to 3AM.

In the Maintenance Tasks section, select the Back Up Database (Full) check box. This will perform a full backup of the database on a daily basis.

From the Define Back Up Database (Full) Task page, configure the databases you want backed up. I chose to do All databases, but you could just do the vCenter database.

Additionally, I expire the backups after 7 days so that I only keep a week of full backups in the catalog. I would advise making another job that finds any backups older than 7 days (or whatever amount of days you use) and removes them, if desired. I also set the folder for backups to E:SQL_Backups as this is an externally mapped drive mapped over iSCSI. Finally, I checked the Verify backup integrity check box to ensure that the backups contain all of the actual data.

Finish the creation and ensure that all actions were successful.

Thoughts

Remember that this is only a backup of the database itself in the event that it is lost or corrupted. This is great at protecting from storage failures, but won’t help out that much if the SQL server dies. For that, I’ve covered a more highly available approach with SQL Log Shipping.