How to Install Microsoft SQL Server 2008 for VMware vCenter 4

Update: This document is great for SQL Server 2008 & vCenter 4, but if you want SQL Server 2008 R2 & vCenter 5 I’ve got a new post with those details.

I’ve had cause recently to do several new VMware vCenter installations, and I thought I’d take the opportunity to update our documentation on setting up Microsoft SQL Server 2008. Since new VMware vSphere users often find themselves unwittingly becoming DBAs I thought it might help others if I posted it.

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. What’s the old joke, “How do you get to Carnegie Hall?” — “Practice, Practice, Practice.”

As of this writing we are using Microsoft SQL Server 2008 (not R2, yet) 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.

Finally, a big thank you to my coworker Steve Tanner, who knew way more about SQL Server when we did our first installs many years ago, and took the original notes we still use today.

Pre-Install

1. Create individual role accounts ‘sqldb’ and ‘vcenter’ for SQL Server and vCenter, respectively. Assign them long, random passwords, set “Password never expires” and “User not allowed to change password.”

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.You may get program compatibility warnings from Windows — go ahead and run the program. The compatibility issues are resolved with the service packs you’ll apply at the end of the installation.

It may wish to update the .NET Framework, as well as the Windows Installer. You don’t have a whole lot of choice if you want SQL Server, so roll with it. Once in the installer window I go to the “Installation” page, then choose “New SQL Server stand-alone installation or add features…” Keep clicking through, enter the license key, permit it to install Setup Support Files.

2. 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.

3. On the “Feature Selection” page:

Check “Instance Features -> Database Engine Services -> Full-Text Search”

Under “Shared Features” choose Client Tools Connectivity, SQL Server Books Online, Management Tools – Basic, Management Tools – Complete, and SQL Client Connectivity SDK.

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

4. 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.

5. 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 Full-text Filter Daemon Launcher” is set to startup type “Manual” and “SQL Server Browser” is set to Disabled. Both should have the account name set to “NT AUTHORITYLOCAL SERVICE”

6. On the “Database Engine Configuration” page:

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…

7. Keep moving through the pages of the installation wizard and finish the installation, then install the latest 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 (2 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.

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.

5. Configure the SQL Server TCP/IP options. VMware has instructions on how to do this in their vSphere installation documentation.

6. 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.

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.

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.

2. Set the vCenter Server for delayed start (so the SQL Server has time to start). Do this with Administrative Tools->Services, then edit the properties of “VMware VirtualCenter Server” and “VMware VirtualCenter Management Webservices” to have the startup type of “Automatic (Delayed Start)”.

3. I usually reboot here to make sure everything starts properly, and fix any problems I encounter.

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.”

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

6. 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.

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

8. For “Define Rebuild Index” I choose all databases, reorganize pages with the default amount of free space.

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

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!