vSphere 5.5 Installation with MS SQL Overview

It has been sometime that I last did any installation and decide to rebuild my home lab.  I will skip the installation or upgrade of ESX server as this is pretty straight forward either using VMware vCenter Update Manager or via booting up from CD.

I will document the vCenter components as well as the databases requirements.  The SQL query are all tested during my installation.  I have record a simple video to show that the SQL query are error free and allows vCenter to install without any errors.

I might miss out some stuff here since I am not a DB guy or a SQL expert so to speak.

• vCenter
• 64bit DSN after user and rights assigned
• Check out support Database from here
• Adobe Flash required for machine access web client 

Similarly to what I have documented year before on vCenter 5.1 here, the SQL scripts did not change other than the requirements of SSO database is no longer required.

Extracted from vCenter installation guide which can be found in the documetation site.

The script is located in the vCenter Server installation package (ISO) at //vCenter- 
Server/dbschema/DB_and_schema_creation_scripts_MSSQL.txt

1) Change the location of the data and log files you would like to store in the create database script.

2) The user created by this script does not follow any security policy. The passwords are provided only for convenience. Please change the passwords as appropriate.

3) The user created will have DBO Privileges on both VCDB and and MSDB databases or will have VC_ADMIN_ROLE on both VCDB and MSDB and VC_USER_ROLE in VCDB.

4) You can create the database before hand then you can skip the database creation portion of the query below.

5) You would need to install dot net  framework 3.5 installed for SQL.  You can just add feature in the server else you will see this error.  Just add in without quitting the SQL installation and click back and forward to get a green passed.

Logon to a Query Analyzer session with the sysadmin (SA) or a user account with sysadmin privileges and run the following script.

Database name: VCDB

Schema name: VMW

Database location: C:\VCDB.mdf (make sure the user have permission to the path)

Database log location: C:\VCDB.ldf
(make sure the user have permission to the path)

VCDB user: vpxuser

I added in Recovery method in this script marked in highlight. You can choose between Full or Simple.

use [master] go CREATE DATABASE [VCDB] ON PRIMARY (NAME = N'vcdb', FILENAME = N'C:\VCDB.mdf' , SIZE = 4000KB , FILEGROWTH = 10% ) LOG ON (NAME = N'vcdb_log', FILENAME = N'C:\VCDB.ldf' , SIZE = 1000KB , FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS go ALTER DATABASE [VCDB] SET RECOVERY SIMPLE  /*or FULL*/  go  use VCDB go sp_addlogin @loginame=[vpxuser], @passwd=N'vpxuser', @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

Please note the size of the database needs to change to 4000KB (default 3000KB) if you are using SQL 2012 else you will encounter this error when  you run the script above.

User should have DBO Privileges on VMW database or create a VC_ADMIN_ROLE and grant specific rights.  There are two scripts so use only one of them.  First one just grant the dbo rights to vpxuser while the other create a VC_ADMIN_ROLE.

use VCDB go sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser' go OR 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

User should have DBO Privileges on MSDB or create a VC_ADMIN_ROLE in MSDB and grant specific rights only.  Similar to above.

use MSDB go CREATE USER [vpxuser] for LOGIN [vpxuser] go sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser' go OR 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

Some company policy would not allowed dbo user on MSDB or VCDB, in such, the below query will remove that.

Remove VC_ADMIN_ROLE from vpxuser on VCDB

use VCDB go sp_droprolemember VC_ADMIN_ROLE , [vpxuser] go

Remove dbo role from vpxuser on MSDB 

use MSDB go sp_droprolemember @rolename = 'db_owner', @membername = 'vpxuser' go