How to Install Microsoft SQL Server 2008 R2 for VMware vCenter 5

by Bob Plankers on October 6, 2011 · 35 comments

in Best Practices,How To,Virtualization

My venerable post on installing MS SQL Server 2008 for vCenter 4 was getting old, so I thought I’d update it, if only because I have a new admin helping me and I’m going to stick him with doing a bunch of installs. Ha!

I thank the VMware folks who have incorporated a lot of the tweaks from my original document into the defaults for vCenter 5. They were probably obvious, and not taken from my work, but it’s content I don’t need anymore. Awesome.

While I don’t mean this page to become a general support site for vCenter SQL Server installations please leave a comment if something needs to be clarified or corrected, or if I’m doing something dumb here. I consider my DBA skills to be somewhere between amateur and semi-pro, I’m self-taught mainly via Googling stuff, and may not have an answer for you if you are asking a support question. It should go without saying that you should talk to VMware or Microsoft Support if you are having issues.

There are some decent installation resources on Microsoft’s TechNet site for preparing SQL Server installations. It’s worth the read through. Likewise, the first installations I did were on a non-Active Directory, standalone Windows Server VM that I’d taken a snapshot of. The ability to revert the snapshot and try again is priceless.

I am using Microsoft SQL Server 2008 R2 running on Microsoft Windows Server 2008 R2. As with all things in IT your mileage may vary, so use your head. These instructions assume a familiarity with Windows, and a general ability to figure things out once you’ve seen it once (towards the end you’ll see why I say this, I start omitting a lot of repetitive maintenance plan stuff). I tend to create local accounts, even with Active Directory, because I’m just paranoid about network authentication. Do what you like or what you need to.

Pre-Install

1. Create an individual role account, ‘sqldb’ for SQL Server. Assign it a long, random password, set “Password never expires” and “User not allowed to change password.” I create this as a local user, regardless of the membership of host in a domain. This user does not have any special membership (it’s just in the “Users” group).

2. Decide where you are going to do the install. On my hosts I have a system drive, C:, and an application/data drive, E:, which I use for installations like this. This might be a good time to use a database sizing calculator.

3. Download the latest VMware-certified Microsoft SQL Server service pack (from Microsoft) for immediate installation afterwards.

4. Install Microsoft .NET Framework 3.5 SP1 through Server Manager->Select Server Roles. Check “Application Server” and permit it to add the required features, then advance to the “Select Role Services” page. Check only the “.NET Framework 3.5.1″ option, then continue through the installation.

Installation

1. Run Setup.The SQL Server Installation Center will start. I usually pick the “System Configuration Checker” from the Planning menu, just to make sure nothing is seriously wrong. If it is I fix it, then exit the Checker.

Once that is complete I choose “Installation” on the left, then “New installation or add features to an existing installation.” This will start the installer. Follow along and enter the license key, then advance to accepting the license terms. I usually don’t opt to send usage data offsite, but do whatever you want.

2. The installer will prompt you to install Setup Support files. You don’t have much choice so go ahead with it.

3. Fix anything Setup Support identifies as a warning or error. If it’s the firewall rules generating a warning consider if you will be connecting from off-machine. If not, no worries, else the warning text has a URL to follow for instructions on how to modify the firewall.

4. On the “Setup Role” page choose “SQL Server Feature Installation.”

5. On the “Feature Selection” page:

Check “Instance Features -> Database Engine Services”

Under “Shared Features” choose Client Tools Connectivity, Integration Services, Management Tools – Basic, and Management Tools – Complete.

I usually also select SQL Server Books Online, too, because I like having the reference available.

I change the Shared Feature directories from C: to E:, with the same path.

6. On the “Instance Configuration” page:

I choose the default instance, leaving the instance ID set to the default (MSSQLSERVER) and change the instance root directory from C: to E: with the same path.

7. Keep clicking Next until you get to the “Server Configuration” page:

Click “Use the same account for all SQL Server services” and enter the account information for the ‘sqldb’ user you created.

Set “SQL Server Agent” to startup type of Automatic. Ensure the “SQL Server Database Engine” is also set to Automatic.

Double-check that “SQL Server Browser” is set to Disabled and has the account name set to “NT AUTHORITY\LOCAL SERVICE”

8. On the “Database Engine Configuration” page:

On the “Account Provisioning” tab choose Mixed Mode (SQL Server authentication and Windows authentication). Set a password for the built-in SQL Server system administrator account that is nice and random. This is the superuser account for the database.

Under “Specify SQL Server administrators” add any additional users that will need to maintain the SQL Server (such as yourself). This makes it easy for them to log in and do things. The “Add Current User” button is nice…

On the “Data Directories” tab I change all the C:s to E:s, but that’s just how I roll.

9. Keep moving through the pages of the installation wizard and finish the installation, then install the latest SQL Server Service Pack. Manually run Windows Update to also check for any updates available that way. Reboot if it tells you to.

Database Configuration

1. Start the Microsoft SQL Server Management Studio and log in as the ‘sa’ user.

2. Right-click the topmost SQL Server object, usually named with the machine name or “local” (depending on how you logged in). Choose “Properties.” Choose the “Memory” page. Set “Maximum Server Memory (in MB)” to something sane for the host. I usually set it to 25%-50% of the RAM on the host. The more memory you can give it the better, as the database will cache data in RAM, but you also want to leave room in RAM for the OS (2 GB), some file cache, and vCenter (4 GB) if you are going to install it on the same host. Swapping is bad, remember you can always go in later and increase it again. Click OK.

3. Right-click the “Databases” folder, pick “New Database…” Under “General” I set the database name to “VCDB” but you can name it whatever you want as long as you remember the name. Under “Options” set the recovery model to “Simple.” Click OK.

4. Right-click the “Security” folder, pick “New->Login.” In the General page enter the username ‘vpxuser’, select “SQL Server authentication” and enter a nice long random password that you’ll remember and/or record. I uncheck “Enforce password policy,” “Enforce password expiration,” and “User must change password at next login.” Set the default database to VCDB and the default language to English.

Click the “User Mapping” page on the right. Check the “Map” box for VCDB, then choose db_owner from the role membership list below. Then check the “Map” box for msdb, and choose db_owner for that, too.

I click the “…” box in the “Default Schema” column for both msdb and VCDB, and set the default schema to ‘dbo’. Then click OK.

By the way, you have some options here, as outlined in the VMware documentation. However, I think their documentation is a bit confusing, and the installer will take care of most of this for you. I like that.

5. Grant “VIEW SERVER STATE” to vpxuser in the database in order to enable database monitoring. The quickest way to do this is to click the “New Query” button in Server Management Studio, then enter:

grant VIEW SERVER STATE to vpxuser
go

and press the “!Execute” button.

6. Configure the SQL Server TCP/IP options. By default TCP/IP is enabled for MS SQL Server, but VMware has instructions on changes that need to be made for JDBC support. Make sure you make those changes to the IPs that will get connections, not just the first one you see. If you’re using IPv6 (and you should be) make sure you set the parameters on those IPs, too.

7. Create an ODBC data source. VMware has instructions on how to do this in their vSphere installation documentation. I usually provide the database name and vpxuser login information so I can test the connection when it offers.

8. Grant “Local Launch” permissions to SQL Server 2008. This fixes a known issue that generates event log errors and may prevent some scheduled jobs from running. To do this open Administrative Tools->Component Services.

Browse to Console Root->Component Services->Computers->My Computer->DCOM Config->MsDtsServer100. Right-click MsDtsServer100 and pick “Properties.”

On the “Security” tab, under “Launch and Activation Permissions” select “Customize” and then click Edit. Add the local ‘sqldb’ user you are using and allow Local Launch. Click OK all the way out.

9. I usually reboot at this point. Some of these changes require restarting the SQL Server anyhow, and if you haven’t rebooted for Windows Update and SQL Server service packs it’s a good opportunity.

vCenter Installation

1. Install vCenter. The specifics of this are documented by VMware. If you are prompted, connect to the VCDB database using the ODBC connection you created above, using the ‘vpxuser’ username and password.

Weekly Database Maintenance Tasks

This part of the installation will set up some regular jobs to clean up the database, make backups, and do some performance optimization. I’ll walk you through the basics, figuring that you can handle schedules and whatnot yourself.

1. Start the Microsoft SQL Server Management Studio again and log in as the ‘sa’ user. Open the “Management” folder and right-click “Maintenance Plans.” Select “Maintenance Plan Wizard.”

2. On the “Select Plan Properties” page give it the name “Weekly Maintenance Plan.” I select “Single schedule for the entire plan or no schedule” and click the “Change” button to pick when I want it to run. I usually have the weekly job run at 2100 on Sunday, as nobody is usually doing anything then, and it’s before my server’s backup window. When you figure this out say OK to the schedule and then Next.

3. On the “Select Maintenance Tasks” page I choose: Check Database Integrity, Reorganize Index, Rebuild Index, Update Statistics, Back Up Database (Full), and Maintenance Cleanup Task. Click Next.

4. On the “Select Maintenance Task Order” page I move “Back Up Database (Full) to after “Check Database Integrity” if it isn’t there already.

5. For “Define Database Integrity Check” I choose all databases, including indexes.

6. For “Define Reorganize Index” I choose all databases, compact large objects.

7. For “Define Rebuild Index” I choose all databases, reorganize pages with the default amount of free space. I also check “Keep index online while reindexing.” Note: a commenter, JJ, indicates that the “Keep index online…” option appears to be an Enterprise version feature, and you may see failures with it enabled on other SQL Server versions.

8. For “Define Update Statistics” I choose all databases, all existing statistics, full scan.

9. For “Define Back Up Database (Full)” I choose all databases, and ignore databases where the state is not online. I set the backup set to expire after 21 days, back up to disk, and create a backup file for every database. I set the backup folder to be on my data drive, E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup, and to use a backup file extension of “bak”.

The “Compress Backup” option seems like a good one but it isn’t supported on 64-bit SQL Server. It’ll let you set it, then fail on execution. This is how I learned where the logs are (E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log). You’ve been warned.

10. For “Define Maintenance Cleanup” I have it delete backup files, using “E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup” as the search path and “bak” as the file extension. I have it delete files older than 22 days, but you can set it to whatever you need. Pick whatever report options you’d like (if you want email you’ll have to define yourself as an operator elsewhere in the Management Studio).

11. Click OK. I usually go into the Maintenance Plans folder now, right click on this job, and choose “Execute” to see if it runs. Check the logs if it doesn’t.

Daily Database Backup Task

1. If you took the steps above you get a weekly full backup. I like daily backups, too. So go back into the Maintenance Plan Wizard and create a new plan called “Daily Differential Backup.” Set the schedule to recur Monday through Saturday at a good time (like before your system backup). You don’t need to schedule it on Sunday if you have the full backup happening then.

2. Choose only “Back Up Database (Differential)” and configure the plan. Again, I set it to back up all databases, ignoring databases where the state is not online, and to back up to the same location as above (E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup) with the “bak” extension, expiring after 21 days. Same “Compress Backup” warning as above.

3. Pick the report options you like, click through to save the plan. You might try executing it to see if it works.

Regular Reorganize Database Task

1. One of the performance suggestions buried in the VMware KB is to regularly reorganize the indexes, since the historical statistics tables get unwieldy. You can do this manually (boo), or schedule a job to do it by running the Maintenance Plan Wizard again (yay). Choose only “Reorganize Indexes” and set the schedule to recur every six hours, every day (or however often you want, I figure four times a day is a nice compromise). This keeps the logical fragmentation of the indices down.

2. Click through the pages of the wizard until you get to “Define Reorganize Index Task.” Have it only reindex VCDB, choose “Tables and views” in the Object selection, and check “Compact large objects.” Click through until you’re done.

Check Your Work

You’re done. At this point I’d make sure that all the scheduled jobs run, then wait a couple days to make sure the backups are happening. You might want to try restoring a backup then, too, so you know how to do it if you ever need to, or perhaps practice kicking off a manual backup (for upgrade situations, etc.). Watch your disk space for backups and logs, too. If you are getting a lot of logs you can add or change a maintenance plan with a maintenance cleanup task to delete old logs.

Enjoy!

{ 29 comments }

spade October 11, 2011 at 12:42 PM

Thanks for taking the time to write this down.

DS November 11, 2011 at 10:35 AM

Fantastic walkthrough. Thank you very much.

Marlon November 16, 2011 at 3:50 AM

Great! I appreciate your effort!

feh2o November 21, 2011 at 12:29 AM

very helpful for me and thank you so much.

kushineta December 5, 2011 at 1:35 AM

very helpful ,thank you very much.

Al LaPrade December 6, 2011 at 5:54 PM

Thanks for writing this, got me through my first vCenter 5 installation with no problem!

iadgreen28 January 10, 2012 at 3:11 PM

Thanks for that! The database maintenance plan section was quite helpful since I’ve never been sure what type of tasks to run on a database that will help optimize it.

Andrew January 25, 2012 at 4:28 PM

Great write up! Thanks
If I could, I’d like to add a couple notes to your instructions based on what I ran into (Windows Server 2008 R2 w/ MSSQL Server 2008 Standard):
Step 5 in “Installation” should include the “Integration Services” feature as well. It was required in my install to get the Maintenance tasks work correctly (They immediately failed until I installed it)
Be sure “World Wide Web Publishing” service did not get turned on… It did for me (I think it happened with the .NET Framework 3.5 Role install). I had to turn it off because vCenter complained that port 80 was in use during the vCenter install.

Bob Plankers February 1, 2012 at 1:59 PM

Awesome — I updated the post. Thank you.

parisv January 27, 2012 at 6:17 AM

Thanks for the guide, I have followed this but I’m having trouble getting venter web admin working. I’ve installed it on the same machine but once the installation is finished nothing happens when I go to https://localhost:9443/admin-app/ Is this because the web admin is looking for a default sql db name which has not be used in this guide?

parisv March 7, 2012 at 2:26 PM

Anyone??

Bob Plankers March 7, 2012 at 11:58 PM

First, this isn’t a support forum. You might try asking your VMware Support representative before you go posting something presumptuous like “anyone??” to someone’s personal blog. I’m usually more than willing to check something out or help if I have time and you’re not a jerk. Second, I’ve found that the most likely cause of not being able to reach the web client is that is isn’t installed. It isn’t installed by default, you know. You might try installing it and seeing if it becomes reachable then. Good luck.

parisv May 4, 2012 at 9:35 AM

Hey Bob, I only asked anyone as it was month later and really needed help to get it working. I did have a post on vmware forums beforehand:
http://communities.vmware.com/thread/342974

but I thought it might be the way I installed it from your instructions which might of caused issues with the web client. I also did have the web client installed.

simon champion February 8, 2012 at 11:33 AM

Hi Bob – nice write up. One thing I have encountered which you don’t mention is tempdb configuration. I have experienced issues where the default options are taken for tempdb (e.g. to auto-grow to an unlimited size), then some activity occurs in vCenter which results in an explosion in tempdb size, which then fills up the disk it sits on, which then stops tempdb growing anymore, which then crashes SQL Server, and then finally vCenter. Microsoft recommend leaving tempdb on the auto-grow/unlimited setting for the first few weeks of use and before setting tempdb size to a proportion of your choosing more than the largest observed size of tempdb and set it not to auto-grow. Given how sensitive SQL Server is to the loss of tempdb its probably best to err on the side of caution when setting tempdb size. For our 400 VM, 24 host environment we went with a SQL Server with an OS drive of 30gb, tempdb drive of 80gb, SQL instance drive of 40gb, Database drive of 40gb, Logs drive of 20gb, and SQL Backup drive of 40gb. We then set the tempdb database to 60gb with a logs file of 1gb. It solved our vCenter crashing issues. It’s also important to continue to monitor tempdb usage and preferably configure alerting for when the tempdb database starts to fill up.

Bob Plankers March 8, 2012 at 12:01 AM

I will check this out… Thanks for bringing it to my attention!

simon champion June 20, 2012 at 9:32 AM

Looks like the tempdb issues we experienced were actually bugs in vCenter as I just found references to a fixed bug in the release notes for vCenter Server 4.1 Update 2 and 5.0 Update 1:

http://www.vmware.com/support/vsphere4/doc/vsp_vc41_u2_rel_notes.html
http://www.vmware.com/support/vsphere5/doc/vsp_vc50_u1_rel_notes.html

Search for “tempdb” in both cases to find the references.

JJ March 12, 2012 at 1:25 AM

Hi,
very nice work. It´s one of the best “guides” i´ve seen for this kind of installation.
But I have a Point to tell about: At the Part “Weekly Database Maintenance Tasks” at No. 7 (keep index online while reindexing) -> This one only works works with an SQL-Enterprise Version isn´t it? my SQL 2008 R2 Workgroup ran into failures with this option. As i deactivated “keep index online while reindexing” everything went like a charm.
sorry for my english im a young german admin.

keep up the good work.
best regards JJ

Bob Plankers March 12, 2012 at 12:30 PM

JJ, your English is better than many native speakers. And, I’m sorry to say, way better than my German, which I’ve learned primarily from an automobile navigation system and various Bavarian biergartens. :)

I appreciate the correction, and I updated the post to reflect that there’s a difference. Thank you!

Nail March 21, 2012 at 3:38 AM

Great guide!!!
One question stay unrevealed for me: the sql server 2008 r2, used in your guide was 64 bit or 32 bit?
I asking because want to be sure the created DSN will be 64-bit, as it required for vCenter 5.0 U1.
Thanks in advance,
Nail.

Deipenbrock June 10, 2012 at 10:21 AM
Caleb Meadows June 13, 2012 at 10:48 AM

Great material!

zelig8 July 14, 2012 at 10:33 AM

Thanks so much for this Bob – very helpful! One thing i’m curious about is this step in your Installation section: Double-check that “SQL Server Browser” is set to Disabled and has the account name set to “NT AUTHORITY\LOCAL SERVICE”. After doing so, when i reached the step for Configuring the SQL Server TCP/IP options for JDBC support, VMware’s listed steps include starting the SQL Server Browser. I’ve assumed your recommendation is to leave this disabled?

Fabio July 31, 2012 at 4:10 AM

Great guide!!!
Thanks so mutch.

Mikeadam August 30, 2012 at 9:13 AM

Seriously thank you! I wish VMWare would post instructions as clear as these are. Saved me a load of time.

Shane September 17, 2012 at 10:42 PM

Good write-up.
In step 4 of ‘Database Configuration’, I wonder if it might be worth changing the newly created SQL account name ‘vpxuser’ to something else? I had one incidence where confusion was caused because this account name is the same as used between vCenter and the ESXi hosts.
Just a thought.
Thanks.

Jimmy September 19, 2012 at 8:21 AM

Wonderful !
Many thanks.

jim rivers December 11, 2012 at 10:46 AM

Bob,

This was such a good article thank you, can you make one up for setting up SQL to use for VMWARE 5.1 single sign on. I keep getting missing tables when I try to attached Vcenter to sql 2008 R2.

I have a few vmware articles but they are very complicated and use a lot of scripts that complicate it even more.

Chris Schroeder March 15, 2013 at 5:34 PM

I enjoyed the write-up on this Bob. Our previous vCenter 2.5 had no maintenance on it (someone else set it up years ago) and it became unmanageable over the years. I’m hoping by following your guide it will result in better performance in the coming years.

steve schmidlap March 24, 2013 at 3:18 PM

Why do you backup the database to the same disk/volume on which the database resides? Defeats the purpose of a backup if the disk fails does it not? Since tape is not an option in my lab I want to backup to a disk on a seperate machine. I tried by using a UNC path to a shared folder on a different machine. That does not seem to be supported. Has anyonen done that?

Comments on this entry are closed.

{ 6 trackbacks }

Previous post:

Next post: