Friday 26 October 2012

Database Mirroring Error


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

2 comments:

  1. 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.

    ReplyDelete
  2. Excellent point Robert, thanks. I will keep that one in mind in future specifically Monday when I mirror the remaining db's :)

    ReplyDelete