For the last few weeks I have been testing VMware Identity Manager with SQL Always On database for multi-site deployments. This has been an interesting learning curve as its been some time since I last did anything substantial with Microsoft SQL. Before I start with the VMware Identity Manager I think it is worth calling out these 2 resources that I found really useful for setting up SQL Always On in my Lab.

This is a quick intro in to SQL Always On and how to configure it

https://www.youtube.com/watch?v=VKCqRgqLAuo

This was a useful step by step guide for deploying SQL Always On

http://www.careexchange.in/installingconfiguring-sql-2014-always-on-cluster-on-windows-2012-r2-recommended-way/

Now before configuring VMware Identity Manager with an SQL Always On Database you should be aware that even though there is a database in each of the datacenter’s all Read and Writes operations will take place on the Primary database with in the Availability Group.

From my testing I found that setting the database to automatic failover worked as expected and the database was only unavailable for a very short time less than a couple of seconds. However, I did find that when I failed the database back after an outage this took a bit more time and I would recommend that any failback is done in a much more controlled manner. In my testing fail back took about 40 seconds so a noticeable difference.

Creating the VMware Identity Manager SQL Always On Database

 

1. Open SQL Management Studio and log in with sysadmin privileges (This should be done on the primary server)
2. Click File – New – Query with current connection
3. In the editor window paste the following SQL Commands

CREATE DATABASE saas

COLLATE Latin1_General_CS_AS;

ALTER DATABASE saas SET READ_COMMITTED_SNAPSHOT ON;

GO

BEGIN

CREATE LOGIN horizon WITH PASSWORD = N'H0rizon!';

END

GO

USE saas;

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'horizon')

DROP USER [horizon]

GO

CREATE USER horizon FOR LOGIN horizon

with default_schema = saas;

GO

CREATE SCHEMA saas AUTHORIZATION horizon

GRANT ALL ON DATABASE::saas TO horizon;

GO

4. Click Execute

5. The saas Database will now be created
6. Make a Full backup of the database (This must be done before adding the database to an Always On High Availability Group)
   • Right click the database – Tasks – Back Up
7. Add the database to the Always On High Availability Group

 

NOTE: It is also recommended to make the following changes to SQL

• Change ‘HostRecordTTL to a lower value than the default in multi-site deployments. 120 seconds is a good value
• Change ‘RegisterAllProvidersIP’ to false in multi-site deployments

Connect VMware Identity Manager to the SQL Database

During the install of VMware Identity Manager connect to the SQL Database using the following settings

Jdbc:sqlserver://SQLAGListener;DatabaseName=saas

• SQLAGListener = the SQL Availability Group Listener, in the example below that is SQLProdServer
• If the secondary SQL server is on a different subnet add the following to the jdbc string
  • multiSubnetFailover=true
    • Jdbc:sqlserver://SQLAGListener;DatabaseName=saas; multiSubnetFailover=true