Unanswered: Transactional Replication and Performance
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?
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.
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.
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.
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.