locked
Moving mirror witness to pc on different domain RRS feed

  • Question

  • Hi all,

    I'd like to know first off if this is possible...

    We currently have a DB mirroring pair across two VM's (on separate physical machines) and a separate witness on another vm on the same host machine as one side of the pair.

    One of our hosts had a small blip recently and it took down the witness, so nothing failed over...

    Due to space and money restrictions... etc. I can't put another physical box into the mix to act as a standalone dedicated witness. Instead I'm trying to setup another VM at a remote location (this is on a different domain but does have a domain trust setup between this location and the location of the mirrors,) to act as an external witness.

    I can ping the FQDN and ip address which all resolve fine. I've done a portquery either end and both sides are listening on port 5022. There are firewalls in the mix but I've allowed this port through.

    I can get so far in the configure security wizard and then when I click the final 'OK' I'm hit with:

    "Alter failed for Database 'X' (Microsoft.SqlServer.Smo)

    An exception occurred while executing a Transact-SQL statement or batch (Microsoft>SqlServer.ConnectionInfo)

    The ALTER DATABASE command could not be sent to the remote server instance." Error 1456

    I've tried using both SQL and windows authentication but neither seem to get past this. I did previously notice an error with pings but I've updated the DNS entries and that seems to have fixed that problem.

    Is what I'm trying to do possible and if so does anyone have any clues as to what could be wrong please? (All updates and patches have been installed.)

    Thanks in advance.


    Mark

    Friday, March 7, 2014 4:48 PM

Answers

  • Hi,

    According to the description, I know you are able to ping each other by host name and the Witness server is listening on the port number 5022.

    I have not tested this scenario, however you may check the following and see how it helps. And it is strongly recommended that the witness reside on a separate computer from either of the partners. The service account used by the witness must be in the same domain as the service accounts used by the principal and mirror server instances, or it must be in a trusted domain.

    1. Do you use the same user account for SQL Server service on the principal and the mirror servers? Grant connect permissions on the endpoint to the login and check permissions are present.

    2. Test telnet between witness and Principal, Witness and Mirror.

    3. Check the output of sys.database_mirroring_endpoints. And you may need to drop the mirroring session and set it up all over again to include the witness.

    Execute Alter database <database name> set partner off at the mirror. For information, see about the impact of removing mirroring, see Removing Database Mirroring (SQL Server).

    Restore the backups at the mirror server without recovery and then again go ahead to setup mirroring.

    Note: Establishing a new mirroring session or adding a witness to an existing mirroring configuration requires that all involved server instances run the same version of SQL Server. http://msdn.microsoft.com/en-us/library/ms189852.aspx

    4. If still no progress, check the error log on both Principal and Witness server.

    Thanks.

    Tracy Cai
    TechNet Community Support

    • Marked as answer by tracycai Tuesday, March 18, 2014 8:50 AM
    Monday, March 10, 2014 6:07 AM