Avatar of Info Tech
Info Tech
Flag for United States of America asked on

SQL Server 2008 Merge Replication Expired - Cannot Reinitialize

I'm running a SQL Server 2008 R2 server with a publication set up for multiple clients running 2008 SQL Express R2.  One of the client subscriptions expired.

What is the easiest way to get this Merge replication going again?  

 I've attempted re-initializing the subscription which didn't help.  

I've deleted the subscription which removed itself from the server and client, then re-created which didn't work.  The subscription re-creates, but on the server side simply says "Not Initialized" when looking at the Replication Monitor.  The odd part is on the client it says successfully synchronized, but it never goes.  

I've completely deleted the database on the client. Then re-created the subscription and re-created the database.  No matter what I do, the server never leaves "Not Initialized" and when looking at the properties of the re-created subscription it says "Inactive".

Any advice / assistance would be greatly appreciated.
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Deepak Chauhan

8/22/2022 - Mon
Deepak Chauhan

what is the status of merge agent job any error there.
Info Tech

It's an On-Demand merge subscription so there is no merge agent job.  When I look at Replication Monitor, I have 5 clients that are all good because those didn't expire.  The one problem client says "Uninitialized Subscription".

No matter what I've tried I cannot get this client to merge replicate again.
Deepak Chauhan

Repl-Merge catagory job should be there, only schedule will be disable if it is On-Demand. Please expand the sql server agent job on publisher and look for like mention below and start this job if it is stopped.

This is SQL agent job naming convention.
<Publisher server name> - <Publication name> - <Subscriber server name> - <id like 123>
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Info Tech

Hmmm, I checked each job's properties and none of them are a REPL-Merge Category job.  I've attached an image of the jobs that are currently listed.  Is this why the expired client is unable to start up again?  

My only concern is the other clients all work fine, so I don't want to do something that causes them to break.  As I stated, the users run the synchronization on the client to sync up with the database.  Nothing is done on the primary server side of things.

Agent Jobs
Deepak Chauhan

Ok. I guess this is Publisher server snapshot and replication set up with pull subscription.

Now follow these step hope subscription will be sync. But as mentioned by you this is ondemand so it will be stop after sync.

1 double click on the unintialised subscription entry in the replication monitor.
2 click on the action button in new window opened after double click
3 click on start synchronise.
4 check the status.

As you said clients are SQL express may i know how other subscriptions are syncing.

Are you using Windows sync manager.
Info Tech

You are correct.  This is a snapshot based replication set up with a pull subscription.

The clients are using SQL Express and yes, we are using Windows Sync Manager to syncronize the laptops back to the server each night.  There are a total of 6 laptops.  5 of them work perfect.  However, this one particular laptop didn't check in within the 14 day expiration period.  

Now - when I open Replication Monitor - Click Action - Start Sync is not an option.  I've attached another screen shot here.  Repl Monitor
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Deepak Chauhan

Oh Sorry Your case is pull subscription and the above steps for Push subscription.

You have to sync with this command as express edition does not have SQL agent. So you can sync it using commandline or Windows sync manager.

Comand line code replace the <?> with correct parameter.

open the command prompt and type

Cd "C:\Program Files\Microsoft SQL Server\100\COM\"

then run the command.

REPLMERG.EXE" -Publication <?> -Publisher <?> -Subscriber <?> -Distributor <?> -PublisherDB <?>  -SubscriberDB <?> -PublisherSecurityMode 1 -outputVerboseLevel 2  -SubscriberSecurityMode 1  -SubscriptionType 1 -DistributorSecurityMode 1 -Validate 3  -ParallelUploadDownload 1

if you want to sync it with Windows Sync mgr , you have to enable the subscripton property at subscriber server.
Info Tech

How do I enable the Subscription Property at the Subscriber Server? The odd part is when I choose to Sync at the client using Windows Sync Manager - it says Successful.  But it isn't syncing anything.  

When I tried the command line above, it says "input string too long".

See attached screen shot. If I could just get this to say "Active" I think the sync would be successful again.

  Agent Properties
Deepak Chauhan

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Info Tech

That script did the trick!  Thanks!!!
Your help has saved me hundreds of hours of internet surfing.
Deepak Chauhan

Great! Good to know

Thanks for giving points