SQL Mirroring End Points

Question

We have a SQL 2005 database server which uses high safety (synchronous, no automatic failover, no witness) mirroring to keep our disaster recovery site in a sort of warm standby. I was about to make some changes to the principle DB the other night preparatory to a new version of the app which uses it when I noticed that the mirroring had stopped the previous Friday whilst I was on leave. The SQL logs and system event logs seemed to suggest a network issue, but after three days of head scratching, in desperation I dropped and re-created the endpoints and it turned out that the endpoint on the principle had failed. My suspicions had been entirely pointed at the remote partner server until then.

Does anyone have any experience of end points failing and the causes thereof? I am still at a loss to explain exactly what happened or why.

Answer 1

'Endpoints failing' is a little bit vague. You should check the principal errorlog and see if there is any pertinent message, like 'endpoint could not start'. If I'd venture a guess I'd say some application took the mirroring listenning port, thus preventing it from starting up. The other likely culprit is authentication failed, eg. the account password was changed (for Windows auth) or the endpoint certificate has expired (for cert auth).

In case it was a certificate expiration issue, I recommend these articles on the topic How does Certificate based Authentication work and Replacing Endpoint Certificates that are near expiration