Lesson 1: Monitoring and Troubleshooting Replication
It is important to monitor all aspects of the replication process, from overseeing changes that are made to a publication to verifying the replicated changes on the Subscriber. SQL Server tools that are designed for this purpose are the most efficient means of monitoring replication, but it is also possible to use system stored procedures and to retrieve information from system tables.
After this lesson, you will be able to
- Monitor and troubleshoot Microsoft SQL Server 7 replication using tools, system stored procedures, and system tables
- Generate a replication script
Estimated lesson time: 60 minutes
Using SQL Server Replication Monitor
SQL Server Replication Monitor is a component of SQL Server Enterprise Manager that is added to the console tree on the Distributor when the Distributor is enabled. It is designed for viewing the status of replication agents and for troubleshooting potential replication problems.
You can use SQL Server Replication Monitor to
- View a list of Publishers, publications, and subscriptions to the publications supported by the Distributor
- View the real-time status of scheduled replication agents
- Display current execution data for a replication agent to obtain a running total of transactions, statements, inserts, and updates that have been processed
- Configure replication agent profiles and properties
- Set up and monitor alerts that are related to replication events
- View replication agent histories
NOTE
Although the Distribution and Merge Agents run on the Subscriber for pull subscriptions, the Replication Monitor is not available on the Subscriber. The status of the agents running on the Subscriber is monitored in the Replication Monitor running on the Distributor to which the Subscriber agents connect.
Maintaining Replication
When you manage replication, you will need to address certain maintenance issues, including space management and backup strategies.
Space Management
Space management requires that you
- Monitor the size of the distribution database to ensure that enough space exists to store the replication jobs. This involves the following:
- Determining the retention period for the replication history and replicated transactions
- Setting up Distributor properties to control the retention period
- Monitor Miscellaneous Agents.
History tables and replicated transactions consume database storage space. Several replication agents clear these tables periodically. Ensure that the agents listed in the following table are running.
| Miscellaneous agent | Description |
|---|---|
| Agent history clean up: distribution | Removes replication agent history records from the distribution database |
| Distribution clean up: distribution | Removes replicated transactions from the distribution database |
| Expired subscription clean up | Detects and removes inactive subscriptions from published databases |
| Reinitialize subscriptions having data validation failures | Reinitializes (performs another initial synchronization of) all subscriptions that have failed due to data validation failure |
| Replication agents checkup | Detects replication agents that are not actively logging history |
Backup Strategies
It is important to back up the distribution database, because if it is lost you must re-create all publications and subscriptions. By default, the trunc. log on chkpt. option is enabled when the distribution database is created. If you choose to perform transaction log backups on the distribution database, you must first disable this option. Plan and implement backup strategies by
- Monitoring any issues with the distribution database that will affect the Publisher. For example, if the distribution database runs out of space, transactions that are waiting to be published cannot be removed from the transaction log on the Publisher.
- Preparing recovery and resynchronization plans in the event that a Publisher, Distributor, or Subscriber fails.
Due to the large number of different possible replication configurations, detailed backup and restore strategies are not presented in this book. Books Online has matrixes that present strategies for each possible configuration.
Using Replication Scripts
A replication script provides many benefits, including the following:
- It can save time if many servers must be configured identically. You can execute the script instead of performing installation steps repeatedly with SQL Server Enterprise Manager.
- It provides a recovery mechanism. For example, if a Publisher or Distributor fails, you can execute the script to reinstall replication after the server hardware is replaced.
- It allows you to track different versions of your replication environment by periodically creating and storing scripts in a source code control system.
- It allows you to customize an existing replication environment.
Creating and Executing Replication Scripts
To create replication scripts, select Tools/Replication/Generate Replication Scripts in SQL Server Enterprise Manager. Figure 17.1 shows the resulting dialog box. A script automatically has the name of the server that was used when replication was defined. To execute a script on another server, change server names in the script as appropriate.
Figure 17.1 The Generate SQL Scripts — Replication dialog box
Exercise: Generating a Replication Script
In this exercise, you will generate a script, preview it, and then save it to disk.
- To generate a script
- In the console tree of SQL Server Enterprise Manager, click your server.
- On the Tools menu, point to Replication, and then click Generate Replication Scripts.
- Review the available options.
- Click Preview.
- Review the script that was generated.
- Click Save As. Save the script to C:\Sqladmin\Exercise\Ch17\Replication.sql.
- Click OK to acknowledge that the script was saved.
- Click Close and Cancel to close the Generate Script dialog boxes.
Monitoring SQL Server Replication Performance
You can use the Replication Monitor in SQL Server Enterprise Manager, Microsoft Windows NT Performance Monitor, and system stored procedures to monitor replication performance. You can obtain information on delivered transactions, undelivered transactions, rates of delivery, and latency.
Using Windows NT Performance Monitor
Replication counters that graphically display replication details are useful for retrieving information about replication. You can use the counters described in this section in Windows NT Performance Monitor.
SQLServer:Replication Agents
The counter for the SQLServer:Replication Agents object has an instance for each type of replication agent.
| Counter | Description |
|---|---|
| Running | The number of replication agents, of the specified type, currently running |
SQLServer:Replication Dist.
The SQLServer:Replication Dist. object has counters for monitoring instances of the Distribution Agent.
| Counter | Description |
|---|---|
| Dist:Delivery Latency | The current amount of time, in milliseconds, that elapses between when transactions are delivered to the Distributor and when they are applied at the Subscriber |
| Dist:Delivered Commands/sec | The number of commands per second delivered to the Subscriber |
| Dist:Delivered Transactions/sec | The number of transactions per second delivered to the Subscriber |
SQLServer:Replication Logreader
The SQLServer:Replication Logreader object has counters for monitoring instances of the Log Reader Agent.
| Counter | Description |
|---|---|
| Logreader:Delivery Latency | The current amount of time, in milliseconds, that elapses between when transactions are applied at the Publisher and when they are delivered to the Distributor |
| Logreader:Delivered Commands/sec | The number of commands per second delivered to the Distributor |
| Logreader:Delivered Transactions/sec | The number of transactions per second delivered to the Distributor |
SQLServer:Replication Merge
The SQLServer:Replication Merge object has counters for monitoring instances of the Merge Agent.
| Counter | Description |
|---|---|
| Uploaded Changes/sec | The number of rows per second merged from the Subscriber to the Publisher |
| Downloaded Changes/sec | The number of rows per second merged from the Publisher to the Subscriber |
| Conflicts/sec | The number of conflicts per second occurring during the merge process |
SQLServer:Replication Snapshot
The SQLServer:Replication Snapshot object has counters for monitoring instances of the Snapshot Agent.
| Counter | Description |
|---|---|
| Snapshot:Delivered Commands/sec | The number of commands per second delivered to the Distributor |
| Snapshot:Delivered Transactions/sec | The number of transactions per second delivered to the Distributor |
Using System Stored Procedures
You can retrieve information about replication using system stored procedures. This method allows you to reference replication information that may be used in triggers, user-defined stored procedures, or scripts. Some of these system stored procedures are listed in the following table.
| To retrieve information on | Use |
|---|---|
| Servers | sp_helpserver sp_helpremotelogin |
| Databases | sp_helpdb sp_helpdistributor sp_helppublication sp_helpsubscription sp_helpsubscriberinfo |
| Replication activity | sp_replcmds sp_repltrans sp_replcounters |
Viewing Replication Agent Histories
History tables contain information for all replication agents. You should regularly view replication histories to identify any tasks that fail and the reasons for the failures. Message detail provides an indicator of the issues for example, connectivity problems, permission restrictions, and log-full errors.
Viewing Selected Replication Agent Histories
With the Agent History in SQL Server Replication Monitor, you can view replication information from the history tables for one or more sessions of a selected agent. Agent History allows you to filter the list of sessions to show
- All sessions
- Sessions in a specified time frame, such as the last 24 hours, the last two days, or the last seven days
- Sessions with errors
Using System Tables
The history tables in the distribution database track replication job activity for all replication agents. Instead of viewing the agent histories in the Replication Monitor, you can query the tables directly in the distribution database. These history tables (one for each agent) are
- MSsnapshot_history
- MSlogreader_history
- MSdistribution_history
- MSmerge_history
Troubleshooting Replication
Many difficulties in replication processing involve connectivity and security. Before these can be addressed, you must determine which servers are involved in a replication problem by observing the processing order of the replication agents. Troubleshooting and resolution should focus on the access to each of the servers and to the databases that are involved in the replication scenario.
Check the Error Logs
Several error logs can assist you in troubleshooting replication problems: SQL Server Error Log, SQL Server Agent Error Log, and Windows NT Event Viewer. You also can use SQL Server Profiler to troubleshoot replication.
Configure and Monitor Replication Alerts
Replication alerts are standard SQL Server alerts, configured to respond to conditions caused by the replication process. In SQL Server Enterprise Manager, you can configure all of the replication alerts under Alerts in SQL Server Agent or under Replication Alerts in the Replication Monitor. You must add new replication alerts under Replication Alerts in the Replication Monitor; otherwise they will show up only in SQL Server Agent. If you use the sp_add_alert system stored procedure to add a replication alert, you must specify the category 'Replication' with the @category_name argument if you want the new alert to be listed under Replication Alerts in the Replication Monitor.
A number of predefined replication alerts are created when you enable publishing and distribution. In order to use any of these alerts, you must enable it and add one or more operators to be notified when it fires. As for standard SQL Server Agent alerts, you can specify a job to be executed when the alert fires. The following predefined replication alerts are created for you:
- Replication: Agent custom shutdown
- Replication: Agent failure
- Replication: Agent retry
- Replication: Agent success
- Replication: Expired subscription dropped
- Replication: Subscriber has failed data validation
- Replication: Subscriber has passed data validation
- Replication: Subscriber reinitialized after validation failure
Verify SQL Server Services
Replication agents run under the user context of the SQLServerAgent service. If you have difficulty with the MSSQLServer service or the SQLServerAgent service, verify that
- MSSQLServer and SQLServerAgent services are running.
- The service account and password are properly configured for the SQLServerAgent service. It is recommended that all participants in the replication process use the same Windows NT domain account for the SQLServerAgent service.
- Multidomain environments have service accounts that are trusted across domains.
Test the Connectivity
By default, SQL Server replication uses the same Windows NT domain user account that SQL Server Agent uses. If you experience a problem with connectivity, test the connectivity as follows:
- For a push subscription,
- Log on to the Distributor with the same Windows NT account that the SQLServerAgent service uses on the Distributor.
- From the Distributor, use SQL Server Query Analyzer, choose Windows NT Authentication Mode, and connect to the Subscriber.
- For a pull subscription,
- Log on to the Subscriber with the same Windows NT account that SQL Server Agent uses on the Subscriber.
- From the Subscriber, use SQL Server Query Analyzer, choose Windows NT Authentication Mode, and connect to the Distributor.
If you cannot connect using either of these methods, the problem is with security rather than replication.
Connectivity to Windows 95 and Windows 98 Servers
On Windows NT_based SQL Servers that need to replicate data from a Microsoft Windows 95_based or Windows 98_based SQL Server, use the SQL Server Client Network Utility to define an alias for the Windows 95 or Windows 98 server that uses the TCP/IP or Multiprotocol network library. This is necessary because Windows NT_based SQL Servers use the Named Pipes client network library by default, and Windows 95_based and Windows 98_based SQL Servers do not support incoming Named Pipes connections.
Check the Replication Agent Schedules
If replication is not occurring as expected, you may simply have to wait for the agents to start as scheduled, or you can manually start the agents to initiate the replication process.
Consider the following transactional replication scenario. After creating a publication and a subscription, the tables corresponding to the publication articles are not being created and populated on the Subscriber. You have checked the security and the connectivity between the Publisher, the Distributor, and the Subscriber, and they are working correctly.
The most likely cause for the replication not occurring in this case is that the initial synchronization has not taken place. This is probably because the Snapshot Agent has not run to generate the initial snapshot that the Distribution Agent needs in order to perform the initial synchronization. Depending on how the initial snapshot was scheduled when the publication was created, the initial snapshot may be created later or on demand. You need either to wait for the snapshot to be created or to start the Snapshot Agent for the publication manually before the initial synchronization will occur. After the initial synchronization is complete, regular transactional replication will begin.
Exercise: Modifying the History Retention Properties on the Distributor
In this exercise, you will modify the length of time that the Distributor retains transaction records in the distribution database. You will also modify the length of time that the Distributor retains replication performance history.
- To modify the history retention properties of the Distributor
- In the console tree of SQL Server Enterprise Manager, click your server. On the Tools menu, point to Replication, and then click Configure Publishing, Subscribers, And Distribution.
- Click Properties to modify the properties of the distribution database.
- Increase the maximum length of time that the Distributor stores transaction records to 96 hours.
- Increase the length of time that the Distributor stores replication performance history to 72 hours.
- Click OK to close the distribution Properties dialog box.
- Click OK to close the Publisher and Distributor Properties dialog box.
Exercise: Reviewing the Properties of the Miscellaneous Agents
In this exercise, you will review the properties of the Miscellaneous Agents.
- To review the properties of the Miscellaneous Agents
- In the console tree, expand Replication Monitor, expand Agents, and then click Miscellaneous Agents.
- For each of the agents in the details pane, perform steps 3, 4, and 5 to see some of the properties of each agent.
- Right-click the agent, and then click Agent Properties.
- On the Steps tab, click Edit, and then review the Command box.
- Click Cancel, and then click Cancel again to close the agent dialog boxes.
What is the function of this agent and how often does it run?
Answer
Lesson Summary
It is important to make backups of the distribution database if you are using replication because most of the replication configuration information is stored in the distribution database. SQL Server provides many tools for monitoring and checking replication. You can use the Replication Monitor in SQL Server Enterprise Manager, the Windows NT Performance Monitor, system stored procedures, agent histories, and replication alerts to monitor the status and performance of replication on your servers. Most problems with replication are caused by connectivity or security problems between the servers.
EAN: 2147483647
Pages: 100