locked
SqlServer 2008 Mirroring - 1418 RRS feed

  • Question

  • Does the server have to be running on port 1433. Our server is running on port 1400 and I cant seem to establish the connection between Principle and Mirror.

    Any help would be appreciated.

    Thanks.

     

    Thursday, December 1, 2011 10:00 PM

Answers

All replies

  • Hi,

    I believe you can use any available port but you must make sure that any hardware or software firewalls allow traffic on that port between the endpoints. You can check the configured ports via: -

    SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints

    WHERE type_desc = 'DATABASE_MIRRORING'

    http://technet.microsoft.com/en-us/library/ms189921.aspx

     

     


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog or Twitter.
    Please click the Mark as Answer button if a post solves your problem!

    Thursday, December 1, 2011 10:19 PM
  • Hi Sean,

    Thank you very much for your reply. Ports are open, I have checked through telnet. In mirror DB log I can see this error 

    "Database Mirroring Login attempt failed with error. Connection handshake failed'

    Two points

    1) If I can connect to both servers in SQL Management Studio (In mirror to production and in Production to mirror) is there a possibility of still not them finding each other? Both the servers are in different domains only accessible through internet.

    2) I should state that I did not create the certificates and was using 'sa' account on both the servers

    Thanks.

    Kamran

    Friday, December 2, 2011 1:44 AM
  • Do the domains have a trust configured between them? If not I would suggest you try to setup the certificates. I am not 100% but I don't believe that using SQL authentication works for this.

    How to: Allow Database Mirroring to Use Certificates for Outbound Connections (Transact-SQL)

    http://technet.microsoft.com/en-us/library/ms186384.aspx

    How to: Allow Database Mirroring to Use Certificates for Inbound Connections (Transact-SQL)

    http://technet.microsoft.com/en-us/library/ms187671.aspx

    http://www.mssqltips.com/sqlservertip/1705/implementing-database-mirroring-in-sql-server-2005-across-domains/

     


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog or Twitter.
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Kamran Shafi Friday, December 2, 2011 7:25 PM
    Friday, December 2, 2011 2:15 AM
  • Can you please also post the error detail message that includes the reason and state code when the handshake fails?

    For the sake of getting an idea where the problem is I would also try to rule out a permission issue by granting everyone connection rights to the endpoint. ("GRANT CONNECT ON ENDPOINT::<endpointname> TO Public" run on both sides)

    Friday, December 2, 2011 7:12 AM
  • Thank you for your response.

    I was able to set up mirroring for two of the databases using certificate authorization

    Third DB which I applied encryption on with certificate and then later turned the encryption off and dropped the certificate, I am not being able to restore the backup on mirror server.

    Error 

    "Msg 33111, Level 16, State 3, Line 1

    Cannot find server certificate with thumbprint '0x0C115871BE87D00FFC0BAD2B1CE8E6F9F4D13F70'.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally."

     

    I have double checked that there is no encryption on the DB and no certificate attached.

    We are using SQLServer 2008 R2 Enterprise 10.50.1617.0

     

    Any help would be appreciated.

     

    Thanks.

    Kamran

    Friday, December 2, 2011 7:30 PM
  • I was able to figure it out.

    Had to recreate the certificate on mirror Db to restore the backup.

     

    Thanks every one.

     

    Friday, December 2, 2011 9:11 PM