SQL Server Performance

which is best replication or log shipping

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Mahmoud_H, Nov 18, 2008.

  1. Mahmoud_H New Member

    I have OLTP central database sql standard 2005 that is currently working within LAN.
    It is required that new three remote branch sites will connect to the SQL server and do update/insert/delete and reporting.
    The four sites are connected with frame relay WAN.
    The main site is 512 kbps and has the database Server , and the other new remote sites are 256 kbps.
    The size of database is 250 MB , and the reporting in branch sites (huge reports) may cause high traffic over WAN and performance may suffer.

    I need to implement one of two solutions for reporting:
    solution 1) using replication
    the main site will be publisher and the other thre remote sites will be subscribers
    OR solution 2) using log shipping
    I have couple of questions:
    what is the best stable solution?
    what is the side effect in case of WAN is down, which is better to re-synchronize the database?
    In case of replication , what is the best replication to be used.?
    please advice
  2. MohammedU New Member

    Log shipping can't be used because whenever you restore the log, all users should be out of the db...it is not recommended...
    Replication is the only solution if i am not mistaken...what type of replication depends on subscribers...
    You can use transactional replication...or check peer to peer replication you the subscribers also modifying the data...
  3. ghemant Moderator

    [quote user="Mahmoud_H"]
    It is required that new three remote branch sites will connect to the SQL server and do update/insert/delete and reporting.
    [/quote]
    IMO Snapshot replication and logshipping both are ruled out as you would required your subscribers to update/insert/delete records. The options that are viable according to me are:
    1. Transaction Replication with updatable subscriber http://msdn.microsoft.com/en-us/library/ms151718(SQL.90).aspx
    2. Merge Replication http://msdn.microsoft.com/en-us/library/ms172367(SQL.90).aspx
    3. Peer-to-Peer Replication http://msdn.microsoft.com/en-us/library/ms151196.aspx
    This is what my opinion is, lets wait for others reply.
  4. Mahmoud_H New Member

    thank all for reply
    The remote sites use 256 Kbps , so which replication is less overhead for WAN.

    Is ther a reasonable speed for link between main and remote sites
  5. Elisabeth Redei New Member

    Hi Mahmoud,
    Your only option is replication - nothing else (from the techniques delivered out of the box) will allow you to update at the remote branch sites.
    Even if your branch offices were local I would recommend you to replicate to other servers for reporting - simply because OLTP and reporting activities don't mix well performance wise.
    You have to choose between Transactional and Merge Replication (peer to peer is only available in Enterprise Edition):
    - Both of them allow you to have bi-directional replication, i.e. data can be updated and replicated between Publisher and Subscribers.
    - Both of them can "queue" replicated data in case your WAN is down
    So which one to choose? Generally, Transactional replication is used in server to server replication and Merge Replication in "loosely coupled" scenarios (the typical example is Sales people with laptops).
    General rules for choosing Transactional Replication:
    - You want incremental changes to be propagated to Subscribers as they occur.
    - You need transactions to adhere to ACID properties (merge replication does not replicate transactions; it replicates commands)
    - Subscribers are reliably and/or frequently connected to the Publisher.
    Merge Replication:
    - Multiple Subscribers need to update data at various times and propagate those changes to the Publisher and to other Subscribers.
    - Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
    - You do not expect many conflicts when data is updated at multiple sites (because the data is filtered into partitions and then published to different Subscribers or because of the uses of your application). However, if conflicts do occur, violations of ACID properties are acceptable.
    Another important thing is that with Transactional replication, you can only solve conflicts in 2 ways; either the Publisher always wins or the Subscriber always wins (there is a 3rd, reinitialization but that is usually not feasible). In Merge Replication you have a range of different Conflict resolvers you can choose from.
    Depending on what your transactions look like, you might get less traffic with Merge Replication because you can choose column-level tracking. With column-level tracking, only the updated columns are replicated not the entire row. This is not going to help you if you have mainly deletes and inserts or typically update several or most columns.
    HTH
    /Elisabeth
  6. satya Moderator

    Say whether it is log shipping or replication the network bandwidth is most important factor for such environment on WAN especially. So you need to be careful in order to perform baseline of number of transactions that are happening within the databases that needs to be involved in this standby operations.
    So in your case such operations by testing is very much helpful to perform between the sites and also the firewall tests as it is another overhead that might hurt the performance .
  7. Mahmoud_H New Member

    Thank you Elisabeth for your greet advice and details
    [quote user="Elisabeth Redei"]
    Depending on what your transactions look like, you might get less traffic with Merge Replication because you can choose column-level tracking. With column-level tracking, only the updated columns are replicated not the entire row. This is not going to help you if you have mainly deletes and inserts or typically update several or most columns.
    [/quote]
    I think i should use Merge replication bez it match my case.

    Thank you satya for your advice
    [quote user="satya"]
    the network bandwidth is most important factor for such environment on WAN especially. So you need to be careful in order to perform baseline of number of transactions that are happening within the databases that needs to be involved in this standby operations.
    [/quote]
    I agree with you for bandwidth and firewall overhead

    Again,
    Thank you all who shared their knowledge and experience

Share This Page