I have configured my Audit store as follows:
I would expect a fail over to the second DSN if SiteMinder Policy Server is unable to insert data into the Audit Database due to error condition in the Database such as a lock table. In fact I can see audit exceptions in the smps.log, but SiteMinder will not fail over.
I've seen same behavior in Session Server and User Directories as well.
It's important to understand how ODBC connections works and under what specific events SM Policy Server will fail over to the next DSN in the list.
The life cycle of the ODBC connections in a nut shell:
By default the database server is contacted every 15 seconds (a login attempt) if the connection is in the available state to make sure that the connection is still available. It may be the case that a connection is in the active state for 15 seconds or more. If it is noted that a connection has been in the active state for more than 15 it is flagged as hung. The connection is disconnected and then reconnected.
How ODBC failover works:
For failover SiteMinder maintains a list of configured data sources with a priority that is determined by the order in which they are listed. When a statement must be executed, a connection is required. When SiteMinder interrogates the database layer for a connection, it looks at the first entry in the list that has the connection for which it is looking. It checks the state of the connection to determine whether it is available. If the state is hung, then the connection is considered not usable and it checks the next data source in the list. This continues until it finds an available connection. When the primary server becomes available again, the thread that was monitoring the connection flags it as available and when another connection is requested this connection is used once again. This ensures that the primary data source always get preferred treatment.
Why SiteMinder Policy Server is not failing over to the next available Data Source:
As defined above, SiteMinder Policy Server will fail over when:
SiteMinder will not fail over to the next data source if there's any other error condition in the database such as lock tables, corrupted indexes or lack of space because the connection is not down or in hang state.