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:
- Mirroring is working during normal setup.
- Accounts have permissions to establish mirroring.
- All DBs have owners that is sa.
- 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.
- 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.
SA
. If you had donealter authorization ..
then you have to explicitelyGRANT CONNECT ON ENDPOINT::mirroring_endpoint TO [account] -- change this as per your env.
Check connectivity between the servers as well as security settings on endpoint.