Foglight for SQL Server (Cartridge) 5.7.5.30

Monitoring Data Replication

If the monitored SQL Server environment contains one or more instances that are configured as distributors (distributing instances) in a replication operation, Foglight for SQL Server now provides a replication monitoring module.


	IMPORTANT: Replication is monitored for instances running on SQL Server 2005 and higher.

The Replication Available flag indicates if an instance is configured as a distributor in these ways:

•

After the instance has been discovered (if the configuration as a distributor took place before the instance was discovered)

Upon the configuration as a distributor for monitored instances. Instances detected as distributors appear with the button Go to the Replication Home Page (

) next to the button Go to the Home Page on the Name column of the Databases table.

The replication home page can be accessed using one of the following methods:

•

Click the button Go to the Replication Home Page.

Figure 2. The Replication button in the Databases table.

•

Click the Replication button on the QuickView panel.

Figure 3. The Replication button in the QuickView panel.

•

Click the link Investigate using the Replication drilldown in the SQL Server Replication pop-up, which appears when you click the Replication icon under the Background Processes section of the instance's home page.

Figure 4. The Investigate using the Replication Drilldown link.

Viewing the Replication Home Page

The table identifies the main elements of the Foglight for SQL Server Replication Home page window, and provides a link to display more information, corresponding to the logical dataflow within the Replication Home page.

Table 3. The Main Elements of the Foglight for SQL Server Replication Home Page window.
Part Name	Description
Replication-related alarms	Monitoring Alarms
Publications drilldown	Monitoring the Replication Publications
Agents drilldown	Monitoring the Replication Agents
Subscriptions drilldown	Monitoring the Replication Subscriptions
Publishing pane	Reviewing the Publishing Process
Distributor Agents pane	Reviewing the Distributor Agents
Subscription pane	Reviewing the Subscription Process
Data flow indicators	Reviewing Data Flow Indicators

The following provides a description of panes of icons and gauges in the main activity areas:

•

Instance identification — used for identifying the instance, its type, and operation time. Identical to the instance identification section of the home page. For details, see Identifying the Instance.

•

Drilldowns — used for carrying out in-depth investigation of each of the main components of the replication process: publications, agents and subscriptions. Access drilldowns by clicking their button on the toolbar or the relevant link on their corresponding panes.

•

Components representing instance data flow — the main activity area in the Foglight for SQL Server Replication Home Page includes several flows that represent the data flow in the replication process. The data flow is described in this topic in a top-down design from the Publishing process to the Subscription process.

•

Panes — displays summarized numerical data about each of the components that take part in the replication-related processes of publishing, distributing, and subscribing. Clicking each component allows you to view a detailed list of the components (for example, Publisher servers). You can also access the relevant drilldown (for example, Publication Replications) for viewing more detailed information about the relevant component in the replication process.

Reviewing the Publishing Process

The Publishing pane provides data regarding the following components of the publishing process:

•

Publishing servers — displays the total number of publishing servers (Publishers) that are currently distributing publications using the selected instance. This number includes publishing servers that reside on the same computer as the distributing instance, or that reside on remote computers.

Clicking this row displays a pop-up, which contains the Publishing servers and Publication databases tables. Each of these tables shows the displayed entity’s name, status and highest severity level.


	NOTE: If more than one publishing servers exist, clicking each publishing server on the table displays only the publication databases residing under the selected server.

•

Publication databases — displays the total number of publication databases that reside under all currently distributing publishers.

Clicking this row displays a pop-up containing a table of all publication databases. This table is identical to the one shown when clicking the Publishing server row, except for the filtering by server, which is carried out by clicking the Server column.


	IMPORTANT: Clicking each publication database’s row opens the Replication Publications drilldown, which only displays publications stored on the selected publication database. To view the entire list of publications from all publication databases, click the Publications toolbar button.

•

Publications — displays the total number of publications that are currently distributed using the selected instance, along with an icon that indicates the highest severity level.

Clicking this row opens the Replication Publications drilldown, which allows reviewing publications by their type. For details, see Monitoring the Replication Publications.

Reviewing the Distributor Agents

The Distributor Agents pane displays the following replication agent types, indicating for each type the number of agents and the highest severity level:

•

Merge — in merge replications, the Merge job first applies the initial snapshot to the subscribing server (Subscriber). Then it connects to the publishing and subscribing servers to update both sides with each incremental data change that takes place.

•

Distribution — in both transactional and snapshot replications, the Distribution job applies the initial snapshot to the subscribing server. In transactional replications, this job also transfers (downloads) newly added transactions stored in the distribution database to the subscribing server.

•

Queue Reader — in transactional replication with the queued updating option, this agent moves (uploads) changes made at the subscribing server back to the publishing server, by reading messages stored in a Microsoft SQL Server queue or a Microsoft Message Queue. Then it applies those messages to the publishing server.


	IMPORTANT: Unlike the Distribution agent and Merge Agent, the Queue Reader agent is an optional component. Only one Queue Reader Agent job exists to service all publishing servers and publications for a given distribution database.

•

Snapshot — in all replication types, the Snapshot agent, which runs on the distributing instance, prepares and maintains the snapshot and synchronization operation. This agent prepares the snapshot files, which contain the schema and data of the entities that are to be published (both tables and database objects). The agent stores the files in the snapshot folder, and tracks synchronization status by recording the synchronization jobs in the distribution database.

•

Log Reader — in transactional replications, the Log Reader agent monitors the transaction log of each published database. It copies the transactions marked for replication from the log to the distribution database.

•

Misc. Jobs — various replication maintenance jobs, which are automatically created by Microsoft.


	NOTE: Some of the miscellaneous jobs are not started by default. In which case a Warning icon appears near the job’s name, and its status is Not Started. It may be advisable to start such a job manually at some point; for example, to clean up the distribution tables.

Selecting a specific agent type’s row (for example, Merge) and clicking it opens the Replication Agents drilldown, displaying only the selected agent type. For details, see Monitoring the Replication Agents.

Reviewing the Subscription Process

The Subscription pane provides data regarding the following components of the subscription process:

•

Subscribing servers — displays the total number of subscribing servers (Subscribers) that are currently making data available to other locations through replication, by hosting one or more Subscription databases. This number includes both subscribing servers that reside on the same computer as the distributing instance and subscribing servers that reside on remote computers.

Clicking this row displays a pop-up, which contains the Subscribing servers and Subscription databases tables. Each of these tables shows the displayed entity’s name, status and highest severity level.


	NOTE: If more than one subscribing servers exist, clicking each subscribing server on the table displays only the subscription databases residing under the selected server.

•

Subscription databases — displays the total number of subscription databases that reside under all currently subscribing servers.

Clicking this row displays a pop-up that contains a table of all subscription databases. This table is identical to the one shown when clicking the Subscribing server row, except for the filtering by server, which is carried out by clicking the Server column.


	IMPORTANT: Clicking each subscription database’s row opens the Replication Subscriptions drilldown, which only displays subscriptions stored on the selected subscription database. To view the entire list of subscriptions from all subscription databases, click the Subscriptions toolbar button.

•

Subscriptions — displays the total number of subscriptions that are currently receiving selected publications through the selected instance, along with an icon that indicates the highest severity level.

Clicking this row opens the Replication Subscriptions drilldown, which allows reviewing subscriptions by their type. For details, see Monitoring the Replication Subscriptions.

Reviewing Data Flow Indicators

The data flow indicators provide a representation of the average delivery rate of each of the replication agent types, except for Misc. Jobs. A numerical value indicates the average number of commands transferred using the specified agent during the specified time range.

Clicking each flow displays a pop-up that contains a description of the flow, and a chart that shows the relevant data plotted over the specified time range.

Monitoring the Replication Publications

The Replication Publications drilldown allows viewing more detailed information about all publications distributed using the selected instance.

To investigate the requested publication:

Select which publication type to display, by selecting the tile that represents the requested type: Snapshot, Transactional, Transactional with update, Merge or All.


	IMPORTANT: In each of these tiles, it is possible to filter the display to show only publications with a specific severity status, by clicking the icon that represents that status. For example, Not Started.

Review the requested publication data on the Publications table:

•

Severity — the highest severity level

•

Status

•

Publishing server name

•

Publication database name

•

Publication type

•

Number of subscriptions

•

Agents taking part in the transaction — Log Reader, Queue Reader, and Snapshot agents

Click the publication’s row to access the Publication Details screen.

The Publication Details Screen

The Publication Details screen allows tracking the process of publishing the selected publication, by displaying all components involved in the process and providing links for further investigation.

Figure 5. The Publication Details screen.

•

Publication Data — provides the publication-related data that appears on the Replication home page, that is, publishing server, publication database, and publication name, and the following data:

•

Type — the publication type (transactional, snapshot, and so on).

•

Status — can have one of the following values: Running, Failed, Retrying, Idle, and Not started.

•

Last synchronized — the time and date when the publication data items residing on the subscribing and publishing server were most recently synchronized. This parameter is highly significant for publications configured to run continuously.

•

Due to expire — displays the time and date when the subscription expires, if changes recorded in the distribution database are not yet synchronized with the subscribing server.


	NOTE: The value displayed on the Due to expire field is set by adding the number of hours defined in the Retention value to the value displayed in the Last synchronized field. For example, if the most recent synchronization took place on 17:00, and the retention period is 14 hours, the value displayed here is 7:00.

•

Retention — indicates the predefined distribution retention period. If by the end of this period there are changes in the distribution database that have not been delivered to the subscribing server and synchronized, the subscription expires and drops.

•

Agents used by Publisher — displays the replication agent used for replicating the publication data to the subscribing server.

The agent types displayed on this pane for each publication type are as follows:

•

In snapshot, Transactional and Merge publications — Snapshot and Log Reader.

•

In Transactional with update publications — Snapshot, Log Reader and Queue Reader.


	IMPORTANT: To view a detailed representation of the agent’s actions using the Replication Agents drilldown, click the requested agent’s row. The Replication Agents drilldown appears, with the clicked agent’s row selected in the All Agents table. For details, see Monitoring the Replication Agents.

•

Subscription Data — provides the following data regarding each of the publication’s subscriptions:

•

Subscription database

•

Subscription status — can have one of the following values: Running, Failed, Retrying, Idle, and Not started.


	IMPORTANT: To view a detailed representation of a specific subscription using the Replication Subscriptions drilldown, click the requested subscription’s name. The Subscription Details screen opens, which provides detailed data about the selected subscription. For details, see The Subscription Details Screen.

Monitoring the Replication Agents

The Replication Agents drilldown provides detailed information of all the agents that were active during the selected time range. This drilldown allows you to filter the display to show agents of selected type or a selected severity level within a certain agent type, and review all the agent’s session actions.

Figure 6. The Replication Agents drilldown.

This drilldown contains the following sections:

•

Agent type tiles — used for configuring the list displayed on the Agents table; either displaying all agents by clicking All, or filtering the display by agent type or severity level.

•

Agent table — provides detailed data regarding each of the agents or jobs selected to be displayed. For details, see Reviewing agent-specific data.

•

Sessions ran by the currently selected agent — displays all the sessions run by the agent selected in the table during the specified time range. Selecting a session on the list displays its details on the middle and right sections.

•

Details of the currently selected session — displays prominent statistics about the session currently selected on the list.

•

Agent-specific session actions data — displays actions (and merge articles, for Merge agents) for all sessions run during the specified time range by the agent selected in the Agent table. For details, see Reviewing session activity data for the selected agent.

Reviewing agent-specific data

The Agents table displays all agents whose type tile was selected on the toolbar. Each row on this table provides the following data about a specific agent:

•

Agent identification — the following agent-related data:

•

Highest severity level

•

Name

•

Type

•

Average delivery rate during the specified time range.


	NOTE: Hovering over this gauge displays a pop-up that contains a description of the flow, and a chart that shows the relevant data plotted over the specified time range.

•

Operation start time

•

Entities involved in the publishing process — provides the names of the following components:

•

•

•

•

•

Subscription database

Except for agents of type Misc. Jobs, clicking each agent row displays the sessions ran by the selected agent during the specified time range. For details, see Reviewing session activity data for the selected agent.

Reviewing session activity data for the selected agent

The lower section of the Replication Agents drilldown displays agent-specific session data for all agents whose type is other than Misc. Jobs.

To view the session actions of a single agent:

Click the requested agent’s row on the Agents table.

Session data is retrieved only on demand by clicking a button or link, or by refreshing a screen component. The initial display of the Session section provides no data on the Sessions Actions pane.

Figure 7. Click the link to retrieve Session data.


	NOTE: When the selected agent’s type is Merge, the Sessions section also displays the Session articles.

To retrieve action data for the first session on the session list, click the link under the message title.

To retrieve action data for any other session, click the requested session on the session list on the left.

The retrieved data is displayed on a detailed list of all actions performed by the session during the specified time range, indicating the time the action took place, and the action message.

Monitoring the Replication Subscriptions

The Replication Subscriptions drilldown allows performing an in-depth analysis of all subscriptions servers by the selected distributing instance.

To investigate the requested subscription:

Select which subscription type to display, by selecting the tile that represents the requested type: Snapshot, Transactional, Transactional with update, Merge or All.


	IMPORTANT: In each of these tiles, it is possible to filter the display to show only subscriptions with a specific severity status, by clicking the icon that represents that status. For example, Not Started.

Review the requested publication data on the Subscriptions table:

•

Severity — the highest (worst) severity level

•

Status

•

Subscribing server name

•

Subscription database name

•

Subscription type — Pull, Push, or Anonymous

•

Publication name

•

Publishing server name

•

Publication database name

•

Publication type

•

Agents taking part in the transaction — Log Reader, Queue Reader, and Snapshot agents

Click the subscription’s row to access the Subscription Details screen.

The Subscription Details Screen

The Subscription Details screen allows tracking the process of replicating data to the selected subscription, by displaying all components involved in the process and providing links for further investigation.

Figure 8. The Subscription Details screen.

•

Publisher — provides the publication-related data that appears on the Replication home page, that is, publishing server, publication database, and publication name, and the following data:

•

Type

•

Latency

•

Status — can have one of the following values: Running, Failed, Retrying, Idle, and Not started.

•

Agents used by Publisher — displays the replication agents used for replicating the publication data to the subscribing server.

The agent types displayed on this pane for each publication type are as follows:

•

In snapshot, Transactional and Merge publications — Snapshot and Log Reader.

•

In Transactional with update publications — Snapshot, Log Reader, and Queue Reader.


	IMPORTANT: To carry out an in-depth analysis of the agent’s actions using the Replication Agents drilldown, click the requested agent’s row. The Replication Agents drilldown appears, with the clicked agent’s row selected in the All Agents table. For details, see Monitoring the Replication Agents.

•

Subscriber — provides the following data regarding each of the publication’s subscriptions:

•

Subscribing server

•

Subscription database

•

Subscription type — Push, Pull, or Anonymous. For further details, see glossary definitions for Pull subscription, Push subscription, and Anonymous subscription.

•

Subscription status — can have one of the following values: Running, Failed, Retrying, Idle, and Not started.

•

Sessions — provides agent-specific session data for all agents taking part in the selected subscription process. If the process involves more than one agent, this section is divided into the following panes:

•

Publisher to Distributor — displays the session actions of the Snapshot or Log Reader agent.

•

Distributor to Subscriber — displays the Distribution agent’s session actions.

Monitoring Alarms

The Alarms pane contains a table that displays only replication-related alarms, invoked if the metrics defined in the replication-related rules have been exceeded during the specified time range (by default: the last 60 minutes). For a detailed list of such alarms, see Replication-related alarms.

Unlike the Alarms pane on the Foglight for SQL Server home page, which always displays all alarm types, this table allows alternating between two display methods:

•

Displaying all alarms — by clicking All.

•

Filtering the alarm display by severity level — by clicking a specific severity level.


	IMPORTANT: Fatal alarms are invoked when the SQL Server agent stops responding, and are not due to replication errors. Therefore, the count of alarms whose severity level is Fatal (indicated in red) should always remain 0. The alarm display should be filtered by clicking either the Warning or Critical severity level.


	NOTE: To view detailed information regarding each alarm, go to the Replication Agents drilldown.

Figure 9. Alarm Indicator Descriptions
Indicator	Description
Yellow (Warning)	The metric (or workload resource) has exceeded the configured threshold or deviated from the baseline.
Orange (Critical)	The metric (or workload resource) has exceeded a threshold or extremely deviated from the baseline.
Red (Fatal)	The SQL Server is not responding, triggering a fatal alarm.

Replication-related alarms

The replication-related alarms are as follows:

•

Replication Agents - Delivery Latency — invoked when the delivery latency for a specific instance exceeds a predefined threshold.

•

Replication Agents - Status — invoked when the replication agent fails to start.

•

Replication Publications Due to Expire — invoked when the number of hours left until the replication publications are due to expire falls below a predefined threshold.

•

Replication Publications - Errors — invoked when an error is encountered in one of the replication publications.

•

Replication Publications - Status — invoked when a replication publication fails to start.

•

Replication Subscriptions - Status — invoked when a replication subscription fails to start.

•

Replication Available — invoked when the replication process has encountered a failure.

For details, see the Foglight for SQL Server Reference Guide.

Monitoring SQL Performance

The SQL Performance page provides the ability to investigate the activity and resource consumption of a selected instance. There are two levels of metrics available:

•

Without SQL PI configured — basic History and lock analysis data. See Monitoring SQL Performance without SQL PI configured

•

With SQL PI configured — The ability to perform a more in-depth analysis and investigation of the Instance activity and resource consumption by adding a dimension view of the activity, change tracking analysis, execution plan analysis and compare toll. See Monitoring SQL Performance with SQL PI Configured.

The SQL Performance page displays the following components:

•

The Instance View — Provides the ability to investigate and analyze the resource consumption of the instance.

•

The Resource toolbar — By selecting one of the resources, the resource consumptions charts and the overview section are updated to display only the relevant information regarding the activity of the selected resource.