Database Mirroring

Database Mirroring is process having a redundant copy of a single database at another location to ensure continuous data availability in case of a disaster on the principal database.

Database mirroring ensures that one viable copy of a database will always remain accessible during disaster recovery or down time needed for the principal server

The principal server is the source server and the mirror is the destination server

There are two types of operation modes when using database mirroring:

Synchronous operation mode:

This is used when very real time accuracy is required; which means that the system must immediately copy every change in the principal’s content to the mirror and vice-versa (this is referred to as a hot standby)

Asynchronous operation mode:

This is used when the content is not fully synchronized, and thus may result in some data loss (this is referred to as a warm standby)

Advantages:

  • Relatively easy to set up
  • Database mirroring is an automatic failover process
  • All application connection can be redirected automatically with proper configuration
  • There will not be data transfer latency (synchronous mode)

Disadvantages:

  • Can only have one to one relationship with principal and mirror
  • Cannot be used for reporting solution (mirror in restoring state)
  • Mirroring supports only Full Recovery (not bulk or simple mode)

SQL Script for Database Mirroring

--1 primary

alter database mirror
set recovery full
go

--2 primary
backup database mirror
to disk = 'c:\s\full.bak'
go

--3 primary

backup log mirror
to disk = 'c:\s\tlog.trn'
go

--4 primary

create endpoint endpoint_principal
state = started
as tcp (listener_port = 5022)
for database_mirroring (role = partner)
go

--5 primary

alter database mirror
set partner = 'tcp://server1:5022'
go

--6 mirror

USE [master]
RESTORE DATABASE [mirror] 
FROM  DISK = N'C:\d\full.bak' 
WITH  FILE = 1,  
MOVE N'mirror' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mirror.mdf',  
MOVE N'mirror_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mirror_log.ldf',  
NORECOVERY,  
NOUNLOAD,  
STATS = 5
GO

--log mirror

RESTORE LOG [mirror] 
FROM  DISK = N'C:\d\tlog.trn' 
WITH  FILE = 1,  
NORECOVERY,  
NOUNLOAD,  
STATS = 10
GO

--7 mirror

create endpoint endpoint_mirror
state = started
as tcp (listener_port = 5023)
for database_mirroring (role = partner)
go

--8 mirror

alter database mirror
set partner = 'tcp://server2:5023'
go
--ENABLE SQLCMD MODE TO SWITCH BETWEEN DIFFERENT SQL SERVERS WTIH A A QUERY PANE
--QUERY - SQLMCMD MODE 

--:CONNECT SQLSERVER1
--Or for a non-default instance 
--:CONNECT DESKTOP-QMOOH4U\DEV

--DROP DATABASE MIRROR

USE MASTER
GO

CREATE DATABASE MIRROR
GO

USE MIRROR
GO

CREATE TABLE CHOCOLATES
(NAME VARCHAR (25))

INSERT INTO CHOCOLATES
VALUES ('GODIVA'),('MARS'),('HERSHYS'),('DOVE'),('KITKAT')

SELECT * FROM CHOCOLATES

BACKUP DATABASE [MIRROR] TO  DISK = N'C:\Mirror\CHOC.BAK' WITH iNIT

BACKUP LOG [MIRROR] TO  DISK = N'C:\Mirror\CHOC.TRN' WITH iNIT

USE [master]
GO

--CHANGE CONNECTIONS TO DEV SERVER: THEN RUN

:CONNECT DESKTOP-QMOOH4U\DEV

RESTORE DATABASE [MIRROR] 
FROM  DISK = N'C:\Mirror\CHOC.BAK' 
WITH  FILE = 1,  
MOVE N'MIRROR' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\DATA\MIRROR.mdf',  
MOVE N'MIRROR_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\DATA\MIRROR_log.ldf',  
NORECOVERY
GO

RESTORE LOG [MIRROR] 
FROM  DISK = N'C:\Mirror\CHOC.TRN' 
WITH  FILE = 1,  
NORECOVERY
GO


--GO TO SECURITY - LOGINS - (RC) [NT AUTHORITY\SYSTEM] - PROPERTIES - SERVERROLE (SYSADMIN) - SELECT DB FOR DBM - DB OWNER

--CHANGE CONNECTIONS TO DEV SERVER: THEN RUN

:CONNECT DESKTOP-QMOOH4U\DEV

INSERT INTO CHOCOLATES
VALUES ('OH HENRY'),('ALMOND JOY')

SELECT * FROM CHOCOLATES

ALTER DATABASE MIRROR SET PARTNER OFF
DROP DATABASE MIRROR

USE MASTER
GO

CREATE DATABASE MIRROR
GO

USE MIRROR
GO

CREATE TABLE CHOCOLATES
(NAME VARCHAR (25))

INSERT INTO CHOCOLATES
VALUES ('GODIVA'),('MARS'),('HERSHYS'),('DOVE'),('KITKAT')

SELECT * FROM CHOCOLATES

--1 primary

alter database mirror
set recovery full
go

--2 primary
backup database mirror
to disk = 'c:\s\full.bak'
go

--4 primary

backup log mirror
to disk = 'c:\s\tlog.trn'
go

--6 primary

create endpoint endpoint_principal
state = started
as tcp (listener_port = 5022)
for database_mirroring (role = partner)
go


--9 primary

alter database mirror
set partner = 'tcp://server1:5022'
go


--SQL SCRIPTS FOR MONITORING AND INVESTIGATING  DATABASE MIRRORING:

--INFORMATION ABOUT DATABASE MIRRORING

select DB_NAME(database_id) dbname,mirroring_state_desc,mirroring_role_desc,
mirroring_safety_level_desc,mirroring_safety_sequence
mirroring_partner_name,mirroring_partner_instance,
mirroring_witness_state,mirroring_witness_state_desc,
mirroring_failover_lsn,mirroring_connection_timeout,mirroring_redo_queue,
mirroring_end_of_log_lsn,mirroring_replication_lsn,*
from master.sys.database_mirroring
where mirroring_state is not null

--INFORMATION ABOUT DATABASE MIRRORING CONNECTIONS

select state_desc,connect_time,login_time,authentication_method,principal_name,
remote_user_name,last_activity_time,is_accept,login_state_desc,
receives_posted,sends_posted,total_bytes_sent,total_bytes_received,
total_sends,total_receives,*
from sys.dm_db_mirroring_connections

--INFORMATION ABOUT DATABASE MIRRORING ENDPOINTS

select name,endpoint_id,protocol_desc,type_desc,state_desc,role_desc,
connection_auth_desc,*
from sys.database_mirroring_endpoints

--SCRIPT TO INDICATE WHICH DATABASE HAS BEEN MIRRORED:

SELECT DB_NAME(database_id) AS mirrored
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
ORDER BY DB_NAME(database_id);

--SCRIPT TO INDICATE WHICH DATABASE IS IN SYNC MODE

SELECT DB_NAME(database_id) AS synchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc = 'FULL'
ORDER BY DB_NAME(database_id);

--SCRIPT TO INDICATE WHICH DATABASE IS IN ASYNC MODE

SELECT DB_NAME(database_id) AS asynchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc = 'OFF'
ORDER BY DB_NAME(database_id);

--SCRIPT TO INDICATE WHICH DATABASE HAS FULLT SYNCHRONIZED OR NOT

SELECT 
 DB_NAME(database_id) AS fully_synchronized
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc = 'SYNCHRONIZED' 
ORDER BY DB_NAME(database_id);

--SETTING THE ASYNCHRONOUS MODE OFF AND ON
 
SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SAFETY OFF;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been set to asynchronous mirroring mode.'';'
  AS command_to_set_mirrored_database_to_use_synchronous_mirroring_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'FULL'
ORDER BY DB_NAME(database_id);

 SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SAFETY FULL;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been set to synchronous mirroring mode.'';'
  AS command_to_set_mirrored_database_to_use_synchronous_mirroring_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'OFF'
ORDER BY DB_NAME(database_id);

 --TO SUSPEND THE DATABASE MIRRORING

 SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER SUSPEND;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring paused.'';' 
  AS command_to_pause_mirroring_for_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_state_desc <> 'SUSPENDED'
ORDER BY DB_NAME(database_id);

 --TO RESUME THE DATABASE MIRRORING 

SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER RESUME;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring resumed.'';' 
  AS command_to_resume_mirroring_for_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_state_desc = 'SUSPENDED'
ORDER BY DB_NAME(database_id);

--SETTING TO FAILOVER DATABASE MIRRORING
 
SELECT 
 'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER FAILOVER;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been been manually failed over.'';' 
  AS command_to_manually_failover_the_mirrored_database
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'FULL'
AND mirroring_state_desc = 'SYNCHRONIZED'
ORDER BY DB_NAME(database_id);

-- HISTORY OF RESULTS

Exec msdb..sp_dbmmonitorresults 'db',1,0

--use on mirror db to stop miroring and drop db
alter database mirror set partner off
restore database mirror
drop database MIRROR
COMPARISON
LOG SHIPINGDATABASE MIRRORING
It automatically sends transaction log backups from one database On another server. two copies of a single database on different server instances of SQL Server Database Engine.
T-Logs are backed up and transferred to secondary server.Individual T-Log records are transferred using TCP endpoints.
It can be configured as one to many One principal server to one mirror server.
requires a manual failoverAutomatic or manual
You can use a secondary database for reporting purposes when the secondary database restore in STANDBY mode.In a continous revoery state: Mirrored DB can only be accessed using snapshot DB.
 Supports both Bulk Logged Recovery Model and Full Recovery Model. Supports only Full Recovery model.
The restore can be completed using either the NORECOVERY or STANDBY option.The restore can be completed using with NORECOVERY.
No limit. Can log ship to many serverGenerally good to have 10 DB’s for one server.
There will be data transfer latency. >1min.There will not be data transfer latency.
Both committed and uncommitted transactions are transferred to the secondary database.Only committed transactions are transferred to the mirror database.
It provides a warm standby solution that has multiple copies of a database and require a manual failover.When a database session is synchronized, database 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).

To configure database mirroring, you can use the following procedure:

1. Connect to the primary server.

SQL Server Database Mirroring

2. Ensure the database is at Full recovery model.

SQL Server Database Mirroring


3. Enable database mirroring.

SQL Server Database Mirroring

4. Configure database mirroring security.

SQL Server Database Mirroring

5. Define whether or not a witness server is used for automatic failover. Since we do not want automatic failover, select “No” and continue to next step.

SQL Server Database Mirroring


6. Define database mirroring endpoint for the principal server. Be sure to open the listener port in the server firewall.

SQL Server Database Mirroring


7. Connect and define the endpoint for the mirror server. In order to successfully connect to the secondary server, we must ensure the instance listening port and SQLBrowser listening port are open.

SQL Server Database Mirroring
SQL Server Database Mirroring

8. Define the security account for principal and mirror server. This service account should be part of the sysadmin SQL server role if it’s not already granted so.

https://isqlplus.com/sql-server/database-mirroring/

9. Click Finish to commit the endpoint configuration.

https://isqlplus.com/sql-server/database-mirroring/

10. Start mirroring session. You would only do so if a full database backup and a transaction log backup from the principal server have been restored on the mirror server with “no recovery”.

SQL Server Database Mirroring


11. If all is configured correctly, the database mirroring session is now active.

SQL Server Database Mirroring