1

For a new project we want to test pull replication in mssql to replicate database (transactional replication) to multiple db server. I setup the pull replication by wizard in MSSMS and but the replication stucks on uninitialized subscription in the replication monitor.

Steps I have done:

  • Created a publication on publisher by wizard (Local Publications > New Publication...).
  • Created a subscription by wizard (Local Subscriptions > New Subscriptions...)

Publisher and distributor are on the same host. Subscriber uses SQLExpress. Authentication is done by sql authentication. SQL user used for authentication is dbo on Distributor DB.

What else is needed to initialize the subscription?

1
  • I also found this "There's no SQL agent on express edition, so no automatic setup of the agent jobs. You'll have to either run Windows tasks to get them running, or use a push subscription." So if thats true, how to use Windows tasks to initialize the subscription?
    – userk23r
    Jul 17 '20 at 11:05
0

If you want to Pull from a SQL Agent, you'll have to schedule and execute the replication job with a custom program or script. Here's an old sample of doing it in PowerShell:

$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 Do-Replication -subscriber "DBROWNE0" "DBROWNE0" "inventory" "subscriberTest" "replTest" $false 1 4;
   .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 }
    }
  }
}
0

The solution was to use initialize from backup option and to import a database dump from the publisher on the subscriber.

  1. Enable initialize from backup option in publication
  2. Create database dump from publisher
  3. Recover database dump on subscriber
  4. On publisher create subscription by T-SQL
    USE [yourpublishedDB]
    EXEC sp_addsubscription
    -- name of publication
    @publication = MyPub,
    -- IP Subscriber
    @subscriber = 'MyServer\SQLEXPRESS',
    -- Subscriber database
    @destination_db = replicDB,
    @sync_type = 'initialize with backup',
    @backupdevicetype = 'disk',
    -- dump on publisher imported on subscriber
    @backupdevicename = 'C:\publisherdump.bak'
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.