Microsoft SQL Availability Groups

Availability groups (AGs) is a high availability cluster of MSSQL servers. It serves as a backup in case the DB server fails.
The DB server stores attachments, notifications, and external resources. If there is a DB server failure, one server from the Microsoft SQL cluster acts as the DB server to ensure uninterrupted operation of the ServiceDesk Plus application.

Configuring Always On Availability Groups

Prerequisites for AG Configuration

The user to be configured in the changeDBServer should have VIEW SERVER STATE permission.
The secondary node should be set to "Readable/Read-Intent Only".

For environments that are not configured with prerequisites or Basic AG configuration or environments where the CONTROL SERVER permission cannot be provided, click here.

1. Creating database in the Primary Node (Skip this step if it's not a fresh installation)

In the ServiceDesk Plus bin directory, execute the changeDBServer.bat [changeDBServer.sh for Linux] command.
The Database Setup Wizard will open. Use the pointers below to fill in the required server details:

Server Type: Select the server type as SQL Server.
Host Type: Select Default as the Host Type.
Host Name: Enter the Primary Node where the database should be created.
Port: Specify the SQL Server port number. The default port number is 1433.
Available SQL Server Instances: The primary server name will be auto-populated. If not, select the primary server from the drop-down.
Database: By default, the database name is set to servicedesk. Edit, if required.
User Name and Password: Specify login credentials for the server.
Master Key Password: Enter the master key password to encrypt the SCHAR columns in the database. This is required only during database creation.

Users with DBCreator / DBOwner permission can create a master key. To learn more, click here.

To check the availability of the connection,

Click Test. If successful, a pop-up window will display a connection established message.
Click OK to proceed.
Click Save to save the SQL server settings.

2.Replicating Database (Skip this step if database is already replicated):

After creating the database, start and stop the ServiceDesk Plus application.
Replicate the database at SQL Server by navigating to Always On High Availability > Availability Groups > Group > Availability Databases > Add Database.

When configuring AG in the SQL Server Management Studio wizard,
In the Select Initial Data Synchronization page, choose Full database and log backup. This will store the database master key password in the credentials of both the nodes.

3. Configuring AG using changeDBServer

In the ServiceDesk bin directory, execute the changeDBServer.bat [changeDBServer.sh for Linux] command.
The Database Setup Wizard will open. Use the pointers below to fill out the required server details:

Server Type: Select SQL Server.
Host Type: Select AG as the Host Type.
Host Name: Enter the IP Address/name of the Virtual Listener.
Port: Specify the SQL Server port number. The default port number is 1433.
Database: By default, the database name is set to servicedesk. Edit, if required.
User Name and Password: Specify the login credentials for the server.
Master Key Password: Enter the master key password configured during database creation.

To check the availability of the connection,

Click Test. If successful, a pop-up window displays the connection established message.
Click OK to proceed.
Click Save to save the SQL server settings.

AG Validation:

While saving the AG configuration, AG verification takes place. If issues are detected during verification, predefined error codes are displayed.

Error Code Details:

Error Code	Description	Recommended Action
AG2001*	The secondary node is not readable.	Make the secondary node as readable/Read-Indent Only.
AG2002*	The individual nodes are unreachable, or the port mismatches the listener port.	Check whether the SQL Server port on individual node is blocked by the firewall configuration. Check whether the SQL Server Browser service is running on all the individual nodes
AG1000	A physical node is configured instead of a virtual listener.	Configure the virtual listener in the Host Name field.
AG1001	The target database is not replicated in the nodes.	Follow the replication steps mentioned in step 2 to replicate the database across the nodes.
AG1002	The database master key is missing in the database of a node.	If these errors appear in Health Meter in AG configuration, run verifyAG.bat/verifyAG.sh and share the output with ServiceDesk Plus support. If these errors appear in changeDBServer configuration, share the logs folder and the error message with ServiceDesk Plus support.
AG1003	Certificate is missing in the database of a node.
AG1004	Symmetric key is missing in the database of a node.

AG1008	The credential store entry is missing in the node. This usually occurs when AutoSeeding is selected during database replication.	Run the command below from the <APP_HOME>bin directory: Windows: verifyAG.bat true Linux: verifyAG.sh true If you get the AG3005 error, click here.
AG2000*	The configured user does not have the VIEW SERVER STATE permission.	Provide the necessary permission to the configured user. Find the attached query.
AG3001	The configured user does not have the VIEW ANY DEFINITION permission.
AG3002	The configured user does not have SELECT permission on sys.credentials.
AG3003	The configured user does not have SELECT permission on sys.master_key_passwords.
AG3004	The configured user does not have SELECT permission on msdb.dbo.backupset.
AG3005	The configured user does not have the CONTROL SERVER permission.	Provide the CONTROL SERVER permission to the configured user. Or Follow the attached steps to resolve it. Since CONTROL SERVER is a high-privilege permission and is configured only once, a revert query is also provided.

For more information on Availability Group (AG) configurations, click here.

Query to resolve error codes AG2000, AG3001, AG3002, AG3003, and AG3004

Replace <user> with the configured user.

/*
<user> permissions setup - Fixed version (no GO inside TRY/CATCH)
Run as sysadmin/sa on EACH replica/node.
The below script will:
1) Grant necessary server-level permissions (VIEW ANY DEFINITION, VIEW SERVER STATE)
2) Create database users in master and msdb if they don't exist, and fix orphaned users if needed
3) Grant SELECT on sys.credentials and sys.master_key_passwords in master, and msdb.dbo.backupset in msdb
*/

DECLARE @ErrorMsg NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
-- declare the user and login names as variables for easier maintenance
DECLARE @LoginName NVARCHAR(128) = '<user>';

BEGIN TRY
    -- Server-level permission for metadata visibility
    IF NOT EXISTS (
        SELECT 1 FROM sys.server_permissions sp
        INNER JOIN sys.server_principals sp2 ON sp.grantee_principal_id = sp2.principal_id
        WHERE sp2.name = @LoginName AND sp.permission_name = 'VIEW ANY DEFINITION'
    )
    BEGIN
        EXEC ('GRANT VIEW ANY DEFINITION TO [' + @LoginName + ']');
        PRINT 'Granted VIEW ANY DEFINITION to ' + @LoginName;
    END
    ELSE
        PRINT 'VIEW ANY DEFINITION already granted to ' + @LoginName;

    -- Server-level permission for AG DMV queries
    IF NOT EXISTS (
        SELECT 1 FROM sys.server_permissions sp
        INNER JOIN sys.server_principals sp2 ON sp.grantee_principal_id = sp2.principal_id
        WHERE sp2.name = @LoginName AND sp.permission_name = 'VIEW SERVER STATE'
    )
    BEGIN
        EXEC ('GRANT VIEW SERVER STATE TO [' + @LoginName + ']');
        PRINT 'Granted VIEW SERVER STATE to ' + @LoginName;
    END
    ELSE
        PRINT 'VIEW SERVER STATE already granted to ' + @LoginName;

-- === MASTER DATABASE ===
EXEC ('USE master');

    -- Create user if it doesn't exist
    IF NOT EXISTS (SELECT 1 FROM master.sys.database_principals WHERE name = @LoginName)
    BEGIN
        EXEC ('USE master; CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + ']');
        PRINT 'Created user ' + @LoginName + ' in master';
    END
    ELSE
    BEGIN
        -- Fix orphaned user if needed
        IF NOT EXISTS (
            SELECT 1 FROM master.sys.database_principals dp
            INNER JOIN sys.server_principals sp ON dp.sid = sp.sid
            WHERE dp.name = @LoginName AND sp.name = @LoginName
        )
        BEGIN
            EXEC ('USE master; ALTER USER [' + @LoginName + '] WITH LOGIN = [' + @LoginName + ']');
            PRINT 'Fixed orphaned user ' + @LoginName + ' in master';
        END
        ELSE
            PRINT 'User ' + @LoginName + ' already exists and mapped correctly in master';
    END

    -- Grant SELECT on credentials (if not already)
    IF NOT EXISTS (
        SELECT 1 FROM master.sys.database_permissions dp
        INNER JOIN master.sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id
        WHERE dp2.name = @LoginName AND dp.major_id = OBJECT_ID('sys.credentials') AND dp.permission_name = 'SELECT'
    )
    BEGIN
        EXEC ('USE master; GRANT SELECT ON sys.credentials TO [' + @LoginName + ']');
        PRINT 'Granted SELECT on sys.credentials to ' + @LoginName;
    END

    IF NOT EXISTS (
        SELECT 1 FROM master.sys.database_permissions dp
        INNER JOIN master.sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id
        WHERE dp2.name = @LoginName AND dp.major_id = OBJECT_ID('sys.master_key_passwords') AND dp.permission_name = 'SELECT'
    )
    BEGIN
        EXEC ('USE master; GRANT SELECT ON sys.master_key_passwords TO [' + @LoginName + ']');
        PRINT 'Granted SELECT on sys.master_key_passwords to ' + @LoginName;
    END

-- === MSDB DATABASE ===
EXEC ('USE msdb');

    -- Create user if it doesn't exist
    IF NOT EXISTS (SELECT 1 FROM msdb.sys.database_principals WHERE name = @LoginName)
    BEGIN
        EXEC ('USE msdb; CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + ']');
        PRINT 'Created user ' + @LoginName + ' in msdb';
    END
    ELSE
    BEGIN
        -- Fix orphaned user if needed
        IF NOT EXISTS (
            SELECT 1 FROM msdb.sys.database_principals dp
            INNER JOIN sys.server_principals sp ON dp.sid = sp.sid
            WHERE dp.name = @LoginName AND sp.name = @LoginName
        )
        BEGIN
            EXEC ('USE msdb; ALTER USER [' + @LoginName + '] WITH LOGIN = [' + @LoginName + ']');
            PRINT 'Fixed orphaned user ' + @LoginName + ' in msdb';
        END
        ELSE
            PRINT 'User ' + @LoginName + ' already exists and mapped correctly in msdb';
    END

    -- Grant SELECT on backupset
    IF NOT EXISTS (
        SELECT 1 FROM msdb.sys.database_permissions dp
        INNER JOIN msdb.sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id
        WHERE dp2.name = @LoginName AND dp.major_id = OBJECT_ID('dbo.backupset') AND dp.permission_name = 'SELECT'
    )
    BEGIN
        EXEC ('USE msdb; GRANT SELECT ON dbo.backupset TO [' + @LoginName + ']');
        PRINT 'Granted SELECT on msdb.dbo.backupset to ' + @LoginName;
    END
    ELSE
        PRINT 'SELECT on msdb.dbo.backupset already granted to ' + @LoginName;

PRINT '=== ' + @LoginName + ' permissions setup COMPLETE ===';

    PRINT 'Reconnect as ' + @LoginName + ' and test your query';
END TRY
BEGIN CATCH
    SELECT
        @ErrorMsg = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);
END CATCH;

RETURN;

Steps to resolve error code AG3005

Step 1: Provide the CONTROL SERVER permission using the query below.

Replace <user> with the configured user.

/*
WARNING: CONTROL SERVER is a high-privilege server-level permission.
Execute the below GRANT script separately only when running verifyAG.bat/sh with true to add the credential store entry.
After the credential store entry is added successfully, execute the REVOKE script below to remove CONTROL SERVER.
*/

-- CONTROL SERVER GRANT SCRIPT - execute separately before verifyAG.bat/sh true
DECLARE @ControlServerLoginName NVARCHAR(128) = '<user>';

BEGIN TRY
    IF NOT EXISTS (
        SELECT 1 FROM sys.server_permissions sp
        INNER JOIN sys.server_principals sp2 ON sp.grantee_principal_id = sp2.principal_id
        WHERE sp2.name = @ControlServerLoginName AND sp.permission_name = 'CONTROL SERVER'
    )
    BEGIN
        EXEC ('GRANT CONTROL SERVER TO [' + @ControlServerLoginName + ']');
        PRINT 'Granted CONTROL SERVER to ' + @ControlServerLoginName;
    END
    ELSE
        PRINT 'CONTROL SERVER already granted to ' + @ControlServerLoginName;
END TRY
BEGIN CATCH
    SELECT
        @ErrorMsg = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);
END CATCH;

Step 2: Run verifyAG.bat/sh true.

Step 3: Run the revert query.

Replace <user> with the configured user.

CONTROL SERVER revoke script: Execute separately after verifyAG.bat/sh completes successfully.

DECLARE @RevokeControlServerLoginName NVARCHAR(128) = '<user>';

BEGIN TRY
    IF EXISTS (
        SELECT 1 FROM sys.server_permissions sp
        INNER JOIN sys.server_principals sp2 ON sp.grantee_principal_id = sp2.principal_id
        WHERE sp2.name = @RevokeControlServerLoginName AND sp.permission_name = 'CONTROL SERVER'
    )
    BEGIN
        EXEC ('REVOKE CONTROL SERVER FROM [' + @RevokeControlServerLoginName + ']');
        PRINT 'Revoked CONTROL SERVER from ' + @RevokeControlServerLoginName;
    END
    ELSE
        PRINT 'CONTROL SERVER is not granted to ' + @RevokeControlServerLoginName;
END TRY
BEGIN CATCH
    SELECT
        @ErrorMsg = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);
END CATCH;

For environments that are not configured with prerequisites or Basic AG configuration or environments where the CONTROL SERVER permission cannot be provided

Follow the procedure below for manual AG validation to check whether the AutoSeeding option is selected while replicating the database.

Run the following query on each individual node:

select sc.credential_id from sys.credentials sc
inner join sys.master_key_passwords sm on sm.credential_id= sc.credential_id
left join msdb.dbo.backupset md on md.family_guid = sm.family_guid
where md.database_name = '<database_name>';

If the query does not return any value, run the following query on the respective node:

EXEC sp_control_dbmasterkey_password @db_name = N'<database_name>'
, @password = N'<Master_Key_Password>'
, @action = N'add' ;

Replace the <database_name> and <Master_Key_Password> values accordingly before running the query.