locked
Database mirroring with multiple instances and databases, how to set up the witness server? RRS feed

  • Question

  • Hi,

    I am using SQL Server 2008 standard edition and want to set up mirroring, together with a witness server for automatic failover.
    I have 1 SQL Server running 5 named instances and about 10 databases in total and am mirroring them to a second SQL Server.
    For this I have created different endpoints on different network ports to facilitate in the mirroring. This is all going fine and my databases are being mirrored.

    Now I set up a third SQL Server with only the default instance as a witness server. For the first instance I added this witness to, it works fine.
    But I can't add the witness server to another mirror of another of my named instances.

    Should I add all 5 named instances to the witness server as well? Or should I create other endpoints at my witness for every named instance?
    Monday, December 14, 2009 3:56 PM

Answers

  • Thanks for all the replies.

    I finally solved the problem.

    Let me allaborate the situation:
    I have 3 SQL Servers, one principle, one mirror and one witness. All 3 are in the same network segment and only the Windows firewall is blocking on port level. All 3 servers are in the same domain and they all use SQL Server 2008 with SP1.
    The principle and mirror are Windows 2008 64-bit with SP2. Both have the same 5 named instances.
    The witness server is Windows 2008 32-bit with SP2. Here only the default instance is used.
    Each instance has a seperate service account per server.

    I now have 5 instances mirrored and this is working correctly. I now started adding the witness server for automatic failover when my problems began.
    Between the principle and mirror I use port 5022 till 5025 (one port per instance) for mirroring.
    To the witness I connect through port 5022 for every instance.

    In the mirroring monitor is see the following connection status:
    - Instance A, B and C the mirroring state is Synchronized and the Witness is Connected. This is as it should be.
    - Instance D the mirroring state is Synchronized for both servers and the Witness is Connected for the principle and disconnected for the mirror. Why is this disconnected? It's using the same port as instance A till C that are working correctly, so that should be no problem. On the witness I also see the required logins that were created.
    - Instance E the mirroring state is Synchronized for both server, but when I try to add the witness I get SQL error 1456:
    "The ALTER DATABASE command could not be sent to the remote server instance TCP://mywitnessserver:5022". The database mirroring configuration was not changed. Verify that the server is connected and try again."

    In the logfiles I found an entry saying:
    "Database mirroring login attempt failed with error: Connection handshake failed There is no compatible encryption algorithm. State 22"

    Looking for this error revealed that Instance E was using an unencrypted connection between principle and mirror and an encrypted connection to the witness. Removing the mirroring did not remove the endpoints, so I removed both endpoints on princple and mirror and then recreated the mirroring with encryption. Now Instance E was working correctly and all witness were connected.

    After rebuilding the mirroring on instance D this also works correctly, even though encryption was turned on there before and I did not change that settings...

    Thanks for all the replies and hope somebody is being helped with this explanation.

    • Marked as answer by Bart Leving Friday, December 18, 2009 9:33 AM
    Friday, December 18, 2009 9:33 AM

All replies

  • There is no need to create another end point ,There can be only one mirroring endpoint per instance .and the same needs to be used for all the other . A single witness server can monitor multiple mirror pairs . 

    But I can't add the witness server to another mirror of another of my named instances

    I am not clear with the above can u elaborate the issue ?

    --------------

    Thanks ,Suhas V 


    Tuesday, December 15, 2009 7:40 PM
  • Hi,

    For database mirroring, a server instance requires its own, dedicated database mirroring endpoint. A database mirroring endpoint is used to distinguish that instance from any other instances over the network. So you only need to create an endpoint on the witness server instance.  To add a database mirroring witness, you need to set the witness for the witness server instance on each of the principal server instance with the following statement:
    ALTER DATABASE <database_name>
        SET WITNESS =
        ' TCP :// < system-address of the witness server > : < port>'
    GO

    If there are any more questions, please let me know.
    Thanks.
    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, December 16, 2009 9:37 AM
  • hi, please go through the database mirroring in my blog

    http://www.my-sql-share.blogspot.com

    Thursday, December 17, 2009 9:49 PM
  • Thanks for all the replies.

    I finally solved the problem.

    Let me allaborate the situation:
    I have 3 SQL Servers, one principle, one mirror and one witness. All 3 are in the same network segment and only the Windows firewall is blocking on port level. All 3 servers are in the same domain and they all use SQL Server 2008 with SP1.
    The principle and mirror are Windows 2008 64-bit with SP2. Both have the same 5 named instances.
    The witness server is Windows 2008 32-bit with SP2. Here only the default instance is used.
    Each instance has a seperate service account per server.

    I now have 5 instances mirrored and this is working correctly. I now started adding the witness server for automatic failover when my problems began.
    Between the principle and mirror I use port 5022 till 5025 (one port per instance) for mirroring.
    To the witness I connect through port 5022 for every instance.

    In the mirroring monitor is see the following connection status:
    - Instance A, B and C the mirroring state is Synchronized and the Witness is Connected. This is as it should be.
    - Instance D the mirroring state is Synchronized for both servers and the Witness is Connected for the principle and disconnected for the mirror. Why is this disconnected? It's using the same port as instance A till C that are working correctly, so that should be no problem. On the witness I also see the required logins that were created.
    - Instance E the mirroring state is Synchronized for both server, but when I try to add the witness I get SQL error 1456:
    "The ALTER DATABASE command could not be sent to the remote server instance TCP://mywitnessserver:5022". The database mirroring configuration was not changed. Verify that the server is connected and try again."

    In the logfiles I found an entry saying:
    "Database mirroring login attempt failed with error: Connection handshake failed There is no compatible encryption algorithm. State 22"

    Looking for this error revealed that Instance E was using an unencrypted connection between principle and mirror and an encrypted connection to the witness. Removing the mirroring did not remove the endpoints, so I removed both endpoints on princple and mirror and then recreated the mirroring with encryption. Now Instance E was working correctly and all witness were connected.

    After rebuilding the mirroring on instance D this also works correctly, even though encryption was turned on there before and I did not change that settings...

    Thanks for all the replies and hope somebody is being helped with this explanation.

    • Marked as answer by Bart Leving Friday, December 18, 2009 9:33 AM
    Friday, December 18, 2009 9:33 AM