The estimated reading time for this post is 7 minutes

Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

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

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.

There are two mirroring operating modes.  high-safety mode supports synchronous operation and high-performance mode, runs asynchronously.

Benefits of Database Mirroring

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

Database Mirroring Terms and Definitions

• Automatic Failover: The process by which, when the principal server becomes unavailable, the mirror server to take over the role of principal server and brings its copy of the database online as the principal database.
• Failover Partners: The two server instances (the principal server or the mirror server) that act as role-switching partners for a mirrored database.
• Forced Service: A failover initiated by the database owner upon the failure of the principal server that transfers service to the mirror database while it is in an unknown state.
• High-Performance Mode: The database mirroring session operates asynchronously and uses only the principal server and mirror server. The only form of role switching is forced service (with possible data loss).
• High-Safety Mode: The database mirroring session operates synchronously and, optionally, uses a witness, as well as the principal server and mirror server.
• Manual Failover: A failover initiated by the database owner, while the principal server is still running, that transfers service from the principal database to the mirror database while they are in a synchronized state.
• Mirror Database: The copy of the database that is typically fully synchronized with the principal database.
• Mirror Server: In a database mirroring configuration, the server instance on which the mirror database resides.
• Principal Database: In database mirroring, a read-write database whose transaction log records are applied to a read-only copy of the database (a mirror database).
• Principal Server: In database mirroring, the partner whose database is currently the principal database.
• Redo Queue: Received transaction log records that are waiting on the disk of a mirror server.
• Role: The principal server and mirror server perform complementary principal and mirror roles. Optionally, the role of witness is performed by a third server instance.
• Role Switching: The taking over of the principal role by the mirror.
• Send Queue: Unsent transaction log records that have accumulated on the log disk of the principal server.
• Session: The relationship that occurs during database mirroring among the principal server, mirror server, and witness server (if present).

After a mirroring session starts or resumes, the process by which log records of the principal database that have accumulated on the principal server are sent to the mirror server, which writes these log records to disk as quickly as possible to catch up with the principal server.

• Transaction Safety: A mirroring-specific database property that determines whether a database mirroring session operates synchronously or asynchronously. There are two safety levels: FULL and OFF.
• Witness: For use only with high-safety mode, an optional instance of SQL Server that enables the mirror server to recognize when to initiate an automatic failover. Unlike the two failover partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.

The illustration below shows database mirroring with high-safety operation mode.

The illustration below shows database mirroring with high-performance operation mode

Implementing Database Mirroring by using T-SQL and SSMS

Prerequisites

For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.

The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.

The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database.

Verify that the mirror server has sufficient disk space for the mirror database.

When you are creating the mirror database on the mirror server, make sure that you restore the backup of the principal database specifying the same database name WITH NORECOVERY. Also, all log backups that were created after that backup was taken must also be applied, again WITH NORECOVERY.

Restrictions

Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases.

A mirrored database cannot be renamed during a database mirroring session.

Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.

Database mirroring is not supported with either cross-database transactions or distributed transactions.

Implementation Steps

In this illustration, I’m going to show you how is implementation of database mirroring between two SQL Server instances in same server without witness by SQL authentication login.

SAEED-PC (SQL Server 13.0.1742.0 – Saeed) as Principal Server

SAEED-PC\SaeedSQL (SQL Server 13.0.1742.0 – Saeed) as Mirror Server

1 – Get full backup and log backup a database which will be principal

2 – Restore full backup and log backups of principal database in mirror server with same name including WITH NORECOVERY backup option for all backup files (You must do it manually in mirror server).

3 – Right click on principal database, click Properties and choose Mirroring. In Mirroring pane click on Configure Security tab and follow direction as below:

Remember this step is necessary to be done for first database mirroring, for next database mirroring you can skip this step.

This step also will create endpoint ports internally but for checking you can use these system views to find out endpoint which is created and its port number.

Source code

SELECT * FROM sys.endpoints
SELECT * FROM sys.tcp_endpoints

If there is no any endpoint for database mirroring, you can use this T-SQL statements to create endpoint.

Source code

CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING
(ENCRYPTION = DISABLED , ROLE = ALL)

4 – Create inbound and outbound rules for new ports and enable them through Windows Defender Firewall with Advanced Security.

5 – Specify each database’s partner with its address. At first do it in mirror database then in principal database by using T-SQL.

Source code

ALTER DATABASE TestMirroring SET PARTNER = 'TCP://SAEED-PC:5022'
ALTER DATABASE TestMirroring SET PARTNER = 'TCP://SAEED-PC:5023'

6– Refresh SQL Server instances.

Now to verify our database mirroring functionality, right click on principal database and open Properties then click Failover button. Refresh SQL Server instances and select Test table from ex mirror database which is principal database now, after failover.

I hope this blog post would be helpful for you to understand and implement basic concept of database mirroring.

Saeed Jabarpourfard

Saeed is a Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer, with extensive experience in Database Development and Administration. He has been working with SQL Server since year 2010, and he used SQL Server 2008 R2 as his first RDBMS.

More Posts - Website