One of the key high availability technologies available in Microsoft SQL SERVER 2005 is database mirroring. When configured with a witness server, the mirroring facility allows automatic failover.
A common error that is seen when setting up the witness server for a database mirror configuration of Microsoft SQL SERVER is the following:
The ALTER DATABASE command could not be sent to the remote server instance 'tcp://witness.example.com:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (.Net SqlClient Data Provider)
A search of the internet for the terms: MICROSOFT SQL SERVER MIRROR WITNESS ALTER DATABASE yields many results which will tell you that there are many administrators who have run into problems with creating the witness server that is required for automatic failover.
There are far fewer answers than questions. The answers also do not seem to be complete in that they seldom fix the problem.
The reason may be that the reported error is the error reported by the gui in SQL MANAGEMENT STUDIO. If the administrator takes the time to look in the logs, the underlying reason is much easier to determine. Even as an administrator struggles to overcome the reported problem, it can be seen at each stage that the message reported in the gui remains the same while the underlying log message changes.
It is also little known that there is a wealth of diagnostic information to be gained from looking at the system tables involved in controlling the mirror and witness behaviour.
The transact-sql queries that we use to diagnose this problem are:
select * from sys.database_mirroring_endpoints select * from sys.sysusers select * from sys.server_principals select * from sys.symmetric_keys select * from sys.certificates
Examine the results for inconsistencies. Each case will be a little different, so it is not possible to point at any particular item to look at. Most of the problems arise from confusion or multiple attempts at creating the witness server role. At some point, some part of the procedure will be forgotten and the result will be a non-functioning mirror configuration. The results of the queries will almost always point a knowledgeable database administrator in the right direction. Of course, this presumes that the administrator also has a grounding in network communications, nomenclature, security, and encryption.
The procedure developed by platformlabs does not follow the conventional order. Instead it is designed to avoid confusion and to avoid hopping back and forth between the different servers. If you have network access to all servers, the entire procedure can be performed from a single remote terminal services session to one of the servers.
In general, we recommend the following work sequence, assuming the three servers are named XXX, YYY, ZZZ:
create certificates and endpoints:
use master go create master key encryption by password = 'the.secret.password' create certificate XXX with subject = 'XXX', start_date = '2007/11/01', expiry_date = '2020/11/01' backup certificate XXX to file = 'c:\XXX.cer'
copy the certificates to all three machines
create logins and users, endpoints, grant access
use master go create endpoint XXX state = started as tcp ( listener_port = 5022, listener_ip = all ) for database_mirroring ( authentication = certificate XXX, encryption = [disabled|AES|MD4], role = [all|witness] ) create login XXX with password = 'the.secret.password' create login YYY with password = 'the.secret.password' create login ZZZ with password = 'the.secret.password' create user XXX from login XXX create user YYY from login YYY create user ZZZ from login ZZZ create certificate YYY authorization YYY from file = 'c:\YYY.cer' create certificate ZZZ authorization ZZZ from file = 'c:\ZZZ.cer' grant connect on endpoint::mirroring to [YYY] grant connect on endpoint::mirroring to [ZZZ]
At this point, all the database mirroring endpoints should be functioning, and able to participate in a database mirror configuration with automatic failover.
Please note that in the above examples, there are a number of places where the reader needs to make appropriate substitutions to accommodate their particular needs and configuration.
If you require additional assistance, platformlabs will accept engagements to perform the work remotely at a fixed fee of $750.00 payable by credit card.
The work assets that will be required prior to commencement of work are: