SQL Server Performance

transactional replication

Discussion in 'Performance Tuning for SQL Server Replication' started by vulcanoro, May 18, 2004.

  1. vulcanoro New Member



    I use a transactional replication from publisher to subscriber. The connection between
    publisher and subscriber is very poor(bad). The agent is replicating data until i get un error "general network error....". I used a profiler on the subscriber and see that the agent is writing data on the subscriber until i get the above error on the publisher then
    the subscriber execute a rollback command. In this situation the data will never replicate.
    It's possible that the agent could make commit more often on the subscriber when is replicating the data?
  2. satya Moderator

    Until unless you resolve the issues of general network error between the 2 machines, you cannot deploy any other trick to enable the replication between them.

    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. vulcanoro New Member

    quote:Originally posted by satya

    Until unless you resolve the issues of general network error between the 2 machines, you cannot deploy any other trick to enable the replication between them.

    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.

    I can resolve this problem because the conection between the publisher and the subscriber is an internet connection ( the provider can resolve this) that is bad. If i replicate only a few commands it works well. When i try to replicate 5 transaction with 100000 commands it doesn't work.
    Any help is appreciated.
  4. satya Moderator

  5. vulcanoro New Member

    quote:Originally posted by satya

    Until unless you resolve the issues of general network error between the 2 machines, you cannot deploy any other trick to enable the replication between them.

    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.


    I can't resolve the connetion problem because is an internet connection. If i replicate only a few commands it works well. When i try to replicate 5 transaction with 100000 commands it doesn't work.
    Any help is appreciated.
  6. satya Moderator

    Check the links specified above in order to gain the performance in the replication.


    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.
  7. vulcanoro New Member

    quote:Originally posted by satya

    Check the links specified above in order to gain the performance in the replication.


    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.

    I checked your link but didn't help me because the indication i found there are implemented in the company i work.
    I discibed you the problem in another way.
    When i replicate 60000 transaction with 75000 commands it works well because at the subscriber after each transaction it makes a COMMIT. So, if the network is interrupted after 10000 transaction it ok because those transaction are commited on the subscriber. Next time the agent starts it will replicate the next 50000 transaction; and so on.
    If i replicate 1 transaction with 500000 commands it doesn't work because of the connection. The agent replicates 40000 commands after that the network is interrupted and
    the rollback tran is executed on the subsriber because no transaction was replicated only commands were replicated.
    I set the commitbatchsize of the agent to 1, but commitbatchsize it refers to transactions not to commands.
    It seems the only solution is a snapshot.

    Thank you satya for your suport.
  8. satya Moderator

    Glad to know the workaround and thanks for the note of mention.

    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