locked
Cannot add witness to my mirroring setup RRS feed

  • Question

  • Hi,

    I'm trying to set up mirroring with a witness using my 3 dedicated servers running SQL Server 2008 R2. I've set up everything as described at this blog post

    Mirroring seems to work fine between the principal and secondary server. But when I run the very last command to add the witness

    alter database citec_vc_prod set witness = 'TCP://witness_address:witness_port';

    I get the following error after a few seconds:

    Msg 1456, Level 16, State 3, Line 1
    The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness_address:witness_port'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.



    Also, the event log on the principal shows the following error:

    The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness_address:witness_port'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
    


    I've already shut down the firewall on the witness, and I've successfully telnetted from the principal into the mirroring endpoint of the witness server to make sure there is no connectivity problem. Yet, I still get this error.

    My setup is as follows:
    Principal: SQL Server 2008 R2 Standard 10.50.1617
    Mirror: SQL Server 2008 R2 Standard 10.50.1617
    Witness: SQL Server 2008 R2 Express 10.50.1600


    Please help me SQL gurus, you're my only hope!
    Adrian Grigore http://www.logmytime.de
    Saturday, December 3, 2011 1:04 PM

All replies

  • Sounds like your instance is not configured to accept remote connections (Is that EXPRESS Edition??). To change it, follow the instructions at http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, December 4, 2011 8:36 AM
  • Uri,

    Thanks for your reply. Unfortunately the answer does not seem to be that easy - I can connect to the server via MSMS from a remote computer just fine.


    Adrian Grigore http://www.logmytime.de
    Sunday, December 4, 2011 10:46 AM
  • Hi,

    Do you use IP or hostname in the script? How do you connect with SSMS - IP or hostname? Make sure the SQL Server error log does not have "network name does not exist" message and you use the FQDN if you do not use IP. If you do not have DNS in place, you may use the local hosts file defining FQDN's. Also you may check that the DNS suffix is set for your mirroring partners. These were the most common cases during my mirroring deployments with or w/o certificate based authentications.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Edited by Janos BerkeMVP Sunday, December 4, 2011 11:07 AM added more clarifications
    Sunday, December 4, 2011 10:56 AM
  • This list posted by Robert Davis

    1. Make sure the witness is litening on the assigned port. Remember that if you telnet into the port, that only shows that soemthing is listening on the port. It may not be your endpoint. Use netstat to verify.
    2. Make sure that the endpoint is started and in the role of either witness or all.
    3. Make sure that there are no other endpoints on any instances on the server trying to use the same port.
    4. Make sure that remote conenctions are enabled and that the tcp protocol is enabled.
    5. Make sure that the servers can ping each other and correctly resolve the FQDN's.
    6. Check the hosts file on all servers for any entries with the other servers' names or FQDN.
    7. Make sure the port is not being blocked by the firewall.
    8. Make sure all servers are using the same domain account for the SQL Server service account or explicit permissions have been granted to connect to the endpoint or certificate authentication has been set up.
    9. If using certificate authentication make sure that the certificates are not expired.
    10. Make sure all endpoints are using the same encryption protocol.
    11. Check network latency between the instances.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, December 4, 2011 11:31 AM
  • @ Janos: I'm using DNS to connect to all of my servers (tcp://serverid.topleveldomain.com). The server is visible from the primary server, so this is definitely not a name resolution issue.

    Adrian Grigore http://www.logmytime.de
    Sunday, December 4, 2011 7:08 PM
  • Adrian,

    Thanks for your feedback. In this case please go through the list posted by Uri and let us know the result.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Sunday, December 4, 2011 7:17 PM
  • Hi Robert,
    Thanks for the huge list of suggestions! Please see my check list below.

    1. Make sure the witness is litening on the assigned port. Remember that if you telnet into the port, that only shows that soemthing is listening on the port. It may not be your endpoint. Use netstat to verify.
    2. Make sure that the endpoint is started and in the role of either witness or all.
    3. Make sure that there are no other endpoints on any instances on the server trying to use the same port.
    4. Make sure that remote conenctions are enabled and that the tcp protocol is enabled.
    5. Make sure that the servers can ping each other and correctly resolve the FQDN's.
    6. Check the hosts file on all servers for any entries with the other servers' names or FQDN.
    7. Make sure the port is not being blocked by the firewall.
    8. Make sure all servers are using the same domain account for the SQL Server service account or explicit permissions have been granted to connect to the endpoint or certificate authentication has been set up.
    9. If using certificate authentication make sure that the certificates are not expired.
    10. Make sure all endpoints are using the same encryption protocol.
    11. Check network latency between the instances.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     

    1. Check, I ensured that SQL server is listening to port 5022, not a different application (there is only sql server installed on that machine anyway).

    2. Check (by looking at  master.sys.endpoints)

    3. Not sure how to check that, but since this is a brandnew vanilla web and sql server with nothing else going on, I'd guess that this is the case.

    4. Check, after all I can SSMS into the server just fine from the principal

    5. Check

    6. Check

    7. Check

    8. Check (I am using certificates, btw).

    9. Check, I just added them and it was in the same manner as on the principal and mirror

    10. How can I do this?

    11. Check, latency is consistently below < 20 ms and there are no dropped packets

     

    So the only point that might be an issue is to make sure all endpoints are using the same encryption protocol. But how can I do this?

     

    Thanks,

     

    Adrian

     

     

     

     

     


    Adrian Grigore http://www.logmytime.de
    Sunday, December 4, 2011 7:29 PM
  • Hi,

    You can use this query to get endpoint information:

    select * from sys.database_mirroring_endpoints 
    

    Please note that the encryption_algorithm_desc column.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Sunday, December 4, 2011 7:38 PM
  • Hi Janos,


    Thanks for the hint. I've just checked the sys_database_mirroring_endpoints and all mirroring endpoints on all servers have the same encryption method (none). In fact all endpoint settings in that table are exactly the same, apart from the endpoint_id which seems to be normal. 

    Any other hints on what to check would be greatly appreciated.

    Thanks,

    Adrian


    Adrian Grigore http://www.logmytime.de
    Monday, December 5, 2011 12:12 AM
  • 1)http://blogs.msdn.com/b/grahamk/archive/2008/11/21/how-to-configure-database-mirroring-between-2-instances-on-a-standalone-server.aspx

    2) GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

    3) http://support.microsoft.com/kb/940254

     

    Take a look at those options and let us know how was it going


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, December 5, 2011 6:25 AM
  • 1)http://blogs.msdn.com/b/grahamk/archive/2008/11/21/how-to-configure-database-mirroring-between-2-instances-on-a-standalone-server.aspx

    2) GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

    3) http://support.microsoft.com/kb/940254

     

    Take a look at those options and let us know how was it going


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, December 5, 2011 6:25 AM
  • I tried 2. , but this gives me the following error:

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'ALL'.

    Regarding 1 and 3: Neither resource seems to to apply since am using DNS for name resolution.


    Adrian Grigore http://www.logmytime.de
    Monday, December 5, 2011 9:39 AM
  • Hi,

    change ALL to your logins created from certs. Also make sure that your ISP does not block those ports which are being used by mirroring.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Monday, December 5, 2011 9:49 AM
  • Hi Janos,

     

    CONNECT is already  granted on the mirroring endpoint to both the principal and the mirror server login. I checked with SSMS. Also, as already mentioned, I can telnet into the endpoint just fine, so this is definitely not a firewall or ISP problem.

     


    Adrian Grigore http://www.logmytime.de
    Monday, December 5, 2011 10:45 AM
  • I am still having the problem... So here's a complete log of the T-SQL commands I used to set up mirroring:


    -- PRINCIPAL
    create master key encryption by password = 'mypassword';
    GO
    create certificate "server1.ourdomain.com_cert" with subject = 'server1.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
    GO

    Create endpoint endpoint_mirroring state = started
    as tcp(listener_port = 5022, listener_ip = all)
    for database_mirroring (authentication = certificate "server1.ourdomain.com_cert", encryption = disabled,  role = all);
    GO
    Backup certificate "server1.ourdomain.com_cert" to file = 'f:\cert\server1.ourdomain.com_cert.cer';
    GO

    -- MIRROR
    create master key encryption by password = 'mypassword';
    GO
    create certificate "server2.ourdomain.com_cert" with subject = 'server2.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
    GO
    Create endpoint endpoint_mirroring state = started
    as tcp(listener_port = 5022, listener_ip = all)
    for database_mirroring (authentication = certificate "server2.ourdomain.com_cert", encryption = disabled,  role = all);
    GO
    Backup certificate "server2.ourdomain.com_cert" to file = 'f:\cert\server2.ourdomain.com_cert.cer';
    GO

    -- WITNESS
    create master key encryption by password = 'mypassword';
    GO
    create certificate "witness.ourdomain.com_cert" with subject = 'witness.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
    GO
    Create endpoint endpoint_mirroring state = started
    as tcp(listener_port = 5022, listener_ip = all)
    for database_mirroring (authentication = certificate "witness.ourdomain.com_cert", encryption = disabled,role = witness);
    GO
    Backup certificate "witness.ourdomain.com_cert" to file = 'd:\cert\witness.ourdomain.com_cert.cer';
    GO

    -- PRINCIPAL again
    create login "server2.ourdomain.com_login" with PASSWORD = 'mypassword';
    GO
    create user "server2.ourdomain.com_user" from login "server2.ourdomain.com_login";
    GO
    select * from sys.certificates
    Create certificate "server2.ourdomain.com_cert"
    Authorization "server2.ourdomain.com_user"
    From file = 'f:\cert\server2.ourdomain.com_cert.cer';
    GO
    Grant CONNECT ON Endpoint::endpoint_mirroring to [server2.ourdomain.com_login];
    GO
    ------
    create login "witness.ourdomain.com_login" with PASSWORD = 'mypassword';
    GO
    create user "witness.ourdomain.com_user" from login "witness.ourdomain.com_login";
    GO
    Create certificate "witness.ourdomain.com_cert"
    Authorization "witness.ourdomain.com_user"
    From file = 'f:\cert\witness.ourdomain.com_cert.cer';
    GO
    Grant CONNECT ON Endpoint::endpoint_mirroring to [witness.ourdomain.com_login];
    GO

    -- MIRROR again
    create login "server1.ourdomain.com_login" with PASSWORD = 'mypassword';
    GO
    create user "server1.ourdomain.com_user" from login "server1.ourdomain.com_login";
    GO
    Create certificate "server1.ourdomain.com_cert"
    Authorization "server1.ourdomain.com_user"
    From file = 'f:\cert\server1.ourdomain.com_cert.cer';
    GO
    Grant CONNECT ON Endpoint::Endpoint_mirroring to [server1.ourdomain.com_login];
    GO
    -------
    create login "witness.ourdomain.com_login" with PASSWORD = 'mypassword';
    GO
    create user "witness.ourdomain.com_user" from login "witness.ourdomain.com_login";
    GO
    Create certificate "witness.ourdomain.com_cert"
    Authorization "witness.ourdomain.com_user"
    From file = 'f:\cert\witness.ourdomain.com_cert.cer';
    GO
    Grant CONNECT ON Endpoint::Endpoint_mirroring to [witness.ourdomain.com_login];
    GO

    -- WITNESS again
    create login "server1.ourdomain.com_login" with PASSWORD = 'mypassword';
    GO
    create user "server1.ourdomain.com_user" from login "server1.ourdomain.com_login";
    GO
    Create certificate "server1.ourdomain.com_cert"
    Authorization "server1.ourdomain.com_user"
    From file = 'd:\cert\server1.ourdomain.com_cert.cer';
    GO
    Grant CONNECT ON Endpoint::Endpoint_mirroring to [server1.ourdomain.com_login];
    GO
    -------
    create login "server2.ourdomain.com_login" with PASSWORD = 'mypassword';
    GO
    create user "server2.ourdomain.com_user" from login "server2.ourdomain.com_login";
    GO
    Create certificate "server2.ourdomain.com_cert"
    Authorization "server2.ourdomain.com_user"
    From file = 'd:\cert\server2.ourdomain.com_cert.cer';
    GO
    Grant CONNECT ON Endpoint::endpoint_mirroring to [server2.ourdomain.com_login];
    GO

    -- MIRROR again
    alter database MyDBName set partner OFF
    alter database MyDBName set partner = 'TCP://server1.ourdomain.com:5022';
    GO

    -- PRINCIPAL again
    alter database MyDBName set partner OFF
    alter database MyDBName set partner = 'TCP://server2.ourdomain.com:5022';
    GO
    alter database MyDBName set witness OFF
    alter database MyDBName set witness = 'TCP://witness.ourdomain.com:5022';
    GO


    Once it did not work, I deleted everything and started from scratch to make sure I did not miss anything, but the problem still stayed the same.


    Adrian Grigore http://www.logmytime.de
    Tuesday, December 6, 2011 2:56 PM
  • Are they SQL Server service running under domain account does it have enough permissions on both principal, mirror and witness server.

    have you assign endpoint connect permissions to service account?


    http://uk.linkedin.com/in/ramjaddu
    Tuesday, December 6, 2011 4:19 PM
  • Are they SQL Server service running under domain account does it have enough permissions on both principal, mirror and witness server.

    have you assign endpoint connect permissions to service account?


    http://uk.linkedin.com/in/ramjaddu
    I am using certificate-based authentication, not windows domains. Just one post over yours you can see all t-sql commands I used to set this up.

    Adrian Grigore http://www.logmytime.de
    Tuesday, December 6, 2011 4:33 PM