We help IT Professionals succeed at work.

SQL Mirroring problem

umentech asked
Someone removed the witness from our SQL Mirroring configuration.
He wanted to do a test. (Bad idea on a production server)
Now, when i try to add the witness, i get the following error message :

ALTER DATABASE xyz SET WITNESS='TCP://abc.domain.com:7022';
Msg 1427, Level 16, State 31, Line 1
The server instance 'TCP://abc.domain.com:7022' could not act as the witness. The ALTER DATABASE SET WITNESS command failed.

All servers use SQL 2008 (10.0.4000)
Endpoints are started. Authentication by certificate.
I can telnet the port.

Everything worked fine before the witness was removed. Why can not I add it?
Any idea ?

Watch Question

Daniel_PLDB Expert/Architect
Top Expert 2011

Please check wheteher ports and encryption options are all the same on all servers, check sys.database_mirroring_endpoints view. Please also check SQL Server error logs.
I solved the problem.

In the witness error logs, i found a lot of connection failure from the mirror.
It seems that when the witness has been removed, the principal and the mirror was out of sync.
So, the principal has no witness, but the mirror still have one!

To correct the problem, i had to do a failover. (The mirror become the principal)
No more connection failure in the witness error logs.
I tried adding the witness again but i still got an error message.
In the principal error log, i saw that the witness refused the connection.
So, i restarted SQL Server on the witness and re-try. And it works!


I solved the problem.