locked
SQL Server 2012 Database Mirroring RRS feed

  • Question

  • Testing DB Mirroring on SQL Server 2012.

    ServerA\inst2012 principal. ServerB\inst2012 is mirror. ServerC default instance is witness.

    From SSMS, when I configure security ... from serverA\inst2012\mirrortest db to serverB\inst2012\mirrortest db, I did not get an error. However, when I click start mirroring , I get the following error:

    The server network address "TCP://serverB.xyz.com:5022" can not be reached or does not exists.

    Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418).

    Tested / checked:

    1. Both of the default instances for ServerA and ServerB is SQL Server 2008 and DB Mirroring tested on there. The DB Mirroring test for SQL2008 worked and had been removed.

    2. Connection from ServerA to ServerB\inst2012 through SSMS connected without any issues.

    3. Same domain account are used for SQL Server service and SQL Service Agent on both ServerA and ServerB

    4. Both state_desc column on sys.database_mirroring_endpoints is 'STARTED'

    Help on the error?

    Thanks.



    Monday, March 7, 2016 8:43 PM

Answers

  • How else can I drop the endpoints from UI / TSQL?

    DROP ENDPOINT is the command. You can also delete them from UI as shown below.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by freshie2012 Monday, March 14, 2016 7:10 PM
    Thursday, March 10, 2016 2:56 PM

All replies

  • Hi,

      Long shot, but did you check your firewall setting? Is the port open for 5022?


    Tuesday, March 8, 2016 1:52 AM
  • Hi freshie2012,

    The above error 1418  can be caused by several reasons, such as incorrect endpoints configuration, firewall blocking or your account doesn’t have sufficient permissions. For further analysis, please help to collect more detailed information from error log files of principal server and mirror server. By default, SQL Server error log file locates in C:\Program Files\Microsoft SQL Server\MSSQL11.xxxx\MSSQL\Log.

    In addition, please note that for a mirroring session to be established, the partners and the witness must be running on the same version of SQL Server.


    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Tuesday, March 8, 2016 2:38 AM
  • two instance can't listen on same port, even for database mirroring. Try using different port. Please share ERRORLOG.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, March 8, 2016 2:41 AM
  • This is a test environment, therefore, all levels of firewall on both servers are disabled

    Thanks for viewing and your suggestions

    Tuesday, March 8, 2016 8:20 PM
  • Partners and witness are running on SQL Server 2012. The only difference is partners are in named instance and witness is on default instance.

    The recent lines in the logs (mirror) just show the following lines:

    Database mirroring has been terminated for database 'mirrortest'. This is an informational message only. No user action is required.
    
    Error: 1443, Severity: 16, State: 2
    
    Database mirroring has been enabled on this instance of SQL Server.
    

    There is a line that says the following in the principal:

    The Database Mirroring endpoint cannot listen on port 5022 because it is in use by another process.

    Thanks for viewing and your suggestions



    Tuesday, March 8, 2016 8:28 PM
  • I have remove Mirroring for the SQL Server 2008 instance. Will there still be a conflict?

    I'm setting up mirroring from SSMS which doesn't allow me to change the port number. Any pointers what is the tsql command to set up mirroring?

    Thanks for viewing and your suggestions

    Edit 1: Can 1 witness be used to monitor multiple sets of partners?

    Edit 2:

    Followed https://msdn.microsoft.com/en-us/library/ms190430.aspx and tsql entered the following:

    ALTER DATABASE mirrortest SET PARTNER='TCP://serverB.xyz.com:7022'
    Still the same error




    Tuesday, March 8, 2016 8:32 PM
  • So real error is

    The Database Mirroring endpoint cannot listen on port 5022 because it is in use by another process

    Drop the endpoints from the UI / TSQL and create using T-SQL.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, March 9, 2016 12:44 AM
  • I have dropped all DB Mirroring. Nothing is partnering now. SQL2008 DBs have even been removed. When I query sys.database_mirroring_endpoints, I can still see the information. But, I cannot delete / remove that entry.

    How else can I drop the endpoints from UI / TSQL?

    Thursday, March 10, 2016 2:41 PM
  • How else can I drop the endpoints from UI / TSQL?

    DROP ENDPOINT is the command. You can also delete them from UI as shown below.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by freshie2012 Monday, March 14, 2016 7:10 PM
    Thursday, March 10, 2016 2:56 PM
  • Thank you very much. That fixed it.

    Monday, March 14, 2016 7:11 PM