I have AWS EC2s of AG (Availability Group) cluster instance of SQL Server on multiple AZ (Availability Zones). Reason not to use AWS RDS is due to multiplied cost of ownership which was veto'ed by management majority.
I am having connectivity issues when Application cluster and SQL primary active AG cluster are on different AZ.
Based on this answer in stackoverflow, there seems to be a benefit of setting this on: https://stackoverflow.com/a/50504027
Microsoft reference: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover?view=sql-server-2017#SupportAgMultiSubnetFailover
When I tried turning on the Multisubnet Failover, the ODBC's Test Connection is ok but there are some issues from Sage X3 side.
I could not find out how Sage X3 runtime does its own ODBC internal connection string.
Additionally, the ODBC settings in runtime server for runtime is always regenerated from scratch by Sage X3 Console.
When observed, I noticed that Multisubnet Failover option is always turned off.
My suggestion is set default, Multisubnet Failover is on, when Sage X3 Console generating the ODBC connection to better support possible SQL Server AG cluster instances.
Chun Heng Lee
