Cloud Lab-11: How to Maintain vCenter DB with SQL Maintenance Plan (Part 1)

If 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. i divided my maintenance plan into two parts. In first, i will show you how to make weekly full backup plan for vCenter DB and in second, will show you to make daily differentials backup plan for vCenter DB.

The following implementation works fine for a vCenter 5.x infrastructure.

LAB-11 TASKS (Part 1)

In this lab we will perform following tasks.

  1. VCenter is already installed
  2. MSQL Server is installed for VCenter DBs
  3. VCenter DBs are already created.
  4. SQL Agent service is running
  5. Full DBs Backup Will performed Weekly on Sunday

4-SQL Agent Service is runinng

In SQL Server machine go to “Control panel-> Administrative Tools->Services” and then verify SQL Agent service is running.

vcloudlab12-vcsqlmaintainence-1.1

5-Full DBs Backup Will performed Weekly on Sunday

We will do all of this through the maintenance plans of MSSQL

Lanuch vCenter’s MSSQL server Management Studio and logon onto your SQL. Once inside.

To create a new Maintenance plan Right Click on “Maintenance Plans” folder under “Management section” and choose “New Maintenance plan”

vcloudlab12-vcsqlmaintainence-1

Click “Next”

vcloudlab12-vcsqlmaintainence-2

Enter a name, description and Click on “Change” Button. Leave reset of the settings as default. 

vcloudlab12-vcsqlmaintainence-3

As we already discussed in our goals. We want to take full weekly backup of vCenter DB’s. Select the desired schedule settings and Click “OK” twice.

vcloudlab12-vcsqlmaintainence-4

Select the SQL Maintenance plan tasks -> Click “Next”

vcloudlab12-vcsqlmaintainence-5

Choose an Order for your tasks to performance. I have one or two changes, please set as you pleased -> Click “Next”

vcloudlab12-vcsqlmaintainence-6

Click on the drop drop to choose your databases for the tasks.

vcloudlab12-vcsqlmaintainence-7

As we want to backup our vCenter DB’s. There are two option to do this, either we choose “All User databases” options or let it choose from “These databases” option -> Click “OK”

vcloudlab12-vcsqlmaintainence-8

I will only do a rebuild task once a week. In enterprise version of MSSQL you can rebuild them online.  Make sure “All User databases” is selected in databases. Leave other as default -> Click “Next”

vcloudlab12-vcsqlmaintainence-9

Make sure “All User databases” is selected in databases. Leave other as default -> Click “Next”

vcloudlab12-vcsqlmaintainence-10

On backup task Choose “Full” backup of “All User databases”. Select your “Database folder path”. Check “create sub-dirs”. Click “verify the backup integrity” and set the backup file extension to bak_full -> Click “Next”

vcloudlab12-vcsqlmaintainence-11

Choose “All User databases” from dropdown. Leave reset as default -> Click “Next”

vcloudlab12-vcsqlmaintainence-12

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-> Click “Next”

vcloudlab12-vcsqlmaintainence-13

Choose “2 Weeks” retention time to clean the history file. 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-> Click “Next”

vcloudlab12-vcsqlmaintainence-14

Choose “write a report to text file” and save it . beacuase I want to have a detailed trace of the plan execution for troubleshooting purpose.-> Click “Next”

vcloudlab12-vcsqlmaintainence-15

Complete the Wizard tasks ->Click “Finish”

vcloudlab12-vcsqlmaintainence-16

Wait for task to create. Check the Status against the task it should be successful -> Click “Close”

vcloudlab12-vcsqlmaintainence-17

If we go into the sql management study under maintenance plan. Then you should see a plan task which we just created

vcloudlab12-vcsqlmaintainence-18

NOTE: I have set the recovery model of my vCenter databases into simple. This means that the only point in time of restore is the last backup.

We have only one task completed yet (week full backup of vCenter DB’s. Next will cover in my later post.). 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s