Installing EMS and configuring SQL always on HA (EMS 7.0.6 or older)

To install EMS and configure SQL always on HA:

1. Log in to DBSRV-1 using SQL Server Management Studio (SSMS), and create a SQL user:
   1. In Object Explorer, right-click Logins, then select New Login.
   2. Select SQL Server authentication.
   3. Enter the desired password.
   4. On the Server Roles page, select sysadmin. Click OK.
2. Repeat step 1 for DBSRV-2.
3. Install EMS on the EMS-1 and EMS-2 servers. For installation on both EMS servers, SQL server DBSRV-1 is used:
   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\share, 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.6.0358_x64.exe SQLServer=DBSRV-1 SQLUser=emsha SQLUserPassword= admin InstallSQL=0 ScriptDB=1 BackupDir=\\EMS-1\share 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.6.0358_x64.exe SQLServer=DBSRV-1\EMSNAMED SQLUser=emsha SQLUserPassword=admin InstallSQL=0 ScriptDB=1 BackupDir=\\EMS-1\share 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\share, 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.6.0358_x64.exe SQLServer=DBSRV-1 SQLUser=emsha SQLUserPassword=admin InstallSQL=0 ScriptDB=0 BackupDir=\\EMS-2\share 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.6.0358_x64.exe SQLServer=DBSRV-1\EMSNAMED SQLUser=emsha SQLUserPassword=admin InstallSQL=0 ScriptDB=0 BackupDir=\\EMS-2\share DBInitialSize=31MB DBInitialLogSize=4MB DBGrowth=11MB DBLogGrowth=11% DBLoginTimeout=31 DBQueryTimeout=61

4. Log in to DBSRV-1 using SSMS. After installation, EMS databases FCM and FCM_Default are created. Following are the prerequisites to add a database to an availability group. You must fulfill these prerequisites on both FCM and FCM_Default:
   1. Right-click the database, go to Options and set Recovery Model to Full.
   2. 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!';

   3. Right-click the database, go to Tasks, and take a full backup.
5. Add the databases to the availability group:
   1. Right-click the Availability Groups folder, and select New Availability Group Wizard.
   2. In the Availability group name field, enter the desired name.
   3. Select Database Level Health Detection. Click Next.
   4. On the Select Databases page, enter the password configured in step 5 for both databases. Click Refresh, then select the checkbox for each database. Click Next.
   5. 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.
   6. 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.

         

   7. On the Select Data Synchronization page, select Full Database and Log backup. Specify the file share path. Ensure that the file share location is not on the database servers and that the SQL user has read and write file system permissions. Click Next.
   8. 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.

      

6. EMS-1 and EMS-2 still point to SQL server DBSRV-1. The next step is to configure EMS servers to use the listener DNS name configured in the previous step to connect to the SQL instances. In this example, it is sqllistener:
   1. Log in to EMS-1.
   2. Go to C:\Program Files (x86)\Fortinet\FortiClientEMS.
   3. Open das.conf in a text editor, and update the Server field to sqllistener. Save the file:

      [dbConnection]
      Server = sqllistener
      Port = 1433
      TrustConnection = yes
      Username = 0625da39429be9d12fe0e047ec6db9a47149af8186a5f898922f2942d95bc1
      Password = 7dbca17d957aeec84bd3894e9388a5c1602b10c5c50524539cf63f6b591bdfa0e5c2da74
      User

   4. Open db.conf in a text editor, and update the Server field to sqllistener. Save the file:

      [Global]
      ProviderString=Provider=MSOLEDBSQL
      IntegratedCredentials=Trusted_Connection=yes
      SQLCredentials=Uid=[[User]];Pwd=[[Password]]
      SQLCredentialsGOLANG=user id=[[User]];password=[[Password]]
      Server=sqllistener
      Encrypt=
      TrustServerCertificate=yes
      User=ems
      Password=Enc 785df7b54fc91b4d1fcc79bfda7f61bcc73542b60b598ac24c35f506a00d6371
      BackupDir=\\GOLLUM\backup

   5. Stop all services on EMS-2 to avoid failover, then restart EMS services on EMS-1.
   6. Follow steps a-e for the EMS-2 server.