Sometimes, when you are a Virtualization’s specialist you might not be a DB specialist. Unfortunately vCenter uses DBs, meaning that you will have to deal with them one time or another.
Here I just wanted to give a little generic and easy to perform way for doing backup and maintenance for these DBs on MSSQL.
The following implementation works fine for a vCenter 4.1 or 5 infrastructure of 300 to 500 VMs.
Let’s agree on several points:
– vCenter 4.1 u2 already installed (preferred not already in production)
– MSSQL Standard 2008 R2 installed locally and used only for vCenter DBs with the SQL agent running
– The non-system databases will be saved once a day, kept for 7 days. The restore point in case of a disaster will be the last backup (recovery model to simple)
– We also want to take care of the system DBs
– We have 2 DBs. vCenter (vCenter_DB) and Update Manager (vupdatemanager_DB)
– The DBs will be saved locally on the server. The server is saved by tier backup software that saves it system files. It will save the backup of the DBs
– Backup compression is not enabled in this scenario. Check this post to figure out if you want/can enable it or not!
First let us take care of the system DBs that might be useful in case of a major crash of your server.
We will do all of this through the maintenance plans of MSSQL
On your vCenter’s MSSQL server launch the and logon onto your SQL
Once inside, extend the Management and right click on Maintenance Plan to start a new Maintenance Plan Wizard. (I told you I would use the easy way, to start at least…)
Enter a name, description and let the default “single schedule”
Click on change to actually set the schedule
We will let this maintenance run once a week. For this MSSQL used exclusively by vCenter it would be enough, of course other use of MSSQL would need to adapt this frequency
Select the following tasks
Personally I move the “Shrink” task right after the backup one and the “Clean History” task to the end.
Now let’s select the databases for every task
Select the System Databases
Do the same for the other tasks:
I will only do a rebuild task once a week. Here you can find a very good article on index rebuild/reorganize If you have an enterprise version of MSSQL you can rebuild them online.
Now on the backup task we still choose the system databases
We also create sub-dirs, verify the backup integrity and set the backup file extension to: bak_full_sys
select the system DBs for the shrink task.
To the maintenance cleanup task we will ask the plan to remove the backup files of the system databases older than 1 week. Of course you can set a higher retention period if you wish. Note that I specify the same file extension than the one set in the precedent step. Like that I won’t risk deleting other files than these
I let the Cleanup one to its defaults. This task clean the history for the backup, agent jobs and maintenance plan for the whole SQL server. It can be set only one time. More details here
Finally I want to have a detailed trace of the plan execution for troubleshooting purpose. I have a dedicated folder for this.
Click on finish and we are done.
Back in the studio you can now see 2 new elements:
Now just before doing the plan for the user databases I will check that the recovery model of my databases is set to simple. This means that the only point in time of restore is the last backup.
In the studio, extend the databases and right click -> properties on each of your user databases:
Go in the “options” page and select the recovery model to “Simple”
Do the same with the others.
Once the recovery model set let’s do the plan for the vCenter DBs. This one will be made in 2 steps.
First name and description
Then we set the schedule. In this step we will set the weekly one
Let’s choose the tasks. (we won’t select the clean Up History again because it is already present in the system’s DB plan)
Now like for the system plan we will choose the user databases
Like with the system DBs I will only do a rebuild task once a week. Here you can find a very good article on index rebuild/reorganize If you have an enterprise version of MSSQL you can rebuild them online.
We also create sub-dirs, verify the backup integrity and set the backup file extension to: bak_full_usr
we set the shrink task
To the maintenance cleanup task we will ask the plan to remove the backup files of the user databases older than 1 week. Of course you can set a higher retention period if you wish. Note that I specify the same file extension than the one set in the precedent step. Like that I won’t risk deleting other file than these
Finally I want to have a detailed trace of the plan execution for troubleshooting purpose. I have a dedicated folder for this.
Back to the studio we see the new plan. We are not finish yet. Let’s right click and modify it
We will add a new subplan to make our daily backups for the user databases.
Click on
Let’s enter a name and description
And set a schedule (daily at 10:00 PM)
Now we will add the necessary tasks
Drag and drop from the toolbox a
Edit it
Choose the type -> Differential; the databases -> user DBs; don’t forget the extension “bak_diff_usr“; the verify and the subdir
We link the backup task to the maintenance one
And edit it
Let’s add again a , link it to the previous task and edit it
Now let’s select the type “Maintenance Plan Text Reports”, go to the output report folder. I personally use retention of 4 weeks because it gives a deeper history of what happened in case of troubleshooting.
before exiting, we will rename the default Subplan_1. double click on it and rename it to a more explicit name.
We are all set just save the plan.
If you go the SQL Server Agent -> Jobs; you will see the 2 jobs named after the subplan you have just added and renamed.
Now let’s try these.
First we will do the system ones:
Right click -> Start Job at Step on the job
Once complete you can see that on the backup repository new folders have been created
With backup devices inside
In the history repository you will also see the report
Now let’s do the same with the user databases Job.
Be careful to run the first. It is on this one that the full backup is set and we want to have a full backup of the databases before running differential ones. Be also certain not to run this plan during the off peak hours of your vCenter. The plan will drain its performances when it runs the different tasks such as the index defrag.
Once complete you can see that on the backup repository new folders have been created
With backup devices inside
In the history repository you will also see the report
Now let’s just try the last one to be certain it will run successfully.
If we go in the user databases repository we will see the new differential backup file
In the history repository you will also see the report
Voilà you have now maintenance not only for your vCenter databases but also for the system databases of your MSSQL.
I want to sincerely thank @ErikBussink for its comments and remarks about this post.
Works like a charm for me.
Only having issues getting my job history to be saved in the Job_History directory.
Hiya Eric,
It took some time, but I have also create an identical article on my own website about the Backup and Maintenance Plan. I have to thank you for the discussions and the idea behind this article which goes back to November 2011. Thanks a lot.
Always a pleasure