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
