January 2, 2013 at 10:42 am
According to the Microsoft curriculum for Maintaining MSSQL 2008, when Mirroring is configured in High Protection Mode, "If the mirror server fails, the principal server takes itself offine to avoid data risks."
In my home environment I'm using for exam prep/self education, I have a copy of AdventureWorks2005 on SQL 2008 R2 mirrored to a 2008 R2 instance on a VM on another computer. It is configured for synchronious operation without a witness server, which should be High Safety\Protection (that is the same thing, right?). But when I shut down the VM hosting the mirror, I can still run update statements on the principal database (which now reads Mirrored\Disconnected in SQLMS)
Is the curriculum incorrect, the functionality in R2 different, or is my mirror not set up as I believe?
January 2, 2013 at 10:55 am
From http://msdn.microsoft.com/en-us/library/ms179344(v=sql.105).aspx
High-Safety Mode Without Automatic Failover <--- Your configuration
--------------------------------------------------------------------------------
When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data). If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server (with possible data loss). For more information, see Forced Service (with Possible Data Loss).
High-Safety Mode with Automatic Failover
--------------------------------------------------------------------------------
Automatic failover provides high availability by ensuring that the database is still served after the loss of one server. Automatic failover requires that the session possess a third server instance, the witness, which ideally resides on a third computer. The following figure shows the configuration of a high-safety mode session that supports automatic failover.
Unlike the two partners, the witness does not serve the database. The witness simply supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.
When a witness is set, the session requires quorum—a relationship between at least two server instances that allows the database to be made available. For more information, see Quorum: How a Witness Affects Database Availability and Automatic Failover. For more information, see Database Mirroring Witness.
Automatic failover requires the following conditions:
•
The database is already synchronized.
•
The failure occurs while all three server instances are connected, and the witness and mirror server remain connected.
The loss of a partner has the following effect:
•
If the principal server becomes unavailable under the above conditions, automatic failover occurs. The mirror server switches to the role of principal, and it offers its database as the principal database.
•
If the principal server becomes unavailable when those conditions are not met, forcing service (with possible data loss) might be possible. For more information, see Forced Service (with Possible Data Loss).
•
If the only mirror server becomes unavailable, the principal and witness continue.
If the session loses its witness, quorum requires both partners. If either partner loses quorum, both partners lose quorum, and the database becomes unavailable until quorum is re-established. This quorum requirement makes sure that in the absence of a witness the database never runs exposed, that is without being mirrored.
January 2, 2013 at 11:10 am
So this curriculum (6231A) is in error. For one thing, it calls it "High Protection Mode" rather than High Safety. This is described as not using a witness server, and the principal goes offline to avoid data loss if the mirror is not available.
January 2, 2013 at 11:26 am
You're referring to the older SQL Server 2005 operating modes which were
High Availability (synch)
High Protection (synch)
High Performance (asynch)
The operating modes are now defined as
High Performance (asynch)
High safety (synch) without auto failover
High safety (synch) with auto failover
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply