Contact us

Madeira Data Solutions

Your Data, Our Solutions

Cross-Server Replication Health-Check Using PowerShell

Written By: Daniel Grossman 28/09/2012

A few years ago, I used to work for an organization that processed big bulks of real-time data coming from several sites around the globe. Running mostly on SQL Server 2000 and Windows Server 2003, the entangled topology of cross-platform Replications between New York, Japan, India and Singapore was a complex system to maintain. As my work was often a hundred percent dedicated to Replication troubleshooting, I was seeking for an efficient way to monitor multiple-server replications at once, a one-stop-shop where I can quickly see a status list of all my replication servers and know immediately if something went wrong. After some unsatisfactory attempts with SQL Server Replication Monitor and script writing, we decided to purchase a 3rd-party product that handled the workloads and showed some pretty colors.

Recently I’ve decided to dive into SQL PowerShell, an integrative Windows terminal and a powerful tool indeed. I always wondered what was that little annoying option which appears every time I right-click objects in SSMS, and after listening to some promising opinions I’ve decided to find out. Using PowerShell allows me to access and manipulate SQL Server and Windows objects, automate processes and enjoy flexibility in cross-server processes. In the following article I will apply some of those features in order to demonstrate a cross-server replication health-check tool. This nifty script will return a simple summary of replication-related info from a list of servers stored in a text file. Exactly the quick and simple tool I was looking for a few years ago 🙂

ps

In order to understand the code in this article you might have to be familiar with some basic concepts of PowerShell, although executing the script won’t require that. It is true that PowerShell may seem intimidating at first, it has its own environment, concepts and syntax, but the effort is promisingly rewarding. Here is a good place to start: http://msdn.microsoft.com/en-us/library/dd938892.aspx

Lets begin by opening PowerShell, this can be done in a hundred ways, here are two of them:

– Right-Click any server or database in SSMS Object Explorer and then choose ‘Start PowerShell’

– Go to Start> All Programs> Accessories> Windows PowerShell> Windows PowerShell.exe

First of all we should make sure that the SQL Server Snap-ins are loaded:

There’s a chance that PowerShell on your machine won’t allow script execution, in this case run the following cmdlet:

Create a text file, containing a list of (Distribution) servers that you wish to check, separated with a line break. The default path for this file is “C:Servers.txt “ but you can change it at the top of the script.

NOTE: At the moment only Distribution Databases named ‘Distribution’ are supported.

Last but not least, you can type the path to the script file (chich you can download here), or select all, copy & paste the following script to your PowerShell window. Hit Enter to run the health-check.

After executing the script we should get a result that looks like this:

Screen-Shot-2012-09-16-at-02.58.50

We can notice that the data for each server begins with a server name header. Following the header we receive the information for each publisher database> publication on that database> subscriber server> subscriber database.

Statuses can be: ‘Started’, ‘Succeeded’, ‘In Progress’, ‘Idle’, ‘Retrying’ and ‘Failed’.

Warnings can be: NULL (OK), ‘Expiration’, ’Latency’ and ‘MergeWarning’.

For example: in the first batch we can see a publication on server WINDOWS-FF742FC, the publisher database is ‘AdventureWorks’ (of course), the publication is ‘WinToRepl’ and the subscriber is the same instance as the publisher but uses a different database.

There are no warnings, there are no transactions waiting to transfer from the publisher to the distributor but the status indicates ‘In Progress’.  This is due to the fact that transactions are still being replicated from the distributor to the subscriber.

Currently there is no property that displays this information but hopefully this feature will be included in the next version of the tool.

That was just a small glimpse into the abilities of SQL PowerShell. Along with automated monitoring it can be used for many more powerful tasks of managing, troubleshooting and fetching useful data. Using SMO, ADO.NET and WMI you can fundamentally link and control almost every SQL SERVER or Windows object, while customizing processes to your own needs. From here on it’s all about being creative!

3 responses to “Cross-Server Replication Health-Check Using PowerShell”

  1. Golam Kabir says:

    it works when Distribution and Publisher are on the same instance. If distribution is on a different instance than the publisher, it cannot give any info

  2. Sree says:

    Hi Thanks for the scipt. I am getting below error when i try to execute the same.
    The object of type “Microsoft.PowerShell.Commands.Internal.Format.FormatStartData” is not valid or not in the correct sequence. This is likely caused by a user-specified
    “format-list” command which is conflicting with the default formatting.
    + CategoryInfo : InvalidData: (:) [out-lineoutput], InvalidOperationException
    + FullyQualifiedErrorId : ConsoleLineOutputOutOfSequencePacket,Microsoft.PowerShell.Commands.OutLineOutputCommand

    So i have edited the code and added out-String to the below line.

    $dt|Format-List | Out-String

    The above changes are worked for me. But i am able to see the result only for one server. Looks like its resulting the last server in the server list. Can you please help me on this?

    -Sri

  3. Mike says:

    The script works great and gives me exactly what I need to report on, however how could I go about emailing the output to a specific address? Thanks in advance!

Leave a Reply

Your email address will not be published. Required fields are marked *