SQL Server Performance

Bandwidth Question

Discussion in 'Performance Tuning for SQL Server Replication' started by photura, Mar 16, 2006.

  1. photura New Member

    Hello.

    We have two data centers and I want to replication data between them for disaster recovery purposes. However, I am being asked to supply bandwidth / connection recommendations.

    Are there any standards or recommendations?

    Thanks.
  2. Twan New Member

    Hi ya,

    No I'm not aware of any standards/recommendations... It would depend a lot on:
    what form of replication
    how up to date the databases need to be with each other
    how much data changes, and how frequently does it change
    how large the databases are
    how easily you need to be able to transfer control from one data-centre to the other.
    how automatic the failover process needs to be
    etc, etc.

    e.g.
    if failover needs to be automatic and data needs to be 100% up to date, then you'd need to consider forming a geographically dispersed cluster
    where as if there are not many changes and the databases are ok to be out of synch by one day, then you could consider transferring database backups overnight

    Cheers
    Twan
  3. satya Moderator

    True, use replication for data consistency and not for high availability.

    You can consider either cluster or log shipping in this case. One of the recommendation we have had from MS support about high availability is:
    Network Bandwidth between the Local and Remote sites

    Bandwidth between the sites is generally the most crucial factor affecting the replication component of a solution.

    * T1 (1.5Mb) links impose less frequent data replication and the use of asynchronous replication methods.

    * T3 (45Mb) links or a 1Gb links allow frequent replication and the flexibility to choose between synchronous replication or asynchronous replication methods.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. photura New Member

    We currently have an Active/Passive Cluster setup within the same data center.

    I initially recommended log shipping to the remote data center but the Network Admin shot it down complaining they tried that in the past and had severe performance issues with it. I'm new at this company (one week) so I don't know what he's talking about.

    I've never experienced performance issues with Log Shipping, have you?

    The remote data center is only being used for our developers right now and I'd like to add this site into the disaster recovery plan.

    We are experiencing severe performance issues with slow running queries so I was going to off-load any reporting to a separate server and either link them or use Merge Replication between the two servers.

    Your thoughts on shipping data to the remote site and replicating reporting data between transaction and reporting would be greatly appreciated.



    quote:Originally posted by satya

    True, use replication for data consistency and not for high availability.

    You can consider either cluster or log shipping in this case. One of the recommendation we have had from MS support about high availability is:
    Network Bandwidth between the Local and Remote sites

    Bandwidth between the sites is generally the most crucial factor affecting the replication component of a solution.

    * T1 (1.5Mb) links impose less frequent data replication and the use of asynchronous replication methods.

    * T3 (45Mb) links or a 1Gb links allow frequent replication and the flexibility to choose between synchronous replication or asynchronous replication methods.

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

    Performance issues with slow running queries must be dealt as a seperate call and why should log shipping or replication is blamed.

    Take help of following KBAs and articles to resolve performance issues:

    http://support.microsoft.com/?id=298475
    http://support.microsoft.com/?id=224587
    http://www.sql-server-performance.com/performance_introduction.asp
    http://www.sql-server-performance.com/stored_procedures.asp
    http://vyaskn.tripod.com/analyzing_profiler_output.htm
    http://www.aspfaq.com/show.asp?id=2513

    Also perform the investigations during the replication process and high availability must not interfere with the performance, if it is so then I blame on bad code or lack of defragmentation practices on database indexes.

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

Share This Page