Strange Database Mirroring Problem

Question

I am noticing a strange problem with DB mirroring on SQL Server 2012 (standard edition). The mirroring is set up, and successfully established, and working. There is no witness.

Once principal is rebooted, all the databases are stuck in recovery mode, thus making the DBs inaccessible. Same thing happens with mirror, when the server is restarted, the DBs are stuck in recovery. In order to get out of the recovery, the endpoint needs to be stopped and started again. I've checked the following:

1. Mirroring is working during normal setup.
2. Accounts have permissions to establish mirroring.
3. All DBs have owners that is sa.
4. SQL Server is running under domain account and the owner is sysadmin on the server. I also gave it explicit grant/authorization to the endpoint.
5. The error log (startup) says that the endpoint is started.

In order to make the DBs accessible again, the following code is executed:

ALTER ENDPOINT mirroring STATE = STOPPED

ALTER ENDPOINT mirroring STATE = STARTED

After executing the above query, DBs come back to normal state.

Startup error log:

2016-04-03 14:03:24.900   spid18s Database Mirroring Transport is disabled in the endpoint configuration.

2016-04-03 14:03:30.240   spid28s The Database Mirroring endpoint is now listening for connections.

Followed by Database Mirroring is Active with Database 'DatabaseName' as principal copy.

After the above code is executed:

2016-04-03 14:12:42.500   spid67  The Database Mirroring endpoint has stopped listening for connections.  

because I am stopping it

2016-04-03 14:12:46.530   spid67  The Database Mirroring endpoint is now listening for connections. 

because I am starting it

At this point, Database mirroring is active with database 'DatabaseName' as principal copy shows up again.

Thanks in advance.

Answer 1

I seriously suggest building another system with SQL Server Express and configure it as your witness server. Then set your operating mode to "High safety with automatic failover (synchronous)".

This solution will alleviate you of this problem. You will incur a cost for the desktop windows license, but as you may already know, SQL Server Express is of no cost monetarily.

Answer 2

to me it appears that there is something wrong as the way mirroring has been set up.

you could use the query below, on the server where the principal databases are.

this would give you some visibility as things have been set up.

--==============================================================================
-- query that shows the current state of each database in the mirroring
--==============================================================================
  SELECT   db_name(sd.[database_id])              AS [Database Name]
          ,sd.mirroring_state                  AS [Mirror State]
          ,sd.mirroring_state_desc             AS [Mirror State] 
          ,sd.mirroring_partner_name           AS [Partner Name]
          ,sd.mirroring_role_desc              AS [Mirror Role]  
          ,sd.mirroring_safety_level_desc      AS [Safety Level]
          ,sd.mirroring_witness_name           AS [Witness]
          ,sd.mirroring_connection_timeout AS [Timeout(sec)]
    FROM sys.database_mirroring AS sd
    WHERE mirroring_guid IS NOT null
    ORDER BY [Database Name];

this is an example of the output