Post reply

Performance Tuning on Transactional Replication in SQL SERVER 2005

  • February 4, 2008 at 11:16 pm

    #186194

    Hi Guys,

    I am started the Transactional Replication process with one subscriber and multiple publishers,Now i am facing the performance problems while replication.

    How can i do the Performance tuning on Transactional replication,Can any one help me on this?

    Thanks in advance,

    S.Ram

  • February 4, 2008 at 11:26 pm

    #774989

    Could you give more details on the problem please?

    Is the log reader slow in picking up transactions? Are the transactions taking time to get from the distributor to the subscriber? Do you have blocking on the publisher? On the distributor? On the subscriber?

    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

  • February 5, 2008 at 12:33 am

    #774999

    Can you please give the solution for the 2 scenarios?

    Thanx in advance:)

  • February 5, 2008 at 1:29 am

    #775010

    sram24_mca (2/5/2008)

    Can you please give the solution for the 2 scenarios?

    Thanx in advance:)

    What 2 scenarios?

    Can you explain where you're seeing performance problems please?

    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

  • February 5, 2008 at 1:36 am

    #775012

    Scenario 1:If the Log reader is slow

    Scenario2 :If the Transaction taking long time from Distributor to Subscriber

    Thanx in advance 🙂

  • February 5, 2008 at 4:44 am

    #775050

    Hey,

    To mitigate a slow log reader, you may want improve I/O of the underlying disk, by adding more disks, updating raid level to either Raid 1 or Raid 10. Most importantly, if you can ensure the disk supporting the transaction log has a dedicated SCSI or FC link to storage, this will help improve performance.

    To mitigate latency between distributor and subscriber, you may want to monitor network performance, use a tracer token and understand what is slow in the service chain.

    Here's a good starting point:

    http://technet.microsoft.com/en-us/library/ms151762.aspx

    Thanks,

    Phillip Cox

  • February 5, 2008 at 5:36 am

    #775076

    Thanks for your valuable information Philip.

  • February 5, 2008 at 6:46 am

    #775104

    Hai,

    I am using sqlserver2000 standard edition. The database size is 20gb. I am implementing transaction replication. While configuring i am not able to replicate views and table without primary key.

    I need all objects of the database to be replicated. Can u help me out .

    Thanks

  • February 5, 2008 at 11:00 pm

    #775426

    You'd probably get more replies if you started a new thread in the correct forum (SQL 2000 -> Replication, seeing as you're using SQL 2000)

    What about views do you want replicated? iirc, you can replicate the definition of the view, but seeing as views don't of themselves contain data, you can't replicate the contents. Indexed viwes may be different.

    For a table to be replicated, either transactional or merge, there must be a way to uniquely identify a row. Therefor all tables that are replicated must have primary keys. The only way around that is to do snapshot replication of those tables (completely copy the entire table)

    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

  • February 5, 2008 at 11:28 pm

    #775432

    Hai

    Thanks Gail for the reply. In my setup there are objects with primary key and some objects without primary key. But the data will be appended in all tables if a transaction happen.

    My business requirement badly needs a transaction replication. IF i proceed with snapshopt the synchronisation between primary and secondary will not be immediate.

    Or else if i create a primary key in all objects i think i can do a tran replication.

    can u pls suggest

  • February 5, 2008 at 11:32 pm

    #775435

    If you need to do transactional replication, then you'll have to ensure there are primary keys on all tables you want to replicate.

    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

  • February 8, 2008 at 7:31 am

    #776423

    basic dogma is that all db's should conform to 3NF and have PK's to reflect content

    - could be either natural key (red,blue,green,..) or synthetic (IDENTITY 1,2,3..)

    in the absence of natural PK you run the risk of duplicate records red,blue,green,red

    - so it may occur to you so have a Unique Index to prevent this [if so escalate to PK!]

    if your application/db design has no obvious natural PK, a simple approach is to add an IDENTITY column

    -designating this the PK will satisfy replication that will happily transaction-repl [real-time]

    but there is STRONG PREFERENCE on getting your design into 3NF without such embellishments!

  • February 11, 2008 at 12:12 am

    #776804

    Thanks for the explanation..

Viewing 13 posts - 1 through 12 (of 12 total)

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