This monitor checks availability of the Distribution database from the Subscriber.
The Subscriber is not able to reach its distribution database.
To use this monitor, it is necessary to add administrator privileges in "Microsoft SQL Server Replication Distributor Availability from Subscriber Monitoring Run As Profile".
Db names in the db names list string should be separated by commas.
A db name should meet requirements of one of the next identifier classes:
1) Regular
The first character must be one of the following:
- A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a to z, from A to Z, and also letter characters from other languages.
- The underscore (_)
Subsequent characters can include the following:
- letters as defined in the Unicode Standard 3.2;
- decimal numbers from either Basic Latin or other scripts;
- the at sign (@), dollar sign ($), number sign (#), or underscore (_).
2) Delimited – can contain any characters, and should be delimited by double quotes or square brackets.
3) Undelimited – can be almost any character sequence, and should meet the next requirements:
should not belong to one of the classes below;
should not contain comma;
should not start or end with any of white-space characters.
Any db name belonging to any of the classes above should be from 1 to 128 characters, not including the delimiter characters.
For more information, see https://msdn.microsoft.com/en-us/library/ms175874 page.
This situation may be caused by:
A connectivity issue between the Distributor and the Subscriber.
Insufficient permissions for the Replication Distribution Agent or Merge Agent account to access the Distribution database.
Impossibility to configure CredSSP
Verify the following conditions:
Ensure the network access between the Distributor and the Subscriber is correctly configured.
Ensure any firewalls between the Distributor and Subscriber are configured to allow access on the ports used by Microsoft SQL Server.
Ensure that the Windows Remote Management (WS-Management) service running on the Subscriber.
Ensure firewall exceptions configured to allow connections for Windows Remote Management through HTTP-In.
Ensure the Replication Agent’s account is configured correctly to access both the Distributor and the Subscriber.
Ensure the Distribution Agent and Merge Agent accounts are added to the db_owner fixed role on the Distribution and the Subscriber databases.
Ensure the monitoring account is in Local Administrators group on Distributor and on Subscribers.
Ensure that the following registry key is present on Distributor and Subscribers: "LocalAccountTokenFilterPolicy"=dword:00000001 in [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\policies\system]
If there is a firewall in your replication topology, you may want to read the following article: http://support.microsoft.com/kb/164667/
To add LocalAccountTokenFilterPolicy key, execute the following:
reg add HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System /v LocalAccountTokenFilterPolicy /t REG_DWORD /d 1 /f
Name | Description | Default Value |
Alert Priority | Defines Alert Priority. | Normal |
Alert Severity | Defines Alert Severity. | Error |
CredSsp Enabled | Indicates that CredSsp is enabled prior to running this workflow. Will leave it enabled after the run | Yes |
Database Names | List of names for databases that should be checked, delimited by ',' symbol |
|
Enabled | Enables or disables the workflow. | No |
Generates Alerts | Defines whether the workflow generates an Alert. | Yes |
Interval (seconds) | The recurring interval of time in seconds in which to run the workflow. | 300 |
Port | Port of wsman service | 5985 |
Prefix | Name of the wsman service | wsman |
Subscriber Names | List of names of subscribers that should be used as a check source, delimited by '|' symbol |
|
Synchronization Time | Synchronization Time |
|
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 200 |
Timeout for database connection (seconds) | The workflow will fail and register an event, if it cannot access the database during the specified period. | 15 |
Transport | Prefix of the protocol to access the wsman service | http |
Target | Microsoft.SQLServer.2017.Replication.Windows.Distributor | ||
Parent Monitor | System.Health.AvailabilityState | ||
Category | AvailabilityHealth | ||
Enabled | False | ||
Alert Generate | True | ||
Alert Severity | Error | ||
Alert Priority | Normal | ||
Alert Auto Resolve | True | ||
Monitor Type | Microsoft.SQLServer.2017.Replication.Windows.MonitorType.DistributorAvailabilityFromSubscriberMonitorType | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Microsoft.SQLServer.MonitoringAccount |
<UnitMonitor ID="Microsoft.SQLServer.2017.Replication.Windows.Monitor.DistributionDBAvailabilityFromSubscriber" Accessibility="Public" Enabled="false" Target="SQL2017ReplWD!Microsoft.SQLServer.2017.Replication.Windows.Distributor" ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2017.Replication.Windows.MonitorType.DistributorAvailabilityFromSubscriberMonitorType" ConfirmDelivery="false" RunAs="GPMP!Microsoft.SQLServer.MonitoringAccount">
<Category>AvailabilityHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2017.Replication.Windows.Monitor.DistributionDBAvailabilityFromSubscriber.AlertMessage">
<AlertOnState>Error</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>Error</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Data/Context/Property[@Name='Subscriber']$</AlertParameter1>
<AlertParameter2>$Data/Context/Property[@Name='Message']$</AlertParameter2>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>
<OperationalState ID="Success" MonitorTypeStateID="Health" HealthState="Success"/>
</OperationalStates>
<Configuration>
<Distributor>$Target/Property[Type='MSRL!Microsoft.SQLServer.2017.Replication.Library.GenericDistributor']/ConnectionString$</Distributor>
<Subscriber/>
<Domain>$RunAs[Name="GPMP!Microsoft.SQLServer.MonitoringAccount"]/Domain$</Domain>
<User>$RunAs[Name="GPMP!Microsoft.SQLServer.MonitoringAccount"]/UserName$</User>
<Pass>$RunAs[Name="GPMP!Microsoft.SQLServer.MonitoringAccount"]/Password$</Pass>
<Transport>http</Transport>
<Prefix>wsman</Prefix>
<Port>5985</Port>
<DatabaseNames/>
<CredSspEnabled>true</CredSspEnabled>
<IntervalSeconds>300</IntervalSeconds>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
<SyncTime/>
<TimeoutSeconds>200</TimeoutSeconds>
</Configuration>
</UnitMonitor>