vCenter MSSQL DBs backup and maintenance

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

Now we add another task ->

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.