Tag Archives: Transact-SQL

vCenter SRM 5.1 database creation using Transact-SQL

Posted on by

I’ve created a simple Microsft SQL Server Transact-SQL script to configure the database for vCenter Site Recovery Manager (SRM) 5.1 . This allows you to quickly create the database on the primary site and the recovery site.

I’m not too impressed by the description in the Site Recovery Manager 5.1 Installation and Configuration documentation on page 20 as seen below…

[box] This information provides the general steps that you must perform to configure an SQL Server database for SRM to use. For specific instructions, see the SQL Server documentation.

Procedure

1 Select an authentication mode when you create the database instance.

Option & Description

Windows authentication. The database user account must be the same user account that you use to run the SRM service.

SQL Authentication. Leave the default local system user.

2 If SQL Server is installed on the same host as SRM Server, you might need to deselect the Shared Memory network setting on the database server.

3 Create the SRM database user account.

4 Grant the SRM database user account the bulk insert, connect, and create table permissions.

5 Create the database schema. The SRM database schema must have the same name as the database user account.

6 Set the SRM database user as the owner of the SRM database schema. 7 Set the SRM database schema as the default schema for the SRM database user.[/box]

 

My general rule when I create a SQL Server 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 the directories for the vCenter databases, vcenter-sso, vcenter-server, vcenter-update-manager and vcenter-srm.

Microsoft SQL Server directory structure for User Databases

Microsoft SQL Server directory structure for User Databases

 

Now let’s insert the Transact-SQL script to create the vcenter-srm database. My database settings limits the database to grow past 2GB, and increases the database as it grows by blocks of 64MB. The initial size starts at 64MB.
I recommend that you cut & paste the following Transact-SQL script into the SQL Server Management Studio and then select the sections to execute them one after another.

 

[code]
—  Transact-SQL script to simplify the creation of the vCenter SRM 5.1 database
— this script as been created to run with a SQL Server 2008 R2 SP2 (10.50.4000)
—  it should run without much changes on SQL Server 2012
— 
—  Erik Bussink, Date created 08/03/2013
—  Twitter @ErikBussink
—
 
— Let’s create the vcenter-srm database in the D:\Microsoft SQL Server\vcenter-srm\
 
USE [master]
GO
CREATE DATABASE [vcenter-srm] on PRIMARY
(NAME = N’vcenter-srm’, FILENAME = N’D:\Microsoft SQL Server\vcenter-srm\vcenter-srm.mdf’, SIZE = 64MB , MAXSIZE = 2048MB, FILEGROWTH = 64MB)
LOG ON
(NAME = N’vcenter-srm_log’, FILENAME = N’D:\Microsoft SQL Server\vcenter-srm\vcenter-srm.ldf’, SIZE = 32MB , MAXSIZE = 1024MB, FILEGROWTH = 32MB)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
 
— Let’s change some default settings for the [vcenter-srm] database
 
USE [vcenter-srm]
GO
ALTER DATABASE [vcenter-srm] SET RECOVERY SIMPLE ;
GO
 
— Let’s create the vCenter SRM database account
 
USE [vcenter-srm]
GO
CREATE LOGIN [srmdb] WITH PASSWORD = ‘password’, DEFAULT_DATABASE = [vcenter-srm], DEFAULT_LANGUAGE =[us_english], CHECK_POLICY= OFF
GO
CREATE USER [srmdb] for LOGIN [srmdb] WITH DEFAULT_SCHEMA= [dbo]
GO
CREATE SCHEMA [srmdb] AUTHORIZATION [srmdb]
GO
ALTER USER [srmdb] WITH DEFAULT_SCHEMA=[srmdb]
GO
 
—  Lets modify the [srmdb] account to have the required server right and user rights
 
USE [vcenter-srm]
EXEC master ..sp_addsrvrolemember @loginame = N’srmdb’, @rolename = N’bulkadmin’
GO
sp_addrolemember [db_accessadmin], [srmdb]
GO
sp_addrolemember [db_backupoperator], [srmdb]
GO
sp_addrolemember [db_datareader], [srmdb]
GO
sp_addrolemember [db_datawriter], [srmdb]
GO
sp_addrolemember [db_ddladmin], [srmdb]
GO
sp_addrolemember [db_owner], [srmdb]
GO
sp_addrolemember [db_securityadmin], [srmdb]
GO

[/code]

Once you have uploaded the script you can execute the script step by step, by selecting the paragraph you want to run, then executing just the selected code.

Create the vcenter-srm database

Create the vcenter-srm database

Create the vcenter-srm database

Modify the vcenter-srm database to put it in Single Recover mode

Configure vcenter-srm database

Configure vcenter-srm database

Create the srmdb user account & schema & change schema owner

Create srmdb user

Create srmdb user

Modify the srmdb user account with rights and

Modify srmdb user

Modify srmdb user

And now we can check the user account with the proper rights.

Validate srmdb user rights

Validate srmdb user rights

I hope that you can now see how a simple well written Transact-SQL script can save you time & errors when creating the Primary and Recovery site’s databases.

I’ve created similar scripts to Create vCenter Server databases with Transact-SQL, and Create vCloud Director database with Transact-SQL .

 

 

 

SQL Server Transact-SQL Line Numbering

Posted on by

When you use Microsoft SQL Server on a regular basis with Transact-SQL scripts, it is a sometimes useful to quickly find the proper line of code.

This is a quick tip on how to activate the Line Numbering when working with Transact-SQL.

Activating Line Numbering for Transact-SQL

Activating Line Numbering for Transact-SQL

I hope this will be helpful.

 

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

vCloud Director 1.5 database creation using Transact-SQL

Posted on by

In the past few weeks I have had to reinstall and clean up the vCloud Director 1.5 database on SQL Server 2008 R2. After a few times doing it using the SQL Server Management Studio GUI, I decided to automated it using four simple Transact-SQL scripts, so it would save me time and make it less error prone, and to better document it. I did modify the Transact-SQL part for the ALTER Database section, and I’m using a Simple Recovery mode for my database.

Create [vcloud] database
USE [master]
GO
CREATE DATABASE [vcloud] on PRIMARY
(NAME = N’vcloud’, FILENAME = N’D:\Microsoft SQL Server\vcloud-director\vcloud.mdf’, SIZE = 1024MB, MAXSIZE = 16384MB, FILEGROWTH = 512MB)
LOG ON
(NAME = N’vcloud_log’, FILENAME = N’D:\Microsoft SQL Server\vcloud-director\vcloud.ldf’, SIZE = 128MB, MAXSIZE = 2048MB, FILEGROWTH = 128MB)
COLLATE Latin1_General_CS_AS
GO

SQL vCD Database – Create vcloud database

Alter [vcloud] Database
This is the step 4 on Page 17 for the vCloud Director 1.5 Installation and Configuration Guide.
VMware Version:
USE [vcloud]
GO
ALTER DATABASE [vcloud] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [vcloud] SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE [vcloud] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
ALTER DATABASE [vcloud] SET MULTI_USER;
GO
My modified version with the database in Simple Recovery mode.
USE [vcloud]
GO
ALTER DATABASE [vcloud] SET RECOVERY SIMPLE;
ALTER DATABASE [vcloud] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [vcloud] SET ALLOW_SNAPSHOT_ISOLATION ON;
EXEC sp_addextendedproperty @name = N’ALLOW_SNAPSHOT_ISOLATION’, @value = ‘ON’;
ALTER DATABASE [vcloud] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
EXEC sp_addextendedproperty @name = N’READ_COMMITTED_SNAPSHOT’, @value = ‘ON’;
ALTER DATABASE [vcloud] SET MULTI_USER;
GO

SQL vCD Database – Alter vcloud database


Create user vcddb
USE [vcloud]
GO
CREATE LOGIN [vcddb] WITH PASSWORD = ‘PASSWORD’, DEFAULT_DATABASE = [vcloud], DEFAULT_LANGUAGE=[us_english], CHECK_POLICY=OFF
GO
CREATE USER [vcddb] for LOGIN [vcddb]
GO

SQL vCD Database – Creat vcddba account

Modify user to add db_owner Role 
USE [vcloud]
GO
sp_addrolemember [db_owner],[vcddb]
GO
SQL vCD Database - Add db_owner role to vcddba