Following are the steps that I follow to set up DB Mirroring between Sql Server 2008 databases on separate servers.
Overview:
- Create Sample Database for Mirroring on Principal Server
- Create some database objects in the database to test i.e. tables, stored procedures, etc
- Create Database Mirroring EndPoints on Principal, Mirror and Witness
- CREATE necessary accounts and GRANT permissions.
- Prepare Mirroring database
- Enable / Set up mirroring.
- Create Snapshot database on Mirror database.
- Failover:
- Test Failover
- Force Failover
- Server Objects.
- Other points.
Server Names:
|
Initial Instance Role
|
Domain Service Account
|
Server Name
|
|
Principal
|
AD\sql1
|
SalesDB.AD.com
|
|
Mirror
|
AD\sql1
|
SalesDBFailOver.AD.com
|
|
Witness
|
AD\sqlWitness
|
WitnessDB.AD.com
|
Assumptions:
- Principal, Mirror and Witness services run with domain accounts.
- All users and application connections to Sql Server are also domain accounts.
Step 1: Create Sample Database for Mirroring on Principal Server
First let’s create a database on Principal server for mirroring. FULL recovery model is required for databases participating in mirroring.
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-- Step 01: CREATE Principal Database.
-- Run on Principal Server
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
CREATE DATABASE DBMir
ON
PRIMARY
(
NAME = N'DBMir_PRIMARY_Data'
, FILENAME = N'E:\MS SQL\MSSQL_2K8\Play1\MDF\DBMir_Primary_Data.MDF'
, SIZE = 1024 MB
, MAXSIZE = 2 GB
, FILEGROWTH = 10
)
LOG ON
(
NAME = N'DBMir_Log'
, FILENAME = N'E:\MS SQL\MSSQL_2K8\Play1\LDF\DBMir_Log.LDF'
, SIZE = 1024 MB
, MAXSIZE = 2048 MB
, FILEGROWTH = 10
)
GO
-- SET recovery model to FULL
ALTER DATABASE [DBMir]
SET RECOVERY FULL
GO
Step 02: Create some database objects and server level objects to test after failover i.e. logins, tables, stored procedures, etc.
-- sample server level object
USE MASTER
GO
CREATE LOGIN [test] WITH PASSWORD = 'test'
WITH DEFAULT_DATABASE = [DBMir]
, DEFAULT_LANGUAGE = [us_english]
GO
USE [DBMir]
GO
CREATE USER [test] FOR LOGIN [test]
GO
-- Sample table
CREATE TABLE dbo.Test
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, Name VARCHAR(40) NULL
, Age INT NULL
, Blob VARCHAR(MAX) NULL
)
GO
-- Insert a few records, before setting up Mirroring.
INSERT INTO dbo.Test (Name, Age, Blob) VALUES('Name1', 10, REPLICATE(CONVERT(VARCHAR(MAX), 'ac'),10000))
GO 10
Step 03: Create Database Mirroring EndPoints on Principal, Mirror and Witness.
-- Create endpoint on Principal
CREATE ENDPOINT DBMirror_Principal
STATE = STARTED
AS
TCP (LISTENER_PORT = 7022) -- Do not use port 7022 on prod. server
FOR DATABASE_MIRRORING (ROLE = PARTNER)
GO
-- Create endpoint on Mirror
CREATE ENDPOINT DBMirror_Mirror
STATE = STARTED
AS
TCP (LISTENER_PORT = 7023)
FOR DATABASE_MIRRORING (ROLE = PARTNER)
GO
-- Create endpoint on Witness
CREATE ENDPOINT DBMirror_Witness
STATE = STARTED
AS
TCP (LISTENER_PORT = 7024)
FOR DATABASE_MIRRORING (ROLE = WITNESS)
GO
Step 04: CREATE necessary accounts and GRANT permissions
Run on Principal
-- CREATE LOGIN for Witness (service account) on Principal
-- and GRANT CONNECT permission on Principal's EndPoint
CREATE LOGIN [AD\sqlWitness] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Principal TO [AD\sqlWitness]
GO
-- CREATE LOGIN for Witness (service account) on Mirror
-- and GRANT CONNECT permission on Mirror's EndPoint
CREATE USER [AD\sqlWitness] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Mirror TO [AD\sqlWitness]
GO
-- CREATE LOGIN for Principal/Mirror(same service account on both) on Witness
-- and GRANT CONNECT permission on Witness's EndPoint
CREATE LOGIN [AD\sql1] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Witness TO [AD\sql1]
GO
Step 05: Prepare Mirroring database
-- Run on Principal
BACKUP DATABASE DBMir TO DISK = N'E:\MS SQL\MSSQL_2K8\Play1\Backup\DBMir_Principal.BAK'
GO
BACKUP LOG DBMir TO DISK = N'E:\MS SQL\MSSQL_2K8\Play1\Backup\DBMir_Principal.TRN'
GO
-- Copy the database files over to Mirror Instance
-- Run on Mirror
RESTORE DATABASE DBMir FROM DISK = N'E:\MS SQL\MSSQL_2K8\Play2\Backup\DBMir_Principal.BAK'
WITH MOVE 'DBMir_Principal_Data' TO 'E:\MS SQL\MSSQL_2K8\Play2\MDF\DBMir_Mirror.MDF'
, MOVE 'DBMir_Log' TO 'E:\MS SQL\MSSQL_2K8\Play2\LDF\DBMir_Mirror_Log.LDF'
, NORECOVERY
, REPLACE
GO
RESTORE LOG DBMir FROM DISK = N'E:\MS SQL\MSSQL_2K8\Play2\Backup\DBMir_Principal.TRN'
WITH NORECOVERY
GO
Step 06: Enable / Set up mirroring
-- Run on Mirror: Inform Mirror about Principal (first step)
ALTER DATABASE DBMir
SET PARTNER = 'TCP://SalesDB.AD.com:7022'
GO
-- Run on Principal: Inform Principal about Mirror
ALTER DATABASE DBMir
SET PARTNER = 'TCP://SalesDBFailOver.AD.com:7023'
GO
-- Run on Principal: Inform Principal about Witness
ALTER DATABASE DBMir
SET WITNESS = 'TCP://WitnessDB.AD.com:7024'
GO
Step 07: Create Snapshot database on Mirror database.
-- Create snapshot on Mirror to run sample queries
USE MASTER
GO
CREATE DATABASE DBMir_SnapShot
ON
(
NAME = 'DBMir_Primary_Data'
, FILENAME = 'E:\MS SQL\MSSQL_2K8\Play2\MDF\DBMir_SnapShot2.DS'
)
AS SNAPSHOT OF DBMir
GO
SELECT COUNT(*) FROM dbo.Test
GO
Step 08: Failover
If you want to test the cussecc of failover in our environment use the following FAILOVER command.
-- To test FailOver, run the following query on Principal
-- Smooth failover happens. If there are any SQL Logins,
-- To map orphan users, run sp_change_users_login on Mirror
ALTER DATABASE DBMir
SET PARTNER FAILOVER
GO
But if the Principal goes down and you want to force FAILOVER to
the mirror server, use the following command. This could result in
some data loss.
-- To force failover, when the Principal is unavailable, run this on Mirror
-- Pre-requisite: Principal must be unavailable
ALTER DATABASE DBMir
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO
Step 09: Server Objects
A note on logins: Make sure logins are created in the same order on the Mirror server as on the Principal server. This way login SID for will remain the same and will not create any orphans.
Any other server level objects could be transferred using an SSIS package designed specifically for this purpose.
Step 10: Other points
If you want to run any maintenance on one of the servers participating in Mirroring the following commands will come to good use. Lets say, you want to upgrade patches on Principal and Mirror database server.
- First, you want to perform a manual FAILOVER [from Principal to Mirror]
- Suspend DB mirroring [stop any data flow from new Principal to new Mirror]
- Upgrade original Principal Server
- Bring Principal up and running
- Resume DB Mirroring to bring previous Principal (now Mirror) up to date.
- Perfom another manual FAILOVER to the newly upgraded Mirror (original Principal).
- Suspend Mirroring
- Upgrade Mirror (original)
- Bring it back up.
- Resume DB Mirroring.
To perform all the above steps, the following commands will be useful.
-- To drop DB Mirroring
ALTER DATABASE DBMir
SET PARTNER OFF -- to DROP DB Mirroring
GO
-- To re-establish DB Mirroring
ALTER DATABASE DBMir
SET PARTNER ON
GO
-- To temporarily suspend mirrorring
ALTER DATABASE DBMir
SET PARTNER SUSPEND
GO
-- To resume, suspended mirroring
ALTER DATABASE DBMir
SET PARTNER RESUME
GO
Hope this helps,
_Sqltimes
