Pre Requisites

• This blog will target an existing Microsoft SQL 2008 R2 Server
• Make sure you are able to log into SQL Management Studio
• vCenter 5 installer for obtaining the script which will set this all up automatically
• vSphere Installation and Setup Guide. Page 176 onwards

Instructions

• Log into your SQL Server and run SQL Management Studio as a System Admin
• Attach your vCenter Installer ISO to your SQL DB VM and navigate to DVDdrive/vpx/dbschema or DVDrive/vCenter Server/dbschema
• Copy the DB_and_schema_creation_scripts_MSSQL.txt to your desktop

• You now need to run through this script and customize the location of the data and log files and the user account and password if you wish
• The vpxuser that is created by this script is not subject to any security policy. Change the passwords as appropriate. The vpxuser will have DBO Privileges on both the VCDB and the MSDB databases.
• Logon to a Query Analyzer session with the sysadmin (SA) or a user account with sysadmin privileges and run the following script once amended. Note that I haven’t changed the locations, everything is stored on C:\ as I am only testing and change the vpxuser password
• A more detailed breakdown is detailed below the script

use [master]
go
CREATE DATABASE [VCDB] ON PRIMARY
(NAME = N’vcdb’, FILENAME = N’C:\VCDB.mdf’ , SIZE = 20000KB , FILEGROWTH = 10% )
LOG ON
(NAME = N’vcdb_log’, FILENAME = N’C:\VCDB.ldf’ , SIZE = 10000KB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
go
use VCDB
go
sp_addlogin @loginame=[vpxuser], @passwd=N’UseaStrongPassword!’, @defdb=’VCDB’, @deflanguage=’us_english’
go
ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go
CREATE SCHEMA [VMW]
go
ALTER USER [vpxuser] WITH DEFAULT_SCHEMA =[VMW]
go
–User should have DBO Privileges or VC_ADMIN_ROLE and VC_USER_ROLE database roles
sp_addrolemember @rolename = ‘db_owner’, @membername = ‘vpxuser’
go
if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = ‘VC_ADMIN_ROLE’)
CREATE ROLE VC_ADMIN_ROLE;
GRANT ALTER ON SCHEMA :: [VMW] to VC_ADMIN_ROLE;
GRANT REFERENCES ON SCHEMA :: [VMW] to VC_ADMIN_ROLE;
GRANT INSERT ON SCHEMA ::  [VMW] to VC_ADMIN_ROLE;
GRANT CREATE TABLE to VC_ADMIN_ROLE;
GRANT CREATE VIEW to VC_ADMIN_ROLE;
GRANT CREATE Procedure to VC_ADMIN_ROLE;
if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = ‘VC_USER_ROLE’)
CREATE ROLE VC_USER_ROLE
go
GRANT SELECT ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT INSERT ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT DELETE ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT UPDATE ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT EXECUTE ON SCHEMA :: [VMW] to VC_USER_ROLE
go
sp_addrolemember VC_ADMIN_ROLE , [vpxuser]
go
sp_addrolemember VC_USER_ROLE , [vpxuser]
go
use MSDB
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go
–User should have DBO Privileges or VC_ADMIN_ROLE
sp_addrolemember @rolename = ‘db_owner’, @membername = ‘vpxuser’
go
if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = ‘VC_ADMIN_ROLE’)
CREATE ROLE VC_ADMIN_ROLE;
go
grant select on msdb.dbo.syscategories to VC_ADMIN_ROLE
go
grant select on msdb.dbo.sysjobsteps to VC_ADMIN_ROLE
go
GRANT SELECT ON msdb.dbo.sysjobs to VC_ADMIN_ROLE
GO
GRANT EXECUTE ON msdb.dbo.sp_add_job TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_update_job TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_category TO VC_ADMIN_ROLE
go
sp_addrolemember VC_ADMIN_ROLE , [vpxuser]
go

A breakdown of the script

This DB_and_schema_creation_scripts_MSSQL.txt file describes how to use optional scripts to create a Microsoft SQL database for vCenter Server and to create the database schema. If you do not use these scripts, you can create the database manually and allow the vCenter Server installer to create the database schema.

To prepare a SQL Server database to work with vCenter Server, you generally need to create a SQL Server database user with database operator (DBO) rights. When you do this, you must make sure that the database user login has the db_owner fixed database role on the vCenter Server database and on the MSDB database. (The db_owner role on the MSDB database is required for installation and upgrade only. You can revoke this role after the installation or upgrade process is complete.) The purpose of granting DBO permissions to the vCenter Server database user is to enable the vCenter Server installer to create the vCenter Server database schema.

For environments in which the user cannot have DBO permissions on the vCenter Server database, you can instead run scripts that create the vCenter Server database schema before you run the vCenter Server installer.

You can use the DB_and_schema_creation_scripts_MSSQL.txt script to create a database, user, and permissions for successful installation of vCenter Server.

• The first part of this script as listed below. (Highlights in blue where changes can be made)
• You must change the Password or you may get an error that the Password does not conform to the Password Complexity rules.(Highlighted in red on screenprint)
• Also I had to make the SIZE=20000KB and 10000KB respectively as SQL would not let me create a DB with the original values in the script
• Paste the following into a SQL Management Studio Query Window and click Execute. (Highlighted in red on screenprint) See screenprint below script

use [master]
go
CREATE DATABASE [VCDB] ON PRIMARY
(NAME = N’vcdb‘, FILENAME = N’C:\VCDB.mdf’ , SIZE = 20000KB , FILEGROWTH = 10% )
LOG ON
(NAME = N’vcdb_log’, FILENAME = N’C:\VCDB.ldf’ , SIZE = 10000KB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
go
use VCDB
go
sp_addlogin @loginame=[vpxuser], @passwd=N’UseaStrongPassword!‘, @defdb=’VCDB’, @deflanguage=’us_english’
go
ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go

• You will see that this part of the script creates the VCDB Database and the user vpxuser under Security Logins and Databases > VCDB > Security > Users

• Next copy and paste the following script into a new SQL Query Windows

use VCDB
go
CREATE SCHEMA [VMW]
go
ALTER USER [vpxuser] WITH DEFAULT_SCHEMA =[VMW]
go

• Navigate to Databases > VCDB > Security > Users > vpxuser > Properties
• Check that VMW is the Default Schema for the vpxuser

• Next the vpxuser should have DBO Privileges or VC_ADMIN_ROLE and VC_USER_ROLE database roles
• Copy the script below into a new SQL Query Window and click Execute

sp_addrolemember @rolename = ‘db_owner’, @membername = ‘vpxuser‘
go

• It gives the vpxuser the db_owner role

• The rest of the script follows on as below

if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = ‘VC_ADMIN_ROLE’)
CREATE ROLE VC_ADMIN_ROLE;
GRANT ALTER ON SCHEMA :: [VMW] to VC_ADMIN_ROLE;
GRANT REFERENCES ON SCHEMA :: [VMW] to VC_ADMIN_ROLE;
GRANT INSERT ON SCHEMA ::  [VMW] to VC_ADMIN_ROLE;
GRANT CREATE TABLE to VC_ADMIN_ROLE;
GRANT CREATE VIEW to VC_ADMIN_ROLE;
GRANT CREATE Procedure to VC_ADMIN_ROLE;
if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = ‘VC_USER_ROLE’)
CREATE ROLE VC_USER_ROLE
go
GRANT SELECT ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT INSERT ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT DELETE ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT UPDATE ON SCHEMA ::  [VMW] to VC_USER_ROLE
go
GRANT EXECUTE ON SCHEMA :: [VMW] to VC_USER_ROLE
go
sp_addrolemember VC_ADMIN_ROLE , [vpxuser]
go
sp_addrolemember VC_USER_ROLE , [vpxuser]
go
use MSDB
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go
sp_addrolemember @rolename = ‘db_owner’, @membername = ‘vpxuser‘
go
if not exists (SELECT name FROM sysusers WHERE issqlrole=1 AND name = ‘VC_ADMIN_ROLE’)
CREATE ROLE VC_ADMIN_ROLE;
go
grant select on msdb.dbo.syscategories to VC_ADMIN_ROLE
go
grant select on msdb.dbo.sysjobsteps to VC_ADMIN_ROLE
go
GRANT SELECT ON msdb.dbo.sysjobs to VC_ADMIN_ROLE
GO
GRANT EXECUTE ON msdb.dbo.sp_add_job TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_update_job TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO VC_ADMIN_ROLE
go
GRANT EXECUTE ON msdb.dbo.sp_add_category TO VC_ADMIN_ROLE
go
sp_addrolemember VC_ADMIN_ROLE , [vpxuser]
go

• Run the scripts in sequence on the VCDB database.The objects created by these scripts need to be owned by the “dbo” user.
• Right click on VCDB in SQL Management Studio and select New Query
• Open the scripts one at a time in the query analyzer window and press F5 to execute each script in the order shown here.
• You can navigate to the vCenter installer folder from the SQL Server and literally just drag and drop the following files into a SQL Query window
• Important: Do this in order

• VCDB_mssql.SQL
• load_stats_proc_mssql.sql
• purge_stat1_proc_mssql.sql
• purge_stat2_proc_mssql.sql
• purge_stat3_proc_mssql.sql
• purge_usage_stats_proc_mssql.sql
• stats_rollup1_proc_mssql.sql
• stats_rollup2_proc_mssql.sql
• stats_rollup3_proc_mssql.sql
• cleanup_events_mssql.sql
• delete_stats_proc_mssql.sql
• upsert_last_event_proc_mssql.sql
• load_usage_stats_proc_mssql.sql
• TopN_DB_mssql.sql
• calc_topn1_proc_mssql.sql
• calc_topn2_proc_mssql.sql
• calc_topn3_proc_mssql.sql
• calc_topn4_proc_mssql.sql
• clear_topn1_proc_mssql.sql
• clear_topn2_proc_mssql.sql
• clear_topn3_proc_mssql.sql
• clear_topn4_proc_mssql.sql
• rule_topn1_proc_mssql.sql
• rule_topn2_proc_mssql.sql
• rule_topn3_proc_mssql.sql
• rule_topn4_proc_mssql.sql
• process_license_snapshot_mssql.sql
• process_temptable0_proc_mssql.sql
• process_temptable1_proc_mssql.sql
• process_temptable2_proc_mssql.sql

You can also run the following scripts to enable database health monitoring.

• job_dbm_performance_data_mssql.sql
• process_performance_data_mssql.sql

• Grant the execute privilege for all the store procedures you created to the vCenter Server database user you created (vpxuser)

• grant execute on purge_stat1_proc to vpxuser
• grant execute on purge_stat2_proc to vpxuser
• grant execute on purge_stat3_proc to vpxuser
• grant execute on purge_usage_stat_proc to vpxuser
• grant execute on stats_rollup1_proc to vpxuser
• grant execute on stats_rollup2_proc to vpxuser
• grant execute on stats_rollup3_proc to vpxuser
• grant execute on cleanup_events_tasks_proc to vpxuser
• grant execute on delete_stats_proc to vpxuser
• grant execute on upsert_last_event_proc to vpxuser
• grant execute on load_usage_stats_proc to vpxuser
• grant execute on load_stats_proc to vpxuser
• grant execute on calc_topn1_proc to vpxuser
• grant execute on calc_topn2_proc to vpxuser
• grant execute on calc_topn3_proc to vpxuser
• grant execute on calc_topn4_proc to vpxuser
• grant execute on clear_topn1_proc to vpxuser
• grant execute on clear_topn2_proc to vpxuser
• grant execute on clear_topn3_proc to vpxuser
• grant execute on clear_topn4_proc to vpxuser
• grant execute on rule_topn1_proc to vpxuser
• grant execute on rule_topn2_proc to vpxuser
• grant execute on rule_topn3_proc to vpxuser
• grant execute on rule_topn4_proc to vpxuser
• grant execute on process_license_snapshot_proc to vpxuser
• grant execute on process_temptable0_proc tovpxuser
• grant execute on process_temptable1_proc tovpxuser
• grant execute on process_temptable2_proc tovpxuser
• grant execute on process_performance_data_proc to vpxuser
• grant execute on process_performance_data_mssql.sql to vpxuser

• For all supported editions of Microsoft SQL Server (except Microsoft SQL Server 2005 Express), ensure that the SQL Agent is running. Run these additional scripts to set up scheduled jobs on the database.
• Right click the VCDB DB and drag the below scripts into the query window and execute. These scripts ensure that the SQL Server Agent service is running.

• job_schedule1_mssql.sql
• job_schedule2_mssql.sql
• job_schedule3_mssql.sql
• job_cleanup_events_mssql.sql
• job_topn_past_day_mssql.sql
• job_topn_past_week_mssql.sql
• job_topn_past_month_mssql.sql
• job_topn_past_year_mssql.sql
• job_property_bulletin_mssql.sql

Create an ODBC Connection

• On your vCenter Server system, select Settings > Control Panel > Administrative Tools > Data Sources (ODBC).
• Click the System DSN tab and do one of the following.

To modify an existing SQL Server ODBC connection, select the connection from the System Data
Source list and click Configure.
To create a new SQL Server ODBC connection, click Add, select SQL Native Client, and click
Finish.

• Type an ODBC datastore name (DSN) in the Name text box. “VMware vCenter Server”
• (Optional) Type an ODBC DSN description in the Description text box.
• Select the server name from the Server drop-down menu. Type the SQL Server host name in the text box if it is not in the drop-down menu.
• Select one of the authentication methods.
• Integrate Windows authentication. Optionally, enter the Service Principal Name (SPN).
• SQL Server authentication. Type your SQL Server login name and password.
• Select the database created for the vCenter Server system from the Change the default database to menu.
• Click Finish.
  For SQL Server 2005 and SQL Server 2008 editions, test the data source by selecting Test Data Source and clicking OK from the ODBC Microsoft SQL Server Setup menu.
• Verify that the SQL Agent is running on your database server.

Run the vCenter Installer in the vCenter Server

• Run the vCenter Server installer and, when prompted, provide the database user login.

Youtube Video

Courtesy of Wee Kiong Tan