Wednesday, March 28, 2012

RMO Replication problems with Pull Subscriptions

Hello,

I have the following situation. I have a single publication on my publisher Server. This publication is created using SQL Server Manager. Snapshot is created completly. Now I want several Pull subscriptions from several machines to work with this publication (One subscription per machine). I'm creating these subscription using RMO. I'm synchronizing data using RMO again. My code workflow is:
Syncronize -> success -> do nothing
-> fail -> Check if everything with Publisher and Publication is ok -> Generate Snapshot -> Create Subsscription -> Sync again. (I tried to eliminate Generate Snapshot step but couldn't because I receive error that I must rerun Snapshot Generation.)

When I tested my code per single machine it's working. Next test I tried was to run my program on 2 machines simultaneously.

The result is:

> System.Data.SqlClient.SqlException: Another snapshot agent for the subscription(s) is running or the server is working on a previous request by the same agent.

or:

> System.Data.SqlClient.SqlException: Unable to acquire the replication merge administrative application lock for database 'XXX'. This could be due an active snapshot running while the schema change (DDL) or the administrative proc change was attempted.
Replication merge admin stored procedure 'sp_changemergepublication' failed for publication 'YYY'. This could be due an active snapshot running while the admin proc was called.

The problem is obvious but because I'm new to the replication I'm not sure if I did general mistake in what I want to achieve. Any advices how can I fix my problem will be highly appreciated.Snapshot need not be run for every subscription. Eliminate that step from your list. Run it once, you should be good.|||Thank you for reply.

To be honest I thought something similar. In my first attempts I generated snapshot through SQL Server Manager once. But every time I ran synchronization after I received error that I must rerun Snapshot Generation. I can post the exact error tomorrow. The facts I can post now for this case are that publication properties: SnapshotAgentExists returns true, but SnapshotAvailable returns false.

Here is the exact error:

You must rerun snapshot because current snapshot files are obsolete.
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Initialize()
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Synchronize()|||Thank you a lot once again.
I did your way and now It seems it's working. I'm waiting for results of the extended testing tomorrow. I found the reason why I needed to rerun snapshot every time (Leaked code from first implementations where I needed to setup publisher and publication. The code modifies publication). So now snapshot generation is removed from code and my task looks complete.

No comments:

Post a Comment