Configuring an Always On availability group is one of the fundamental concepts you should understand while studying for the DP-300 Administering Microsoft Azure SQL Solutions exam. Always On availability groups is an enterprise-level high-availability and disaster-recovery solution introduced in SQL Server 2012 to replace database mirroring. An availability group supports a failover environment for a discrete set of user databases, known as availability databases.
Before you begin configuring an Always On availability group, it is essential to understand its prerequisites: a Windows Server Failover Cluster (WSFC), SQL Server instances and shared storage.
Prerequisites
- Windows Server Failover Cluster (WSFC):
- SQL Server Instances:
- Shared Storage:
Before you set up an Always On availability group, you need to configure a Windows Server Failover Cluster (WSFC). A WSFC is necessary to host the Always On availability groups deployment. After WSFC is set up, ensure that each computer node possesses a SQL Server instance to host and maintain availability replicas of an availability group.
You need at least two SQL Server instances, which will interact with the underlying WSFC. Each SQL Server instance will host an availability replica. For data synchronization, one replica will act as the primary replica while the second will be the secondary replica.
Shared storage is not required between SQL Server instances in Always On availability groups. However, a backup of the database must exist on a shared path accessible by all replicas.
Configuration Steps
Here is a basic example of how one can configure an Always On availability group:
Step 1: Enable SQL Server Always On Availability Groups Feature
To enable the SQL Server Always On Availability Groups on each instance of SQL Server, use the SQL Server Configuration Manager.
Step 2: Create a Database for the Availability Group
Next, create a database that will be part of the Always On availability group. To contribute to an availability group, the database must be a user database.
Step 3: Backup all Databases
Before joining the database to an availability group, you must ensure that you have taken a full backup of the database on the primary replica.
Step 4: Create the Availability Group
You can create the availability group using SQL Server Management Studio (SSMS), Transact-SQL (T-SQL), or PowerShell in SQL Server.
For example, using SMSS:
USE [master]
GO
CREATE AVAILABILITY GROUP [MyAG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)
FOR DATABASE [MyDatabase]
REPLICA ON 'SQL01' WITH (ENDPOINT_URL = 'TCP://SQL01.Virtx.Virtx:5022', FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
Step 5: Join the Availability Group
After creating the availability group on the primary replica, connect back to the server instance hosting the secondary replica. You must execute the command under the master database context.
Step 6: Add Database to the Availability Group
Finally, on the primary replica, simply use ADD DATABASE command to add a database to the availability group.
Conclusion
An Always On availability group enhances the availability of a set of user databases for an enterprise. Any issues that impact the availability databases in primary replica will cause automatic failover. This robust feature also helps handle planned and unplanned downtime without affecting applications or clients. By mastering the configuration of an Always On availability group, you will gain an important skill for the DP-300 Administering Microsoft Azure SQL Solutions exam.
Practice Test
True or False: Always On Availability Groups is only available on SQL Server 2017 and later.
- True
- False
Answer: False
Explanation: Always On Availability Groups is a high-availability, disaster-recovery solution introduced in SQL Server
Single select: What is the maximum number of secondary replicas that you can configure in SQL Server 2016’s Always On Availability Groups?
- a) 2
- b) 4
- c) 8
- d) 10
Answer: c) 8
Explanation: In SQL Server 2016, Always On Availability Groups supports a maximum of eight secondary replicas.
True or False: A SQL server instance can host multiple availability groups.
- True
- False
Answer: True
Explanation: A single SQL server instance can host multiple availability groups, each with its own set of primary and secondary databases.
Multiple select: Which of the following are the prerequisites for configuring Always on Availability Groups?
- a) The SQL Server instance should be on SQL Server 2016 or later
- b) Enable Always On Availability Groups on the SQL Server Configuration Manager
- c) All the nodes should be in the same subnet
- d) A Windows Server Failover Cluster
Answer: b) Enable Always On Availability Groups on the SQL Server Configuration Manager and d) A Windows Server Failover Cluster
Explanation: The Always On Availability Groups feature must be enabled on SQL Server Configuration Manager and a Windows Server Failover Cluster is a prerequisite before creating Availability Groups.
Single select: The primary replica for an Availability Group is ____________.
- a) Read-only
- b) Writable
- c) Not accessible
- d) None of the above
Answer: b) Writable
Explanation: The primary replica is the read-write copy of a database. The secondary replicas are read-only.
True or False: A single database can belong to multiple Always On Availability Groups simultaneously.
- True
- False
Answer: False
Explanation: A database can belong to only one availability group at any given time.
Multiple select: Which of the following automatic failover modes does Always On Availability Groups support?
- a) Manual failover
- b) Automatic failover (without data loss)
- c) Forced Manual Failover (with possible data loss)
- d) Both a and b
Answer: d) Both a and b
Explanation: Always On Availability Groups supports both manual and automatic failover modes.
True or False: The secondary replica Async commit mode can result in some data loss following a forced failover.
- True
- False
Answer: True
Explanation: The asynchronous-commit mode allows for the secondary replica to lag behind the primary replica, which may result in some data loss after a forced failover.
Single select: The number of readable secondary replicas in Always On Availability Groups is restricted to __________.
- a) 0
- b) 2
- c) 4
- d) No restriction
Answer: d) No restriction
Explanation: There is no set limit on the number of readable secondary replicas. All the secondary replicas can be configured for readable access.
True or False: Always On Availability Groups requires all replicas to be running the same edition of SQL Server.
- True
- False
Answer: True
Explanation: All replicas participating in the same availability group must be running the same edition of SQL Server.
Interview Questions
What are the core components of an Always On Availability Group?
The core components include a primary replica, one or more secondary replicas, and an availability database.
What is the use of “DB01” while configuring Always ON Availability Group?
DB01 is used as a sample placeholder Database name which is utilized while configuring Always ON Availability Group.
Which Windows Server feature plays a role in the functionality of Always On Availability Groups?
Windows Server Failover Clustering (WSFC) plays a crucial role in the functionality of Always On Availability Groups.
What is the maximum number of replicas that you can have in an Always On Availability Group?
An Always On Availability Group can have up to one primary replica and eight secondary replicas.
What is the role of “Listener” in Always On Availability Group configuration?
The Listener enables client applications to connect to the primary replica or a read-only secondary replica by routing connection requests.
Before setting up an Always ON Availability Group, what feature must you enable under ‘SQL Server Configuration Manager’?
Before setting up an Always ON Availability Group, you must enable the ‘Always ON High Availability’ feature under ‘SQL Server Configuration Manager’.
What SQL Server permission is required to create or configure an Always On Availability Group?
The ALTER ANY AVAILABILITY GROUP server-level permission is required to create or configure an Always On Availability Group.
What type of workload can be offloaded to readable secondary replicas in an Always On Availability Group?
Read-only workloads and backup operations can be offloaded to readable secondary replicas in an Always On Availability Group.
Can the SQL Server named instance be used in an Always On Availability Group?
Yes, SQL Server named instances can be used in an Always On Availability Group configuration.
Can all secondary replicas in an Always On Availability Group be set to be sync-commit replicas?
No, not all secondary replicas can be set to be sync-commit replicas. There can only be two synchronous-commit replicas, including the primary replica.
What function does the Failover Wizard perform during the process of Always On Availability Group configuration?
The Failover Wizard performs a planned manual failover without data loss during the process of Always On Availability Group configuration.
What is the purpose of the ‘SEEDING_MODE’ option in the CREATE AVAILABILITY GROUP statement?
The ‘SEEDING_MODE’ option is used to specify the initial synchronization method for the secondary replicas.
What are the two types of automated failover modes in an Always On Availability Group?
The two types of automated failover modes are ‘Synchronous-commit mode’ and ‘Automatic Failover’.
What is the key difference between Automatic Failover and Manual Failover in an Always On Availability Group?
The key difference is that Automatic Failover happens without administrative intervention during a failure condition while Manual Failover requires administrative intervention.
What is the maximum number of secondary replicas that can forward changes to other secondary replicas in an Always On Availability Group?
There is no maximum number of secondary replicas that can forward changes to other secondary replicas in an Always On Availability Group.
extutorials.com