Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
DB Mirroring failing to configure

Unanswered DB Mirroring failing to configure

  • Thursday, December 15, 2011 1:48 AM
     
     
    Sign In to Vote
    0

    We have the following database mirroring configuration

    SERVER_A/PROD_GUI (Port1433)  <--Port:1500-DBMirroring-Port:1500--> SERVER_B/DR_GUI Port(1533)
    SERVER_A/PROD_WEB (Port1436)  <--Port:1550-DBMirroring-Port:1550--> SERVER_B/DR_WEB Port(1536)

    I can telnet from either machine to the other on any of these ports so know the connections work.
    PROD_GUI to DR_GUI is working with no issue, the databases are synchronised and I have the option to fail over.

    We have a problem on the _WEB connections.  I have removed all the mirroring and endpoints to double check my configuration

    Steps Taken
    CREATE ENDPOINT [DBMirroring_DR_WEB]
     AUTHORIZATION [mySysAdmin]
     STATE=STARTED
     AS TCP (LISTENER_PORT = 1550, LISTENER_IP = ALL)
     FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
    , ENCRYPTION = REQUIRED ALGORITHM RC4)
    GO

    GRANT CONNECT ON ENDPOINT::[DBMirroring_DR_WEB] TO [DOMAIN\SQL_PROD_WEB_SQLService]

    This creates an endpoint that I can see in sys.endpoints
    DBMirroring_DR_WEB 65547 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0

    I then try and configure the endpoint at the other end on PROD_WEB

    CREATE ENDPOINT [DBMirroring_PROD_WEB]
     AUTHORIZATION [mySysAdmin]
     STATE=STARTED
     AS TCP (LISTENER_PORT = 1550, LISTENER_IP = ALL)
     FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
    , ENCRYPTION = REQUIRED ALGORITHM RC4)
    GO

    As soon as this command completes I start to get errors in the event logs on SERVERB for the MSSQL$DR_GUI service!!

    Database Mirroring login attempt by user 'DOMAIN\SQL_PROD_WEB_SQLService.' failed with error: 'Connection handshake failed. The login 'DOMAIN\SQL_PROD_WEB_SQLService' does not have CONNECT permission on the endpoint. State 84.'.

    If I try and configure any other components on DB Mirroring or use the wizard mirroring fails to connect and synchronise

    I can not work out why this error is occurring. 
     1] None of my configuration for this server lists DR_GUI, they are on separate ports.
     2] The errors begin to occur as soon as I create the endpoint...I thought an endpoint "Listened" for connections, how can a listening endpoint send connections out to another instance.

    Can anyone shed any light on this.

    Thanks

    Smitty

    • Edited by graz.smith Thursday, December 15, 2011 1:53 AM Formatting
    • Edited by graz.smith Sunday, December 18, 2011 10:32 PM Corrected Port Numbers
    •  
     

All Replies

  • Thursday, December 15, 2011 4:16 AM
     
     
    Sign In to Vote
    0

    Hi,

    Did you create login for both the service accounts in the remote instance in the master database? Looks like you have two instances using different service accounts. I believe you need to manually create the login in master for both and also do the grant connect.

    http://msdn.microsoft.com/en-us/library/ms366346.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 15, 2011 4:27 AM
     
     
    Sign In to Vote
    0

    I have,

     

    I have actually added the service account to the MSSQL Service group on the respective machines, the issue isn't that they can not connect, the issue is it is trying to connect to the wrong server

     

    I should also add using the mirror wizard produces the same result.

     

    Both servers get an endpoint, both servers get the other service account as a login that is granted access to the endpoint.  But the PROD_WEB server tries to make it's connections to the DR_GUI server

    MCDBA, MCAD, MCITP, MCTS
    • Edited by graz.smith Thursday, December 15, 2011 5:01 AM
    •  
     
  • Thursday, December 15, 2011 10:12 AM
     
     
    Sign In to Vote
    0
    for DBMirroring_PROD_WEB have you granted the connection permissions on ENDPOINT?
    http://uk.linkedin.com/in/ramjaddu
     
  • Thursday, December 15, 2011 10:08 PM
     
     
    Sign In to Vote
    0

    Yes!

     

    See above.  Permissions are there and applied to the right endpoints.

     

    The issue is

     

    As soon as I configure the endpoint at the other end on PROD_WEB using the following script I start to get errors in the event logs on SERVERB for the MSSQL$DR_GUI service!!  This is the the wrong server.  At this point i.e. creating the EndPoint it should not be making a connection to ANY server

    CREATE ENDPOINT [DBMirroring_PROD_WEB]
    AUTHORIZATION [mySysAdmin]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 4550, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
    , ENCRYPTION = REQUIRED ALGORITHM RC4)
    GO

     

    The fact I am getting a permission error is correct, the service account for PROD_WEB does not have permission to DR_GUI and NEVER should. 

     

    My problem is it is attempting the connection in the first place, and it attempts it as soon as the endpoint is created.

    MCDBA, MCAD, MCITP, MCTS
     
  • Friday, December 16, 2011 1:55 AM
     
     
    Sign In to Vote
    0

    I'm a little confused here. First you tell us your mirroring configuration which includes DR_GUI and then you insist that your configuration doesn't include DR_GUI. Please clarify.

    Mirroring requires a 2 way conversation between partners. If DR_GUI is your witness (for example) the other partners still need rights to access its endpoint.

    Also, telnet does not verify that the mirroring endpoint is listening on the assigned port. It verifies that something is listening on the port. It could be just about anything. You have to use the net stat command to determine what is listening to the assigned port.

    My blog: SQL Soldier
    Twitter: @SQLSoldier
    Microsoft Certified Master: SQL Server 2008
    My book: Pro SQL Server 2008 Mirroring
     
  • Friday, December 16, 2011 3:04 AM
     
     
    Sign In to Vote
    0
    I read through it again and see what you meant about DR_GUI. Can you give us the output of sys.database_mirroring and sys.database_mirroring_endpoints on all 4 servers?
    My blog: SQL Soldier
    Twitter: @SQLSoldier
    Microsoft Certified Master: SQL Server 2008
    My book: Pro SQL Server 2008 Mirroring
     
  • Friday, December 16, 2011 3:31 AM
     
     
    Sign In to Vote
    0
    Output from select* from sys.database_mirroring_endpoints
    Server Name name endpoint_id principal_id protocol protocol_desc type type_desc state state_desc is_admin_endpoint role role_desc is_encryption_enabled connection_auth connection_auth_desc certificate_id encryption_algorithm encryption_algorithm_desc
    SERVERB\DR_WEB DBMirroring_DR_WEB 65547 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 1 PARTNER 1 3 NEGOTIATE 0 1 RC4
    SERVERB\DR_GUI DBMirroring_DR_GUI 65540 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 1 PARTNER 1 3 NEGOTIATE 0 1 RC4
    SERVERA\PROD_GUI DBMirroring_PROD_GUI 65537 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 1 PARTNER 1 3 NEGOTIATE 0 1 RC4
    SERVERA\PROD_WEB DBMirroring_PROD_WEB 65537 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 1 PARTNER 1 3 NEGOTIATE 0 1 RC4
    select * from sys.database_mirroring
    Server Name database_id mirroring_guid mirroring_state mirroring_state_desc mirroring_role mirroring_role_desc mirroring_role_sequence mirroring_safety_level mirroring_safety_level_desc mirroring_safety_sequence mirroring_partner_name mirroring_partner_instance mirroring_witness_name mirroring_witness_state mirroring_witness_state_desc mirroring_failover_lsn mirroring_connection_timeout mirroring_redo_queue mirroring_redo_queue_type mirroring_end_of_log_lsn mirroring_replication_lsn
    SERVERB\DR_WEB 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_WEB 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_WEB 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_WEB 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_WEB 5 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_WEB 6 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_WEB 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_WEB 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_WEB 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_WEB 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_WEB 5 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_WEB 6 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_GUI 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_GUI 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_GUI 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_GUI 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_GUI 5 38B0F805-5453-438D-91A7-92E70AFDCB45 4 SYNCHRONIZED 2 MIRROR 3 2 FULL 1 TCP://SERVERA:1500 SERVERA\PROD_GUI   0 UNKNOWN 105000000127900001 10 NULL UNLIMITED 105000000127900001 105000000127900001
    SERVERB\DR_GUI 6 53C93CBC-632F-409D-BD72-87AB8E6BE4A9 4 SYNCHRONIZED 2 MIRROR 3 2 FULL 1 TCP://SERVERA:1500 SERVERA\PROD_GUI   0 UNKNOWN 79000000258300001 10 NULL UNLIMITED 79000000258300001 79000000258300001
    SERVERB\DR_GUI 7 17896600-EFA0-4B86-AD73-45DC970A614D 4 SYNCHRONIZED 2 MIRROR 3 2 FULL 1 TCP://SERVERA:1500 SERVERA\PROD_GUI   0 UNKNOWN 20000001347200001 10 NULL UNLIMITED 20000001347200001 20000001347200001
    SERVERB\DR_GUI 8 02E4E30D-41B5-44FA-A4D6-367E445A23E4 4 SYNCHRONIZED 2 MIRROR 3 2 FULL 1 TCP://SERVERA:1500 SERVERA\PROD_GUI   0 UNKNOWN 20000007902800001 10 NULL UNLIMITED 20000007902800001 20000007902800001
    SERVERB\DR_GUI 9 D992047D-1F3D-415C-869B-DC49188D9AA2 4 SYNCHRONIZED 2 MIRROR 3 2 FULL 1 TCP://SERVERA:1500 SERVERA\PROD_GUI   0 UNKNOWN 20000005750800001 10 NULL UNLIMITED 20000005750800001 20000005750800001
    SERVERB\DR_GUI 10 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_GUI 11 EC946D5E-1C74-49C9-B247-E05B60493332 4 SYNCHRONIZED 2 MIRROR 3 2 FULL 1 TCP://SERVERA:1500 SERVERA\PROD_GUI   0 UNKNOWN 2961000005108800001 10 NULL UNLIMITED 2961000005108800001 2961000005108800001
    SERVERB\DR_GUI 12 F61544FB-C7BA-4B2E-8521-739C0C7B4391 4 SYNCHRONIZED 2 MIRROR 3 2 FULL 1 TCP://SERVERA:1500 SERVERA\PROD_GUI   0 UNKNOWN 327000018880900001 10 NULL UNLIMITED 327000018880900001 327000018880900001
    SERVERB\DR_GUI 13 6E75A55D-A56C-4129-984E-3648F1CFA278 4 SYNCHRONIZED 2 MIRROR 3 2 FULL 1 TCP://SERVERA:1500 SERVERA\PROD_GUI   0 UNKNOWN 833000006043800001 10 NULL UNLIMITED 833000006043800001 833000006043800001
    SERVERB\DR_GUI 14 A70FDDEA-C06C-4AF3-AC2F-7C39CE7B55F6 4 SYNCHRONIZED 2 MIRROR 1 2 FULL 1 TCP://SERVERA:1500 SERVERA\PROD_GUI   0 UNKNOWN 160000000109000001 10 NULL UNLIMITED 160000000109000001 160000000109000001
    SERVERB\DR_GUI 15 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_GUI 16 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_GUI 17 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_GUI 18 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERB\DR_GUI 19 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_GUI 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_GUI 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_GUI 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_GUI 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_GUI 5 38B0F805-5453-438D-91A7-92E70AFDCB45 4 SYNCHRONIZED 1 PRINCIPAL 3 2 FULL 1 TCP://SERVERB:1500 SERVERB\DR_GUI   0 UNKNOWN 105000000127900001 10 NULL UNLIMITED 105000000127900001 105000000127900001
    SERVERA\PROD_GUI 6 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    SERVERA\PROD_GUI 7 EC946D5E-1C74-49C9-B247-E05B60493332 4 SYNCHRONIZED 1 PRINCIPAL 3 2 FULL 1 TCP://SERVERB:1500 SERVERB\DR_GUI   0 UNKNOWN 2961000005108800001 10 NULL UNLIMITED 2961000005108800001 2961000005108800001
    SERVERA\PROD_GUI 8 F61544FB-C7BA-4B2E-8521-739C0C7B4391 4 SYNCHRONIZED 1 PRINCIPAL 3 2 FULL 1 TCP://SERVERB:1500 SERVERB\DR_GUI   0 UNKNOWN 327000018880900001 10 NULL UNLIMITED 327000018880900001 327000018880900001
    SERVERA\PROD_GUI 9 6E75A55D-A56C-4129-984E-3648F1CFA278 4 SYNCHRONIZED 1 PRINCIPAL 3 2 FULL 1 TCP://SERVERB:1500 SERVERB\DR_GUI   0 UNKNOWN 833000006043800001 10 NULL UNLIMITED 833000006043800001 833000006043800001
    SERVERA\PROD_GUI 10 53C93CBC-632F-409D-BD72-87AB8E6BE4A9 4 SYNCHRONIZED 1 PRINCIPAL 3 2 FULL 1 TCP://SERVERB:1500 SERVERB\DR_GUI   0 UNKNOWN 79000000258300001 10 NULL UNLIMITED 79000000258300001 79000000258300001
    SERVERA\PROD_GUI 11 17896600-EFA0-4B86-AD73-45DC970A614D 4 SYNCHRONIZED 1 PRINCIPAL 3 2 FULL 1 TCP://SERVERB:1500 SERVERB\DR_GUI   0 UNKNOWN 20000001347200001 10 NULL UNLIMITED 20000001347200001 20000001347200001
    SERVERA\PROD_GUI 12 02E4E30D-41B5-44FA-A4D6-367E445A23E4 4 SYNCHRONIZED 1 PRINCIPAL 3 2 FULL 1 TCP://SERVERB:1500 SERVERB\DR_GUI   0 UNKNOWN 20000007902800001 10 NULL UNLIMITED 20000007902800001 20000007902800001
    SERVERA\PROD_GUI 13 D992047D-1F3D-415C-869B-DC49188D9AA2 4 SYNCHRONIZED 1 PRINCIPAL 3 2 FULL 1 TCP://SERVERB:1500 SERVERB\DR_GUI   0 UNKNOWN 20000005750800001 10 NULL UNLIMITED 20000005750800001 20000005750800001
    SERVERA\PROD_GUI 14 A70FDDEA-C06C-4AF3-AC2F-7C39CE7B55F6 4 SYNCHRONIZED 1 PRINCIPAL 1 2 FULL 1 TCP://SERVERB:1500 SERVERB\DR_GUI   0 UNKNOWN 160000000109000001 10 NULL UNLIMITED 160000000109000001 160000000109000001
    MCDBA, MCAD, MCITP, MCTS
     
  • Friday, December 16, 2011 9:38 AM
     
     
    Sign In to Vote
    0

    I suspect endpoints are using some other services?

    When you are configuring the network for a database mirroring session, make sure the database mirroring endpoint of each server instance is used by only the database mirroring process. If another process is listening on the port assigned to a database mirroring endpoint, the database mirroring processes of the other server instances cannot connect to the endpoint.

    To display all the ports on which a Windows-based server is listening, use the netstat command-prompt utility. The syntax for netstat depends on the version of the Windows operating system.

    http://uk.linkedin.com/in/ramjaddu
     
  • Friday, December 16, 2011 9:40 PM
     
     
    Sign In to Vote
    0

    Can you verify the prot numbers? I see from the output that the ports being used on the GUI boxes are not the ports you reported in your post.

    My blog: SQL Soldier
    Twitter: @SQLSoldier
    Microsoft Certified Master: SQL Server 2008
    My book: Pro SQL Server 2008 Mirroring
     
  • Saturday, December 17, 2011 12:02 AM
     
     
    Sign In to Vote
    0
    GUI = 1500 for both endpoints WEB = 1550 for both endpoints
    MCDBA, MCAD, MCITP, MCTS
     
  • Tuesday, January 10, 2012 1:40 PM
     
     
    Sign In to Vote
    0
    Can you please post me an output of sys.dm_db_mirroring_connections from PROD_WEB? I'm curious to see if this is really mirroring traffic.
     
  • Saturday, March 31, 2012 12:38 PM
     
     
    Sign In to Vote
    0

    Hi.

    Configure to run SQL SERVER Database engine Service ( log on Property) of both server with same account ( domain \admin ) 

    Thanks

    Prashant Deshpande