Register Sign in Need help? FAQ SailPoint directory
Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Configuring IdentityIQ for use with SQL Server mirroring

Configuring IdentityIQ for use with SQL Server mirroring

 

Introduction

IdentityIQ has strict requirements for how its schema is configured on SQL Server environments. SQL Server databases handle row-level record mutual exclusion or "Locking" differently than other relational database systems.  In SQL Server it is very easy to construct a set of queries across multiple tables that cause locking to be "contagious" or liberally applied to all objects involved in the query.  This is not how IdentityIQ expects its relational database to apply locking semantics; IdentityIQ expect locking semantics similar to MySQL, Oracle, or DB2, all of which share a very similar and very restrictive structure for lock escalation at the relational database row and table level.

 

To support highly concurrent usage patterns, IdentityIQ's schema, as of the 7.0 release, requires the use of an isolation type called "READ_COMMITTED_SNAPSHOT".  This mode of configuration enables a SQL Server database to have locking semantics similar to other relational database management systems, and is required for IdentityIQ to be able to safely execute parallelized and concurrent operations against a SQL Server database.

 

To address this, the IdentityIQ 7.0 release includes a modification to the DDL for SQL Server based databases that improves compatibility and performance on SQL Server based systems.  This update also helps prevent deadlock conditions that have caused stack trace exceptions on previous versions of IdentityIQ. IdentityIQ 7.0 (and newer releases that will follow) now ship with the following statements in their SQL Server DDL files:

 

-- Enable automatic snapshot isolation

ALTER DATABASE identityiq SET ALLOW_SNAPSHOT_ISOLATION ON

GO

ALTER DATABASE identityiq SET READ_COMMITTED_SNAPSHOT ON

GO

 

Most SQL Server databases are not configured for "READ_COMMITTED_SNAPSHOT" by default.  While it may be obvious to some, it is worth nothing the above mentioned ALTER TABLE commands set isolation at the database level. Configuring SQL Server's "Mirroring" redundancy feature can be challenging for databases that require "READ_COMMITTED_SNAPSHOT" type isolation.  The aim of this guide is to aid the deployment professional with the configuration of a SQL Server environment so that Mirroring's replication and safety benefits can co-exist with IdentityIQ's highly-parallelized performance features.

 

For more information about the particulars of SQL Server database locking semantics and how they compare and differ to other relational databases, please see the following KB article:

 

 

This MSDN article also has interesting background information on snapshot isolation:

 

 

SQL server configuration requirements

Only "High Performance" (asynchronous) mirroring mode is supported for use with IdentityIQ.  The "High Safety" ("synchronous") mode of SQL Server mirroring is not a supported configuration for use with IdentityIQ.  The "High Safety" mode causes SQL Server to operate in a 2-phase-commit type of operation that is shown as not-supported in the following guide: (Recommended IdentityIQ Deployment Architectures ).

 

IdentityIQ's back-end database can not be simply loaded into an already mirroring-enabled pair of servers due to its special locking configuration requirements.  SQL Server will ignore the locking semantics requested when a new database is loaded onto an already existing pair or set of mirroring servers.  Mirroring must be disabled during the initial loading of IdentityIQ's DDL in order for SQL Server to honor the "READ_COMMITTED_SNAPSHOT" parameter of IdentityIQ's DDL.

 

Retrofitting IdentityIQ onto a mirroring pair

To retrofit IdentityIQ onto a mirroring pair, you will apply a default snapshot setting schema-wide, where all connections are established and by defaulted assigned the "READ_COMMITTED_SNAPSHOT" isolation level (described below). This is the preferred method of configuration because it prevents connections from running transactions with isolation levels not compatible with IdentityIQ's locking needs.

 

Configuring schema-wide isolation

SQL Server mirroring is the technology that automates replication of the primary database to a backup database server.  It is used by many, if not most, of the IdentityIQ installations that run enterprise environments on Microsoft SQL Server.  SQL Server will not allow an administrator to alter the default isolation settings of a schema that is currently being replicated by SQL Server mirroring.

 

There is a workaround for this limitation of SQL Server; however, the procedure can be cumbersome. Installations that already have mirroring enabled, and would like to configure SQL Server to strictly default to using "READ_COMMITTED_SNAPSHOT" isolation for connections made to IdentityIQ's database, should follow a procedure similar to the following when installing an IdentityIQ schema:

 

  1. Stop the SQL Server mirroring process between the two systems.
    1. Screen Shot 2015-12-05 at 1.05.45 PM.png
    2. Screen Shot 2015-12-05 at 1.06.45 PM.png
    3.  
    4. Screen Shot 2015-12-05 at 1.08.02 PM.png
    5. Screen Shot 2015-12-05 at 1.09.52 PM.png
  2. Install the IdentityIQ database on the primary/master server.  Take care to ensure the locking configuration statements are applied.
    1. Screen Shot 2015-12-07 at 2.30.28 PM.png
  3. Test connecting the IdentityIQ database from IIQ Console.
    1. Screen Shot 2015-12-07 at 2.37.14 PM.png
  4. Re-configure SQL Server mirroring to replicate the database to the standby side using "High Performance" mirroring.
    1. Make sure the IIQ database is set to use the FULL recovery model.
    2. Screen Shot 2015-12-08 at 2.42.02 PM.png
    3. Back up the IdentityIQ database and transaction logs.
      1. Start with the database
        1.  
        2. Screen Shot 2015-12-08 at 1.59.03 PM.png
      2.  
      3. Screen Shot 2015-12-08 at 2.45.29 PM.png
      4. Screen Shot 2015-12-08 at 2.46.34 PM.png
      5. Then back up the transaction log.
      6. Screen Shot 2015-12-08 at 1.59.03 PM.pngScreen Shot 2015-12-08 at 2.47.34 PM.pngScreen Shot 2015-12-08 at 2.48.24 PM.pngScreen Shot 2015-12-08 at 2.48.55 PM.png
      7. Copy the database backup and transaction log files to the Mirroring (secondary) server, and restore them.
        1. Screen Shot 2015-12-09 at 4.10.01 PM.png
        2. Screen Shot 2015-12-09 at 4.11.17 PM.png
        3. Screen Shot 2015-12-09 at 4.27.03 PM.png
        4. Screen Shot 2015-12-09 at 4.28.08 PM.png
        5. Screen Shot 2015-12-09 at 4.50.07 PM.png
        6. go into "Options" and change Recovery state to "RESTORE WITH NORECOVERY".
        7. Screen Shot 2015-12-09 at 4.31.33 PM.png
        8. Now do the same process with the backup transaction log from the principal server, making sure to change Recovery state to "RESTORE WITH NORECOVERY" as you did with the database backup.
        9. Screen Shot 2015-12-09 at 4.33.48 PM.png
        10. Screen Shot 2015-12-09 at 4.35.05 PM.png
        11. Screen Shot 2015-12-09 at 4.36.04 PM.png
        12. Screen Shot 2015-12-09 at 4.52.31 PM.png
        13. Screen Shot 2015-12-09 at 4.53.10 PM.png
        14. Screen Shot 2015-12-09 at 4.53.38 PM.png
  5. Now, configure mirroring. Select the Principal and Mirror servers.

    1. Screen Shot 2015-12-05 at 1.05.45 PM.png

    2. Screen Shot 2015-12-07 at 2.51.14 PM.png
    3. Screen Shot 2015-12-07 at 2.53.53 PM.png
    4. Screen Shot 2015-12-07 at 2.52.58 PM.png
    5. Screen Shot 2015-12-07 at 2.54.51 PM.png
    6. Screen Shot 2015-12-07 at 2.57.20 PM.png
    7. Screen Shot 2015-12-07 at 2.58.18 PM.png
    8. Once the Security Wizard is complete, it will default to "High Safety (synchronous) operating mode". This mode is unsupported for IIQ, so click "Do Not Start Mirroring"
    9. Screen Shot 2015-12-07 at 3.01.22 PM.png
    10. Change the operating mode to "High Performance (asynchronous)".
    11. Screen Shot 2015-12-07 at 3.02.05 PM.png
    12. Click "Start Mirroring".
    13. Screen Shot 2015-12-07 at 3.02.35 PM.png
    14. Screen Shot 2015-12-09 at 4.56.31 PM.png
    15. Verify that the database on the principal server shows status "Principal, Synchronized".
    16. Screen Shot 2015-12-09 at 4.57.18 PM.png
    17. Mirroring setup is complete!
  6. Test the configuration by running a heavily partitioned Identity Refresh or Account Aggregation task.

 

Services standard build/build tools - Special considerations

If you are using Services Standard Build (SSB) to build the database, you may still encounter deadlocks that the isolation changes described in this document are intended to address. This occurs because the SSB script executes the ALTER statements in a multi-statement transaction.

 

An error like this one may occur:

 

[sql] ALTER DATABASE iiq70 SET ALLOW_SNAPSHOT_ISOLATION ON

 

[sql] com.microsoft.sqlserver.jdbc.SQLServerException: ALTER DATABASE statement not allowed within multi-statement transaction.

 

These errors typically occur when you use a tool that executes the statements as one transaction; they don't occur when you use the script directly.

 

You can remedy this problem by running the ALTER statements separately after the tool has run. Alternatively, you can edit the SSB's build.dev.xml file (in the scripts directory),to insert the following on line 31:

 

autocommit="true"

Labels (1)
Labels:
Comments
gaurav_jain
‎Jul 01, 2016 04:07 PM
‎Jul 01, 2016 04:07 PM

cathy.mallet​

Is this setting also recommended for iiq versions prior to 7.0?

ALTER DATABASE identityiq SET ALLOW_SNAPSHOT_ISOLATION ON 

GO

ALTER DATABASE identityiq SET READ_COMMITTED_SNAPSHOT ON 

GO

Also, I guess it is only required if replication is setup through mirroring and not using Always ON. Is that correct?

Thanks,

Gaurav

cathy_mallet
‎Aug 01, 2016 12:33 PM
‎Aug 01, 2016 12:33 PM

Hi Gaurav -

I ran this by Jason S, who is the real expert. He said yes, this setting is recommended for pre-7.0 releases. He also said it should be added regardless of whether you are using mirroring or not; the tricky part is that if you are using mirroring, you have to disable that, apply the commands, then re-enable the mirroring.

Dharmender
‎Mar 09, 2021 02:55 PM
‎Mar 09, 2021 02:55 PM

Hi There,

I have a query were I would seek your suggestion/recommendations .

Our Application (IIQ 8.1) Server is  Apache Tomcat hosted on AWS EC2 along Database server(AWS RDS -SQL Server 2019).

We would like to understand the recommendation for above mentioned SailPoint deployment as we are seeing performance issues.

Any help here would be appreciated.

kaselva
‎Sep 20, 2024 11:27 AM
‎Sep 20, 2024 11:27 AM

@cathy_mallet @gaurav_jain 

Thank you for this insightful article. Based on the information from the Solved: MS SQL Server in Detail - Compass (sailpoint.com), I believe it's acceptable to proceed with AlwaysOn in synchronous mode for high availability, provided the database instances are on the same subnet with minimal latency. Our DBA is also recommending AlwaysOn with failover, but we are hesitant after reviewing the information above.

Could you please advise on any recent improvements? Also, what is recommended for SQL Server DB high availability in a setup where we have 2 UI and 2 task servers pointing to the same database?

Version history
Revision #:
3 of 3
Last update:
‎Jul 25, 2023 06:34 PM
Updated by:
Community Administrator
 
View Article History