Coming from a Microsoft infrastructure background, I found it fairly easy to create the vCenter database but I’ve noticed in the past that some of my colleagues who haven’t had exposure to Microsoft SQL Server and/or minimal experience with Windows servers tend to get lost at certain parts so I hope this blog post will help anyone that may find themselves in a similar situation.
Creating the service account
Before you create the database on the SQL server, create a domain account for the vCenter service. This service does not need to be a member of any group aside from the Domain Users group.
Log onto the new vCenter Windows Server 2008 R2 64-bit server and open up Server Manager.
Navigate to Configuration –> Local Users and Groups –> Groups and open up the Administrators group. Add the service account to the local administrators group.
Creating the SQL Database on Microsoft SQL Server
Open up SQL Server Management Studio.
Once SQL Server Management Studio launches, connect to the SQL instance.
Once in 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 80 in the installation guide (http://www.vmware.com/pdf/vsphere4/r41/vsp_41_esx_vc_installation_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
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’
go
I never end up using this script because it essentially creates a new SQL authentication account for vCenter to use. What I prefer doing is to add the vCenter domain service account to the permissions instead of creating a completely new one. The following is a modified script that I use instead.
Please note that the parameters that will need to be changed accordingly to your infrastructure are Enlarged and BOLDED.
use [master]
go
CREATE DATABASE [VCDB] ON PRIMARY
(NAME = N’vcdb’, FILENAME = N’D:databasesVCDB.mdf‘, SIZE = 2000KB, FILEGROWTH = 10% )
LOG ON
(NAME = N’vcdb_log’, FILENAME = N’D:databasesVCDB.ldf‘, SIZE = 1000KB, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
go
use VCDB
go
sp_grantlogin @loginame=[domainsvc_vCenter]
go
sp_defaultdb @loginame=[domainsvc_vCenter], @defdb=’VCDB’
go
ALTER LOGIN [domainsvc_vCenter] WITH DEFAULT_LANGUAGE = us_english;
go
CREATE USER [domainsvc_vCenter] for LOGIN [domainsvc_vCenter]
go
sp_addrolemember @rolename = ‘db_owner’, @membername = ‘domainsvc_vCenter‘
go
use MSDB
go
CREATE USER [domainsvc_vCenter] for LOGIN [domainsvc_vCenter]
go
sp_addrolemember @rolename = ‘db_owner’, @membername = ‘domainsvc_vCenter‘
go
Parameters:
D:databasesVCDB.mdf‘ = The location of the database and the database filename.
D:databasesVCDB.ldf‘ = The location of the log and the log filename.
domainsvc_vCenter = The vCenter service account name preceded by the domain NetBIOS name.
——————————————————————————————————————————————————————
For those who are interested, the line:
ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF
… was taken out because this line disables the password policy check and only applies to a SQL Authentication account.
The code essentially does the following:
- Create a database named VCDB.
- Add the domain service account we created to the SQL server.
- Set the default database for the service account.
- Set the default language for the service account.
- Give service account db_owner permissions to VCDB.
- Give service account db_owner permissions to msdb.
Once you’ve made the appropriate changes execute the code and if no syntax mistakes were made, you will see the message:
Command(s) completed successfully.
The new database has now been created and the vCenter service account now has the proper permissions.
Creating the 64-bit ODBC DSN Connection
Update: If you find that you’re missing the SQL Server Native Client 10.0 option, please see this blog post: https://blog.terenceluk.com/2010/10/vcenter-odbc-dsn-database-connection.html.
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
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 using a domain service account, we can leave the following parameters as default.
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. 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.
Hope this helps clarify the process. Remember this is for vSphere 4.1 and not 4.0 as the latter uses a 32-bit ODBC DSN.
14 Responses
What should I do if I do not see the SQL Server Native Client 10.0 to create my 64 bits system DSN ?
I am using Windows Server 2008 SP2 standard 64 bits and the SQL database will be on another server (SQL server 2008 standard 32 bits).
Should the option be there by default or do I have to install something new ???
Thanks
Hi,
If your SQL server is not installed locally on the vCenter server, you will not see the SQL Server Native Client 10.0 which means you'll need to download and install the feature pack in order to get that driver.
Also, if you're using vCenter 4.0, you will need to use a 32-bit ODBC and if you're using 4.1, you'll need to use the 64-bit. In any case, download one of these features packs:
–Microsoft SQL Server 2008 Feature Pack, August 2008–
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
–Microsoft SQL Server 2008 Feature Pack, October 2008–
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en
–Microsoft® SQL Server® 2008 R2 Feature Pack–
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52
I've tried the first 2 (August 2008 and October 2008) for vSphere 4.0 and both work but with vSphere 4.1, I would recommend that you try the last one which would be the most up-to-date version. Hope this helps.
Actually, since I haven't tested the R2 driver myself, I would suggest that you stick with the April 2009 pack: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4
This version is listed as 10.1.2531.0 which is the Native Client installed when I installed SQL Server 2008 onto the vCenter server.
Here's a blog post to help clear the confusion: http://terenceluk.blogspot.com/2010/10/vcenter-odbc-dsn-database-connection.html
Nice document Terence, one aspect I am unsure of is creating the ODBC DSN on the 64bit vCenter server for a remote 64bit SQL 2008 server.
Do I need a SQL Server Native Client for SQL2008- I cannot make a connection with 2005 native client and when I try to install the 2008 version on the VC server, it reports I must have SQL2008 installed to proceed.
Any help appreciated.
Hi,
You should be able to install the native client on a Windows Server 2008 R2 64-bit server without having to install SQL. I've done it in other environments and I just logged onto one to double check. For that environment, I used the SQL Server 2008 native client version 10.1.2531.0 on vCenter 4.1 connecting to another SQL server.
Reference this post to find the right version of the native client: http://terenceluk.blogspot.com/2010/10/vcenter-odbc-dsn-database-connection.html
hey terence..
i had tried ur step to connecting vcenter to SQL DB…
every step done well, but.. when i try to start vCenter Server services through services.msc it always failed.
i try to connect vcenter to SQL Server at the different VMs.
any suggest for me???
or mail me at nurfahmi.hamdika@gmail.com if u dont mind..
Hi Nur,
Your best bet is to check the event logs to see if there's more information about why it's failing.
Hi Terence, great document. I have tried to import my existing SQL Express DB that was running on the vCenter 4.1 server into a stand alone VM running 2008 R2 SQL server, and all seems to go well DSN, connection is correct and vCenter service starts.
However, anytime I deploy a template or try to clone a VM after moving to the SQL 2008 server DB the vCenter service stops, and I can not restart it.
If i check the vpxd.log on my vCenter server all the failures leading up to the vCenter service stopping, seem to be related to missing stored procedures; and the clone failure itself.
[2011-04-03 20:47:24.579 03504 error 'App' opID=0FAF86D6-0000018E] [VdbStatement] Diagnostic data from driver is 42000:1:2812:[Microsoft][SQL Server Native Client 10.0][SQL Server]Could not find stored procedure 'delete_stats_proc'.
[2011-04-03 20:47:24.688 03504 error 'App' opID=0FAF86D6-0000018E] [VpxdVmprov] Clone failed because of unknown error
[2011-04-03 20:47:24.698 03504 error 'App' opID=0FAF86D6-0000018E] An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) "ODBC error: (23000) – [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert the value NULL into column 'ONLINE_STANDBY', table 'pdiwaas-vcenter.dbo.VPX_VM'; column does not allow nulls. INSERT fails." is returned when executing SQL statement "INSERT INTO VPX_VM WITH (ROWLOCK) (ID, DATACENTER_ID, HOST_ID, RESOURCE_GROUP_ID, POWER_STATE, DISABLED_SNAPSHOT, FAILED_DMOTION, IS_TEMPLATE, AGENT_ID, AGENT_CNX_STATE, FILE_NAME, LOCAL_FILE_NAME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
After this failure occurs I can no longer start the vCenter server and have to restore my 2008 SQL server to a previous snapshot.
If you have any tips or pointers of where to check in the SQL server or what may have gone wrong it would be much appreciated.
Thanks,
Mike
Hi Mike,
Based on the information you've provided, it almost sounds like the SQL instsance you have does not have the stored procedures that vCenter needs. What I would do at this point is to connect via SQL Management Studio and navigate to the stored procedures node and look around for 'delete_stats_proc'. If you don't see this stored procedure, it's most likely because that stored procedure was not moved over (most likely stored in another database other than the VCDB) so what I would recommend is to schedule some downtime and do the following:
1. Back up the MDF and LDF of your vCenter, VUM and any other vSphere related databases.
2. Recreate a new vCenter and VUM database.
3. Uninstall the vCenter instance you have on your vCenter server.
3. Reinstall vCenter and VUM onto your vCenter server and point it to the empty database.
4. Once the install is complete, replace the databases with your backups.
Note that I have not tested this and you may lose some non-database specific settings so if you'd like to test this in a lab (i.e. clone the 2 virtual machines), please try that if your situation warrents it.
Perfect. Thanks.
Thanks for posting the instructions. Very useful!
I was new to db,but your blog had taught me whole lot of thing
lovely digg styles 2012 http://onlinecasinoworld.weebly.com free online casino