Installing EMS and configuring SQL always on HA

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.

When installing EMS using remote databases, the dbuser/windowsuser must have one of the following roles on the database:

• dbcreator and serveradmin
• dbcreator and processadmin
• sysadmin

This is because the installation creates the database. If desired, you can revoke these db roles after the installation is complete.

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. EMS 7.2 does not rely on FILESTREAM for file synchronization between EMS nodes. Instead, it uses network share. Install EMS:
   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. After installation, the W:\ drive is also used to store FortiClient installation files for future FortiClient deployments.

   2. You must disable Network access: Do not allow storage of passwords and credentials for network authentication in Local Group Policy Editor. Otherwise, it causes installation issues. Do the following on both EMS nodes:

      1. Open Local Group Policy Editor.
      2. Go to Local Computer Policy > Windows Settings > Security Settings > Local Policies > Security Options.
      3. Double-click Network access: Do not allow storage of passwords and credentials for network authentication.
      4. Select Disabled, then click OK.
   3. On EMS-1, open Command Prompt as an administrator.
   4. Run the following command:

      FortiClientEndpointManagementServer_7.2.4.0983_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.2.4.0983_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

   5. 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.2.4.0983_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 the upgrade does not execute scripts to upgrade the database because you upgraded the database in step c.
      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.2.4.0983_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

3. Log in to DBSRV-1 using SSMS. Right-click the database and execute the following query: 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'. This query applies the password that you created to open the primary key. The following gives 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.

You must configure a maintenance plan for this deployment to prevent the log file from growing over time, since SQL server HA uses the FULL recovery model.

You must enable Change permissions for backup directories.