SQL Server 2012 Database Mirroring

Views 2927

Database mirroring was first introduced in SQL Server 2005. This technology is still available as a viable option to meet your High Availability (HA) or Disaster Recovery (DR) requirements. In SQL Server 2012 the introduction of AlwaysOn Availability Groups (AG) uses the mirroring technology to transfer the data from your primary replica to the secondary replica(s).

Database mirroring can be configured with two (2) operating modes :

High Safety Mode provides synchronous data transfer allowing for either Automatic or Manual Failover. The setup consists of a Principal Server, a Mirror Server and a Witness server (Depending on the failover type). As this setup is utilising the synchronous transfer of transactions this has the potential to impact the performance on the principle.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-image_2

High Safety Mode – Automatic Failover   High Safety Mode No Automatic Failover

High Performance Mode provides asynchronous data transfer with a Manual Failover. If you are going to operate in his mode to perform a Manual Failover, you need to change to the High Safety Mode to perform the Failover. Once you have failed over you will need to change back to the High Performance Mode. The setup consists of a Principal Server and a Mirror Server. As it’s name indicates this mode does not have the same impact on performance on the system as the High Safety mode as the transaction does not have to be completed on the Mirror server, this in-turn can lead to the potential of data loss in the event of a failure at the principle server.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-image_4

High Performance Mode

Configuring Database Mirroring

On your Principle server identify the database that you wish to mirror. Open SSMS | Expand the Databases tree | Right Click the appropriate database | Properties.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-image_6

Click the Mirroring Tab in the Select Page of the Database Properties. Click Configure Security.

This starts the Configure Database Mirroring Security Wizard.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image001_2

Click Next to start the wizard.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image002_2

At this point in the configuration we need to determine if we are going to use a Witness server or not. This decision impacts if you are needing to utilize the High Safety or High Performance Modes. For this article we are going to choose Yes to allow us to configure a Witness Server.

Click Next.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image003_2

The High Safety Mode allows you to configure the mirroring to utilise synchronous data transfer. This mode allows you configure manual or automatic failover. If you wish to have automatic failover in the event of an issue then you need to configure a Witness Server.

Click Next.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image004_2

Configuring the Principal Server to use mirroring requires an endpoint that the mirroring connects to between the servers. As can be seen above the endpoint name is “Hadr_endpoint”. This is because I have configured this server previously for AlwaysOn Availability Groups. The default name would normally be Mirroring. The default mirroring port of 5022 has been set.

Click Next.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image005_2

To configure the Mirror Server, ensure the server name is correct, Click Connect.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image006_2

Like the Principal Server the Endpoint name and Ports are set.

Click Next.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image007_2

To configure the Witness Server, ensure the server name is correct, Click Connect.

Click Next.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image008_2

The service accounts for the Principal, Mirror & Witness servers are required. In my environment the service accounts are running under the same service account. (This is not the best practice but this is a development environment). As I am also logged in as this account I do not need to specify any accounts here.

Click Next.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image009_2

Confirm what is going to be configured and Click Finish.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image010_2

The successful completion of the configuration should be indicated as can be seen above.

Click Close.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image011_2

At this point in time you have two (2) options to continue with the setup.

Do not start mirroring as you do not have your databases on the secondary server. Navigate away from the Database Properties tab into SSMS.

To be able to start mirroring, we have to:

  1. Backup the database on the Principal server.
  2. Restore the database on the Mirror server leaving in a NoRecovery state.
  3. Backup the transaction log on the Principal server.
  4. Restore the transaction log on the Mirror server leaving in a NoRecovery state.

Navigate back to the Database Properties window and click Start Mirroring.

Now that you have configured your database to be mirrored you can keep track of status of your mirroring using the Database Mirroring Monitor.

Right Click on your mirrored database | Tasks | Launch Database Mirroring Monitor.

Windows-Live-Writer-SQL-Server-2012-Database-Mirroring_6351-clip_image013_2

Good luck with configuring your SQL Server 2012 database mirroring.

One response to “SQL Server 2012 Database Mirroring”

  1. nickolas says:

    I wanted to setup dB mirroring on two servers, both are server 2012r2 Standard edition, I get an error that principal and mirror must be of the same edition, I don’t know what I haven’t done right to get this error

Leave a Reply

Your email address will not be published. Required fields are marked *

Warwick

I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.

Search