Can a SSO (SiteMinder) Session Store use SQL AlwaysOn to failover without having to restart the Policy Server Services? Currently, if I failover between SQL nodes, I lose Session Store connectivity.
The way I understand this feature, this is internal to MS SQL and a feature which provides high availability & failover capabilities within MS SQL.
Always On Availability Groups (SQL Server) | Microsoft Docs
At the end of the day, CA SSO is a Client requesting response from a ODBC database. Thus I believe this rule comes into play.
availability group listenerA server name to which clients can connect in order to access a database in a primary or secondary replica of an Always On availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica.
I would say take a look at the "availability group listener" details (IP Address, Port Numbers, Credentials) and replace that in CA SSO systemodbc.ini & smconsole. Then test it out.
There is no statement I can think of which says CA SSO has been tested against a MS SQL architecture which has been implemented "Always on".
But my view is as long as a Client is making a ODBC request to listener and the listener responds with the same response (as an independant SQL Server would); functionally this should work.
I'd also recommend a load test to flush out any hidden anomalies (e.g. crashes, leaks etc); if functional unit test succeeds.
In my test environment I did point the System ODBC and the SM Console at the AlwaysOn listener, but when I do a failover, then users cannot login until I either (a) failback SQL or (b) restart the Policy Server Service.
I'm wondering if there is a way that CA SSO can support this robust, High Availability feature of SQL.
What is being referred to as Failover? I am assuming it is within the "availability group" and not across different "availability group listener".
I see there are two usecase, not sure which usecase you are trying....
UseCase-1 : If it is failover within a single "availability group listener"; then.....
From a logical standpoint any client connecting to "availability group listener" should be unaware of the underlying physical servers. "availability group listener" would be masking the underlying database architecture. To the client "availability group listener" is emulating as a physical server & "availability group listener" is always up. To any client (including CA SSO) the failover within the availability group should be masked. Like I mentioned, though it seems simple logically, may it needs some testing / support effort on CA SSO to formally introduce support.
UseCase-2 : If it is failover across multiple "availability group listener"; then.....
Like I mentioned it may not be supported or tested with CA SSO / Data Direct Drivers (shipped with CA SSO).
Listeners, Client Connectivity, Application Failover | Microsoft Docs
When an availability group failover occurs, existing persistent connections to the availability group are terminated and the client must establish a new connection in order to continue working with the same primary database or read-only secondary database. While a failover is occurring on the server side, connectivity to the availability group may fail, forcing the client application to retry connecting until the primary is brought fully back online.
If the availability group comes back online during a client application’s connection attempt but before the connect timeout period, the client driver may successfully connect during one of its internal retry attempts and no error will be surfaced to the application in this case.
Our support matrices does specifically mention for Oracle whether RAC is supported or not. I am unsure if "AlwaysOn" feature of MS SQL does fit into such a scenario, wherein we may to specifically state if the feature is tested / supported. It is always good to know via Support Matrices.
My recommendation is to raise a Case & if needed followup with an ER (ideation) to formally include support for the HA feature of MS SQL (if the word turn out to be not tested / supported).
As Hubert said, AlwaysOn functionality is internal to Sql Server.
So, I believe, if the failover works for your other sql clients, then it should work (theoretically ) with CA SSO Policy server as well..
Have you considered configuring this failover directly in CA SSO ? You can configure multiple failover DSN (each pointing to different sql nodes) .. this way we have more control on the failover...