Hi, I would like to talk to you briefly. But if you already know a good white paper or walkthrough, it will very very welcome!
We do have a growing number of customers using both SQL Always On for SQL HA+ NLB for PI AF Application HA. Depending on the scale and the server availability, it can make sense. You are right though, you would need 2+ SQL Servers, 2+ AF Servers, and your NLB (software or hardware).
AF Installation and Upgrade guide contains the information.
Installation guide contains detailed procedure too.
Also, it mentions Microsoft&s article too. so it is good to check it too.
Always On Availability Groups (SQL Server) | Microsoft Docs
I have not configured Always on by myself but I know that AF Service is not possible to be installed on the "always on SQL machines".
So it requires a lot of machines to do a test.
Good luck if you will forward to configure it.
Thanks for the links Kenji. From the documentation the minimum numbers of servers for AF HA system are 3, AF app services server, SQL primary replica and the secondary. I think it is reasonable for a AF HA system. I'm not looking for a Network Load Balancing (NLB) solution which I think will incur in more server (maybe 2, a second AF app server and the load balance server). And I agree that AF services should not be installed on the AlwaysOn SQL servers, it would not be possible have a full HA system if the AF services reside in the same box of the SQL services.
This is basically the architecture that we are pursuing. We like to facilitate the patching process to have no downtime. Did you know any white paper to configure SQL HA with AlwaysOn? The AF setup is "pretty clear" in the docs but the SQL part is not that easy to follow.
When configure a AlwaysOn Availability Group, does the AF SQL setup configure the synchronization between the SQLs participating in the availability group? How the database keep synchronized with the same data?
Install PI AF in a SQL Server availability group has the details on how to set it up. The sync itself is all done on the SQL side, not by the application (AF).
If you use only one AF Service, then it will be a single fail point.
Though I know that NLB configuration can be achieved by more machines.
(For NLB, there is a windows Network Load Balancing feature, so NLB machine is not required for all the time. it can reduce 1 machine)
Network Load Balancing | Microsoft Docs
The other option currently is a SQL mirroring.
If you use SQL mirroring, you can install AF Service on the SQL Mirroring machine too.
Mirroring should use witness SQL for hot failover, but it is the other option for achieving AF HA with less number machines.
(However Microsoft will deprecate this future. So currently always on is the best way to go)
Deprecated Database Engine Features in SQL Server 2017 | Microsoft Docs
I think 4 machines are minimum required machine number for AlwaysOn AF HA full high availability.
SQL Primary, SQL Secondary, AF Service 1, AF Service 2 and windows NLB feature.
If enabling the NLB feature, you still need to be aware of its out-of-the-box limitations.
Since the load balancer feature is not application aware, it will require additional configuration to detect certain types of front-end failures (e.g. service stopped). Its basic functionality only looks if the machine is online/offline AFAIK. NLB itself does not have any way to monitor health of application. You can create your own component that does that and then use node weight to tell a node to stop taking traffic on a port.
Retrieving data ...