What should you do?

You are the database administrator for your company. You are administering the Sql1 SQL server that containsthe Prod1 database.
The Prod1 database is configured to use the Full Recovery model and implements a backup strategy to provideminimum downtime if a failure occurs.
You want to implement database mirroring to maximize the performance ofthis database. The mirror database Prod2 is maintained on the Sql2 server.
The following conditions must be met:
? The database should be made available to the users even if the mirrored server becomes unavailable.
? Service should be forced on the mirrored server if the principal server fails.
What should you do?

A.
Set the transaction safety attribute to OFF.

B.
Set the transaction safety attribute to FULL.

C.
Configure a witness server for the mirrored session.

D.
Configure the mirrored sessions to operate in synchronous mode.

Explanation:

To meet the specified conditions, you should set the transaction safety attribute to OFF . This will enable themirrored database to operate in asynchronous mode. By configuring database mirroring, you can operatemirrored sessions in asynchronous or synchronous mode. Operating mirrored sessions in asynchronous modeimproves database performance. In asynchronous mode, the transaction safety is set to OFF , and the principalserver does not wait for a transaction to be committed on the mirrored server. This minimizes the time required tocomplete a transaction on the principal server and improves the performance. When the mirrored sessions areoperating in asynchronous mode, the principal server continues to function even if the mirrored server becomesunavailable. In this scenario, the performance of the database is more critical than availability. If the principalserver fails, service can be forced on the mirrored server only if the mirrored sessions are operating inasynchronous mode. You should not set the transaction safety attribute to FULL because it will configure the mirrored session tooperate in synchronous mode. Synchronous mode ensures high availability of the database and does not ensurehigh performance. In synchronous mode, the transactions will be committed on the principal server only whenthey are copied to the mirrored server. This increases the time required to commit transactions and hampersdatabase performance. You should not configure a witness server for the mirrored session. A witness server is used while configuringdatabase mirroring to ensure high availability of the database. A witness server can be used only in synchronousmode. The witness server constantly monitors the mirrored sessions and initiates an automatic failover if theprincipal server fails. This process ensures maximum availability of the database. This scenario demands betterperformance of the database. Therefore, you should not configure a witness server. You should not configure the mirrored sessions to operate in synchronous mode because synchronous modedoes not provide enhanced performance of the database. You should configure the mirrored sessions to operatein synchronous mode when you want increased database availability or increased protection for database transactions.

Objective:
Implementing High Availability

Sub-Objective:
Implement database mirroring.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > High Availability > DatabaseMirroring > Database Mirroring Overview > Data Mirroring Sessions > Asynchronous Database Mirroring (High-Performance Mode) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > High Availability > DatabaseMirroring > Database Mirroring Overview > Data Mirroring Sessions > Synchronous Database Mirroring (High-Safety Mode) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > High Availability > DatabaseMirroring > Database Mirroring Overview