Create vCenter database quickly with Transact-SQL

Posted on by

Creating new databases for VMware vCenter is something I have to do over and over again. I use mostly Microsoft SQL Server 2008 R2 so here are six quick procedures to simplify the creation and make all your vCenter databases to the same standard. I keep my Transact-SQL scripts in Evernote, so I just need to make six Copy & Paste and my vCenter database is created within 3 minutes. You can find the Transact-SQL to download at the bottom of this post.

My general rule when I create the VMware vCenter database is to have my user database on a separate disk from the operating system. This disk is formatted with 64K block size. SQL Server works with two specific IO request size 8K and 64K in general, so having 64K block size is optimum for SQL Server databases (See Disk partition alignment Best Practice for SQL Server ). I usually create a directory path for my SQL database D:\Microsoft SQL Server in which I will create two directories for the vCenter databses, vcenter-server and vcenter-update-manager.

Microsoft SQL Server directory structure for User Databaes

Using the Microsoft SQL Server Management Studio interface we can start a New Query, in which we will add the Transact-SQL code.

SQL Server Management Studio – Open a New Query

Now let’s insert the Transact-SQL script to create the new vcenter-server database. My database settings limit the database to grow past 16GB, and increases the database as it grows by blocks of 512MB. The initial size starts at 1GB. The code below is a bit wide for this blog, but you can find the full Transact-SQL code at the bottom.

USE [master]
GO
CREATE DATABASE [vcenter-server] on PRIMARY
(NAME = N’vcenter-server’, FILENAME = N’D:\Microsoft SQL Server\vcenter-server\vcenter-server.mdf’, SIZE = 1024MB, MAXSIZE = 16384MB, FILEGROWTH = 512MB)
LOG ON
(NAME = N’vcenter-server_log’, FILENAME = N’D:\Microsoft SQL Server\vcenter-server\vcenter-server.ldf’, SIZE = 512MB, MAXSIZE = 2048MB, FILEGROWTH = 256MB)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

vCenter SQL Database creation with settings

Lets now change the Recovery mode of our database for our needs, to Simple.

USE [vcenter-server]
GO
ALTER DATABASE [vcenter-server] SET RECOVERY SIMPLE;
GO

vCenter SQL Database alter recovery mode to Simple

Lets create a dedicated vCenter database user such as vpxdb.

USE [vcenter-server]
GO
CREATE LOGIN [vpxdb] WITH PASSWORD = ‘insert-a-password-here’, DEFAULT_DATABASE = [vcenter-server], DEFAULT_LANGUAGE=[us_english], CHECK_POLICY=OFF
GO
CREATE USER [vpxdb] for LOGIN [vpxdb]
GO

SQL Database vpxdb user creation

Now we let the newly create database user connect to the vCenter database.

USE [msdb]
GO
CREATE USER [vpxdb] FOR LOGIN [vpxdb]
GO

SQL Database vpxdb user login for vCenter Database

We allow the newly create vpxdb database user have db_owner rights to the [MSDB] database, so that the user can create the SQL Agent jobs in SQL.

USE [msdb]
GO
EXEC sp_addrolemember N’db_owner’, N’vpxdb’
GO

SQL Database user vpxdb db_owner rights to MSDB

And last we change the ownership of the vCenter Database for the vpxdb user.

USE [vcenter-server]
GO
sp_addrolemember [db_owner],[vpxdb]
GO

SQL Database user vpxdb db_owner rights to vcenter-database

You can find the all the Transact-SQL code in this simple text file vCenter-SQL-TransactSQL-database.txt. If you want the same type of Transact-SQL script to help you setup the vCenter Update Manager database check out this text file vCenter-Update-Manager-SQL-TransactSQL-database.txt