Configuring ActivID CMS to Use a MS-SQL Server High Availability Solution

This section covers two high-availability solutions:

There are 2 options for configuring ActivID CMS with Always On availability groups:

  • The preferred option is to use a connection string targeting an availability group listener that will redirect ActivID CMS to the correct replica regardless of the occurrence of a failover. It is also recommended to use a Test on Borrow strategy at the connection level.

    For example, the JDBC connection strings for each ActivID CMS database could be edited as follows:

    Copy 
    AIMSEE_DB_URL =jdbc:sqlserver://CMS-AG-LISTENER/AIMSEE;serverName=CMS-AG-LISTENER;databaseName=AIMSEE;socketTimeout=60000;ValidationQuery=SELECT 1;TestOnBorrow=true

  • The other option is to use a JDBC connection string similar to the one used for database mirroring, that is, using the failoverPartner attribute. However, if a default SQL Server instance is used, the JDBC driver limits connection to the failover server to the default port: 1443.

    Note: If a named instance is used, you can specify the instance name in the failoverPartner attribute in order for ActivID CMS to request the correct port number.

For more information about client connections to Always On availability groups, refer to the Microsoft SQL Server documentation at the following URL:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server#ClientConnections

Note: Since Microsoft plans on removing database mirroring from future versions of SQL Server, it is recommended to use Always On availability groups instead. For more information, see Setting Up Always On Availability Groups and Configuring ActivID CMS to Use Always On Availability Groups above.

There is a key relationship required between the Principal and Mirror databases to support automatic database failover. To enable an MS-SQL server to support failover for the ActivID CMS, you must define a Principal (database) server and a Mirror (backup) server, even if the ActivID CMS databases are not installed on the Mirror server.

ActivID CMS automatically uses the databases configured on the Mirror server when the Principal server fails. The Witness server detects the Principal server failure and promotes the Mirror server to act as the “new” Principal server.

  • Database Server—the role for the Principal server instance (shown as SERVER-A in this illustration).

  • Backup Server—the role for the Mirror server instance (shown as SERVER-B in this illustration).

Both Microsoft Windows authentication and MS-SQL server authentication modes are supported for SQL server mirroring. For information on the installation and configuration processes for the ActivID CMS, refer to Installing ActivID CMS.