SQL Pull replication stops on unintialized subscription

Question

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?

Answer 1

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 }
    }
  }
}

Answer 2

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'