microsoft sql server database mirroring setup

Need it fixed quick? Payment by credit card available.

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://'.
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

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

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:

Need it fixed quick? Payment by credit card available.


copyright © 2003 - 2016
all rights reserved

microsoft platform hosting

site uptime monitoring

managed global failover dns

emergency failover services

web site spell check
overdone ...
not done over