Here is an assumption I am making, given the expectation that, as far as we know, the size and load on the various dbs running as HA are "the same" for all of the dbs:
For a particular database, even though the Mirror is being "fed transactions" so that it can keep its mirrored db copy up-to-date, we can expect that, in general, the Primary is doing more work. At a minimum it is managing all of the work associated with queries (since we currently do not have a system in place to direct queries to another, R/O db copy).
1) Is this assumption correct? If so:
2) Does it make sense to implement a (crude) form of load balancing by spreading the dbs across the two servers? What this means is half of the dbs are launched on Server01 as Primary and the other half of the dbs are launched on Server02 as Primary. If that makes sense, then:
3) After a crash of one server (upon which all dbs are running on the other server) and the crashed server is restarted, how do we "move" half of the servers back to that (previously crashed) server?
asked 19 Apr, 19:11
See that other FAQ...
IMHO, your suggestions seem reasonable, altough for number one, only you can tell... - when the normal database activity is dominated by read-only queries, you should be right.
For option 3, there's the "preferred" option in the CREATE MIRROR SERVER statement, it should just do what you want, i.e. make the according partner the primary when the system is started and even after a failover (so another role-switch will be performed once the preferred partner is available again).
So in your case, you might choose to set the option "ON" for partner server 1 for half of the databases and for partner server 2 for the other half...
Apparently, as a HA system requires three different servers, you could also use the server used as arbiter for some HA setups to be a partner in other HA setups and vice versa.
In addition to Volker's answer, you should be aware that until v17 all of the transactions that are being shipped to the secondary/mirror server (for each database) are replayed on a single thread on the mirror. (see Database Mirroring bullet on the What's new in 17 Performance Enhancements page).
What this means is that if you are not using v17 then the mirror may actually be just as "busy" as the primary. The reason is that on the primary all of the transactions can be happening concurrently (in parallel with each other) but on the mirror all of the changes would be (until v17) applied one at a time on a single thread. This can cause the mirror to fall significantly behind the primary if the mirror is not appropriately configured (with enough memory, CPU power, and disk I/O speed).
So my recommendations would be:
After a server failure (in which case the primary may have migrated to one of the other servers) and a restart of the failed server you could then use a script to connect to the "current" primary of each database to verify that it is running on the "correct" (i.e. where you really want it to be) server. If it is not where you want it to be then use the ALTER DATABASE SET PARTNER FAILOVER statement to cause the primary to switch to the other server.
answered 20 Apr, 09:07
In addition, some thoughts from flyover country...
AFAIK the arbiter computer doesn't have to be "equally configured" but the two partners should be... the arbiter doesn't need much disk space, for example :)
Hot tip: Think "partner 1" and "partner 2", they are real things. Primary and mirror (or secondary) are transient roles.
Setting an HA partner to be the "preferred partner" doubles end user annoyance: after the first failover, the user must reconnect, and then when the failed preferred partner is restarted, the user is disconnected again.
IMO only folks who have never (yet) administered HA think "preferred" is a good idea... it's the same group who think "asynchronous" is a good idea :)
What you are proposing is far far far far far more complex than "classic HA", so... get it to work first, THEN worry about load balancing. To put it another way, if it doesn't have to work, load balancing will not be a problem.
Worry about backup and restore before load balancing, such as "how fast can I get the secondary up and running after restoring the primary?" ...or were you thinking HA is a substitute for backup and restore? Did I get you there? (snork :)
Be very careful about choosing between SQL Anywhere 16 and 17.
...ok, time to shut up now.
answered 20 Apr, 17:00