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 .