Configuring SQL Server Database Mirroring

SQL Server Database Mirroring is a straight forward way to provide improved database availability, and protection. In the following paragraphs, I'll go through the pre-requisites, configurations, and some basic troubleshooting which proved helpful during my implementation of Database Mirroring.

In this article, I will outline how to configure database mirroring with a domain account. Though Database mirroring can be configured between SQL instances on the same server, this should only be done for testing, and perhaps in development environments.

To provide the most resiliency and highest level of availability with database mirroring, the principal server, mirror server, and an optional witness server should all be hosted on separate physical servers.

Pre-Requisites

Prior to beginning database mirroring configuration, the following should be completed on all involved servers (principal, mirror, and witness):

1. SQL Server 2008, either standard or enterprise edition, installed on each server that will participate in the database mirroring.
2. SQL Server services configured to run under a domain account
3. TCP port 5022 (or whichever port you decide to use) open between all servers in the database mirroring configurations. Including windows firewall and network devices.
4. The SQL database mirroring account/group has been granted "Access this computer from the network". This will be the account (or group containing the account) under which database mirroring runs, and should be a service account/group. This needs to be done on all participating servers (principal, mirror, and witness).

o    Open up the Local Security Policy on the principal server

o    Expand Security Settings > Local Policies > User Rights Assignment

o    Grant the database mirroring service account "Access this computer from the network" permissions

Principal Server Configurations

Once the pre-requisites have been completed, the principal server and databases need to be prepared for database mirroring. The database recovery model needs to be set appropriately, and SQL logins and users need to be synchronized between the primary and mirror servers. These configurations can be verified through the following steps.

·         Verify the database uses the full recovery model

select name, recovery_model_desc from sys.databases

·         If the recovery_model_description is not FULL, set the recovery model for the database being mirrored to full:

alter database dbname set recovery full

·         If the mirrored database contains non domain users, SQL_LOGINs should be created on the mirror server with the same sid as the corresponding login on the principal server. This ensures the database failover goes smoothly, and all users are mapped to the correct logins on the mirror server. The following query can be used to get the sid of each SQL_LOGIN on the principal server:

select SDP.name as DB_PrincipalName

       , SDP.sid

       , SSP.name as Server_PrincipalName

       , SSP.default_database_name

       from sys.database_principals as SDP

join sys.server_principals as SSP on SDP.sid = SSP.sid

where SDP.type in ('U','S')

·         Make note of the user names and SIDs that will be created on the mirror server (any non domain users granted permissions on the mirrored database should be created on the mirror server with the same SID as on the principal server.

·         Take a full database backup and at least 1 transaction log backup of the database being mirrored.

·         Copy the full and transaction Log backups to the mirror server

Once the above steps have been completed on the principal server, it's time to configure the mirror server.

Mirror Server Configurations

The database will need to be restored (with move if the database file location differs between the principal and mirror locations), and non domain SQL logins and users created on the mirror server. I like to create an empty shell database before doing the restore and create the non domain SQL Logins or users. This allows me to associate the logins and users with the appropriate default database.

By associating the logins and users with the correct default database ahead of time, there is no need to make any changes going forward. If you choose not to do this, the first time the mirror server takes starts hosting the database, you should set the default database for any login that is using the mirrored database as it's default database.

·         Create a shell database that you will restore the mirror database over. The initial file sizes are unimportant, as you will be restoring the actual database over these files later.

USE [master]

GO

CREATE DATABASE [WTHIERDB_2k8] ON  PRIMARY

( NAME = N'QSDB_Data', FILENAME = N'D:\Data\WTHIERDB_2k8.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 100%)

 LOG ON

( NAME = N'QSDB_Log', FILENAME = N'D:\Data\WTHIERDB_2k8.ldf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16%)

GO

·         Create the users with the same sid, username, and password as the user had on the mirror server, with the default database set to the database to be mirrored.

USE [master]

GO

CREATE LOGIN [user]

WITH PASSWORD=N'p@ssw0rd'  -- password from principal server

, DEFAULT_DATABASE=[WTHIERDB_2k8]

, DEFAULT_LANGUAGE=[us_english]

, sid = 0x6829BC83217A7944A9C68A6AE8D567E5  -- sid from principal server

GO

·         Restore the database and at least 1 transaction log. Specifying the path to the database file location if the mirror servers database files path differs from that of the principal server. This will overwrite the shell db created earlier.

RESTORE DATABASE [WTHIERDB_2k8] FROM

DISK = N'D:\BackupPath\WTHIERDB_2k8_FULL_20110102_020045.bak'

WITH FILE = 1, MOVE N'QSDB_Data' TO N'D:\Data\WTHIERDB_2k8.mdf'

 , MOVE N'QSDB_Log' TO N'D:\Data\WTHIERDB_2k8.ldf'

 , NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

GO

RESTORE LOG [WTHIERDB_2k8] FROM DISK = N'D:\Backup\WTHIERDB_2k8_201101070928.trn'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

Everything is now in place, and we are ready to begin creating the mirror endpoints, and configuring actual database mirroring.

Create Mirror Endpoints

Now it's time to create the mirroring endpoints. The mirroring endpoints are essentially listeners for the database. Each one is configured to enable communication over a particular port and protocol. The mirroring endpoints by default, and for the purpose of this article, are configured for TCP port 5022.

·         Check for mirroring endpoints with the following query:

select name,type_desc,state_desc,port,is_dynamic_port,ip_address from sys.tcp_endpoints

Desired output:

Name	type_desc	state_desc	port	is_dynamic_port	ip_address
EndPointForMIrroring	DATABASE_MIRRORING	STARTED	5022	0	NULL

·         If there are no endpoints of type DATABASE_MIRRORING, they need to be created. Each server must have an endpoint for mirroring (the Principal, mirror, and witness server if used).

·         The following code can be used to create the mirroring endpoints on the principal and mirror servers

/****** Object:  Endpoint [Mirroring] ******/

CREATE ENDPOINT [Mirroring]

        STATE=STARTED

        AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

        FOR DATA_MIRRORING (ROLE = PARTNER

                            , AUTHENTICATION = WINDOWS NEGOTIATE

                            , ENCRYPTION = REQUIRED ALGORITHM RC4)

GO

-- grant permission to the Mirroring service account

GRANT CONNECT ON ENDPOINT::Mirroring TO [Domain\UserB]

GO

·         OPTIONAL: this step is only requred if automatic failover will be used. A dedicated witness server endpoint should be created with the following code:

/****** Object:  Endpoint [Mirroring] ******/

CREATE ENDPOINT [Mirroring]

        STATE=STARTED

        AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

        FOR DATA_MIRRORING (ROLE = WITNESS

                            , AUTHENTICATION = WINDOWS NEGOTIATE

                            , ENCRYPTION = REQUIRED ALGORITHM RC4)

GO

-- grant permission to the Mirroring service account

GRANT CONNECT ON ENDPOINT::Mirroring TO [Domain\UserB]

·         A witness server that may also serve as a principal or mirror endpoint should be created with the following code:

/****** Object:  Endpoint [Mirroring] ******/

CREATE ENDPOINT [Mirroring]

        STATE=STARTED

        AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

        FOR DATA_MIRRORING (ROLE = ALL

                            , AUTHENTICATION = WINDOWS NEGOTIATE

                            , ENCRYPTION = REQUIRED ALGORITHM RC4)

GO

-- grant permission to the Mirroring service account

GRANT CONNECT ON ENDPOINT::Mirroring TO [Domain\UserB]

Verify Servers and Database(s) are Ready to Start Mirroring

·         Make sure each participating server has a mirroring endpoint and it is started.

-- verify there is a mirroring endpoint and it's port

SELECT type_desc, port FROM sys.tcp_endpoints;

GO

Desired output:

type_desc	port
DATABASE_MIRRORING	5022

-- check mirroring endpoint properties

SELECT state_desc,role_desc,type_desc,name,protocol_desc

FROM sys.database_mirroring_endpoints

GO

Desired output for principal and mirror servers:

state_desc	role_desc	type_desc	name	protocol_desc
STARTED	PARTNER	DATABASE_MIRRORING	Mirroring	TCP

Desired output for witness server:

state_desc	role_desc	type_desc	name	protocol_desc
STARTED	WITNESS	DATABASE_MIRRORING	Mirroring	TCP

·         Verify the appropriate accounts have been granted permission to connect to the mirroring endpoints on each of the servers.

-- verify mirroring security is granted connect perms:

SELECT 'Metadata Check';

SELECT EP.name, SP.STATE,

   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))

      AS GRANTOR,

   SP.TYPE AS PERMISSION,

   CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))

      AS GRANTEE

   FROM sys.server_permissions SP , sys.endpoints EP

   WHERE SP.major_id = EP.endpoint_id

   ORDER BY Permission,grantor, grantee;

GO

name	STATE	GRANTOR	PERMISSION	GRANTEE
Mirroring	G	Domain\UserA	CO	Domain\UserB (Mirroring service account)

Set the Server Endpoints

The order the server endpoints are set is important, and needs to be followed for mirroring to be configured properly. The mirroring endpoint needs to be set on the mirror server first, then the principal server. If the enpoints are set in the reverse order, you will receive the following error:

Msg 1416, Level 16, State 31, Line 2
Database "WTHIERDB_2k8" is not configured for database mirroring.

·         From the mirror server set the endpoint to reference the principal server (if mirroring is done over a non-standard port, replace 5022 with the port you are using)

ALTER DATABASE databasename SET PARTNER='TCP://PrincipalServernameFQDN:5022'

GO

·         From the principal server set the endpoint to reference the mirror server (if mirroring is done over a non-standard port, replace 5022 with the port you are using)

-- When you establish a session using ALTER DATABASE statements, the

-- session begins with the SAFETY property set to FULL.

-- The session begins in high-safety mode.

-- After the session starts, a witness can be added.

ALTER DATABASE databasename SET PARTNER='TCP://MirrorServernameFQDN:5022'

GO

-- OPTIONAL STEP, only needs to be done

-- If you are using a witness server

ALTER DATABSE databasename SET WITNESS='TCP://WitnessServernameFQDN:5022'

GO

Congratulations, database mirroring is now configured. The principal and mirror servers have been configured, and are synchronizing according to the configured safety mode, full (this is the default). If the witness server is configured, the database is capable of automatic failover.

Further information on database mirroring can be found on Microsoft's

TROUBLESHOOTING

ERROR MESSAGE:

Msg 1469, Level 16, State 1, Line 2
Database mirroring cannot be enabled because the "WTHIERDB_2k8" database is an auto-close database on one of the partners.

PROBLEM: The database on either the principal, the mirror, or both servers has the AutoClose property enabled.

·         This can be verified with the below query. This needs to be checked on both the principal and mirror servers.

·         A 1 indicates AutoClose is enabled. This should be disabled as outlined int he RESOLUTION section.

·         A 0 indicates AutoClose is disabled, and nothing needs to be done.

SELECT DATABASEPROPERTYEX('wthierdb_2k8','IsAutoClose')

RESOLUTION: Disable the AutoClose property on the database

·         First disable AutoClose on the principal server

ALTER DATABASE wthierdb_2k8 SET AUTO_CLOSE OFF

·         Once this is done, the AutoClose will need to be disabled on the mirror: After disabling AutoClose for the database on the principal server, take a transaction log backup and restore it with norecovery on the mirror server.

ERROR MESSAGE:

Msg 1478, Level 16, State 0, Line 2
The mirror database, "WTHIERDB_2k8", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.

PROBLEM: As stated in the error message, there is too long a period of unrestored transaction log between the principal and mirror server.

RESOLUTION: Roll the mirror database forward by restoring more recent transaction logs. Once this has been completed follow the steps in the Set Server Endpoints section.

ERROR MESSAGE:

The server network address "TCP://<server>:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

PROBLEM: There are multiple problems that will cause this error. If you are able to telnet from the principle and mirror server and from the mirror to the principal server on the port being used for mirroring using the FQDN, It is likely a permission issue, as outlined in the resolution.

RESOLUTION: Grant the mirrroring account "Access this computer from the network" on all participating servers (principal, mirror, and witness servers), as outlined in step 4 of the pre-requisites.