Installing VMware ESXi and vCenter 5.5 [Part 3]

Installing VMware vSphere 5.5 Series:
Part 1: Introduction and ESXi Installation
Part 2: Microsoft SQL 2012 Installation
Part 3: Creating the vCenter Database & ODBC Setup
Part 4: vCenter 5.5 Installation
Part 5: Initial Setup (Accessing vCenter, Configure Basic Permissions, Add Host)

Now that we have SQL installed its time to setup the vCenter database and ODBC connections.

Creating the vCenter Database

If you are experienced in SQL you can set the database up manually or use the provided schema SQL script from VMware vCenter installation media. In this install i’ll be using the VMware provided DB schema. This can be obtained in the \\vCenter Installation Media\vCenter-Server\dbschema\DB_and_schema_creation_scripts_MSSQL.txt To make it easier below is a copy/paste of the script. You will have to change the database name, username, password and database location to match your setup.

use [master]
go
CREATE DATABASE [VCDB] ON PRIMARY
(NAME = N'vcdb', FILENAME = N'C:\VCDB.mdf' , SIZE = 3000KB , 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', @defdb='VCDB', @deflanguage='us_english'
go
ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go
CREATE SCHEMA [VMW]
go
ALTER USER [vpxuser] WITH DEFAULT_SCHEMA =[VMW]
go

use [master]

CREATE DATABASE [VCDB] ON PRIMARY

(NAME = N'vcdb', FILENAME = N'C:\VCDB.mdf' , SIZE = 3000KB , FILEGROWTH = 10% )

LOG ON

(NAME = N'vcdb_log', FILENAME = N'C:\VCDB.ldf' , SIZE = 1000KB , FILEGROWTH = 10%)

COLLATE SQL_Latin1_General_CP1_CI_AS

use VCDB

sp_addlogin @loginame=[vpxuser], @passwd=N'vpxuser', @defdb='VCDB', @deflanguage='us_english'

ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF

CREATE USER [vpxuser] for LOGIN [vpxuser]

CREATE SCHEMA [VMW]

ALTER USER [vpxuser] WITH DEFAULT_SCHEMA =[VMW]

Open SQL Server Management Studio and login using an account that has access:

Now click on “New Query” and enter the database script from above. You will need to change the following:

Add the location of your SQL data directory. I used defaulted install so below is my path./
Change the initial size of the MDF to 4096KB, if you leave it at the default 3000KB you will get an error when you execute.
This script will create a user and give it DBO permissions, rename the vpxuser with your own username. Make sure it matches all the way through the script.

Click Execute. You should receive a message saying “Command(s) completed successfully. You will now have a VCDB DB under Databases.

Next right click on your VCDB database and go to Properties:

Go to the Options page and change the Recovery model to Simple. Click Ok:

Before we leave SQL Management Studio as a best practice right click your SQL server name and go to Properties:

On the Memory page we will want to limit the amount of memory SQL can use. I am running this in my home lab on a VM with 4 GB of RAM. I will give SQL 2 GB which leaves 2 GB for the OS. Click Ok:

Skip this part if vCenter and SQL are running on the same box: You will need to modify the TCP/IP settings for SQL. Open the SQL Server Configuration Manager, go to SQL Server Network Configuration, then Protocols for MSSQLSERVER. Right click on TCP/IP and click Properties. Make sure to change Enabled to Yes and TCP Dynamic Ports to 0 (zero). Click OK:

It is a good time to reboot your SQL server for the settings to take effect.

ODBC Setup:

Next parts are to be done on your vCenter server. Now we get to setup the ODBC connection for vCenter to use.

Go to Start -> Administrative Tools -> and open ODBC Data Sources (64 Bit):

Click on the System DSN tab and click Add:

Click on SQL Server Native Client 11.0 and click Finish. Note: You will not have this option if SQL was installed on a separate server. To install it proceed to the next step:

Skip this if you had the SQL Server Native Client as an option: The easiest way to install this is by downloading it from the Microsoft website. Under “Install Instructions” find the section that is shown in the screenshot below. Download and install the x64 version. It’s a straight forward install. Now perform the step above.

http://www.microsoft.com/en-us/download/details.aspx?id=29065&fa43d42b-25b5-4a42-fe9b-1634f450f5ee=True

Direct Link to X64 Package: http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409

Now you will want to name this connection, give it a description and enter your SQL server name. If you are going to run vCenter and SQL on the same server use localhost. Otherwise use the FQDN. Click Next: