The following is one of 15 replication tips in 15 minutes. Click here to return to the full list of tips.
11 of 15 replication tips: Expire subscribers
If you are replicating over the Internet or low-bandwidth lines, you will find that your pull agents constantly fail. With Replication Alerts, you can configure your agent to alert you when it fails once every failure, every minute, hour, day or some period in between (i.e. twice a day). However, with large numbers of subscribers, the visibility of failures is low due to the noisiness of alerts (you quickly start to ignore them). You will find that your subscribers will expire without your knowledge, requiring you to resend your snapshot. This can be painful if your snapshot is large or if the available bandwidth is small. Hence you want to know which subscribers are close to expiration.
A good idea is to use data transformation services (DTS) to send your MSrepl_errors table from the distribution database on the subscribers to a central location and evaluate the type of errors you are encountering on the subscribers. You will realize that certain subscribers are encountering certain classes of errors such as authentication, network or latency problems. Also, DTS the MSdistribution_history tables locally, and issue queries to determine which pull subscribers have not connected with the publisher over the past 24 hours. Here is an example of such a query:
Declare @count int
SELECT @count=COUNT(max_time) FROM MSdistribution_agents, (SELECTagent_id, max_time=MAX(time) FROM MSdistribution_history GROUP BY agent_id) AS a WHERE a.agent_id=MSDistribution_agents.id AND max_time< DATEADD(d,-1, GETDATE())
Click here to return to 15 replication tips in 15 minutes.
About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied both economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
