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

For more information about sp_configure settings, see https://msdn.microsoft.com/en-us/library/ms188787.aspx

Tables

Alert.InstanceConfigDefer

This table holds drive instance configuration orders. Insert a row here to prevent ME from sending instance configuration alerts for a particular instance, for a set period of time.

 
Column Type Description
ID bigint Primary key row identifier.
InstanceID bigint The instance ID of the instance in question, as defined in the table dbo.Servers.
Name varchar The name of the configuration option. Example: “optimize for ad hoc workloads”.
DeferDate date The date on which the deferment begins.
DeferEndDate date The date on which the deferment expires.
DeferEndTime time The time at which the deferment expires.

Collector.InstanceConfig

Stores collected instance configuration values from the servers.

Column Type Description
ID bigint Primary key row identifier.
ExecutionDateTime datetime The execution date and time, common to the run of a collection for a single instance.
InstanceID int The instance ID of the instance in question, as defined in the table dbo.Servers.
Name Varchar The name of the configuration option. Example: “optimize for ad hoc workloads”.
Minimum int The minimum value of the configuration option.
Maximum int The maximum value of the configuration option.
ConfigValue int The value that the configuration option was set to.
RunValue int The current run value of the configuration option.
 

dbo.InstanceConfigExceptions

This is an alert configuration table. Use this to tell ME about the instances that you do not want to alert on.

Name Type Description
ID bigint Primary key row identifier.
InstanceID bigint The instance ID of the instance in question, as defined in the table dbo.Servers. You can use InstanceID = 0 to apply an instance configuration exeption to all servers.
Name varchar The name of the configuration option. Example: “optimize for ad hoc workloads”.
 
To set an exception for ALL instance configuration options, set Name = ‘All’.
IsActive bit Determines whether the current row is active, and should be used.
Comment varchar For your reference only. Label each row with a short description and/or purpose.
 

dbo.InstanceConfigValue

Holds the standard values to enforce across instances.

You can use this table to alert or enforce the entire set of configuration values for one or more instances, if you wish. Or, you can alert or enforce any combination of values for servers. 

If, for example, you only ever want to alert on “optimize for ad hoc workloads”, you can simply insert a set of rows – one for each instance in dbo.Servers – with Name=’optimize for ad hoc workloads’ and Action=’Alert’.

 
Column Type Description
ID bigint Primary key row identifier.
InstanceID Int The instance ID of the instance in question, as defined in the table dbo.Servers.
Name varchar The name of the configuration option. Example: “optimize for ad hoc workloads”.
DesiredValue Bigint The value that you want the configuration option set to.
IsAdvancedOption bit Denotes whether the option is an advanced option or not.
Push bit Denotes whether to push the change (the desired value) to the target server(s), or not.
 
Note: When Action=’Enforce’ and the system detects a difference between the standard value (here) and the run value on the instance, Minion Enterprise will change Push=1. Once ME pushes that change to the instance, it will set Push back to 0. (In other words: ME manages the Push bit automatically.)
 
Note also that you can “manually” push config changes to server by setting Push=1, regardless of the Action value.
Action Varchar The desired action to take, upon finding a mismatch between the current value on an instance and the DesiredValue.
 
Valid values:
Alert
Enforce
 

History.InstanceConfig

This table holds a history of instance configuration setting alerts and enforcement actions.

Column Type Description
ID bigint Primary key row identifier.
InstanceID int The instance ID of the instance in question, as defined in the table dbo.Servers.
ServerName varchar The name of the server.
Name varchar The name of the configuration option. Example: “optimize for ad hoc workloads”.
DesiredValue int The value that you want the configuration option set to.
RunValue int The detected run value of the configuration option.
ExecutionDateTime datetime The execution date and time, common to the run of an alert for a single instance.
ServiceLevel Varchar A simple label for the level of the server’s importance, which determines the level of service that Minion Enterprise provides.
 
Example: Gold.
Action varchar The action taken, upon finding a mismatch between the current value on an instance is not the DesiredValue.
 
Examples: Alert, Enforce
AppName varchar Application name (as defined in dbo.Application, and tied to InstanceID in dbo.ServerAppRoleApp).
 

Stored Procedures

Alert.InstanceConfig

This procedure sends an alert for configuration values that are out of compliance, as compared to the latest collection for the server.  The procedure records the alert into the History.InstanceConfig table. 

Note: If Action is set to ‘Enforce’ for a setting in dbo.InstanceConfigValue, this procedure sets Push = 1 for that row.  (The CollectorInstanceConfigPush job subsequently pushes the value to the servers, and records.) 

Parameter Type Description
@EmailProfile varchar The name of the email profile to use, as defined in the SQL Server system table MSDB.dbo.sysmail_profile.
@ServiceLevel varchar A simple label for the level of the server’s importance, which determines the level of service that Minion Enterprise provides.
Example: Gold.
@IncludeDefer Bit Flag that determines whether to include the T-SQL alert deferment statements at the end of the alert email, or not. By default, this is enabled.
@IncludeException Bit Flag that determines whether to include the T-SQL alert exception statements at the end of the alert email, or not. By default, this is enabled.
@IncludeChange Bit
Flag that determines whether to include the T-SQL value change statements at the end of the alert email, or not. By default, this is enabled.
 
Example output, when @IncludeChange is enabled:
EXEC dbo.InstanceConfigValueUpdate @ServerName = 'Server1', @ConfigName = 'optimize for ad hoc workloads', @DesiredValue = 1, @Push = 0, @Action = 'Enforce';
 

Collector.InstanceConfigInsert

InstanceConfigGET.exe calls this procedure to insert data into the Collector.InstanceConfig table.

Important: This procedure is meant to be used by automated collectors; we recommend against using it manually. Therefore, the parameter set is only documented internally.

dbo.InstanceConfigValueInsert

The script InstanceConfigDefaultValuesGET.exe calls this procedure to insert data into the dbo.InstanceConfigValue table.

NOTE: The primary use of this SP is InstanceConfigDefaultValuesGET.exe, though you can run the SP by hand. Dbo.InstanceConfigValueInsert does not provide an @Action parameter; the default action is 'Alert'.  This prevents to keep the system from changing values on servers without the DBA’s knowledge.

 
Parameter Type Description
@ID int The instance ID of the instance in question, as defined in the table dbo.Servers.
@Name varchar The name of the configuration option. Example: “optimize for ad hoc workloads”.
@RunValue bigint The value of the configuration option you would like to alert on or enforce.
 
Example execution: 
EXEC dbo.InstanceConfigValueInsert
@ID = 1,
@Name = 'optimize for ad hoc workloads',
@RunValue = 1;

dbo.InstanceConfigValueUpdate

Use this procedure to update existing rows in the dbo.InstanceConfigValue table.

Parameter Type Description
@ServerName varchar The name of the instance in question, as defined in the table dbo.Servers.
@ConfigName varchar The name of the configuration option. Example: “optimize for ad hoc workloads”.
@DesiredValue bigint The value of the configuration option you would like to alert on or enforce.
@Push bit Denotes whether to push the change (the desired value) to the target server(s), or not.
If you set Push=1, the system will push this config value to the server regardless of the Action value.
@Action varchar
The desired action to take, upon finding a mismatch between the current value on an instance and the DesiredValue.
 
Valid values:
Alert
Enforce
 
Example execution: 
EXEC dbo.InstanceConfigValueUpdate
@ServerName = 'Server1',
@ConfigName = 'optimize for ad hoc workloads',
@DesiredValue = 1,
@Push = 1,
@Action = 'Enforce';
 

Setup.InstanceConfigDefer

Use this procedure to set up an alert/enforce action deferment.

Parameter Type Description
@ServerName varchar The name of the instance in question, as defined in the table dbo.Servers.
@Name varchar The name of the configuration option. Example: “optimize for ad hoc workloads”.  You can also use “All” to denote all configuration options.
@DeferDate date The date on which the deferment should begin.
@DeferEndDate date The date on which the deferment should expire.
@DeferEndTime Time The time at which the deferment should expire.
 
Example execution: 
EXEC Setup.InsatnceConfigDefer
@ServerName = 'Server1',
@ConfigName = 'optimize for ad hoc workloads',
@DeferDate = '10/10/2016'
@DeferEndDate = '10/12/2016'
@DeferEndTime = '22:00:00';
 

Setup.InstanceConfigException

This procedure is not yet in use.

Views

Collector.InstanceConfigCurrent

Provides the most recent collection of instance configuration data.

Each “Current” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:

  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr

Collector.InstanceConfigPrevious

Provides the next-to-most recent collection of instance configuration data. 

Each “Previous” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr
 

Jobs

AlertInstanceConfig-GOLD

Calls the procedure Alert.InstanceConfig for all servers with ServiceLevel = ‘Gold’, to alert on instance configuration values that are out of compliance. A server is out of compliane if one or more of its instance configuration settings (based on the last collection time from the server) do not match the standards set in dbo.InstanceConfigValue. 

The procedure also records the alert into the History.InstanceConfig table. 

Note that, for any given if the action is set to ‘Enforce’ in dbo.InstanceConfigValue, the procedure sets Push = 1.  The CollectorInstanceConfigPush job subsequently pushes the value to the servers, and records.

AlertInstanceConfig-SILVER

Calls the procedure Alert.InstanceConfig for all servers with ServiceLevel = ‘Silver’, to alert on instance configuration values that are out of compliance. A server is out of compliane if one or more of its instance configuration settings (based on the last collection time from the server) do not match the standards set in dbo.InstanceConfigValue. 

The procedure also records the alert into the History.InstanceConfig table. 

Note that, for any given if the action is set to ‘Enforce’ in dbo.InstanceConfigValue, the procedure sets Push = 1.  The CollectorInstanceConfigPush job subsequently pushes the value to the servers, and records.

AlertInstanceConfig-BRONZE

Calls the procedure Alert.InstanceConfig for all servers with ServiceLevel = ‘Bronze’, to alert on instance configuration values that are out of compliance. A server is out of compliane if one or more of its instance configuration settings (based on the last collection time from the server) do not match the standards set in dbo.InstanceConfigValue. 

The procedure also records the alert into the History.InstanceConfig table.

Note that, for any given if the action is set to ‘Enforce’ in dbo.InstanceConfigValue, the procedure sets Push = 1.  The CollectorInstanceConfigPush job subsequently pushes the value to the servers, and records.

CollectorInstanceConfigGet-GOLD

Calls the executable InstanceConfigGET.exe for all servers with ServiceLevel = ‘Gold’, to retrieve instance configuration information.

CollectorInstanceConfigGet-SILVER

Calls the executable InstanceConfigGET.exe for all servers with ServiceLevel = ‘Gold’, to retrieve instance configuration information.

CollectorInstanceConfigGet-BRONZE

Calls the executable InstanceConfigGET.exe for all servers with ServiceLevel = ‘Gold’, to retrieve instance configuration information.

CollectorInstanceConfigPush

Calls the executable InstanceConfigPUSH.exe for any server with a Push=1 row in dbo.InstanceConfigValue, to push enforced values to each server that is out of compliance.

Executables

InstanceConfigDefaultValuesGET.exe

This script retrieves initial instance configuration values into the dbo.InstanceConfigValue table, to act as the gold standard. This is an optional step; alternately, you can pick and choose the servers and/or sp_configure settings that should be alerted on (or enforced).

This executable is not scheduled as part of a job.

IMPORTANT: InstanceConfigDefaultValuesGET.exe retreives all of the instance configure values for all ServiceLevel=Gold (or silver, or bronze, depending on the input parameter) instances in dbo.Servers, whether or not those servers already have entries in dbo.InstanceConfigValue. Running the executable with preexisting values in dbo.InstanceConfigValue may result in duplicate entries. We highly recommend that you run this executable ONLY if dbo.InstanceConfigValue is empty.

Input parameters:

  • $Query – This parameter refers to the service level that the collector should operate on. Examples: Gold, Silver, Bronze. For more information on this topic, see the article “Executables and Service Levels”.

Example execution:

C:\MinionByMidnightDBA\Collector\InstanceConfigDefaultValuesGET.exe Gold

InstanceConfigGET.exe

Collects instance configuration data for the instances in the specified service level, and logs the results to Collector.InstanceConfig.

Input parameters:

  • $Query – This parameter refers to the service level that the collector should operate on. Examples: Gold, Silver, Bronze. For more information on this topic, see the article “Executables and Service Levels”.
Example execution:
C:\MinionByMidnightDBA\Collector\InstanceConfigGET.exe Gold

InstanceConfigPUSH.exe

Pushes the instance configuration values from dbo.InstanceConfigValue (where Push=1), to each server that is out of compliance.

The job CollectorInstanceConfigPUSH calls InstanceConfigPUSH.exe. This script takes sp_configure settings that are set to be pushed in the dbo.InstanceConfigValue table and sets the servers to those values. This is how you control your server-level settings.  You can change the settings for as many servers as you like at the same time.

Input parameters:

  • $Query – This parameter refers to the service level that the collector should operate on. Examples: Gold, Silver, Bronze. For more information on this topic, see the article “Executables and Service Levels”.
Example execution:
C:\MinionByMidnightDBA\Collector\InstanceConfigPUSH.exe Gold
 

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. 

To make changes and push them to managed servers:
  1. Update the dbo.InstanceConfigValue table, and set Push = 1. 
  2. Run InstanceConfigPUSH.exe to push the change to the server(s).
  3. The collector (job name CollectorInstanceConfigGet-GOLD) will run InstanceConfigGET.exe on its own schedule, and enter the current server values into Collector.InstanceConfig.  

By default, when the alert is created, it will simply alert you of any subsequent changes on the managed server. But you can update the Action column in dbo.InstanceConifigValue from 'Alert' to 'Enforce', to automatically change the value on the server back, as well as alerting you. 

Exceptions and Deferments

To except a server from a single instance configuration alert (or enforcement action), insert a row into dbo.InstanceConfigExceptions for that server and instance configuration option:
 

INSERT  INTO dbo.InstanceConfigExceptions
        ( InstanceID
        , Name
        , IsActive
        , Comment
        )
VALUES  ( 1
        , 'optimize for ad hoc workloads'
        , 1
        , 'This server should get no alerts on “optimize for ad hoc”.'
        );

 
To except a server from all instance configuration alerts (and enforcement actions), insert a row into dbo.InstanceConfigExceptions for that server with Name=’All’:
 

INSERT  INTO dbo.InstanceConfigExceptions
        ( InstanceID
        , Name
        , IsActive
        , Comment
        )
VALUES  ( 1
        , 'All'
        , 1
        , 'This server should get no alerts on any sp_configure option.'
        );

 
To defer alerts (or enforcement actions) for a server, for a single instance configuration, insert a row into Alert.InstanceConfigDefer:

INSERT  INTO Alert.InstanceConfigDefer
        ( InstanceID
        , Name
        , DeferDate
        , DeferEndDate
        , DeferEndTime
        )
VALUES  ( 1
        , 'optimize for ad hoc workloads'
        , '10/10/2016'
        , '10/12/2016'
        , '22:00:00'
        );

 
To defer alerts (or enforcement actions) for a server, for all instance configuration, insert a row into Alert.InstanceConfigDefer with Name=’All’:

INSERT  INTO Alert.InstanceConfigDefer
        ( InstanceID
        , Name
        , DeferDate
        , DeferEndDate
        , DeferEndTime
        )
VALUES  ( 1
        , 'All'
        , '10/10/2016'
        , '10/12/2016'
        , '22:00:00'
        );

 
 
https://minionware.desk.com/customer/portal/articles/2376731-instance-config-module
http://www.MinionWare.net