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 🙂
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:
##If not Loaded, Please Load SQLServer Snapins Into PowerShell By Running The Following Cmdlets:
There’s a chance that PowerShell on your machine won’t allow script execution, in this case run the following cmdlet:
##If Script Execution Is Disabled Please 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.
## Beginning of Monitor
## Path To Server ListFile: Edit Path and List Target Servers In File With Line Breaks, For Named Instances Use The Typical "ServerInstance" Convention
foreach ( $svr in get-content "C:Servers.txt")
##Connection String With Server Variable, Distribution Database name is 'Distribution'
$con = "server= $svr;database=Distribution;Integrated Security=sspi"
##Begin SQL Query
##Refreshing Replication Monitor Cache
$cmd = "SET NOCOUNT ON; EXEC sp_replmonitorhelppublisher"
##Getting Info From Replication Sp and Tables, Joining and Selecting
$cmd = $cmd + " CREATE TABLE #COUNTERS"
$cmd = $cmd + " ("
$cmd = $cmd + " [DATABASE] SYSNAME,"
$cmd = $cmd + " REPLICATEDTRANS INT,"
$cmd = $cmd + " REPRATESEC FLOAT,"
$cmd = $cmd + " REPLATENCY FLOAT,"
$cmd = $cmd + " LSN1 BINARY(10),"
$cmd = $cmd + " LSN2 BINARY(10)"
$cmd = $cmd + " )"
$cmd = $cmd + " INSERT INTO #COUNTERS"
$cmd = $cmd + " EXEC sp_replcounters"
$cmd = $cmd + " SELECT DISTINCT "
$cmd = $cmd + " m.Publisher_db,"
$cmd = $cmd + " m.Publication,"
$cmd = $cmd + " s.name AS 'Subscriber',"
$cmd = $cmd + " ma.Subscriber_db,"
$cmd = $cmd + " CASE [Status]"
$cmd = $cmd + " WHEN 1 THEN 'Started'"
$cmd = $cmd + " WHEN 2 THEN 'Succeeded'"
$cmd = $cmd + " WHEN 3 THEN 'In Progress'"
$cmd = $cmd + " WHEN 4 THEN 'Idle'"
$cmd = $cmd + " WHEN 5 THEN 'Retrying'"
$cmd = $cmd + " WHEN 6 THEN 'Failed'"
$cmd = $cmd + " END AS [Status],"
$cmd = $cmd + " CASE warning"
$cmd = $cmd + " WHEN 0 THEN NULL"
$cmd = $cmd + " WHEN 1 THEN 'Expiration'"
$cmd = $cmd + " WHEN 2 THEN 'Latency'"
$cmd = $cmd + " ELSE 'MergeWarning'"
$cmd = $cmd + " END AS [Warning],"
$cmd = $cmd + " C.REPLICATEDTRANS AS 'Awaiting Transactions To Dist',"
$cmd = $cmd + " CONVERT(INT, c.REPRATESEC) AS 'Avrage Trans/Sec to Dist',"
$cmd = $cmd + " CONVERT(DECIMAL(10, 2), c.REPLATENCY) AS 'Avg. Latency to Dist/Sec',"
$cmd = $cmd + " CONVERT(DECIMAL(10, 2), mm.cur_latency) AS 'Avg. Latency to Subscriber'"
$cmd = $cmd + " INTO #MonitorRepl"
$cmd = $cmd + " FROM dbo.MSpublications m"
$cmd = $cmd + " INNER JOIN dbo.MSreplication_monitordata mm"
$cmd = $cmd + " ON mm.publisher_db = m.publisher_db"
$cmd = $cmd + " AND mm.publication_id = m.publication_id"
$cmd = $cmd + " AND mm.agent_type = 3"
$cmd = $cmd + " INNER JOIN dbo.MSdistribution_agents ma"
$cmd = $cmd + " ON ma.publisher_id = m.publisher_id"
$cmd = $cmd + " AND ma.publication = mm.publication"
$cmd = $cmd + " AND ma.job_id = mm.job_id"
$cmd = $cmd + " INNER JOIN sys.servers s"
$cmd = $cmd + " ON ma.subscriber_id = s.server_id"
$cmd = $cmd + " INNER JOIN #COUNTERS C"
$cmd = $cmd + " ON m.publisher_db = C.[DATABASE]"
$cmd = $cmd + " ORDER BY"
$cmd = $cmd + " m.publisher_db,"
$cmd = $cmd + " m.publication,"
$cmd = $cmd + " s.name,"
$cmd = $cmd + " ma.subscriber_db"
$cmd = $cmd + " DROP TABLE #COUNTERS"
$cmd = $cmd + " SELECT *"
$cmd = $cmd + " FROM #MonitorRepl"
$cmd = $cmd + " DROP TABLE #MonitorRepl"
##Creating DataSet Object
$set = new-object system.data.dataset
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
##Filling DataSet With Results
$da.fill($set) | out-null
##Creating Table Object and Inserting DataSet
$dt = new-object System.Data.DataTable
$dt = $set.Tables
##Displaying Current Server Name
##Formating DataTable To A Readable List And Presenting
After executing the script we should get a result that looks like this:
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!