ME Trial 5 – Service Properties

Hidden, ME Trial / By

minion enterprise-01

A quick glance behind, and ahead

So last time, we talked about ME schemas, and introduced the Drive Space module.

This time we’ll look at the Service Properties module.

The Service Properties Module

The ME Service Properties module collects information about service properties, including the startup account, service status, and the start mode.

What can we do with this? Minion Enterprise alerts on stopped services as part of this module. You can also use this information to assess the impact of an account password change.

IMPORTANT: ME will alert on SQL services where the service start mode is Auto, and where the status is not “Running” or “Unknown”. This means that any SQL service with a start mode of Manual or Disabled will not show up in an alert. Likewise, services which are not SQL services are not alerted on automatically. You can of course devise your own alerts for non-SQL services.

The Service Properties module and service down alerts do not require any setup. Once you have entered instances into the dbo.Servers table, along with their associated service level, Minion Enterprise automatically collects service data and performs alerts as necessary.

Objects in the Service Properties Module

Tables

  • Collector.ServiceProperties – Stores the collections of service properties data.
  • History.ServiceStatus – Holds a history of service status alerts.

Views

  • Collector.ServicePropertiesCurrent – Provides the most recent collection of service properties data.
  • Collector.ServicePropertiesPrevious – Provides the next-to-latest collection of service properties data.

Stored Procedures

  • Alert.ServiceStatus – Logs alerts and sends the alert email for downed SQL services.

Alert Jobs

  • AlertServiceStatus-GOLD – Calls the proceure Alert.ServiceStatus to alert on downed services on Gold level servers.
  • AlertServiceStatus-SILVER – Calls the proceure Alert.ServiceStatus to alert on downed services on Silver level servers.

NOTE: ME does not provide service down alerts for Bronze servers by default, as they are considered lowest priority.

Examples: Service Properties Use Cases

Let’s talk about a few use cases:

  • Account password changes
  • Service account audit
  • Detect changes in the service settings

Account password changes: Use the collected service information to see the impact of an account password change. For example if it’s time to rotate the password for the account DOMN\SQLapp1, it would be an excellent idea to first check for SQL Server services that depend on that account:

SELECT  ExecutionDateTime
      , ServerName
      , ServiceLevel
      , ServiceName
      , StartName
      , Status
      , StartMode
FROM    [Collector].[ServicePropertiesCurrent]
WHERE   ServiceName LIKE '%SQL%'
        AND StartName = 'DOMN\SQLapp1';

We make sure to query the Collector.ServicePropertiesCurrent view, so we only pull up the most recent set of collected data.

Service account audit: The Service Properties collection makes it easy to research what accounts are in use for SQL services across your enterprise. The following query is a great place to start. It gives you a list of SQL services and their startup accounts:

SELECT  ServiceName
      , StartName
      , COUNT(*) AS [count]
FROM    [Collector].[ServicePropertiesCurrent]
WHERE   ServiceName LIKE '%SQL%'
GROUP BY ServiceName, StartName
ORDER BY StartName, ServiceName;

If something strange arises, we can drill down further into specifics:

SELECT  ServerName
      , ServiceName
      , StartName
FROM    [Collector].[ServicePropertiesCurrent]
WHERE   ServiceName LIKE '%SQL%'
        AND StartName = 'DOMN\SomeUser'
ORDER BY StartName, ServiceName;

Detect changes in the service settings: Minion Enterprise collects data at regular intervals and stores that data over time. So we have a built in way to examine changes in service settings – for example, discovering when a service’s start mode changed from Auto to Manual.  We could write a query against the base table Collector.ServiceProperties, but it’s very handy to write change detection queries against the provided views:

SELECT  curr.ServerName
      , curr.ServiceLevel
      , curr.ServiceName
      , curr.StartName
      , curr.Status
      , curr.StartMode
      , prev.StartMode AS PreviousStartMode
FROM    [Collector].[ServicePropertiesCurrent] curr
INNER JOIN [Collector].[ServicePropertiesPrevious] prev
        ON curr.InstanceID = prev.InstanceID
           AND curr.ServiceName = prev.ServiceName
WHERE   curr.StartMode != prev.StartMode;

We can use a very similar query to detect when the service account has changed:

SELECT  curr.ServerName
      , curr.ServiceLevel
      , curr.ServiceName
      , curr.StartName
                  , prev.StartName AS PreviousStartName
      , curr.Status
      , curr.StartMode
FROM    [Collector].[ServicePropertiesCurrent] curr
INNER JOIN [Collector].[ServicePropertiesPrevious] prev
        ON curr.InstanceID = prev.InstanceID
           AND curr.ServiceName = prev.ServiceName
WHERE   curr.StartName != prev.StartName;

 

 

There are a few excellent uses for the Service Properties module. And, this is another module that’s good excellent for all servers, regardless of whether they run SQL Server. Write us with questions and comments any time at https://minionware.desk.com/, and get more information on our Minion Enterprise YouTube playlist.

Next time we’ll talk about the Instance Config module.