Share this page

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

In this topic:

## 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:

- Upgrade projects

https://admhelp.microfocus.com ALM 8942
Tips to improve your search results... ALM Help Center ALM Help Center ALM Help 16.00-16.0.1 All View Local Help Center View Online Help Center back Go back in browser history forward Go forward in browser history Remove search highlights next topic Go to next topic previous topic Go to previous topic Print the current topic Version: