Multiple Availability groups under same IP

Is it possible to have 4 Availability Groups distributed on 3 servers all of them under the same IP?

For example I would like the users to connect to DATABASE01 on SQLSTACK01 no matter if the Database is in the server 1 or server 3.

Thanks!

In theory, each Availability Group needs its own IP address (and virtual network name) for the listener. This way, you can fail the primary around between places - after all, you could have one AG that's a primary on server1, while you fail a separate one of the AGs over to server2 because it's having corruption problems.

In practice, you can use Availability Groups without a listener, and just have your apps connect to a specific SQL Server, bypassing the listener (and IP requirements) altogether. However, this means that:

• Managing failover is completely up to you

• Every time you have a failover, you have to touch every one of your app's connection strings because the server name will change (or you have to use a DNS CNAME)

• You can't have some of the AGs be primary on one server, and some on another (in other words, all 4 of the primary writeable workloads have to be on the same server)

• You can't use read-only routing - if you want to run read-only workloads on the secondaries, your apps have to connect to those other servers by name

It's a lot of drawbacks, but it IS doable. (StackOverflow was running this way for a while, not sure if they still are, because we had so many problems with the AG listeners.)

StackExchange Database Administrators Q#148820, answer score: 5