{"id":1074,"date":"2013-03-08T14:03:21","date_gmt":"2013-03-08T13:03:21","guid":{"rendered":"http:\/\/www.bussink.ch\/?p=1074"},"modified":"2018-05-23T17:57:19","modified_gmt":"2018-05-23T15:57:19","slug":"vcenter-srm-5-1-database-creation-using-transact-sql","status":"publish","type":"post","link":"https:\/\/www.bussink.ch\/?p=1074","title":{"rendered":"vCenter SRM 5.1 database creation using Transact-SQL"},"content":{"rendered":"

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.<\/p>\n

I’m not too impressed by the description in the Site Recovery Manager 5.1 Installation and Configuration<\/a> documentation on page 20 as seen below…<\/p>\n

[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.<\/p>\n

Procedure<\/strong><\/p>\n

1 Select an authentication mode when you create the database instance.<\/p>\n

Option & Description<\/p>\n

Windows authentication. The database user account must be the same user account that you use to run the SRM service.<\/p>\n

SQL Authentication. Leave the default local system user.<\/p>\n

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.<\/p>\n

3 Create the SRM database user account.<\/p>\n

4 Grant the SRM database user account the bulk insert, connect, and create table permissions.<\/p>\n

5 Create the database schema. The SRM database schema must have the same name as the database user account.<\/p>\n

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]<\/p>\n

 <\/p>\n

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<\/a> ). 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.<\/p>\n

\"Microsoft<\/a>

Microsoft SQL Server directory structure for User Databases<\/p><\/div>\n

 <\/p>\n

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.
\nI 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.
\n<\/code><\/p>\n

 <\/p>\n

\n
[code]<\/div>\n
—\u00a0 Transact-SQL script to simplify the creation of the vCenter SRM 5.1 database<\/span><\/div>\n
— this script as been created to run with a SQL Server 2008 R2 SP2 (10.50.4000)<\/span><\/div>\n
—\u00a0 it should run without much changes on SQL Server 2012<\/span><\/div>\n
—\u00a0<\/span><\/div>\n
—\u00a0 Erik Bussink, Date created 08\/03\/2013<\/span><\/div>\n
—\u00a0 Twitter @ErikBussink<\/span><\/div>\n
—<\/span><\/div>\n
\u00a0<\/span><\/div>\n
— Let’s create the vcenter-srm database in the D:\\Microsoft SQL Server\\vcenter-srm\\<\/span><\/div>\n
\u00a0<\/span><\/div>\n
USE<\/span>\u00a0[master]<\/span><\/div>\n
GO<\/span><\/div>\n
CREATE<\/span>\u00a0DATABASE<\/span>\u00a0[vcenter-srm]<\/span>\u00a0on<\/span>\u00a0PRIMARY<\/span><\/div>\n
(<\/span>NAME<\/span>\u00a0=<\/span>\u00a0N’vcenter-srm’<\/span>,<\/span>\u00a0FILENAME<\/span>\u00a0=<\/span>\u00a0N’D:\\Microsoft SQL Server\\vcenter-srm\\vcenter-srm.mdf’<\/span>,<\/span>\u00a0SIZE<\/span>\u00a0=<\/span>\u00a064MB<\/span>\u00a0,<\/span>\u00a0MAXSIZE<\/span>\u00a0=<\/span>\u00a02048MB<\/span>,<\/span>\u00a0FILEGROWTH<\/span>\u00a0=<\/span>\u00a064MB<\/span>)<\/span><\/div>\n
LOG<\/span>\u00a0ON<\/span><\/div>\n
(<\/span>NAME<\/span>\u00a0=<\/span>\u00a0N’vcenter-srm_log’<\/span>,<\/span>\u00a0FILENAME<\/span>\u00a0=<\/span>\u00a0N’D:\\Microsoft SQL Server\\vcenter-srm\\vcenter-srm.ldf’<\/span>,<\/span>\u00a0SIZE<\/span>\u00a0=<\/span>\u00a032MB<\/span>\u00a0,<\/span>\u00a0MAXSIZE<\/span>\u00a0=<\/span>\u00a01024MB<\/span>,<\/span>\u00a0FILEGROWTH<\/span>\u00a0=<\/span>\u00a032MB<\/span>)<\/span><\/div>\n
COLLATE<\/span>\u00a0SQL_Latin1_General_CP1_CI_AS<\/span><\/div>\n
GO<\/span><\/div>\n
\u00a0<\/span><\/div>\n
— Let’s change some default settings for the [vcenter-srm] database<\/span><\/div>\n
\u00a0<\/span><\/div>\n
USE<\/span>\u00a0[vcenter-srm]<\/span><\/div>\n
GO<\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcenter-srm]<\/span>\u00a0SET<\/span>\u00a0RECOVERY<\/span>\u00a0SIMPLE<\/span>\u00a0;<\/span><\/div>\n
GO<\/span><\/div>\n
\u00a0<\/span><\/div>\n
— Let’s create the vCenter SRM database account<\/span><\/div>\n
\u00a0<\/span><\/div>\n
USE<\/span>\u00a0[vcenter-srm]<\/span><\/div>\n
GO<\/span><\/div>\n
CREATE<\/span>\u00a0LOGIN<\/span>\u00a0[srmdb]<\/span>\u00a0WITH<\/span>\u00a0PASSWORD<\/span>\u00a0=<\/span>\u00a0‘password’<\/span>,<\/span>\u00a0DEFAULT_DATABASE<\/span>\u00a0=<\/span>\u00a0[vcenter-srm]<\/span>,<\/span>\u00a0DEFAULT_LANGUAGE<\/span>\u00a0=<\/span>[us_english]<\/span>,<\/span>\u00a0CHECK_POLICY<\/span>=<\/span>\u00a0OFF<\/span><\/div>\n
GO<\/span><\/div>\n
CREATE<\/span>\u00a0USER<\/span>\u00a0[srmdb]<\/span>\u00a0for<\/span>\u00a0LOGIN<\/span>\u00a0[srmdb]<\/span>\u00a0WITH<\/span>\u00a0DEFAULT_SCHEMA<\/span>=<\/span>\u00a0[dbo]<\/span><\/div>\n
GO<\/span><\/div>\n
CREATE<\/span>\u00a0SCHEMA<\/span>\u00a0[srmdb]<\/span>\u00a0AUTHORIZATION<\/span>\u00a0[srmdb]<\/span><\/div>\n
GO<\/span><\/div>\n
ALTER<\/span>\u00a0USER<\/span>\u00a0[srmdb]<\/span>\u00a0WITH<\/span>\u00a0DEFAULT_SCHEMA<\/span>=<\/span>[srmdb]<\/span><\/div>\n
GO<\/span><\/div>\n
\u00a0<\/span><\/div>\n
—\u00a0 Lets modify the [srmdb] account to have the required server right and user rights<\/span><\/div>\n
\u00a0<\/span><\/div>\n
USE<\/span>\u00a0[vcenter-srm]<\/span><\/div>\n
EXEC<\/span>\u00a0master<\/span>\u00a0..<\/span>sp_addsrvrolemember<\/span>\u00a0@loginame<\/span>\u00a0=<\/span>\u00a0N’srmdb’<\/span>,<\/span>\u00a0@rolename<\/span>\u00a0=<\/span>\u00a0N’bulkadmin’<\/span><\/div>\n
GO<\/span><\/div>\n
sp_addrolemember<\/span>\u00a0[db_accessadmin]<\/span>,<\/span>\u00a0[srmdb]<\/span><\/div>\n
GO<\/span><\/div>\n
sp_addrolemember<\/span>\u00a0[db_backupoperator]<\/span>,<\/span>\u00a0[srmdb]<\/span><\/div>\n
GO<\/span><\/div>\n
sp_addrolemember<\/span>\u00a0[db_datareader]<\/span>,<\/span>\u00a0[srmdb]<\/span><\/div>\n
GO<\/span><\/div>\n
sp_addrolemember<\/span>\u00a0[db_datawriter]<\/span>,<\/span>\u00a0[srmdb]<\/span><\/div>\n
GO<\/span><\/div>\n
sp_addrolemember<\/span>\u00a0[db_ddladmin]<\/span>,<\/span>\u00a0[srmdb]<\/span><\/div>\n
GO<\/span><\/div>\n
sp_addrolemember<\/span>\u00a0[db_owner]<\/span>,<\/span>\u00a0[srmdb]<\/span><\/div>\n
GO<\/span><\/div>\n
sp_addrolemember<\/span>\u00a0[db_securityadmin]<\/span>,<\/span>\u00a0[srmdb]<\/span><\/div>\n
GO<\/span><\/div>\n

[\/code]<\/p>\n

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.<\/p>\n<\/div>\n

Create the vcenter-srm database<\/p>\n

\"Create<\/a>

Create the vcenter-srm database<\/p><\/div>\n

Modify the vcenter-srm database to put it in Single Recover mode<\/p>\n

\"Configure<\/a>

Configure vcenter-srm database<\/p><\/div>\n

Create the srmdb user account & schema & change schema owner<\/p>\n

\"Create<\/a>

Create srmdb user<\/p><\/div>\n

Modify the srmdb user account with rights and<\/p>\n

\"Modify<\/a>

Modify srmdb user<\/p><\/div>\n

And now we can check the user account with the proper rights.<\/p>\n

\"Validate<\/a>

Validate srmdb user rights<\/p><\/div>\n

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.<\/p>\n

I’ve created similar scripts to Create vCenter Server databases with Transact-SQL<\/a>, and Create vCloud Director database with Transact-SQL<\/a> .<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":1103,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[18,81,19,82],"_links":{"self":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts\/1074"}],"collection":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1074"}],"version-history":[{"count":26,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts\/1074\/revisions"}],"predecessor-version":[{"id":1938,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts\/1074\/revisions\/1938"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/media\/1103"}],"wp:attachment":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1074"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1074"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1074"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}