≡ Menu

SQLPSX Release 1.3

I completed Release 1.3 of SQLPSX which adds 21 new functions for working with SQL Server Replication via RMO. With this release there are now 59 total functions and 10 scripts around SMO, Agent, RMO.
Here’s a few example of working with SQL Server Replication through RMO:

Get a reference to a Replication Server. Note: server may or may not participate in replication several properties will indicate:
$replServer = Get-Replserver ‘Z002Sql1’

Get all of  Subscriptions on a subscriber server. Note: Unlike the other functions that are executed on the publisher, this is the only
function which should be executed on the subscriber:
Get-ReplSubscriberSubscription $replServer

Get all Publications:
Get-ReplPublication $replServer

Get all registered Subscriptions of all Publications
Get-ReplPublication $replServer | Get-ReplSubscription

Get all Articles of all Publications:
Get-ReplPublication $replServer | Get-ReplArticle

Monitoring examples that show the same type of information available in Microsoft’s GUI Replication Monitor utility
$replMon = Get-ReplMonitor ‘Z002Sql1’
$publisherMon = Get-ReplPublisherMonitor ‘Z002Sqlqa1
$pubMon = Get-ReplPublicationMonitor ‘Z002Sql1’
$pubMon | Get-ReplTransPendingCommandInfo
$publisherMon | Get-ReplEnumPublications
$publisherMon | Get-ReplEnumPublications2
$pubMon | Get-ReplEnumSubscriptions
$pubMon | Get-ReplEnumSubscriptions2
$pubMon | Get-ReplenumlogReaderAgent
$pubMon | Get-ReplenumSnapshotAgent

Script out Replication Server, Publication, Subscription and Articles:
$replServer | Get-Replscript
Get-Replpublication ‘Z002Sql1’ | Get-ReplScript
Get-Replpublication ‘Z002Sql1’ | Get-ReplSubscription | Get-ReplScript
Get-Replpublication ‘Z002Sql1’ | Get-ReplArticle | Get-ReplScript

The complete list of new functions added in the 1.3 Release:
Get-SqlConnection
Returns a ServerConnection object
Get-ReplServer
Returns an RMO.ReplicationServer
Get-ReplLightPublication
Returns an RMO.LightPublication
New-ReplTransPublication
Constructor for RMO.TransPublication
New-ReplMergePublication
Constructor for RMO.MergePublication
Get-ReplSubscriberSubscription
Returns an RMO.SubscriberSubscription. Note: this is the only function executed on a subscriber
Get-ReplPublication
Returns either an RMO.TransPublication or RMO.MergePublication object
Get-ReplSubscription
Returns an RMO.TransSubscription or RMO.MergeSubscription object from a Publication
Get-ReplArticle
Returns an RMO.TransArticle or RMO.MergeArticle object from a Publication
Get-ReplMonitor
Returns an RMO.ReplicationMonitor
Get-ReplPublisherMonitor
Returns an RMO.PublisherMonitor
Get-ReplPublicationMonitor
Returns an RMO.PublicationMonitor
Get-ReplEnumPublications
Calls the EnumPublications method on a PublisherMonitor object
Get-ReplEnumPublications2
Calls the EnumPublications method on a PublisherMonitor object
Get-ReplEnumSubscriptions
Calls the EnumSubscriptions method on a PublicationMonitor object
Get-ReplEnumSubscriptions2
Calls the EnumSubscriptions2 method on a PublicationMonitor object
Get-ReplTransPendingCommandInfo
Calls the TransPendingCommandInfo method on a PublicationMonitor object
Get-ReplEnumLogReaderAgent
Calls the EnumLogReaderReader method on a PublicationMonitor object
Get-ReplEnumSnapshotAgent
Calls the EnumSnapshotAgent method on a PublicationMonitor object
Set-ReplScriptOptions
Sets the Enum ScriptOptions for scripting RMO objects. Unlike SMO which has a default script options
RMO at at a minimum CREATION enum must be specified.
Get-ReplScript
Calls Script Method on RMO objects include ReplicationServer, Publication, Subscription and Articles
Just as in the 1.2 released focused on SQL Agent I choose to put the Replication related functions into a separate Library file, LibraryRMO.ps1. I did this because the RMO related objects are in the Management.SqlServer.Replication namespace instead of the Smo namespace, so it made sense to use separate Library file. You’ll need to source the additional Library file to load function definitions. With Release 1.3 complete, I’m starting work on the 1.4 Release which will add functions for working with Integration Services.
I noted in the post on the 1.2 Release my goal is to to have two more releases completed by the end of this calendar year (2008). Other priorites have meant I’ve spent less time working on SQLPSX and given that December is half over, February or March 2009 is more likely.

Comments on this entry are closed.

  • sreenath June 21, 2013, 7:23 am

    Using the module, how do I sync subscription server with publication server on one time basis ? The requirement for us is we have taken backup of publication database and we need to sync with subscription database after all mundane configuration.

    We want this to be automated as the sync process normally takes about 2-3 hours to complete. Any help regarding this would be appreciated. Thanks for making this great module.

    • Chad Miller June 21, 2013, 7:15 pm

      The replication module doesn’t have any functions for creating or updating replication. You may want to ask the question in a SQL Server forum.

%d bloggers like this: