MCQs on High Availability and Disaster Recovery | SQL Server
High availability (HA) and disaster recovery (DR) are critical components in SQL Server to ensure system uptime and data protection. These techniques like SQL Server Clustering, Always On Availability Groups, Database Mirroring, and Log Shipping help businesses maintain operational continuity and safeguard against data loss. Below are 30 multiple-choice questions (MCQs) covering these essential concepts:
SQL Server Clustering
- What is the main purpose of SQL Server Clustering? a) Improve SQL query performance
b) Provide high availability
c) Optimize disk storage
d) Manage multiple databases - Which SQL Server clustering mode provides automatic failover? a) Active-Passive
b) Active-Active
c) Passive-Passive
d) None of the above - How many nodes are required for a SQL Server cluster? a) One
b) Two or more
c) Three
d) Four or more - In a SQL Server cluster, which component shares the same disk resources? a) Primary replica
b) Secondary replica
c) Cluster nodes
d) SQL Server agent - Which of the following is true about SQL Server clustering? a) It requires dedicated storage
b) It is only applicable for SQL Server 2012 or higher
c) It ensures automatic backup of databases
d) It only works for primary replica servers
Always On Availability Groups
- What is the key feature of Always On Availability Groups in SQL Server? a) Single-node failover
b) Automatic backup
c) Multi-database failover
d) Database encryption - How many replicas are supported in an Always On Availability Group in SQL Server 2016? a) 2
b) 3
c) 4
d) 8 - Which of the following is a requirement for Always On Availability Groups? a) Windows Server Failover Clustering (WSFC)
b) SQL Server 2008 or earlier
c) SQL Server Standard edition
d) None of the above - In Always On Availability Groups, which replica type supports read-only access? a) Primary
b) Secondary
c) Standby
d) None of the above - What happens during the automatic failover in Always On Availability Groups? a) The database is unavailable for a few hours
b) Data is lost
c) The secondary replica becomes the primary replica
d) Backup jobs are suspended
Database Mirroring
- Which database mirroring mode allows for high availability with automatic failover? a) High-safety
b) High-performance
c) Manual failover
d) None of the above - Which component is not required for database mirroring? a) Two SQL Server instances
b) Witness server
c) Dedicated storage
d) Primary replica - In database mirroring, what happens during a failover in high-safety mode? a) Data is replicated instantly
b) Data is lost
c) The mirror database becomes the primary
d) The database becomes read-only - What is the primary difference between database mirroring and Always On Availability Groups? a) Mirroring supports more replicas
b) Always On uses synchronous replication
c) Mirroring does not support multiple databases
d) Always On requires a witness server - Which of the following is required for database mirroring in SQL Server? a) A dedicated witness server
b) Multiple instances of SQL Server
c) Same database name on both servers
d) A SQL Server cluster
Log Shipping
- What is the primary purpose of log shipping in SQL Server? a) Replicate data between servers
b) Improve query performance
c) Backup and restore transaction logs
d) Automatically balance loads - Which of the following is NOT a component of log shipping? a) Primary server
b) Secondary server
c) Witness server
d) Transaction log backups - What happens in log shipping when the primary server fails? a) The secondary server immediately becomes the primary
b) Data on the secondary server is deleted
c) The database is locked until recovery
d) The transaction logs are not applied - In log shipping, how often are transaction logs applied to the secondary database? a) Every hour
b) Once per day
c) Based on user-defined frequency
d) Every minute - How many secondary servers can a log shipping configuration have in SQL Server? a) Only one
b) Up to two
c) Multiple
d) No limit
High Availability and Disaster Recovery Best Practices
- Which of the following is a recommended best practice for high availability? a) Keep backups on the same server
b) Use mirrored disks for data storage
c) Implement load balancing
d) Always use synchronous replication - In the context of disaster recovery, what does RTO (Recovery Time Objective) represent? a) Maximum allowable data loss
b) Maximum allowable downtime
c) Minimum recovery speed
d) Number of servers needed for recovery - Which of the following is critical for a successful disaster recovery plan? a) A test environment
b) Daily system updates
c) Redundant power sources
d) Low-cost hardware - How often should backups be tested in a disaster recovery plan? a) Monthly
b) Weekly
c) Annually
d) Regularly, based on the plan - What is the recommended approach for disaster recovery in large-scale SQL Server environments? a) Manual failover
b) Regular log shipping only
c) Use a combination of high availability solutions
d) Backup to tape storage
SQL Server Recovery Models
- Which recovery model supports point-in-time recovery? a) Simple
b) Full
c) Bulk-logged
d) None of the above - Which of the following SQL Server recovery models offers the least overhead? a) Full
b) Simple
c) Bulk-logged
d) Transaction-log-based - What happens in SQL Server when switching from simple to full recovery model? a) Immediate data loss
b) Transaction logs start being retained
c) Performance improves
d) Backups stop working - In the full recovery model, what is the purpose of the transaction log? a) Store database settings
b) Track changes made to the database
c) Archive database backups
d) Prevent unauthorized access - Which recovery model should be used for databases that require high availability and no data loss? a) Simple
b) Full
c) Bulk-logged
d) Transaction-log-based
Answer Key
| Qno | Answer |
|---|---|
| 1 | b) Provide high availability |
| 2 | a) Active-Passive |
| 3 | b) Two or more |
| 4 | c) Cluster nodes |
| 5 | a) It requires dedicated storage |
| 6 | c) Multi-database failover |
| 7 | d) 8 |
| 8 | a) Windows Server Failover Clustering (WSFC) |
| 9 | b) Secondary |
| 10 | c) The secondary replica becomes the primary replica |
| 11 | a) High-safety |
| 12 | c) Dedicated storage |
| 13 | c) The mirror database becomes the primary |
| 14 | c) Mirroring does not support multiple databases |
| 15 | b) Multiple instances of SQL Server |
| 16 | c) Backup and restore transaction logs |
| 17 | c) Witness server |
| 18 | a) The secondary server immediately becomes the primary |
| 19 | c) Based on user-defined frequency |
| 20 | c) Multiple |
| 21 | c) Implement load balancing |
| 22 | b) Maximum allowable downtime |
| 23 | a) A test environment |
| 24 | d) Regularly, based on the plan |
| 25 | c) Use a combination of high availability solutions |
| 26 | b) Full |
| 27 | b) Simple |
| 28 | b) Transaction logs start being retained |
| 29 | b) Track changes made to the database |
| 30 | b) Full |