Sql-server – Error configuring witness server for failover in SQL Server

database-mirroringfailoversql server

I'm lost here, I am configuring a SQL Server for failover and have three servers.

Principal server > SQL 2008 R2
Witness server > SQL 2014
Mirror server > SQL 2008 R2

All three servers can ping the other via IP and FQDN.

The endpoints configure successfully but when I go to start mirroring, I get this error:

Alter failed for Database 'DBNAME'

The ALTER DATABASE command could not be sent to the remote server instance 
'TCP://witness.domain.abc:5022'.  The database mirroring configuration was not changed.  
Verify that the server is connected and try again.

Any idea what this error is?

I restored the database and transaction log to the mirror server. All I did on the witness server is set up SQL, is there anything else I need to do?

Banging my head against the wall here.

Best Answer

You've created an ENDPOINT on the witness, right?
Read this on how to create a witness endpoint.

Check that there isn't either the Windows firewall or a network device blocking the endpoint port on either principal to witness or mirror to witness. I always like to use:

telnet hostname port

To test that the ports are open. You should get a connection with telnet. A time out or a connection refused means some things is wrong.
I'd also check the authorization used on the endpoints. KERBEROS requires SPNs setup for it to work. NEGOTIATE is the most compatible, but weakest in terms of security.

Related Solutions

Sql-server – SQL Server Mirroring – endpoint permissions – why grant connect to SQL service account

The requirement to create a login for the peer and grant connect permission is required only if such a permission is necessary. If the peers (and witness) run under a service account that already has the required privileges, then the grant is not technically required.

However I would recommend that, whatever you do, do it consistently. If all your DBM deployments always create a login for the peer(s) and grant connect permission on the DBM endpoint, then do it everywhere, even if not required. If you decide that the explicit logic/connect permission is not necessary, be consistent and don't add this login anywhere.

Sql-server – Cannot add witness to the mirroring setup

Can you attach the SQL Profiler to all three instances involved and monitor for these events:

Then attempt again to establish the mirroring session. Make sure you select all columns when adding the events. Start from a blank template.

2011-12-09 20:04:07.983  Database Mirroring Connection    Connected   
2011-12-09 20:04:08.133  Audit Database Mirroring Login   Login Success
2011-12-09 20:04:27.980  Database Mirroring State Change DBM: 
  Synchronized Principal without Witness -> 
  DBM: Synchronizing Principal             
2011-12-09 20:04:28.237  Database Mirroring State Change DBM: 
  Synchronizing Principal -> 
  DBM: Synchronized Principal without Witness
2011-12-09 20:05:42.530  Database Mirroring Connection   An error occurred...

Here is the explanation for what happens:

at 20:04:07 the principal connect with the witness
at 20:04:08 the principal and witness finish a successful handshage (audit login succes)
at 20:04:27 DBM changes the state from synchronized w/o witness to synchronized
at 20:04:28 DBM changes the state back from syncronized to synchronized w/o witness
at 20:05:42 the connection with the witness times out due to inactivity and closes

This sequence of events indicates that the connectivity between principal and witness is functional. The request to add the witness fails, and there could be multiple reasons. Similar events must occur on the mirror and on the witness as well, is not clear why you say that you could only capture them on the principal.

Best Answer

Related Solutions

Sql-server – SQL Server Mirroring – endpoint permissions – why grant connect to SQL service account

Sql-server – Cannot add witness to the mirroring setup

Related Topic