VMware vCenter 5.1 Installation: Part 6 (Create vCenter and VUM Databases)

In Part 5 of my series on installing and configuring VMware vCenter 5.1 we configured the Inventory Service to use trusted SSL certificates. Now that we have the SSO service and Inventory Service fully installed and configured, it's finally time to start installing vCenter Server.

This post will cover configuring the vCenter and VUM databases (using SQL Server 2008 R2), and the vCenter DSN. This must be done prior to starting the vCenter installation, as one of the first prompts is selecting the vCenter DSN, which must be tied back to a database in SQL server.

Let's get started creating the vCenter and VUM databases, then the vCenter DSN.

1. On your vCenter server you must install the appropriate SQL Native Client. The ODBC configuration uses the Native Client to communicate back to your SQL server. Since vCenter requires a 64-bit server you need the 64-bit native SQL client. If you followed my blog post here to create a SQL 2008 R2 SP2 slipstream media, you can find the SP2 native client at this path: \SP\1033_enu_lp\x64\setup\x64\sqlncli.msi.Or if you didn't make slipstreamed media you can download the 64-bit Microsoft SQL Server 2008 R2 SP2 native client from here.

2. Install the SQL Server 2008 R2 native client on your vCenter server using all default values.

3. On your SQL 2008 R2 server you need to create the vCenter and VUM databases. I've included a sample script below that does the trick. Of course you will need to modify the vCenter service account name, database names and paths to suit your environment.

Cut and paste the script into SQL Server Management Studio and execute it. If you use the same service account here as you did for the SSO installation you can either comment out the CREATE LOGIN statement below, or just ignore the warning when you run the script since the login already exists. No harm done trying to add a login that already exists.

----

/* Creates vCenter server and VUM databases. */

/* Change login name to vCenter service account */
EXEC('CREATE LOGIN [contoso\svc-vctr02-001]FROM WINDOWS')

USE MSDB
EXEC sp_grantdbaccess 'contoso\svc-vctr02-001'
EXEC sp_addrolemember db_owner, 'contoso\svc-vctr02-001'

USE master
create database "D001-vCenter Server"
on
( name = 'D001-vCenter Server',
  filename = 'K:\Microsoft SQL Server\MSSQL\Data\D001-vCenter_Server.mdf',
  size = 10000MB,
  filegrowth = 1000MB )
  log on
  ( name = 'D001-vCenter Server log',
    filename = 'L:\Microsoft SQL Server\MSSQL\Data\Logs\D001-vCenter_Server.ldf',
    size = 200MB,
    filegrowth = 20MB )
    COLLATE SQL_Latin1_General_CP1_CI_AS;

create database "D001-vCenter VUM"
on
( name = 'D001-vCenter VUM',
  filename = 'K:\Microsoft SQL Server\MSSQL\Data\D001-vCenter_VUM.mdf',
  size = 250MB,
  filegrowth = 25MB )
  log on
  ( name = 'D001-vCenter VUM log',
    filename = 'L:\Microsoft SQL Server\MSSQL\Data\Logs\D001-vCenter_VUM.ldf',
    size = 25MB,
    filegrowth = 2MB )
    COLLATE SQL_Latin1_General_CP1_CI_AS;

EXEC('ALTER AUTHORIZATION ON DATABASE::"D001-vCenter Server" TO [contoso\svc-vctr02-001]')
EXEC('ALTER AUTHORIZATION ON DATABASE::"D001-vCenter VUM" TO [contoso\svc-vctr02-001]')

GO

---

4. Back on the vCenter server you now must create a 64-bit DSN for vCenter to use. You can create it manually through the ODBC GUI, but for consistency I like to script it, so I've included a sample PowerShell script below. I saved the script as vCenter-DSN.ps1. The script requires two arguments, the first one is the FQDN of the SQL server and the second is the database name, enclosed in quotes if it has spaces in it. Run the script from an elevated PowerShell command window.

NOTE: If you have configured your SQL server to allow encrypted connections you can change the Encrypt value to YES. If want a quick guide on configuring SQL transport encryption you can check out my article here. Again, for security I would strongly suggest you use SQL SSL encryption.

---

## Creates a 64-bit System DSN for vCenter Server.

$DSNName = $args[1]
$DBName = $args[1]

If($args[0] -eq $NULL) { echo "Must specify FQDN of SQL server."; Exit}
If($args[1] -eq $NULL) { echo "Must specify Database name."; Exit}

$HKLMPath1 = "HKLM:\SOFTWARE\ODBC\ODBC.INI\" + $DSNName
$HKLMPath2 = "HKLM:\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"

md $HKLMPath1 -ErrorAction silentlycontinue
set-itemproperty -path $HKLMPath1 -name Driver -value "C:\WINDOWS\system32\sqlncli10.dll"
set-itemproperty -path $HKLMPath1 -name Description -value $DSNName
set-itemproperty -path $HKLMPath1 -name Server -value $args[0]
set-itemproperty -path $HKLMPath1 -name LastUser -value "Administrator"
set-itemproperty -path $HKLMPath1 -name Trusted_Connection -value "Yes"
set-itemproperty -path $HKLMPath1 -name Encrypt -value "No"
set-itemproperty -path $HKLMPath1 -name Database -value $DBName

## This is required to allow the ODBC connection to show up in the ODBC Administrator application.

md $HKLMPath2 -ErrorAction silentlycontinue
set-itemproperty -path $HKLMPath2 -name "$DSNName" -value "SQL Server Native Client 10.0"

---

Running the script is shown below, and you should see similar output.

5. At this point I would recommend you test the ODBC connection to avoid any vCenter installation issues. In the Windows Start menu search box type ODBC and select Data Sources (ODBC). When the ODBC Administrator appears click on System DSN and you should see the DSN the script created.

Click on the Configure button and run through the wizard (without changing any settings) and you should arrive at the summary screen below. In my case I require data encryption, so that option is set to Yes. Since most people probably don't have SQL setup for encryption (you should!) this will be a No for you.

Click on Test Data Source.. and you should see a successful connection message. If you have configured SQL transport encryption it will also note that the connection was encrypted and that the server certificate was validated (not self-signed).

At this point you have now successfully configured vCenter and VUM databases and setup the vCenter DSN. We will configure the VUM DSN when we do the VUM installation. Part 7 in this series is installing the vCenter 5.1 server using the database and DSN you created.