SQL Server – Mirroring

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.

Benefits of Database Mirroring

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.

Limitations and Restrictions

  • You cannot mirror the master, msdb, temp, or model system databases.
  • A mirrored database cannot be renamed during a database mirroring session.
  • You cannot mirror a database that belongs to an Always On availability group.
  • 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.

Database Mirroring – Terminology

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.

Recommendations

  • Use a very recent full database backup or a recent differential database backup of the principal database.
  • If a log backup job is scheduled to run very frequently on the principal database, you might have to disable the backup job until mirroring has started.
  • If possible, the path (including the drive letter) of the mirror database should be identical to the path of the principal database.
  • If the principal database has any full-text catalogs, we recommend that you see Database Mirroring and Full-Text Catalogs (SQL Server).
  • For a production database, always back up to a separate device.

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.

Database Mirroring – Operating Modes

The synchronous and asynchronous operating modes for database mirroring sessions.

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.

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 whether 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 following table summarizes how the operating mode of a session depends upon its transaction safety setting and on state of the witness.

Operation ModeTransaction SafetyWitness State
High-performance modeOFFNULL (no witness)**
High-safety mode without automatic failoverFULLNULL (no witness)
High-safety mode with automatic failover*FULLCONNECTED

*If the witness becomes disconnected, we recommend that you set WITNESS OFF until the witness server instance becomes available.
**If a witness is present in high-performance mode, the witness does not participate in the session. However, to make the database available, at least two of the server instances must remain connected. Therefore, we recommend keeping the WITNESS property set to OFF in high-performance mode sessions.

  • High performance (asynchronous) – When database mirroring is configured in this mode, the transaction log records are sent asynchronously to the mirrored server. The primary server does not wait for any confirmation from the mirrored server. This mode does not guarantee that all transactions that are committed on the principal server are saved in the mirrored database. You can use asynchronous mirroring if:
    • Possible data loss of the last few transactions is acceptable.
    • The database mirror is located in a remote site, which results in too long latency periods.
  • High safety without automatic failover (synchronous) – When database mirroring is configured in this mode, the primary server confirms the transaction only after receiving the acknowledgment from the mirrored server. This mode offers high protection of data, since at any point of time the databases in both principal and mirror server are always consistent. But the requirement of two-phase transactional commit in this mode has an impact on the performance.
  • High safety with automatic failover (synchronous) – When database mirroring is configured in this mode, an automatic failover to the mirrored server takes place in the event of problems on the principal server, thereby increasing the availability of your production database. This mode requires a Witness Server to be installed to check the availability and status of the principal server.

Factors Affecting Behavior on Loss of the Principal Server

Transaction safetyMirroring state of mirror databaseWitness stateBehavior when principal is lost
FULLSYNCHRONIZEDCONNECTEDAutomatic failover occurs.
FULLSYNCHRONIZEDDISCONNECTEDMirror server stops; failover is not possible, and the database cannot be made available.
OFFSUSPENDED or DISCONNECTEDNULL
(no witness)
Service can be forced to the mirror server (with possible data loss).
FULLSYNCHRONIZING or SUSPENDEDNULL
(no witness)
Service can be forced to the mirror server (with possible data loss).

To configure database mirroring

  1. After connecting to the principal server instance, in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases, and select the database to be mirrored.
  3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
  4. To begin configuring mirroring, click the Configure Security button to launch the Configure Database Mirroring Security Wizard.
  5. The Configure Database Mirroring Security Wizard automatically creates the database mirroring endpoint (if none exists) on each server instance, and enters the server network addresses in the field corresponding to the role of the server instance (Principal, Mirror, or Witness).
  6. Optionally, change the operating mode. The availability of certain operating mode(s) depends on whether you have specified a TCP address for a witness. The options are as follows:
    • High performance (asynchronous)
    • High safety without automatic failover (synchronous)
    • High safety with automatic failover (synchronous)
database mirroring
  1. When all of the following conditions exist, click Start Mirroring to begin mirroring:
    • You are currently connected to the principal server instance.
    • Security has been configured correctly.
    • The fully-qualified TCP addresses of the principal and mirror server instances are specified (in the Server network addresses section).
    • If the operating mode is set to High safety with automatic failover (synchronous), the fully-qualified TCP address of the witness server instance is also specified.
  2. After mirroring begins, you can change the operating mode and save the change by clicking OK.

Pause or Resume a Database Mirroring Session

At any time, you can suspend a database mirroring session, which might improve performance during bottlenecks, and you can resume a suspended session at any time.

Using SQL Server Management Studio

  1. During a database mirroring session, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases, and select the database.
  3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
  4. To pause the session, click Pause. A prompt asks for confirmation; if you click Yes, the session is paused, and the button changes to Resume.
  5. To resume the session, click Resume.

Using Transact-SQL

To pause database mirroring

  1. Connect to the Database Engine for either partner.
  2. From the Standard bar, click New Query.
  3. Issue the following Transact-SQL statement:
Syntax:
ALTER DATABASE database_name SET PARTNER SUSPEND
where database_name is the mirrored database whose session you want to you want to suspend.
The following example pauses the AdventureWorks2012 sample database:
ALTER DATABASE AdventureWorks2012 SET PARTNER SUSPEND;

To resume database mirroring

  1. Connect to the Database Engine for either partner.
  2. From the Standard bar, click New Query.
  3. Issue the following Transact-SQL statement:
Syntax:
ALTER DATABASE database_name SET PARTNER RESUME
where database_name is the mirrored database whose session you want to resume.
The following example pauses the AdventureWorks2012 sample database.
ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME;

Remove Database Mirroring

The database owner can manually stop a database mirroring session by removing mirroring from the database. Database mirroring from a database in SQL Server 2019 (15.x) by using SQL Server Management Studio or Transact-SQL. 

Using SQL Server Management Studio

  1. During a database mirroring session, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases, and select the database.
  3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
  4. In the Select a Page pane, click Mirroring.
  5. To remove mirroring, click Remove Mirroring. A prompt asks for confirmation. If you click Yes, the session is stopped and mirroring is removed from the database.

Using Transact-SQL

  1. Connect to the Database Engine of either mirroring partner.
  2. From the Standard bar, click New Query.
  3. Issue the following Transact-SQL statement:
Syntax:
ALTER DATABASE database_name SET PARTNER OFF
where database_name is the mirrored database whose session you want to remove.
The following example removes database mirroring from the AdventureWorks2012 sample database.
ALTER DATABASE AdventureWorks2012 SET PARTNER OFF;

 

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply