SQL Server Performance

Replication! Please help.

Discussion in 'Performance Tuning for SQL Server Replication' started by TALAT, Mar 21, 2004.

  1. TALAT New Member

    Hi Folks, I've run into serious problem. I've 4 GB of Publisher that i need to replicate to a subscriber over the internet using VPN. The Snapshot(Merge-Agent) has been running for last 8 hours and now it gives me the following error and has restarted from beginning.
    "The Subscription to Publication is Invalid, Check your Network Documentation".
    Damn!! The publisher and subscriber were intact throughout. Snapshot has now restarted and will need more 8 hrs.
    Any ideas of what has gone wrong?
    Also, Is there any settings in the agent-profile that i need to set to make the snapshot wait if the subscriber disconnects for a while during the snapshot rather that to start from the beginning, because the internet is not rather stable to keep the subscriber online all along. Please help!!!!!!!! It's been so frustrating.
  2. satya Moderator

    I suspect this error is not related to the publication or replication itself, but to connectivity issues. To test this is the case you could try connecting to the subscriber from qa on the distributor.

    Also run SP_HELPSERVER on both publisher & subscriber to make sure they return same network name and does not return NULL, if so you need to drop and add the servername to take new setting affect.
    (if SQL 7 is used then you need to refresh SQL services).

    As it clearly mention check network, take help from Network admin to findout any issues/bottlenect on Network during this approach.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. TALAT New Member

    Thanx Satya. Sp_helpserver doesn't return Null for either of the servers. However the Subscriber disconnects some times from VPN, for it's over the internet.
    Can one manage to retain the snapshot if the subscriber disconnects for a few mins? Why is it restarted from the beginning. What about agent-profiles "slow-linked" etc.
  4. satya Moderator

    If any of the replication process is disrupted then the LogAgent will restart the process and you should check on VPN for the cause of these disconnection & failures.

    In case of slow link network then try to apply the snapshot manually.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. TALAT New Member

    Satya, thanx again. Kindly guide about applying manual snapshot. This is what i am really lookin for. How do i do that? Thanx for ur patience and help.
  6. satya Moderator

  7. TALAT New Member

    Thank You! So kind u'v been so far. I've enabled dynamic filters, created a dynamic-snapshot-job and run it. It's mentioned in the documentation to use pull-subscription at the subscriber and specify the folder-path(for dynamic snapshot) and mention that it's a dynamic-snapshot. Since i am using VPN with no trust-relation b/w subscriber and publisher, shall i mention the dynamic snapshot location like "\servernamesnapshofile.cab"?
    What if i want a push-subscription; i couldn't find any one of these options i.e., folder path for dynamic snapshot location etc. Means that dynamic snapshot won't work for Push-Subscription? If i run the merge agent on publisher with push subscription; it uses normal snapshot files rather than the dynamic. Plz guide.
    Thanx for ur patience. I would be really greatful if u square this away for me.
  8. satya Moderator

    In any case mentioning with UNC name for a share is better approach and in your case its inevitable.
    Make sure you'd gone thru material listed in books online about push/merge replication process and managing dynamic snapshots.

    Follow as suggested and feel free to come back if in doubt.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. TALAT New Member

    Hi Satya, some how it worked, i.e. the Merge-Agent started and applied the tables scripts very quickly, i could see the PUBLISHER-INSERTS with 100 batch-size. Now in the middle of this process the Subscriber disconnected; i restarted and immediately the Merge-Agent reported Success as if the SNAPSHOT is done. But the subscriber contains zero records in the published tables. ???? It has got messed up. Can i put the snapshot folder on the subscriber and then apply the snapshot from locally at the subscriber? What would be the consequences?
  10. satya Moderator

    Using PROFILER you can get to know the details where its failed and also take network admin help for any information on network for this disconnect behaviour.

    Is this component failing recently?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. TALAT New Member

    Yes, Satya, the subscriber disconnects recently and the dynamic snapshot i guess if the subscriber disconnects; merge agent says it's succeeded and then starts the normal transactions(b/w publisher and subscriber).
  12. satya Moderator

    THen try to verify any recent changes on network which may causing issue of disconnection.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page