SQL Server Performance

Replication Help

Discussion in 'Performance Tuning for SQL Server Replication' started by nilayinc, May 22, 2003.

  1. nilayinc New Member

    Hello,

    I am very new to Replication. I have just started reading replication because I think it can solve my problem.
    I am developing an application for client which has online as well as offline version. Online version would have SQL2K and offline application would have MSDE as database. Offline application is a stand alone application and would connect over VPN. Whenever offline application connects via VPN, synchronization should happen between both the databases. Offline application contains only part of data.

    Comparatively my rules are preety simple. If the data is updated in offline application, then it cannot be updated in online application and vice-versa.

    On overall basis, replication seems to be the option. But I have coouple of question in order to come to decision.
    1. There would be many instances of offline application and so it can happen that many offline application connects to server simulateneously for Synchronization of data. And each instance contains different data regarding on the user. (So might require replication which accepts parameter). Not sure if it is possible with Replication.
    2. I am not sure what would be the performance (time required for) of replication?
    3. Does the table gets locked while replication?

    Thanks for all your help.

    Regards,
    Nilay.
  2. bradmcgehee New Member

    My gut feeling is that replication is not a good choice for you. Replication is limited in what it can do well, and is subject to many problems, especially if you want to attach and detach an MSDE database to it from time to time.

    Instead (and this will be a more time-consuming option) I think you need to write your own replication code. This will make the replication process much more seamless and easier to maintain. It will also be much easier from the user's perspective.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. vbkenya New Member

    While agreeing with brad....and just to answer you questions:

    (1)Parametrized subscribers: Possible with both snapshot and transactional replication. You will have to set up the partitions for each of the offline databases using suitable criteria.
    (2)Performance: This depends on many things. Check out this site for general tips
    http://www.sql-server-performance.com/replication_tuning.asp]

    (3)Locking: Yes, it occurs during replication. You may notice deadlocks between the log reader and the distribution process.
  4. nilayinc New Member

    Can u guide me in how to write my own replication code?
    Should I go for DTS and create file for each user and when user connects transfer the file and update? Or something else.

    Some help in this regard would be highly appreciated.

Share This Page