What is the overhead of a "large" number of databases all started on an "HA server"?

[former_SQLA_member1694877]

Hello,

What is the overhead involved in having a large number of databases all started on an HA server but only a small number of them actually "in use" at any time? That is, we expect to have a "large" number (relative to the max of 255) of databases all "started" on a server in an "HA system". However, at any point in time we expect that there will only be a "small" number that are actively is use (processing SQL commands from clients). Is there a material overhead having the other databases started but not in use? (We don't know which will be in use at any time so having all of them started and "ready to go" is the desired state.)

Thank you.

[VolkerBarth]

What is the overhead involved in having ...

Well, I can't tell that but apparently you want to make sure performance is fine for all your databases and the possible connections. I guess that's difficult to tell for someone else, not knowing your system and requirements. Do all the databases fit into your system's cache? Or will there be a race condition for cached pages?

If one goal is to provide quick responses even for the first connection to each database, you may think about cache warming in general and v17's new feature of "cache warming to a steady state", here both as discussed in this forum.

What would be your alternative? Not starting databases until they are used? If so, then I guess you would need to track how long that does take (and how you can do that at all in a HA setup - quite tricky, methinks...)

[VolkerBarth]

FWIW, are you aware of the SQL Anywhere On Demand product? (Although I don't know whether this is actively promoted, see that FAQ...)

[former_SQLA_member1694877]

Hi Volker,

I am having to support a support a cloud based service that supports "many" customers (hence the "large" number of dbs) any one of which may access the system at any time, but is not expected that everyone is using the system all the time. If possible, I want to avoid having to detect that a db is needed and then START it; as you suggest, in an HA context I'm not sure what that would involve. What I want to determine with this question is whether or not having 255 dbs (worst case - the max possible) all STARTed but only a few actually in use at any point in time is a "really poor" design. My sense from your comment is that it may not be an issue, assuming of course that the system has enough RAM to support that. With all dbs STARTed, having "a few seconds" delay for the servers to "activate" a (already STARTed) db (if such a delay even happens) upon the 1st access "after a period of non-use" (an hour or more?) would be no problem. Thanks for pointing me to the cache links; I'll take a look at them. Do you have any other comments?

[former_SQLA_member1694877]

Funny you bring that up. Last year I had done a bunch of research on On-Demand and even download and installed the system. Had to be diverted from that for a while and when I came back to it early this year Product Management for On-Demand told me that, in effect, it had been discontinued. Existing licenses would be supported but there would be no new licenses. That's why I am now having to "piece" together "my own version" with the SQLA HA features. Hence all of my current (and upcoming!) questions here.

[VolkerBarth]

Ok, then you know the actual answer to the cited question by Breck...

[Breck_Carter]

When you say "HA" are you specifically referring to the SQL Anywhere "database mirroring" feature documented here?

The answer may be obvious, unless the answer is "no" 🙂

[former_SQLA_member1694877]

Yes, HA = High Availability which, based on some web pages, seems to be the "other way" to refer to the "database mirroring" facility. Please see my 04/09 Replay above for the background on my reason for asking about this. I am very curious about "The answer may be obvious, unless the answer is "no"" since my answer is yes!

Thanks.

[VolkerBarth]

AIK, we do not really know the requirements of your application (except that you had planned to use the OnDemand Edition and seem to need to support hundreds of separate (and small?) databases)...

Have you considered using an OS-based HA setup, say via VMs running in a "mirrored" host setup, as offered by VmWare vSphere or MS Hyper-V (and certainly others)? - I'm thinking of features to run SQL Anywhere in a virtualized OS that can be "moved" between several physical hosts, so the "mirror"/HA feature would be supplied by the OS and not by SQL Anywhere itself and would allow some kind of load-balancing. (I'm no sysadmin at all, so my description might use wrong/unusual terms, but I hope you get the point...)

Just saying, apparently:)

[Breck_Carter]

If your answer had been "no", that would have meant "HA" was NOT referring to "database mirroring"...

[Breck_Carter]

Personally, I am glad the On Demand product is fading away. It was almost completely opaque from an administrator's point of view, extremely difficult to set up and use... and those were just my first impressions 🙂

Ironically, it was the extreme emphasis on lights-out ease-of-operation that crippled On Demand by turning it into a reincarnation of Microsoft Bob.

At the same time, I am glad the On Demand product was created because it brought with it much-need improvements to HA in SQL Anywhere 16 (since robust HA and scale-out were prerequisites for On Demand).

[end rant]

[former_SQLA_member1694880]

On Demand has done its duty, On Demand can go :).

[Breck_Carter]

It's off topic... BUT this is the best On Demand meme ever :)...