This past week I have been working with a client to setup mirroring on a SQL 2014 instance.  I realize mirroring is a deprecated feature; but many DBAs and companies still use it as a fail safe line of defense. And the fact that Mirroring has been a “deprecated feature” since SQL 2012 and is still available in SQL 2016; I think it might be around a little longer.

I am writing about this not to call out any client on the use of Mirroring or to promote or condone mirroring but to remind me of the things to look for when setting up any mirroring environment.

The Task at hand

My client was upgrading from SQL 2012 to 2014.  The current SQL 2012 instance was mirrored to a second server.  I was to reproduce the mirroring on the 2014 instance in preparation for the rollout to production.

This particular setup was problematic for me because of Windows Firewall rules and having multiple instance (heck multiple versions) of SQL installed on the same server.

Server Setup

The following version of SQL were installed on the same server:

• SQL 2008 R2 (default instance)
• SQL 2012 ( named instance) mirrored with a second server
• SQL 2014 (named instance)  this was the server I needed to setup with mirroring to reproduce the SQL 2012 environment.
• Windows Firewall was basically configured to close everything unless a rule specified otherwise.

This list of things to check may not apply to all configurations and all mirroring setups.  It is just some things that prevented this setup for succeeding.

1. With multiple instances and connecting via Named Pipes, it is very important to have the “Named Pipes” protocol enabled in SQL Configuration Manager.
2. With multiple instances SQL Browser becomes very important, especially if you do not want to use Port Numbers in your connection string.   Here is the important part, check the Windows Firewall rules to ensure UDP Port #1434 has both an Incoming and Outgoing rule.
3. Mirroring requires its own Port for communication as well.  Incoming and Outgoing Windows Firewall rules will have to be created to ensure communication between the two servers.  By default this is TCP Port #5022.  However, because the SQL 2012 instance was already using this port for mirroring, I had to configure SQL 2014 with port #5023.  Again, another firewall rule. Actually, I just added the port to the existing firewall rule.
4. The last piece that was problematic was permissions.  Because Principle and Target each had their own domain service account, each account needed to be added to the opposite server with the correct database permissions. 

Once all of this was figured out and configured correctly, the mirroring session configured and started up with no problems!