Partager via

Synchronizing a Transactional Replication Subscription From PowerShell

  • Article

A customer asked me the other day for a sample of synchronizing a Replication subscription from PowerShell.  The scenario is that they are running SQL Express on
disconnected systems, and need to kick of a sync from the client.  SQL Express doesn't have SQL Agent, so you need to have a custom program of some sort kick off
the sync.  You could use the command line agent for sync, by harvesting the command line from a SQL Agent Replication Job, but PowerShell gives you a lot more control,
without forcing you to go to a compiled application written in .NET.
 
Anyway here it is.  It's delivered as a PowerShell function, which is called inline in at the bottom of the file. Do-Replciation.ps1: 

$asm = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") $asm = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") $asm = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO") Function Do-Replication { <# .SYNOPSIS Initiate Merge Replication Sync .DESCRIPTION This Function kicks of a Transactional Replication Synchronization .EXAMPLE Give an example of how to use it .PARAMETER subscriber The SQL Instance Name name of the Publication, EG localhost .PARAMETER spublisher The SQL Instance Name name of the Publisher, eg MyPublisher .PARAMETER publication The name of the publication .PARAMETER subscriptionDatabase The name of the Subscriber Database .PARAMETER publicationDatabase The name of the publisher database .PARAMETER forceReInit $true to force a ReInitialization of the subscription, $false otherwise .PARAMETER verboseLevel Logging verbosity level .PARAMETER retries Number of times to retry the sync in case of a failure #> param ( [String] [parameter(Mandatory=$true, position=0)] $subscriber, [String] [parameter(Mandatory=$true, position=1)] $publisher, [String] [parameter(Mandatory=$true, position=2)] $publication, [String] [parameter(Mandatory=$true, position=3)] $subscriptionDatabase, [String] [parameter(Mandatory=$true, position=4)] $publicationDatabase, [Boolean] [parameter(Mandatory=$true, position=5)] $forceReInit, [Int32] [parameter(Mandatory=$true, position=6)] $verboseLevel, [Int32] [parameter(Mandatory=$true, position=7)] $retries ) "Subscriber: $subscriber"; "Publisher: $publisher"; "Publication: $publication"; "Publication Database: $publicationDatabase"; "Subscription Database: $subscriptionDatabase"; "ForceReInit: $forceReinit"; "VerboseLevel: $verboseLevel"; "Retries: $retries"; for($counter = 1; $counter -le $retries; $counter++) { "Subscriber $subscriber"; $serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $subscriber; try { $serverConnection.Connect(); $transPullSubscription = new-object Microsoft.SqlServer.Replication.TransPullSubscription; $transPullSubscription.ConnectionContext = $serverConnection; $transPullSubscription.DatabaseName = $subscriptionDatabase; $transPullSubscription.PublisherName = $publisher; $transPullSubscription.PublicationDBName = $publicationDatabase; $transPullSubscription.PublicationName = $publication; if ( $true -ne $transPullSubscription.LoadProperties() ) { throw New-Object System.ApplicationException "A subscription to [$publication] does not exist on [$subscriber]" } if ( $null -eq $transPullSubscription.PublisherSecurity ) { throw New-Object System.ApplicationException "There is insufficent metadata to synchronize the subscription. Recreate the subscription with the agent job or supply the required agent properties at run time."; } $transPullSubscription.SynchronizationAgent.Output = "c:\temp\ReplicationLog.txt"; $transPullSubscription.SynchronizationAgent.OutputVerboseLevel = $verboseLevel; if ($forceReInit -eq $true) { $transPullSubscription.Reinitialize(); } $transPullSubscription.SynchronizationAgent.Synchronize(); "Sync Complete"; return; } catch [Exception] { if ($counter -lt $retries) { $_.Exception.Message + ": " + $_.Exception.InnerException "Retry $counter"; continue; } else { return $_.Exception.Message + ": " + $_.Exception.InnerException } } } } Do-Replication -subscriber "DBROWNE0" "DBROWNE0" "inventory" "subscriberTest" "replTest" $false 1 4;