Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38

    Unanswered: Transactional Replication and Performance

    Hi all,

    I am keen to hear peoples perspectives on how much additional load Transactional Replication will have on a server.
    Obviously this will depend greatly on the level of transactions in the database, but a general indication would be great (eg 10% increase in overheads).

    I am thinking of encorperating this into a new server structure which we are going to be setting up and am unsure as to whether to make the primary server BOTH the publisher and distributor; or make the secondary server the distributor to reduce the load on the primary to only being the publisher.

    Basically the secondary server will simply be a 'hot swap' of the primary - so i/o load on the secondary is not going to be an issue.
    There may be 2 primary's (if that makes sense) replicating to the hot swap so that if either primary is dropped the hot swap could take over either servers load/responsibilities - not sure if this will make a difference on where to put the roles?

    Anyone have any thoughts on this?

    Thanks in advance.

    Cheers
    Last edited by E3xtc; 03-09-04 at 16:47.
    Sideways see's more scenery

  2. #2
    Join Date
    Mar 2004
    Posts
    45
    Transactional replication is not the optimal method for implementing high availability. Ideal would be clustering but if a SAN is too expensive then consider log shipping.

    Hans.

  3. #3
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38

    Smile

    Thanks for the response - I was looking into log shipping also - however read somewhere that it is a feature of Enterprise Edition only, and currently we are running on Standard.
    Although I have a document which talks about how to setup log shipping and it doesn't go into needing enterprise and seems to use features which are available in standard - so a little unsure about this really?
    A SAN isn't an option in this scenario.

    Keen to hear any other ideas on where you think I should be heading though.

    Cheers
    Sideways see's more scenery

  4. #4
    Join Date
    Mar 2004
    Posts
    12
    You might want to consider merge replication vs. transactional.

    If I understand your planned architecture, you want to have 2 Primarys, both replicating to 1 secondary. If one or both of the Primarys fails, then your clients can connect to the secondary (that's a bigger problem and why clustering is the preferred solution). However, when you bring the failed Primary back online, you'll need to get the data modifications that were made while the secondary was the Active server from the secondary back to the primary. Merge replication can do this type of bi-directional replication, thus keeping both databases in sync.
    Nick

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Log shipping is only automated for Enterprise Edition, you can easily manually configure it on Standard Edition.

    Merge replication is quick and easy to set up, and it is my choice as the most resilient of the replication topographies. You can bounce servers around quickly and easily with little fuss, muss, or bother. As long as you don't have concurrent updates (two different servers updating the same data before it has a chance to replicate), there is no manual intervention required.

    Straight Transactional replication can be done, but it is one way (publisher to subscriber), so you run into problems if you want to support updates in more than one place at a time. This is a great topography if you want to split out a "reporting" server and an "interface" server to ease the load on your publisher.

    The load from Merge replication is very small, it probably averages about 2% and maxes out at 5%. The load from Transactional is significantly smaller than the load from Merge replication. If you are trying to support redundancy on the cheap, I think replication is a good way to go.

    Log shipping is even lower load than replication, but when it goes "toes skyward" there is little automated warning, and it can do really bad things to your server.

    -PatP

  6. #6
    Join Date
    Mar 2004
    Posts
    45
    You can implement log shipping without the wizards and SPs that are included for this in SQL Server 2000 EE. Microsoft wants you to buy EE of course. See these articles:

    http://support.microsoft.com/?id=275146
    http://www.chriskempster.com/data/SQ...ailability.pdf
    Last edited by HansVE; 03-09-04 at 17:49.

  7. #7
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38

    Thumbs up

    Thanks guys - I don't like the sounds of the fun which Log shipping can run into.
    But I think I might have to do some more reading up of merge replication - as this sounds quite nice.
    However I think in the event of the secondary needing to be used (which would be the only situation where the data would be modified on the secondary) - it would be easy enough once the primary is rebuilt simply to restore the database off the secondary onto the primary to get the ball rolling again with the updated data....unless I am sadly mistaken - which wouldn't surpise me at all.

    Your feedback on this is much appreciated guys!
    Sideways see's more scenery

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •