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

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 AUTHORITYLOCAL 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 FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBackup, 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 FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLLog). You’ve been warned.

10. For “Define Maintenance Cleanup” I have it delete backup files, using “E:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBackup” 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 FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBackup) 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!