Setting Up Always On Availability Groups

This section describes the prerequisites and procedures for the following operations:

Creating a Windows Server Failover Cluster (WSFC)

Prerequisites: Set up two or more identical machines (physical or virtual), each having the same configuration (same OS version, same SQL Server version, etc.).

A Windows Server Failover Cluster (WSFC) consists of two or more nodes, which are identical physical (or virtual) machines that host the same OS version, same OS patch version, and same SQL Server version.

On each machine of the cluster:

  1. Log in as domain Administrator.

    Note: If you do not have a Domain Admin account, you can use a Domain Users account that is in the Administrators group on each server in the cluster. In this case, the account (or the group that the account is a member of) must be assigned Create Computer Objects and Read All Properties permissions for the domain.

  2. Update the Active Directory domain membership to the same domain for all nodes.

  3. Open the Server Manager and install the Failover Clustering feature.

Then, on one of the machines in the cluster:

  1. Open the Management console and select the Failover Cluster Manager.

  2. Run all validation tests and review the report to make sure all the tests were passed.

    Note: You can ignore any warnings due to the absence of a storage disk or due to unavailability of a secondary network connection between the nodes.

  3. Run the Create Cluster Wizard until the Confirmation page.

  4. Review the Confirmation page. If you choose not to add all eligible storage to the cluster, you will be able to add specific disks after the cluster is created.

    Note: After the WSFC cluster is created, and if the quorum configuration is dynamic (default setting), the following warning may be displayed to remind the administrator that configuring a quorum is recommended:

    An appropriate disk was not found for configuring the disk witness. The cluster is not configured with a quorum witness. As a best practice, configure a quorum witness to help achieve the highest availability of the cluster.

Creating an Always On Availability Group

This group consists of two or more replicas: one primary replica to be targeted by ActivID CMS, and secondary replicas to stand ready for failover.

For more information about creating Always On availability groups, refer to the Microsoft SQL Server documentation at the following URL:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/getting-started-with-always-on-availability-groups-sql-server

Enabling the Always On Availability Group

On each cluster node, open the SQL Server Manager:

  1. Go to SQL Services, right-click on the SQL Server service with the appropriate instance name and click on Properties.

  2. In the Always On High Availability tab, select the Enable AlwaysOn High Availability Groups option and click OK.

  3. Restart the SQL Server service.

Creating the Database Mirroring Endpoint

You can use the New Availability Group wizard to create the database mirroring endpoint. However, you must configure the SQL Server and SQL Server Agent services on each availability group replica to log in with a privileged domain account. This enables each replica to use Windows Authentication to log in to each other’s database mirroring point.

For more information about the database mirroring endpoint, refer to the Microsoft SQL Server documentation at the following URL:

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/the-database-mirroring-endpoint-sql-server

Installing ActivID CMS and SQL Server Management Studio

On a specific ActivID CMS server machine:

  1. Install ActivID CMS to target the SQL Server instance on Cluster node #1.

  2. Start ActivID CMS once to ensure that CMS starts successfully and that all the database objects are created properly.

  3. Stop ActivID CMS.

  4. Install SQL Server Management Studio corresponding to your SQL server.

Configuring Full Recovery

On the ActivID CMS server machine, in the SQL Server Management Studio:

  1. Connect to the SQL Server instance on Cluster node #1 where the ActivID CMS databases were created.

  2. Right-click on each database, select Properties and in Options, select Full in the Recovery model combo box.

  3. For each database, perform a full back-up in order to initiate the full-recovery log chain.

Run the New Availability Group Wizard

On the ActivID CMS server machine, in the SQL Server Management Studio:

  1. Launch the New Availability Group Wizard

  2. In the Specify Options menu, type the Availability Group name and select the WSFC cluster type.

  3. Uncheck the Database Health Detection check box if you want to avoid triggering automatic failover when the database is no longer in the online status.

  4. In the Select Databases menu, select all the ActivID CMS databases and click Next.

    Note: All databases must have been configured with full recovery mode and must have been fully backed up at least once.

  5. In the Specify Replicas menu of the Replicas tab, for the primary replica, check only Synchronous Commit if you want to favor data integrity over transaction latency, and select Yes for Readable.

  6. Add the secondary replica(s) with the same parameters.

  7. In the Endpoints tab, you can leave the default settings.

  8. In the Backup Preferences tab, you can leave the default Prefer Secondary option.

  9. In the Listener tab, you can leave the default Do not create an availability group listener now option.

  10. In the Select Data Synchronization menu, you can select Automatic Seeding.

  11. In the Validation menu, check that there are no errors.

Creating an Availability Group Listener

An availability group listener permits ActivID CMS to be able to target the newly-created Always On availability group.

On the ActivID CMS server machine, in the SQL Server Management Studio:

  1. Connect to the primary replica of the availability group.

  2. In the Always On High Availability menu, expand the Availability Groups, select the availability group, right-click on Availability Group Listeners and select Add Listener.

  3. Select a name (for example, CMS-AG-Listener), a port (for example, 1433), a network mode (Static IP) and add a new static IP address dedicated to the listener for each desired subnet (if replicas belong to multiple subnets).

  4. Set the SQL port used on Cluster node #1 and Cluster node #2 to a custom port (for example, 50000).

  5. Configure ActivID CMS to connect to this custom port.