Administering a SQL Database Infrastructure — Question 191

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You need to deploy a new Microsoft SQL Server environment that meets the following requirements:
✑ The SQL Server instance must be highly available.
✑ There must be minimal downtime incurred during hardware failure or operating system maintenance.
✑ All instance-level security settings and SQL Server Agent jobs must be available without additional synchronization tasks.
What should you implement?

Answer options

• A. a Microsoft Azure Stretch Database
• B. log shipping
• C. an Always On Availability Group with all replicas in synchronous-commit mode
• D. a file share witness
• E. a Microsoft SQL Server failover cluster instance (FCI)
• F. a Windows cluster with a shared-nothing architecture
• G. an Always On Availability Group with secondary replicas in asynchronous-commit mode

Correct answer: E

Explanation

The best choice is E, a Microsoft SQL Server failover cluster instance (FCI), as it provides high availability and minimizes downtime during failures and maintenance while maintaining security settings and jobs. Other options do not fully satisfy all requirements; for example, log shipping (B) and an Always On Availability Group with secondary replicas in asynchronous-commit mode (G) may introduce more downtime and require additional synchronization tasks.