Active Roles can use the AlwaysOn Availability Groups feature introduced in Microsoft SQL Server 2012 to improve the availability of the Administration Service. With the AlwaysOn Availability Groups feature, SQL Server provides a failover environment, known as an availability group, for a set of availability databases that fail over together from one SQL Server instance to another. You can add the Active Roles database to an availability group, and have the Administration Service automatically reconnect to the database when the availability group fails over to another SQL Server instance.
An availability group defines a set of availability replicas to host copies of each availability database. Each availability group has at least two availability replicas: the primary replica and a secondary replica. The primary replica hosts the read-write copy of each availability database held in the availability group; a secondary replica hosts a read-only copy of each availability database, and serves as a potential failover target for the availability group. During a failover, a secondary replica transitions to the primary role, becoming the new primary replica. The new primary replica brings its databases online as the primary databases for read-write access.
Adding the Active Roles database to an availability group helps ensure uninterrupted operation of the Active Roles Administration Service. If a server or software failure occurs on the SQL Server side, the availability group can instantly switch the database to a secondary replica, enabling the Administration Service to reconnect seamlessly to the database in the new location.
For detailed information about the AlwaysOn Availability Groups feature, see AlwaysOn Availability Groups (SQL Server)
Important Note: The Active Roles database must first be configured in a standard standalone database configuration as described in the Active Roles Quick Start Guide. Once the database has been configured, it can then be added to an Availability Group by following the guidance below, or by following the steps in the Active Roles Administration Guide
Availability Group Setup in Active Roles
Suppose you have the Active Roles Administration Service installed and configured as described in the Active Roles Quick Start Guide. This section provides instructions on how to configure the Administration Service to use the database that belongs to an availability group (an availability database).
Note that the Administration Service whose database belongs to an availability group cannot participate in Active Roles replication. Active Roles does not support the replication function for availability databases. If you attempt to perform the “Promote to Publisher” or “Add Subscriber” operation with the Administration Service connected to an availability database, you receive an error.
For instructions on how to configure an availability group, and how to add a database to an availability group, refer to Microsoft's documentation (see “Getting Started with AlwaysOn Availability Groups (SQL Server"). We also assume that Active Roles replication is not configured, neither for Configuration data nor for Management History data.
Here we assume that the Active Roles database is already added to an availability group on SQL Server. Under these conditions, you can configure the Administration Service to connect to the database via the availability group listener. By using the listener, the Administration Service can connect to the current primary replica of the availability group that holds the Active Roles database without knowing the name of the physical instance of SQL Server that hosts the primary replica. The listener enables support for failover redirection. In case of a failover, the listener automatically redirects the Administration Service's connections to the new primary replica.
Configuring the database connection to use the listener
To enable the use of the availability group listener, you need to modify the database connection setting of the Administration Service. You can specify the availability group listener in the SQL Server field on the Change Active Roles Database wizard pages provided by Active Roles Configuration Center.
Depending upon the location of the Management History database in your Active Roles environment, you need to specify the listener in the SQL Server field on the Connection to Database page, on the Connection to Management History Database page, or on both pages. The value in the SQL Server field must identify the DNS host name and, optionally, the TCP port of the listener of the availability group to which the database belongs. For example, if the DNS host name of the listener is AGListener and the TCP port used by this listener is 1234, the value is AGListener,1234. You may omit the port number in case of the default port, 1433.
Case 1: Management History data stored in the Configuration database
Normally, Active Roles uses the same database to store both the Configuration data and Management History data. In this case, specify the listener of the availability group that holds the Configuration database in the SQL Server field on the Connection to Database page in the Change Active Roles Database wizard.
To specify the listener
Case 2: Management History data stored in a separate database
Active Roles provides the option to store the Management History data in a separate database. If you use this option, then you have two databases, the Configuration database and the Management History database, each of which (or both) can belong an availability group. In this case:
To specify the listener