Tag Archives: 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.

 

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