How to Detect SQL Server Replication Using PowerShell or CMD

command linepowershellreplicationsql server

I am trying to find a way to detect if a customer has SQL Server replication on. This helps decide whether they can use Express (as they frequently request) and this is one of the blocking factors.

Preferred responses would use a Powershell or command line script and would not require anything additional to be installed as this would need to work on many different server configurations. Also, the version of SQL Server would vary.

I do not need to know status or do monitoring, just check whether it is in use.

Windows/MSSQL are not my strong suit, so if there are any incorrect assumptions in this question, please feel free to point them out.

The goal is to login via RDP, open PS or CMD, paste in a command and know right away whether customer has replication and will not be able to utilize SQL Server Express (I know there are other factors, but this one is the first I am tackling).

As a quick note, I did do a lot of research on this before I came to ask. I found tons of information on monitoring replication, but nothing to just check if it was there. I wanted to put this to make sure nobody thinks I skipped the "rtfm" or "google it" steps.

Best Answer

You can use Replication Management Objects. You can interrogate them locally or remotely (the better option). The DLLs should be on some systems. If you still have known SQL 2000 instances please test them as it's unclear if modern versions of RMO will still talk down to SQL 2000 instances; 2005 and above should be more reliable.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo")

$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer(".")
if ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
    "Has Replication"
}

There are other flags you can check out. I should tell you that RMO is deprecated (much to my chagrin) but it still works for now.

Another option was to

Import-Module SQLPS -DisableNameChecking
Get-ChildItem SQLSERVER:\SQL\...(server)...\...(instance or DEFAULT)...\Databases | Where { $_.ReplicationOptions -ne "None" }

But SQLPS is part of the 2008 R2 Feature Pack or 2012+. So if you have a server like that you can run it remotely. However also this replication flag can be enabled for other reasons like CDC and so is usually a little misleading but in your case maybe it's just what you're after.

Related Question