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.

Seems like you need to load `Microsoft.SqlServer.Replication.dll` first? Also, note that according to my research the `Rmo` namespace / api is deprecated. — marsze, Oct 09 '20 at 20:04
Have you load assembly with [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Rmo') | Out-Null OR Add-Type -AssemblyName "Microsoft.SqlServer.Rmo, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop — Sanpas, Oct 09 '20 at 20:47
Hi i think you have to remove your comment present in $scriptargs this break multiline variable " #one way tried to get the subscriptions" — Sanpas, Oct 09 '20 at 20:56
@marsze Depricated...hmm? Will research a bit more on that. It helps, if you can provide the link where you saw it. — sagar pant, Oct 09 '20 at 21:07
@Sanpas ignore if you see #comments. It is just there to help the viewers since I couldn't bold the line I didnt use loadwithparticalname because the servers I am testing against were producing errors. So, I used LoadFrom and copied from outside and provided Rmo.dll in a custom location. — sagar pant, Oct 09 '20 at 21:10
@sagarpant yes it's just for test other i'm trying to help but other think : use script with S caps [string] $subs=$tranpub.TransSubscriptions.Script($scriptargs) — Sanpas, Oct 09 '20 at 21:13

PowerShellGuy · Answer 1 · 2020-10-09T22:25:52.263

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.

Thanks but I think I have used the reference you mentioned( FYI, sqlserver is 2017). I have edited my original question to show how I have used the reference. Of all options, I have shown in the question like IncludeArticles, IncludeGo etc, I get error only when I add IncludePublisherSideSubscriptions. Question is, it works for one option but not for the other. — sagar pant, Oct 10 '20 at 12:18

Issue with scripting out subscriptions using Windows PowerShell

1 Answers1