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, 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
AG2000*	The configured user is missing the VIEW SERVER STATE permission.	Provide VIEW SERVER STATE permission to the configured user.
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 happens when AutoSeeding is chosen while replicating the database.)	Run the below command from the <APP_HOME>bin directory: Windows: verifyAG.bat true Linux: verifyAG.sh true

For more information on AG configurations, click here.

* For environments that are not configured with prerequisites or Basic AG configuration:

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.