locked
I CANT CONFIG MIRRORING RRS feed

  • Question

  • My scenary is

    - 2 servers, with same Windows version -> Windows 10 Server 2016 Standard

    - Firewall fully disabled

    - SQL Server Standard Edition Stand Alone installed in both servers only selecting Database Engine Services and SQL Server Replication in installing Wizard

    I want to set a mirroring but without Witness. I read the doc and My actions were:

    1.- I create a DB in master server from a backup with NORECOVERY and then apply T-LOG restore too with RECOVERY, because this server will be master.

    2.- In my slave server i create DB (from the same backup than MASTER) with NORECOVERY and then apply TLOG restore too (from same TLOG backup than MASTER) but WITH NO RECOVERY

    3.- Same login is created in MASTER AND SLAVE in same domain

    4.- Create ENDPOINT like this:

    -MASTER (Role Partner)

    CREATE ENDPOINT [Mirroring]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

    -SLAVE (Role ALL)

    CREATE ENDPOINT [Mirroring]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

    6.- Give grant to connect on endpoint to my login

    GRANT CONNECT ON ENDPOINT::Mirroring TO [Domain\login]

    5.- Try to start mirroring like this:

    -SLAVE

    USE MASTER;
    GO
    ALTER DATABASE [dbName] SET PARTNER='TCP://serverMASTER:5022'
    GO

    -MASTER

    USE MASTER;
    GO
    ALTER DATABASE [dbName] SET PARTNER='TCP://serverSLAVE:5022'
    GO

    At this point allways fails command run on Master Server. I test to change the roles, i mean, the server that i used like Master, use like SLAVE and in the other way, server used like SLAVE, use like MASTER, this is: in server working like MASTER i create the BD WITH RECOVERY, so it keeps opened and server working like SLAVE i create DB WITH NORECOVERY so it keeps in "restoring" state. But the command (ALTER DATABASE ...SET PARTNER) allways fails in server i use like MASTER, this is in server where DB is opened.

    Where is my fault??

    Thanks in advance.

    Friday, October 18, 2019 9:36 AM

All replies

  • Could you post the error message?
    Friday, October 18, 2019 2:55 PM
  • Hi J_R37,

    Could you please share us the error message from SQL server error log ? Please try to change the endpoint role to All in your MASTER server.  And please check you’re the full name of your MASTER and SLAVE server when creating partner. Please check your steps again follow the steps from MS official document.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, October 21, 2019 7:27 AM
  • Hi, error message got was:

    "The server network address "TCP://<ServerName>:5022" 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."

    Monday, October 21, 2019 1:53 PM
  • Hi Cathy Ji,

    error message was: "The server network address "TCP://<ServerName>:5022" 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."

    I have tried with ALL role, but same result.

    Logs shows this error:

    "Database Mirroring login attempt by user '<Domain>\<ServerName>$.' failed with error: 'Connection handshake failed. The login '<Domain>\<ServerName>' does not have CONNECT permission on the endpoint. State 84."

    But I dont know why i need create a login with same name than server?? Where is "reading" that from??

    I try to start mirroring connected with my user and with Windows Authentication. My user/login have permission to CONNECT.

    Should i have to create a login with same name than ServerName??

    Regards

    Monday, October 21, 2019 2:13 PM
  • Hi J_R37,

    >> error message was: "The server network address "TCP://<ServerName>:5022" 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."

    For this error message, please follow below steps from this blog to troubleshoot your issue. 

    >> But I dont know why i need create a login with same name than server?? Where is "reading" that from??

    Please check what SQL server service account that the both SQL server instances are running under? Did the account has the connecting permission to the Database Mirroring endpoint of the other instance. Please refer to Set Up Login Accounts - Database Mirroring to get more explanation.

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, October 22, 2019 9:55 AM