Creating VMware vCenter 5.1 SQL database and ODBC DSN Connection

As with my previous posts for creating vCenter 4.1 and 5.0 databases, this post serves to demonstrate how to set up the database for vCenter 5.1.  Before I begin, it’s important to note that the script to create the database actually hasn’t changed at all since the earlier versions but to save time for anyone looking for version specific instructions, I’ve decided to write this post.  As always, the process I’m about to demonstrate follows the vSphere Installation and Setup for VMware vSphere 5.1 guide which can be found at the following link:

http://pubs.vmware.com/vsphere-51/topic/com.vmware.ICbase/PDF/vsphere-esxi-vcenter-server-51-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.1 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 205 in the installation guide (http://pubs.vmware.com/vsphere-51/topic/com.vmware.ICbase/PDF/vsphere-esxi-vcenter-server-51-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]
go

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 here: http://terenceluk.blogspot.com/2010/10/creating-vcenter-41-sql-database-and.html) 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'

Creating the 64-bit ODBC DSN Connection

Open up the Start menu, navigate to Administrative Tools –> Data Sources (ODBC) then select the System DSN tab and click Add:

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

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: