Monday 28 February 2011

Updating Witness Server in DBM High Availability/Auto Failover failed

Issue
My witness server is hosted in a datacentre that is no longer available (server is down).

When attempting to update my witness server in a SQL 2005 Database Mirroring solution (High Availability/Automatic Failover) I get an error.

Troubleshooting
Command:
ALTER DATABASE [DBName] SET WITNESS = 'TCP//SERVER.domain:port'

Error:
Msg 1426, Level 16, State 2, Line 1
To issue ALTER DATABASE SET WITNESS, all three server instances must be interconnected, and the mirror database must be caught up. When these conditions are met, reissue the command

Fix
Before altering the witness server, run a command to drop the existing witness:
ALTER DATABASE [DBName] SET WITNESS OFF

Then run the command to update the witness:
ALTER DATABASE [DBName] SET WITNESS = 'TCP//SERVER.domain:port'

How to update the Witness in a Database Mirroring solution

- Note that no failover occurs because of this change and no change in DBM state

On the principal:
select * from sys.database_mirroring to check what is configured

Check if the (new) witness server has database mirroring endpoint enabled?
SELECT role_desc, state_dec FROM sys.database_mirroring_endpoints

Check what other endpoints are setup for SQL Server:
SELECT name, port FROM sys.tcp_endpoints

If no endpoint enabled on the (new) witness, create one:
CREATE ENDPOINT endpoint_mirroring
STATE = started
AS TCP ( LISTENER_PORT = [port] )
FOR DATABASE_MIRRORING (ROLE=WITNESS);
GO

Grant connect permissions on endpoint to service account:
GRANT CONNECT ON ENDPING::Endpoint_Mirrproing to [Domain\Account];

Remove the old witness server from DBM:
ALTER DATABASE [DBName] SET WITNESS OFF

Add the new witness server for DBM:
ALTER DATABASE [DBName] SET WITNESS = 'TCP//SERVER.domain:port'

No comments:

Post a Comment