A customer called me yesterday asking for a script to create the necessary databases to run:
- vCenter Server
- vCenter Single Sign On
- vCenter Update Manager
- vCloud Director
The customer was about to perform a clean install in their test environment and will be using vCenter Server 5.1 U1, ESXi 5.1 U1 and vCloud Director (vCD) 5.1.2. vCenter Chargeback and and vCenter orchestrator will not be used in this test environment.
The following requirements exists:
- MSSQL must be used as database engine.
- MSSQL user authentication and no domain user authentication must be used.
- Place the database data files on E:.
- Place the database log files on F:.
- Each database must use simple recovery mode.
- The initial file size should be 1024 MB for both database data and database log files.
- The file growth should be set to 200 MB for both database data and database log files.
- The databases must all use the numerical value of 03 in the end of the name.
- The databases users must all be created using the numerical value of 03 in the end of the name.
I have used a combination of the database creation examples found in the VMware installation guides, my own suggestions and the customer requirements when creating the below scrip. The example in the vCloud Director 5.1 Installation and Configuration guide found here can not be used in its entirety because it sets the database recovery model to Full.
Run the below MSSQL script to create the required databases and make sure you get the correct characters when copy the script, e.g the lines starting with — defines a MSSQL query comment and is equal to two minus characters.
I know more or less everyone got different preferred database creation parameters so change the the parameters to fit your environment and remember the below script will be used for a vSphere and vCloud Director test environment.
— Create vCenter Single Sign On Database
USE MASTER
GO
CREATE DATABASE MGRSSO03 ON PRIMARY(
NAME=’RSA_DATA’,
FILENAME=’e:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAMGRSSO03_DATA.mdf’, SIZE=1024MB, MAXSIZE=UNLIMITED, FILEGROWTH=10%),
FILEGROUP RSA_INDEX(
NAME=’RSA_INDEX’,
FILENAME=’e:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAMGRSSO03_INDEX.ndf’, SIZE=1024MB, MAXSIZE=UNLIMITED, FILEGROWTH=200MB)
LOG ON(
NAME=’RSA_log’,
FILENAME=’f:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataMGRSSO03.ldf’, SIZE=1024MB, MAXSIZE=UNLIMITED, FILEGROWTH=200MB )
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
alter database “MGRSSO03” set recovery simple
GO
CHECKPOINT
—
— Create vCenter Single Sign On Database users
USE MASTER
GO
CREATE LOGIN MGRuserSSO03_DBA WITH PASSWORD = ‘notsecret’, DEFAULT_DATABASE = MGRSSO03
GO
CREATE LOGIN MGRuserSSO03_USER WITH PASSWORD = ‘notsecret’, DEFAULT_DATABASE = MGRSSO03
GO
USE MGRSSO03
GO
ALTER AUTHORIZATION ON DATABASE::MGRSSO03 TO [MGRuserSSO03_DBA]
GO
CREATE USER MGRuserSSO03_USER FOR LOGIN [MGRuserSSO03_USER]
GO
CHECKPOINT
GO
—
— Create vCenter Server DB
use [master]
go
CREATE DATABASE “MGRvCenter03”
ON
(NAME = ‘MGRvCenter03_data’,
FILENAME = ‘e:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAMGRvCenter03.mdf’, SIZE = 1024MB, FILEGROWTH = 200MB)
LOG ON
(NAME = ‘MGRvCenter03_log’,
FILENAME = ‘f:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataMGRvCenter03.ldf’, SIZE = 1024MB, FILEGROWTH = 200MB)
COLLATE SQL_Latin1_General_CP1_CI_AS
go
alter database “MGRvCenter03” set recovery simple
go
—
—
— Create vCenter Server database user and add as DB-owner for vCenter Server DB and MSDB DB
use [MGRvCenter03]
go
sp_addlogin @loginame=[MGRuservcenter03], @passwd=N’notsecret’, @defdb=’MGRvCenter03′,
@deflanguage=’us_english’
go
CREATE USER [MGRuservcenter03] for LOGIN [MGRuservcenter03]
EXEC sp_addrolemember N’db_owner’, N’MGRuservcenter03′
go
use MSDB
go
CREATE USER [MGRuservcenter03] for LOGIN [MGRuservcenter03]
EXEC sp_addrolemember N’db_owner’, N’MGRuservcenter03′
go
—
— Create vCenter Update Manager Database
use [master]
go
CREATE DATABASE “MGRVUM03”
ON
(NAME = ‘MGRVUM03_data’,
FILENAME = ‘e:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAMGRVUM03.mdf’, SIZE = 1024MB, FILEGROWTH = 200MB)
LOG ON
(NAME = ‘MGRVUM03_log’,
FILENAME = ‘f:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataMGRVUM03.ldf’, SIZE = 1024MB, FILEGROWTH = 200MB)
COLLATE SQL_Latin1_General_CP1_CI_AS
go
alter database “MGRVUM03” set recovery simple
go
—
—
— Create vCenter Update Manager database user and add as DB-owner for vCenter Server DB and MSDB DB
use MGRVUM03
go
sp_addlogin @loginame=[MGRuservum03], @passwd=N’notsecret’, @defdb=’MGRVUM03′,
@deflanguage=’us_english’
go
ALTER LOGIN [MGRuservum03] WITH CHECK_POLICY = OFF
go
CREATE USER [MGRuservum03] for LOGIN [MGRuservum03]
EXEC sp_addrolemember N’db_owner’, N’MGRuservum03′
go
use MSDB
go
CREATE USER [MGRuservum03] for LOGIN [MGRuservum03]
EXEC sp_addrolemember N’db_owner’, N’MGRuservum03′
go
—
— Create vCloud Director Database
USE [master]
GO
CREATE DATABASE “MGRvCD03” ON PRIMARY
(NAME = N’MGRMGRvCD03_data’, FILENAME = N’e:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAMGRvCD03.mdf’, SIZE = 1024MB, FILEGROWTH = 200MB)
LOG ON
(NAME = N’MGRvCD03_log’, FILENAME = N’f:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataMGRvCD03.ldf’, SIZE = 1024MB, FILEGROWTH = 200MB)
COLLATE Latin1_General_CS_AS
GO
alter database “MGRvCD03” set recovery simple
go
—
— Create vCloud Director database user
USE [MGRvCD03]
GO
CREATE LOGIN [MGRuservCD03] WITH PASSWORD = ‘notsecret’, DEFAULT_DATABASE =[MGRvCD03],
DEFAULT_LANGUAGE =[us_english], CHECK_POLICY=OFF
GO
CREATE USER [MGRuservCD03] for LOGIN [MGRuservCD03]
EXEC sp_addrolemember N’db_owner’, N’MGRuservCD03′
GO
Make sure to remove the MSDB db_owner privileges for the vCenter Server and vCenter Update Manager users needed during the installation when the installation is finished by running the below MSSQL query/script.
— Remove users MGRuservcenter03 and MGRuservum03 from MSDB database
use MSDB
drop user [MGRuservcenter03]
go
use MSDB
drop user [MGRuservum03]
go
4 pings