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:
- 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.