Dev's Weblog

Please follow the following steps to setup the MS SQL Database Mirroring.The MS SQL database mirroring is a very good low cost high availability solution.The MS SQL database Mirroring can be configured using the SSMS (SQL Server Management Studio) but If you really want to know what’s happening behind the scene then you need to setup the mirroring using the command line.

You need to have at least 3 different servers or SQL Server instance to setup the database mirroring.I would recommend to have different SQL Server instances running on the different machines for the production environment.

For this example,

Please note that you need to Primary and Secondary instances running under Enterprise edition or Standard Edition but witness server can run on the Express edition.

The witness server will be responsible to monitoring the primary and secondary instance and will initiate the automatic failover if witness server and secondary server can not see the primary server.

Prerequisites:

• Make sure that Database to be mirrored is in running under FULL recovery model.

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model]
FROM sys.databases
GO

• Make sure that all the SQL Server instances are configured to allow TCP/IP connections.

You can check from the SQL Server configuration Manager —> SQL Server Network Configuration —> Protocols for TestDB

• Make sure that all the SQL Server instances are running under the same Domain user account.If the server instances are running under different domain user accounts, each requires a login in the master database of the others.
• Make sure that both Primary and Secondary instances are running same edition of SQL server software.

Configuration Steps:

• Create the Mirroring Endpoint on all the Servers.

-- Create Database Mirroring Endpoint on Principal Server
CREATE ENDPOINT [Mirroring]
AUTHORIZATION [EXAMPLE\Administrator]
STATE=STARTED
AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4);
GO

-- Create Database Mirroring Endpoint on Mirror Server
CREATE ENDPOINT [Mirroring]
AUTHORIZATION [EXAMPLE\Administrator]
STATE=STARTED
AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4);
GO

-- Create Database Mirroring Endpoint on the Witness Server
CREATE ENDPOINT [Mirroring]
AUTHORIZATION [EXAMPLE\Administrator]
STATE=STARTED
AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

-- Verify the Database Mirroring Endpoint Status
SELECT name, protocol_desc, state_desc FROM sys.database_mirroring_endpoints
GO

• Grant Privileges on the End Points. ( This steps is not necessary if you have created the Endpoints under the Domain admin account )

-- Execute it on Principal Server
-- Grant Connect to Permission for the DB Engine Accounts (Of Mirror and Witness Server)
-- on the Database Mirroring Endpoint (On Principal Server)
GRANT CONNECT ON ENDPOINT::Mirroring TO [EXAMPLE\Administrator];
GO

-- Execute it on Mirror Server
-- Grant Connect to Permission for the DB Engine Accounts (Of Principal and Witness Server)
-- on the Database Mirroring Endpoint (On Mirror Server)
GRANT CONNECT ON ENDPOINT::Mirroring TO [EXAMPLE\Administrator];
GO

-- Execute it on Witness Server
-- Grant Connect to Permission for the DB Engine Accounts (Of Principal and Mirror Server)
-- on the Database Mirroring Endpoint (On Witness Server)
GRANT CONNECT ON ENDPOINT::Mirroring TO [EXAMPLE\Administrator];
GO

• Backup the Database to be mirrored.

-- Backup the TestDB database on the Principal Server
USE [master]
GO
BACKUP DATABASE [TestDB]
TO DISK = '\\Node2\c$\SQL_DATABASE_BACKUPS\TestDB.bak';
GO
BACKUP LOG [TestDB]
TO DISK = '\\Node2\c$\SQL_DATABASE_BACKUPS\TestDB_Log.trn';
GO

• Restore the TestDB database on the Mirrored instance using NORECOVERY option.

-- Restoring the database TestDB from the backup file
USE [master]
GO
RESTORE DATABASE [TestDB]
FROM DISK = 'c:\SQL_DATABASE_BACKUPS\TestDB.bak'
WITH MOVE 'TestDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.T02\MSSQL\DATA\TestDB.mdf',
MOVE 'TestDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.T02\MSSQL\DATA\TestDB.lDF',
NORECOVERY, FILE = 1;
GO
RESTORE LOG [TestDB]
FROM DISK = 'c:\SQL_DATABASE_BACKUPS\TestDB_Log.trn' WITH NORECOVERY;
GO

• Setup the Mirroring sessions.

-- Adding the database to Database Mirroring Session (Execute it on Mirror Server)
USE [master]
ALTER DATABASE [TestDB]
SET PARTNER = 'TCP://Node1.EXAMPLE.COM:7028';
GO
-- Adding the database to Database Mirroring Session (Execute it on Principal Server)
USE [master]
ALTER DATABASE [TestDB]
SET PARTNER = 'TCP://Node2.EXAMPLE.COM:7028';
GO

-- Adding the database to Witness Session (Execute it on Principal Server)

ALTER DATABASE [TestDB]
SET WITNESS = 'TCP://Node3.EXAMPLE.COM:7028';
GO

USE [master]
SELECT * FROM sys.database_mirroring_witnesses;
GO