Mirrored Databases disconnect and goes in-recovery state after a server restart

Recently, I had a situation where a production server was restarted and soon after the restart we found that the database mirroring was disconnected.
And as a bonus, one of the mirrored database was hung in recovery state.

When I investigated further, I found the below message in the SQL Server Logs.

Bypassing recovery for database '<DB Name>' because it is marked as an inaccessible database mirroring database. A problem exists with the mirroring session. The session either lacks a quorum or the communications links are broken because of problems with links, endpoint configuration, or permissions (for the server account or security certificate). To gain access to the database, figure out what has changed in the session configuration and undo the change.

This clearly says the connection between the principal and  the mirror is not established properly and the combination of a Mirrored Database with a big log file and a server restart caused the "In Recovery State" of the mirrored database.
To fix this error, I had to Restart the Database Mirroring Endpoint.

--To Stop the Endpoint
ALTER ENDPOINT <EndPoint Name> STATE=STOPPED

--To Start the Endpoint
ALTER ENDPOINT <EndPoint Name> STATE=STARTED

Check AutoClose Status of a Database

For Setting up Database Mirroring, one of the important pre-requisite is to turn off AutoClose option on the database.
This script helps to check the status of the autoclose option on a database and will generate the script to disable autoclose if it is enabled.

DECLARE @DBName NVARCHAR(100)
SET @DBName ='SansSQL'
PRINT CASE WHEN DATABASEPROPERTY ( @DBName, 'IsAutoClose') = 1
   THEN 'AUTO Close is enabled. Run the below statement to Disable Auto Close '+ 
     char(10) + '----------------------------------------------------------'+ char(10)+
     'ALTER DATABASE '+ @DBName +' SET AUTO_CLOSE OFF'
   WHEN DATABASEPROPERTY ( @DBName, 'IsAutoClose') = 0
   THEN 'AUTO Close is Disabled'
  END

T-SQL Query to find the list of databases mirrored

Here is an T-SQL Query to find the list of all databases mirrored in a particular SQL Server instance.

SELECT [Database Name] = DB.name
   ,[Recovery Model] = DB.recovery_model_desc 
   ,[Mirroring Status] = CASE WHEN DM.mirroring_state is NULL THEN 'Not Mirrored' ELSE 'Mirrored' END
FROM sys.databases DB, sys.database_mirroring DM
WHERE DB.database_id=DM.database_id
ORDER by name

Configuring Database Mirroring and Testing Mirroring Failover

Slno Steps Stage Remarks   1 Set the Database (Those Involved in Mirroring)  to Readonly Pre Configuration While Configuring on production environment, set the involved database to Read-Only to avoiding data modification during configuration   2 Backup Databases in Source Pre Configuration   3 Move Backups to local Drives on Destination Server Pre Configuration   4 Restore Backups on Mirror server with No recovery Pre Configuration Execute AS USER='dbo' (To Make the Database Owner as 'sa') Recommended to Use Scripts while restoring. Make Use of the above Query in order to restore the database with 'sa' as owner when logged in as a different User. Ex: EXECUTE AS USER='dbo' GO Restore Database XYZ from Disk='C:\....' ....   5 Manually Change the DB Owner to "sa" Pre Configuration If not Using Scripts in Step 4 Ex: Use DBName GO Exec sp_changedbowner 'sa'   6 Make sure that the Databases are in FULL Recovery Mode Pre Configuration Full recovery mode against the principal instance databases   7 Script out required logins from Principal server using sp_help_revlogin (More Information @ http://support.microsoft.com/kb/918992) and Create on VM's (Principal and Mirror Pre Configuration This is done to match the SID's for SQL Users on both Principal And Mirror   8 Make sure that the Default Database for the Logins involved in mirroring should be 'Master' Pre Configuration   9 Make Sure SQL Services are running under the same service account Pre Configuration   10 Right-Click on the Principal Database and Go to properties Mirror Configuration     10.A Go to "Mirroring" Tab Mirror Configuration     10.B Click on "Configure Security" Mirror Configuration     10.C Choose "Yes" In the Include Witness Server Page Mirror Configuration     10.D In the Next Page, Check the "Witness Server Instance" option Mirror Configuration     10.E Choose the Port Number and Chage the Endpoint Name For the Principal Server. Mirror Configuration     10.F In the Next Page, Connect to the Mirror Server and Choose the appropriate Port Number and Endpoint Name for the Mirror Server. Mirror Configuration     10.G In the Next Page, Connect to the Witness Server and Choose the appropriate Port Number and Endpoint Name for the Witness Server. Mirror Configuration     10.H In the Next Page, provide the Service Account Names under which mirroring should be configured. Mirror Configuration Preferably a Service Account     10.I Once step 10.H is completed, click Finish to start the configuration of Mirroring Mirror Configuration     10.J Once the Mirroring is setup, it will prompt for starting the Mirroring. Choose "Start Mirroring" if you are sure that the all the Information that is shown in the Popup box is correct. If you find any discrepancies choose "Do not Start Mirroring" and then make the necessary changes and then Start the mirroring. Mirror Configuration     10.K In the Database Properties Mirroring Page, the status should show "Synchronized: the databases are fully synchronized" if there are no issues encountered. Mirror Configuration     10.L Once Step 10.K is confirmed, Click "OK" on the Database Properties Mirroring Page Mirror Configuration   12 Make Sure That the Mirror Database status is "Mirror, Synchronized / Restoring ..." Post Configuration   13 Give appropriate access to the DB Users in Principal Post Configuration   14 Give appropriate access to the DB Users in Mirror Post Configuration FailOver must be done for giving access to the Mirror Database   15 To Check the Mirroring Status, Right-Click on the Principal Database and go to "Tasks" and choose "Launch Database Mirroring Monitor …" Post Configuration   16 Register The Mirrored Databases by connecting to the appropriate Instance of SQL Server Post Configuration   18 Go to "Mirroring" Tab Testing / Test Case 1   19 And Click "FailOver" Testing / Test Case 1   20 In the PopUp Window, Choose "Yes" Testing / Test Case 1   21 Once the failover is complete, Login to the Mirror (Now Principal) using a SQL User and run few queries against the database Testing / Test Case 1   22 Change the Default DB of a SQL Login to the Mirrored Database. Testing / Test Case 2   23 Initiate Failover Testing / Test Case 2   24 Once the failover is complete, Login to the Mirror (Now Principal) using the SQL User and run few queries against the database Testing / Test Case 2   25 Shut Down SQL Services on the Principal Server (Right Click on the Server and Choose Stop Services) Testing / Test Case 3   26 Once the Services is stopped, Login to the Mirror  (Now Principal) and run few queries against the database Testing / Test Case 3   27 Hard Shut Down SQL Services on the Principal Server (Execute SHUTDOWN WITH NO WAIT) Testing / Test Case 4 Execute The Command Execute SHUTDOWN WITH NO WAIT   28 Once the Services is stopped, Login to the Mirror  (Now Principal) using a SQL User and run few queries against the database Testing / Test Case 4   29 Stop SQL Services on Witness Server. Testing / Test Case 5 There should be No Effect on Mirroring   30 Pause the Mirroring Testing / Test Case 6   31 Stop the Services on Principal Testing / Test Case 6   32 Rename the Data file of the Principal database Testing / Test Case 6   33 Start the Services. Testing / Test Case 6 Does not Fail over to the Mirror.   34 Stop the Services on Principal Testing / Test Case 6 To Bring back to Original State   35 Rename the Data file of the Principal database to original Name Testing / Test Case 6   36 Start the Services. Testing / Test Case 6 Principal and Mirror databases are still on the Same Servers.

Order of Installing Service Pack on systems hosting Database Mirroring

If you have servers which hosts database mirroring and you want to install the service pack on those systems and you are trying to find out the order in which you need to do it then, here is the order in which you need to install service pack or a hotfix on the systems which hosts Database Mirroring.

1. Backup the principal
2. If you are using a witness, remove it from mirroring
3. Upgrade the mirror
4. Failover to mirror
5. Upgrade original principal/current mirror
6. If you wish to fail back to original principal continue on; otherwise, proceed to step 8
7. Failover to original principal
8. If you are using a witness, add it back into mirroring

For more information refer to the following article to know the entire process for upgrading both the principal and mirror servers.
http://technet.microsoft.com/en-us/library/bb497962(SQL.90).aspx