Installing the SQL Server 2008 R2 Management Pack for System Center Operations Manager 2007 R2

By Bill Ramos
Principal Program Manager, SQL Server Manageability

Abstract

In this blog post, I will go over the steps to install System Center Operations Manager (SCOM) 2007 R2 and the recent release of the SQL Server 2008 R2 Management Pack (MP) on a bare bones Windows Server 2008 R2 Hyper-V virtual machine.  I’ll include where you can download the various software components (free & evaluation copies). My goal is to make operational DBAs familiar with SCOM 2007 and the various MPs that are available to help you better manage large number of server instances.

This blog post is part of the series of posts that I’m using to prepare for my SQL PASS 2010 session on the SQL MP.

Installing SCOM 2007 R2 and the SQL Server 2008 R2 MP

I’m starting with a Windows Server 2008 R2 Hyper-V image with 4GB of RAM and 1 virtual CPU. This configuration is the minimum recommended configuration for all of the SCOM 2007 R2 features.

Step 0 – Download and install updates

There is always something out there that needs to get updated after installing Windows Server 2008 R2. Be sure to check for updates as part of your Initial Configuration Tasks as shown below.

Generally, you will need to reboot after this step to make sure Windows Server 2008 R2 didn’t have more updates that require a reboot. Just reboot to be on the safe side.

Step 1 – Add the IIS Role

After the reboot, you will need to add the IIS role to your server. Just click on the “Add roles” command shown above in the “Customize This Server” section of the Initial Configuration Tasks dialog. This will launch the Add Roles Wizard. At this point, I’m assuming you are logged into the server with a Windows login that is a system administrator for the machine.

Just click Next past the introduction page.  You will then select the Web Server (IIS) role as shown below.

Then click Next. The wizard will then take you to the “Introduction to Web Server (IIS)” page. You are welcome to read the page and click on the hyperlinks to read more about IIS, but we are focusing our attention on getting SCOM up and running.  So, click Next to move along.

The Role Services dialog is the tricky one since you need just about all of the services selected to pass the prerequisite checks for SCOM. So, select all of the check boxes to be on the safe side. At some point, I’ll follow up with a blog post on what you can disable. See below for what the Role Services page should look like before selecting the Next command.

Once you have selected every service – click Next to go to the Confirmation page and then click Install. With any luck, you should get to the Results page indicating “Installation succeeded”.

Go ahead and click the close button for the Add Roles wizard.  Time for a quick test to see if IIS installed. go ahead and launch IE 8. If you are running with Enhanced Security Configuration, we’ll deal with that in a moment since you’ll need to download more software from the web.

In the IE8 address bar, type http://localhost. If all went well, you should see the following:

Go ahead and close the browser for now. You can also close the Initial Configuration Tasks dialog and you might want to check the box that says, “Do not show this window at login”.

As soon as the dialog closes, Windows Server 2008 R2 launches the Server Manager. At this point, let’s reconfigure “Configure IE ESC” so that you can browse the web without the annoying prompts. NOTE: It is Microsoft’s recommendation to have this feature on for production servers,, but we are just trying to get things spun up the first time with ease. So, be sure to turn ESC back on once everything is running well for you.

Go ahead and click on the Configure IE ESC link as shown below.

Then select Off for both Administrators and Users as shown below and click OK.

Step 1.5 – Installing AJAX

There is one more add-on that you must install called ASP.NET AJAX 1.0 that you can download from here. It’s also listed in the download links below. Just click on the Download button on the web site and run through the installation taking all of the defaults.

Step 2 – Installing SQL Server 2008 R2

I’m taking a gamble here since support for using SQL Server 2008 R2 was just announced on September 25, 2010 as part of a “FAST PUBLUSH” knowledge base article – see Support for System Center Operations Manager 2007 R2 that runs on a SQL Server 2008 R2 database. You don’t really need to check out KB Article 2425714 since I’ll guide you through the basics later on.

I’ll assume that you have access to the installation files for SQL Server 2008 R2. Check out the download links at the end of this posting for getting an evaluation copy. I recommend going with an Enterprise SKU since it has some cool features like row level compression for your database tables that can save you up to 20% or more disk space with a little more overhead on CPU. There are other great scalability features you can check out at the MSDN article titled Features Supported by the Editions of SQL Server 2008 R2.

Go to your installation folder and the click on Setup.exe. This displays the SQL Server Installation Center as shown below. You’ll want to select the Installation page.

Then select the hyperlink for New installation or add features to an existing installation as shown below.

I’m not going to run through all of the installation screens, but here are the basics to get you up and running. To keep things simple, I’ll assume you will use a Windows domain account with system administration permissions for all of the SQL Server services. I know this is not recommended, and you can change to lower privileged accounts once everything is working.

1. On the Setup Role page, select the SQL Server Feature Installation option.
2. On the Features Selection page, click Select All – maybe unclick SQL Server Books Online – since the topics will point to the web based topics by default.
3. On the Instance Configuration page, select the Default instance.
4. On the Service Accounts page, click on the “Use the same account for all SQL Server services” button and then enter in the Windows account that has system administration privileges. Yes – I did it again – you should really use a low privileged account, but this is a test environment.  Make sure to set the Startup Type for SQL Server Agent to Automatic as well.  DO NOT CHANGE THE Collation for the server. SCOM requires the use of SQL_Latin1_General_CP1_CI_AS.
5. On the Database Engine Configuration page, be sure to click “Add Current User” for the admin account. Like a good DBA, be sure to change your data directories off the C: drive.
6. On the Analysis Services Configuration page, click “Add Current user” and change the Data Directories as needed.
7. On the Reporting Services Configuration page, select the top option for “Install the native mode default configuration”.
8. You should be all set for installing SQL Server 2008 R2

With any luck, setup would have gone without a hitch and you can close down the SQL Server Installation Center dialog.

Step 3 – Install Microsoft Office 2010

If you want to author your own Management Packs, you need to install as a minimum Microsoft Word. SCOM 2007 R2 will work with older versions of MS Word, but this is a new box, so you might as well install all of Office. At least, you no longer need Outlook to send emails with SQL Server. NOTE: You will need to install the 32-bit version of Office for this all to work correctly. SCOM uses the Visual Studio Tools for Office runtime which is a 32 bit tool and only works with 32 bit versions of Office at the time of this blog posting. 

Go ahead and just install with the default “Big Button” install option – Install Now.

Step 4 – Install SCOM 2007 R2

Note – Before you do anything, make sure you didn’t skip Step 1.5 above. If you forgot, the installation process will politely remind you that you needed to install the ASP.NET AJAX component. 

Go to your installation location for System Center Operations Manager R2 release and run OPSMGR2007R2SEL.exe from your local disk. This file is a self extracting zip file with all the components needed to install SCOM 2007 R2.

When you run the .exe file, you should see the following message box kicking off the installation process.

Followed by the WinZip Self-Extractor UI.

Click the Unzip bottom. At the end of the unzipping process, you should get a message box indicating that 243 file(s) unzipped successfully. Once you click OK, the System Center operations Manager 2007 R2 Setup will be displayed. You may find it hidden behind another window.  It looks like this:

Click on the Browse this CD because we need to “manually” run the DBCreateWizard command that is located in the SupportTools\AMD64 folder as shown below.

Here are the relevant instructions from the the article “Support for System Center Operations Manager 2007 R2 that runs on a SQL Server 2008 R2 database”.

Step 4.1 - How to use the DBCreateWizard tool to install the OperationsManager database

Note You must run the DBCreateWizard tool on the server that is running SQL Server 2008 R2.

1. Open the <InstallationMedia>\SupportTools\AMD64 folder
   Note <InstallationMedia> represents installation media of System Center Operations Manager 2007 R2.
2. Double-click the DBCreateWizard.exe file to start the Database Configuration Wizard.
   Note You may receive an error message when you start the wizard.
   For more information, click the following article number to view the article in the Microsoft Knowledge Base:

   938997 (http://support.microsoft.com/kb/938997/ ) Operations Manager 2007 Support Tools requirements

3. On the Welcome page, click Next.
4. On the Database Information page, perform the following operations:
   • Under Database Type, select Operations Manager Database.
   • Under Full Database Instance Name, select the instance of SQL Server where you want the database to be installed.
     Note An instance of SQL Server has the name of <ServerName>\<InstanceName>. The default instance has the name of <ServerName>.
   • Leave the Create New Database option selected.
   • Leave the SQL Port box blank.
     Note If you do not provide a port, the Database Configuration Wizard uses the port 1433 to connect to SQL Server. However, if the instance of SQL Server that you specify uses a port other than 1433 for connection, you must specify the port in the SQL Port box.
   • By default, the database name is OperationsManager under Database Name.
   • By default, the database size is 500 megabyte (MB) under Database Size.
5. Click Next.
6. On the Management Group page, perform the following operations:
   • Under Management Group name, specify a name for the management group.
   • By default, the BUILTIN\Administrators user group is specified under Configuration Administrator. If you want to specify a different user group, follow these steps: (NOTE: I just went with BUILTIN\Administrators) 
     1. Click Browse.
     2. In the Select Group dialog box, click Locations.
     3. In the Locations dialog box, select the domain that contains the user group that you want to specify, and then click OK.
     4. Under Enter the object name to select, type the user group, and then click Check Names.
     5. If the name that you typed becomes underlined, click OK.
     For more information, see the "Before you Start" section of the following Microsoft TechNet website:

     http://technet.microsoft.com/en-us/library/dd789046.aspx (http://technet.microsoft.com/en-us/library/dd789046.aspx)

7. On the Error Reporting page, optionally indicate whether you want to send error reports to Microsoft, and then click Next.
8. On the Summary page, review the configuration summary shown below, and then click Finish

If everything went according to plan, you will get a message box indicating that the “Database created successfully” and click OK.

Step 4.2 - How to use the DBCreateWizard tool to install the OperationsManagerDW database

Note You must run the DBCreateWizard tool on the server that is running SQL Server 2008 R2.

1. Open the <InstallationMedia>\SupportTools\AMD64 folder
   Note <InstallationMedia> represents installation media of System Center Operations Manager 2007 R2.
2. Double-click the DBCreateWizard.exe file to start the Database Configuration Wizard.
   Note You may receive an error message when you start the wizard.
   For more information, click the following article number to view the article in the Microsoft Knowledge Base:

   938997 (http://support.microsoft.com/kb/938997/ ) Operations Manager 2007 Support Tools requirements

3. On the Welcome page, click Next.
4. On the Database Information page, perform the following operations:
   • Under Database Type, select Operations Manager Data Warehouse Database.
   • Under Full Database Instance Name, select the instance of SQL Server where you want the database to be installed.
     Note An instance of SQL Server has the name of <ServerName>\<InstanceName>. The default instance has the name of <ServerName>.
   • Leave the Create New Database option selected.
   • Leave the SQL Port box blank.
     Note If you do not provide a port, the Database Configuration Wizard uses the port 1433 to connect to SQL Server. However, if the instance of SQL Server that you specify uses a port other than 1433 for connection, you must specify the port in the SQL Port box.
   • By default, the database name is OperationsManagerDW under Database Name.
   • By default, the database size is 500 megabyte (MB) under Database Size.
5. Click Next.
6. On the Summary page, review the configuration summary, and then click Finish.

Step 4.3 – Now it’s time to install SCOM 2007 R2

1. Click on the Hyperlink to “Install Operations manager 2007 R2”. Then, click Next on the welcome page.
2. Accept the license terms and click Next.
3. Type in a User Name and Organization and click Next.

Now comes the tricky part because we don’t want to install the Database components at this time as per the article “Support for System Center Operations Manager 2007 R2 that runs on a SQL Server 2008 R2 database”. This article neglects to tell you how not to install the Database components. Starting from the figure below:

Click on the icon drop down control next to the word Database as highlighted above.

Choose the command “This component will not be available.”. Once you’ve selected the command, the dialog should look like this:

• Enter in the name of your SQL Server into the dialog below and click Next.
  
  
• Enter in your Domain account name in the dialog below and press Next
  
• Enter in your domain name again for the SDK and Config Service Account and click Next.
  
• For the Web Console Authentication Configuration, choose the first option “Use Windows Authentication (Recommended)” and click Next.
• For the Customer Experience Improvement Program, chose the first option “Join the Customer Experience Improvement Program (Recommended). By doing this, we can see how you are using the product to determine if we are firing too many false alerts, or that you are not using certain reports, etc. This will help the product teams make rational decisions on feature enhancements based on your input. Click Next to continue. Then, click Install.

If life is treating you fairly, the installation should complete and display the following dialog.

You don’t want to Start the Console quite yet, but you do want to backup the Encryption Key when you Finish the wizard. just follow the steps for backing up the key to a box off-site from the original machine.  The instructions in the wizard are straightforward.

Step 4.3 - How to install System Center Operations Manager 2007 R2 Reporting

Note You must follow these steps on the SQL Server Reporting Services server.

1. Open Computer Management.
2. In the Computer Management MMC snap-in, expand System Tools, expand Local Users and Groups, and then click Groups.
3. Locate the following group:
   SQLServerReportServerUser$<hostname>$MSRS10_50.<SQLInstanceName>
4. Rename the group to the following by removing "_50" from the group name:
   SQLServerReportServerUser$<hostname>$MSRS10.<SQLInstanceName>
   
5. Install System Center Operations Manager 2007 R2 Reporting.
   For more information, see the following Microsoft TechNet Website: http://technet.microsoft.com/en-us/library/bb432143.aspx

   
   
   Important When you install System Center Operations Manager 2007 R2 Reporting, you should not install the Data Warehouse component. See below image for not installing the DW component.
   
   
   The installation process is very similar to installing the main SCOM 2007 tools, so I’ll skip the dialogs. Again, when you get to the “Operational Data Reports” wizard page, please select Yes, send operational data reports to Microsoft.

6. After you install System Center Operations Manager 2007 R2 Reporting, rename SQLServerReportServerUser$<hostname>$MSSRS10.<SQLInstanceName> back to original name SQLServerReportServerUser$<hostname>$MSSRS10_50.<SQLInstanceName>
   

Supported configurations for System Center Operations Manager 2007 R2
For more information about supported configurations for Operations Manager 2007 R2 that runs on a SQL Server 2008 R2 database, visit the following Microsoft Website: http://technet.microsoft.com/en-us/library/bb309428.aspx

You can now Exit the SCOM 2007 R2 installation program

Step 5 – Install the SQL Server Monitoring Management Pack

Go to the download center for the free SQL Server MP at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8c0f970e-c653-4c15-9e51-6a6cadfca363&displaylang=en

1. Go ahead and download the SQLServerMPGuide.doc file for detailed information regarding the MP.
2. Click Download for the SQLServerMP.msi file and then run it in place – or save it to your Downloads folder and then run the MSI.
3. For the installation folder, leave it to the default location under the Program Files (x86) folder structure. Also, select to Install the MP for Everyone.
4. Click Install to download the files.

The files are simply downloaded to the install location, you now need to import the MP files into the SCOM Operations Console.

1. Go to the Start menu, locate the Operations Console, and click on the command. If all the stars aligned, you should see a screen that looks like this for Monitoring when you select Windows Computers in the Monitoring tree.
   
2. Now, select the Administration workspace item and then highlight the Management Packs node in the tree as shown below:
   
3. Now choose the Import Management Packs… command as shown above.  In the Select Management Packs dialog, click on the + Add button and choose Add from disk … as shown below.
   
4. Go ahead and click Yes to search the web for dependencies, but they are all in download location C:\Program Files (x86)\System Center Management Packs\SQLServerMP. press Ctrl+A to select all files in the dialog and click Open. Then, click the Install button.
5. You will see two Discovery and Monitoring packs for SQL 2005 and SQL 2008. SQL Server 2008 covers both 2008 and 2008 R2 release.
6. Once the import is complete, press the Close command.
7. Now click back to the Monitoring workspace and then expand the Microsoft SQL Server node and select Computers as shown below. You might have to click inside the detail area and press F5 to see your SCOM SQL instance.
   
8. To Add and new instance to monitor, select the Administration tab and click Discover Wizard… hyperlink as shown below.
   
9. Select the Windows computers option and click Next.
10. Choose the Advanced discovery option and det the Computer and Device Classes to Servers only and click Next..
11. Choose the Browse for option for the Discovery Method and type in the computer name you want to add in the dialog area.  You can also browse for computers.
12. Use your admin Windows account for the Administration Account and click Discover.
13. Check the FQDN for the SQL machine that you want to discover and click Next.
14. Chose the Other action for the Agent Action Account and type in your Windows credentials, then click Finish. This will display the Agent Management Task Status. Wait for this to finish.  You should see a dialog like this completing the addition of the new box. SCOM will attempt to enumerate all of the SQL Server instances on the box. Then click Close.
    
15. If you go have to Monitoring and click on Windows Computers, you should now wee two box names. When you go to SQL Computers, you should see two instances as well.

 

Software Evaluation Download Sites

Are the “go to” links to download evaluation copies of the software described  in this blog post.

• Windows Server 2008 R2  (64-bit only) – 180 Day Trial - http://technet.microsoft.com/en-us/evalcenter/dd459137.aspx
• SQL Server 2008 R2 – 180 Day Trial - http://technet.microsoft.com/en-us/evalcenter/ee315247.aspx
• System Center Operations Manager 2007 R2 – 180 Day Trial - http://www.microsoft.com/systemcenter/en/us/operations-manager/om-try-it.aspx#downloads
• SQL Server Monitoring Management Pack – Free - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8c0f970e-c653-4c15-9e51-6a6cadfca363&displaylang=en
• Microsoft Office Professional 2010 – 60 Day Trial - http://technet.microsoft.com/en-us/evalcenter/ee390818.aspx. You’ll need this if you want to author your own management pack.
• ASP.NET Ajax Extensions 1.0 – Free - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ca9d90fa-e8c9-42e3-aa19-08e2c027f5d6&displaylang=en

Looking Forward to SQL PASS

As I continue to prepare for my SQL PASS session on the new SCOM MP for SQL Server, I’ll continue blogging – especially examples with using the new SQL Server 2008 R2 Monitoring MP.  I will also cover how the data collection with SCOM 2007 R2 can be used in conjunction with the SQL Server 2008 Data Collector and Management Data Warehouse and the SQL Server 2008 R2 manageability tools.

Please feel free to add a comment to the blog post and don’t forget to rate it!