Urgent!! How to ImproveLOG Shipping performance in SQL Server 2005 ?

  • Hi All,

    In our production server, we have 2 databases of size 48GB and 80GB respectivelt.

    Server Configuration : SQL Server 2005 on windows 2003

    RAM : 8GB

    Number of Users Connect : 170+ evry time

    Its running for WEB Application - Secondary server is on Sharepoint server databases.

    Here according to our application we have configured LS on 2005 server keeping DB compatibility as 80 not 90.

    And after this we have scheduled COPY, BACKUP for 2hrs and RESTORE for every 4hrs respectively. By this also we are unable to improve the performance ! this was the question raised from CLIENT ?

    Can anyone help me out to proceed with the things, how can i come across this. Below are the issues we are facing some times for couple of days.

    Drawbacks : CPU Utilization reach 100% sometimes.

    RAM : memory usage exceeds top level.

    Now they need this server must be configured for Replication:

    I'd like some advise on how best to set this up. For instance:

    1) How do I go about delivering secondary updates back to the repaired

    primary?

    2) Should I use push or pull subscription?

    3) The database uses a number of triggers. How should I ensure that

    replication works correctly for these - is it just a matter of using the NOT

    FOR REPLICATION option? I've read a couple of posts indicating that this may

    not work properly in the case of updateable subscriptions.

    4) Will Transactional Replication works to improve the performance.

    5) Trans Repl

    Any assistance much appreciated. Apologies for the scope of the question,

    but I'm new to sql server replication and would like some expert advice to

    get me started.

    Thanks in advance.

    - Shekhar.C

  • cshekhar (2/26/2009)


    And after this we have scheduled COPY, BACKUP for 2hrs and RESTORE for every 4hrs respectively. By this also we are unable to improve the performance ! this was the question raised from CLIENT ?

    Log shipping has nothing to do with performance. Log shipping is for high availability, to have a warm standby server.

    If you're having performance problems, use profiler to find the most intensive queries running on your server and them see if you can tune them. Might be by adding indexes, might be by fixing code, probably both.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I aplogize for improper information.

    Users will be forcebly disconnected when the sec server is in restore mode.

    Here CLIENT needs to implement Replication on the same dbs what i have explained earlier. Aim is to replicate only tables. But my concern is, we have triggers for many tables and do they work properly when i use Transactional Replication.

    Kindly adivse me how can i proceed implementing replication. Sorry if i have missed anything this time.

    Thanks in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply