≡ 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:
Returns a ServerConnection object
Returns an RMO.ReplicationServer
Returns an RMO.LightPublication
Constructor for RMO.TransPublication
Constructor for RMO.MergePublication
Returns an RMO.SubscriberSubscription. Note: this is the only function executed on a subscriber
Returns either an RMO.TransPublication or RMO.MergePublication object
Returns an RMO.TransSubscription or RMO.MergeSubscription object from a Publication
Returns an RMO.TransArticle or RMO.MergeArticle object from a Publication
Returns an RMO.ReplicationMonitor
Returns an RMO.PublisherMonitor
Returns an RMO.PublicationMonitor
Calls the EnumPublications method on a PublisherMonitor object
Calls the EnumPublications method on a PublisherMonitor object
Calls the EnumSubscriptions method on a PublicationMonitor object
Calls the EnumSubscriptions2 method on a PublicationMonitor object
Calls the TransPendingCommandInfo method on a PublicationMonitor object
Calls the EnumLogReaderReader method on a PublicationMonitor object
Calls the EnumSnapshotAgent method on a PublicationMonitor object
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.
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: