Introduction
We will setup the Identify to work with the SQL cluster environment
Environment setup
We have the fresh environment like below:
- The basic 2-nodes failover cluster are ready
- Source instance of SQL server - Primary replica. In my sample, I use the environment: Window 2016 with SQL 2014. its name is: WIN2K16FRESH
- Destination instance of SQL server - Secondary replica. In my sample, I use the environment: Window 2016 with SQL 2014. its name is: SQLSRV02
- On both 2 replicas:
- Enable the below setting then have the SQL service reset:
Identify and listener setup
Fresh Identify ’s instance setup
The Identify web server doesn’t have any Identify instance before and there are no Identify databases on the SQL servers. By doing this step, the Identify databases are created and the fresh instance is ready.
- Execute the Identify configurator
- Click Next to go to the next step.
- Continue to click Next.
- At the server field, input the primary replica info, in this case, I input: WIN2K16FRESH\SQL2014,1433 and use the Window authentication to login. Click Next to go to the next step
- Choose “Create new instance” then click Next
- Input the required info and the admin password, then click Next
- Choose the database authentication for the tenant then click Next
- User can choose the database provider to store the audit database. In this case, I choose: SQL database then click Next. Please note: if your SQL server is using different port, e.g 14331, you need to enter 14331 to the “enter port” field.
- Input the Identify’s domain name, then click Next
- Choose the Identify ‘s SSL certificate and the identify ’s signing certificate then choose Next
- Click Next to carry on the instance creation
- The instance is created successfully
Listener setup
After the Identify databases are ready, we create the listener for the availability group of the Identify databases. I create the sample to setup the listener.
Prepare
User needs to do the backup on the Identify databases: Identify;IdentifyAudit;IdentifySession; IdentifySessionstate;IdentifyCache;IdentifyTenant like below:
Else he encounters this message when creating the available group
How to do
- Remote to the primary SQL server, in my sample, it’s WIN2K16FRESH
- Execute SQL Server Management studio. In Object Explorer, connect to the server instance that hosts the primary replica. Expand the Always On High Availability node and the Availability Groups node. To launch the New Availability Group Wizard, select the New Availability Group Wizard command
- On the Specify Availability Group Options page, enter the name of the new availability group in the Availability group name field. This name must be a valid SQL Server identifier that is unique on the cluster and in your domain as a whole. The maximum length for an availability group name is 128 characters.
- Choose the Identify databases: Identify;IdentifyAudit;IdentifySession; IdentifySessionstate;IdentifyCache;IdentifyTenant then click Next
- Click "Add Replica...".
- Add the secondary replica. In my sample, it’s SQLSRV02
- User can enable the following:
- Automatic Failover
- Synchronous Commit
- Readable Secondary
- User can enable the following:
- Click Endpoint tab and configure your endpoints.
- Click the Listener tab and configure the listener. Please notice I’m using the port I defined when configuring the networking. In my sample, I input the name: “AGL01” as the listener name.
- User needs to configure a data synchronization method. In this sample, I use Full where the path is a share available on my node to node communication network. Click Next
- Ensure all validation checks are green.
- Click next and then finish. This will configure endpoints and perform various other tasks.
Reconfig the previous new-created Identify instance
Update SQL server name at the table: [Tenant] on the Identifytenant database
- Execute SQL Server Management studio, access to server with the listener name
- Run the following query to update the SQL server name by the listener name:
|
1 2 3 |
Update [IdentifyTenant].[dbo].[Tenant] set [XmlConfiguration]=REPLACE([XmlConfiguration],N'WIN2K16FRESH\SQL2014',N'AGL01') |
where “WIN2K16FRESH\SQL2014” is the SQL server info and “AGL01” is the listener name
Update its connectionstring at Admin/Runtime/Service
- Access to the Identify web server
- Execute the Command prompt with “Run as administrator”
- Input: Cd C:\Windows\Microsoft.NET\Framework\v4.0.30319 then press enter
- Update the following command lines by replacing the #yourtenantname by your tenant name then run them one by one to decrypt the connectionStrings and the sessionState
- aspnet_regiis.exe -pdf "system.web/sessionState" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\admin"
- aspnet_regiis.exe -pdf "connectionStrings" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\admin"
- aspnet_regiis.exe -pdf "system.web/sessionState" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\runtime"
- aspnet_regiis.exe -pdf "connectionStrings" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\runtime"
- aspnet_regiis.exe -pdf "system.web/sessionState" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\service"
- aspnet_regiis.exe -pdf "connectionStrings" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\service"
- Open the web.config at Admin/Runtime/Service then replace the SQL server name by the listener name. In my sample, I replace: WIN2K16FRESH\SQL2014 by my listener “AGL01”. We recommend to add the setting: “MultiSubnetFailover=True;” to the connectionstring. Here is the sample:
- Back to the Command prompt, update the following command lines by replacing the #yourtenantname by your tenant name then run them one by one to encrypt the connectionStrings and the sessionState
- aspnet_regiis.exe -pef "system.web/sessionState" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\admin"
- aspnet_regiis.exe -pef "connectionStrings" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\admin"
- aspnet_regiis.exe -pef "system.web/sessionState" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\runtime"
- aspnet_regiis.exe -pef "connectionStrings" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\runtime"
- aspnet_regiis.exe -pef "system.web/sessionState" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\service"
- aspnet_regiis.exe -pef "connectionStrings" "C:\Program Files\Safewhere\Identify\Tenants\#yourtenantname\service"
- Reset the IIS or the Identify ’s application pool so that the change is affected after that.
- Open the browser and access to the tenant, here is its result:
FAQ
- Question: For the next tenant creation/upgrade, which database server info do I use on the Identify configuration?
- Answer: you can use the listener name from now on.
- Question: Adding the setting: “MultiSubnetFailover=True;” to the connectionstring for every created/upgraded Identify manually is boring, what should I do?
- Answer:Right after you install the Identify installation, you can open the web.config at the following locations:
- C:\Program Files\Safewhere\Identify\admin
- C:\Program Files\Safewhere\Identify\runtime
- C:\Program Files\Safewhere\Identify\service
add the “MultiSubnetFailover=True;” at its connectionStrings likes below:
|
1 2 3 4 5 6 7 |
<connectionStrings> <add name="IdentityProvider" providerName="System.Data.SqlClient" connectionString="Server=#SERVER; Database=Identify; #LOGIN_CREDENTIAL; MultipleActiveResultSets=true;MultiSubnetFailover=True;" /> <add name="IdentityProviderAudit" providerName="System.Data.SqlClient" connectionString="#CONNECTION_STRING_AUDIT_LOG_PROVIDERMultiSubnetFailover=True;" /> <add name="IdentityProviderCache" providerName="System.Data.SqlClient" connectionString="Server=#SERVER; Database=IdentifyCache; #LOGIN_CREDENTIAL; MultipleActiveResultSets=true;MultiSubnetFailover=True;" /> <add name="IdentifyUserNamePassword" providerName="System.Data.SqlClient" connectionString="Server=#SERVER; Database=Identify; #LOGIN_CREDENTIAL; MultipleActiveResultSets=true;MultiSubnetFailover=True;" /> <add name="IdentityProviderSession" providerName="System.Data.SqlClient" connectionString="Server=#SERVER; Database=IdentifySession; #LOGIN_CREDENTIAL; MultipleActiveResultSets=true;MultiSubnetFailover=True;" /> </connectionStrings> |
then save it.
Here is the result when I decrypt the connectionstring of the new created/upgraded the tenant:
- Question: If I upgrade the tenant which is created at the first time, do I need to update its connectionstring after that?
- Answer:No, you don’t need to update it.
- Question: I get the error about the login credential when accessing the Identify site, what should I do?
- Answer:The database login credential needs to create manually among the SQL servers. Please follow: https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server to create the database login credentail.