Hi, we are using right now SQLA 10 for 3 databases, all running on one engine and on one box, each HA component is separated on its own box (primary, arbiter, mirror), works fine but we need to place each DB on its own box. (performance and license reasons)
Boxes 1,2,3,5 are planning to be the same hardware, (mirror doesnt need to be superfast).
Question 1> Almost each of 3 primary dbsrv is heavily used, thats the reason of separating them to separate boxes, how will the performance get affected by Box5, where all the DBs will be mirrored ? There is really just a small chance of a failure of primary, so I understand that in case of failure i.e. Box1, the performance of dbsrv_1 after failover to Box5 will be affected by other mirror engines, there's doubt about. But in case where each primary is running correctly on Boxes1-3, will be Box5 fast enough to handle all the transactions/changes of primary DBs ?
Question 2> How are the transactions performed in behind when using HA mode? Is it A: 2phase commit (change writes to both sides at one time) or is it B: tran commits on primary db, and after successfull write on primary it performs the same change and commits on mirror db (something like rep server)
Thanks a lot in advance
I can't comment on the performance considerations, but as to the "commit mode" - there's the "database mirroring mode" with three options:
AFAIK, the default synchronous mode does not work really in 2PC mode (transactions are committed locally before they have been negotiated with the mirror) but it works similar: The primary waits until the according translog pages have been transmitted to the mirror and written to its trangslog.
The other modes offer a looser coupling (and therefore better performance) at the risk of losing committed transaction in case of a failover.
More discussion on this topic can be found in this HA question.
answered 18 Apr '11, 07:42
To answer your question 1, you have to understand the change transaction volume on your primary servers, as opposed to the query volume. Are your servers running a mixed workload behind an interactive application, where there is a healthy mix of queries, followed by a few change (INSERT, UPDATE, DELETE) transactions? OR, are your servers primarily change transactions, perhaps fed by an automated process? These are the questions you have to answer to understand if your server5 can keep up.
Have a look at the current configuration, is your mirror server under heavy load, or is only your primary under heavy load? My assumption is, that as you are already looking for 3 separate primaries, that the primaries have a higher load than the current mirror. Anyway your server 5 will have the exact load of your current mirror. So if it is ok now it will be also ok in your planned configuration.
answered 18 Apr '11, 12:29