Home
  •  

AK606071

Monitor_AK606071 (UnitMonitor)

SQL Server missing update KB2682488 to prevent backup failures on databases with CDC feature enabled

Knowledge Base article:

External

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

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 missing update KB2682488 to prevent backup failures on databases with CDC feature enabled
<Details>
<Content>The SQL Server instance contains databases enabled for change data capture [CDC] feature. When you execute database backup commands for these databases, you might encounter duplicate key error messages [like 2601] and the backup command will terminate. Review the list of databases enabled for CDC and apply the fix from KB article 2682488. </Content>
<CollectedInformation>
<Info>
<Name>Databases with CDC enabled</Name>
<Value>{0}</Value>
</Info>
<Info>
<Name>Current SQL Server version</Name>
<Value>{1}</Value>
</Info>
<Info>
<Name>Recommended SQL Server version</Name>
<Value>{2}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

<UnitMonitor ID="Monitor_AK606071" 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="MonitorMessageb3bbc554e7164a8496fbccea57de1cb4">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

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

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

      <AlertParameter3>$Data/Context/Property[@Name='RecommendedSQLServerVersionOutput']$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <ScriptName>AK606071.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>ConnectionString</Name>

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

      </Parameter>

      <Parameter>

        <Name>CurrentSQLServerVersionInput</Name>

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

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($ConnectionString,$CurrentSQLServerVersionInput)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

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

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



# Set up the output

$global:scriptoutput = new-object psobject

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

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

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

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

#----------------------------------------------------- 

# MAIN CODE SECTION

#----------------------------------------------------- 



# Environment



$scriptenv = New-Object psobject

$scriptenv | Add-Member NoteProperty "RuntimeError" $false

$scriptenv | Add-Member NoteProperty "IsCDCEnabledOnAnyDatabaseEnv" $false

$scriptenv | Add-Member NoteProperty "ListOfDBWithCDCEnabledEnv" ""

$scriptenv | Add-Member NoteProperty "IsSQLRunningAffectedBuild" $false

$scriptenv | Add-Member NoteProperty "RecommendedSQLBuildEnv" ""





#function to check if SQL Server is using Lock Pages

function IsCDCEnabledOnAnyDatabase()

{

	#T-SQL query = "SELECT name FROM sys.databases WHERE is_cdc_enabled = 1"

	#return IsCDCEnabledOnAnyDatabaseEnv

	

	# 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 name FROM sys.databases WHERE is_cdc_enabled = 1"

	$oSQLCommand.Connection = $oConnection

	

	# Execute Command 

	$reader = $oSQLCommand.ExecuteReader()

	$rowID = 1

	[array]$listDBWithEnabledCDC = $null

	

	while($reader.Read())

	{

		$scriptenv.IsCDCEnabledOnAnyDatabaseEnv = $true

		$listDBWithEnabledCDC += $reader[0].ToString()

		

		$rowID++

	}

	

	if($listDBWithEnabledCDC -ne $null -and $listDBWithEnabledCDC.Count -ne 0)

	{

		$scriptenv.ListOfDBWithCDCEnabledEnv = [string]::Join(";",$listDBWithEnabledCDC)

	}

	

	# Close reaser object

	$reader.Close()

	

	# Close SqlConnection object

	$oConnection.Close()

}





#function to check if the current SQL Build is affected

Function SQLBuildCheck()

{

	#SQL 2008 R2 SP1

	if([version]$scriptargs.CurrentSQLServerVersionInput -ge [version]"10.50.2500.0" -and [version]$scriptargs.CurrentSQLServerVersionInput -lt [version]"10.50.2811.0")

	{

		$scriptenv.IsSQLRunningAffectedBuild = $true

		$scriptenv.RecommendedSQLBuildEnv = "10.50.2811.0"

	}

	#SQL 2008 R2 RTM

	elseif([version]$scriptargs.CurrentSQLServerVersionInput -ge [version]"10.50.1600.1" -and [version]$scriptargs.CurrentSQLServerVersionInput -lt [version]"10.50.1815.0")

	{

		$scriptenv.IsSQLRunningAffectedBuild = $true

		$scriptenv.RecommendedSQLBuildEnv = "10.50.1815.0"

	}

	#SQL 2008 SP3

	elseif([version]$scriptargs.CurrentSQLServerVersionInput -ge [version]"10.00.5500.00" -and [version]$scriptargs.CurrentSQLServerVersionInput -lt [version]"10.00.5775.00")

	{

		$scriptenv.IsSQLRunningAffectedBuild = $true

		$scriptenv.RecommendedSQLBuildEnv = "10.00.5775.00"

	}

	#SQL 2008 SP2

	elseif([version]$scriptargs.CurrentSQLServerVersionInput -ge [version]"10.00.4000.00" -and [version]$scriptargs.CurrentSQLServerVersionInput -lt [version]"10.00.4330.00")

	{

		$scriptenv.IsSQLRunningAffectedBuild = $true

		$scriptenv.RecommendedSQLBuildEnv = "10.00.4330.00"

	}

}







# Main function



function AdvisorRule($scriptargs, $scriptoutput)

{ 

	# All parameters should be populated outside of the main function. 

	# The main function should only include the detection logic so that it can be easily reused by the Atlanta authoring tool.



	trap [Exception] {

		$scriptenv.RuntimeError = $true

      	continue; 

   	}

	

	# Initialize parameters

	$scriptoutput.HasIssue = $false

	$scriptoutput.DBWithCDCEnabledOutput = ""

	$scriptoutput.CurrentSQLServerVersionOutput = ""

	$scriptoutput.RecommendedSQLServerVersionOutput = ""



	# Set parameter values

	IsCDCEnabledOnAnyDatabase

	

	if($scriptenv.IsCDCEnabledOnAnyDatabaseEnv -eq $true)

	{

		SQLBuildCheck

		if($scriptenv.IsSQLRunningAffectedBuild -eq $true)

		{

			if($scriptenv.RuntimeError -eq $false)

			{

				$scriptoutput.HasIssue = $true

				$scriptoutput.DBWithCDCEnabledOutput = $scriptenv.ListOfDBWithCDCEnabledEnv

				$scriptoutput.CurrentSQLServerVersionOutput = $scriptargs.CurrentSQLServerVersionInput

				$scriptoutput.RecommendedSQLServerVersionOutput = $scriptenv.RecommendedSQLBuildEnv

			}

		}

	}

}

AdvisorRule $scriptargs $scriptoutput



# set the output

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

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.HasIssue -ne $null)

{

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

}



if ($scriptoutput.DBWithCDCEnabledOutput -ne $null)

{

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

}



if ($scriptoutput.CurrentSQLServerVersionOutput -ne $null)

{

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

}



if ($scriptoutput.RecommendedSQLServerVersionOutput -ne $null)

{

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

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86397</Schedule>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

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

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

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

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

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

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

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

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>