I came across a bug while testing the setup of Database Mirroring on SQL Server 2016 Standard Edition with SP1 and using the newest version of SQL Server SQL Server Management Studio: v17.8.1
Firstly, the preparation went fine.
Create the database on the Principal and perform both a Full and Transaction Log Backup of the new database.
CREATE DATABASE db1;
BACKUP DATABASE db1 TO DISK = 'C:\SQLBackups\db1.bak';
BACKUP LOG db1 TO DISK = 'C:\SQLBackups\db1.trn';
Next restore the backups on the Mirror.
RESTORE DATABASE db1 FROM DISK='\\SQL2K16SEP1\SQLBackups$\db1.bak' WITH NORECOVERY;
RESTORE LOG db1 FROM DISK='\\SQL2K16SEP1\SQLBackups$\db1.trn' WITH NORECOVERY;
The firewall Inbound Rules for both SQL / Mirroring were added on the Principal and Mirror.
Good so all the prep work is done, lets proceed to setup Mirroring using the Wizard on Principal.
Go through the Configure Security steps.
Bang you get the following 927 error:
Database ‘db1’ cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)
So, what is going on here?
Looking at Profiler during the setup we see that the GUI issues a use [db1] on the Mirror instance and of course that is going to end badly as the database is not accessible and hence you get the error that ‘db1’ cannot be opened. Microsoft needs to update the GUI to use [master].
So how do you resolve this?
Well you need to complete the Database Mirroring setup manually and the steps are:
1.) On the Mirror use the following to set the principal as a partner:
ALTER DATABASE [db1] SET PARTNER = 'TCP://SQL2K16SEP1:5022';
2.) Then finally on the Principal use the following to set the mirror as a partner:
ALTER DATABASE [db1] SET PARTNER = 'TCP://SQL2K16SEDR1:5022';
Database Mirroring should now be working.
I’m sure Microsoft will address this issue in a future release of SSMS, but until then the above should hopefully assist you until then.
Previous
Next