Microsoft SQL Server 2012 Bible

Checking Database Mirroring Configuration

After database mirroring is configured, you can verify the database mirroring setup in several ways.

• View the status of the principal and mirror databases.
• Expand Databases in Management Studio to view the status of the principal and mirror databases. Figure 27.9 shows an example for the principal database.
• Query the sys.database_mirroring catalog view to view the database mirroring metadata for each mirrored database. For example, executing the following T-SQL command against the principal and mirror server displays the results in Table 27.2.

 SELECT DB_NAME(database_id) AS Database_Name, 
 mirroring_state_desc, 
 mirroring_role_desc, 
 mirroring_safety_level_desc, 
 mirroring_partner_name, 
 mirroring_witness_name,
 mirroring_witness_state_desc,
 mirroring_failover_lsn
 FROM sys.database_mirroring 
 WHERE mirroring_state IS NOT NULL;

Figure 27.9 Status of principal database.

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 ...