SQL Server Interview Questions & Answers

Topic 11: HA/DR & Administration

Questions 97–100 | Intermediate to Advanced

This section covers SQL Server High Availability (HA), Disaster Recovery (DR), Failover Cluster Instances, Availability Groups, RPO, RTO and the key areas that should be monitored on a production SQL Server.

A good HA/DR architecture is not simply about selecting a SQL Server technology. The design should start with business requirements for availability, acceptable data loss, recovery time, infrastructure, operational complexity and cost.

97. What SQL Server HA/DR technologies are available?

Answer:
SQL Server provides several technologies that can be used to meet different High Availability and Disaster Recovery requirements.

Common options include:

Always On Availability Groups
Provides database-level high availability and disaster recovery using replicas of selected user databases.
Failover Cluster Instances (FCI)
Provides instance-level high availability through Windows Server Failover Clustering and shared or cluster-compatible storage.
Log Shipping
Periodically backs up transaction logs on the primary server, transfers them and restores them to one or more secondary servers.
Database Mirroring
An older database-level HA/DR technology that may still exist in legacy environments. Availability Groups are generally preferred for modern designs.
Backup and Restore
Remains a fundamental disaster-recovery mechanism even when other HA technologies are implemented.
Replication
Primarily a data-distribution technology. It can support certain availability requirements, but it should not normally be treated as a replacement for a complete HA/DR and backup strategy.

A production architecture may combine several technologies.


      Primary SQL Server

         │

         ├── Availability Group Replica

         │     High Availability

         │

         ├── Remote DR Replica

         │     Disaster Recovery

         │

         └── Full + Differential + Log Backups

               Data Protection

Interview Tip:
High Availability and Disaster Recovery are related but not identical. HA focuses primarily on maintaining or rapidly restoring service availability, while DR focuses on recovering from larger failures such as site, infrastructure or regional disasters.

98. FCI vs Availability Groups?

Answer:
Failover Cluster Instances (FCI) and Availability Groups (AG) both provide SQL Server availability capabilities, but they operate at different levels and use different architectures.

Area	Failover Cluster Instance	Availability Group
Protection Level	SQL Server instance	Selected user databases
Storage	Shared or cluster-compatible storage architecture	Each replica maintains its own database storage
Database Copy	Same database storage is accessed by the active node	Each replica maintains a separate copy
Readable Secondary	No secondary database copy for read workloads	Supported with appropriate configuration and edition/features
Storage Failure Protection	Depends on resilience of the underlying storage	Separate replica storage can provide additional protection

In simplified terms:

FCI
Protects the SQL Server instance and uses clustered storage.

Availability Group
Protects selected databases by maintaining separate database copies on SQL Server replicas.

An architecture can also combine FCI and Availability Groups when justified by business and infrastructure requirements.

Architect Tip:
Do not select FCI or AG simply because one technology is considered "better." Start with failure scenarios, RPO, RTO, storage architecture, database scope, read-scale requirements, licensing and operational capabilities.

99. What is RPO and RTO?

Answer:
RPO and RTO are business recovery objectives used to design backup, High Availability and Disaster Recovery solutions.

RPO: Recovery Point Objective

RPO defines how much data loss the business is willing to tolerate following a failure.

For example:

RPO = 15 minutes

Business requirement:

After a failure, no more than approximately
15 minutes of recent data should be lost.

This requirement influences decisions such as transaction-log backup frequency and replication architecture.

RTO: Recovery Time Objective

RTO defines the target amount of time within which the service should be restored after an outage.

For example:

RTO = 30 minutes

Business requirement:

Database service should be restored within
approximately 30 minutes after the outage.

A simple way to remember the difference is:

RPO = How much data can we afford to lose?

RTO = How long can we afford to be unavailable?

For example, consider a database with:

Database Size = 5 TB

RPO = 5 minutes

RTO = 15 minutes

A strategy based only on restoring a nightly full backup would almost certainly be insufficient. The architecture may require frequent log backups combined with appropriate HA/DR technologies and tested recovery procedures.

Interview Tip:
Never start an HA/DR architecture discussion with technology. Start with the business-defined RPO and RTO, then design an appropriate technical solution.

100. What should be monitored on a production SQL Server?

Answer:
Production SQL Server monitoring should cover availability, performance, capacity, recoverability and security.

I would organize monitoring into the following areas:

1. SQL Server Availability

SQL Server service status
SQL Server Agent status
Unexpected restarts
Database state
Database availability

2. CPU

Sustained CPU utilization
SQL Server CPU consumption
Expensive queries
Scheduler pressure
Parallelism-related behavior

3. Memory

Operating system memory pressure
SQL Server memory usage
Query memory grants
Memory grant waits
Buffer Pool behavior

4. Storage & I/O

Data-file read latency
Data-file write latency
Transaction-log write latency
Disk capacity
Database-file growth
Autogrowth events

5. Transaction Log

Log usage percentage
Unexpected log growth
log_reuse_wait_desc
Transaction log backup status
Long-running transactions
VLF health where relevant

6. Query Performance

Long-running queries
High CPU queries
High logical-read queries
Plan regressions
Query Store performance history
Execution-plan warnings

7. Blocking & Deadlocks

Long blocking chains
Head blockers
LCK_M_* waits
Deadlock events
Long-running open transactions

8. Backup & Recovery

Failed backups
Missing transaction log backups
Backup duration
Backup storage capacity
Restore-test results
Ability to meet RPO and RTO

9. HA/DR

Availability Group replica health
Synchronization state
Send queue
Redo queue
Replica connectivity
Failover readiness
Cluster health

10. SQL Server Agent Jobs

Failed jobs
Long-running jobs
Jobs that did not execute as expected
Backup jobs
ETL and maintenance jobs

11. Database Integrity

DBCC CHECKDB results
Suspect pages
I/O-related database errors
SQL Server error log

12. Capacity & Growth

Database growth trends
Data-file free space
Transaction-log capacity
Disk free space
tempdb usage
Backup-storage growth

13. Security

Failed login activity
Privileged login membership
Unexpected permission changes
Certificate and encryption-key management
Audit events where required

Senior DBA Interview Tip:
Do not answer this question by listing only CPU, memory and disk. Production monitoring should cover Availability + Performance + Capacity + Recoverability + HA/DR + Security.

Useful DBA Script: Check Database Status

SELECT
    name AS DatabaseName,
    state_desc AS DatabaseState,
    recovery_model_desc AS RecoveryModel,
    user_access_desc AS UserAccess,
    log_reuse_wait_desc AS LogReuseWait
FROM sys.databases
ORDER BY name;

Useful DBA Script: Last Full, Differential & Log Backup

SELECT
    d.name AS DatabaseName,

    MAX(CASE
        WHEN bs.type = 'D'
        THEN bs.backup_finish_date
    END) AS LastFullBackup,

    MAX(CASE
        WHEN bs.type = 'I'
        THEN bs.backup_finish_date
    END) AS LastDifferentialBackup,

    MAX(CASE
        WHEN bs.type = 'L'
        THEN bs.backup_finish_date
    END) AS LastLogBackup

FROM sys.databases AS d

LEFT JOIN msdb.dbo.backupset AS bs
    ON d.name = bs.database_name

GROUP BY
    d.name

ORDER BY
    d.name;

Useful DBA Script: Check Availability Group Database Health

SELECT
    DB_NAME(drs.database_id) AS DatabaseName,
    ar.replica_server_name AS ReplicaServer,
    rs.role_desc AS ReplicaRole,
    drs.synchronization_state_desc AS SyncState,
    drs.synchronization_health_desc AS SyncHealth,
    drs.log_send_queue_size AS LogSendQueueKB,
    drs.redo_queue_size AS RedoQueueKB
FROM sys.dm_hadr_database_replica_states AS drs

INNER JOIN sys.availability_replicas AS ar
    ON drs.replica_id = ar.replica_id

INNER JOIN sys.dm_hadr_availability_replica_states AS rs
    ON drs.replica_id = rs.replica_id
    AND drs.group_id = rs.group_id

ORDER BY
    DatabaseName,
    ReplicaServer;

Production SQL Server Monitoring Model


      Production SQL Server

         ↓

      Availability

         ↓

      CPU + Memory + I/O

         ↓

      Query Performance

         ↓

      Blocking + Deadlocks

         ↓

      Transaction Log

         ↓

      Backup + Restore

         ↓

      HA/DR Health

         ↓

      Integrity + Agent Jobs

         ↓

      Capacity + Growth

         ↓

      Security

         ↓

      Alert + Investigate + Resolve + Trend

SQL Server HA/DR Design Flow


      Business Criticality

         ↓

      Define RPO

         ↓

      Define RTO

         ↓

      Identify Failure Scenarios

         ↓

      Server / Storage / Data Center / Region

         ↓

      Select HA Technology

         ↓

      Select DR Technology

         ↓

      Design Backup Strategy

         ↓

      Configure Monitoring

         ↓

      Document Failover and Recovery Procedures

         ↓

      Test Failover + Restore + DR

         ↓

      Validate Actual RPO and RTO

Interview Quick Recap

SQL Server HA/DR technologies include Availability Groups, Failover Cluster Instances, Log Shipping and backup/restore.
Legacy environments may also contain Database Mirroring.
FCI provides instance-level availability.
Availability Groups provide database-level availability for selected databases.
Availability Groups maintain separate copies of databases on participating replicas.
RPO defines acceptable potential data loss.
RTO defines the target recovery duration after an outage.
HA does not eliminate the requirement for backups.
Production SQL Server monitoring should include CPU, memory, storage, I/O, queries, blocking, transaction logs, backups, SQL Agent jobs, HA/DR, integrity, capacity and security.
Restore and failover procedures should be tested rather than assumed to work.

Part I Complete: Core SQL Server Interview Questions

You have now completed Questions 1–100 covering SQL Server fundamentals, architecture, storage, recovery, backup, indexing, performance tuning, concurrency, security and HA/DR.

Next: Part II – 100 Advanced SQL Server Scenario Questions

← Previous Topic
# Topic 10: Security

Next Topic →

# Topic 12: Production Performance Troubleshooting

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning

Search This Blog

Technical Architects Zone

SQL Server Q&A Topic 11: HA/DR & Administration