Month: October 2014

Installing the SQL Management Pack

Standard

In this blog post I going thru the steps I took to install and configure the SQL Management Pack. You can download the management pack from here

1. Create a Active Directory Domain User Account

  1. Start "Active Directory Users and Computers"
  2. Expand your domain and browse to the Organization Unit where you create your Service Accounts
  3. Click on the “Create a new user in the current container”

    CreateUserAccount

  4. On the “New Object – User” page file in the “First name:”,”Full Name:”,”User logon name:” and the “User logon name (pre-Windows 2000):” and click “Next”

    NewObjectUser

  5. On the "New Object – User" enter a "Password",">Confirm password", uncheck "User Must change Password at next logon" and check "User cannot change password","Password never expires" and click "Next"
  6. On the "New Object – User" click "finish"

You can also do this steps using Windows Powershell. The following windows Powershell commands needs to be run at an "administrator-level Windows Powershell" command prompt:

2. Making the Active Directory Account Member of the Local Administrators Group

  1. On the Database Server that you want to monitor start the "Server Manager"
  2. In the "Server Manager" expand "Configuration","Local Users and Computers","Groups"

    ServerManagerGroups

  3. In the "Groups" section double click the group "administrators"
  4. On the "Administrators Properties" page click "Add..

    AdministratorProperties

  5. On the "Select Users, Computers, Service Accounts, or groups" Type the above created account name and click "Check Names" then click "OK"
  6. On the "Administrators Properties" page, click "Apply" and the "Ok"

3. Grant the Active Directory Account Sys Admin Right on the SQL

  1. On the Database Server that you want to monitor start the "SQL Server Management Studio"
  2. In the "Connect to server" page, enter the login credentials and click "Connect"
  3. In the "Object Explorer" screen, expand "Security","Logins

    MicrsoftSQLServerManagementStudio

  4. Right click on "Logins" and click "New Login"

    SQLNewLogin

  5. In the "Login – New" screen click "Search"

    SQLSearch

  6. In the "Select User or Group" screen, change the location to "Entire Domain" and search for the above created account and click "OK"
  7. In the "Login – New" screen, in the "Select a Page pane, select "Server Role" and check "sysadmin" and click "OK"

    SQLsysadmin

  8. Close the "Microsoft SQL Server Management Studio"

4. Create a Run As account in System Center Operations Manager Console

  1. Start "System Center Operations Manager Console"
  2. Browse to the "Administration" pane
  3. In the "Administration" pane, expand "Run As Configuration" and click "Accounts"

    RunsAsAccounts

  4. Right Click "Accounts" and click "Create Run As Account

    CreateRunAsAccount

  5. On the "Introduction" page, Click "Next

    CreateRunAsAccountIntroduction

  6. On the "General Properties" page, select the "Run As account type", "Windows" and enter a "Display Name" and click "Next

    CreateRunAsAccountGeneralProperties

  7. On the "Credentials" page, enter the above created "User Name", "Password", "Confirm password", "Domain" and click "Next"

    CreateRunAsAccountCredentials

  8. On the "Distribution Security" page, select "More secure – I want to manually select the computers which the credentials will be distributed" and click "Create"

    CreateRunAsAccountDistributionSecurity

  9. On the "Completion" Page, click "Close"

    CreateRunAsAccountCompletion

  10. Right click the above created Run As Account and click "properties"
  11. On the "Run As Account properties – SQL Monitoring Run As Account" page, select the tab "Distribution"

    RunsAsAccountsDistribution

  12. On the "Distribution" page, click "Add"
  13. On the "Computer Search" page, enter the sql server that you want to monitor and click "Add" and the "Ok"

    ComputerSearch

  14. On the "Distribution" page, click "Apply" and the "OK"

4. Import the SQL Management Pack

  1. Copy the "SQLServerMP.msi" to a management server
  2. Double click "SQLServerMP.msi", check "I accept the terms in the license Agreement", and click "Install"

    SQLManagementPackInstall

  3. If a "User account Control" window pops up click "Yes"
  4. In the "System Center Monitoring Pack for SQL Server Setup" window, uncheck "Open Management Pack folder" and click "Finish"
  5. The default location for the Management Pack is "C:\Program Files (x86)\System Center Management Packs\System Center Monitoring Pack for SQL Server\6.5.1.0"
  6. Start the "System Center Operations Manager Console", go the "Administration" workspace and right click "Management Pack’s"

    ImportManagementPacks

  7. In the "Import Management Packs" screen, click "Add" and click "Add from Disk"

    AddfromDisk

  8. In the "Online Catalogue Connection" screen click "No"
  9. Browse to the location where you installed the "System Center Monitoring Pack for SQL Server" and select the management pack that you want to import. I selected:
    • Microsoft SQL Server Generic Dashboards
    • Microsoft SQL Server Generic Presentation
    • Microsoft SQL Server Core Library
    • Microsoft SQL Server Visualisation Library
    • Microsoft SQL Server 2008 (Discovery)
    • Microsoft SQL Server 2008 Mirroring (Discovery)
    • Microsoft SQL Server 2008 Mirroring (Monitoring)
    • Microsoft SQL Server 2008 (Monitoring)
    • Microsoft SQL Server 2008 (Presentation)
    • Microsoft SQL Server 2012 AlwaysOn (Discovery)
    • Microsoft SQL Server 2012 Alwayson (MOnitoring)
    • Microsoft SQL Server 2012 (Discovery)
    • Microsoft SQL Server 2012 (Monitoring)
    • Microsoft SQL Server 2012 (Presentation)
  10. On the "Select Management Packs" page, click "Install"

    SelectManagementPackInstall

  11. When the import is finished click "Close"

5. Associate Runs As Account with the Run As Profile

  1. Start the "System Center Operations Manager Console", go the "Administration" workspace, expand "Run As Configuration" and select "Profiles"

    RunsAsConfigurationProfiles

  2. Right click "SQL Server Discovery Account" and select the properties

    SQLServerDiscoveryAccountProperties

  3. On the "Introduction" page, click "Next"
  4. On the "General Properties" page, click "Next"
  5. On the "Run As Accounts page, click "Add"

    RunsAsAccountsAdd

  6. On the "Add a Run As Account screen, Select the "SQL Monitoring Run As Account" check "All Targeted Objects" and click "OK"
  7. On the "Run As Accounts page, click "Add"
  8. On the "Run As Accounts page, click "Save"
  9. On the "Completion" page, click "SQL Monitoring Run As Account"
  10. The "Run As account Properties – SQL Monitoring Run As Account will be opened. Click "Ok"
  11. On the "Completion" page, click "Close

    RunsAsAccountsCompleted

  12. Repeat the above steps for "SQL Server Default Action Account" and "SQL Server Monitoring Account"

When finished the sql management pack should look something like this.

SQLManagementPackReady

If you want more information on the SQL management pack. Take a look at Kevin Holeman blog. This blog post