3

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.

  • what does windows event log say ? Are permissions fine for both server's service account on witness ? – Kin Shah Jul 11 '18 at 16:10
  • @Kin The windows event log on the mirror has the same reason 1451, and state 3 messages. There are no relevant events in the event log on the witness server. – Jon Jul 11 '18 at 16:15
  • @Kin Are you asking about the user account running MS SQL Server or are you talking about the logins which have GRANT CONNECT ON ENDPOINT? Most SQL processes are running as SYSTEM and another process (MsDtsSrvr.exe)is running as NETWORK SERVICE. How do I verify permissions? – Jon Jul 11 '18 at 16:32
  • I would say .. it needs a domain account that can talk with each other and that domain service account needs sysadmin on all the 3 servers (pri - sec and witness). – Kin Shah Jul 11 '18 at 16:43
  • @Kin There is no common domain server in this environment. Logins are via SQL server authentication accounts. I just tried granting the sysadmin role to those SQL logins and it did not resolve the issue. Thanks for the suggestion though. – Jon Jul 11 '18 at 17:03
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.