Microsoft Identity Manager 2016 SP1 (4.4.1459.0 or Later) – Support for SQL 2016 Always On Availability Groups

Hello folks, David Steadman Here !!

Today with the release of the Microsoft Identity Manager 2016 SP1 March 2017 Hotfix (4.4.1459.0), In the past the identity management platform only supported certain scenarios defined here : https://support.microsoft.com/en-us/kb/3200896. (Updated Today) This did not provide customers going to cloud and expanding their database infrastructure and provide high availability of services

We have heard you via https://aka.ms/mimfeedback

We have confirmed that the complete Identity platform databases can be run using the Always On Availability Groups Configuration

As of Today the Complete Microsoft Identity Manager 2016 SP1 4.4.1459.0 or later is Supported on the SQL 2016 AoA

Known Issues and findings:

Note: Unless otherwise specified, all items below apply to all databases for the MIM server components.

• SQLNCLI OLE DB Provider DOES NOT support the MultiSubnetFailover keyword, but the ODBC driver does https://msdn.microsoft.com/en-us/library/gg471494.aspx
  • Action Required: If  using a management agent Turn off RegisterAllProvidersIP in the SQL Always On configuration, so the management agent can connect to the database
• The Service throws the Exception. the Service tries to send fault to client if it is necessary and update request status to Denied.
• The Service retries to connect to the database. There are 10 retries with timeout 6 second after which the Service is terminated if the database is not available. If the database is available, the service tries to continue processing request.
• The same situations could happen when the service tries to update status to Denied if the database is not become available.
• For Synchronization Service, if request fails during Sync session (FIM MA Import/Export) due to FIMService database is not available the Sync session will stop with Stop Server status (the same error as for FIMSynchronizationService database is not available).
  • Run (Delta/Full) Import then Continue with Export
• Scenario / Slow SQL replication is lagging as this can/will bring down service request pipeline

Troubleshooting

For monitoring Always On Availability Groups we can periodically query dynamic views. For example once an hour and it could be changed in config file.

1. To check if Always On  Availability Groups is enabled:
   • select SERVERPROPERTY ('IsHadrEnabled')
   • 1 - enabled;  0 – disabled; NULL – Input is not valid, an error, or not applicable (for example for MS SQL Server 2008 R2)
   • https://msdn.microsoft.com/en-us/library/ms174396.aspx
2. If AOAG is enabled checking status using
   • select * from sys.dm_hadr_database_replica_states hdrs where hdrs.database_id = DB_ID()
   • Analyze synchronization_health column. If there are more than 2 rows and status of all is 2 (HEALTHY) – report info message with status_description
   • Otherwise – report warning/error https://msdn.microsoft.com/en-us/library/ff877972.aspx
   • https://msdn.microsoft.com/en-us/library/ff877883.aspx

For other Availability solutions (https://msdn.microsoft.com/en-us/library/ms190202.aspx ):

1. Always On Failover Cluster Instances:
   • select * from sys.dm_os_cluster_nodes
   • If row count greater than 0 - Always On Failover Cluster is enabled
   • If row count less than 2 or there is rows with status not equals 0 – report warning/error
   • Otherwise – report Info message with status_description https://msdn.microsoft.com/en-us/library/ms187341.aspx
2. Database mirroring:
   • There is dynamic view for monitoring
   • select * from sys.database_mirroring where database_id = DB_ID()
   • but it is necessary to have sysadmin role for the user. Also it is recommended using Always On Availability Groups instead.
     • https://msdn.microsoft.com/en-us/library/ms178655.aspx
3. Log shipping:
   • There is no dynamic views for monitoring log shipping. It is necessary to query tables from MSDB database. And log shipping doesn’t support automatic failover. https://msdn.microsoft.com/en-us/library/ms190224.aspx

Reference Articles:

Overview of Always On Availability Groups (SQL Server) Configure SQL Server AlwaysOn Availability Groups for SharePoint Server 2016 Overview of the features in Azure Backup