ME Trial week 6 – Instance Config

Hidden, ME Trial / By

minion enterprise-01

A quick glance behind, and ahead

Last time, we introduced the Service Properties module.

This time we’ll look at the Instance Config Module.

The Instance Config Module

The Instance Configuration module collects and manages sp_configure settings – the global configuration settings – from managed servers.

What can we do with this? Well, you can see all of your sp_configure settings for all managed servers, for starters. With just a query, you can see which production servers have ‘optimize for ad hoc workloads’ off, for example. And, you can update sp_configure settings across the enterprise; it’s just another update statement to change ‘cost threshold for parallelism’ to 10, wherever it’s at the default setting (5).

Objects in the Instance Config Module

Tables

  • Collector.InstanceConfig – Stores collected instance configuration values from the servers.
  • dbo.InstanceConfigValue – Holds the standard values to enforce across instances. Consider this the “gold standard” for all of your servers.
  • History.InstanceConfig – Holds a history of Instance Config alerts.

 

Views

  • Collector.InstanceConfigCurrent – Provides the most recent collection of instance configuration data.
  • Collector.InstanceConfigPrevious – Provides the next-to-most recent collection of instance configuration data.

 

Jobs

  • AlertInstanceConfig– Sends an alert for config values that are out of compliance, based on the last collection time from the server.  The job then puts a record of the alert into the history table.  Note that if the action is set to ‘Enforce’, then this job sets Push = 1 for the enforced values in the dbo.InstanceConfigValue table.  (The CollectorInstanceConfigPush job subsequently pushes the value to the servers, and records.)
  • CollectorInstanceConfigGet – Retrieves the current config values from each server.
  • CollectorInstanceConfigPush – Pushes the enforced values to each server that is out of compliance.

 

Setup

To set up the instance config module, run the executable C:\MinionByMidnightDBA\Collector\InstanceConfigDefaultValuesGET.exe. This pulls the initial values into the dbo.InstanceConfigValue table.  You are now ready to customize sp_configure values, so ME can push them to managed servers.

IMPORTANT: The sytem assumes, at least for this initial quick load, that the current values are the ones you want the server to have.

Example: Push new sp_config values

Let’s take an example. Say we want to configure Svr1, Svr9, and Svr100 so that they have ‘optimize for ad hoc workloads’ enabled, and a ‘cost threshold for parallelism’ of 15. It only takes two steps.

Step one: Update the dbo.InstanceConfigValue table for those servers:

UPDATE  dbo.InstanceConfigValue
SET     DesiredValue = 1
      , IsAdvancedOption = 1
      , Push = 1
  --, Action = 'Enforce'
WHERE   InstanceID IN ( SELECT  InstanceID
                        FROM    dbo.Servers
                        WHERE   ServerName IN ( 'Svr1', 'Svr9', 'Svr100' ) )
        AND Name = 'optimize for ad hoc workloads';


 


UPDATE  dbo.InstanceConfigValue


SET     DesiredValue = 15


      , IsAdvancedOption = 1


      , Push = 1


                  --, Action = 'Enforce'


WHERE   InstanceID IN ( SELECT  InstanceID


                        FROM    dbo.Servers


                        WHERE   ServerName IN ( 'Svr1', 'Svr9', 'Svr100' ) )


        AND Name = 'cost threshold for parallelism';


Note that Push is set to 1. Also notice that we have “Action = ‘Enforce’” commented out. You can leave Action set to ‘Alert’, if you wish to be alerted when someone changes that value on the server. Or, you can set Action = ‘Enforce’, so that Minion Enterprise will change that value BACK to the setting configured in dbo.InstanceConfigValue.


Step two: Run the executable C:\MinionByMidnightDBA\Collector\InstanceConfigPUSH.exe to push the change to the servers; or, wait for the job “CollectorInstanceConfigPUSH” to run (it will push the changes out).


That’s all!




The Instance Config module is invaluable for standardizing shops – especially large shops! Write us with questions and comments any time at https://minionware.desk.com/, and get more information on our Minion Enterprise YouTube playlist.


You’re about halfway through your trial of Minion Enterprise.

Write to us today at MinionWareSales@MidnightDBA.com for a demo and a quote!


Next time we’ll talk about the Error Log Search module.