A few weeks ago, as I was running some test on our Database Mirroring session in DBA environment (development for DBAs), I ran into this error:

[sourcecode language=”sql”]

Msg 1473, Level 16, State 6, Line 1
This SQL Server edition does not allow changing the safety level. ALTER DATABASE command failed

[/sourcecode]

My earlier (incorrect) understanding was that, synchronous mirroring is possible only with Enterprise edition of Sql Server. All other editions only allow asynchronous transmission of logs from Principal to Mirror. But after running into this error, I now stand corrected.

This is the complete story:

Transaction Safety & Transfer Mechanism

Standard edition can only perform in SAFETY FULL transaction safety mode; which means that transactions occurring at Principal are synchronously transmitted to Mirror. When transactions occur on Principal, the information first goes to Principal‘s transaction log. As it is hardening, the logs are sent to Mirror. Once the transaction is committed on Mirror, then ‘ack‘ is sent to Principal and then the transaction is committed on Principal. On successful completion of this cycle, commit ack is sent back to the application/user that initiated the transaction.

Operating Mode

With SAFETY FULL, if we have a witness, it will be automatic failover, if not its a manual failover. SAFETY FULL has two operating modes

• High Availability :  Witness is configured allowing automatic failover
• High Protection: Witness is not configured, so manual failover.

²Asynchronous database mirroring is supported only by SQL Server 2005 Enterprise Edition SP1 and later version

High Performance is only available in Enterprise Edition.

In High Performance, transactions are committed on Principal as they occur first. Then asynchronously, they are transferred to Mirror. This reduces latency in sending commit back to the user/application that started the transaction.

Hope this helps,
_Sqltimes