none
Trouble shooting DB mirroring shouldn't be this difficult...

Odpovědi

  • Hello,

    Your issue seems to be that the port specified in the mirroring partner was 1433 which is probably incorrect as that's the default tcp endpoint. You'll need to switch this to the port created for your mirroring payload:

    I didn't have a chance to check this, it's from memory so it may not work but you'll get the idea. You'll also want to check the roles specified when creating the endpoint (for example you didn't give your partner the role of witness).

    SELECT e.[name], e.endpoint_id, e.[state], is_encryption_enabled, connection_auth_desc, port FROM
    	sys.database_mirroring_endpoints e 
    	INNER JOIN sys.tcp_endpoints t
    		ON t.endpoint_id = e.endpoint_id

    -Sean

    • Označen jako odpověď Stephanie Lv 30. března 2012 9:38
    22. března 2012 15:56
    Přispěvatel

Všechny reakce

  • Hi,

    How did you configure mirroring? Did you use TSQL DDL statements? If so can you post the code? Did you configure the endpoints?

    http://msdn.microsoft.com/en-us/library/ms189127.aspx

    This code will check your endpoints. Run this and post the results.

    SELECT perm.class_desc, perm.permission_name, endpoint_name = e.name, perm.state_desc,
     grantor = prin1.name, grantee = prin2.name
    FROM master.sys.server_permissions perm
     INNER JOIN master.sys.server_principals prin1 ON perm.grantor_principal_id = prin1.principal_id
     INNER JOIN master.sys.server_principals prin2 ON perm.grantee_principal_id = prin2.principal_id
     LEFT JOIN master.sys.endpoints e ON perm.major_id = e.endpoint_id
    WHERE  perm.class_desc = 'ENDPOINT'


    Sean Massey | Consultant, iUNITE

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

    21. března 2012 22:01
  • I ran your SQL and here are the results:

    Primary Server:
    ===============
    ENDPOINT CONNECT TSQL Local Machine GRANT sa public
    ENDPOINT CONNECT TSQL Named Pipes GRANT sa public
    ENDPOINT CONNECT TSQL Default TCP GRANT sa public
    ENDPOINT CONNECT TSQL Default VIA GRANT sa public
    ENDPOINT CONNECT Mirroring GRANT CCC\jsanthanam CCC\servicesql

    Mirror Server:
    ==============
    I can't run the sQL on the mirrored server since the DB is in recovery mode and does not show up in SQL Management Studio. Let me know.


    Jagannathan Santhanam

    22. března 2012 1:58
  • The endpoints can be checked on the mirror instance as well with the code.

    You still should be able to see the mirror copy of the database even though it will not serve connections you still should be able to see it.


    Sean Massey | Consultant, iUNITE

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

    22. března 2012 3:11
  • Hello,

    Your issue seems to be that the port specified in the mirroring partner was 1433 which is probably incorrect as that's the default tcp endpoint. You'll need to switch this to the port created for your mirroring payload:

    I didn't have a chance to check this, it's from memory so it may not work but you'll get the idea. You'll also want to check the roles specified when creating the endpoint (for example you didn't give your partner the role of witness).

    SELECT e.[name], e.endpoint_id, e.[state], is_encryption_enabled, connection_auth_desc, port FROM
    	sys.database_mirroring_endpoints e 
    	INNER JOIN sys.tcp_endpoints t
    		ON t.endpoint_id = e.endpoint_id

    -Sean

    • Označen jako odpověď Stephanie Lv 30. března 2012 9:38
    22. března 2012 15:56
    Přispěvatel
  • Hi,

    This is because you're using incorrect port in mirroring configuration. Default tcp port for mirroring is 5022.

    The server network address "TCP://<servername>:1433" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)


    Regards, Vishal Srivastava

    27. března 2012 11:15
  • While configuring the database mirroring session, you need to provide the Port number, where your database Mirroring endpoint is listening to. Modify the script as follows, my database mirroring endpoint is listening on Port 7048:

    USE [master];  
    GO  
    ALTER DATABASE [TestDB]  
    SET PARTNER = 'TCP://ServerA:7048;  
    GO 

    Recently, I have created a SQL CMD script to setup database mirroring in a Lab environment, here is a link for the same: http://sudeeptaganguly.wordpress.com/2012/03/06/database-mirroring-with-sqlcmd/

    Hope, this may help :)


    SKG: Please Marked as Answered, if it resolves your issue.

    27. března 2012 12:58
  • All - Thanks for all of your replies. The problem seems to be the port#. We are asked by RIM (black berry) to use the default port# 1433. Once we changed the port # to 5022, it started working. 

    Jagannathan Santhanam

    4. května 2012 19:22