You are the database administrator of your company. You want to configure high-availability for a SQL Server2008 database named Corpdb by using database mirroring.
You want to configure automatic failover databasemirroring sessions. You must also improve reliability of the automatic failover.
Which conditions must be met to achieve the stated objectives? (Choose three. Each correct answer representspart of the solution.)
A.
The principal database and the mirror database must be stored on a single computer.
B.
The principal database and the mirror database must be stored on different computers.
C.
The database mirroring session must be running in synchronous mode.
D.
The database mirroring session must be running in asynchronous mode.
E.
The database mirroring session must have a witness server.
Explanation:
To achieve the objectives stated in this scenario, the principal database and the mirror database must be storedon different computers, the database mirroring session must be running in synchronous mode, and the databasemirroring session must have a witness server. Failover clusters provide high availability for an entire instance ofSQL Server, whereas database mirroring provides high availability for a single database. When you want to usedatabase mirroring with failover clusters, both the principal server and mirror server must reside on differentclusters. However, a mirroring session can also be established when one partner resides on the failover clusteredinstance of a cluster and the other partner resides on a separate, unclustered computer. When you use databasemirroring with failover clusters, the database mirroring sessions can be configured for automatic failover. Tosupport automatic failover, the database mirroring session must be running with a witness in synchronous or high-safety mode. In synchronous or high-safety mode, a transaction is committed on both servers, which ensures thatthe mirror database is in synchronization with the principal database. To improve the reliability of automaticfailover, the principal database and the mirror database must reside on different computers. The option stating that the principal database and the mirror database must be stored on a single computer isincorrect because this will not improve reliability of automatic failover. When both the principal and mirrordatabases are stored on a single computer, both databases will become unavailable if the computer fails. In thiscase, automatic failover will not occur. The option stating that the database mirroring session must be running in asynchronous mode is incorrect. Tosupport automatic failover, the database mirroring session must be running with a witness in synchronous or high-safety mode. In asynchronous or high-performance mode, the transactions commit without waiting for the mirrorserver to write the log to disk. This will not guarantee that the mirror database is updated with all the transactionscommitted on the principal database.
Objective:
Implementing High AvailabilitySub-Objective:
Implement a SQL Server clustered instance.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > High Availability > HighAvailability: Interoperability and Coexistence > Database Mirroring and Failover Clustering MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > High Availability > DatabaseMirroring > Database Mirroring Overview > Database Mirroring Sessions > Role Switching During a DatabaseMirroring Session > Automatic Failover