0

Steps I followed:

  1. Create DB on primary.
  2. Took full backup and log backup.
  3. Restored full and log on secondary in NORECOVERY mode (now tb shows restoring satus on secondary I guess this is because NORECOVERY mode).
  4. Tried configuring mirror from primary.

I was able to configure it successfully with windows 2012 and SQL server 2012 using SSMS 17.6 but it's giving above error with Win2k16 and SQl server 2016

2
  • 1
    Did you try to configure mirroring with SSMS or via TSQL ?
    – liotims
    Apr 11, 2018 at 11:29
  • Yes, I tried it and it worked like a charm. Thanks a lot Apr 13, 2018 at 5:44

6 Answers 6

3

It seems there are bugs with SSMS (2016) to configure mirroring https://stackoverflow.com/questions/38008208/sql-server-2016-cant-start-mirror

Try to use TSQL or SSMS 2014

https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-establish-session-windows-authentication

1
  • ssms 2014 did the trick
    – tomeron11
    Mar 25, 2019 at 10:11
0

Keep in mind that database mirroring has been deprecated. If there are bugs with mirroring (which there are many) they are unsupported and will not be fixed by Microsoft at this time. Please consider using Availability Groups as it will function similarly to mirroring, but provide a more stable environment and greater functionality.

0

Yes it seems there is a bug in SSMS as configuring mirroring through GUI wizard gives above error. I tried it with TSQL commands and it works smoothly. Commands are

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'
0

I've faced the same issue, finally resolved it. My setup windows 2012 server & sql server 2016 sp3 on both instances After going to microsoft website I found the solution. check, it's very clear Link

SSMS or SQL server taking the hostname for mirror network address but it should be instance name of sql server

Hope it helps

0

Yes Dear Definitely this is bug in ssms you can try with t-sql command it will work fine. Same is working fine for me also.

1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    May 11 at 10:22
-1

Service accounts running SQL Server in SQL Config manager need to use the same domain user on both primary and secondary.

1
  • Could you potentially provide some more information? IE how would you set this up? Why does it fix the problem? Etc. Dec 14, 2018 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.