Setup Safewhere with SQL Cluster Guideline

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:

Cluster_Enable_AlwaysOn_Availability_Groups

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
    Cluster_IdentifyConfigurator
  • Click Next to go to the next step.
    Cluster_IdentifyConfigurator_Check_Prerequisite
  • Continue to click Next.
    Cluster_IdentifyConfigurator_Tenant_Database
  • 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
    Cluster_IdentifyConfigurator_Select_action
  • Choose “Create new instance” then click Next
    Cluster_IdentifyConfigurator_Specific_Settings
  • Input the required info and the admin password, then click Next
    Cluster_IdentifyConfigurator_GeneralInfo
  • Choose the database authentication for the tenant then click Next
    Cluster_IdentifyConfigurator_Auditloginformation
  • 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.
    Cluster_IdentifyConfigurator_IISsetup
  • Input the Identify’s domain name, then click Next
    Cluster_IdentifyConfigurator_Certificate
  • Choose the Identify ‘s SSL certificate and the identify ’s signing certificate then choose Next
    Cluster_IdentifyConfigurator_Execution
  • Click Next to carry on the instance creation
    Cluster_IdentifyConfigurator_Finish
  • 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:

Cluster_Listener_Setup_Prepare

Else he encounters this message when creating the available group

Cluster_Listener_Setup_SelectDatabase

 

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
    Cluster_Listener_Setup_HowToDo
  • 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.
    Cluster_Listener_Setup_HowToDo_2
  • Choose the Identify databases: Identify;IdentifyAudit;IdentifySession; IdentifySessionstate;IdentifyCache;IdentifyTenant then click Next
    Cluster_Listener_Setup_HowToDo_3
  • Click "Add Replica...".
    Cluster_Listener_Setup_HowToDo_4
  • Add the secondary replica. In my sample, it’s SQLSRV02
    • User can enable the following:
      • Automatic Failover
      • Synchronous Commit
      • Readable Secondary

    Cluster_Listener_Setup_HowToDo_5

  • Click Endpoint tab and configure your endpoints.
    Cluster_Listener_Setup_HowToDo_6
  • 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.
    Cluster_Listener_Setup_HowToDo_7
  • 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
    Cluster_Listener_Setup_HowToDo_8
  • Ensure all validation checks are green.
    Cluster_Listener_Setup_HowToDo_9
  • Click next and then finish. This will configure endpoints and perform various other tasks.
    Cluster_Listener_Setup_HowToDo_10

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
    Cluster_Reconfig
  • Run the following query to update the SQL server name by the listener name:

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:
    Cluster_Update_its_connectionstring
  • 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:
    Cluster_Update_its_connectionstring_2

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.
    Cluster_FAQ_1
  • 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:

then save it.

Here is the result when I decrypt the connectionstring of the new created/upgraded the tenant:

Cluster_FAQ_2

  • 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.