Installing EMS and configuring SQL always on HA (EMS 7.0.7 or newer)

For EMS 7.0.7 or newer, you can perform the installation by directly pointing to the SQL listener instead of first installing EMS outside of the availability group and later changing the EMS configuration files to point to the SQL listener.

Creating a site automatically adds the site database to the availability group. Deleting a site automatically removes the site database from the availability group.

To install EMS and configure SQL always on HA:

1. To create an always on high availability (HA) group, you need a database in your instance. Log in to the DBSRV-1 instance using SQL Server Management Studio (SSMS):
   1. Create two empty databases, FCM and FCM_Default. These are default EMS databases. Ensure that the database names are exactly as mentioned.
   2. Right-click each database, then go to Tasks. Take a full backup. This is a prerequisite to add a database to an availability group.
   3. Right-click the database. Go to options and ensure that Recovery Mode is set to Full. This is a prerequisite to add a database to an availability group.
   4. Right-click the Availability Groups folder. Select the New Availability Group Wizard option.
   5. In the Availability group name field, enter the name of the availability group. Enable Database Level Health Detection, then click Next.
   6. On the Select Databases page, select the FCM and FCM_Default database checkboxes to include them in the availability group, then click Next.
   7. On the Replicas tab, do the following:
      1. Click Add Replicas. Connect to the other SQL Server instances previously joined as nodes with the Windows Server failover cluster. In this example, it is DBSRV-2.
      2. Enable Automatic Failover.
      3. From the Availability Mode dropdown list, select Synchronous commit.
      4. For Readable Secondary, select Yes.
   8. On the Listener tab, enter the following details:
      1. In the Listener DNS Name field, enter the name that you will configure later in EMS configuration files.
      2. In the Port field, enter the desired port. This example uses the default SQL port, 1433.
      3. Add a virtual IP address for both subnets. A single subnet environment requires only one IP address. Click Next.

         

   9. For Data Synchronization, select Automatic Seeding. Click Next.
   10. Verify that validated checks succeed. At this point, the ems availability group has been created. FCM and FCM_Default are added to the high availability group. You can see the that the databases are synchronized. DBSRV-1 is the primary replica and DBSRV-2 is the secondary replica. On the Active Directory server, you can see that a sqllistener computer account is created and tied to provided virtual IP addresses.

       

2. Install EMS on the EMS-1 and EMS-2 servers. For installation on both EMS servers, SQL server DBSRV-1 is used. Do one of the following:

   1. If using EMS 7.0.8 or a later version, do the following. EMS 7.0.8 and later versions do not rely on FILESTREAM for file synchronization between EMS nodes. Instead, it uses network share. Do the following:

      1. Create and share a folder on the network. This file share is used to share files between EMS nodes. All EMS nodes should be able to access the file share. During EMS installation, the installer mounts the file share as the W:\ drive. Ensure that the W:\ drive is free on all EMS nodes.

      2. On EMS-1, open Command Prompt as an administrator.

      3. Run the following command:

         FortiClientEndpointManagementServer_7.0.8.0484_x64.exe SQLServer=sqllistener SQLUser=emsha SQLUserPassword=123456789 InstallSQL=0 ScriptDB=1 FileStorageNic=\\Server\fileshare FileStorageNicUser=LAB\administrator FileStorageNicPass=Admin123! BackupDir=\\EMS-1\backup DBInitialSize=31MB DBInitialLogSize=4MB DBGrowth=11MB DBLogGrowth=11% DBLoginTimeout=31 DBQueryTimeout=61

         Parameter	Description
         ScriptDB=1	Specifies that this is the primary node.
         BackupDir	Configured to \\EMS-1\backup, which is a locally shared folder on EMS-1. EMS and the SQL service user must have read/write/modify permissions to this folder.
         FileStorageNic	Fileshare path.
         FileStorageNicUser	Username for account with read/write/modify permissions to the shared folder.
         FileStorageNicPass	Password for account with read/write/modify permissions to the shared folder.

         The following is an example of the command when using a named SQL instance. In this example, the SQL instance is EMSNAMED: FortiClientEndpointManagementServer_7.0.8.0484_x64.exe SQLServer=sqllistener\EMSNAMED SQLUser=emsha SQLUserPassword=123456789 InstallSQL=0 ScriptDB=1 FileStorageNic=\\Server\fileshare FileStorageNicUser=LAB\administrator FileStorageNicPass=Admin123! BackupDir=\\EMS-1\backup DBInitialSize=31MB DBInitialLogSize=4MB DBGrowth=11MB DBLogGrowth=11% DBLoginTimeout=31 DBQueryTimeout=61

      4. On EMS-2, open Command Prompt as an administrator. Run the following command:

         You must use a unique backup directory for each EMS node. The following shows BackupDir values for an example HA configuration with one primary (EMS 1) and two secondary EMS nodes (EMS 2 and 3): Primary (EMS 1): BackupDir=\\EMS-1\backup Secondary (EMS 2): BackupDir=\\EMS-2\backup Secondary (EMS 3): BackupDir=\\EMS-3\backup All EMS nodes share the same FileStorageNic.

         FortiClientEndpointManagementServer_7.0.8.0484_x64.exe SQLServer=sqllistener SQLUser=emsha SQLUserPassword=123456789 InstallSQL=0 ScriptDB=0 FileStorageNic=\\Server\fileshare FileStorageNicUser=LAB\administrator FileStorageNicPass=Admin123! BackupDir=\\EMS-2\backup DBInitialSize=31MB DBInitialLogSize=4MB DBGrowth=11MB DBLogGrowth=11% DBLoginTimeout=31 DBQueryTimeout=61

         Parameter	Description
         ScriptDB=0	Indicates that the upgrade does not execute scripts to upgrade the database because you upgraded the database in step iii.
         BackupDir	Configured to \\EMS-2\backup, which is a locally shared folder on EMS-2. EMS and the SQL service user must have read/write/modify permissions to this folder.
         FileStorageNic	Fileshare path.
         FileStorageNicUser	Username for account with read/write/modify permissions to the shared folder.
         FileStorageNicPass	Password for account with read/write/modify permissions to the shared folder.

         The following is an example of the command when using a named SQL instance. In this example, the SQL instance is EMSNAMED: FortiClientEndpointManagementServer_7.0.8.0484_x64.exe SQLServer=sqllistener\EMSNAMED SQLUser=emsha SQLUserPassword=123456789 InstallSQL=0 ScriptDB=0 FileStorageNic=\\Server\fileshare FileStorageNicUser=LAB\administrator FileStorageNicPass=Admin123! BackupDir=\\EMS-2\backup DBInitialSize=31MB DBInitialLogSize=4MB DBGrowth=11MB DBLogGrowth=11% DBLoginTimeout=31 DBQueryTimeout=61

   2. If using EMS 7.0.7, do the following:
      1. On EMS-1, open Command Prompt as an administrator. Run the following command. ScriptDB=1 indicates that this is the primary, active server. BackupDir is configured to \\EMS-1\backup, which is a locally shared folder on EMS-1. EMS and the SQL service user must have read/write/modify permissions to this folder:

         FortiClientEndpointManagementServer_7.0.7.0398_x64.exe SQLServer=sqllistener SQLUser=emsha SQLUserPassword=123456789 InstallSQL=0 ScriptDB=1 BackupDir=\\EMS-1\backup DBInitialSize=31MB DBInitialLogSize=4MB DBGrowth=11MB DBLogGrowth=11% DBLoginTimeout=31 DBQueryTimeout=61

         The following shows an example of the command when using a named SQL instance. In this example, the SQL instance is EMSNAMED:

         FortiClientEndpointManagementServer_7.0.7.0398_x64.exe SQLServer=sqllistener\EMSNAMED SQLUser=emsha SQLUserPassword=admin InstallSQL=0 ScriptDB=1 BackupDir=\\EMS-1\backup DBInitialSize=31MB DBInitialLogSize=4MB DBGrowth=11MB DBLogGrowth=11% DBLoginTimeout=31 DBQueryTimeout=61

      2. On EMS-2, open Command Prompt as an administrator. Run the following command. ScriptDB=0 indicates the upgrade will not execute scripts to upgrade the database, because you upgraded the database in step a. BackupDir is configured to \\EMS-2\backup, which is a locally shared folder on EMS-2. EMS and the SQL service user must have read/write/modify permissions to this folder:

         You must use a unique backup directory for each EMS node. The following shows BackupDir values for an example HA configuration with one primary (EMS 1) and two secondary EMS nodes (EMS 2 and 3): Primary (EMS 1): BackupDir=\\EMS-1\backup Secondary (EMS 2): BackupDir=\\EMS-2\backup Secondary (EMS 3): BackupDir=\\EMS-3\backup All EMS nodes share the same FileStorageNic.

         FortiClientEndpointManagementServer_7.0.7.0398_x64.exe SQLServer=sqllistener SQLUser=emsha SQLUserPassword=123456789 InstallSQL=0 ScriptDB=0 BackupDir=\\EMS-2\backup DBInitialSize=31MB DBInitialLogSize=4MB DBGrowth=11MB DBLogGrowth=11% DBLoginTimeout=31 DBQueryTimeout=61

         The following shows an example of the command when using a named SQL instance. In this example, the SQL instance is EMSNAMED:

         FortiClientEndpointManagementServer_7.0.7.0398_x64.exe SQLServer=sqllistener\EMSNAMED SQLUser=emsha SQLUserPassword=admin InstallSQL=0 ScriptDB=0 BackupDir=\\EMS-2\backup DBInitialSize=31MB DBInitialLogSize=4MB DBGrowth=11MB DBLogGrowth=11% DBLoginTimeout=31 DBQueryTimeout=61

3. Right-click the database and execute the following query. You must execute the query for both the FCM and FCM_Default databases:

   ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '...';

   For example, you can enter the following:

   ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'SQLHA123!';

4. Execute sp_control_dbmasterkey_password @db_name = N'db_name', @password = N'Password' , @action = N'add' for both EMS databases. This query applies the password that you created earlier to open the master key. The following give examples of this query:

   sp_control_dbmasterkey_password @db_name = N'FCM’, @password = N'SQLHA123!' , @action = N'add'

   sp_control_dbmasterkey_password @db_name = N'FCM_Default’, @password = N'SQLHA123!' , @action = N'add'

5. Log in to DBSRV-2 using SSMS. Repeat step 4 on DBSRV-2.