March 16, 2010 at 1:15 pm
I am currently researching some High Availability options for a new environment we are setting up. I'd like to know if anyone has had experience in replicating a database (for reporting purposes) that is either clustered or mirrored for HA.
I found one Microsoft article relating to replicating a mirrored environment but none on replicating a clustered environment.
Has anyone else had experience with this or could someone help point me to documentation regarding this? Also, if you've had experience replicating either HA solution, what do you like/dislike about your solution. I'd especially like to know how you set up the publisher and how replication resumes after a cluster failover (the article I linked addresses part of this issue with mirroring).
Thank-you in advance!
March 17, 2010 at 11:31 am
A good tech Document can be found here
I have set up Mirroring with Transactional Replication. Its very good for what it does. I would recommend you set this up in a test environment and test every possible scenario and the behavior of SQL in this case. i.e mirror down, principal down
One important thing to note is that if the mirror goes down then the replication also starts queuing at the distributor so you have a latency issue. This makes sense as the subscriber cannot get the new data before the mirror gets the new data. If that happened then you run the risk of the subscriber having more data than the publisher so this transactional ordering has to be maintained. (You can override this by disabling a trace in SQL 2008 but I would not recommend it!).
So if the mirror goes down for a prolonged period of time you have to make the decision wether to break the replication to allow your transactions to continue to replicate.
Also remember that you still have a single point of failure for the distributor
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply