Question. How to configure Hosting Controller with SQL Server Always On
Always On in SQL Server is designed to meet the ever increasing need for high availability (HA) and disaster recovery. Always On was introduced with the release of SQL Server 2012. Always On makes use of AlwaysOn Availability Groups, that allow a discrete set of user databases, known as availability databases, to fail over together.
An availability group supports one set of primary databases and one to eight sets of corresponding secondary databases. Each set of availability database is hosted by an availability replica therefore each availability group supports one primary replica and up to eight secondary replicas. The set of databases in the primary replica failover to the secondary replica.
Hosting Controller supports AlwaysOn Availability Groups for high availability and disaster recovery for SQL databases. The following explains the process of configuring and using availability groups in HC.
- Create an availability group
An availability group needs to be created manually before it can be configured with HC. Create an availability group having a primary replica with primary databases and a secondary replica with secondary databases.
- Add availability group to HC
To take advantage of Always On availability groups, an availability group needs to be added to HC. The availability group is added as a SQL Server instance. Even multiple instances of availability groups are supported however for each availability group a separate SQL Server instance needs to be added.
Instance Name: Provide the availability group listener name. The name should include the domain name.
Select Database Path: Use the default database path on all replicas (to ensure backup and transfer of databases).
SQL Server IP Address: Specify the IP address of availability group listener (this should be a static IP which is also added in DNS on creation of listener).
SQL Server Port: Use the default port of listener (Port: 1433).
SQL Server Admin User: Provide the admin user (sa) credentials.
Admin Password: Provide admin (sa) password.
Verify & Save: Click the Verify & Save button. It will check if this SQL instance is on high availability and will save the credentials.
- Create Databases
Once availability group is configured, create databases through HC. All databases created through HC will be highly available through Always On.
The primary availability databases will have the (Synchronized) label in front of them.
The database users under the primary replica will exist under Logins.
The secondary availability databases will also have the (Synchronized) label in front of them.
The database users under the secondary replica will also exist under Logins.