Preparing / Creating VMware vSphere vCenter 5.0 database

I’ve been meaning to write an updated post to follow up on one of my previous ones:

Creating vCenter 4.1 SQL database and ODBC DSN Connection
http://terenceluk.blogspot.com/2010/10/creating-vcenter-41-sql-database-and.html

… to demonstrate setting up the database for vCenter 5.0 but I’ve been so busy that I only found the time now to dig up that email I had with the screenshots of the process which I had stashed in my Outlook’s draft box since June last year.  Anyways, the following demonstrates how to set the process of setting up database for a new install.

**Note: Unlike my post for vCenter 4.1, this post will demonstrate using a SQL Authentication account instead of a domain account.

As always, the process I’m about to demonstrate follows the vSphere Installation and Setup guide which can be found at the following link:

http://pubs.vmware.com/vsphere-50/topic/com.vmware.ICbase/PDF/vsphere-esxi-vcenter-server-50-installation-setup-guide.pdf

Creating the SQL Database on Microsoft SQL Server

The first step for setting up the database for your new vCenter 5.0 to use is to create the database on your Microsoft SQL Server so open up SQL Server Management Studio:

Start a new query with the New Query button at the top left hand corner:

This will bring up a new query window:

Now if we refer to page 177 in the installation guide (http://pubs.vmware.com/vsphere-50/topic/com.vmware.ICbase/PDF/vsphere-esxi-vcenter-server-50-installation-setup-guide.pdf) we see the following script:

use [master]

go

CREATE DATABASE [VCDB] ON PRIMARY

(NAME = N'vcdb', FILENAME = N'C:\VCDB.mdf', SIZE = 2000KB, 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

use VCDB

go

sp_addlogin @loginame=[vpxuser], @passwd=N'vpxuser!0', @defdb='VCDB',

@deflanguage='us_english'

go

ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF

go

CREATE USER [vpxuser] for LOGIN [vpxuser]

go

use MSDB

go

CREATE USER [vpxuser] for LOGIN [vpxuser]

From this point, we need to make a decision on the following:

1. Set Database Permissions By Manually Creating Database Roles and the VMW
   Schema
2. Set Database Permissions by Using the dbo Schema and the db_owner Database
   Role

The former takes a bit more time because you need to manually create the database roles and assign them to the service account while the latter simply assigns the dbo Schema and db_owner database role to your service account.  What’s interesting is that I believe option #1 was introduced in vCenter 5.0 because I don’t recall seeing this in the earlier deployment guides.  I’ve always used the 2nd option but it’s obviously not as secure because VMware does recommend option #1.  With that being said, I will only demonstrate option #2 as I for this particular deployment, I went with that route.

Since we won’t be using Windows Authentication (like my vCenter 4.1 post) and that we’ll be using SQL Authentication, we can pretty much just use the default script with the modifications highlighted in red:

use [master]

go

CREATE DATABASE [VCDB] ON PRIMARY

(NAME = N'vcdb', FILENAME = N'K:\Databases\VCDB.mdf', SIZE = 2000KB, FILEGROWTH = 10% )

LOG ON

(NAME = N'vcdb_log', FILENAME = N'L:\Logs\VCDB.ldf', SIZE = 1000KB, FILEGROWTH = 10%)

COLLATE SQL_Latin1_General_CP1_CI_AS

go

use VCDB

go

sp_addlogin @loginame=[vpxuser], @passwd=N'P@ssw0rd', @defdb='VCDB',

@deflanguage='us_english'

go

ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF

go

CREATE USER [vpxuser] for LOGIN [vpxuser]

go

use MSDB

go

CREATE USER [vpxuser] for LOGIN [vpxuser]

Continue by clicking on the Execute button and ensure that the Messages windows outputs the message: Command(s) completed successfully.

**Note: If you prefer to use a domain account with Windows Authentication, you can refer to my vCenter 4.1 post: http://terenceluk.blogspot.com/2010/10/creating-vcenter-41-sql-database-and.html.

Since we went with option #2 for this example, we will also need to execute the following:

use VCDB
go
sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'
go
use MSDB
go
sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'
go

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Alternatively, if you want to combine both of the transact-sql statements together, you can execute the following in one shot:

use [master]
go
CREATE DATABASE [VCDB] ON PRIMARY
(NAME = N'vcdb', FILENAME = N'K:\Databases\VCDB.mdf', SIZE = 2000KB, FILEGROWTH = 10% ) LOG ON
(NAME = N'vcdb_log', FILENAME = N'L:\Logs\VCDB.ldf', SIZE = 1000KB, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS
go
use VCDB
go
sp_addlogin @loginame=[vpxuser], @passwd=N'P@ssw0rd', @defdb='VCDB',
@deflanguage='us_english'
go
ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go
sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'
go
use MSDB
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go
sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Proceed with expanding the Databases node:

Then hit the F5 button to refresh the database and you should see the newly created VCDB database:

Creating the 64-bit ODBC DSN Connection

Open up the Start menu and navigate to Administrative Tools –> Data Sources (ODBC):

Navigate to the System DSN tab and click Add:

Make sure you click on the System DSN tab before you create the DSN!

A common mistake I’ve come across when troubleshooting DSN creation problems is that a SQL Server driver was created instead of the SQL Server Native Client 10.0. So make sure you select the latter and click Finish.

SQL Server = WRONG

SQL Server Native Client 10.0 = RIGHT

**Note: If you find that you’re missing the SQL Server Native Client 10.0 option, please see this blog post: http://terenceluk.blogspot.com/2010/10/vcenter-odbc-dsn-database-connection.html.

Fill in the following fields:

Name: This is just a logical name and can be anything you want.

Description: Logical description.

Server: You can either put the NetBIOS or FQDN of the server name. I personally prefer the FQDN.

Since we’re not using a domain service account, select the radio button for With SQL Server authentication using a login ID and password entered by the user and enter the credentials for the account you configured during the database creation:

Make sure you change the default database to your vCenter database that you created earlier. The name in the installation guide is VCDB:

Leave the following settings as default and click Finish:

A window is now presented that allows you to test the ODBC connection. Proceed with clicking on the Test Data Source button to test connecting over to the vCenter database with the SQL Authentication credentials entered in one of the previous screens:

Confirm that the test completes successfully:

Once you click finish, you will now see your new ODBC DSN:

Now when you go through the install of vCenter 5.0, you should see this DSN listed in the Data Source Name (DSN) option:

… and that’s it.  Hope this was as informative as my vSphere 4.1 blog post.