SQL Server AAGs/Possible Endpoint contention/Synchronous replication to WAN Secondary In the interest of saving some SQL Server licensing moolah ($$$), we’re going down the path of extreme consolidation. We’re trying to shoehorn as many databases as possible into a SQL Server 2016 Always On Availability Group “Cluster” which, the way I see it is really one active server at a time with the data replicated to other standby secondaries. To me, the word “cluster” (as Microsoft SQL Server does it) is more of a marketing term and a dangerous one at that because it gives the impression of a big bad server when it is not. 

So on this new environment we have, among other things, a default instance of SQL Server hosting about 20 AAGs, and a second instance to host Sharepoint as well. Overall a total of potentially 70 databases in these AAGs (including classic hits like MS SCCM which uses service broker and is highly transactional+did I mention, Sharepoint) on a 4 core SQL Server VM w/72GB of RAM. One of the things I noticed is that each AAG communicates with a single HADR endpoint on the destination instance so you end up with 20 AAGs communicating with a SINGLE endpoint. 

Will all of those AAGs sending log data to a single endpoint (single tcpip port) create a point of contention?

The design was to use synchronous replication EVEN to the remote site which I immediately saw as a potential problem but I was told that there have been significant improvements in SQL Server 2016 specific to AAG replication. Your thoughts here will be appreciated as well.

Will all of this work? Is it fair for me to wear a Nostradamus hat and predict a potential Cluster-Mesa or should I play “Let it Be” like the Beatles and keep on trucking...
Few things that I learned from having a similar setup with my old company. We had 53 AOAGs with many DBs in each AG.


TCP Endpoint will not be a contention. (remember that you can have only one endpoint per server instance).Even with our setup having MPLS network (NY and LD) and a dedicated VPN between (NY and HK), we ran the above setup and we were fine.
with such amount of DBs, your availability group DMV queries might be slow. So make sure you use temp tables.
Since you are running SQL Server 2016, Always On Availability Groups are Turbocharged along with better AlwaysOn Parallel Compression / Improved Algorithms
Make sure you offload read work by utilizing Round-robin load balancing in readable secondaries.
Make sure you are on latest 2016 SP2 service pack.


As always, do some benchmark and testing and have a baseline to compare. This will give you confidence in presenting to management that the consolidation project indeed helped !