Setting Up Database Mirroring on Sql Server 2008

Following are the steps that I follow to set up DB Mirroring between Sql Server 2008 databases on separate servers.

Overview:

1. Create Sample Database for Mirroring on Principal Server
2. Create some database objects in the database to test i.e. tables, stored procedures, etc
3. Create Database Mirroring EndPoints on Principal, Mirror and Witness
4. CREATE necessary accounts and GRANT permissions.
5. Prepare Mirroring database
6. Enable / Set up mirroring.
7. Create Snapshot database on Mirror database.
8. Failover:
   1. Test Failover
   2. Force Failover
9. Server Objects.
10. 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.

1. First, you want to perform a manual FAILOVER [from Principal to Mirror]
2. Suspend DB mirroring [stop any data flow from new Principal to new Mirror]
3. Upgrade original Principal Server
4. Bring Principal up and running
5. Resume DB Mirroring to bring previous Principal (now Mirror) up to date.
6. Perfom another manual FAILOVER to the newly upgraded Mirror (original Principal).
7. Suspend Mirroring
8. Upgrade Mirror (original)
9. Bring it back up.
10. 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

Read Full Post »

Scalability and Performance design techniques for high transactional volume, VLDBs

Update: March 26, 2017 – Added links to posts with details steps.

When designing VLDBs for large transactional volume, I keep these design techniques for better scalability and performance. Details on each of these points will come in future.

• Processor related:

1. 1. MAX DEGREE OF PARALLELISM  and MAXDOP
   2. COST THRESHOLD FOR PARALLELISM
   3. Soft-NUMA

• Memory
  1. /PAE, /AWE, /3GB (not needed for 32 bit machines)
  2. Lock Pages in Memory
  3. MAX/MIN SERVER MEMORY

• FileGroups and Partitioning:

1. 1. Multiple FG & Files
   2. No data in Primary FG (for online restore)
   3. Use Table Partitions
   4. Allocate large file size initially for data files (MDFs NDFs)
   5. Allocate calculated large size for transactional log file
   6. Instant File Initialization

• Indexes and related tasks:

1. 1. Covered Indexes
   2. Separate nCI and CI
   3. Online index rebuild
   4. Filtered Indexes
   5. Indexed Views (Automatic Query Substitution)
   6. Fill Factor and Pad Index
   7. Update Stats regularly

• Disks:

1. 1. Disk Partition Alignment
   2. Separate disks for os/binaries, sysDB, TempDB, MDF, LDF and BAK file locations
   3. RAIO 10

• Maintenance:

1. 1. Stripe Backup
   2. SET PAGE_VERIFY CHECKSUM
   3. BACKUP with CHECKSUM
   4. RESTORE VERIFYONLY WITH CHECKSUM
   5. DBCC CHECKDB Optimization
   6. Archiving

Hope this helps,

_SqlTimes

Read Full Post »