January 28, 2011 at 9:34 am
I have 3 instances of SQL 2008 SP1 Ent (instance A, B, and C).
I want to leverage SQL mirroring. Ideally I would mirror one database on A (make it the principal) and one database on B as the principal and have both mirrors residing on SQL Instance C.
This is strictly for reporting - the plan is to mirror the two databases (from different SQL instances) onto a separate instance (C) and leverage database snapshots.
Has anyone tried this?
January 31, 2011 at 4:32 am
Just curios, what is the mode you are planning to configure.
M&M
February 1, 2011 at 1:46 am
What is purpose of this kind of approach if the server goes down none of the database will be available.
Regards,
Subbu
Click here to Get Speedy answer or solution
February 1, 2011 at 2:39 pm
subbu_e (2/1/2011)
What is purpose of this kind of approach if the server goes down none of the database will be available.
If which server goes down? If anyone one goes down, the others are still available. If both A and B goes down, C might become overloaded though, so keep that in mind, especially if C is also handling reporting queries.
If I understand the OP's setup, he's going to have server C be the mirror server for both A and B and will then take snapshots of the mirrored servers on server C to use for reporting. I've never done this, but I believe it would work. You'd probably need to make sure you use different mirroring port numbers for the Server A and C pair and the Server B and C pair. I would also make sure at least server C is a 64 bit machine - you don't mention how many databases you will be mirroring, but each one takes a couple threads and with a 32 bit machine, you can run low on threads after a few dbs. Best practices also states your file paths should be the same on the mirror and principal, so you might run into issues there is the paths are different on servers A and B because then they couldn't both be the same on C.
February 1, 2011 at 11:20 pm
Yes, this is fine. If you want automatic failover, you can have B be the witness for A to C and A be the witness for B to C. If you choose to mix roles like this, be sure to make the endpoints' roles to ALL instead of PARTNER or WITNESS.
I've even done scenarios where all three servers serve in all roles. For example: A mirrored to B with C as witness, A mirrored to C with B as witness, B mirrored to A with C as witness, B mirrored to C with A as witness, C mirrored to A with B as witness, and C mirrored to B with A as witness.
February 2, 2011 at 10:16 am
Robert Davis (2/1/2011)
Yes, this is fine. If you want automatic failover, you can have B be the witness for A to C and A be the witness for B to C. If you choose to mix roles like this, be sure to make the endpoints' roles to ALL instead of PARTNER or WITNESS.I've even done scenarios where all three servers serve in all roles. For example: A mirrored to B with C as witness, A mirrored to C with B as witness, B mirrored to A with C as witness, B mirrored to C with A as witness, C mirrored to A with B as witness, and C mirrored to B with A as witness.
Thank you Robert for sharing your inputs, very useful stuff :-). I will try this some time.
M&M
February 9, 2011 at 7:05 am
Hi Everyone -
Thanks for the responses... Much appreciated.
I plan to give this is try in our testing environment.
I'll let you know how it goes.
Thanks again.
Matt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply