SQL Server – Adding a Witness Without Restarting

high-availabilitysql serversql-server-2005

I want to add a third instance of SQL server to my mirroring pairs to act as a Witness. When I set the witness, the mirror remains disconnected from the witness and the mirror's log accumulates errors. There are two distinct errors that occur with the same timestamp repeating every ~24 seconds.

Mirror Error Message 1:

Error: 1438, Severity: 16, State: 2.

Mirror Error Message 2:

The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.

There are no corresponding errors on the witness server that I could find.

Database Mirroring Monitor will show the witness connection as disconnected on the mirror

Database Mirroring Monitor

In this scenario, I have three instances of SQL server. SqlServer01 is the principle. SqlServer02 is the mirror. The operating mode is high safety. I am attempting to transition to high safety with automatic fail-over by adding SqlServer03 as a witness.

All instances are running the same version SQL Server 2005, 9.00.5292.

Authentication is via SQL Server users with certificate.

The only solution I've found thus far is to restart SQL Server on the witness. Is there a better way? Why does the witness need to be restarted?

I would really like to avoid restarting witnesses because when I set this up in my production environment, witnesses are also part of separate mirroring pairs with their own separate witnesses. Therefore, restarting SQL server on one server will cause a fail-over of all the principal databases on that server.

Best Answer

Here's a way to avoid restarting SQL server. It restarts the mirroring endpoint.

Run on the witness server:

--Run the following on a witness server after configuring a witness
USE [master];
DECLARE @Endpoint NVARCHAR(128);
DECLARE @Cmd NVARCHAR(MAX);
SELECT @Endpoint = (SELECT name
                    FROM sys.endpoints
                    WHERE type = 4);
SELECT @Cmd = 'ALTER ENDPOINT [' + @Endpoint + '] STATE = STOPPED;';
EXEC (@Cmd);
SELECT @Cmd = 'ALTER ENDPOINT [' + @Endpoint + '] STATE = STARTED;';
EXEC (@Cmd);

My servers are already in mirroring pairs with endpoints configured only for PARTNER roles. My witness deployment procedure is as follows:

  1. Add the witness role to each server's endpoint. Each server only supports one endpoint of type four. 

    --Run the following on a each witness server before configuring it as a witness to enable WITNESS and PARTNER roles
USE [master];
DECLARE @Endpoint NVARCHAR(128);
DECLARE @Cmd NVARCHAR(MAX);
SELECT @Endpoint = (SELECT name
                    FROM sys.endpoints
                    WHERE type = 4);
SELECT @Cmd = 'ALTER ENDPOINT [' + @Endpoint + '] FOR DATABASE_MIRRORING (ROLE = ALL);';
EXEC (@Cmd);

  2. For Each Server:

    1. Set the witness for each principal database: 

      USE [master]
ALTER DATABASE [TODO] SET WITNESS = N'TCP://WITNESS_FQDN:ENDPOINT_PORT';

  3. For Each server configured to be a witness:

    1. Manually fail-over all principal databases so that the server only contains mirrors
    2. Restart the mirroring endpoint on the server to fix connection issues (refer to the script at the beginning of this answer)
  4. After the mirrors have finished any log synchronization backlog, manually fail-over databases as needed so that the primary server is as it was initially.
Related Question