Powershell Day 25

Day 25 RMO

 

I have observed that no body has written anything about RMO. And replications is one of my favoriate topic, so tried to write this blog.

Please DO NOT TRY THIS SCRIPT ON PRODUCTION, I AM NOT RESPONSIBLE OF ANY CURRUPTION.

I worked on transaction replication so here is the Powershell script to create the transaction replication with PUSH subscriber.

In this scenario I am using publisher and subscriber on the same box so no need to create a connection object for subscriber server.

For more information about replication please see my blogs under “replication” Category.

Create Distribution Database

To configure publishing and distribution on a single server

  1. Create a connection to the server by using the ServerConnection class.

$servername=”ServerName”

$srv = New-Object “Microsoft.SqlServer.Management.Common.ServerConnection” $servername

$srv.connect()

  1. Create an instance of the ReplicationServer class. Pass the ServerConnection from step 1.

$distributor = New-Object “Microsoft.SqlServer.Replication.ReplicationServer” $srv

  1. Create an instance of the DistributionDatabase class.

$dist_db= New-Object “Microsoft.SqlServer.Replication.DistributionDatabase” “distribution”,$srv

  1. Set the Name property to the database name and set the ConnectionContext property to the ServerConnection from step 1.

 

  1. Install the Distributor by calling the InstallDistributor method. Pass the DistributionDatabase object from step 3.

$distributor.InstallDistributor($srv, distributionDb)

  1. Create an instance of the DistributionPublisher class.

“Microsoft.SqlServer.Replication.DistributionPublisher”

       $publisher = New-object “Microsoft.SqlServer.Replication.DistributionPublisher” (“L-108326-b\SQLSERVER2008SE”, $srv)

  1. Set the following properties of DistributionPublisher:

 

publisher.DistributionDatabase = distributionDb.Name

publisher = New DistributionPublisher(publisherName, conn)

    publisher.DistributionDatabase = distributionDb.Name

    publisher.WorkingDirectory = “\\” + publisherName + “\repldata”

    publisher.PublisherSecurity.WindowsAuthentication = True

    publisher.Create()

  1. Call the Create method.

Configure Publisher

 

publicationDb = New ReplicationDatabase(publicationDbName, conn)

$publicationDb =new-object “Microsoft.SqlServer.Replication.ReplicationDatabase” (“Vinay”, $srv)

$publicationDb. .EnabledTransPublishing =1

PS C:\> $publicationDB.LogReaderAgentPublisherSecurity.WindowsAuthentication=1

PS C:\> $publicationDB.CreateLogReaderAgent()

PS C:\> $publication=new-object “Microsoft.SqlServer.Replication.TransPublication”

PS C:\> $publication.ConnectionContext=$srv

PS C:\> $publication.DatabaseName=”Vinay”

PS C:\> $publication.Type

Transactional

PS C:\> $publication.Status

Active

PS C:\> $publication.SnapshotGenerationAgentPublisherSecurity.WindowsAuthentication=1

PS C:\> $publication.IsExistingObject

PS C:\> $publication.Name=”VinayP”

PS C:\> $publication.Create()

 

Assign Articles

PS C:\> $article =new-object “Microsoft.SqlServer.Replication.TransArticle”

PS C:\> $article.ConnectionContext=$srv

PS C:\> $article.Name=”i”

PS C:\> $article.DatabaseName=”Vinay”

PS C:\> $article.SourceObjectName=”i”

PS C:\> $article.PublicationName=”VinayP”

PS C:\> $article.Type

LogBased

PS C:\> $article.SchemaOption

PrimaryObject

PS C:\> $article.IsExistingObject

False

PS C:\> $article.Create()

#**********************************************************************

#Configure PUSH Subscription

$subscription =new-object “Microsoft.SqlServer.Replication.TransSubscription”

PS C:\> $subscription =new-object “Microsoft.SqlServer.Replication.TransSubscription”

PS C:\> $subscription.ConnectionContext=$srv

PS C:\> $subscription.SubscriberName=”Servername “

PS C:\> $subscription.PublicationName=”VinayP”

PS C:\> $subscription.DatabaseName=”Vinay”

PS C:\> $subscription.SubscriptionDBName=”Vinay_sub”

PS C:\> $subscription.AgentSchedule.FrequencyType

Continuously

PS C:\> $subscription.AgentSchedule.FrequencyType=”onDemand”

PS C:\> $subscription.AgentSchedule.FrequencyType

OnDemand

PS C:\> $subscription.Create()

PS C:\>

Reference:

http://sqlblogcasts.com/blogs/antxxxx/archive/2011/04/04/scripting-replication-with-powershell.aspx

RMO Namespace :

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.aspx

                    http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.scriptoptions.aspx

* will blog more on this later ….

Hope this will help someone… who are interested to learn more on RMO

About these ads
This entry was posted in Replication and tagged . Bookmark the permalink.

2 Responses to Powershell Day 25

  1. Eric says:

    Thanks for this. I used your code as a guide to develop an automated replication topology deployment to production.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s