Friday, March 16, 2012

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

image

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:

image

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

image

This will bring up a new query window:

image

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]

image

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

imageimage

imageimage

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]

image

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

image

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

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:

image

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

image

Creating the 64-bit ODBC DSN Connection

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

image

Navigate to the System DSN tab and click Add:

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

image

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

image

**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.

image

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:

image

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

image

Leave the following settings as default and click Finish:

image

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:

image

Confirm that the test completes successfully:

image

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

image

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

image

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

3 comments:

windows server said...

As a Dell employee I think your post is really impressive, The information about SQL Server is very much useful. With th detailed screen shot attched it added an advantage for users to understand i simpple manner. Thanks for sharing with us.

Anonymous said...

By any chance, could you update the instructions to also show how to script with active directory credentials? and it would be grwat if you could show ad credentials and srm database configuration. thanks.

Terence Luk said...

Sorry, I don't really have the time to demonstrate with an AD account but if you can actually use the same script I used for version 4 to achieve that.