MSSQL: Availability Database Backup Status

Microsoft.SQLServer.Linux.MonitorType.AvailabilityDatabaseHealth.BackupStatus (UnitMonitorType)

This monitor type checks the state of availability database backup on a Microsoft SQL Server Database Engine.

Element properties:

Show References:
- Unit Monitors (1)

RunAs	Microsoft.SQLServer.Core.RunAs.Monitoring
Accessibility	Internal
Support Monitor Recalculate	False

Member Modules:

ID	Module Type	TypeId	RunAs
DS	DataSource	Microsoft.SQLServer.Linux.DataSource.AvailabilityDatabaseHealth.BackupStatus	Default
DatabaseFilter	ConditionDetection	System.ExpressionFilter	Default
FilterBad	ConditionDetection	System.ExpressionFilter	Default
FilterGood	ConditionDetection	System.ExpressionFilter	Default

Overrideable Parameters:

ID	ParameterType	Selector	Display Name	Description
Threshold	int	$Config/Threshold$	Backup Period (days)	The target backup frequency in days. Should be set according to your Recovery Point Objective (RPO).
ConsiderAgBackupPreference	bool	$Config/ConsiderAgBackupPreference$	Track 'Availability Group Backup Preferences'	Instructs the monitor to track the backup location configured in the Availability Group backup preferences. If enabled, the monitor verifies whether the backup on the selected replica is in compliance with the backup frequency setting.
NumSamples	int	$Config/NumSamples$	Number of samples	The number of times a measured value should breach the threshold before the state is changed.
IntervalSeconds	int	$Config/IntervalSeconds$	Interval (seconds)	The recurring interval of time in seconds in which to run the workflow.
SyncTime	string	$Config/SyncTime$	Synchronization Time	The synchronization time specified by using a 24-hour format. May be omitted.
TimeoutSeconds	int	$Config/TimeoutSeconds$	Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.
SqlExecTimeoutSeconds	int	$Config/SqlExecTimeoutSeconds$	Timeout for query execution (seconds)	The workflow will fail and register an event, if the query execution takes longer than the specified period.
SqlTimeoutSeconds	int	$Config/SqlTimeoutSeconds$	Timeout for database connection (seconds)	The workflow will fail and register an event, if it cannot access the database during the specified period.

Source Code:

<UnitMonitorType ID="Microsoft.SQLServer.Linux.MonitorType.AvailabilityDatabaseHealth.BackupStatus" Accessibility="Internal" RunAs="SqlCoreLib!Microsoft.SQLServer.Core.RunAs.Monitoring">

  <MonitorTypeStates>

    <MonitorTypeState ID="Good"/>

    <MonitorTypeState ID="Bad"/>

  </MonitorTypeStates>

  <Configuration>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="MachineName" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="NetbiosComputerName" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="InstanceName" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="DatabaseName" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="ConnectionString" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="InstanceVersion" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="InstanceEdition" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="Threshold" type="xsd:int"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="ConsiderAgBackupPreference" type="xsd:boolean"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="NumSamples" type="xsd:integer"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="SqlExecTimeoutSeconds" type="xsd:integer"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="SqlTimeoutSeconds" type="xsd:integer"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="TimeoutSeconds" type="xsd:int"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="IntervalSeconds" type="xsd:int"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="SyncTime" type="xsd:string"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="Threshold" ParameterType="int" Selector="$Config/Threshold$"/>

    <OverrideableParameter ID="ConsiderAgBackupPreference" ParameterType="bool" Selector="$Config/ConsiderAgBackupPreference$"/>

    <OverrideableParameter ID="NumSamples" ParameterType="int" Selector="$Config/NumSamples$"/>

    <OverrideableParameter ID="IntervalSeconds" ParameterType="int" Selector="$Config/IntervalSeconds$"/>

    <OverrideableParameter ID="SyncTime" ParameterType="string" Selector="$Config/SyncTime$"/>

    <OverrideableParameter ID="TimeoutSeconds" ParameterType="int" Selector="$Config/TimeoutSeconds$"/>

    <OverrideableParameter ID="SqlExecTimeoutSeconds" ParameterType="int" Selector="$Config/SqlExecTimeoutSeconds$"/>

    <OverrideableParameter ID="SqlTimeoutSeconds" ParameterType="int" Selector="$Config/SqlTimeoutSeconds$"/>

  </OverrideableParameters>

  <MonitorImplementation>

    <MemberModules>

      <DataSource TypeID="Microsoft.SQLServer.Linux.DataSource.AvailabilityDatabaseHealth.BackupStatus" ID="DS">

        <MachineName>$Config/MachineName$</MachineName>

        <NetbiosComputerName>$Config/NetbiosComputerName$</NetbiosComputerName>

        <InstanceName>$Config/InstanceName$</InstanceName>

        <ConnectionString>$Config/ConnectionString$</ConnectionString>

        <InstanceVersion>$Config/InstanceVersion$</InstanceVersion>

        <InstanceEdition>$Config/InstanceEdition$</InstanceEdition>

        <SqlExecTimeoutSeconds>$Config/SqlExecTimeoutSeconds$</SqlExecTimeoutSeconds>

        <SqlTimeoutSeconds>$Config/SqlTimeoutSeconds$</SqlTimeoutSeconds>

        <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

        <IntervalSeconds>$Config/IntervalSeconds$</IntervalSeconds>

        <SyncTime>$Config/SyncTime$</SyncTime>

      </DataSource>

      <ConditionDetection ID="DatabaseFilter" TypeID="System!System.ExpressionFilter">

        <Expression>

          <And>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

                  <XPathQuery Type="String">Property[@Name='DatabaseName']</XPathQuery>

                </ValueExpression>

                <Operator>Equal</Operator>

                <ValueExpression>

                  <Value Type="String">$Config/DatabaseName$</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

            <Expression>

              <Or>

                <Expression>

                  <SimpleExpression>

                    <ValueExpression>

                      <Value Type="Boolean">$Config/ConsiderAgBackupPreference$</Value>

                    </ValueExpression>

                    <Operator>Equal</Operator>

                    <ValueExpression>

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

                    </ValueExpression>

                  </SimpleExpression>

                </Expression>

                <Expression>

                  <SimpleExpression>

                    <ValueExpression>

                      <XPathQuery Type="Integer">Property[@Name='PreferredForBackup']</XPathQuery>

                    </ValueExpression>

                    <Operator>GreaterEqual</Operator>

                    <ValueExpression>

                      <Value Type="Integer">0</Value>

                    </ValueExpression>

                  </SimpleExpression>

                </Expression>

              </Or>

            </Expression>

          </And>

        </Expression>

      </ConditionDetection>

      <ConditionDetection ID="FilterGood" TypeID="System!System.ExpressionFilter">

        <Expression>

          <And>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

                  <XPathQuery Type="UnsignedInteger">Property[@Name='DaysSinceBackup']</XPathQuery>

                </ValueExpression>

                <Operator>Less</Operator>

                <ValueExpression>

                  <Value Type="UnsignedInteger">$Config/Threshold$</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

            <Expression>

              <Or>

                <Expression>

                  <SimpleExpression>

                    <ValueExpression>

                      <Value Type="Boolean">$Config/ConsiderAgBackupPreference$</Value>

                    </ValueExpression>

                    <Operator>Equal</Operator>

                    <ValueExpression>

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

                    </ValueExpression>

                  </SimpleExpression>

                </Expression>

                <Expression>

                  <SimpleExpression>

                    <ValueExpression>

                      <XPathQuery Type="Integer">Property[@Name='PreferredForBackup']</XPathQuery>

                    </ValueExpression>

                    <Operator>Equal</Operator>

                    <ValueExpression>

                      <Value Type="Integer">1</Value>

                    </ValueExpression>

                  </SimpleExpression>

                </Expression>

              </Or>

            </Expression>

          </And>

        </Expression>

      </ConditionDetection>

      <ConditionDetection ID="FilterBad" TypeID="System!System.ExpressionFilter">

        <Expression>

          <Or>

            <Expression>

              <And>

                <Expression>

                  <SimpleExpression>

                    <ValueExpression>

                      <Value Type="Boolean">$Config/ConsiderAgBackupPreference$</Value>

                    </ValueExpression>

                    <Operator>Equal</Operator>

                    <ValueExpression>

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

                    </ValueExpression>

                  </SimpleExpression>

                </Expression>

                <Expression>

                  <SimpleExpression>

                    <ValueExpression>

                      <XPathQuery Type="Integer">Property[@Name='PreferredForBackup']</XPathQuery>

                    </ValueExpression>

                    <Operator>Equal</Operator>

                    <ValueExpression>

                      <Value Type="Integer">0</Value>

                    </ValueExpression>

                  </SimpleExpression>

                </Expression>

              </And>

            </Expression>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

                  <XPathQuery Type="UnsignedInteger">Property[@Name='DaysSinceBackup']</XPathQuery>

                </ValueExpression>

                <Operator>GreaterEqual</Operator>

                <ValueExpression>

                  <Value Type="UnsignedInteger">$Config/Threshold$</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

          </Or>

        </Expression>

        <SuppressionSettings>

          <MatchCount>$Config/NumSamples$</MatchCount>

          <SampleCount>$Config/NumSamples$</SampleCount>

        </SuppressionSettings>

      </ConditionDetection>

    </MemberModules>

    <RegularDetections>

      <RegularDetection MonitorTypeStateID="Good">

        <Node ID="FilterGood">

          <Node ID="DatabaseFilter">

            <Node ID="DS"/>

          </Node>

        </Node>

      </RegularDetection>

      <RegularDetection MonitorTypeStateID="Bad">

        <Node ID="FilterBad">

          <Node ID="DatabaseFilter">

            <Node ID="DS"/>

          </Node>

        </Node>

      </RegularDetection>

    </RegularDetections>

  </MonitorImplementation>

</UnitMonitorType>