none
SQL 2016 : Unable to start mirroring RRS feed

  • Question

  • Hello,

    I have a SQL 2016 (13.0.1601.5) with 3 instance of SQL server running on it. 

    Instances:

    1. DBMirror (to hold the principal DB)

    2. ALWAYSON2016 (to hold the mirror DB)

    3. SQLWITNESS (to be the witness server)

    I have attached security configuration of the 3 instances. The configuration completes successfully. 

    All the 3 instances of SQL server and SQL agent is running under the same domain administrator account. The DB has been restored(no recovery) with DB and the transaction log

    Once i start mirroring, i get the error: Database <name> cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

    I have attached the screenshot


    Monday, August 29, 2016 4:50 PM

Answers

  • Also 2016 has quite bugs in Native tools like ssms,try with Tsql & still if you have the issues let me know.

    Regards, S_NO "_"

    Tuesday, August 30, 2016 4:21 AM
  • Also 2016 has quite bugs in Native tools like ssms,try with Tsql & still if you have the issues let me know.

    Regards, S_NO "_"

    Hi Praveen Shastri,

    I agree with S_NO on this, I had same issue with SSMS 2016 before and fixed by using TSQL to establish database mirroring session. Also, if you have pervious SSMS installed on your computer I would suggest you give it a try as I’ve successfully established database mirroring between SQL 2016 servers via SSMS 2014.

     If you have any other questions, please let me know.

    Regards,
    Lin

    Tuesday, August 30, 2016 8:33 AM
    Moderator

All replies

  • Hi Praveen,

    Can you pl cross check, if you are starting before the restore ends.

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Monday, August 29, 2016 5:02 PM
  • Some time you may face issue like below-

    Setting up Database mirroring in SQL Server 2008 using T-SQL when the database is encrypted using Transparent Data Encryption.
    https://blogs.msdn.microsoft.com/sqlserverfaq/2009/03/31/setting-up-database-mirroring-in-sql-server-2008-using-t-sql-when-the-database-is-encrypted-using-transparent-data-encryption/

    http://annevamsikrishna.blogspot.in/2013/09/database-mirroring-on-tde-database.html

    use the TSQL)script to configure(GUI always you cant trust(
    -----------------------------------------------------------
    Prepare a Mirror Database for Mirroring (SQL Server)
    https://msdn.microsoft.com/en-in/library/ms189053.aspx

    use the below command to see progresses

    SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 
    FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
    WHERE r.command in ('RESTORE DATABASE')

    if you dont see the progresses then -

    RESTORE DATABASE <DB name_here> WITH RECOVERY
    GO
    
    --- execute in the mirror server


    Drop it in mirror and create with tsql.

    Also kindly ensure you have followed the pre-requisites

     

    Prerequisites, Restrictions, and Recommendations for Database Mirroring
    https://msdn.microsoft.com/en-us/library/ms366349.aspx

    >>Ensure on the primary side for the database if any log backup is scheduled then please hold for an while before you configure DBM.


    Regards, S_NO "_"

    Tuesday, August 30, 2016 1:05 AM
  • Also 2016 has quite bugs in Native tools like ssms,try with Tsql & still if you have the issues let me know.

    Regards, S_NO "_"

    Tuesday, August 30, 2016 4:21 AM
  • Hello Satish,

    From what i have seen till now with DB mirroring, we need to restore the secondary DB with no-recovery and this usually keeps the DB in restoring state. I have left the secondary DB for a day and then tried the mirroring, yet it failed.

    Tuesday, August 30, 2016 6:10 AM
  • Also 2016 has quite bugs in Native tools like ssms,try with Tsql & still if you have the issues let me know.

    Regards, S_NO "_"

    Hi Praveen Shastri,

    I agree with S_NO on this, I had same issue with SSMS 2016 before and fixed by using TSQL to establish database mirroring session. Also, if you have pervious SSMS installed on your computer I would suggest you give it a try as I’ve successfully established database mirroring between SQL 2016 servers via SSMS 2014.

     If you have any other questions, please let me know.

    Regards,
    Lin

    Tuesday, August 30, 2016 8:33 AM
    Moderator
  • I agree the same that I face the same issue with SSMS 2016, You need to try with previous Version of SSMS ( 2012/2014)  OR TSQL.

    Thanks

    Shabeer


    • Edited by ShabSQL Tuesday, August 30, 2016 9:30 AM
    Tuesday, August 30, 2016 9:30 AM
  • Thanks Lin,

    Thanks for your inputs..I did install SSMS 2014 and it worked...Finally!!

    Thursday, September 1, 2016 9:35 AM
  • Thanks Shabeer, it did work with SSMS 2014.
    Thursday, September 1, 2016 9:36 AM
  • Experienced the same issue using SQL Server SSMS 2016 and able to successfully setup mirroring with SQL Server 2014 SSMS for the SQL Server 2016 instance.  Issue seems to be due to query command generated when using SQL Server 2016 SSMS.

    Thanks for the tips all!

    Phillip Cox

    Tuesday, October 25, 2016 1:55 PM
  • Holy f*cking sh*t. This is still not fixed, even with latest SMSS 2017.4. I just spend 3.5 hours trying to fix a broken mirror. On 2016 SP1. Updated to newest CU for the SQL Servers, updated SMSS from 2017.3 to latest 2017.4. Nothing... Tried from a server with SMSS 2012 installed and it worked instantly. This is beyond ridiculous.

    I kept getting the error message "... database in the middle of a restore ... error 927"

    Thursday, January 25, 2018 10:38 PM
  •  This is still not fixed, even with latest SMSS 2017.4. I just spend 3.5 hours trying to fix a broken mirror. On 2016 SP1. Updated to newest CU for the SQL Servers, updated SMSS from 2017.3 to latest 2017.4. Nothing... Tried from a server with SMSS 2012 installed and it worked instantly. This is beyond ridiculous.

    I kept getting the error message "... database in the middle of a restore ... error 927"

    Ahh... So glad to see this. I have spent a full day on this getting exactly what you are getting. So glad to finally see I'm not crazy, its SSMS not me! Same issue with SSMS 17.4 and both SQL 2017-CU3 Dev.
    • Proposed as answer by ChecoFierro Friday, April 20, 2018 4:27 PM
    Wednesday, February 14, 2018 4:35 AM
  • You know the problem is the SQL Management Studio  on SQL2016,   download and install a SQL Management Studio SQL2012  and you can built a Mirror without problem
    Friday, April 20, 2018 4:29 PM
  • Ahh... So glad to see this. I have spent a full day on this getting exactly what you are getting. So glad to finally see I'm not crazy, its SSMS not me! Same issue with SSMS 17.4 and both SQL 2017-CU3 Dev.

    Same problem continues in version SSMS 17.7
    Thursday, May 24, 2018 8:10 AM
  • Ahh... So glad to see this. I have spent a full day on this getting exactly what you are getting. So glad to finally see I'm not crazy, its SSMS not me! Same issue with SSMS 17.4 and both SQL 2017-CU3 Dev.

    Same problem continues in version SSMS 17.7

    The same problem continues as of 6/27/2018. What is M$ trying to say by NOT fixing this bug for years now?

    Wednesday, June 27, 2018 9:47 PM
  • issue is still present in SSMS 17.9.1....
    Monday, January 7, 2019 12:53 PM
  • Hi Buddy,

    please use below command after configure mirroring.

    there is bug in SSMS.

    On mirror server

    ALTER DATABASE <mirror_db> SET PARTNER = 'TCP://<primary_server_hostname>:5022'

    On primary server

    ALTER DATABASE <mirror_db>
       SET PARTNER = 'TCP://<mirror_server_hostname>:5022'

    Sunday, February 24, 2019 2:04 PM