Set up Safewhere with SQL Cluster Guideline
Introduction
We will set up Identify to work with the SQL cluster environment.
Environment setup
We have a fresh environment as follows:
The basic 2-nodes failover cluster is ready.
Source instance of SQL server - Primary replica. In my sample, we use the environment: Window 2016 with SQL 2014. Its name is: WIN2K16FRESH.
Destination instance of SQL server - Secondary replica. In my sample, we 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.
- Open Identify Configurator and go through the following step:
- Click Next.
- In this step, input the primary replica info to be used for storing Safewhere Identify instances configuration. In this case, input: WIN2K16FRESH\SQL2014,1433, and use Windows authentication to log in before clicking Next
- Click Next and then choose Create a new instance
- Click Next to get to the Specific settings screen:
Remember to enable Multi Subnet failover setting.
- Click Next to get to the General info screen:
- Click Next to get to the Session state settings screen:
- Click Next to get to the Audit log information screen:
Choose the database provider to store the audit database. In this case, choose SQL database.
- Click Next to get to the IIS setup screen:
- Click Next to get to the Security setup screen:
- Click Next to get to the Certificates screen:
Choose the Identify's SSL certificate and the identify's signing certificate.
- Click Next to execute the creation of a new instance.
- 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. We created a sample to set up the listener.
Preparation
We do the backup on the Identify databases: Identify;IdentifyAudit;IdentifySession; IdentifySessionstate;IdentifyCache;IdentifyTenant like below:
Otherwise, we encounter this message when creating the available group:
How to do
- Remote to the primary SQL server, in our sample, it's WIN2K16FRESH\SQL2014.
- Execute the 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 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 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 our sample, it's SQLSRV02. Enable the following:
- Automatic Failover
- Synchronous Commit
- Readable Secondary
- Click the Endpoint tab and configure the endpoints.
- Click the Listener tab and configure the listener. In our sample, input the name AGL01 as the listener name.
- We need to configure a data synchronization method. In this sample, we use Full where the path is a share available on our 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 the 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:
UPDATE [IdentifyTenant].[dbo].[Tenant]
SET [XmlConfiguration]=REPLACE([XmlConfiguration],N'WIN2K16FRESH\SQL2014',N'AGL01')
WHERE [NAME]='#yourtenantname'
where WIN2K16FRESH\SQL2014 is the SQL server information and AGL01 is the listener name.
Update its connectionstring at Admin/Runtime/Service
Please note that the Service folder has been retired starting from version 5.13, so there is no need to make any changes to it if your tenant version is 5.13 or later.
To update the connection strings, follow the steps below:
- Access the Identify web server and open the CMD prompt from the Start\Run.
- Execute the following command to navigate to the correct directory:
Cd C:\Windows\Microsoft.NET\Framework\v4.0.30319
- Replace the #yourtenantname with your own tenant name in the following command lines and 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 file at Admin/Runtime/Service, and replace the SQL server name with the listener name. For example, replace WIN2K16FRESH\SQL2014 with AGL01 listener.
- ack to the Command prompt, replace the #yourtenantname with your tenant name in the following command lines, and 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 to apply the changes.
- Access your tenant in the browser to ensure that the update has been successful.
FAQ
- If you create or upgrade a tenant in the future, you can use the listener name as the database server info in the Identify configuration.
You don't need to update the connectionstring when you upgrade the tenant that was created for the first time. Its connectionstring was updated at the Reconfig the previous new-created Identify instance section.
If you encounter a login credential error when accessing the Identify site, you can verify the database login credential is created properly on the SQL servers or not. And you can 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 credential manually.