On 10/14/2013 The SQL MP was updated. There is a huge number of updates, fixes, and new features that were released in this version (and the version which shipped in Sept 2013.) See the download link for all the updates, or the MP guide that ships with this version.
SQL Server Management Pack version 6.4.1.0 includes the following changes:
- Fixed CPU Utilization Monitor
- Fixed SQL Server seed discovery for WoW64 environments
- Alert severity of Average Wait Time Monitor was changed to Warning, added consecutive sampling to reduce noise, threshold was changed to 250
- Alert severity of SQL Re-Compilation monitor was changed to Warning, threshold was changed to 25. The monitor was disabled by default.
- Minor fixes
- New Dashboard for SQL Server 2012 DB
- New Monitors and Rules – only for SQL 2008 and SQL 2012
- Collect DB Active Connections count
- Collect DB Active Requests count
- Collect DB Active Sessions count
- Collect DB Active Transactions count
- Collect DB Engine Thread count
- Thread Count monitor
- Transaction Log Free Space (%) monitor
- Transaction Log Free Space (%) collection
- Collect DB Engine CPU Utilization (%)
- CPU Utilization (%) monitor for DB engine
- Buffer Cache Hit Ratio monitor
- Collect DB Engine Page Life Expectancy (s)
- Page Life Expectancy monitor
- Collect DB Disk Read Latency (ms)
- Collect DB Disk Write Latency (ms)
- Disk Read Latency monitor
- Disk Write Latency monitor
- Collect DB Transactions per second count
- Collect DB Engine Average Wait Time (ms)
- Average Wait Time monitor
- Collect DB Engine Stolen Server Memory (MB)
- Stolen Server Memory monitor
- Collect DB Allocated Free Space (MB)
- Collect DB Used Space (MB)
- Collect DB Disk Free Space (MB)
- SQL Re-Compilation monitor
- SPN monitor improved
- Support for special symbols in DB names.
- Improved AlwaysOn seed discovery
- Run As configuration changes to support Low privilege for SQL Server 2012 Cluster
- Improved performance of AlwaysOn discovery
- Custom User Policy Discovery and Monitoring performance optimization
- Hided AG health object from Diagram view
- Minor changes
| File | Display name |
| Microsoft.SQLServer.Library.mp | SQL Server Core Library |
| Microsoft.SystemCenter.Visualization.Component.Extensions.Library.mpb | The Visualization Component Extensions Library |
| Microsoft.SQLServer.2005.Discovery.mp | SQL Server 2005 (Discovery) |
| Microsoft.SQLServer.2005.Monitoring.mp | SQL Server 2005 (Monitoring) |
| Microsoft.SQLServer.2008.Discovery.mp | SQL Server 2008 (Discovery) |
| Microsoft.SQLServer.2008.Monitoring.mp | SQL Server 2008 (Monitoring) |
| Microsoft.SQLServer.2008.Mirroring.Discovery.mp | SQL Server 2008 Mirroring (Discovery) |
| Microsoft.SQLServer.2008.Mirroring.Monitoring.mp | SQL Server 2008 Mirroring (Monitoring) |
| Microsoft.SQLServer.2012.Discovery.mp | SQL Server 2012 (Discovery) |
| Microsoft.SQLServer.2012.Monitoring.mp | SQL Server 2012 (Monitoring) |
| Microsoft.SQLServer.2012.AlwaysOn.Discovery.mp | SQL Server 2012 AlwaysOn (Discovery) |
| Microsoft.SQLServer.2012.AlwaysOn.Monitoring.mp | SQL Server 2012 AlwaysOn (Monitoring) |
| Microsoft.SQLServer.Presentation.mp | SQL Server 2012 Presentation |
If I were deploying this MP as an update, or to a new environment, there are some configuration scenarios I’d want to ensure I had covered.
1. The first step is to import the MP’s that are applicable to your environments (DUH!)
2. Next up, we need to configure security, in the form of Run As accounts. I previously did a deep dive on configuring security for SQL on a previous version of this MP, available here http://blogs.technet.com/b/kevinholman/archive/2010/09/08/configuring-run-as-accounts-and-profiles-in-r2-a-sql-management-pack-example.aspx. I covered a lot of the concepts of RunAs there which I wont rehash fully here. But I will cover what I consider the most common scenario.
Why do we have RunAs accounts?
We use RunAs accounts when we need a Management Pack workflow to run under the credentials of a different account than the default agent action account.
Generally, the situation is that the default agent action account does not have enough rights and privileges to perform the action that must be taken in the management pack. This is typical under two scenarios:
- The application being monitored has its own security model and access list, and does not necessarily share the rights of the Operating System (e.g. SQL Server)
- The default agent action account is running under a low-priv domain user account, and does not have enough rights for typical management pack operations (rare)
However, the SQL team has restricted the NT AUTHORITY\SYSTEM (Local System) SQL login, and does not allow the “SA” (System Administrator) right for this account. In this case, the Local System account has full rights to monitor the server OS, however, does not have enough rights to discover and monitor the SQL application and databases. So, we need to configure and use a Run-As account(s) to manage access for the SQL workflows only, to execute under this Run-As account. This account(s) can be created and fully managed by the SQL team. This is the MOST common scenario.
MOST customers I work with use Local System as the default agent action account. This is also what I always recommend, unless a company has a VERY specific and well thought out reason they can’t do this. Local System is a low maintenance account on each agent that generally has local administrative rights to that operating system only. Another alternative is to use a domain user account as the default agent action account, and place this account in the Local Administrators group on the operating system. However, there are some management packs that don’t support this method and require local system for the default agent action account, so be warned.
The SQL Example
When SQL 2005 is installed by default, it places some default security to the SQL instance. BUILTIN\Administrators (Local Administrators Group from the OS) and NT AUTHORITY\SYSTEM (Local System) are automatically added to the SQL Security access list, and provided with SA (SysAdmin) server role access to the instance.
When SQL 2008 is installed by default, it no longer places BUILTIN\Administrators in the SQL security access list. The install of SQL 2008 now prompts the installer to give SQL a user account, or Group, to grant SA (SysAdmin) rights to. If installing on a standalone instance, NT AUTHORITY\SYSTEM (Local System) is still granted SA rights. If installing on a clustered instance, NT AUTHORITY\SYSTEM (Local System) is granted public rights, but not SA.
When SQL 2012 is installed by default – we no longer place BUILTIN\Administrators in the security access list, AND we restrict NT AUTHORITY\SYSTEM to “public” access.
Most savvy SQL customers would always remove the BUILTIN\Administrators login to SQL to harden the SQL security access. Some will additionally restrict NT AUTHORITY\SYSTEM (Local System) to have public server role only, removing the SA rights. There are pros and cons to restricting Local System from SA, that isn't the purpose of this blog post.
Moving forward… in this example, we have decided we want to keep using Local System for the default agent action account, and set up a RunAs account for access to SQL. At a very high level, this will involve the following:
- Create a Run As account using a Domain User credential.
- Associate this Run As account with one or more Run as profiles.
Step 1: Create a Run As Account
1. Open the console and browse to Administration pane > Run As Configuration > Accounts.
2. Create a Run As Account of type = Windows
3. Give it a good display name, such a “SQL MP Monitoring Account”
4. Type in the credentials for username, password, and domain.
5. On the next pane – you are asked about a distribution security option. You need to choose “Less Secure” or “More Secure”. This REALLY warrants a good discussion.
“Less Secure” just means distribute this credentials to ALL Health Services in the management group.Once you create the RunAs account, you can open it back up to handle distribution. Go to the distribution tab:
“More Secure” just means distribute this credential ONLY to Health Services that I EXPLICITLY define.
If you want to see a detail explanation of the differences, see my previous post on this HERE. For this post, let me summarize:
“Less Secure” should likely never be used. In my opinion, it is not very feasible for any typical deployment of OpsMgr. Always use “More Secure”
Click “Add” and now we can add the Health Service objects we want to send this account to. In this example – I am sending this credential to all my SQL servers. Our only option here is to search by name. If you have a good naming standard – this is fine. If you don’t… this will be a bit painful initially. Luckily, I have the term “SQL” or “DB” in almost all my SQL server names, so this is easy enough for me – I type in “DB” and hit search, and add all my SQL servers, one by one.
Click “OK” and you are done.
Behind the scenes – all of these Health Services are notified of a config update – they download their new config and get the new credential.
Step 2: Associate the Run As Account to a Run As profile.
Now the fun begins! Run As Profiles are objects that are shipped within Management Packs. You don’t create RunAs Profiles in the UI – they will come delivered with the MP that needs them. Each workflow or module in an MP can *optionally* attempt to load a Run As Profile (if it is associated with a Run As Account for that system). If the Run As Profile is not associated with anything (default) then the workflow will run under the Default Agent Action Account credentials, like any other workflow.
Here is an XML example from the SQL MP:The monitor which inspects SQL Service pack version is below:
Note the highlighted part in yellow. This instructs the workflow to try and run this workflow as the “SQLProbeAccount” profile if it is associated for this HealthService.
So… Workflow tries to load a profile > Profile is associated with a Run As account > Run As account contains a credential for execution.
What you will see – is a new MonitoringHost.exe process will spin up on the agent monitored server, and execute any workflows that need to run under this credential.
Make sense?
Ok – let’s associate!The SQL MP includes 3 Run As profiles. These are:
- SQL Server Discovery Account
- Used for discoveries and discovery based datasources
- In the XML – referenced as: “SQLDiscoveryAccount”
- SQL Server Monitoring Account
- Use for monitoring workflows and monitoring based datasources
- In the XML – referenced as: “SQLProbeAccount”
- SQL Server Default Action Account
- Used to elevate Monitoring tasks in the console.
- Used for when the default agent action account is extremely low priv (rare)
- In the XML – referenced as: “SQLDefaultAccount”
Let’s start with the “SQL Server Discovery Account” Profile. (Oh how I WISH MP developers would name a PROFILE with the WORD “Profile” instead of “Account”…. but I digress)
1. Open the Properties of this profile. On the Run As Accounts page, click “Add”.
2. Select your “SQL MP Monitoring Account”.
3. Now we have to choose “All targeted objects” or “A selected class, group, or object”. Essentially – you should choose “all targeted objects”. Choosing “All Targeted Objects” means that anywhere the profile is associated with a workflow – load and use the defined account (SQL MP Monitoring Account) on all systems.
It is a best practice for all management packs with a discovered class to use a “seed” discovery. This discovery targets ALL agents (or servers, or operating systems) and runs a lightweight registry discovery. Then, from the instances discovered in the “seed” discovery, you can target that new class, with your more in-depth role/application based discoveries. Then – a secondary best practice – is to NEVER load a RunAs Profile against your seed discovery. This is covered here: http://social.technet.microsoft.com/wiki/contents/articles/worst-practice-adding-a-run-as-profile-to-your-seed-discovery.aspx
In previous SQL MP’s – they lacked a seed discovery, and therefore it was more complicated to configure RunAs. However, in this version, there is a seed discovery class that does not use RunAs, so we can now choose “All Targeted Objects” Yay!
If you have a very complex environment, you could create groups of SQL computers and target those groups or classes much more granularly here, if necessary.
Next – let’s examine the SQL Server Monitoring Account profile.
1. Open the Properties of the SQL Server Monitoring Account profile. On the Run As Accounts page, click “Add”.Last – let’s examine the SQL Server Default Action Account profile.
2. Select your “SQL MP Monitoring Account”.
3. Now we can choose “All targeted objects”
1. This is a new Run As profile that showed up with Version 6.1.314.36 of the SQL MP. A deeper analysis shows that this profile is primarily used for:***Note – the above practice will work for simple implementations. You might see some errors where there are issues with a Run-As account for specific systems, such as where we detect SQL on the registry, but do not wish to monitor it. For those systems you should disable the SEED discovery and run a Remove-SCOMDisabledClassInstance. See an overview of that concept here: http://om2012.wordpress.com/2013/06/09/how-to-remove-objects-from-monitoring-remove-scomdisabledclassinstance/
Technically – this profile *shouldn’t* be needed. If you didn’t associate it, we would use the default agent action account to handle all of these tasks, and this would work fine *except* for the tasks. If you didn’t need or want to use the Tasks in the MP with elevated rights – then I’d lean toward ignoring this profile and leaving it un-associated. If you DO need to use, and elevate the tasks provided in the MP to use a special account – then configure this profile.
- Tasks
- Monitoring workflows that don’t need any special rights for the datasource used (event log, perfmon, scripts that don’t access SQL)
- Write actions
The kind of “noise” you will see – until you take care of the RunAs account security, will be alerts such as:
Run As Account does not exist on the target system or does not have enough permissions
Management Group: OMMG1. Script: DiscoverSQL2012FileGroups.js : Cannot login to database [DB01.opsmgr.net][MSSQLSERVER:AppController]
Operations Manager failed to start a processIf you have not distributed the RunAs Account to a system, but we discovered SQL on it, you will see:
The process started at 11:59:18 AM failed to create System.PropertyBagData, no errors detected in the output. The process exited with 0
Command executed: "C:\Windows\system32\cscript.exe" /nologo "GetSQL2008DBFilesFreeSpace.vbs" "DB04" "DB04.opsmgr.net" "MSSQLSERVER"
Working Directory: C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Monitoring Host Temporary Files 454\13680\
One or more workflows were affected by this.
Workflow name: Microsoft.SQLServer.2008.Monitoring.DBLogFileSpaceMonitor
Instance name: mastlog
Instance ID: {8CF4EB67-40E8-CA73-22AE-455BC4A83547}
Management group: OMMG1
System Center Management Health Service Credentials Not Found Alert MessageReview:
An account specified in the Run As profile "Microsoft.SQLServer.SQLDiscoveryAccount" cannot be resolved.
This condition may have occurred because the account is not configured to be distributed to this computer. To resolve this problem, you need to open the Run As profile specified below, locate the account entry as specified by its SSID, and either choose to distribute the account to this computer if appropriate, or change the setting in the profile so that the target object does not use the specified account.
Note: you may use the command shell to get the Run As account display name by its SSID.
Management Group: OMMG1
Run As Profile: Microsoft.SQLServer.SQLDiscoveryAccount
Let’s take a break and review.
- We need to use a Run As account whenever we need a Management Pack workflow to run under the credentials of a different account than the default agent action account.
- The Run As account is the credential (username and password)
- The Run As account needs to be distributed to Health Services (More Secure and a Less Secure)
- Discoveries, Rules, Monitors (and their associated data sources) can be configured to use a Run As Profile.
- For a Run As profile to work, it must be associated correctly with a Run As account.
- There is a lot of flexibility in how we can associate the account to the profile.
So – the example above will work really well for customers, where all their SQL servers are tightly secured, and where all their SQL servers are managed by the same SQL team, in the same trusted domain, and can use the same SQL Run As Account. This allows success for a large number of environments.
But – what if the corporate SQL team only “owns” or supports half of the SQL servers in the management group, while the other SQL servers are owned and supported by various application teams?
What if we want to monitor ALL SQL servers in the management group, but need to use different Run As accounts depending on their domain, or support team? Maybe for some SQL servers, we don’t want to use any Run As accounts at all, and just use the default action account? Read on:
Scenario #5 You use Local System as the default action account.
Contoso.com is a global IT services firm. Their OpsMgr Management Group has about 500 SQL servers discovered, a mix of SQL 2005 and SQL 2008, with a handful of SQL 2008R2 instances. Their corporate IT SQL team manages 400 of these SQL instances. The remaining 100 SQL servers are not supported by the corporate SQL team, they are one-off SQL instances which are specific to applications, and managed by the application owners.To configure the SQL MP and Run As – we will use a very similar plan to what we did above. However, we will need to be more precise in two areas:
Of the 400 SQL servers supported by the corporate IT SQL team, 20 of these are in a highly secured domain disconnected from the corporate IT domain.
The goal is to develop a Run As strategy which will accommodate all SQL servers to be monitored.
All agents will be deployed as Local System for the default agent action account, as that is the standard for the Enterprise Monitoring Team.
All SQL security settings on the 400 corporate IT SQL team servers has been hardened and standardized. The SQL security settings for the 100 application owners SQL instance is largely unknown. Some could be hardened, but it is assumed most will be left to default settings performed at the installation.
- Run As account distribution
- Use Groups for Run As profile association.
- Corp IT SQL Run As account
- Corp IT SQL Run As account for the high security domain
- Non-Corp IT SQL team (Application owners) Run As account
- Corp IT SQL Computers
- Corp IT SQL Computers High Security
- Non Corp IT SQL Computers
1. Create the three RunAs accounts just like we did above. Ensure each account will have Local Administrator and SQL SysAdmin rights over the correct OS and instance.
2. Distribute the correct Run As accounts to the appropriate Health Services. For Non-Corp IT SQL instance, we will attempt to use the Default Agent action account to monitor SQL. If Local System does not have enough rights to SQL, we will then distribute and associate our RunAs account, and tell the application owner to make our special Run As account a local admins and SQL admin if they want their SQL server monitored.
3. On the Run As Profile Associations – choose “Group” instead of “Class” this time. Select the group from the object picker.
Now – we simply can make sure the right computers/engines are members of the appropriate group. If you want a Non-Corp SQL instance to try and use the default agent action account for SQL monitoring – just don’t distribute the Run As account, and don’t add them to the “Non-Corp IT SQL Computers” group. Then they will have NO association, no credential, and will try local system. If this fails to work – you can simply have a standard document for the application owner for how to configure their SQL server for proper monitoring. This takes the burden off the OpsMgr administrator.
The thing to keep in mind here – is the profile association works just like overrides. More specific wins. So you could have the group associations just like above, but if you had a special one-off instance that needed its OWN run as account – you can associate that specific DB Engine to a very specific Run As account, and it would take priority over the conflicting group association.
What’s Next?
Ok, that covers the security.
Once this is done – lets spot check and make sure we are discovering our key components.
The first view should be the Microsoft SQL Server \ Computers view, where we should see all our computers that have SQL installed, and their Windows Computer rollup health:
Next up – expand Microsoft SQL Server \ Server Roles \ Database Engines view – and make sure all SQL instances are discovered:
Next – lets check out the discover databases view:
Finally – check out the NEW Dashboard view (for SCOM 2012) It is pretty slick.
Optional Configuration:
SQL Agent Job Discovery.
One of the first things you might notice is that SQL Agent Jobs are not discovered by default, SQL services only monitor if set to AUTO statup, and some rules and monitors are disabled out of the box to limit noise. You might consider enabling these. Most customers I know like to monitor SQL agent Jobs individually. To discover these, I wrote a previous article at http://blogs.technet.com/b/kevinholman/archive/2011/08/05/how-to-monitor-sql-agent-jobs-using-the-sql-management-pack-and-opsmgr.aspx
Also – see the guide on how to configure additional optional configurations.
SQL Instance Discovery
You might also want to exclude specific SQL instances by name, or stop monitoring of SQL express or Windows Internal Database. See: http://blogs.technet.com/b/kevinholman/archive/2010/02/13/stop-monitoring-sql-express-and-windows-internal-database.aspx
SQL Database Discovery
Ok, above we controlled which SQL Instances get discovered, but perhaps there are specific database you never wish to monitor by name. We support excluding specific databases in the Database discovery. For instance, perhaps you wish to never monitor your “model” database. You could create a group, dynamically populated of SQL databases where name = Model, and then go disable all the rules and monitors in the MP which target the SQL database class. OR – we can choose to never discover these DB’s in the first place. Using an override on the database discovery, we can accomplish this. There are two discoveries that handle discovering SQL 2008 and 2012 databases:
Override each one, and input the names of the DB’s you wish to undiscover and not monitor. (comma separated)
Save this Override to your SQL Overrides MP which you should create for storing all your SQL MP modifications:
Disabled workflows:
There are many disabled rules in the MP by default – once you have configured, discovered, and TUNED your default settings of the management pack, consider reviewing these. Search for the section “Disabled Rules” in the MP guide under Key Monitoring Scenarios.
Next – check out the Performance views – there are many more counters collected in this newer version of the MP than in previous MP’s:
And the reports included:
Kaynak : Kevin Holman
l
