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:

• Name – name of the Publisher.
• ConnectionContext – the ServerConnection from step 1.
• DistributionDatabase – the name of the database created in step 5.
• WorkingDirectory – the share used to access snapshot files.
• PublisherSecurity – the security mode used when connecting to the Publisher. WindowsAuthentication is recommended.

 

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