IT Consulting: Microsoft SQL Server 2012 Mirroring

Setup Microsoft SQL Server 2012 Mirroring with Failover Step by Step

1. Architecture:

In our lab, we'll use 4 Virtual machines:

SRV1: Active Directory.
SQL1: Principal SQL Server.
SQL2: Mirror SQL Server.
WINSQL: Witness Server.

2. Introduction:

Database mirroring is a simple strategy that offers the following benefits:

Increases availability of a database.
Increases data protection.
Improves the availability of the production database during upgrades.

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. Starting database mirroring on a database, initiates a relationship, known as a database mirroring session, between these server instances.

One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

Our case we will setup "High-safety mode with automatic failover"

High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.

3. Installation:

3.1. Installation prerequisites:

I'm using hyper-v to create virtual machines. All virtual machines are running with Windows Server 2012 R2. I will setup SQL Server 2012 Enterprise Edition.

Note:

SQL Server mirroring is working only with Enterprise Edition.

3.2. install Clustering feature:

We will install Clustering feature in each of SQL1 and SQL2.

In Server Manager, Click on "Manager" - "Add Roles and Features"

Check "Failover Clustering"

In start Menu, open "Failover Cluster Manager"

In this step you must install "Failover Clustering" in the second server otherwise you can not add it in the cluster.

Choose "Create Cluster"

Add the two SQL Servers

Give a name to the cluster and choose an IP address (floating address)

Now our 2 SQL Servers are online and running on cluster

3.3. Install SQL Server 2012:

Install SQL Server 2012 in each 3 VMs (SQL1, SQL2, WINSQL)

3.4. Backup and Restore Database:

We need to setup a DataBase to work with, I will use "AdventureWorks" for test. You find here how add "AdventureWorks" in SQL Server.

We change the recovery Model to Full. Right click on the DataBase "Properties" then "Options"

Now we need to backup the DataBase and restore it in the other servers (SQL2, WINSQL)

Right click on the DataBase "Tasks" - "Backup"

Next step is to restore the DataBase in the other two servers with NORECOVERY option

Go to the second SQL Server, Copy the backup of the database we done before in backup folder SQL Server. Right click on "Database" - "Restore Database"

Select "Device"

Click "Add" and select the backup of the Database

Go to "Options" choose the Recovery state "RESTORE WITH NORECOVERY"

Click "OK"

3.5. Setup mirroring:

First thing is to use a domain account who has access permission in all SQL Servers.

Right click on the Database "Properties" - "Mirroring". Execute "Configure Security"

Select "Yes" to configure the witness server with the mirroring

This step for the principal Server

Next step is to specified the mirror server

Third step is for the witness server

You can leave these fields blank if you use the same account for all the servers.

Click on "Start Mirroring"

Now the mirroring is successfully configured

We need to activate the high availability, in Start menu execute "SQL Server Configuration Manager"

Select "SQL Server Services" Right click on "SQL Server (instance)"

In tab "AlwaysOn High Availability" check "Enable AlwaysOn Availability Groups"

Do the same thing in the second SQL Server (SQL2)

We check Database in principal server (SQL1):

Database in mirror server (SQL2)

3.6.Testing:

A simple test to check if the mirroring and the failover cluster are working, I will add a new column in table"Person.Address" named "NewTestColumn"