SQL Server Database Mirroring

SQL Server Database Mirroring can be configured in three different Modes:

1). Sync Mode with Automatic Failover: Witness server is required. Data movement from Primary to Mirror server in Sync mode, first committed at Mirror Server. Zero Data loss in case of Failover. Both EE and STD Edition supports this Mirroring Configuration.

2). Sync Mode with Manual Failover: Witness is not required. Data movement from Primary Server to Mirror Server in Sync mode, first committed at Mirror Server. Zero Data Loss in case of Failover. Both EE and STD Edition supports this Mirroring Configuration.

3). ASync Mode with Manual Failover: Witness Server is not required. Data Movement from Primary Server to Secondary Server Async Mode. First committed at Primary and than log is harden at Mirror Server. This Mode has performance benefits to high transaction OLTP systems, at cost of data loss. This Mirroring Configuration can only be setup in EE edition of SQL Server.

---

--Script to Mirror SQL Server DB in Sync mode without witness server.

--1). Primary SQL Server.

CREATE ENDPOINT Endpoint_Mirroring

STATE=STARTED

AS TCP (LISTENER_PORT=5022)

FOR DATABASE_MIRRORING (ROLE=PARTNER)

GO

USE master ;

GO

CREATE LOGIN [Domain\SQLSVC] FROM WINDOWS ;

GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Domain\sqlsvc];

GO

--2). Run the following script on Mirror server.

CREATE ENDPOINT Endpoint_Mirroring

STATE=STARTED

AS TCP (LISTENER_PORT=5022)

FOR DATABASE_MIRRORING (ROLE=ALL)

GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Domain\sqlsvc];

GO

ALTER DATABASE testmirror

SET PARTNER =

'TCP://PrincipalServer.domain.com:5022'

GO

--3).Connect to Principal instance and run the following script.

ALTER DATABASE testmirror

SET PARTNER =

'TCP://mirrorserver.domain.com:5022'

GO

--At this point Mirroring setup for TestMirror DB is complete. Following steps highlight how to monitor mirrored DB.

---

--Run the following Monitor Mirroring scripts on Principal and Mirror instance.

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

SELECT db.name, m.mirroring_role_desc

FROM sys.database_mirroring m

JOIN sys.databases db

ON db.database_id = m.database_id

WHERE db.name = N'testmirror';

GO

--Add Database to Database Mirror Monitor and set threshold.

SQL Server Database Mirror Monitor Threshold

--Check SQL Server Errorlog for any unsend or unrestored log messages.

--Setting up the Threshold monitor will write threshold violation to the sql server error log.

---

--Only EE supports Alter Partner Saftey. By default Partner Safety is Full. i.e. Transactions are transmitted from Primary to Mirror server in Sync Mode.

---

--Below script to change Mirror Configuration in Sync Mode.

--Needs to be execute at Primary Server.

ALTER DATABASE testmirror SET PARTNER SAFETY FULL

---

--Below Script to change Mirror Configuration in Async Mode. Only EE support this Feature.

--Needs to be execute at Primary Server.

ALTER DATABASE testmirror SET PARTNER SAFETY OFF

---

You will not notice following error message in SQL Server if you are not using EE Edition.

Msg 1473, Level 16, State 6, Line 1

This SQL Server edition does not allow changing the safety level. ALTER DATABASE command failed.

---

--Script to Stop Database Mirroring

--Script need to executed at Mirror Server

ALTER DATABASE testmirror SET PARTNER OFF

drop endpoint Endpoint_Mirroring

--Script to be executed at Primary Server.

drop endpoint Endpoint_Mirroring

---

--Script to add Database to Already Configured Mirrored Endpoint.

--1). Script need to be executed on Mirror Server

ALTER DATABASE [testmirror2]

SET PARTNER =

'TCP://PrimaryServer.domain.com:5022'

GO

--2). Script need to be executed on Primary Server

ALTER DATABASE [testmirror2]

SET PARTNER =

'TCP://SecondaryServer.domain.com:5022'

GO

---

--Steps to perform Database Failover

--First check the database is set to Safety Full.

--ALTER DATABASE testmirror SET PARTNER SAFETY FULL

ALTER DATABASE [testmirror] SET PARTNER FAILOVER

GO

--Verify the failover. Run the following script on new principal server.

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

GO

SELECT db.name, m.mirroring_role_desc

FROM sys.database_mirroring m

JOIN sys.databases db

ON db.database_id = m.database_id

WHERE db.name = N'testmirror';

GO

---