SQL server Always On support

ALM supports the SQL Server Always On availability groups (AG) solution. When you create a schema, ALM creates a database backup and adds it to the availability group. This solution ensures that you can still use ALM when a database fails, because ALM switches automatically and seamlessly to a backup.

Overview of SQL Server AG support

The SQL Server AG feature is a high-availability disaster-recovery solution. This solution ensures a failover environment for a discrete set of databases. ALM supports this solution to maximize the availability of a set of databases at the enterprise level.

ALM supports the high availability groups only. Read-scale availability groups are not supported by ALM.

For details about this availability feature, see the Microsoft documentation.

Back to top

Before you use the solution

Before you migrate projects to your availability group in the production environment, try it in your test environment and do performance test to make sure the SQL Server Always on group cluster is capable of the workload.

Prioritize the projects to migrate

For a 4 core SQL Server Always on availability group instance, to avoid worker thread exhaustion, we recommend the maximum number of active projects is 70. Therefore, prioritize the projects to migrate. Focus on your important projects that cannot afford downtime.

The project number limit is decided by the max worker threads used by Always On availability groups. For details about max worker threads, see the Microsoft documentation: Prerequisites, Restrictions, and Recommendations for Always On availability groups and Configure the max worker threads Server Configuration Option.
Extra disk and network I/O performance buffer You need to have extra disk and network I/O performance buffer in your SQL Server availability group cluster. Do performance tests after you migrate projects to your test environment to make sure the SQL Server Always On group cluster can handle your workload.

Back to top

Prerequisites

Prepare the following before enabling the SQL Server AG support.

  • Set up an SQL Server Always On availability group listener and an availability group cluster.

  • ALM connects to the group listener with JDBC. Make sure the database user has all the permissions, which are described in the User Permissions for Connecting ALM to a Microsoft SQL Database Server section of the installation guide. See Installation and upgrade.

  • If you are using SQL authentication, sync the SQL Server login user "td" from ALM to all the replicas before creating or importing projects in your Always On availability group.

Back to top

Enable SQL Server AG support

To enable the SQL Server AG support, add the following connection properties in the SQL Server connection URL.

Property Description
multiSubnetFailover Set this property to true when you connect to the group listener. This is required by SQL Server JDBC.
alwaysOnGroup Set this property to your availability group name. The property value tells ALM whether the ALM projects that are connected to the current database server are in Always On mode.
backupFolder

Set this property to the path of your backup folder on the SQL Server instances. All SQL Server instances must use the same path.

Note: In Windows, end the path with a backward slash (\). In Linux, end it with a forward slash (/).

SQL Server connection URL example:

Copy code 
jdbc:sqlserver://192.168.0.10;multiSubnetFailover=true;
alwaysOnGroup=alm_ag;backupFolder=\\storage01\SQLServerSync\

Back to top

Migrate a project to AG

Follow the instructions below to migrate a project to your availability group.

  1. Export a project. For details, see Export a project.

  2. Import the project to your availability group. For details, see Create a project by importing from a project file.

  3. Restore the project. For details, see Restore projects.

  4. Copy the project database to the Always On primary instance.

  5. Fully back up the project database (use the full recovery model) and run the following SQL command to update the database user information.

    The user "td" is for SQL authentication mode and the user "dbo" is for Windows authentication mode.

    Copy code 
    use yourdb;
    exec sp_change_users_login 'yourdb','td','td';
    exec sp_change_users_login ''yourdb','dbo','dbo';
  6. Add the database to your Always On availability group, and wait until it shows "synchronized" on all the replicas. Then you can use or upgrade your project.

Back to top

Troubleshooting

You may encounter the following issues when using ALM with SQL Server Always On availability groups.

Issue Cause and Solution
Unable to connect to your project or restart ALM after Always On availability groups failover.

This usually happens if you haven’t synchronized the SQL Server login user "td" to all the replicas of the group.

If you are using the SQL Server SQL password authentication, make sure the SID of the user "td" is identical on every replica.
Unable to convert your project to Unicode.

You need to convert the project to Unicode before migrating it to an availability group.

Alternatively, you need to select the Unicode project option when you create the project. After the project is created, you can’t convert it to Unicode in an availability group.

After you delete a project in Site Administration, its status is displayed as restoring in secondary replicas.

 The project database needs to be dropped manually by your DBA in secondary replicas after you delete a project.

Back to top

See also: