Configuring SQL Database for AlwaysOn Availability feature

Contents

• What is AlwaysOn Availability Group?
• How Celiveo supports AlwaysOn availability?
• Enabling multi-subnet failover during Celiveo installation
• Enabling multi-subnet failover and other options during database configuration

What is AlwaysOn Availability Group?
Microsoft SQL Server’s AlwaysOn feature was developed to as solution to support “always” availability of databases during back-end failure or any other disaster, in a corporate (multi-network) environment.
An AlwaysOn Availability Group comprises of the following components:

• Availability Group — Collection of user databases that is being protected against failover.
• Primary replica —This is an instance of the primary SQL Server that hosts the databases that needs to be protected. In case of Celiveo, the databases to be protected from failover are Celiveo database and Track-Green Saver databases. This node will have read and write permissions on a regular day.
• Secondary replica(s) —These are instances (one or more replicas) of the primary SQL Server, which host a set of replicated databases, updated consistently so as to take-over if the primary replica fails. These nodes will have only read permission on a regular day.
• Listener —This provides access for client applications to the primary and secondary databases in AG and enables automatic client re-connection in the event of a failover.

Methods of synchronizing database nodes

• Asynchronous-commit mode — In this method, updates from the primary node are sent asynchronously to the secondary nodes.
• Synchronous-commit mode — In this method, secondary nodes are updated consistently with the primary node. This mode of synchronization is high availability.

An Availability Group consists of independent SQL Server instances residing on distinct Windows Server instances (nodes) within a Windows clustering environment; working together to protect a set of user databases (primary database) known as Availability databases. These Availability databases can support up to five secondary replicas of the primary database. The primary replica having read/write privileges consistently updates the secondary replicas (with read-only access) either via synchronous or asynchronous mode.

In the event of a failure on the primary database, all of the databases within an Availability Group will fail over together and become active on the designated failover (secondary) replica. This secondary SQL Server instance will now become the primary replica with read/write privileges.

Enabling Multi-Subnet Failover option during Celiveo installation

Thanks to the Microsoft technology, Celiveo has enhanced its features to support AlwaysOn Availability Groups in a multi-subnet environment. If you already have an AlwaysOn Availability Group configured, then enable the Multi-Subnet Failover option during installation to enable the Microsoft SQL Server MultiSubnetFailover configuration:

Follow the installation instructions provided here.

Enabling multi-subnet failover in a multi-SQL environment
After Celiveo installation, if you wish to create a database profile (primary or secondary) to support AlwaysOn Availability groups, follow the instructions given below:

In Web Admin:

1. Click Setup icon at the top right-hand corner of the screen.
2. Select the Database Configuration tab.
3. Click the [+Add] button.
   
4. Enter the following information for creating the database profile:
   • Profile Name: This is the name of the database profile
   • DB Server Name: The server name is either the [computer name] or [IP address]. E.g. CELIVEO SRVR, 192.68.3.211
   • DB Instance: For the default instance of SQL Server, leave this field empty. For a named instance of SQL Server, provide the instance name.
     Note: If SQL Server is installed using the Celiveo installer (during installation), the instance name is “SQLExpress”.
   • DB Username: Type the user name with admin access privileges for this database.
   • DB Password: Type the corresponding password.

Dual Service Account System

To avoid any connection error after refreshing/changing the login/password on service accounts used by Celiveo, the administrator can define a secondary set of credentials so that if the default (primary) set is declined by the solution, then the secondary set takes over and prevents the access from being denied.

5. Options
   • SQL Encryption (SSL over SQL)
     • Enable Encryption: Select this option to secure the connection between Web Admin application and the specified SQL server. This allows authorized communication to occur between them. The SSL security layer prevents unwanted sensitive data leak, and/or excludes the possibility for any SQL injection attack.
       • Make sure encryption is enabled on the Server. To do this:
         1. Open SQL Server Configuration Manager, go to SQL Server Network configuration.
         2. Choose Protocols properties for the SQL Server instance and enable ForceEncryption option in Flags tab.
            

• AlwaysOn Availability Groups
  • Multi-Subnet Failover: When enabled, Web Admin attempts parallel connections to the failover IP addresses of an (Always On) Availability Group during a multi-subnet failover.
• Connection Timeout:
  • This denotes the time limit (in seconds), within which the connection to the specified SQL server must be made before terminating the attempt.

6. Define tags for your database profile:
   
7. Click the Test button to perform a test on the database.
   A confirmation message displays if the test is successful. The Save button becomes available.
8. Select Synchronize database after save checkbox to synchronize with the primary database when you click [Save].
   This will synchronize and create a replica of primary database as the secondary database.
9. You can also synchronize the secondary database later using the [Sync] icon in the [DB Settings].
10. A confirmation prompt displays. Click OK.
11. During Synchronization, all unnecessary data is deleted from the secondary database.
12. Once Synchronization is complete, a confirmation message displays.
    

©2024 Celiveo Pte Ltd – All Rights Reserved