Creating vCenter Update Manager 4.1 SQL database and ODBC DSN Connection

Some of you might have come across one of my previous posts:

Creating vCenter 4.1 SQL database and ODBC DSN Connection

http://terenceluk.blogspot.com/2010/10/creating-vcenter-41-sql-database-and.html

… what I realized today while having to install Update Manager 4.1 was that I did not write a post specifically for creating its database and ODBC connection.  Since it’s been about two months the last time I did an install, I couldn’t quite remember what the database requirements were and after spending 10 minutes or so to freshen up through the documentation, I thought it would be best to spend a bit of time writing this post so I can refer to it in the future.

Requirements

As per the 4.1 ESX and vCenter Server Installation Guide (vsp_41_esx_vc_installation_guide.pdf):

… the database requirements for vCenter Update Manager is the same as vCenter server except that DB2 is not supported.  This means that if you don’t have a SQL server that’s bogged down by your vCenter server, you should be able to use it for Update Manager as well.  For this example, we’re going to use the same SQL Server we used for vCenter Server.

Creating the vCenter Update Manager Database

The process for creating the vCenter Update Manager database is also the same as what you would do for vCenter Server as shown in this post: http://terenceluk.blogspot.com/2010/10/creating-vcenter-41-sql-database-and.html, the difference is that certain lines in the script will not be necessary assuming you’ve already created a vCenter Server database (you would have to in order to install Update Manager).  Here’s what the script will look like:

Please note that the parameters that will need to be changed accordingly to your infrastructure are Enlarged and BOLDED.

use [master]
go
CREATE DATABASE [VUMDB] ON PRIMARY
(NAME = N'VUMdb', FILENAME = N'E:\SQLData\VUMDB.mdf', SIZE = 2000KB, FILEGROWTH = 10% )
LOG ON
(NAME = N'VUMdb_log', FILENAME = N'E:\SQLLogs\VUMDB.ldf', SIZE = 1000KB, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
go
use VUMDB
go
sp_grantlogin @loginame=[domain\svc_vCenter]
go
sp_defaultdb @loginame=[domain\svc_vCenter], @defdb='VUMDB'
go
ALTER LOGIN [domain\svc_vCenter] WITH DEFAULT_LANGUAGE = us_english;
go
CREATE USER [domain\svc_vCenter] for LOGIN [domain\svc_vCenter]
go
sp_addrolemember @rolename = 'db_owner', @membername = 'domain\svc_vCenter'
go

Parameters:

D:\SQLData\VUMDB.mdf' = The location of the database and the database filename.

D:\SQLLogs\VUMDB.ldf' = The location of the log and the log filename.

domain\svc_vCenter = The vCenter service account name preceded by the domain NetBIOS name.

Here is what the screen should look like when you execute this in SQL Server:

Creating the 32-bit ODBC DSN Connection:

Since VMware vCenter Update Manager is really a 32-bit application that requires you to install it on a 64-bit operating system, we can’t just launch the Data Sources (ODBC) shortcut from the Start menu to create the ODBC DSN connection:

What we need to do is open up the command prompt (make sure you run as administrator) and then paste in the following:

%systemdrive%\Windows\SysWoW64\Odbcad32.exe

 

This will launch the the 32-bit ODBC Data Source Administrator and allow you to create a 32-bit DSN.  Once this window opens, navigate to the System DSN tab and click the Add button:

Select the driver SQL Server Native Client 10.0 and click the Finish button:

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.

A Create a New Data Source to SQL Server wizard will launch so fill in the Name, Description and Server fields:

I also prefer to have the DSN use a domain service account but it’s up to you whether you want to change it to use a SQL Server Authentication account:

Make sure you change the default database to your vCenter Update Manager database that you created earlier. The name for this example is VUMDB.

Leave the following settings as default and click Finish.

A window is now presented that allows you to test the ODBC connection. You might wonder what credentials it’s using to test the connection and the answer to that is what you’re currently logged in as. This DSN is set up to automatically use whichever process that attempts to run it and since the process that will be using this DSN is ran under the domain service account we created, those credentials would be passed.

To test the service account’s permissions, make sure you’re logged into Windows with that account:

Once you click finish, you will now see your new ODBC DSN.  Notice how you don’t see your vCenter Server DSN here?  It’s because the window you’re looking at is for 32-bit DSNs:

Hope this helps anyone out there looking for instructions with screenshots.  I know I’ll be reading this post again a few months later because I would need a refresher.