I have been
setting up multi instance database mirroring for the last couple of days along
with some other DR related processes. I
will do a detailed post about these at a later date. I came across this particular error again (for
about the sixth or seventh time) Error Message below;
Msg 1416, Level 16, State 31, Line “LineNumberHere”
Database "DatabaseNameHere" is not configured for database mirroring
The reason
for me posting this is because each time I have seen this error it has been at
the same point in the process of setting up database mirroring. As always I prefer to avoid using the GUI and
Wizard and instead have a set of scripts to setup mirroring. I had created my endpoints, granted
permissions, taken full and log backups of the database(s) in question on the
PRINCIPAL Server and restored them to the MIRROR server WITH NORECOVERY. The next step in my process is to run the
below to enable the mirroring partnership;
ALTER DATABASE "DatabaseNameHere" SET PARTNER = 'TCP://FullyQualifiedNameHere:PortNumberHere';
GO
The reason
for the failure is that I ran the script on the PRINCIPAL and it should be run
on the MIRROR server first, Doh!
I remember
the first time I saw this error I followed what a lot of people in various
forums advised and amongst other solutions (which also didn’t work) recreated
the backups and restored them. This can
be a time consuming process if you are working with large databases so I would
advise you to first check that you are running this step on the MIRROR server
first as it could save you a considerable amount of time and effort.
Enjoy!
Chris
another good thing to keep in mind about this error is that if the ALTER DATABASE ... SET PARTNER command fails on the principal, you may need to re-run the command on the mirror before you re-run it on the principal. If it attempts to start a mirroring session and is able to connect to the mirror, but it fails, it reverts the setting on the mirror as well.
ReplyDeleteExcellent point Robert, thanks. I will keep that one in mind in future specifically Monday when I mirror the remaining db's :)
ReplyDelete