Issue with scripting out subscriptions using Windows PowerShell

Question

I am trying to script out the replication objects via PowerShell using Microsoft.SqlServer.Rmo.dll. The replication type is transactional with push subscriptions.

I have been able to script out publication, articles, PALs but not able to script out publisher side subscriptions.

Reference

[reflection.assembly]::LoadFrom("c:\\sql\\Microsoft.SqlServer.Rmo.dll") | out-null

ScriptOptions

$scriptargs =[Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeCreateLogreaderAgent `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeCreateMergeAgent `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeCreateQueuereaderAgent `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludePublicationAccesses `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeArticles `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludePublisherSideSubscriptions`   #one way tried to get the subscriptions
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeGo

foreach($replicateddatabase in $repsvr.ReplicationDatabases)
{
    if ($replicateddatabase.TransPublications.Count -gt 0)
    {
        foreach($tranpub in $replicateddatabase.TransPublications)
        {
            **[string] $myscript=$tranpub.script($scriptargs)**   #Errors out here
            writetofile $myscript $filename 0
        }
    }
}

The other way I tried is exclude IncludePublisherSideSubscriptions from Scriptoptions and tried to script out directly using the following statement

foreach($replicateddatabase in $repsvr.ReplicationDatabases)
{
    if ($replicateddatabase.TransPublications.Count -gt 0)
    {
        foreach($tranpub in $replicateddatabase.TransPublications)
        { 
            [string] $subs=$tranpub.TransSubscriptions.script($scriptargs)   #another way but same error
            writetofile $subs $filename 0
        }
    }
}

The third way I tried:

$repsvr.ReplicationDatabases.TransPublications.TransSubscriptions.Script($scriptargs)

Of all, I found the following link to be very helpful where my code is mostly based on but just got stuck in the scripting out of publisher side subscriptions. I appreciate your help.

Answer 1

Microsoft.SqlServer.Replication has been deprecated since SQL Server 2012 (I think), and as of SQL Server 2017, it's no longer available. Follow the link and try switching the version to 2017 or 2019.

If you're using server 2016 and below, you may just need to load the rmo assembly from the GAC (Global Assembly Cache), this will do that for you.

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

If for whatever reason, you don't want to, or can't use the GAC, do this

Add-Type -Path 'C:\path\to\dll\Microsoft.SqlServer.Rmo.dll'

I'll dig, and see if there's a different way to go about doing what you're trying to do.

Edit: Not sure if I'm misunderstanding the docs about RMO, or if they're just out of date

you take this link and it shows you how to configure publishing & distribution using RMO on Server 2019. Step 2 references this which shows the ReplicationServer class. The problem is, that's for SQL Server 2016. I'm not entirely sure if this is supported past 2016 or not.