Wednesday, November 30, 2011

SQL Server Fix: Database Mirroring Principal Disconnected In Recovery

Issue: In SQL Server Mirroring (Principal Disconnected / In Recovery). 

For example "MSSQLFIX_A" is the Principal, "MSSQLFIX_B" is the Mirror and "MSSQLFIX_C" is the Witness you have 3 levels of redundancy. 

1.  If suppose MSSQLFIX_B fails, the quorum is between MSSQLFIX_A & MSSQLFIX_C 
2.  If MSSQLFIX_C fails, the quorum is between MSSQLFIX_A and MSSQLFIX_B. 
3.  If both MSSQLFIX_B & MSSQLFIX_C fail, this leaves MSSQLFIX_A in a (Principal Disconnected / In Recovery) state. 

Resolution: If the quorum is lost between the three servers/computers then this problem will occur. You can recover the database by using any one of the option from the below two. 

Option:1 
The quickest way to recover from this is to run the following script on MSSQLFIX_A. 

USE Master
GO
RESTORE DATABASE [DatabaseName] SET PARTNER OFF
GO 

This will remove mirroring but will recover your database. 

Option:2
Step 1.   Log into the primary database server 

Step 2.   Right-Click on the database that is listed as (Principal, Disconnected / In Recovery) and select Properties.

Step 3.   Click on the "Mirroring" page and then click the "Failover" button. 

Step 4.   The database will now be listed as (Mirror, Synchronized / Restoring) and will be accessible from the other SQL Server.



step by step tutorial. step by step guide. Step by Step Installation. Step-by-step procedure. Database Mirroring step by step. Step by Step Guide to. sql server step by step. sql server step by step ebook. microsoft sql server step by step ebook. sql server step by step tutorial. step by step installation guide. microsoft sql server 2008 step by step. Prerequisites. Restrictions. and Recommendations for Database Mirroring. Prerequisites and Recommendations for Database Mirroring. SQL Server Database Mirroring Pre-requisites and guidelines. Prerequisites for Database Mirroring. Database Mirroring in SQL Server 2008. Prerequisites: Configuring database mirroring or database. Introduction to Database Mirroring. Setting Up Database Mirroring. Database Mirroring Witness. Database Mirroring Sessions. Configure availability by using SQL Server database mirroring. Setting Up Database Mirroring. Database Mirroring Best Practices and Performance Considerations. Things to consider when setting up database mirroring in SQL Server 2012. Database Mirroring and Other Features and Components. Database Mirroring Components In SQL Server 2012. Database Mirroring Components In SQL Server 2008 R2.

1 comments:

Post a Comment