How to configure Database Mirroring in an Active Directory Environment Using SQL Server 2008


Database Mirroring is one of the High Availability features available with SQL Server 2005 onwards. Though, SQL Server 2005 RTM does not support Database Mirroring feature natively, you can start the database mirroring feature in RTM by executing the following command:

1: DBCC TRACEON (1400);
2: GO

This is the first part of the two part series on database mirroring configuration. The First part talks about how to configure database mirroring in a domain based environment and the second part will talk about how to configure database mirroring on a stand alone machine in a workgroup environment. The later post targets for people wants to configure database mirroring for learning and training purpose. Since the Database Mirroring is a High Availability solution, configuring the database mirroring on a stand-alone machine doesn’t serve the purpose. However, its ideal for learning and training purpose.

For a detailed information on Database Mirroring, please have a look on this excellent white paper on the TechNet website. In this post, we are going to configure database mirroring in an Active Directory environment. A quick lookup on some of the database mirroring terms:

TCP Endpoint: A database mirroring session requires a TCP endpoint with DATABASE_MIRRORING payload. An endpoint is used to communicate between Principal, Mirror and Witness instance. Also, we need to understand that it is used to transport all the database mirroring traffics. You can have only one Database Mirroring endpoint per instance of SQL Server. The default configuration port for Database Mirroring endpoint is 5022; however, its highly recommended that you should use a different port instead of the default port.

Principal Role: The database with Principal role accepts all the connections from the clients and serves all the transactions.

Mirror Role: It is a stand-by database which receives all transactions however, it does not allow any connection to the database. In case of the Principal database is down, the mirror database can be promoted as principal (automatic / manually) and accepts all connections.

Witness Role: You can use SQL Server Express edition to participate as a witness server. What I mean to say that all version of SQL Server can work as a witness server. Witness server is used when the database mirroring session works in High Availability Mode. The Witness server talks to the principal and mirror server, and in case of a the principal server goes down, the witness promotes the mirror server as a Principal server. 

Operating Modes in Database Mirroring

Database Mirroring operates in three different modes. They are:

  • High Performance Mode
  • High Protection Mode / High Safety Mode
  • High Availability Mode

High Performance Mode: While operating in High Performance Mode, a database mirroring session requires two instances of SQL servers to participate. The data transfer mode between Principal and Mirror server is asynchronous, thus, the Principal Server does not wait for an acknowledgement from the Mirror server, which will gives better performance from the Application point of view. However, when the Principal server goes down, the failover process will be manual, and since the data transfer is in asynchronous mode, data loss may occur during the switchover.

High Protection Mode / High Safety Mode: High Protection mode is also known as High Safety Mode. This modes also require two SQL Server instances; however, the data transfer between Principal and Mirror instance is synchronous. Thus, when the mirroring session starts, the mirror database quickly sync with the principal database. In case the principal server goes down, you need to manually switchover the mirroring session and promotes the mirror database as the principal database. Since the data transfer mode is synchronous, there can be a performance impact on the application.

High Availability Mode: While operating in High Availability mode, a database mirroring session requires three SQL Server instances, a Principal Server, a Mirror Server and a Witness server. This mode offers synchronous data transfers between Principal and Mirror server, along with automatic failover feature. If the principal server participating in database mirroring session goes down, then the witness server confirms the same. The witness server along with the mirror server creates the quorum and agree for a switchover. The witness server promotes the mirror server as a Principal server and accept the client connections. Once the original principal server comes online, the witness server demotes it as a Mirror server, and all the transactions from the principal server starts flowing to mirror server.

Configuring the Service Accounts

While installing a SQL Server instance in a domain environment, you should ensure that the SQL Server Services are using individual domain user accounts. However, their should not be any additional rights given for the service accounts. For the current post, we are going to use an SQL Server 2008 Express edition instance as a Witness server. For this post, we use the following instances for the initial configuration.

  • TestLab1   —> This will work as the Principal Server
  • TestLab2   —> This will work as the Mirror Server
  • TestLab3   —> This will work as the Witness Server 
Configuring the Database Mirroring Session

1. As we start configuring Database Mirroring session, first we need to create endpoint on all the instances. Since we are using three different SQL server instances, we will configure the Database Mirroring Session in High Availability Mode. Execute the following codes on Principal and Mirror servers to create the Database Mirroring endpoints. Since we are using three different instances, we will use the port 7028 to configure Database Mirroring.

-- Create Database Mirroring Endpoint on Principal Server
CREATE ENDPOINT [Mirroring]
AUTHORIZATION [TESTLAB\SQL_D_TestLab1]
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 [TESTLAB\SQL_D_TestLab2]
STATE=STARTED
AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4);
GO

We need to create an endpoint on the Witness instance as well. Execute the following code to create an endpoint on the Witness instance:

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

2. Once all the endpoints are created, you can verify the details about all the endpoints by executing the following command on the individual instances.

-- Verify the Database Mirroring Endpoint Status
SELECT * FROM sys.database_mirroring_endpoints
GO

You will have only one row per instance as an output of the above command.

3. Once the endpoints are created, you need to grant connect to permission on the endpoints for the Database Engine Service accounts. Thus, the database engine service account should have grant connect to permission on the remote Database Mirroring endpoint. So the following piece of code need to execute on the different instances, as mentioned in the comment:

-- 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 [TestLab\SQL_D_TestLab2];
GRANT CONNECT ON ENDPOINT::Mirroring TO [TestLab\SQL_D_TestLab3];
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 [TestLab\SQL_D_TestLab1];
GRANT CONNECT ON ENDPOINT::Mirroring TO [TestLab\SQL_D_TestLab3];
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 [TestLab\SQL_D_TestLab1];
GRANT CONNECT ON ENDPOINT::Mirroring TO [TestLab\SQL_D_TestLab2];
GO

4. We have completed all the Database Mirroring endpoints related configuration. Now we are going to prepare the database for mirroring session. For this post, we are going to use the test database SG_Mirror. We will going to create a table name dbo.Mirrortest in the database and we will add some data into the table.

-- For this demo, we are going to create a Database named SG_MIRROR
USE [master]
GO
IF EXISTS (SELECT DB_ID() FROM sys.databases WHERE name = 'SG_MIRROR')
DROP DATABASE [SG_MIRROR];
CREATE DATABASE [SG_MIRROR]
GO
-- Creating a Test table to test the database mirroring features
USE [SG_MIRROR]
GO
Create Table dbo.MirrorTest
(
TestID INT IDENTITY(1,1),
TestName VARCHAR(50),
TestDate DATE DEFAULT (GETDATE())
);
GO
-- Adding initial data to the table dbo.MirrorTest
DECLARE @id int = 1;
WHILE (@id <= 100000)
BEGIN
DECLARE @name VARCHAR(50) = 'TestName';
SET @name = (@name +CONVERT(VARCHAR(10), @id,0));
INSERT INTO SG_MIRROR.dbo.MirrorTest (TestName) VALUES (@name);
SET @id = (@id + 1);
END
GO
-- Verifying the initial data
SELECT COUNT(*) FROM SG_MIRROR.dbo.MirrorTest;
GO

5. Take a Full backup of the database SG_MIRROR, followed by a Transaction-Log Backup on the Principal server (TestLab1). Restore the backups on the Mirror Server (TestLab2) with NORECOVERY option. The below mentioned code is used to create a backup file of SG_MIRROR database and save the backup file on the share of the Mirror Server (TestLab2).

-- Backup the SG_Mirror database on the Principal Server
USE [master]
GO
BACKUP DATABASE [SG_MIRROR]
TO DISK = '\\TestLab2\Migration\SG_Mirror_Full.bak';
GO
BACKUP LOG [SG_MIRROR]
TO DISK = '\\TestLab2\Migration\SG_Mirror_Log1.trn';
GO

The code mentioned below is used to restore the database from the data and log backup file using NORECOVERY option.

-- Restoring the database SG_MIRROR from the backup file
USE [master]
GO
RESTORE DATABASE [SG_MIRROR]
FROM DISK = 'D:\Migration\SG_Mirror_Full.bak'
WITH MOVE 'SG_MIRROR' TO 'D:\SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SG_MIRROR.mdf',
MOVE 'SG_MIRROR_log' TO 'D:\SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SG_MIRROR_1.LDF',
NORECOVERY, FILE = 1;
GO
RESTORE LOG [SG_MIRROR]
FROM DISK = 'D:\Migration\SG_Mirror_Log1.trn' WITH NORECOVERY;
GO

6. Now we are going to add the database in to the Database Mirroring sessions. We need to execute the following code on the instances as mentioned below:

-- Adding the database to Database Mirroring Session (Execute it on Mirror Server)
USE [master]
ALTER DATABASE [SG_MIRROR]
SET PARTNER = 'TCP://TESTLAB1.TESTLAB.LOCAL:7028';
GO

-- Adding the database to Database Mirroring Session (Execute it on Principal Server)
USE [master]
ALTER DATABASE [SG_MIRROR]
SET PARTNER = 'TCP://TESTLAB2.TESTLAB.LOCAL:7028';
GO


ALTER DATABASE [SG_MIRROR]
SET WITNESS = 'TCP://TESTLAB3.TESTLAB.LOCAL:7028';
GO

7. Once the above set of commands executed, you have completed all the necessary configuration to create the database mirroring sessions. Congratulation!!!

To verify that all the configurations are working correctly, execute the following command on the witness server (Testlab3):

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

8. You will get the following pictures, when you connect to both the Principal and Mirror instance using SQL Server 2008 Management Studio.

image

9. Since we have configured Database Mirroring in High Availability Mode, if the Principal Database instance goes down (in this case, TestLab1), the Mirror database will be promoted as Principal database (TestLab2) and it will serves the connection request from the clients. As discussed earlier, the failover will be automatic; the Witness instance confirms that the Principal instance is down and promotes the mirror instance as a Principal instance. When the server back online, the Witness instance talks with the failed principal instance and demotes it as a Mirrored instance. Then the transactions will start flowing from Principal instance to Mirror instance.

Note: Database Mirroring can not be configured, if the participated database is having FILESTREAM features enabled. While the Database Mirroring is configured using SQL Server 2008 instances, if the database participated in a database mirroring session is corrupt, then the corrupt page has been restored from the Mirror database.

This post discussed about how to configure database mirroring in an Active Directory environment. The scripts used in this post, can be found here.The next part of this series will discuss about how to configure database mirroring on a stand alone environment.  We will also check how to configure database mirroring using SSMS without writing much code..

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Database Mirroring. Bookmark the permalink.

5 Responses to How to configure Database Mirroring in an Active Directory Environment Using SQL Server 2008

  1. Pingback: Review of My Blog by WordPress for year 2010 | SQL…. Still Learning

  2. Pingback: Database Mirroring with SQLCMD | SQL…. Still Learning

  3. Gary80 says:

    Hi Sudeepta, this seems like a well laid out plan. Even though I am new to this I can see myself succeed with this. I have 1 question though, I have a VB .Net application that has the IP address for Server A hardcoded in the app.config file. If I mirror the database onto Server B(diff IP address(), how will the application know to make the switch. Is there some change I must make to the config file? Thanks!

  4. Gary80 says:

    keep up the good work!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s