SQL server database mirroring and practical demonstration. |

SQL Server database mirroring is a disaster recovery and high availability technique that involves two or more SQL Server instances on the same or different machines. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Active copy of database is known as ‘Principal’ database & secondary copy is known as ‘Mirrored’ database. SQL Server instance that host principal database known as ‘Primary’ and server that holds mirrored copy known as ‘Secondary’ server. In special cases, there can be a third SQL Server instance that acts as a Witness.

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.

Please note, Microsoft has depreciated this feature and may not be available in SQL 2016 OR later version of SQL. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always-On Availability Groups instead.

Following steps can be used to setup SQL mirroring for a database reside on two different instances. In my test environment, I am using two SQL instance on different machine (SQL1 & SQL2). Version of SQL is 2014 & operating system is 2012 R2.

Verify SQL services running with domain user credential on both servers running.

On the primary SQL server, execute following script one by one.

----A. Create sample database for mirroring.
USE [master]
CREATE DATABASE MirroringDemo
GO

ALTER DATABASE [MirroringDemo] SET RECOVERY FULL
GO

----B. Create table schema.

USE [MirroringDemo]
CREATE TABLE EmployeeCity
(
ID INT IDENTITY(1,1),
NAME VARCHAR (100),
City VARCHAR(100)
)
GO

----C. Insert 100 records
USE [MirroringDemo]
INSERT INTO EmployeeCity
VALUES
('Pradeep', 'Pune')
GO 100

----D. Take database backup.

BACKUP DATABASE [MirroringDemo]
TO  DISK = N'D:\Backup\MirroringDemo.bak'
GO

On Secondary SQL server, execute following script one by one.

----A. Restore sample database.

USE [master]
RESTORE DATABASE [MirroringDemo]
FROM  DISK = N'D:\Backup\MirroringDemo.bak'
WITH   NORECOVERY
GO

-----B. Create mirroring end point (if mirroring endpoint already exists then drop it using query ‘drop endpoint ’

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO

 ----C. Create SQL login for service a/c, if login already exists then leave this step.

USE [master]
CREATE LOGIN [domain\sqlservice] FROM
WINDOWS WITH DEFAULT_DATABASE=[master]
GO

 ----D. Assign permission on endpoint to SQL service a/c.

USE [master]
GRANT CONNECT ON ENDPOINT::endpoint_mirroring to [domain\sqlservice]
GO

----E. This is important step, set mirroring partner now. The URL should point to first server. This should always be performed on secondary database first.

ALTER DATABASE [MirroringDemo]
SET PARTNER = 'TCP://SQL1.Domain.LOCAL:5022'
GO

Go back to primary server, execute following script one by one.

-----A. Create mirroring end point (if mirroring endpoint already exists then drop it using query ‘drop endpoint ’

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO

----B. Create SQL login for service a/c, if login already exists then leave this step.
USE [master]
CREATE LOGIN [domain\sqlservice] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

 ----C. Assign permission on endpoint to SQL service a/c.

USE [master]
GRANT CONNECT ON ENDPOINT::endpoint_mirroring to [domain\sqlservice]
GO

----D. Set mirroring partner now. The URL should point to Second server.

ALTER DATABASE [MirroringDemo]
SET PARTNER = 'TCP://SQL2.EV.LOCAL:5022'
GO

The database mirroring is ready now. The database will now be visible like below.

SQL Server database mirroring can be set to provide high availability or disaster recovery. Depending on the needs, a DBA can choose among three available modes. By default, SQL mirroring works in high safety mode.

High safety without automatic fail-over – Transaction logs are written and committed on the principal and mirror databases synchronously. This might produce delay and slower operation because transaction log must be committed on both databases before considering it secure. This does not provide automatic fail-over and DBA needs to manually change mirrored copy to principal one when primary server is not available.

High safety with automatic fail-over –This require one additional server called as witness server and works same as high safety mode. If the principal database goes down, the automatic fail-over process initiate by witness server, then mirrored database becomes the principal.

High performance – the asynchronous communication, transaction logs are written and committed on the principal server first, and later sent and committed to the mirror server. Automatic fail-over isn’t possible. When primary server is down then DBA needs to manually force mirrored copy to become principal database, this may cause data loss due to asynchronous communication between database. To reduce data loss, take the tail of the log backup if the failed server allows, remove mirroring and restore the tail of the log on the previously mirrored database

----Change safety mode
USE [master]
ALTER DATABASE [MirroringDemo] SET SAFETY OFF

Use following syntax’s for pause, resume, remove OR fail-over mirrored database.

----Pause mirroring.
ALTER DATABASE [MirroringDemo] SET PARTNER SUSPEND
GO

----Resume mirroring:
ALTER DATABASE [MirroringDemo] SET PARTNER RESUME
GO

----Failover
ALTER DATABASE [MirroringDemo] SET PARTNER FAILOVER
GO

--Remove mirroring
ALTER DATABASE [MirroringDemo] SET PARTNER OFF
GO

My suggestion of every students to learn how things can be configure using SSMS for understanding the concepts. Configuration using SQL query greatly helps for documentation purpose, reapplying same steps and repetitive task. Here is the slide show of SQL mirroring using SSMS.

This slideshow requires JavaScript.

You can monitor the health of database mirroring by a feature called ‘database mirroring monitor.

SQL DMV to check mirroring details.

SELECT
               db_name(SM.[database_id])         AS [Database Name]
               ,SM.mirroring_state               AS [Mirror State]
               ,SM.mirroring_state_desc          AS [Mirror State]
               ,SM.mirroring_partner_name        AS [Partner Name]
               ,SM.mirroring_partner_instance    AS [Parnter Instance]
               ,SM.mirroring_role_desc           AS [Mirror Role]
               ,SM.mirroring_safety_level_desc   AS [Safety Level]
               ,SM.mirroring_witness_name        AS [Witness]
               ,SM.mirroring_connection_timeout  AS [Timeout(sec)]
               ,SM.mirroring_redo_queue          AS [REDO QUEUE]
FROM sys.database_mirroring AS SM
WHERE mirroring_guid IS NOT null
ORDER BY [Database Name];

All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about Windows Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.

If you like above content then feel free to share it with your colleague and friends. If you wish to improve the content the feel free to comment and contact me directly.