Home
  •  

AK96574

Monitor_AK96574 (UnitMonitor)

SQL Server has differences between configured and installed configuration values

Knowledge Base article:

External

http://go.microsoft.com/fwlink/?LinkId=211274

Element properties:

TargetMicrosoft.KnowledgeServices.SQLServer.DBEngine
Parent MonitorSystem.Health.ConfigurationState
CategoryAlert
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.KnowledgeServices.Library.PowerShellMonitorEx
RemotableTrue
AccessibilityPublic
Alert Message
SQL Server has differences between configured and installed configuration values
<Details>
<Content>
The configured and installed value for some SQL Server configuration option are different. You either have not run the RECONFIGURE command to install a configuration option or have not restarted SQL Server for the configuration option change to take affect. Review all SQL Server configuration option values to ensure you have them configured to the appropriate value.
</Content>
<CollectedInformation>
<Info>
<Name>Configuration Differences Found</Name>
<Value>{0}</Value>
</Info>
<Info>
<Name>Configuration Name, Value, ValueInUse, IsDynamic</Name>
<Value>{1}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

<UnitMonitor ID="Monitor_AK96574" Comment="SupportTopic=TBD;VersionNumber=1.0.0.0;" Accessibility="Public" Enabled="true" Target="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessage728e88055583486e9e1d46b5fcb1307d">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='DifferenceCount']$</AlertParameter1>

      <AlertParameter2>$Data/Context/Property[@Name='DifferenceList']$</AlertParameter2>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Success" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>AK96574.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>ConnectionString</Name>

        <Value>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.DBEngine"]/ConnectionString$</Value>

      </Parameter>

      <Parameter>

        <Name>Platform</Name>

        <Value>$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/Platform$</Value>

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($ConnectionString,$Platform)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

$scriptargs | add-member NoteProperty "ConnectionString" $ConnectionString

$scriptargs | add-member NoteProperty "Platform" $Platform



# Set up the output

$global:scriptoutput = new-object psobject

$scriptoutput | add-member NoteProperty "AlertRaised" $false

$scriptoutput | add-member NoteProperty "DifferenceCount" ""

$scriptoutput | add-member NoteProperty "DifferenceList" ""

function AdvisorRule($scriptargs, $scriptoutput)

{

	$scriptoutput.AlertRaised = $false

	$scriptoutput.DifferenceCount = 0

	$scriptoutput.DifferenceList = ""



	if ($scriptargs.Platform -eq "x64")

	{

		$iDefaultMinServerMemory = 16

	}

	else

	{

		$iDefaultMinServerMemory = 8

	}



	# Create SqlConnection object and define connection string

	$oConnection = New-Object System.Data.SqlClient.SqlConnection

	$oConnection.ConnectionString = "Server=" + $scriptargs.ConnectionString + ";" + "Database=master;Integrated Security=true"

	$oConnection.open()



	# Create SqlCommand object, define command text, and set the connection

	$oSQLCommand = New-Object System.Data.SqlClient.SqlCommand

	$oSQLCommand.CommandText = "select configuration_id, name, value, value_in_use, is_dynamic from sys.configurations where value != value_in_use"



	$oSQLCommand.Connection = $oConnection



	# Create SqlDataReader

	$oSQLDataReader = $oSQLCommand.ExecuteReader()



	# Array of fileids, names, and counts

	$configresults = @()



	If ($oSQLDataReader.HasRows)

	{

		While ($oSQLDataReader.Read())

		{

			$config = "" | Select-Object Id, Name, Value, ValueInUse, IsDynamic

		

			$config.Id = $oSQLDataReader["configuration_id"]

			$config.Name = $oSQLDataReader["name"]

			$config.Value = $oSQLDataReader["value"]	

			$config.ValueInUse = $oSQLDataReader["value_in_use"]

			if ($oSQLDataReader["is_dynamic"] -eq 1)

			{

				$config.IsDynamic = $true

			}

			else

			{

				$config.IsDynamic = $false

			}

		

			# One exception is 'max server memory' (configid = 1544). A config value of 0 and run value of 2147483647 means they are the same

			if ($config.Id -eq 1544)

			{

				# So if max server memory and config valu_in_use is 2147483647 but config value is not 0 then fire the alert

				if ($config.ValueInUse -eq 2147483647 -and $config.Value -ne 0)

				{

					$scriptoutput.AlertRaised = $true

					$configresults += $config

				}

				# The only other condition is that max server memory value in use is not 2147483647 whic means we should fire the alert

				elseif ($config.ValueInUse -ne 2147483647)

				{

					$scriptoutput.AlertRaised = $true

					$configresults += $config

				}

			}

			# Another exception is 'min server memory' (configid = 1543).

			# In SQL Server, when the server engine starts, it checks to see if the value of 'min server memory' is &lt; "default min server memory".

			# If so, it fixes the "run value" to the "default min server memory". so we need exclude this scenario.

			elseif ($config.Id -eq 1543)

			{

				if ($config.ValueInUse -ne $config.Value -and ($config.ValueInUse -ne $iDefaultMinServerMemory -or $config.Value -gt $iDefaultMinServerMemory))

				{

					$scriptoutput.AlertRaised = $true

					$configresults += $config

				}

			}

			else

			{

				$scriptoutput.AlertRaised = $true

				$configresults += $config

			}

		}

	}



	# Close the data reader and the connection

	$oConnection.Close()

	$oSQLDataReader.Close()



	# Write out statistics

	#Write-Host "Is Alert Raised = " $scriptoutput.AlertRaised



	foreach ($config in $configresults)

	{

		$scriptoutput.DifferenceList += "(" + $config.Name + ", " + $config.Value + ", " + $config.ValueInUse + ", " + $config.IsDynamic + ") "

	}

	$scriptoutput.DifferenceCount = $configresults.Count



}







AdvisorRule $scriptargs $scriptoutput



# set the output

$mom = new-object -comobject "MOM.ScriptAPI"

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.AlertRaised -ne $null)

{

	$bag.AddValue("AlertRaised", $scriptoutput.AlertRaised)

}



if ($scriptoutput.DifferenceCount -ne $null)

{

	$bag.AddValue("DifferenceCount", $scriptoutput.DifferenceCount)

}



if ($scriptoutput.DifferenceList -ne $null)

{

	$bag.AddValue("DifferenceList", $scriptoutput.DifferenceList)

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86397</Schedule>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="Boolean">Property[@Name='AlertRaised']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="Boolean">true</Value>

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Boolean">Property[@Name='AlertRaised']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Boolean">true</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>