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
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
- Open SQL Management Studio and log in with sysadmin privileges (This should be done on the primary server)
- Click File – New – Query with current connection
- 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
- Click Execute
- The saas Database will now be created
- 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
- 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
- multiSubnetFailover=true