Always On Availability Groups
7 questions found
What is an Always On Availability Group in SQL Server, and what problem does it solve?
Beginner
An Always On Availability Group is a high availability feature that keeps a copy of one or more databases synchronized across multiple SQL Server instances, so if the main server fails, another server already holding an up to date copy can take over quickly, minimizing downtime for your applications.
-- Simplified concept: primary replica and secondary replicas
-- stay synchronized, with automatic failover configured
CREATE AVAILABILITY GROUP MyAG
FOR DATABASE MyDatabase
REPLICA ON 'ServerA' WITH (...), 'ServerB' WITH (...);
Real-world example
An online banking system uses an Always On Availability Group so that if its primary database server suddenly goes offline, a secondary server automatically takes over within seconds, keeping the banking application available to customers.
Common follow-ups: What is the difference between synchronous and asynchronous data movement in an availability group?;How many secondary replicas can an availability group support?
Backup & Recovery;Replication
What is the difference between the primary replica and secondary replicas in an Always On Availability Group?
Beginner
The primary replica is the active database that handles all the read and write operations from your applications, while secondary replicas hold a continuously updated copy of that data and can optionally be used for read only reporting queries, backups, or as a standby ready to take over if the primary fails.
-- Read only routing lets reporting queries run against a secondary replica
-- instead of adding extra load to the primary
Real-world example
A company offloads its heavy reporting queries to a secondary replica, keeping the primary replica free to handle the application's regular transactional workload without slowing down.
Common follow-ups: Can you write data directly to a secondary replica?;How much delay is typical between the primary and a secondary replica?
Backup & Recovery;Query Optimization & Plans
What is the difference between automatic failover and manual failover in an Always On Availability Group?
Intermediate
Automatic failover happens without any human intervention when a properly configured synchronous secondary replica detects that the primary has failed, immediately promoting itself to become the new primary, while manual failover requires an administrator to explicitly trigger the switch, which is often used for planned maintenance rather than emergencies.
-- Automatic failover requires synchronous commit mode
-- and a configured availability group listener
ALTER AVAILABILITY GROUP MyAG
MODIFY REPLICA ON 'ServerB' WITH (FAILOVER_MODE = AUTOMATIC);
Real-world example
A hospital's patient record system relies on automatic failover so that if the primary database server crashes unexpectedly in the middle of the night, the system recovers on its own without waiting for an administrator to respond.
Common follow-ups: What conditions are required for automatic failover to actually work correctly?;Why might a company choose manual failover for certain replicas instead?
Backup & Recovery;SQL Server Architecture & Editions
What is an availability group listener, and why is it important for applications connecting to an Always On Availability Group?
Intermediate
An availability group listener provides a single, stable network name and address that applications connect to, regardless of which replica is currently acting as the primary, meaning your application configuration never needs to change even after a failover occurs, since the listener automatically redirects connections to the current primary.
-- Applications connect using the listener name
-- rather than a specific server name
Server=MyAGListener;Database=MyDatabase;
Real-world example
An e-commerce application connects to its database using the availability group listener name, so when a failover happens during a server outage, the application automatically reconnects to the new primary without requiring any configuration changes.
Common follow-ups: What happens to existing connections during a failover event?;Can a listener also help route read only queries to secondary replicas?
SQL Server Architecture & Editions;Query Optimization & Plans
How do you decide between synchronous commit mode and asynchronous commit mode for a specific replica in an Always On Availability Group?
Advanced
Synchronous commit mode waits for a secondary replica to confirm it has received and written the transaction before the primary considers the transaction complete, guaranteeing zero data loss but adding some latency, while asynchronous commit mode does not wait, offering better performance but with a small risk of losing recent transactions if the primary fails unexpectedly.
ALTER AVAILABILITY GROUP MyAG
MODIFY REPLICA ON 'ServerB' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
ALTER AVAILABILITY GROUP MyAG
MODIFY REPLICA ON 'ServerC' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);
Real-world example
A financial trading platform uses synchronous commit mode for its local disaster recovery replica to guarantee no lost transactions, while using asynchronous commit mode for a distant replica in another region where network latency would otherwise slow down every transaction.
Common follow-ups: How does network latency between data centers affect the choice between these two modes?;Can automatic failover be configured with asynchronous commit mode?
Transactions & ACID;Backup & Recovery
How would you plan a disaster recovery strategy that combines Always On Availability Groups across multiple data centers with regular backups?
Advanced
You would configure a local synchronous replica for immediate automatic failover during common hardware failures, an asynchronous replica in a geographically distant data center to protect against a full site disaster, and continue taking regular backups of the primary database as an additional layer of protection against issues like accidental data corruption that replication alone cannot fix.
-- Local replica: synchronous commit, automatic failover
-- Remote replica: asynchronous commit, manual failover
-- Backups still run independently on a regular schedule
Real-world example
A multinational retailer keeps a synchronous replica in the same city for fast automatic failover during routine outages, an asynchronous replica in another country for full disaster recovery, and nightly backups to guard against data corruption that could otherwise replicate across both live copies.
Common follow-ups: Why are backups still necessary even with a well configured availability group?;How do you test that a disaster recovery failover plan actually works before a real emergency happens?
Backup & Recovery;SQL Server Architecture & Editions
What are the licensing and edition requirements for using Always On Availability Groups in SQL Server?
Intermediate
Full featured Always On Availability Groups with multiple secondary replicas and automatic failover typically require SQL Server Enterprise edition, while a simplified version called basic availability groups, supporting only a single database and two replicas, is available starting with SQL Server Standard edition for organizations with smaller high availability needs.
-- Basic Availability Groups (Standard Edition)
-- support only one database and two replicas total
Real-world example
A small business running SQL Server Standard edition sets up a basic availability group to protect their single most critical database, while a larger enterprise customer uses the full Enterprise edition features to protect dozens of databases with multiple replicas.
Common follow-ups: What specific features are missing from basic availability groups compared to the full version?;How do you check which edition and features are currently licensed on a server?
SQL Server Architecture & Editions;Backup & Recovery