Guide To Microsoft System Center Management Pack For SQL Server 2014 Replication

Guide to Microsoft System Center Management
Pack for SQL Server 2014 Replication

Microsoft Corporation
Published: June, 2017
The Operations Manager team encourages you to provide any feedbacks on the management
pack by sending them to [email protected].
Copyright
This document is provided "as-is". Information and views expressed in this document, including
URL and other Internet website references, may change without notice. You bear the risk of
using it.
Some examples depicted herein are provided for illustration only and are fictitious. No real
association or connection is intended or should be inferred.
This document does not provide you with any legal rights to any intellectual property in any
Microsoft product. You may copy and use this document for your internal, reference purposes.
You may modify this document for your internal, reference purposes.
© 2017 Microsoft Corporation. All rights reserved.
Microsoft, Active Directory, Windows, and Windows Server are trademarks of the Microsoft
group of companies.
All other trademarks are property of their respective owners.

Contents
Guide History..................................................................................................................................5
Getting started................................................................................................................................7
Supported Configurations...........................................................................................................7
Management Pack Scope............................................................................................................8
Prerequisites...............................................................................................................................9
Files in this Management Pack....................................................................................................9
Mandatory Configuration..........................................................................................................10
Management Pack Purpose..........................................................................................................10
Monitoring Scenarios................................................................................................................11
Discovery of SQL Server Replication objects..........................................................................11
Distributor Discovery and monitoring....................................................................................11
Publisher Discovery and monitoring......................................................................................11
Subscriber Discovery and monitoring....................................................................................11
Publication Discovery and monitoring...................................................................................12
Subscription Discovery and monitoring.................................................................................12
Many Publication Snapshots on the same drive....................................................................12
SQL Server Agent state...........................................................................................................12
Maintenance Job failure........................................................................................................12
Job failure..............................................................................................................................13
Data Flow..................................................................................................................................13
Logical structure....................................................................................................................14
Publication flow.....................................................................................................................14
Replication Database Health..................................................................................................16

Configuring the Management Pack..............................................................................................19
Best Practice: Create a Management Pack for Customizations.................................................19
How to import a Management Pack..........................................................................................20
How to enable Agent Proxy option...........................................................................................21
How to configure Run As profile................................................................................................21
Security Configuration...............................................................................................................21
Run As Profiles.......................................................................................................................22
Required permissions............................................................................................................22
Low-Privilege Environments...................................................................................................23
TLS 1.2 Protection..................................................................................................................29
Viewing Information in the Operations Manager Console...........................................................30
Version-independent (generic) views and dashboards.............................................................30
SQL Server 2014 Replication views............................................................................................30
Dashboards...............................................................................................................................31
Replication Top Level Datacenter Dashboard........................................................................32
Replication Components Datacenter Dashboard...................................................................32
SQL Server 2014 Replication Datacenter Dashboard.............................................................33
Links..............................................................................................................................................33
Appendix: Terms and Definitions..................................................................................................34
Appendix: Management Pack Views and Dashboards..................................................................35
Appendix: Management Pack Objects and Workflows.................................................................37
Appendix: Run As Profiles.............................................................................................................73
Appendix: Known Issues and Troubleshooting.............................................................................79

Guide to Microsoft System Center Management
Pack for Microsoft Server 2014 Replication
This guide is based on version 6.7.31.0 of the Management Pack for Microsoft SQL Server 2014
Replication.
Guide History
Release Date Changes
June, 2017  Added Distributor name caching to Subscription discovery

(version 6.7.31.0  Restricted the length of some string class properties
RTM)  Improved the internal structure of SQL scripts storage
 Fixed variable types in SQL scripts
 Fixed connectivity issues in SmartConnect module
 Introduced minor updates to the display strings
 Updated the visualization library
December, 2016  Added support for configurations where computer host names are
(version 6.7.15.0 longer than 15 symbols
RTM)  Fixed: MonitorDistributorSnapshotFreeSpace fails when being
launched against SQL Server 2014 SP2
 Fixed: Wrong source detection in logging
 Updated the visualization library
June, 2016  Decreased database timeout value for some workflows to match
(version 6.7.2.0) new connection logic
June, 2016  Updated the visualization library

(version 6.7.1.0)
May, 2016  Fixed percentage threshold comparison of deactivated subscriptions

(version 6.7.0.0) workflow in Distributor
 Implemented support for TLS 1.2 in connection logic
April, 2016  Reviewed threading implementation

(version 6.6.7.30)  Fixed issue: Distributor Discovery could fail for Replication
 Fixed error logging, added more details
 Fixed "Availability of the Distribution database" monitor issue:
5
Release Date Changes
added missing overridable parameter

 Assigned SDK profile to the Replication Database Health Discovery
 Fixed replication monitoring issue: compatibility with SQL 2005 as a
part of Replication
 Fixed Publisher Discovery issue: unable to cast object of type
'System.DBNull' to type 'System.String'
 Added timeout support for .Net modules
 Fixed issue: SQL Server Event Log Collection Target was not
discovered Associate due to invalid path in DataSource
 Fixed Replication Agent State monitor type cookdown issue
 Fixed issue: failing replacement warning in "One or more of the
Replication Agents are retrying on the Distributor" monitor
 Dashboards fixes: added bindings to the tiles; added Datacenter
Classes (L1 dashboard for Replication)
 Fixed upgradeability
 Fixed Knowledge Base articles and display strings
 Updated Known Issues and Troubleshooting section of the guide
January, 2016  Added support for SQL Express edition

(version 6.6.7.5)  Fixed "Invalid length parameter passed to the LEFT or SUBSTRING
function" error after deleting last replication objects
 Fixed “SQL Server blocked access to Agent XP's procedure”
 Supported case-sensitive collation on DBEngine instance
 Fixed issue: an INSERT EXEC statement cannot be nested
 Updated Low-Privilege Environments configuration
November, 2015  Updated the visualization library

(version 6.6.4.0)
November, 2015  Updated the visualization library

(version 6.6.3.0)
October, 2015  Preset dashboards are updated

(version 6.6.2.0)
June, 2015  Original release of this management pack

(version 6.6.0.0)
6
Getting started
In this section:
 Supported Configurations
 Management Pack Scope
 Prerequisites
 Mandatory Configuration
Supported Configurations
This management pack is designed for the following versions of System Center Operations
Manager:
 System Center Operations Manager 2012 (Except Dashboards)

 System Center Operations Manager 2012 SP1
 System Center Operations Manager 2012 R2
 System Center Operations Manager 2016
A dedicated Operations Manager management group is not required for this management pack.
The following table details the supported configurations for the Management Pack for Microsoft
SQL Server 2014 Replication:
Configuration Support
SQL Server 2014 64-bit SQL Server 2014 Database Engine on 64-bit
OS
Clustered servers No
Agentless monitoring Not supported
Virtual environment Yes
Note that neither SQL Server Express edition (SQL Server Express, SQL Server Express with Tools,
SQL Server Express with Advanced Services) support SQL Server Agent, Log Shipping, Always On,
7
OLAP Services and Data Mining, SQL Server In-Memory OLTP, Analysis Services and Integration
Services.
Also, SQL Server Express and SQL Server Express with Tools don't support Reporting Services and
Full text search. However, SQL Server Express with Advanced Services support Full text search
and Reporting Services with limitations.
All SQL Server Express editions support Database mirroring as Witness, and Replication as
Subscriber only.
For more information, see features supported by SQL Server 2014 editions:
http://go.microsoft.com/fwlink/?LinkId=717843
Management Pack Scope

Management Pack for Microsoft SQL Server 2014 Replication enables the monitoring of
following features:
 SQL Server 2014 Replication
Important
Agentless monitoring is not supported by Management Pack for Microsoft SQL Server 2014
Replication.
Note
Please refer to “Monitoring Scenarios” section for a full list of monitoring scenarios
supported by this management pack.
Note
For more information and detailed instructions on setup and configuration see “Configuring
the Management Pack for Microsoft SQL Server 2014 Replication” section of this guide.
Note
This management pack doesn’t discover database engines and database objects. It is
recommended to import the Microsoft System Center Management Pack for SQL Server
2014 management pack to enable discovery, monitoring and health rollup for SQL Server
2014 Replication databases. This management pack depends on management pack for SQL
Server 2014, i.e. installation of management pack for SQL Server 2014 is required.
8
Prerequisites
As a best practice, you should import the Windows Server Management Pack for the operating
system you are using. The Windows Server Management Packs monitor aspects of the operating
system that influence the performance of computers running SQL Server, such as disk capacity,
disk performance, memory utilization, network adapter utilization, and processor performance.
Files in this Management Pack

The Management Pack for Microsoft SQL Server 2014 Replication includes the following files:
File Description
Microsoft.SQLServer.2014.Replication.Di This Management Pack discovers Microsoft SQL

scovery.mpb Server 2014 Replication objects. The management
pack only contains the discovery logic and requires
the separate monitoring management pack to be
imported to monitor the discovered objects.
Microsoft.SQLServer.2014.Replication.M The Microsoft SQL Server 2014 Replication

onitoring.mpb (Monitoring) Management Pack enables the
monitoring of Microsoft SQL Server 2014 Replication
related objects and depends on the Microsoft SQL
Server 2014 Replication (Discovery) Management
Pack.
Microsoft.SQLServer.2014.Replication.Vi This management pack defines views for Microsoft

ews.mpb SQL Server 2014 Replication.
Microsoft.SQLServer.2014. This Management Pack adds SQL Server 2014

Replication.Presentation.mp Replication Dashboard.
Microsoft.SQLServer.Replication.Library. Microsoft SQL Server Replication Library contains

mpb base Replication components required for SQL Server
2014 Replication.
Microsoft.SQLServer.Replication.Library. This management pack defines the version-

Views.mpb independent views for Microsoft SQL Server
Replication.
Microsoft.SQLServer.Replication.Library. This Management Pack adds SQL Server Replication

Presentation.mp Summary Dashboard.
Microsoft.SQLServer.Generic.Presentatio This Management Pack defines common folder

n.mp structure and views.
9
File Description
Microsoft.SQLServer.Generic.Dashboard This Management Pack contains generic SQL Server

s.mp dashboards.
Microsoft.SQLServer.Visualization.Library This Management Pack contains base visual

.mpb components required for SQL Server dashboards.
Mandatory Configuration
To configure Management Pack for Microsoft SQL Server 2014 Replication complete following
steps:
 Review the “Configuring the Management Pack for Microsoft SQL Server 2014
Replication” section of this guide.
 Grant required permissions as described in “Security Configuration” section of this

guide.
 Enable the Agent Proxy option on all agents that are installed on servers, which host an
Instance of SQL Server 2014 Replication. For more information about enabling Agent
Proxy option see “How to enable Agent Proxy option” section of this guide.
 Import the Management Pack.
 Associate SQL Server 2014 Replication Run As profiles with accounts that have
appropriate permissions. For more information about configuring Run As profiles see
“How to configure Run As profile” section of this guide.
Management Pack Purpose

In this section:
 Monitoring Scenarios
 Data Flow
Note
For details on the discoveries, rules, monitors, views, and reports contained in this
management pack, see following sections of this guide:
 Appendix: Management Pack Objects and Workflows
 Appendix: Management Pack Views and Dashboards
10
Monitoring Scenarios
Discovery of SQL Server Replication objects

The Management Pack for Microsoft SQL Server 2014 Replication automatically discovers stand-
alone and clustered replication instances across all managed systems that run the System Center
Operations Manager agent service. This management pack enables the monitoring of Microsoft
SQL Server 2014 Replication objects. The following objects are automatically discovered:
 Distributor
 Publisher
 Subscriber
 Publication
 Subscription
Each managed replication object is discovered and monitored using a number of rules and
monitors. Please refer to the “Appendix: Management Pack Objects and Workflows” section for
the full list of rules and monitors targeted to replication objects.
Distributor Discovery and monitoring

For each managed database engine, the distributors on it are discovered and monitored using a
number of rules and monitors. Please refer to the “Appendix: Management Pack Objects and
Workflows” section for the full list of rules and monitors targeted to distributor.
For more information, see Distributor and Publisher Information Script in the MSDN Library
Publisher Discovery and monitoring

For each managed database engine, the publishers on it are discovered and monitored using a
Workflows” section for the full list of rules and monitors targeted to publisher.
For more information, see Distributor and Publisher Information Script in the MSDN Library
Subscriber Discovery and monitoring

For each managed database engine, the subscribers on it are discovered and monitored using a
Workflows” section for the full list of rules and monitors targeted to subscriber.
11
Publication Discovery and monitoring
For each managed publisher instance, the publications on it are discovered and monitored using
a number of rules and monitors. Please refer to the “Appendix: Management Pack Objects and
Workflows” section for the full list of rules and monitors targeted to publication.
Subscription Discovery and monitoring

For each managed subscriber instance, the subscriptions on it are discovered and monitored
using a number of rules and monitors. Please refer to the “Appendix: Management Pack Objects
and Workflows” section for the full list of rules and monitors targeted to subscription.
Many Publication Snapshots on the same drive

Space monitoring introduced by this management pack may be noisy in environments where
many publications’ snapshots share the same media. In such cases, an alert for a publication’s
snapshot is generated when the amount of free space on the hard drive reaches the threshold.
To reduce the noise, turn off the space monitors for “Snapshot Available Space (%)” and use
Operating System Management Pack to monitor space on the hard drive.
SQL Server Agent state

The management pack defines a monitors targeted to Distributors and Subscribers. These
monitors oversee the SQL Server agents and change the state when the SQL Server Agent is not
running or running but the “Startup Type” is “Manual”. An alert is registered if an SQL Server
Agent is not running.
You may also consider the “Job failure” scenario for per-job failure monitoring.
Maintenance Job failure

Replication uses the maintenance jobs that are monitored by the “MSSQL 2014 Replication: The
Maintenance Job(s) Failed on Distributor Alert Rule”:
 Reinitialize subscriptions having data validation failures
 Agent history clean up: distribution
 Replication monitoring refresher for distribution.
 Replication agents checkup
 Distribution clean up: distribution
 Expired subscription clean up
For more information, see Run Replication Maintenance Jobs (SQL Server Management Studio) in the
MSDN Library
12
Job failure
The management pack defines a monitor targeted at the Distributor and Subscriber. These
monitors oversee the replication agents’ jobs and change the monitor state when the job has
the following states:
 Job Exist But Never Run and Has Not Schedule
 Job Expired
 Job Failed
 Job is Disabled
 Job is Enabled but Schedule is Disabled
 Job Execution Failed and was Not in Accordance with the Schedule
 Job is Retry
 Job Never Run
 Job Never Run But Schedule Exist
 Job Successfully Done But Not in Accordance with the Schedule
 Job Execution was Stopped and was Not in Accordance with the Schedule
 Previous Job Execution Failed
 Previous Job Execution was Stopped
 Unknown State of the Job
Please refer to “Appendix: Management Pack Objects and Workflows” section for the full list of
rules and monitors targeted to Replication Agents.
Data Flow
The following diagrams show the data flows in this management pack for:
 Logical structure
 Publication flow
 Replication Database Health
See Terms and Definitions Appendix for more details on the diagrams contents.
13
Logical structure
Publication flow
14
15
Replication Database Health
Replication Database Health is built for the Databases participating in replication process as
published database.
Top-level structure
Virtual Distributor level structure
16
Replication agent files are located under <drive>:\Program Files\Microsoft SQL Server\100\
COM. The following table lists the replication executable names and file names. Click the link for
an agent to view its parameter reference.
Agent Executable File Name
Replication Snapshot Agent snapshot.exe
Replication Distribution Agent distrib.exe
Replication Log Reader Agent logread.exe
Replication Queue Reader Agent qrdrsvc.exe
Replication Merge Agent replmerg.exe
In addition to replication agents, a replication has a number of jobs that perform scheduled and
on-demand maintenance.
Replication Maintenance Jobs
Replication uses the following jobs to perform scheduled and on-demand maintenance.
Clean up job Description Default schedule
Agent History Clean Up: Removes replication agent history Runs every ten
Distribution from the distribution database. minutes
Distribution Clean Up: Removes replicated transactions from Runs every ten
Distribution the distribution database. Deactivates minutes
subscriptions that have not been
synchronized within the maximum
distribution retention period.
Expired Subscription Clean Up Detects and removes expired Runs every day at
subscriptions from publication 1:00 A.M.
databases.
17
Reinitialize Subscriptions Detects all subscriptions that have data No default
Having Data Validation Failures validation failures and marks them for schedule (not
re-initialization. The next time the enabled by
Merge Agent or Distribution Agent default).
runs, a new snapshot will be applied at
the Subscribers.
Replication Agents Checkup Detects replication agents that are not Runs every ten
actively logging history. It writes to the minutes.
Microsoft Windows event log if a job
step fails.
Replication monitoring Refreshes cached queries used by Runs continuously.

refresher for distribution Replication Monitor.
Virtual Publisher level structure
18
Virtual Subscriber level structure
Configuring the Management Pack

This section provides guidance on configuring and tuning this management pack.
In this section:
 Best Practice: Create a Management Pack for Customizations

 How to import a Management Pack
 How to enable Agent Proxy option
 How to configure Run As profile
 Security Configuration
o Run As Profiles
o Required permissions
Best Practice: Create a Management Pack for Customizations

The Management Pack for Microsoft SQL Server 2014 Replication is sealed so that you cannot
change any of the original settings in the management pack file. However, you can create
19
customizations, such as overrides or new monitoring objects, and save them to a different
management pack. By default, the Operations Manager saves all customizations to the default
management pack. As a best practice, you should instead create a separate management pack
for each sealed management pack you want to customize.
Creating a new management pack for storing overrides has the following advantages:
 When you create a management pack for the purpose of storing customized settings for a
sealed management pack, it is helpful to base the name of the new management pack on
the name of the management pack that it is customizing, such as “Microsoft SQL Server
2014 Replication Overrides”.
 Creating a new management pack for storing customizations of each sealed management
pack makes it easier to export the customizations from a test environment to a production
environment. It also makes it easier to delete a management pack, because you must delete
any dependencies before you can delete a management pack. If customizations for all
management packs are saved in the Default Management Pack and you need to delete a
single management pack, you must first delete the Default Management Pack, which also
deletes customizations to other management packs.
For more information about sealed and unsealed management packs, see Management Pack
Formats. For more information about management pack customizations and the default
management pack, see About Management Packs.
How to Create a New Management Pack for Customizations

1. Open the Operations console, and then click the Administration button.
2. Right-click Management Packs, and then click Create New Management Pack.
3. Enter a name (for example, MSSQL2014 Replication MP Customizations), and then click
Next.
4. Click Create.
How to import a Management Pack

For more information about importing a management pack, see How to Import an Operations
Manager Management Pack.
20
How to enable Agent Proxy option
To enable Agent Proxy option complete following steps:
1. Open the Operations Console and click the Administration button.

2. In the Administrator pane, click Agent Managed.
3. Double-click an agent in the list.
4. On the Security tab, select Allow this agent to act as a proxy and discover managed
objects on other computers.
How to configure Run As profile

To configure Run As profile complete following steps:
1. Identify the names of the target computers where the default action account has
insufficient rights to monitor SQL Server 2014 Replication.
2. For each system, create or use an existing set of credentials that have at least the set of
privileges discussed in the “Security Configuration” section of this management pack
guide.
3. For each set of credentials identified in step 2, make sure a corresponding Run As
Account exists in the management group. Create the Run As Account if necessary.
4. Setup the mappings between the targets and the Run As Accounts on the Run As
Accounts tab of each of the Run As Profiles.
Note
Please refer to “Run As Profiles” section for the detailed explanation of what Run As profiles
are defined in Management Pack for Microsoft SQL Server 2014 Replication.
Note
Please refer to “Appendix: Run As Profiles” section for the full list of discoveries, rules and
monitors to identify rules and monitors associated with each Run As Profile.
Security Configuration
This section provides guidance on configuring the security for this management pack.
In this section:
21
 Run As Profiles
 Required Permissions
 Low-Privilege Environments
 TLS 1.2 Protection
Run As Profiles
When the Management Pack for Microsoft SQL Server 2014 Replication is imported for the first
time, it creates four new Run As profiles:
 Microsoft SQL Server Replication Discovery Run As Profile

 Microsoft SQL Server Replication Distributor Availability from Subscriber Monitoring Run
As Profile
 Microsoft SQL Server Replication Monitoring Run As Profile
 Microsoft SQL Server Replication SCOM SDK Discovery Run As Profile
By default, all discoveries, monitors and rules defined in the SQL Server 2014 Replication
management pack use accounts defined in the “Default Action Account” Run As profile. If the
default action account for a given system does not have the necessary permissions to discover
or monitor the objects of SQL Server 2014 Replication, then those systems can be bound to
more specific credentials in the “Microsoft SQL Server Replication …” Run As profiles.
Note
For more information about configuring Run As profiles see “How to configure Run As
profile” section of this guide.
Note
Required permissions
This section describes how to configure required permissions for the Management Pack for
Microsoft SQL Server 2014 Replication. All workflows (discoveries, rules and monitors) in this
management pack are bound to Run As profiles described in “Run As Profiles” section. To enable
the monitoring, appropriate permissions should be granted to Run As accounts and these
22
accounts should bound to respective Run As Profiles. Subsections below describe how to grant
permissions at Operating System and SQL Server level.
Note
Please refer to “Run As Profiles” section for the detailed explanation of what Run As profiles
are defined in Management Pack for Microsoft SQL Server 2014 Replication.
Note
For more information about configuring Run As profiles see “How to configure Run As
profile” section of this guide.
Note
Low-Privilege Environments
To configure permissions in Active Directory

1. In Active Directory, create four domain users that will be commonly used for low-privilege
access to all target SQL Server instances:
a. SSReplDiscovery
b. SSReplAvDB
c. SSReplMonitoring
d. SSReplSDK
2. Create a domain group named SSReplMPLowPriv and add the following domain users:
a. SSReplDiscovery
b. SSReplMonitoring
c. SSReplAvDB
d. SSReplSDK
3. Grant special permission: Read-only Domain Controllers – “Read Permission” to the
SSReplMPLowPriv
23
To configure permissions on the agent machine
1. On the agent machine, add the SSReplMonitoring domain user to the “Performance
Monitor Users” local group.
2. Add the SSReplMonitoring domain user to “EventLogReaders” local group.
3. Go to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\EventLog, Open
Permissions settings, add Low-Privilege group SSReplMPLowPriv to the security list, and
grant a special permission (besides the default one):
1. - Set Value
2. - Create Subkey
3. - Read Control
4. Add the SSReplMPLowPriv domain group as a member to the local Users group.
5. Go to Local Computer Policy – Windows Settings – Security Settings – Local Policies – User
Rights Assignment and configure the “Allow log on locally” policy to add the
SSReplMPLowPriv domain group in order to log on locally.
6. Grant Read permission on “HKLM:\Software\Microsoft\Microsoft SQL Server” registry path
for SSReplMPLowPriv.
7. Grant “Execute Methods”, “Enable Account”, “Remote Enable”, “Read Security” permissions
to SSReplMPLowPriv for the following WMI namespaces:
 root
 root\cimv2
 root\default
 root\Microsoft\SqlServer\ComputerManagement11(12)
8. Grant permissions to SSReplSDK for all SQL Server services.

Read the existing privileges for a given service (using SC sdshow) and then grant additional
privileges to the SSReplSDK user for that server.
For example, suppose the results of the SC sdshow command for SQL Server service are as
follows:
D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)
(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:
(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)
In this case, the following command line grants sufficient access to SSReplSDK for remote
reading information about the SQL Server service (please replace colored strings with
appropriate values and keep everything on a single text line):
sc sdset SQLServerServiceName D:(A;;GRRPWP;;;SID for SSReplSDK)

(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)
(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:
(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)
24
Also, if you didn't change the settings before, it is necessary to change Service Control
Manager default security settings to get non-administrators remote access to the Service
Control Manager:
sc sdset SCMANAGER D:(A;;CCLCRPRC;;;AU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)S:

(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)
For more information, see Sc sdset page.
Note
You can obtain SID of a user through WMIC USERACCOUNT command.
For example,
wmic useraccount where (name='SSReplSDK' and domain='%userdomain%') get name,sid
Note
The monitoring account user must have the following permissions to 'C:\Windows\Temp' folder:
 Modify
 Read & Execute
 List Folder contents
 Read
 Write
To configure the low-privilege environment on the agent machine in cluster

1. For each node in the cluster, execute the steps outlined in “To configure the low-privilege
environment on the agent machine” section.
2. Grant “Remote Launch” and “Remote Activation” DCOM permissions to the
SSReplMPLowPriv using DCOMCNFG. Please note that both defaults and limits should be
adjusted. Under DCOM config, select Windows Management and Instrumentation
properties; on Security tab, grant “Remote Launch” and “Remote Activation” to the
SSReplMPLowPriv group.
3. Allow Windows Remote Management through the Windows Firewall.
4. Grant “Read” and “Full Control” access for the cluster to the SSReplMPLowPriv using
Failover Cluster Manager.
To configure permissions on the SQL Server 2014 Replication Distribution Database

1. In SQL Server Management Studio, for the instance of SQL Server Database Engine (which
appears to be the Distributor), create a login for “SSReplMPLowPriv”.
25
2. Create an SSReplMPLowPriv user in all Distribution Databases.
3. Assign db_datareader and replmonitor roles for SSReplMPLowPriv on all distribution
databases (see How to: Allow Non-Administrators to Use Replication Monitor (Replication Transact-
SQL Programming) for more details).
4. On all instances, it is necessary to assign SQLAgentReaderRole and db_datareader roles to

msdb for the SSReplMPLowPriv.
5. In Studio, grant the SSReplMPLowPriv user db_owner rights to all subscription and
publication databases (see Security Role Requirements for Replication for more details). Also, if
the current SSReplMPLowPriv user has not been created – create it.
6. Create a new executor role if it has not been created.
USE msdb;
go
CREATE ROLE db_executor;
GRANT EXECUTE TO db_executor;
go
Then, grant execute permissions for SSReplMPLowPriv user by means of this role.
USE msdb;
go
EXEC sp_addrolemember 'db_executor', ' yourdomain\SSReplMPLowPriv';
go
7. For each Publication, select Properties – Publication Access List and add SSReplMPLowPriv
user to the list.
8. Also, other permissions need to be granted in all Replication instances.
use master
go
grant select on master.dbo.sysperfinfo to [yourdomain\SSReplMPLowPriv]
go
grant execute on master.dbo.xp_sqlagent_notify to [yourdomain\SSReplMPLowPriv]
go
grant execute on master.dbo.xp_sqlagent_enum_jobs to [yourdomain\SSReplMPLowPriv]
go
26
grant execute on master.dbo.xp_sqlagent_param to [yourdomain\SSReplMPLowPriv]
go
grant execute on master.dbo.xp_sqlagent_is_starting to [yourdomain\SSReplMPLowPriv]
go
grant execute on master.dbo.xp_instance_regenumvalues to [yourdomain\
SSReplMPLowPriv]
go
use msdb
go
grant execute on msdb.dbo.sp_help_alert to [yourdomain\SSReplMPLowPriv]
go
grant execute on msdb.dbo.sp_help_notification to [yourdomain\SSReplMPLowPriv]
go
grant select on msdb.dbo.sysalerts to [yourdomain\SSReplMPLowPriv]
go
grant select on msdb.dbo.sysoperators to [yourdomain\SSReplMPLowPriv]
go
grant select on msdb.dbo.sysnotifications to [yourdomain\SSReplMPLowPriv]
go
grant select on msdb.dbo.sysjobschedules to [yourdomain\SSReplMPLowPriv]
go
grant select on msdb.dbo.sysschedules to [yourdomain\SSReplMPLowPriv]
go
grant select on msdb.dbo.sysjobhistory to [yourdomain\SSReplMPLowPriv]
go
grant select on msdb.dbo.sysjobservers to [yourdomain\SSReplMPLowPriv]
go
grant execute on msdb.dbo.agent_datetime to [yourdomain\SSReplMPLowPriv]
go
27
grant select on msdb.dbo.sysjobs to [yourdomain\SSReplMPLowPriv]
go
Note
The Distributor may have more than one Distribution database (one for specific Publisher).
To configure permissions on the System Center Operations Manager Management Server

1. Grant Local Administrator permissions to SSREPLSDK account.
To configure permissions on the System Center Operations Manager

1. Open SCOM Console and navigate to “Administration” pane.
2. Select “User Roles” view (located under “Security” folder).
3. Right click on “Operations Manager Operators” role and click “Properties” in the context
menu.
4. In the “General Properties” tab click “Add” button.
5. Find SSREPLSDK user and click “OK”.
6. Click “OK” button to apply the changes, and close “User Role Properties” dialog.
To configure System Center Operations Manager

1. Import the SQL Server Management Pack if it has not been imported.
2. Create SSReplDiscovery, SSReplMonitoring, SSReplAvDB and SSREPLSDK Run As accounts
with “Windows” account type. For more information about how to create a Run As account,
see How to Create a Run As Account in Operations Manager 2007 or How to Create Run As Account
in Operations Manager 2012. For more information about various Run As Account types, see
Run As Accounts and Run As Profiles in Operations Manager 2007 or Managing Run As Accounts and
Profiles in Operations Manager 2012.
3. Perform the following procedure to the created SSReplDiscovery, SSReplMonitoring and

SSReplAvDB Run As accounts: open Properties - Distribution tab in the created Run As
account, and add computer names that are to be discovered. Perform the following
procedure to SSREPLSDK Run As account: open Properties - Distribution tab in the created
Run As account, and add the management server with the management group (SCOM).
4. On the System Center Operations Manager console, configure the Run As profiles as follows:
a. Set “Microsoft SQL Server Replication Discovery Run As Profile” to use the
SSReplDiscovery Run As account.
28
b. Set “Microsoft SQL Server Replication Distributor Availability from Subscriber
Monitoring Run As Profile” to use the SSReplAvDb Run As account.
c. Set “Microsoft SQL Server Replication Monitoring Run As Profile” to use the
SSReplMonitoring Run As account.
d. Set “Microsoft SQL Server Replication SCOM SDK Discovery Run As Profile” to use
the SSREPLSDK Run As account.
TLS 1.2 Protection

Operating protection of connections in SQL Server is provided by means of TLS protocol. In order
to have the ability to use TLS 1.2 protocol, your environment should meet the following
prerequisites:
1. SQL Server should be updated to version that supports TLS 1.2.

2. Make sure that your environment meets the prerequisites provided in the table
below:
PowerShel
OS Version SCOM Version .NET Version l version
(2.0<=version<4.0) with TLS 1.2
update*
Windows 2012 version>=
and >=3.0
and above MINIMAL_SUPPORTED**
update*

Windows 2012 version>= update*
>=3.0
and above MINIMAL_SUPPORTED** and
version>=4.6

Windows
version>=2012 SP1 UR10 update*
2008R2 and >=2.0
version>=2012 R2 UR7 and
below
version>=4.6

Windows update*
version>=2012 SP1 UR10
2008R2 and and >=2.0
version>=2012 R2 UR7
below (4.0<=version<4.6) with TLS 1.2
update*
Windows version<2012 SP1 UR10 (2.0<=version<4.0) with TLS1.2 2.0
29
2008R2 and
2012 R2<=version<2012 R2 UR7 update*
below
* .NET Framework TLS 1.2 updates can be downloaded from TLS 1.2 support for Microsoft SQL
Server page (Client component downloads section).
** Minimal supported SCOM versions are stated in Supported Configurations section.
Viewing Information in the Operations Manager Console
Version-independent (generic) views and dashboards

This management pack uses common folder structure introduced with the first release of
Management Pack for SQL Server 2014. Following views and dashboards are version-
independent and show information about all versions of SQL Server:
SQL Server Replication
Active Alerts
All Replication Related Objects
SQL Server Replication Database Health
“All Replication Related Objects” diagram view provides an information about all SQL Server
Replication objects and their relations.
“SQL Server Replication Database Health” state view provides information about all databases
participating in replication as published database. From this view, it is easy to open the diagram
view specific to the published database.
SQL Server 2014 Replication views

The Management Pack for Microsoft SQL Server 2014 Replication introduces the comprehensive
set of state, performance and alert view, which can be found in the dedicated folder:
Monitoring
Microsoft SQL Server

30
SQL Server Replication
SQL Server 2014 Replication
SQL Server Replication Views
Note
Please refer to “Appendix: Management Pack Views and Dashboards” section of this
guide for the full list of views.
Note
Some views may contain very long list of objects or metrics. To find a specific object or
group of objects, you can use the Scope, Search, and Find buttons on the Operations
Manager toolbar. For more information, see “Finding Data and Objects in the Operations
Manager Consoles” article in the Operations Manager Help.
Dashboards
This management pack includes a set of rich dashboards, which provide detailed information
about SQL Server 2014 Replication. Each dashboard has a navigation widget (located in the
leftmost part of the dashboard), which can be used to switch the current presentation context,
i.e. the information displayed by other widgets depends on the object selected in the navigation
widget.
Note
The Replication dashboards have three personalization settings:
 “Refresh Rate” – this setting defines how often dashboard refreshes data on a client. This
setting does not change the actual collection frequency of metrics on a server.
 “Time Interval” – this setting defines for which period to display data. The Performance
widget on the “Instance” view depends on this setting.
 “Background Color” – change background color for all views.
Note
Use menu button of Datacenter Dashboard or a group In order to add a group or tile.
Note
To delete or edit a tile just right click on the tile and select required action.
31
Note
Background color, time interval and refresh rate settings are applied to the Datacenter
Dashboard and all Instance Dashboards and can be set from the Datacenter Dashboard
menu.
For more information, see Guide to Microsoft System Center Management Pack for
Microsoft SQL Server Dashboards
Replication Top Level Datacenter Dashboard

The Top Level Datacenter Dashboard is the main dashboard for SQL Server Replication. This
dashboard contains only one group by default: SQL Server Replication Database Health.
This class is a dynamic application used to group distributors, publishers and subscribers, which
are linked with a one exact published Database.
The collapsed group tile consists of two parts, the left part displays the number of objects in the
most critical state. The right part of the widget shows the number of alerts with the highest
severity.
Replication Components Datacenter Dashboard

The Replication Components Datacenter Dashboard is dashboard for SQL Server version-
independent Replication. This dashboard has more detail than the Top Level Dashboard and
contains four groups by default: Replication Database Health Group, Distributors Group,
Publishers Group and Subscribers Group.
32
SQL Server 2014 Replication Datacenter Dashboard
The SQL Server 2014 Replication Datacenter Dashboard is a specific dashboard for SQL
Server 2014 Replication. This dashboard contains only SQL Server 2014 Replication group by
default that contains all SQL Server 2014 Distributors, Publishers and Subscribers.
Links
The following links connect you to information about common tasks that are associated with
System Center Management Packs:
1. Management Pack Life Cycle

2. How to Import an Operations Manager Management Pack
3. Creating a Management Pack for Overrides
4. Managing Run As Accounts and Profiles
5. How to Export an Operations Manager Management Pack
6. How to Remove an Operations Manager Management Pack
For free training on System Center Operations Manager, see the System Center 2012 R2 Operations
Manager Management Pack course at the Microsoft Virtual Academy (MVA).
For questions about the Operations Manager and management packs, see the System Center
Operations Manager community forum (http://go.microsoft.com/fwlink/?LinkID=179635).
Important
All information and content on non-Microsoft sites is provided by the owner or the
users of the website. Microsoft makes no warranties, express, implied, or statutory, as
to the information at this website.
33
Appendix: Terms and Definitions
Term Definition
Distributor Distributor is a database instance that acts as a store for replication specific
data associated with one or more Publishers. Each Publisher is associated with
a single database (known as a distribution database) at the Distributor. In many
cases, a single database server instance acts as both Publisher and Distributor.
This is known as a local Distributor. When Publisher and Distributor are
configured on separate database server instances, the Distributor is known as a
remote Distributor.
Distribution database Distribution database stores replication status data, metadata about the
publication, and, in some cases, acts as a queue for data moving from Publisher
to Subscribers. In many cases, a single database server instance acts as both
Publisher and Distributor. This is known as a local Distributor. When Publisher
and Distributor are configured on separate database server instances, the
Distributor is known as a remote Distributor.
Publisher Publisher is a database instance that makes data available to other locations
through replication. A Publisher can have one or more publications, each
defining a logically related set of objects and data to replicate.
Publication Publication is a collection of one or more articles from one database. Such
grouping of multiple articles into a publication makes it easier to specify a
logically related set of data and database objects that are replicated as a unit. A
publication can contain different types of articles, including tables, views,
stored procedures, and other objects. When tables are published as articles,
filters can be used to restrict the columns and rows of the data sent to
Subscribers.
Article Article identifies a database object that is included in a publication.
Subscriber Subscriber is a database instance that receives replicated data. A Subscriber
can receive data from multiple Publishers and publications. Depending on the
selected replication type, a Subscriber can also pass data changes back to the
Publisher, or republish the data to other Subscribers.
Subscription Subscription is a request for a copy of a publication to be delivered to a
Subscriber. A subscription defines what publication will be received, where and
when. There are two types of subscriptions: push and pull.
Push subscription Push subscription is represented by a subscription created and administered at
the Publisher. The distribution agent or merge agent for this subscription runs
at the Distributor. For more information about subscriptions, see Subscribe to
Publications.
Pull Subscription Pull subscription is represented by a subscription configured and maintained at
each recipient. The subscribers administer the synchronization schedules and
can pull changes whether they consider it necessary. For more information
about subscriptions, see Subscribe to Publications.
34
Term Definition
Virtual Distributor Virtual Distributor is a virtual entity, which serves to represent a real distributor
on the diagram view for a Replication Database Health.
Virtual Publisher Virtual Publisher is a virtual entity serves to represent a real publisher on the
diagram view for a Replication Database Health.
Virtual Subscriber Host Virtual Subscriber Host is a virtual entity that contains Virtual Subscribers.
Virtual Subscriber Virtual Subscriber is a virtual entity serves to represent a real Subscriber on the
diagram view for a Replication Database Health.
Virtual Publication Host Virtual Publication Host is a virtual entity that contains Publications.
Publication database Publication database is the database on the Publisher that is the source of data
and database objects to be replicated.
Virtual Subscription Virtual Subscription is a virtual entity serves to represent a real subscription on
the diagram view for a Replication Database Health. The purpose of this entity
is to hide all subscriptions when the diagram is opened for the first time.
Appendix: Management Pack Views and Dashboards

Root
Microsoft SQL Server
SQL Server Replication [version independent folder]
Active Alerts – [target: Replication Alerts Scope Group, scope: SQL

Server Replication Objects]
All Replication Related Objects – [target: Replication Flow Group,

scope: All replication related objects]
SQL Server Replication Database Health – [target: Distribution

Databases, scope: SQL Server Published Databases]
Replication 2014 [version dependent folder]
Active Alerts – [target: SQL Server 2014 Replication Alerts Scope

Group, scope: SQL Server 2014 Replication Objects]
Distributors – [target: Distributor SQL 2014, scope: SQL Server 2014

Replication Distributors]
35
Publications – [target: Publication SQL 2014, scope: SQL Server 2014
Replication Publications]
Publishers – [target: Publisher SQL 2014, scope: SQL Server 2014

Replication Publishers]
Subscribers – [target: Subscriber SQL 2014, scope: SQL Server 2014

Replication Subscriber]
Subscriptions – [target: Subscription SQL 2014, scope: SQL Server

2014 Replication Subscription]
Summary
Performance
Distributor Performance Collections – [target: Distributor

SQL 2014, scope: Performance counter objects]
Publisher Performance Collections – [target: Publisher SQL

2014, scope: Performance counter objects]
Subscriber Performance Collections – [target: Subscriber SQL

2014, scope: Performance counter objects]
SQL Server Replication Views [version independent folder]
Distributors – [target: Generic Distributor, scope: SQL Server

Replication Distributors]
Publications – [target: Generic Publication, scope: SQL Server

Replication Publications]
Publishers – [target: Generic Publisher, scope: SQL Server Replication

Publishers]
Subscribers – [target: Generic Subscriber, scope: SQL Server

Replication Subscribers]
Subscriptions – [target: Generic Subscription, scope: SQL Server

Replication Subscriptions]
36
Appendix: Management Pack Objects and Workflows
The Management Pack for Microsoft SQL Server 2014 Replication discovers the object types
described in the following sections.
Microsoft SQL Server Replication Seed

An installation of Microsoft SQL Server Replication Seed
Microsoft SQL Server Replication Seed - Discoveries
MSSQL 2014 Replication: Discover SQL Server 2014 Replication (seed)
This discovery rule discovers a seed for a Microsoft SQL Server 2014 Replication Database
Health. This object indicates that the particular server computer contains an installation of
Microsoft SQL Server 2014 with Replication Distributor configured.
Name Description Default value
Enabled Enables or disables the Yes
workflow.
Interval (seconds) The recurring interval of time 14400
in seconds in which to run the
workflow.
Synchronization Time The synchronization time
specified by using a 24-hour
format. May be omitted.
MSSQL 2014 Replication: Alerts Scope Group

SQL Server 2014 Replication Alerts Scope Group
MSSQL 2014 Replication: Alerts Scope Group - Discoveries
MSSQL 2014 Replication: Alerts Scope Group Discovery
Discovery of Alerts Scope Group
MSSQL 2014 Replication: Distributor

SQL Server 2014 Distributor is a SQL Server instance that acts as a store for replication specific
data associated with one or more Publishers
MSSQL 2014 Replication: Distributor - Discoveries
MSSQL 2014 Replication: Distributor Discovery
The object discovery discovers all Distributors of an instance of Microsoft SQL Server 2014.
workflow.
37
workflow.
Timeout (seconds) Specifies the time the 300
workflow is allowed to run
before being closed and
marked as failed.
Timeout for database The workflow will fail and 15
connection (seconds) register an event, if it cannot
access the database during the
specified period.
MSSQL 2014 Replication: Distributor - Unit monitors

One or more of the Replication Agents are retrying on the Distributor
This monitor checks if any of the following Replication Agents are retrying an operation:
Distribution Agent, Log Reader Agent, Merge Agent, Queue Reader Agent or Snapshot Agent.
workflow.
Generate Alerts Defines whether the workflow True
generates an Alert.
Failed jobs count threshold Failed jobs count threshold 1
workflow.
Per-Job threshold Per-Job threshold 3
marked as failed.
38
specified period.
Distribution Agents state (aggregated for all Publications)

This monitor checks the state of the Distribution agents for all publications serviced by this
Distributor.
workflow.
generates an Alert.
Estimated Job Duration Threshold which is being used 15
to check the job schedule
compliance
workflow.
Show Jobs with Unknown State Include jobs with unknown false
state to the monitor output
and alert context. Will affect
health.
marked as failed.
specified period.
Availability of the Distribution database

This monitor checks the availability of the Distribution database from the Distributor.
workflow.
39
generates an Alert.
workflow.
marked as failed.
specified period.
Availability of the Distribution database from a Subscriber

This monitor checks availability of the Distribution database from the Subscriber.
Enabled Enables or disables the No
workflow.
generates an Alert.
CredSsp Enabled Indicates that CredSsp is true
enabled prior to running this
workflow. Will leave it enabled
after the run
Database Names List of names for databases
that should be checked,
delimited by ',' symbol
workflow.
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
40
source, delimited by '|' symbol
marked as failed.
specified period.
Transport Prefix of the protocol to access http
the wsman service
Replication Merge Agent State for the Distributor (aggregated for all Subscriptions)
This monitor checks the state of the Merge agents for all Subscriptions on the Distributor.
workflow.
generates an Alert.
compliance
workflow.
health.
marked as failed.
41
specified period.
Replication Snapshot Agent State for Distributor (aggregated for all Publications)
This monitor checks the state of the Snapshot Agent services for all Publications on the
Distributor.
workflow.
generates an Alert.
compliance
workflow.
health.
marked as failed.
specified period.
Percent of Deactivated Subscriptions

Percent of Deactivated Subscriptions Monitor
42
workflow.
generates an Alert.
Error Threshold Error Threshold 10
workflow.
marked as failed.
specified period.
Warning Threshold The monitor will change the 0
state to Warning if the value
drops below this threshold.
Unsynchronized Subscriptions on Distributor

This monitor detects unsynchronized subscriptions for specific Distributors.
workflow.
generates an Alert.
workflow.
Number of samples Indicates how many times a 12
measured value should breach
a threshold before the state is
changed.
Per-Subscription threshold Per-Subscription threshold 0
43
marked as failed.
specified period.
Unhealthy subscriptions count Unhealthy subscriptions count 0
threshold threshold
SQL Server Agent State for Distributor

This monitor checks if the SQL Server Agent is running on Distributor.
Replication Log Reader Agent State for the Distributor (aggregated for all Publications)
This monitor checks the state of the Replication Log Reader for all Publications serviced by the
Distributor.
workflow.
generates an Alert.
compliance
workflow.
health.
44
marked as failed.
specified period.
Percent of Expired Subscriptions

Percent of Expired Subscriptions Monitor
workflow.
generates an Alert.
workflow.
marked as failed.
specified period.
Publication Snapshot Available Space

The monitor reports a warning when the available disk space for the publication Snapshot drops
below the Warning Threshold setting. This is indicated as a percentage of the snapshot files
folder size. The monitor reports a critical alert when the available space drops below the Critical
Threshold.
45
workflow.
generates an Alert.
workflow.
marked as failed.
specified period.
Warning Threshold Warning Threshold 20
Replication Queue Reader Agent State for Distributor (aggregated for all Publications)
This monitor checks the status of the Queue Reader Agent services for all Publications on the
Distributor. Note: This monitor is disabled by default. Please use overrides to enable it when
necessary.
Enabled Enables or disables the No
workflow.
generates an Alert.
compliance
workflow.
46
health.
marked as failed.
specified period.
Replication Agents failed on the Distributor

This monitor checks if the following Replication agent jobs are in healthy state: Distribution
agent, Merge agent, Queue Reader agent, Log reader agent or Snapshot agent. If any of the
agents are in failed state, the monitor will be triggered.
workflow.
generates an Alert.
workflow.
marked as failed.
47
specified period.
Total daily execution time of the replication agent

This monitors the total daily execution time of the replication agents, Distribution, Log Reader,
Merge, Queue Reader and Snapshot on the Distributor.
workflow.
generates an Alert.
workflow.
Measurement Period (hours) Period of time used for 24
measurement (hours)
marked as failed.
specified period.
Pending Commands on Distributor

There are pending commands on the Distributor waiting for delivery.
workflow.
generates an Alert.
48
workflow.
changed.
Threshold Threshold 5
marked as failed.
specified period.
MSSQL 2014 Replication: Distributor - Dependency (rollup) monitors

Microsoft SQL Server 2014 Replication Database Performance Rollup
This monitor rolls up the performance state from Database to Microsoft SQL Server 2014
Replication
MSSQL 2014 Replication: Distributor - Rules (alerting)

MSSQL 2014 Replication: The Maintenance Job(s) Failed on Distributor Alert Rule
The Maintenance Job(s) Failed on Distributor Alert Rule
workflow.
Generate Alerts Defines whether the workflow Yes
generates an Alert.
workflow.
Priority Defines Alert Priority. 2
Severity Defines Alert Severity. 2
49
marked as failed.
specified period.
MSSQL 2014 Replication: Distributor - Rules (non-alerting)

MSSQL 2014 Replication: Count of Publications for the Distributor
Count of Publications for the Distributor.
workflow.
Generate Alerts Defines whether the workflow No
generates an Alert.
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Count of Subscriptions for the Distributor

Count of Subscriptions for the Distributor.
50
workflow.
generates an Alert.
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Distribution Agent: Delivery Latency

The current amount of time (in milliseconds) elapsed from when transactions are delivered to
the Distributor to when they are applied at the Subscriber.
workflow.
generates an Alert.
Frequency (seconds) The recurring interval of time 900
workflow.
MSSQL 2014 Replication: Count of Pending Commands in the Distribution Database

Count of commands in the Distribution database pending replication.
workflow.
generates an Alert.
51
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Distribution Agent: Delivered Transactions per Second

The number of transactions per second delivered to the Subscriber.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Log Reader Agent: Delivery Latency

The current amount of time (in milliseconds) elapsed from when transactions are applied at the
Publisher to when they are delivered to the Distributor.
workflow.
generates an Alert.
workflow.
52
MSSQL 2014 Replication: Expired Subscriptions (%)
The Percentage of Expired Subscriptions for the Distributor
workflow.
generates an Alert.
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Replication Snapshot Available Space (%)

The amount of space left on media hosting a Replication Snapshot.
workflow.
generates an Alert.
workflow.
marked as failed.
53
specified period.
MSSQL 2014 Replication: Count of the Queue Reader Instances for the Distributor
Count of the Queue Reader Instances for the Distributor.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Count of the Snapshot Agents Instances for the Distributor
Count of the Snapshot Agent Instances for the Distributor.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Count of the Distribution Agent Instances for the Distributor
Count of the Distribution Agent Instances for the Distributor
workflow.
generates an Alert.
workflow.
54
MSSQL 2014 Replication: Snapshot Agent: Delivered Commands per Second
The number of commands per second delivered to the Distributor.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Snapshot Agent: Delivered Transactions per Second

The number of transactions per second delivered to the Distributor.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Count of Failed Replication Agents Jobs on the Distributor
Count of Failed Replication Agents Jobs on the Distributor
workflow.
generates an Alert.
workflow.
55
marked as failed.
specified period.
MSSQL 2014 Replication: Log Reader Agent: Delivered Transactions per Second
The number of transactions per second delivered to the Distributor.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Merge Agent: Download Changes per Second

The number of rows per second replicated from the Publisher to the Subscriber (aggregated for
Distributor).
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Log Reader Agent: Delivered Commands per Second
The number of commands per second delivered to the Distributor.
workflow.
56
generates an Alert.
workflow.
MSSQL 2014 Replication: Merge Agent: Conflicts per Second

The number of conflicts per second occurring during the merge process.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Distribution Agent: Delivered Commands per Second

The number of commands per second delivered to the Subscriber.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Count of Unsynchronized Subscriptions for the Distributor

Count of unsynchronized subscriptions for the Distributor
workflow.
generates an Alert.
workflow.
57
marked as failed.
specified period.
MSSQL 2014 Replication: Count of the Merge Agent Instances for the Distributor
Count of the Merge Agent Instances for the Distributor.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Count of the Log Reader Agent Instances for the Distributor
Count of the Log Reader Agent Instances for the Distributor.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Deactivated Subscriptions (%)

The percentage of Deactivated Subscriptions for the Distributor
58
workflow.
generates an Alert.
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Merge Agent: Uploaded Changes per Second

The number of rows per second replicated from the Subscriber to the Publisher.
workflow.
generates an Alert.
workflow.
MSSQL 2014 Replication: Event Log Collection Target

This object is used to collect module errors from event logs of computers that have Replication
components.
MSSQL 2014 Replication: Event Log Collection Target - Discoveries
MSSQL 2014 Replication: Event Log Collection Target Management Server Discovery
59
This discovery rule discovers an event log collection target for a Microsoft SQL Server 2014
Replication. This object is used to collect module errors from event logs of management server
computers.
workflow.
workflow.
MSSQL 2014 Replication: Event Log Collection Target Discovery

This discovery rule discovers an event log collection target for a Microsoft SQL Server 2014
Replication. This object is used to collect module errors from event logs of computers that have
Replication components.
workflow.
workflow.
marked as failed.
MSSQL 2014 Replication: Event Log Collection Target - Rules (alerting)

MSSQL 2014 Replication: Replication Management Pack Module Error Event Alert Rule
Microsoft SQL Server 2014 Replication Management Pack Module Error Event Alert Rule
workflow.
Generate Alerts Defines whether the workflow Yes
60
generates an Alert.
Priority Defines Alert Priority. 2
Severity Defines Alert Severity. 2
MSSQL 2014 Replication: Group

A group containing all SQL Server 2014 Replication components
MSSQL 2014 Replication: Group - Discoveries
MSSQL 2014 Replication: Populate SQL Server 2014 Replication Group
This discovery rule populates the SQL Server 2014 Replication group with all SQL Server 2014
Replication components.
MSSQL 2014 Replication: Publication

A publication is a collection of one or more articles from one database. The grouping of multiple
articles into a publication makes it easier to specify a logically related set of database data and
objects that are replicated as a unit.
MSSQL 2014 Replication: Publication - Discoveries
MSSQL 2014 Replication: Publication Discovery
The object discovery discovers all publications of a Publisher of Microsoft SQL Server 2014
workflow.
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Publication - Unit monitors

Replication Log Reader Agent State for Publication
61
This monitor checks the status of the Replication Log Reader Agent service for Publications.
workflow.
generates an Alert.
compliance
workflow.
health.
marked as failed.
specified period.
All Subscriptions are Synchronizing for Publication

All Subscriptions are Synchronizing
workflow.
generates an Alert.
workflow.
62
marked as failed.
specified period.
Replication Snapshot Agent State

This monitor checks the status of the Replication Snapshot Agent service for Publications.
workflow.
generates an Alert.
compliance
workflow.
health.
marked as failed.
63
specified period.
MSSQL 2014 Replication: Publisher

SQL Server 2014 Publisher is a SQL 2014 instance that makes data available to other locations
through replication.
MSSQL 2014 Replication: Publisher - Discoveries
MSSQL 2014 Replication: Publisher Discovery
The object discovery discovers Publishers of an instance of Microsoft SQL Server 2014.
workflow.
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Publisher - Unit monitors

Subscriptions state for Publisher
This monitor checks whether there are any inactive subscriptions for any publications.
workflow.
generates an Alert.
workflow.
64
marked as failed.
specified period.
SQL Server Agent State for Publisher

This monitor checks if the SQL Server Agent is running on Publisher.
MSSQL 2014 Replication: Publisher - Rules (non-alerting)

MSSQL 2014 Replication: Count of Publications for the Publisher
Count of Publications for the Publisher.
workflow.
generates an Alert.
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Subscriber

65
SQL Server 2014 Subscriber is a SQL 2014 instance that receives replicated data.
MSSQL 2014 Replication: Subscriber - Discoveries
MSSQL 2014 Replication: Subscriber Discovery
The object discovery discovers Subscribers of an instance of Microsoft SQL Server 2014.
workflow.
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Subscriber - Unit monitors

Load of Replication agents on Subscriber
Replication agents (Distribution and Merge) load on Subscriber
workflow.
generates an Alert.
workflow.
Measurement Period (hours) Period of time used for 24
measurement (hours)
66
marked as failed.
specified period.
SQL Server Agent State for Subscriber

This monitor checks if the SQL Server Agent is running on Subscriber. Note that SQL Server
Agent Windows Service is not supported by any edition of SQL Server Express. Therefore, this
monitor is not applicable for SQL Server Express cases.
Subscriber Agent is Retrying

The Subscriber Agent (Distribution, Log Reader, Merge, Queue Reader and Snapshot) is Retrying
Monitor. Note that SQL Server Agent Windows Service is not supported by any edition of SQL
Server Express. Therefore, this monitor is not applicable for SQL Server Express cases.
workflow.
generates an Alert.
workflow.
marked as failed.
67
specified period.
MSSQL 2014 Replication: Subscriber - Rules (non-alerting)

MSSQL 2014 Replication: Count of the Failed Replication Jobs for the Subscriber
Count of the Failed Replication Jobs for the Subscriber
workflow.
generates an Alert.
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Count of Subscriptions for the Subscriber

Count of Subscriptions for the Subscriber.
workflow.
generates an Alert.
workflow.
68
marked as failed.
specified period.
MSSQL 2014 Replication: Subscription

SQL Server 2014 Subscription is a request for a copy of a publication to be delivered to a
Subscriber.
MSSQL 2014 Replication: Subscription - Discoveries
MSSQL 2014 Replication: Subscription Discovery
The object discovery discovers all subscriptions of a Subscriber of Microsoft SQL Server 2014
workflow.
workflow.
marked as failed.
specified period.
MSSQL 2014 Replication: Subscription - Unit monitors

Subscription latency
69
This monitor checks the latency for commands in the Distribution database waiting to be
delivered to the Subscribers.
workflow.
generates an Alert.
workflow.
marked as failed.
specified period.
Distribution Agent State for Subscription

This monitor checks the status of the Replication Distribution Agent services for Subscription.
Note that SQL Server Agent Windows Service is not supported by any edition of SQL Server
Express. Therefore, this monitor is not applicable for SQL Server Express cases.
workflow.
generates an Alert.
compliance
workflow.
70
health.
marked as failed.
specified period.
Replication Merge Agent State for Subscription

This monitor checks the status of the Replication Merge Agent service for Subscription. Note
that SQL Server Agent Windows Service is not supported by any edition of SQL Server Express.
Therefore, this monitor is not applicable for SQL Server Express cases.
workflow.
generates an Alert.
compliance
workflow.
health.
71
marked as failed.
specified period.
Subscription Pending Commands

There are pending commands on the Distributor for specific Subscription waiting delivery.
workflow.
generates an Alert.
workflow.
changed.
marked as failed.
specified period.
MSSQL 2014 Replication: Subscription - Rules (non-alerting)

MSSQL 2014 Replication: Count of Pending Commands
Count of commands pending replication in distribution database for the Subscription.
72
workflow.
generates an Alert.
workflow.
marked as failed.
specified period.
Appendix: Run As Profiles
Run As Profile Workflow Workflow

Type
Microsoft SQL Server Discovery Microsoft SQL Server 2014 Replication:

Replication Discovery Run Distributor Discovery
As Profile
Discovery Microsoft SQL Server 2014 Replication: Event
Log Collection Target Discovery
Discovery Microsoft SQL Server 2014 Replication: Event

Log Collection Target Management Server
Discovery
Discovery Microsoft SQL Server 2014 Replication:

Publication Discovery
73
Type

Publisher Discovery
Discovery Microsoft SQL Server 2014 Replication: Discover

SQL Server 2014 Replication (seed)

Subscriber Discovery

Subscription Discovery
Microsoft SQL Server Monitor Availability of the Distribution database from a

Replication Distributor Subscriber.
Availability from
Subscriber Monitoring
Run As Profile
Microsoft SQL Server Discovery Microsoft SQL Server Replication Database

Replication SCOM SDK Health Discovery
Discovery Run As Profile
Monitor All Publishers Discovered for Distributor
Microsoft SQL Server Monitor Distribution Agents state (aggregated for all
Replication Monitoring Publications)
Run As Profile
Monitor Availability of the Distribution database.
Monitor Pending Commands on Distributor
Monitor Replication Log Reader Agent State for the

Distributor (aggregated for all Publications)
Monitor Replication Merge Agent State for the

Distributor (aggregated for all Subscriptions)
Monitor Percent of Deactivated Subscriptions
Monitor Percent of Expired Subscriptions
Monitor Publication Snapshot Available Space
Monitor Replication Queue Reader Agent State for

Distributor (aggregated for all Publications)
74
Type
Monitor Replication Agents failed on the Distributor.
Monitor One or more of the Replication Agents are

retrying on the Distributor.
Monitor Total daily execution time of the replication

agent.
Monitor Replication Snapshot Agent State for Distributor

(aggregated for all Publications)
Monitor SQL Server Agent State for Distributor
Monitor Unsynchronized Subscriptions on Distributor
Monitor Replication Log Reader Agent State for

Publication
Monitor Replication Snapshot Agent State
Monitor All Subscriptions are Synchronizing for

Publication
Monitor SQL Server Agent State for Publisher
Monitor Subscriptions state for Publisher
Monitor Subscriber Agent is Retrying
Monitor Load of Replication agents on Subscriber.
Monitor SQL Server Agent State for Subscriber
Monitor Subscription latency
Monitor Subscription Pending Commands
Monitor Distribution Agent State for Subscription
Monitor Replication Merge Agent State for Subscription
Rule MSSQL 2014 Replication: Count of Failed

Replication Agents Jobs on the Distributor
Rule MSSQL 2014 Replication: Count of Pending

Commands in the Distribution Database
75
Type
Rule MSSQL 2014 Replication: Deactivated

Subscriptions (%)
Rule MSSQL 2014 Replication: Expired Subscriptions

(%)
Rule MSSQL 2014 Replication: Replication Snapshot

Available Space (%)
Rule MSSQL 2014 Replication: Count of

Unsynchronized Subscriptions for the
Distributor
Rule MSSQL 2014 Replication: Merge Agent:

Conflicts per Second
Rule MSSQL 2014 Replication: Distribution Agent:

Delivered Commands per Second

Delivery Latency

Delivered Transactions per Second
Rule MSSQL 2014 Replication: Count of the

Distribution Agent Instances for the Distributor

Download Changes per Second
Rule MSSQL 2014 Replication: Count of the Log

Reader Agent Instances for the Distributor
Rule MSSQL 2014 Replication: Log Reader Agent:


Delivery Latency

Rule MSSQL 2014 Replication: Count of the Merge

76
Type
Agent Instances for the Distributor
Rule MSSQL 2014 Replication: Count of the Queue

Reader Instances for the Distributor
Rule MSSQL 2014 Replication: Count of Publications

for the Distributor
Rule MSSQL 2014 Replication: Count of Subscriptions

for the Distributor
Rule MSSQL 2014 Replication: Count of the Snapshot

Agents Instances for the Distributor
Rule MSSQL 2014 Replication: Snapshot Agent:

Rule MSSQL 2014 Replication: Snapshot Agent:


Uploaded Changes per Second
Rule MSSQL 2014 Replication: Count of Publications

for the Publisher
Rule MSSQL 2014 Replication: Count of the Failed

Replication Jobs for the Subscriber
Rule MSSQL 2014 Replication: Count of Subscriptions

for the Subscriber
Rule MSSQL 2014 Replication: Count of Pending

Commands
Rule MSSQL 2014 Replication: The Maintenance

Job(s) Failed on Distributor Alert Rule
Rule MSSQL 2014 Replication: Microsoft SQL Server

2014 Replication Management Pack Module
Error Event Alert Rule
77
78
Appendix: Known Issues and Troubleshooting
Error events similar to “Loading managed module type in
"Microsoft.SQLServer.2012.Replication. Module.Discovery…” assembly may occur in the
Operations Manager Windows event log.
Issue: The distributor, publisher and subscriber workflows may throw this error after the
Replication MP is installed.
Resolution: No action is required since the errors occur only once when the management packs
are imported.
Warning events in Windows event log on the agent-servers.

Issue: When there is a configured distributor, but it is not used by any publishers, the distributor
performance counters are registered but have no objects. In the performance view,
performance rules will be initialized by since the objects do not exist they return nothing.
Resolution: No resolution.
Alerts from the "All Publishers Discovered for Distributor" monitor are not placed into the
Active Alerts folders of the SQL Server MPs.
Issue: Since the monitored objects are managed by the management group and are hosted by
virtual objects, it is impossible to map them to the view.
Resolution: The objects may be found in the root folder: Monitoring\Active Alerts
Incorrect databases are listed in the "SQL Server Replication Database Health" view.
Issue: If a user enters incorrect databases in the replication jobs, the databases will be
discovered and put in the view. Such objects will have no properties and throw errors each time
the user tries to work with them.
Alert description does not change untill the whole alert is resolved.
Issue: The alert context of the aggregated monitors lists all objects that have issues. The context
will not be updated until the whole alert is resolved.
Resolution: This is a known SCOM issue. There is no known workaround at the moment.
Replication jobs without history cause crttical alerts.

Issue: The Replication MP treats all replication jobs that have empty history as having
unsuccessfully runs and notifies about them. Even if such jobs have a schedule and are run, an
alert will be raised.
Resolution: No resolution. The alert will be automatically resolved at the moment the job run is
completed.
79
The "Distribution Database Availability from Subscriber" monitor throws the following error
"Cannot configure CredSSP" for Subscriber on Windows Server 2008 R2 Enterprise.
Issue: By default, PowerShell 2.0 is installed on Windows 2008 and PowerShell 4.0 on Windows
2012. If Distributors are run on Windows 2012 and Subscribers are run on Windows 2008, the
PowerShell versions are different, and there are two possible errors: "Cannot configure
CredSSP" and “Failed to configure CredSSP on Distributor”.
Resolution: Install the same PowerShell version on Subscribers as the one installed on the
Distributors. Enable CredSSP for servers running Windows 2008.
The Subscription for Merge Replication is shown as Inactive.

Issue: Subscriptions that are synchronizing by schedule are shown as Inactive and alerts are
raised about Inactive Subscriptions. Incorrect state for subscriptions are caused by incorrect
data in the distribution database. The monitor gets data from the distribution database without
taking into account the “active” property (MSmerge_subscriptions table). The SQL Server
Management Studio takes this state from the publication database (by executing
sp_helpmergesubscription).
"Pending Commands on Distributor" monitor does not change the state.

Issue: "Pending Commands on Distributor" monitor does not change the state to “Warning”
after generation of error logs in Job on Publisher. The monitor works correctly for Transaction
and Snapshot publications. Merge publication is skipped, i.e. for merge replication the monitor
is always healthy.
"Discovery data couldn't be inserted to the database" error may occur upon creation of a new
subscription.
Issue: Upon installation of the MP, DB engine may fail to discover all the data during the first
launch, and the above error may appear in the log.
Resolution: The Discovery Distributor may need some extra time to discover the data. The
second option is to clear the cash of the agent manually.
When SQL Express instance is used, Subscriber targeted rules and minitors may throw an
error.
Issue: When SQL Express instance is used, rules and monitors that target on Subscriber can
throw the following error: "The filename, directory name, or volume label syntax is incorrect" in
the event log.
An error may occur due to discovery timeout conflict.

Issue: If discovery of Distributor or Subscriber is completed earlier than discovery of the
database of this object (e.g. discovery of database on Distributor instance) according to the set
80
timeout, the Operations Manager may throw the following error with relationship target:
"Discovery data couldn't be inserted to the database, RelationshipInstance TypeId".
Resolution: To avoid the issue, make sure that the timeout in overrides for database discovery in
DB-engine is less or equal to the timeout in overrides for Distributor or Subscriber instances
discovery.
Monitoring workflows throw "Access denied" exception when a default Run As profile is used.
Issue: A number of monitoring workflows throw "Access denied" exception when a default Run
As profile is used, and monitoring agent uses Local System account.
Resolution: A domain account (or account with access to all Replication machines) should be
used for default Run As profile.
Property reference errors may appear in the agent log upon the MP upgrade.
Issue: When the Management Pack is upgraded from version 6.6.4.0. to version 6.7.2.0, some
property reference errors may appear in the agent log. The affected monitors are as follows:
 SQL Server Agent State for Publisher

 SQL Server Agent State for Distributor
 SQL Server Agent State for Subscriber
Resolution: Enable Distributor, Publisher and Subscriber discoveries and wait until the discovery
process is completed.
Publications and Subscriptions on unaccessible databases may get undiscovered in SCOM.

Issue: If a database participating in Replication goes offline, Publications and/or Subscriptions
from this database may get undiscovered in the Operations Manager.
81

Guide To Microsoft System Center Management Pack For SQL Server 2014 Replication

Uploaded by

Copyright:

Available Formats

Guide To Microsoft System Center Management Pack For SQL Server 2014 Replication

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Guide To Microsoft System Center Management Pack For SQL Server 2014 Replication

Uploaded by

Copyright:

Available Formats

Guide to Microsoft System Center Management

Pack for SQL Server 2014 Replication

Published: June, 2017

© 2017 Microsoft Corporation. All rights reserved.

All other trademarks are property of their respective owners.

Management Pack Scope............................................................................................................8

Files in this Management Pack....................................................................................................9

Management Pack Purpose..........................................................................................................10

Discovery of SQL Server Replication objects..........................................................................11

Distributor Discovery and monitoring....................................................................................11

Publisher Discovery and monitoring......................................................................................11

Subscriber Discovery and monitoring....................................................................................11

Publication Discovery and monitoring...................................................................................12

Subscription Discovery and monitoring.................................................................................12

Many Publication Snapshots on the same drive....................................................................12

SQL Server Agent state...........................................................................................................12

Maintenance Job failure........................................................................................................12

Replication Database Health..................................................................................................16

Best Practice: Create a Management Pack for Customizations.................................................19

How to import a Management Pack..........................................................................................20

How to enable Agent Proxy option...........................................................................................21

How to configure Run As profile................................................................................................21

TLS 1.2 Protection..................................................................................................................29

Viewing Information in the Operations Manager Console...........................................................30

Version-independent (generic) views and dashboards.............................................................30

SQL Server 2014 Replication views............................................................................................30

Replication Top Level Datacenter Dashboard........................................................................32

Replication Components Datacenter Dashboard...................................................................32

SQL Server 2014 Replication Datacenter Dashboard.............................................................33

Appendix: Terms and Definitions..................................................................................................34

Appendix: Management Pack Views and Dashboards..................................................................35

Appendix: Management Pack Objects and Workflows.................................................................37

Appendix: Run As Profiles.............................................................................................................73

Appendix: Known Issues and Troubleshooting.............................................................................79

June, 2017  Added Distributor name caching to Subscription discovery

June, 2016  Updated the visualization library

May, 2016  Fixed percentage threshold comparison of deactivated subscriptions

April, 2016  Reviewed threading implementation

added missing overridable parameter

January, 2016  Added support for SQL Express edition

November, 2015  Updated the visualization library

November, 2015  Updated the visualization library

October, 2015  Preset dashboards are updated

June, 2015  Original release of this management pack

 Management Pack Scope

 System Center Operations Manager 2012 (Except Dashboards)

Agentless monitoring Not supported

Virtual environment Yes

Management Pack Scope

 SQL Server 2014 Replication

Files in this Management Pack

Microsoft.SQLServer.2014.Replication.Di This Management Pack discovers Microsoft SQL

Microsoft.SQLServer.2014.Replication.M The Microsoft SQL Server 2014 Replication

Microsoft.SQLServer.2014.Replication.Vi This management pack defines views for Microsoft

Microsoft.SQLServer.2014. This Management Pack adds SQL Server 2014

Microsoft.SQLServer.Replication.Library. Microsoft SQL Server Replication Library contains

Microsoft.SQLServer.Replication.Library. This management pack defines the version-

Microsoft.SQLServer.Replication.Library. This Management Pack adds SQL Server Replication

Microsoft.SQLServer.Generic.Presentatio This Management Pack defines common folder