Replication monitoring

Got fed up of looking at the replication monitor day after day so created this simple stored proc to do it for me.

CREATE PROCEDURE dba_CheckSubscriptions
AS BEGIN
SELECT [Subscriber_server] + '/' + [db_name] AS [Server], [last_sync_date] AS [SyncTime], CASE WHEN [last_sync_date] > DATEADD(minute,-10,GETDATE()) THEN 'True' ELSE 'False' END AS [IsSync]
      FROM dbo.sysmergesubscriptions
      WHERE [last_sync_date] IS NOT NULL
END

This routine will populate a list of subscriptions attached to a merge replication (if you're using transactional or snapshot replication there are two different tables, not too sure which but just look under system tables)

You can then set up a job to email you if the InSync column comes up as false... Of course I don't use SQLMail as it poses security issues so I have instead went for the .NET way of notifying me if there's an issue, below is the code I used in a console app, notice I'm also using the DBConnection and DBRecordset classes that you can look at here.

Sub Main()
        Dim strData As String = ""
        Dim blnContinue As Boolean = True
        Dim strServer As String = ""
        Dim strDatabase As String = ""
        Dim strEmailServer As String = ""
        Dim strEmailAddress As String = ""
        Dim blnFailed As Boolean = False
        Dim myConnection As DBConnection = New DBConnection

        ' Check the arguments given are valid, we need the following
        ' %1 - Name of the database server
        ' %2 - Name of the Database
        ' %3 - SMTP Server to connect to
        ' %4 - Email address to send to
        If (blnContinue) Then
            Dim strCommands As String() = Command.Split(",")
            If (strCommands.Length <> 4) Then
                Console.WriteLine("Parameters Incorrect")
                Console.WriteLine("Usage ReplMon [Database Server],[Server Instance],[Email Server],[Email Address]")
                Console.WriteLine("   e.g. ReplMon 'BACKUP\BackupLogs', 'Logs', 'TAG', 'dba@mymail.co.uk'")
                blnContinue = False
            Else
                ' Get the details into our vars, notice I strip any quotes
                strServer = strCommands(0).Replace("""", "").Replace("'", "")
                strDatabase = strCommands(1).Replace("""", "").Replace("'", "")
                strEmailServer = strCommands(2).Replace("""", "").Replace("'", "")
                strEmailAddress = strCommands(3).Replace("""", "").Replace("'", "")
            End If
        End If
        ' Connect to the database with the details given
        If (blnContinue) Then
            myConnection.DatabaseName = strDatabase
            myConnection.Server = strServer
            blnContinue = myConnection.ConnectDatabase()
            If (Not blnContinue) Then
                Call Console.WriteLine("Unable to connect to the database " + strServer + "\" + strDatabase)
            End If
        End If
        ' Get the data from the stored proc and produce the email
        If (blnContinue) Then
            Dim recSet As DBRecordSet = New DBRecordSet(myConnection)
            recSet.OpenRecordSet("EXEC dba_CheckSubscriptions")
            If (recSet.EOF) Then
                Console.WriteLine("No records retrieved from database, replication maybe faulty")
                blnContinue = False
            Else
                ' Put the information from the database into a text object to send in the email
                strData = "Location            Last Sync Time      In Sync?" + vbCrLf
                Do Until (recSet.EOF)
                    strData += recSet.Fields("Computer").ToString.PadRight(20, " ")
                    strData += recSet.Fields("SyncTime").ToString.PadRight(20, " ")
                    strData += recSet.Fields("InSync").ToString + vbCrLf
                    ' If we have a sync issue, mark the failed flag we'll use this to change the header on the email
                    If (recSet.Fields("InSync") <> "True") Then
                        blnFailed = True
                    End If
                    ' Move to the next record
                    recSet.MoveNext()
                Loop
            End If
            ' CLose up the recordSet
            recSet.Close() : recSet = Nothing
        End If
        ' Close the Database COnnection
        myConnection = Nothing

        ' Send the mail
        If (blnContinue) Then
            Dim mailMsg As Net.Mail.MailMessage = New Net.Mail.MailMessage
            mailMsg.From = New Net.Mail.MailAddress(strEmailAddress)
            mailMsg.To.Add(New Net.Mail.MailAddress(strEmailAddress))
            mailMsg.Subject = IIf(blnFailed, "DATABASE REPLICATION SYNC FAILED", "Database Replication is in sync")
            mailMsg.Body = strData
            mailMsg.IsBodyHtml = False
            ' Set priority if we have a fail
            mailMsg.Priority = IIf(blnFailed, Net.Mail.MailPriority.High, Net.Mail.MailPriority.Normal)
            Dim smtpClient As New Net.Mail.SmtpClient
            smtpClient.Host = strEmailServer
            smtpClient.Send(mailMsg)
            mailMsg = Nothing
        End If
    End Sub

Notice no error logging??? so did I, I know my email and SQL server work so I don't care, you may want to put some in.

With the app compiled I then just fill in the arguments as described and then set it up as a scheduled task to run every four hours. I then get and email informing me of the up to date status of the database replication... Simples.