Installing YSoft SafeQ Management Server on MSSQL AlwaysOn Availability group

Preconditions

  1. Correctly configured Windows Failover Cluster with installed MSSQL 2016, MSSQL 2017, or MSSQL 2019 enterprise edition (on both nodes of cluster).

    Tip: you can follow f.e. following guide: [1] Step-By-Step: Creating a SQL Server Always On Availability Group

  2. Enabled SQL Server 2016/2017/2019 AlwaysOn Availability Groups feature.

  3. Enabled Contained Databases.

images/download/attachments/160480941/enableContainedDB.PNG

Installation steps

Using Server installer package

Use this package if you want to deploy more roles on this server (Management Server, Spool Server, Scan Server, Mobile Print Server)

  1. Run Server installer package on any server

  2. On the Server environment screen select the First server installation scenario

  3. On the Database configuration screen select the External Microsoft SQL database server option

  4. Fill all required fields in the Microsoft SQL database screen:

    1. As database connection string set the IP/hostname of the master node of your MSSQL cluster (Windows Failover Cluster)

    2. and check the Always On Availability Group Server checkboximages/download/attachments/160480941/Microsoft_SQL_database.PNG

  5. Proceed until installation is finished

Using Management Server subsystem installer

Use this installer if you want to deploy only the Management Server role.

  1. Run YSoft SafeQ Management Server installation on any server

  2. Continue in installation process until YSoft SafeQ Management Server installation settings screen and select the I want to customize my YSoft SafeQ installation checkboximages/download/attachments/160480941/managementServerSetupScreen.PNG

  3. On the Installation mode screen select the Install a new YSoft SafeQ Management Server (or the first node of a new YSoft SafeQ Management Server cluster) option

  4. Select the Use an existing external MSSQL database server option

  5. Fill all required fields to the MSSQL database connection:

    1. As database connection string set the IP/hostname of the master node of your MSSQL cluster (Windows Failover Cluster)

    2. and check the Always On Availability Group Server checkbox

      images/download/attachments/160480941/configurateDatabaseGroupScreen.PNG

  6. Proceed until installation is finished

Using an external database for a data warehouse on the same server

After YSoft SafeQ Management Server installation finishes, open SQL Server Management Studio and connect to the Database Engine as System Administrator (user sa) and run the following queries:

ALTER DATABASE [SQDB6_DWH] SET CONTAINMENT = PARTIAL WITH NO_WAIT;
ALTER DATABASE [SQDB6_DWH] SET TRUSTWORTHY ON;


Do not forget to change the database name in the queries above. The queries have the default data warehouse database name in them (i.e. SQDB6_DWH), change it to the correct YSoft SafeQ data warehouse database name you have chosen for your deployment.

After installation

One of the possible solutions for correct environment setup is the following guide:

Create AlwaysOn High Availability group

  1. Navigate to Microsoft SQL Server Management Studio, where the databases are created (SQDB6 and SQDB6_IMS, optionally also SQDB6_YPS - if you are using YSoft SafeQ Payment System)

  2. Set Recovery model of both SQDB6 and SQDB6_IMS databases to Full (SQDB6_YPS is configured this way by default)

  3. Perform Full Backup of all SQDB6* databases

  4. Navigate to AlwaysOn High Availability > Availability Groups > New Availability Group wizard...

    images/download/attachments/160480941/alwaysOnAG.PNG
  5. In the Specify Availability Group Name page enter the name of the Availability group

  6. Next, select all SQDB6* databases

  7. In the Specify Replicas page > Replicas tab, click the Add Replicas button and connect to the second SQL Server that you joined as nodes in your Windows Server Failover Cluster and configure the following [1]:

    • Automatic Failover: Checked

    • Synchronous Commit: Checked

    • Readable Secondary: No

  8. In the Endpoints tab verify that the port number is 5022

  9. In the Listener tab, select Create an availability group listener option and enter the following [1]:

    • Listener DNS name: Name that you will use in your application connection string

    • Port: 1433

  10. Add IP Address in Add IP Address dialog box

  11. Select Full as Initial Data Synchronization.

  12. Proceed until Always On Availability Group is created

Proceed with the following steps and set up YSoft SafeQ 6

  1. Navigate back to the server where your YSoft SafeQ Management Server is installed

  2. Navigate to <install_dir>\Management\Conf\safeq.properties file, edit it, and set the following:

    database.host = <Listener DNS name>
  3. Navigate to <install_dir>\Management\ims\application.properties file, edit it and replace the IP address in the spring.datasource.url with the Listener DNS name:

    1. Change

      spring.datasource.url = jdbc:sqlserver://<IP address>:1433;databaseName=SQDB6_IMS;applicationName=IMS
    2. To

      spring.datasource.url = jdbc:sqlserver://<Listener DNS name>:1433;databaseName=SQDB6_IMS;applicationName=IMS
  4. Save changes and restart YSoft SafeQ Management Service and YSoft Infrastructure Service services

  5. Repeat steps 1.-4. on all Management Servers in cluster

If you are using YSoft SafeQ Payment System, reconfigure it as well

  1. Navigate to the server where your Payment System is installed

  2. Navigate to <install_dir>\YPS\ps-conf\environment-configuration.properties file, edit it and replace the IP address in the database.url with the Listener DNS name:

    1. Change

      database.url=jdbc:sqlserver://<IP address>:1433;databaseName=SQDB6_YPS;
    2. To

      database.url=jdbc:sqlserver://<Listener DNS name>:1433;databaseName=SQDB6_YPS;
  3. Save changes and restart YSoft SafeQ Payment System service

  4. Repeat steps 1.-3. on all servers where is Payment System installed