Merge Replication with Pull Subscriptions and Dynamic Snapshots - Synchronization Issues

Question

I have a Merge Publication setup on SQL Server 2005 and a subscription created on SQLExpress 2005. The publication has Dynamic Snapshots enabled.

After initializing the subscription. When I do a sync using "Windows Synchronization Manager", the windows agent creates dynamic shot files if they don't already exists and does the sync without any issues.

But when I try to use RMO to do the sync for the subscription, I'm getting this error:

"You cannot specify an alternate snapshot folder (-AltSnapshotFolder) or the partitioned snapshot folder name (-DynamicSnapshotLocation) when the publication has one or more articles that use non-overlapping partitions that are unique for each subscription (@partition_options = 3). Restart the Merge Agent without specifying these parameters."

Did any of you run into a similar issue? Also somewhat a related question, does calling Synchronize() create dynamic snapshot files if they don't already exist on the server. I have included the code in this post. Any help in this regard is greatly appreciated.
I'm using the code below to initiate the sync:

Thanks,
Ramesh

public int ProcessReplication() {
            MergePullSubscription subscription = new MergePullSubscription();
            
            // Create a connection to the Subscriber.
            ServerConnection subscriberConn = new ServerConnection(SubscriberName, SubscriberUserName, SubscriberPassword);        

            try {
                // Connect to the Subscriber.
                subscriberConn.Connect();
        
                // Define the pull subscription.
                subscription.ConnectionContext = subscriberConn;
                subscription.DatabaseName = SubscriptionDbName;
                
                subscription.PublisherName = PublisherName;
                subscription.PublicationDBName = PublicationDbName;
                subscription.PublicationName = PublicationName;
                
                subscription.SynchronizationAgent.PublisherSecurityMode = SecurityMode.Standard;
                subscription.SynchronizationAgent.PublisherLogin = PublisherUserName;
                subscription.SynchronizationAgent.PublisherPassword = PublisherPassword;

                subscription.SynchronizationAgent.Distributor = PublisherName;
                subscription.SynchronizationAgent.DistributorSecurityMode = SecurityMode.Standard;
                subscription.SynchronizationAgent.DistributorLogin = PublisherUserName;
                subscription.SynchronizationAgent.DistributorPassword = PublisherPassword;

                subscription.SynchronizationAgent.Status += new AgentCore.StatusEventHandler(SynchronizationAgent_Status);
                
                // If the pull subscription exists, then start the synchronization.
                if (subscription.LoadProperties()) {                    
                    
                    // Check that we have enough metadata to start the agent.
                    if (subscription.PublisherSecurity != null || subscription.DistributorSecurity != null) {                       

                        // Synchronously start the Merge Agent for the subscription.                                                                        
                        subscription.SynchronizationAgent.Synchronize();
                        return 0;
                    }
                    else {
                        ErrorMessage = "There is insufficent metadata to synchronize the subscription. Recreate the subscription with the agent job or supply the required agent properties at run time.";
                        return -97;
                    }
                }
                else {
                    // The pull subscription does not exist
                    ErrorMessage = String.Format("Properties could not be loaded, ensure a subscription to '{0}' exists on {1}", PublicationName, SubscriberName);
                    return -98;
                }
            }
            catch (Exception ex) {
                ErrorMessage = string.Format("The subscription could not be synchronized. Verify that the subscription has been defined correctly.{0}{1}", ex, Environment.NewLine);
                return 1;
            }
            finally {
                //During IDE sessions - often would not shut down 
                // This is an attempt to clear all Non-Managed code
                subscription.SynchronizationAgent.Status -= SynchronizationAgent_Status;
                subscription.SynchronizationAgent.Dispose();
                subscriberConn.Disconnect();
                subscriberConn = null;
                subscription = null;
            }

        }

 

---

Tumati

Answer 1

by any chance is there a alternatesnapshotfolder defined in your profile?

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

I set the alternate snapshot folder for the publication and subscription. Do I have to set the same for the agent profile?  I looked at the merge agent profile properties and I don't see the AltSnapshotFolder property there?

Thanks,
Ramesh
 

Answer 3

dynamic snapshots do not use the alternatesnapshotfolder. it uses the dynamicsnapshotlocation. But IIRC it ignores whatever you set as the alternatesnapshotfolder.

Hilary

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

When there are massive updates to the tables involved in replication, is there anyway I could clear the dynamic snapshot files along with reinitializing the subscriptions programatically. It's taking forever to download all the changes to the client with a regular sync.

And is this the right solution to speed up the sync?


Thanks,
Ramesh

Answer 5

reinitialization is your best bet.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

Hi ,

How do we troubleshot merge replication conflects?

Regards,

Vanchandar

---

chandar

Answer 7

that depends on what type of conflict you have. If it is an error you need to resolve the error, many errors can be avoided by using the not for replication switch. PK violations can be avoided by carefully using identity range seeds or a location identifier field which is part of your primary key.

Conflicts themselves can be investigates by using sp_showlineage and sp_showcolv

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 8

Hi Hilary,

Thank you for your replay.

We are facing the conflicts due to data updates from different sites.so how do we troubleshoot in this scenario.

Regards,

Vanchandar

 

---

chandar

Answer 9

HI,

We have 1 publisher and more then 50 subscribers and we have have configured merge replication based on HOST_NAME filter.

While synching we can able to see all subscriber updates data.

How do we prevent one subscriber updates  to other subscriber  while synching

Thank you

---

chandar

Answer 10

Do you have overlapping partitions? To investigate conflicts you should use the conflict viewer. You may need to dig more deeply using sp_browselineage.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 11

Hi Hilary,

Thank you for your replay.We have 1 publisher and more then 50 subscribers and we have have configured merge replication based on HOST_NAME filter.

we can able to see the updates of one subscriber data While synchronizing other subscriber .

How do we prevent one subscriber updates  to other subscribers ? I think we have to configure dynamic snapshot if so please let me know how do we configure dynamic snapshot?

Thank you

---

Vanchandar

---

chandar

Answer 12

It sounds like your filter is not set up to correctly partition your data. Can I see your filtering logic?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 13

Hi

Filter:

SELECT <published_columns> FROM [dbo].[tblAd] WHERE cast (
[CustomerId]  as varchar(50)) =HOST_NAME()

Thanks and Regards,Vanchan

---

chandar

Answer 14

I am waiting for your replay

---

chandar

Answer 15

Please respond to this thread.

Thanks in advance....

Regards,

Chandar

---

chandar