Administering Microsoft SQL Server 2012/2014 Databases — Question 66

You administer a Microsoft SQL Server database.
You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01.
You need to configure the availability group to prevent data loss. In the event of a database failure, the designed secondary database must come online automatically.
Which Transact-SQL statement should you use?

Answer options

• A. ALTER AVAILABILITY GROUP haContosoDbs MODITY REPLICA ON Server01\Contoso01 WITH (AVAILABILITY _MODE=ASYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC)
• B. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON Server01\Contoso01 WITH (AVAILABILITY _MODE=ASYNCHRONOUS_COMMIT, FAILOVER_MODE=MANUAL)
• C. ALTER AVAILABILITY GROUP haContosoDbs MODITY REPLICA ON Server01\Contoso01 WITH (AVAILABILITY _MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC)
• D. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON Server01\Contoso01 WITH (AVAILABILITY _MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=MANUAL)

Correct answer: C

Explanation

The correct answer is C because it configures the availability group for synchronous commit mode, which ensures no data loss, and sets the failover mode to automatic, allowing the secondary database to come online without manual intervention. Options A and B use asynchronous commit mode, which does not guarantee data loss prevention. Option D, while using synchronous commit, sets the failover mode to manual, preventing automatic failover during a failure.