In a nutshell: each availability group node must be a member of a Failover Windows cluster. Each SQL Server instance can have several availability groups. Each availability group can have up to 8 secondary replicas.
What is an Always On availability group?
AlwaysOn Availability Groups are a high availability and disaster recovery solution and are an alternative to mirroring databases at the enterprise level. If your database cannot keep up with the volume of requests or if you are concerned that a server failure will result in the loss of valuable data, it makes sense to use this solution. AlwaysOn availability groups can fulfill two tasks at once: a high availability level ensures uninterrupted system operation, while the database read load is partially handled by replicas.
Creating an availability group might be necessary if you need the following:
-
Creating redundant availability of databases (in this case, we recommend placing nodes in geographically remote data centers, since redundant availability implies database availability in case of any technical failures on any node).
-
Increasing performance of database responses based on the horizontal expansion principle (in this case, one node in the cluster is a master that performs read-write operations, the remaining nodes work in the listener mode and allow to read data when requests are addressed.
If the primary replica fails, the cluster will elect a new primary replica and Always On will move one of the secondary replicas to primary status. Since users connect to the cluster listener (a specific cluster IP address and its corresponding DNS name) when working with Always On, the ability to make write requests is fully restored. The Listener is also involved in balancing select requests between secondary replicas.
Preparing the infrastructure
First, you need to create a virtual machine and users. Create 3 VMs in VDC, give names according to roles, make customization settings.
After that, move on to the step of configuring the domain controller. Install AD, DNS, Failover Cluster roles.
Set the domain controller role
Create the ND01 and ND02 computers in the AD.
On the ND01 and ND02 VMs, install the Failover Cluster component
Now let's create a Failover Cluster. On the DC01 domain controller, create a Failover Cluster and add our nodes to it.
Name the cluster.
When creating a cluster, untick the checkbox to add arrays to the directory. This setting can be done later.
The cluster creation is complete.
Creating a Quorum Witness Share
We proceed to setting up the quorum. To do this, select the items that are shown in the screenshot.
In the quorum witness configuration, specify file share.
After that, you need to create a directory on a server that is not part of the cluster, but shares a network with the cluster. After creating such a directory and adding shares for nodes from the cluster to access it, you must specify the UNC path in the witness setup.
If after creating the witness you get an error as in the example below,
...in this case you need to check the permissions settings for the network directory specified in the witness settings.
Move on to installing MS SQL 2015 Enterprise on the nodes in the cluster. Before installing the module, you must disable the domain firewall on all VMs participating in the cluster.
Install MS SQL in standalone mode, without any additional modules. When selecting a user as an example, take Domain Network Administrator. For the production Servers, we recommend making a separate user. Perhaps I don't need to explain why this is important.
Then we need to install SQL Management Studio on both nodes in the cluster.
Adding a test database in MSSQL
On the ND01 node install test pattern database. The name of the test database will be Bike-Store. The test database is taken from here.
After installing the database, select the created database, then select the database file with the combination Ctrl+O.
After opening the file press "Run".
When you have added a new database, you need to fill it. To do this, open the BikeStores Sample Database - load data.sql file and add it using the same method. At the end of the operation you should see the message "The query was successfully completed".
Important: Before deploying an availability group, be sure to make a backup copy of the database, otherwise you will not be able to create an availability group.
Configuring Always On in MS SQL Server
For each node, you must enable support for the AlwaysON scheme in SQL Server Configuration Manager in the instance properties.
On the ND01 node in SQL Server Management Studio, select the "Always On High Availability" node and run the New Availability Group Wizard.
Name your accessibility group: BikeStores-AG
Click "Add replica" and connect to the second SQL server. This way you can add up to 8 servers.
Key parameters
-
Initial Role - the role of the replica at the time the group was created. It can be Primary or Secondary;
-
Automatic transition - If the database becomes unavailable, Always On will transfer the primary role to another replica. Check the box;
-
Availability mode - it is possible to select Synchronous Commit or Asynchronous Commit. When selecting Synchronous mode, transactions coming to the primary replica will be sent to all other secondary replicas with Synchronous mode. The primary replica will complete the transaction only after the replicas have written the transaction to disk. This eliminates the risk of data loss if the primary replica fails. In asynchronous mode, the primary replica writes changes immediately, without waiting for a response from the secondary replicas;
-
Secondary replica for reading - a parameter specifying the ability to make select requests to secondary replicas. When set to yes, clients can gain read-only access even when connected without ApplicationIntent=readonly;
-
Commits require synchronized recipients - the number of synchronized secondary replicas to complete the transaction. Required to be set depending on the number of replicas. Keep in mind that if the number of secondary synchronized replicas becomes less than the specified number (e.g. during a crash), the Accessibility Group databases will become unavailable even for read-only access.
On the Backup Options tab, you can select where backups will be created from. Leave everything by default - Prefer Secondary.
Specify the availability group listener name, port and IP address.
If all the tests at the time of completion were successful, then click "Next".
This completes the initial configuration of the AlwaysON availability group. You can run fault tolerance tests by turning off each node in the cluster and giving simple select, insert requests.
We hope our instruction on how to create availability groups will help you ensure that your IT infrastructure is up and running properly. We plan to release other variations of the scenarios in the future.
