0

Update:

Basic answer: I asked for something that isn't part of the design of AOAG.

Which in my eyes makes this a 'not a good question to ask'. Unless this offends someone, I'll keep it here in the odd case someone has this same bad idea as I had.


Excuse the terminology, as I'm not a DBA.

I am consulting on a project that will most likely need between 700-900 of databases. The software can't utilize multiple SQL instances, and must host all DBs on the same SQL instance name.

The client's DBA explained why hosting more than a few hundred DBs on AlwaysOn Availability Groups is probably going to be unreasonable, and official Microsoft documentation, while it does not state an upper limit, it does state that MS only tests with 100 databases.
AlwaysOn Failover Clusters is not an option, as the data is shared and not replicated.

My understanding was that an AOAG is addressable as a SQL instance, and that I will not be able to share the instance name across multiple AOAG.

Someone mentioned to me that it might be possible to do exactly that - add an additional AG and have those additional DBs under the same SQL Instance name, but I am unable to find a description of this option with my searches.

Either I am searching for information on this with the wrong terminology, or my assumption that an AG is 1:1 with SQL instances or maybe even 1:m, but not multiple AGs per single instance.

Update

terminology I (think I) understand:

  • Host: Server running one or more SQL Server instances.
  • Instance: A copy of SQL Server process running on a host, accessible by an Instance name.
  • Instance name: logical address of the instance, composed by a hostname\instance
  • database - a container of data within an instance.

Relationships without AOAG:

Host --1:n--> Instance --1:n--> database

Where I'm getting lost:

(please correct me if I'm wrong here)

  • AG - a group of SQL Server Instances along with additional services to manage logical addressing that host data that is replicated across multiple hosts, these are addressable under a single logical Instance name.

Relationships? with AOAG:

AO Cluster (logical name AO\All)
  +-- AG
        +-- Host A
        |     +-- Instance One
        |           +-- db1
        |           +-- db2
        +-- Host B
              +-- Instance One (replica of A\Instance One)
                    +-- db1
                    +-- db2

dbs on Host A/Instance One or Host B/Instance One are accessible via AO\All served by AlwaysOn

The question:

Is it possible to have multiple Availability Groups in AlwaysOn span databases in a single DB instance? If not, do you have a suggestion of how to effectively host so many DBs with Always On? (other than 'it should work' like here)

In other words, is this a possible architecture? And will it allow me to serve/host more dbs (by doubling the hardware?)

AO Cluster (logical name AO\All)
  +-- AG-1 (as specified above)
  |     +-- Host A
  |     |     +-- Instance One
  |     |           +-- db1
  |     |           +-- db2
  |     +-- Host B
  |           +-- Instance One (replica of A\Instance One)
  |                 +-- db1
  |                 +-- db2
  +-- AG-2 (as specified above)
        +-- Host C
        |     +-- Instance Two
        |           +-- db3
        |           +-- db4
        +-- Host D
              +-- Instance Two (replica of C\Instance Two)
                    +-- db3
                    +-- db4

With db3 and db4 accessible with the same logical instance name (AO\All) just like db1 and db2.

6
  • It's a little unclear how AlwaysOn Availability Groups come into play here. I understand X (you have hundreds of databases you need to keep on one SQL Server instance) and you mention Z (the reasonable limitations of AlwaysOn Availability Groups) but the question is Y do you want to use AlwaysOn Availability Groups?...are the 900 databases currently living on different instances and you're trying to replicate them to the single instance you need to centralize then on? AlwaysOn Availability Groups is a technology for replicating the same database across multiple SQL Server instances.
    – J.D.
    Dec 7, 2021 at 23:08
  • The DBs will be added, gradually in the coming months. My question is whether there is an architecture that will allow me to have multiple AGs server different set of DBs so that a logical AO cluster will present/access the DBs as part of the same instance. I've update my question with some visual queues.
    – Lockszmith
    Dec 8, 2021 at 0:44
  • 1
    After re-reading your post, I contemplated when you said "The software can't utilize multiple SQL instances...". Do you mean the software you're consulting for can only reference one SQL Server instance at a time?...and you're looking for a way to implement database redundancy, which is why you're asking about AlwaysOn Availability Groups, but you need to work around the software limitation? If that's the case, then AGs can accomplish your goals, specifically the Listener is what you can point the software to, to do so. It'll have one name and route connections to the correct instance.
    – J.D.
    Dec 8, 2021 at 5:31
  • 1
    An AG cannot span over several instances. The databases that are included in an AG all lived on one instance, and those are replicated (by the AG functionality) to one or more different instance(s). Those different instance(s) need to live on different hosts (machine). (I.e., you can't have an AG where the replicas resides on diferent instances on the same host.) Dec 8, 2021 at 9:41
  • 1
    @J.D. you are correct, and I see your solution ('workaround'?) - that actually might work. Thanks.
    – Lockszmith
    Dec 8, 2021 at 17:00

2 Answers 2

5

The client's DBA explained why hosting more than a few hundred DBs on AlwaysOn Availability Groups is probably going to be unreasonable [...]

That's correct, although if you have the hardware for it then it could work, but in most cases with databases that are all active at the same time, having hundreds to thousands of database is going to be a disaster.

[...] official Microsoft documentation, while it does not state an upper limit, it does state that MS only tests with 100 databases.

That was a specific test on specific hardware and is not indicative of a general performance level, maximums, or minimums. It's simply a data point that 100 databases with the given load in the test will work well on the hardware in the test. This means your hardware will be different, the amount of cpus, memory, etc., and thus might not even be able to run with 100. There is no maximum number (outside of the number of databases for SQL Server which is a hard cap) and the only way to know is test your specific setup (both infrastructure and workload) to know where the upper limit exists. This may also change over time as users, workload, etc., are added to the system and is not static.

My understanding was that an AOAG is addressable as a SQL instance, and that I will not be able to share the instance name across multiple AOAG.

I'm not sure I fully understand the statement here, but an instance (executable of SQL Server running) can have one or more AGs. Those AGs can span one or more instances.

Someone mentioned to me that it might be possible to do exactly that - add an additional AG and have those additional DBs under the same SQL Instance name, but I am unable to find a description of this option with my searches.

It is possible to have multiple AGs, however a database can only exist in a single AG. So an instance hosts AGs, an AG is a logical container for a database. That logical container (the AG) moves all resources within it (databases) when it "moves" to any other instance that could host the AG. All databases in the AG logical container must all be together and cannot be split, as in you can't pick and choose which databases in an AG go to each replica, it's all.

Either I am searching for information on this with the wrong terminology, or my assumption that an AG is 1:1 with SQL instances or maybe even 1:m, but not multiple AGs per single instance.

Depends on how you look at it, but an instance can host 1:m AGs. An AG span across 1:8 instances.

Is it possible to have multiple Availability Groups in AlwaysOn span databases in a single DB instance?

An instance is not a database, an instance hosts databases. AGs are a logical container of database resources that all "go together" wherever they are asked to go (whichever isntance of SQL Server is allowed to host them and the databases are physically available). Thus a single database in an AG will have an exact copy of itself on all other instances the AG spans.

You cannot, however, take multiple databases across multiple AGs and make them all look like a single instance. They would all need to be hosted on a single instance for that to work (which is possible), however you're then back to your original issue of "that's too many databases for the hardware to handle".

4
  • I understand the difference between instance and db. I'll edit my question to explain the relationship better. But I think from your answer that it's just a terminology issue for me
    – Lockszmith
    Dec 8, 2021 at 0:18
  • I've update my question
    – Lockszmith
    Dec 8, 2021 at 0:44
  • 1
    @Lockszmith one instance can't span multiple servers, in your diagram above "instance one" cannot live on two seperate servers, they would be their own instances. The only caveat to that is an FCI. What you're looking to do is not possible with SQL Server where multiple instances or AGs all act and look as one instance. Dec 8, 2021 at 14:21
  • 1
    Thank you so much for all the detail and clarification - I was missing the clarity you provided.
    – Lockszmith
    Dec 8, 2021 at 16:53
3

If not, do you have a suggestion of how to effectively host so many DBs with Always On?

Technically, there are Always On Availability Groups (AGs) and Always On Failover Cluster Instances (FCIs).

And an FCI will work fine with large numbers of databases. You need shared storage with a Shared disk, SMB3 Share, or a Storage Spaces Direct replicated disk.

and

In other words, is this a possible architecture?

Yes. You could scale across multiple servers and have each AG only on a subset of the nodes, eg with 3 AGs and 6 cluster nodes:

    N1  N2  N3 N4 N5 N6
AG1  X   X
AG2          X  X
AG3                X  X

And each AG would have 1/3 of the databases. Or with 3 nodes

    N1  N2  N3 
AG1  X   X
AG2      X   X
AG3  X       X  

Where each node hosts the primary replicas for one AG and the secondary replicas for one AG.

But this is an awfully complicated and expensive alternative to using an solution that scales better to large numbers of databases, like FCI, VM replication, storage replication, or simply live with the HA your hypervisor gives a VM.

2
  • On my first read I believe I didn't understand what you were explaining. Now I have questions: focusing on the first option you diagrammed - each of the AG, are they part of a "single cluster" - would accessing it be with the same instance name?
    – Lockszmith
    Dec 8, 2021 at 19:25
  • 1
    Each AG has its own network address, called an Availability Group Listener which you would use in the connection string. eg AG1.MyDomain.com, AG2.MyDomain.com, etc. Dec 8, 2021 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.