Questions about "load balancing" in an HA system

[former_SQLA_member1694877]

Hello,

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?

Thank you.

[VolkerBarth]

Hot tip 3: It would help to tell what exact problem the HA setup should solve: 24x7 availability, disaster protection, local availability..., and whether the app will handle an immediate re-connect transparently for users...

[MarkCulp]

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:

• Use latest version of v17
• Use (at least) three equally configured computers to host your primary, secondary, and arbiter servers
• Spread the primary databases equally across the three (or more) computers, placing the associated mirror copies of each database also across the three, and then place the arbiter on the remaining computer.

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.

HTH

[VolkerBarth]

Ah, I wasn't aware of that v17 change either...

For the "After a server failure..." paragraph: Would the "preferred" option not do that autmatically?

[MarkCulp]

I think the preferred option would depend on the application. I.e. The issue with not redistributing the primary databases would be dependent on the work load. In AIK's situation he says that the mirror is not handling R/O connections so all of the work would be directed at a single server (the one hosting the primary databases). It would be better to redistribute the primaries evenly across all of the servers in this situation.

[VolkerBarth]

I had understood that AIK would try to distribute the HA databases over two (or three) servers (see his second point), and IMHO that could easily be done w.t.h. of that option (apparently "distributed" accordingly).

[VolkerBarth]

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.

[Breck_Carter]

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.

[Breck_Carter]

Hot Tip 2: Stop thinking "mirror server", start thinking "secondary". The docs are hopelessly confused about the term "mirror", how else can you describe the syntax "CREATE MIRROR SERVER ... AS MIRROR".

[VolkerBarth]

FWIW, my suggestion is related to the OP's other postings with the plan to support hundreds of (small) databases each in a HA setup, and possibly not always with user connections. In that respect, it might not be a problem if after a failover another role switch is made to "balance" the databases on the hosts. Of course, if some databases would have no connections, another role switch would not cost much. But possibly Mark's approach with a explicitly triggered re-balancing might be more appropriate than the automatic one based on the "preferred" option.

And yes, that's certainly a complex HA setup...