none
Database mirroring configuration failed in sql 2016

    Question

  • Hello everyone

    Just started working on sql 2016,I wanted to do database mirroring between two instance of sql 2016 on same machine
    I took backup of my database and then restored it on another server with norecovery .....it was fine till here...

    When I wanted to configure database mirroring ,in final step it gave me  error message, saying"Db2 can not be opened as it is in middle restore."????

    Same steps followed in sql 2012 and there was no issue ,only on sql 2016 instance it gave me error and I am not able to configure database mirroring ...................

    Thanks in advance


    srm_2010

    Friday, April 12, 2019 9:25 AM

Answers

  • Hi srm_2010,

    From your description, did you use the SSMS to configure database mirroring? I test it on my environment. Please try to follow below steps to solve the issue.


    a.On the mirror server instance, you can run below T-SQL to set the primary instance as the partner. On my test environment, L1 is the primary server, L2 is the mirror server.

    USE master;  
    GO  
    ALTER DATABASE mirror
        SET PARTNER =   
        'TCP://L1.contoso.com:5022'  
    GO  

    b.On the primary server instance, you can run below T-SQL to set the mirror instance as the partner.

    USE master;  
    GO  
    ALTER DATABASE mirror   
        SET PARTNER =   
        'TCP://L2.contoso.com:5022'  
    GO

    Please refer to Setting Up Database Mirroring Using Windows Authentication (Transact-SQL).

    Hope it helps you.

    Best regards,
    Cathy Ji

      

    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, April 15, 2019 8:54 AM

All replies

  • Hi,

    The process for setting up database mirroring is the same on 2016, however 2016 is notoriously buggy!

    I would suggest the following steps in the below order and try the mirror again after each:

    1. Update Management Studio on the machines you're configuring DB mirroring from (

    https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017#ssms-1791-is-the-current-general-availability-ga-version-of-ssms)

    2. Step 1 may well resolve the issue.  f not, patch both SQL servers to the latest SP and CU (SP 2 is https://www.microsoft.com/en-us/download/details.aspx?id=56836 and the CU for that is https://support.microsoft.com/en-us/help/4488536/cumulative-update-6-for-sql-server-2016-sp2

    3. Failing the above 2 steps, if you set this up with TSQL instead then that will do the trick (https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/example-setting-up-database-mirroring-using-windows-authentication-transact-sql?view=sql-server-2017)

    Do try steps 1 and 2 first though (step 1 may well be enough).

    Let me know how you get on,

    Thanks,

    Matt

    Sunday, April 14, 2019 9:48 AM
  • Hi srm_2010,

    From your description, did you use the SSMS to configure database mirroring? I test it on my environment. Please try to follow below steps to solve the issue.


    a.On the mirror server instance, you can run below T-SQL to set the primary instance as the partner. On my test environment, L1 is the primary server, L2 is the mirror server.

    USE master;  
    GO  
    ALTER DATABASE mirror
        SET PARTNER =   
        'TCP://L1.contoso.com:5022'  
    GO  

    b.On the primary server instance, you can run below T-SQL to set the mirror instance as the partner.

    USE master;  
    GO  
    ALTER DATABASE mirror   
        SET PARTNER =   
        'TCP://L2.contoso.com:5022'  
    GO

    Please refer to Setting Up Database Mirroring Using Windows Authentication (Transact-SQL).

    Hope it helps you.

    Best regards,
    Cathy Ji

      

    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, April 15, 2019 8:54 AM
  • Hello There, I tried DB mirroring with updated SSMS 17.9.1 version but still error
    then tried step 2 to apply Sp2 for sql 2016 but sp2 did not run error was "There are no instance or shared features to apply the updates"

    Tried to do with TSql code to set partners but on primary error is coming as "transport is disabled  for db mirroring"

    Nothing seems to work..............???


    srm_2010

    Wednesday, April 17, 2019 11:13 AM
  • Also when I tried step 2 to apply CU and Sp2 it gave me error again that version of Sql 2016 which
    in my case is 13.0.4001.0 is not matching ,latest version is needed to update.....what next to do?

    srm_2010

    Wednesday, April 17, 2019 11:44 AM
  • Could not do with Tsql code...

    srm_2010

    Wednesday, April 17, 2019 11:45 AM
  • Hi srm_2010,

    Could you please check the endpoint that you configured and check if the port is occupied. You can use netstat –an cmd to get the list of  port which are in use on the server.

    Best regards,
    Cathy Ji

    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

    Thursday, April 18, 2019 10:07 AM
  • Hi,

    13.0.4001.0 is SP1 and so you need to make sure that you install SP 2 before you run the CU, then it should apply.

    Thanks,

    Matt

    Friday, April 19, 2019 1:04 PM
  • ok...let me try again

    thnx


    srm_2010

    Monday, April 22, 2019 5:36 AM
  • Hi there,This time I retried installing SSMS sp2 and there were no problem and then applied cu also
    without any issue...Restarted the machine later..

    But Again when I configured DB mirroring on my machine which has two instances,it gave error of
    "That DB1 is in restoring stage and csn not be opened"......so after doing ok to dialog box

    I tried following:

    1step:On mirror server typed the code=

    USE master;  
    GO  
    ALTER DATABASE DB1
        SET PARTNER =   
        'TCP://win2012.com:5022' ----5022 for Principal server  there is no error
    GO

    2step on Principal server the code=

    USE master;  
    GO  
    ALTER DATABASE DB1
        SET PARTNER =   
        'TCP://win2012.com:5023' ----5023 for mirror server ,here it gives error

    GO

    Error is :-Msg 1418, Level 16, State 1, Line 152
    The server network address "TCP://win2012.com:5023" 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.

    Not getting it....any further help will be appreciated..

    Thanx in advance


    srm_2010

    Monday, April 22, 2019 8:20 AM
  • Hi Finally I could do it ..Did all the steps from start again and I could do it without any issue...It was probably
    Authentication issue..

    srm_2010

    • Marked as answer by srm_2010 Tuesday, April 23, 2019 9:19 AM
    • Unmarked as answer by srm_2010 Tuesday, April 23, 2019 9:19 AM
    Monday, April 22, 2019 10:17 AM
  • Hi Finally I could do it ..Did all the steps from start again and I could do it without any issue...It was probably
    Authentication issue..

    srm_2010

    Hi srm_2010,
    If you have resolved your issue, please close the thread by marking the useful reply as answer.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.
    Best regards,
    Cathy Ji

    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, April 22, 2019 10:24 AM