Tag Archives: SQL Server 2008 R2

Creating a Maintenance Plan for SQL Server 2008 R2 for vCenter/UpdateMgr/vCloud

Posted on by

I shall start by saying that I’m by no way a Database Administrator, but over the years I have picked up some knowledge and I have talked to a few guys that have more Knowledge on the topic to learn small tips & tricks. I have created in previous posts how to quickly create a vCenter Database using Transact-SQL scripts, and how to create a vCloud Director database using Transact-SQL script. It this small article, I will just resume how to create some Maintenance Plans to ensure that your vCenter/UpdateManager/vCloudDirector databases are backed up. I’m not using the Full Recovery model in SQL Server 2008 R2 for my lab and my clients, so these maintenance jobs should be fine. I believe that if you have a large enough environment that is critical to your day-to-day operations, you should use the Full Recovery model, but you would then also have a real Database Administrator onsite that could manage, nurture and keep your databases in proper running condition.

I have seem my share of transaction log databases for VMware vCenter go haywire, such that the Roll-Up jobs are not running anymore (Check your History Log) and the transaction log databases explodes. My personal worse situation was last year at a client that didn’t check their database and the transaction log database run out of storage on the disk when it passed the 90GB. There are procedures on the VMware Knowledge Base on how to compact and roll-up these huge transaction database, but it takes a lot of time. In most cases, we cut out losses and just purge the transactions logs.

Coming back to my Maintenance plan. We will create to sets of database maintenance plans, one for the System Dababases and one for the User databases. I need to thank my friend Eric Krejci for showing me how to separate the two maintenance plans.

System Maintenance Plan

We need to connect to our database server using the SQL Server Management Studio program. And from the Management folder, select the Maintenance Plan and start the Wizard.

Start Maintenance Plan Wizard

The System databases is comprised of the Master, Model, MSDB and TempDB databases. These database don’t change much, but I will select to make a Twice Weekly maintenance and Backup Plan. Please note that the MSDB database contains all the Stored Procedures for your vCenter & Update Manager database.

Define Maintenace Plan

And let’s Schedule the Plan for two runs per week on Tuesday evening and Friday evening.

Job Schedule Properties

You can select any other pattern that you wish.I for one also use VMware Data Recovery 2.0 for making daily backup of my virtual machines, so I make sure that my  VMware Data Recovery schedule does not run on my databases between 23:00 and 01:00.

Now we can select the various Maintenance Tasks we want to run.

Select Maintenance Tasks

I have selected

  • Check Database Integrity
  • Shrink Database
  • Update Statistics
  • Clean Up History
  • Back Up Database (Full)
  • Maintenance Cleanup Task.

And I have changed their Order around on Select Maintenance Task Order step.

Select Maintenance Task Order

So we run

  1. Check Database Integrity
  2. Update Statistics
  3. Back Up Database (Full)
  4. Shrink Database
  5. Maintenance Cleanup Task
  6. Clean Up History

Now let’s configure the Maintenance Tasks – Define Check Database Integrity. I have selected for this first Maintenance Plan the System Databases.

Define Database Check Integrity Task – System Databases

We now Define Update Statistics Task for the System Databases

Define Update Statistics Task – System Databases

The next step is the definition of the back up job. Define Back Up Database (Full) Task. Please note that we have added the option to create a sub-directory for each database, and to verify the backup integrity. I have also modified the Backup File Extention to BAK_FULL_SYS so that we can make better use and more flexible backup cleanup maintenance job later in this article.

Define Back Up Database (Full) Task – System Databases

There is always a good discussion if you have enough Compute power to create a compressed backup or not.

Now that we have a good full backup for the system databases we can do some database shrinkage. Define Shrink Database Task.

Update 22/03/2013. Since I created this post, I’ve stopped using the Shrink task in the maintenance plan. I rather do it sparingly manually than automate it.

Define Shrink Database Task – System Databases

Now remember that we modified the Backup File Extension earlier. We we will now Define Maintenance Cleanup Task to erase all System Databases backups that are older than two weeks, and we will use the various sub-folders for the backups.

Define Maintenance Cleanup Task – System Databases

And  last we Define History Cleanup Task for the whole SQL Server 2008 R2 instance. I did not modify the settings of this tab. This Maintenance Task will cleanup the Backup and Restory History, the SQL Server Agent job history and the Maintenance Plan History.

Define History Cleanup Task

We will also save a copy the Maintenance Plan actions to a text file in the same directory as where the backup files are stored.

Select Report Option for Maintenance Plan

We now have a resume of the Maintenance Plan we can complete.

Maintenance Plan Wizard Complete

We see the new job in the Maintenance Plans section and the new job in the SQL Server Agent

Maintenance Plans & SQL Agent Jobs

 

User Maintenace Plan

We now attack the User Databases Maintenance Plan. We start our Maintenance Plan Wizard and start the definition of the plan properties. I’m creating a Maintenance Plan for the Users Databases that will create a Differential Back Up every day, and a Full Back Up on Friday.

Users Database Maintenance Plan Properties

I modify the Schedule so that the main part of this Maintenance Plan including the Full Back Up happens each Friday. I will then later add a subplan to do the Differential plan each day.

User Databases Maintenance Plan – Job Schedule

We now add the various Maintenance Tasks for our Users Databases.

Select Maintenance Tasks

I have selected

  • Check Database Integrity
  • Shrink Database
  • Rebuild Index
  • Update Statistics
  • Back Up Database (Full)
  • Maintenance Cleanup Task

And we Select Maintenance Task Order to move down the Shrink Database task after the Back Up Database (Full).

Select Maintenance Task Order

So we run

  1. Check Database Integrity
  2. Rebuild Index
  3. Update Statistics
  4. Back Up Database (Full)
  5. Shrink Database
  6. Maintenance Cleanup Task

The first Task to run is the Database Check Integrity Task where we select the Users Databases

Database Check Integrity Task – User Databases

We then Rebuild Index Task for the Users Databases

Rebuild Index Task – User Databases

We Define Update Statistics Task for the User Databases.

Update Statistics Task – User Databases

We now do the Back Up Database (Full) Task for the User Databases. Note that we use sub-directories for each database, we changed teh Backup File extionsion to BAK_FULL_USR and we verify the integrity of the backup.

Back Up Database (Full) Task – User Databases

Once we have the Full Back Up of the User Databases we can launch the Shrink Database Task.

Shrink Database Task – User Databases

We now setup the Maintenance Cleanup Task for the User Databases so that we keep only the last two weekly full backups.

Maintenance Cleanup Task – User Databases

And we save the Maintenance Plan Report to the job_history directory.

Maintenance Plan Report Path

We now have a complete Maintenace Plan ready.

User Databases Maintenance Plan Wizard Complete

This creates the new Maintenance Plan and the SQL Agent Job.

Maintenance Plan & SQL Agent Jobs

We now select to Modify the User Databases – MaintenancePlan

Modify User Databases Maintenance Plan

And let’s quickly rename the Subplan_1 to Subplan_Weekly in the Subplan menu.

Rename Subplan_1 to Subplan_Weekly

So we can now Add Subplan to this Maintenance Task

Add Subplan_Daily

And we edit the Job Schedule to run everyday but Friday at the same time.

Job Schedule Subplan_Daily

We will now drag and drop the Back Up Database Task into the Subplan_Daily

Back Up Database Task in Subplan_Daily

We now edit the Back Up Database Task

Edit Back Up Database Task

And we modify the Back Up Database Task for Differential Jobs, we also make sure the backups are written in their correct directories, that they are verified, and that the Backup File Extension is BAK_DIFF_USR.

Back Up Database Task – User Databases – Differential Job

We now add the Maintenance Cleanup Task to this Subplan_Daily job and Linked it to the Back Up Database Task.

Add Maintenance Cleanup Task

And we will edit the Maintenance Cleanup Task so that we erase the old BAK_DIFF_USR files.

Maintenance Cleanup Task 1 – Backup Files

We add a 2nd Maintenance Cleanup Task to clean up the old text reports that are older than 4 weeks.

Maintenance Cleanup Task 2 – Text Reports

We are now done with the User Databases Maintenance Plan. Do NOT forget to SAVE the Maintenace Plan before quiting it.

We now have two specific SQL Server Agent Jobs.

SQL Server Agent Jobs

 

We will now run the Maintenace Plan Jobs. We start with the System Database job using Start Job at Step…

Running Maintenace Plan Jobs – System Databases

And for the User Databases we will first start the Full Back Up Task, before doing the Differential Back Up Task.

Running Maintenance Plan – User Database – Subplan_Weekly

Running Maintenance Plan – User Database – Subplan_Daily

When we check the Backup folder we now have a full back of the System Databases and User Databases (Full and Differential).

vCenter Server Backup Full and Diff

There you are with a Maintenance Plan for the SQL Server 2008 R2 running your vCenter, Update Manager and vCloud Director databases.

I hope this will help you.

I have to thank once more my friend Eric Krejci as we have discussed this topic extensively a few months ago and he already wrote the same article on vCenter and SQL Backup and Maintenance on his web blog.

Create vCenter database quickly with Transact-SQL

Posted on by

Creating new databases for VMware vCenter is something I have to do over and over again. I use mostly Microsoft SQL Server 2008 R2 so here are six quick procedures to simplify the creation and make all your vCenter databases to the same standard. I keep my Transact-SQL scripts in Evernote, so I just need to make six Copy & Paste and my vCenter database is created within 3 minutes. You can find the Transact-SQL to download at the bottom of this post.

My general rule when I create the VMware vCenter database is to have my user database on a separate disk from the operating system. This disk is formatted with 64K block size. SQL Server works with two specific IO request size 8K and 64K in general, so having 64K block size is optimum for SQL Server databases (See Disk partition alignment Best Practice for SQL Server ). I usually create a directory path for my SQL database D:\Microsoft SQL Server in which I will create two directories for the vCenter databses, vcenter-server and vcenter-update-manager.

Microsoft SQL Server directory structure for User Databaes

Using the Microsoft SQL Server Management Studio interface we can start a New Query, in which we will add the Transact-SQL code.

SQL Server Management Studio – Open a New Query

Now let’s insert the Transact-SQL script to create the new vcenter-server database. My database settings limit the database to grow past 16GB, and increases the database as it grows by blocks of 512MB. The initial size starts at 1GB. The code below is a bit wide for this blog, but you can find the full Transact-SQL code at the bottom.

USE [master]
GO
CREATE DATABASE [vcenter-server] on PRIMARY
(NAME = N’vcenter-server’, FILENAME = N’D:\Microsoft SQL Server\vcenter-server\vcenter-server.mdf’, SIZE = 1024MB, MAXSIZE = 16384MB, FILEGROWTH = 512MB)
LOG ON
(NAME = N’vcenter-server_log’, FILENAME = N’D:\Microsoft SQL Server\vcenter-server\vcenter-server.ldf’, SIZE = 512MB, MAXSIZE = 2048MB, FILEGROWTH = 256MB)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

vCenter SQL Database creation with settings

Lets now change the Recovery mode of our database for our needs, to Simple.

USE [vcenter-server]
GO
ALTER DATABASE [vcenter-server] SET RECOVERY SIMPLE;
GO

vCenter SQL Database alter recovery mode to Simple

Lets create a dedicated vCenter database user such as vpxdb.

USE [vcenter-server]
GO
CREATE LOGIN [vpxdb] WITH PASSWORD = ‘insert-a-password-here’, DEFAULT_DATABASE = [vcenter-server], DEFAULT_LANGUAGE=[us_english], CHECK_POLICY=OFF
GO
CREATE USER [vpxdb] for LOGIN [vpxdb]
GO

SQL Database vpxdb user creation

Now we let the newly create database user connect to the vCenter database.

USE [msdb]
GO
CREATE USER [vpxdb] FOR LOGIN [vpxdb]
GO

SQL Database vpxdb user login for vCenter Database

We allow the newly create vpxdb database user have db_owner rights to the [MSDB] database, so that the user can create the SQL Agent jobs in SQL.

USE [msdb]
GO
EXEC sp_addrolemember N’db_owner’, N’vpxdb’
GO

SQL Database user vpxdb db_owner rights to MSDB

And last we change the ownership of the vCenter Database for the vpxdb user.

USE [vcenter-server]
GO
sp_addrolemember [db_owner],[vpxdb]
GO

SQL Database user vpxdb db_owner rights to vcenter-database

You can find the all the Transact-SQL code in this simple text file vCenter-SQL-TransactSQL-database.txt. If you want the same type of Transact-SQL script to help you setup the vCenter Update Manager database check out this text file vCenter-Update-Manager-SQL-TransactSQL-database.txt