9 Mar 2010

Using Powershell to understand a replication topology

I recently had to investigated a client replication topology.  I needed to understand the publications, subscribers and the articles. The problems I was facing were as follows:

  • The replication topology was only defined on the production infrastructure.
  • There was no documentation about the replication topology.
  • I had no way of being able re-create replication topology.
  • Only short amount of time.

I was able to script the replication topology from the SQL Server Management Studio.  This was partially helpful as I now had a script which could be used to review the replication topology.  However this end up being a fairly large file and would be time consuming to review all information. 

So I deicide to write a PowerShell, SQL Server Powershell for the Invoke-Sqlcmd commandlet, that would do the following steps:

  • Parse the file to find the lines containing the following store procedures:
    • sp_addarticle.
    • sp_addsubscription.
    As theses contain most useful parameters to help understand the replication topology
  • Pares the line to find the key parameters and their values.
  • Then insert the information into tables within a database.

I chose to use PowerShell because this was a perfect fit for a scripting language: manipulating a text file and doing regular expression on strings.

This help me see that articles were being repeated across publication, some of the publication had identical articles but had different subscriber.

A copy of powershell script is available here.