SQL 設定資料庫無法使用

Microsoft.ActiveDirectoryFederationServices.2016.FederationServerRemoteSQLServerPingMonitor (UnitMonitor)

Knowledge Base article:

Element properties:

Source Code:

<UnitMonitor ID="Microsoft.ActiveDirectoryFederationServices.2016.FederationServerRemoteSQLServerPingMonitor" Accessibility="Public" Enabled="true" Target="Microsoft.ActiveDirectoryFederationServices.2016.FederationServer" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="Microsoft.ActiveDirectoryFederationServices.2016.TwoStateScriptMonitorType" ConfirmDelivery="false">

  <Category>AvailabilityHealth</Category>

  <AlertSettings AlertMessage="Microsoft.ActiveDirectoryFederationServices.2016.FederationServerRemoteSQLServerPingMonitor_AlertMessageResourceID">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property["Server"]$</AlertParameter1>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <PowerShellPath>%windir%\system32\windowspowershell\v1.0\powershell.exe</PowerShellPath>

    <ScriptName>FederationServerRemoteSQLServerPing.ps1</ScriptName>

    <ScriptBody><Script>

                        $scomapi = new-object -comObject "MOM.ScriptAPI"



                        $script:serverReachable = $true

                        $script:exceptionMessage = ""

                        $script:server = ""



                        $stsWMIObject = (Get-WmiObject -Namespace root\ADFS -Class SecurityTokenService)

                        $sqlConnectionString = $stsWMIObject.ConfigurationDatabaseConnectionString



                        [System.Data.SqlClient.SqlConnectionStringBuilder] $sqlConnectionBuilder = new-object System.Data.SqlClient.SqlConnectionStringBuilder $sqlConnectionString



                        $isWID = [StringComparer]::OrdinalIgnoreCase.Equals( $sqlConnectionBuilder.DataSource, 'np:\\.\pipe\Microsoft##WID\tsql\query');



                        if ($isWID -eq $false)

                        {

                        # if sql configuration store, check if remote sql server is pingable

                        $ping = new-object System.Net.NetworkInformation.Ping

                        &amp;{

                        $script:server = $sqlConnectionBuilder.DataSource

                        if ( $script:server.StartsWith(".") -or $script:server.StartsWith("(local)") )

                        {

                        $script:server = "localhost"

                        }

                        else

                        {

                        $script:server = $script:server.Split('\')[0].Split(',')[0]

                        }

                        $scomapi.LogScriptEvent("ActiveDirectoryFederationServices", 1000, 4, "SQL server ping:" + $script:server)

                        $reply = $ping.Send( $script:server )

                        if ($reply -ne $null)

                        {

                        if ($reply.Status -ne [System.Net.NetworkInformation.IPStatus]::Success)

                        {

                        $script:serverReachable = $false

                        $script:exceptionMessage = $reply.Status.ToString()

                        }

                        }

                        }

                        }

                        else

                        {

                        # if wid configuration store, check if the sql servcie is running

                        # TODO: Think of taking the connection string and do a dummy query, instead of just checking status

                        $widService = get-service MSSQL'$MICROSOFT##WID'

                        $scomapi.LogScriptEvent("ActiveDirectoryFederationServices", 1001, 4, "WID sql service status:" + $widService.Status)

                        if ($widService.Status -ne "Running")

                        {

                        $script:serverReachable = $false

                        }

                        }

                        Trap [System.Exception]

                        {

                        $script:serverReachable = $false

                        $script:exceptionMessage = $_.Exception.Message

                        continue

                        }



                        $scompb = $scomapi.CreatePropertyBag()

                        $scompb.AddValue("ServerReachable",  $script:serverReachable )

                        $scompb.AddValue("Server",  $script:server )

                        $scompb.AddValue("ErrorMessage",  $script:exceptionMessage )

                        $scomapi.AddItem($scompb)

                        $scomapi.ReturnItems()

                    </Script></ScriptBody>

    <IntervalSeconds>300</IntervalSeconds>

    <TimeoutSeconds>180</TimeoutSeconds>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

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

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

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

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <SimpleExpression>

        <ValueExpression>

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

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

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

        </ValueExpression>

      </SimpleExpression>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>