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

Všechny reakce

• 21. března 2012 22:01

   

   
  

  0

  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!

  • Reagovat
  • Citovat

   
• 22. března 2012 1:58

   

   
  

  0

  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

  • Reagovat
  • Citovat

   
• 22. března 2012 3:11

   

   
  

  0

  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!

  • Reagovat
  • Citovat

   
• 22. března 2012 15:18

   

   
  

  0

  check this link

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

  • Reagovat
  • Citovat

   
• 22. března 2012 15:56

  Přispěvatel

   

   
  

  0

  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 LvModerator 30. března 2012 9:38
  •  
  • Reagovat
  • Citovat

   
• 27. března 2012 11:15

   

   
  

  0

  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

  • Reagovat
  • Citovat

   
• 27. března 2012 12:58

   

   
  

  0

  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.

  • Reagovat
  • Citovat

   
• 4. května 2012 19:22

   

   
  

  0
  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

  • Reagovat
  • Citovat