Microsoft® SQL Server® 2012 Bible

702

Part V: Enterprise Data Management

TABLE 27-2 Querying the sys.database_mirroring Catalog View

Column Name Principal Server Results Mirror Server Results

Database_Name AdventureWorks AdventureWorks

mirroring_state_desc SYNCHRONIZED SYNCHRONIZED

mirroring_role_desc PRINCIPAL MIRROR

mirroring_safety_level_desc FULL FULL

mirroring_partner_name TCP://sql2008ni2.hol169.local:5022 TCP://sql2008ni1.hol169

.local:5022

mirroring_witness_name TCP://witness.hol169.local:5023 TCP://witness.hol169

.local:5023

mirroring_witness_state_desc CONNECTED CONNECTED

mirroring_failover_lsn 1120000000012600001 1120000000012600001

The mirroring state result is SYNCHRONIZED. This state indicates that the mirror database

has suffi ciently caught up with the principal database. If you chose SAFETY FULL, there

will be no data loss. If you chose SAFETY OFF, there is a potential for data loss. Other pos-

sible mirroring states are as follows:

■ (^) Synchronizing: Indicates that the mirror database is trying to catch up with the
principal database. This is typically seen when you just start database mirroring or
are in high-performance mode.
■ (^) SUSPENDED: Indicates that the mirror database is not available. During this time
the principal is referred to as running exposed because it is processing transactions
but not sending any transaction log records to the mirror.
■ (^) PENDING_FAILOVER: Indicates the state that the principal goes through before
transitioning to the mirror role.
■ (^) DISCONNECTED: Indicates the partners are unable to communicate with each other.
The mirroring_failoverlsn indicates the log sequence number (lsn) of the latest
transaction log record that is written to disk. When there is heavy load on the principal
database and the mirror tries to catch up with the principal, you see that the mirroring
failover_lsn on the principal is ahead of the mirror.
■ (^) Query the sys.database_mirroring_witnesses catalog view to review data-
base mirroring session information. For example, executing the following T-SQL
command against the witness server displays the results in Table 27-3.
SELECT database_name,
principal_server_name,
mirror_server_name,
safety_level_desc,
c27.indd 702c27.indd 702 7/31/2012 9:50:28 AM7/31/2012 9:50:28 AM
http://www.it-ebooks.info