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.
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.
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.
Click the Mirroring Tab in the Select Page of the Database Properties. Click Configure Security.
This starts the Configure Database Mirroring Security Wizard.
Click Next to start the wizard.
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.
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.
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.
To configure the Mirror Server, ensure the server name is correct, Click Connect.
Like the Principal Server the Endpoint name and Ports are set.
Click Next.
To configure the Witness Server, ensure the server name is correct, Click Connect.
Click Next.
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.
Confirm what is going to be configured and Click Finish.
The successful completion of the configuration should be indicated as can be seen above.
Click Close.
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:
-
Backup the database on the Principal server.
-
Restore the database on the Mirror server leaving in a NoRecovery state.
-
Backup the transaction log on the Principal server.
-
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.
Good luck with configuring your SQL Server 2012 database mirroring.
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