{"id":317,"date":"2012-01-20T11:34:03","date_gmt":"2012-01-20T10:34:03","guid":{"rendered":"http:\/\/www.bussink.ch\/?p=317"},"modified":"2018-05-23T18:00:17","modified_gmt":"2018-05-23T16:00:17","slug":"create-vcenter-database-quickly-with-transact-sql","status":"publish","type":"post","link":"https:\/\/www.bussink.ch\/?p=317","title":{"rendered":"Create vCenter database quickly with Transact-SQL"},"content":{"rendered":"

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.<\/p>\n

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<\/a> ). 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.<\/p>\n

\"\"<\/a>

Microsoft SQL Server directory structure for User Databaes<\/p><\/div>\n

Using the Microsoft SQL Server Management Studio interface we can start a New Query, in which we will add the Transact-SQL code.<\/p>\n

\"\"<\/a>

SQL Server Management Studio – Open a New Query<\/p><\/div>\n

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.<\/p>\n

USE<\/span>\u00a0[master]<\/span><\/div>\n
GO<\/span><\/div>\n
CREATE<\/span>\u00a0DATABASE<\/span>\u00a0[vcenter-server]<\/span>\u00a0on<\/span>\u00a0PRIMARY<\/span><\/div>\n
(<\/span>NAME<\/span>\u00a0=<\/span>\u00a0N’vcenter-server’<\/span>,<\/span>\u00a0FILENAME<\/span>\u00a0=<\/span>\u00a0N’D:\\Microsoft SQL Server\\vcenter-server\\vcenter-server.mdf’<\/span>,<\/span>\u00a0SIZE<\/span>\u00a0=<\/span>\u00a01024MB<\/span>,<\/span>\u00a0MAXSIZE<\/span>\u00a0=<\/span>\u00a016384MB<\/span>,<\/span>\u00a0FILEGROWTH<\/span>\u00a0=<\/span>\u00a0512MB<\/span>)<\/span><\/div>\n
LOG<\/span>\u00a0ON<\/span><\/div>\n
(<\/span>NAME<\/span>\u00a0=<\/span>\u00a0N’vcenter-server_log’<\/span>,<\/span>\u00a0FILENAME<\/span>\u00a0=<\/span>\u00a0N’D:\\Microsoft SQL Server\\vcenter-server\\vcenter-server.ldf’<\/span>,<\/span>\u00a0SIZE<\/span>\u00a0=<\/span>\u00a0512MB<\/span>,<\/span>\u00a0MAXSIZE<\/span>\u00a0=<\/span>\u00a02048MB<\/span>,<\/span>\u00a0FILEGROWTH<\/span>\u00a0=<\/span>\u00a0256MB<\/span>)<\/span><\/div>\n
COLLATE<\/span>\u00a0SQL_Latin1_General_CP1_CI_AS<\/div>\n
GO<\/span><\/div>\n
\"\"<\/a>

vCenter SQL Database creation with settings<\/p><\/div>\n

Lets now change the Recovery mode of our database for our needs, to Simple.<\/p>\n

USE<\/span>\u00a0[vcenter-server]<\/span><\/span><\/div>\n
GO<\/span><\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcenter-server]<\/span>\u00a0SET<\/span>\u00a0RECOVERY<\/span>\u00a0SIMPLE<\/span>;<\/span><\/span><\/div>\n
GO<\/span><\/span><\/div>\n
\"\"<\/a>

vCenter SQL Database alter recovery mode to Simple<\/p><\/div>\n

Lets create a dedicated vCenter database user such as vpxdb.<\/p>\n

USE<\/span>\u00a0[vcenter-server]<\/span><\/span><\/div>\n
GO<\/span><\/span><\/div>\n
CREATE<\/span>\u00a0LOGIN<\/span>\u00a0[vpxdb]<\/span>\u00a0WITH<\/span>\u00a0PASSWORD<\/span>\u00a0=<\/span>\u00a0‘insert-a-password-here’<\/span>,<\/span>\u00a0DEFAULT_DATABASE<\/span>\u00a0=<\/span>\u00a0[vcenter-server]<\/span>,<\/span>\u00a0DEFAULT_LANGUAGE<\/span>=<\/span>[us_english]<\/span>,<\/span>\u00a0CHECK_POLICY<\/span>=<\/span>OFF<\/span><\/span><\/div>\n
GO<\/span><\/span><\/div>\n
CREATE<\/span>\u00a0USER<\/span>\u00a0[vpxdb]<\/span>\u00a0for<\/span>\u00a0LOGIN<\/span>\u00a0[vpxdb]<\/span><\/span><\/div>\n
GO<\/span><\/span><\/div>\n
\"\"<\/a>

SQL Database vpxdb user creation<\/p><\/div>\n

Now we let the newly create database user connect to the vCenter database.<\/p>\n

USE<\/span>\u00a0[msdb]<\/span><\/div>\n
GO<\/span><\/div>\n
CREATE<\/span>\u00a0USER<\/span>\u00a0[vpxdb]<\/span>\u00a0FOR<\/span>\u00a0LOGIN<\/span>\u00a0[vpxdb]<\/span><\/div>\n
GO<\/span><\/div>\n
\"\"<\/a>

SQL Database vpxdb user login for vCenter Database<\/p><\/div>\n

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.<\/p>\n

USE<\/span>\u00a0[msdb]<\/span><\/div>\n
GO<\/span><\/div>\n
EXEC<\/span>\u00a0sp_addrolemember<\/span>\u00a0N’db_owner’<\/span>,<\/span>\u00a0N’vpxdb’<\/span><\/div>\n
GO<\/span><\/div>\n
\"\"<\/a>

SQL Database user vpxdb db_owner rights to MSDB<\/p><\/div>\n

And last we change the ownership of the vCenter Database for the vpxdb user.<\/p>\n

USE<\/span>\u00a0[vcenter-server]<\/span><\/div>\n
GO<\/span><\/div>\n
sp_addrolemember<\/span>\u00a0[db_owner]<\/span>,<\/span>[vpxdb]<\/span><\/div>\n
GO<\/span><\/div>\n
\"\"<\/a>

SQL Database user vpxdb db_owner rights to vcenter-database<\/p><\/div>\n

You can find the all the Transact-SQL code in this simple text file vCenter-SQL-TransactSQL-database.txt<\/a>. 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<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":329,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[28,19,29,30],"_links":{"self":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts\/317"}],"collection":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=317"}],"version-history":[{"count":19,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts\/317\/revisions"}],"predecessor-version":[{"id":1972,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts\/317\/revisions\/1972"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/media\/329"}],"wp:attachment":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=317"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=317"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=317"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}