Scripts For Database Mirroring Presentation At Rocky Mountain Tech Trifecta

I gave a presentation and demonstration about SQL Server 2008 Database Mirroring at the Rocky Mountain Tech Trifecta on February 21, 2009 at Metro State University in Denver. This demo sets up a synchronous mirror on the AdventureWorks database.

I used the custom connection colors feature in SSMS 2008 to set green for the default instance, red for the mirror instance, and blue for the witness instance, to keep from connecting to the wrong instance during the demonstration.

I had three instances of SQL Server 2008 Developer Edition installed on a Windows Server 2003 R2 virtual machine, but I could have done this with three instances installed on the host machine. The first three scripts that I used for the demonstration are shown below:

-- Glenn Berry
-- http://glennberrysqlperformance.spaces.live.com/
-- Rocky Mountain Tech Trifecta
-- Feb 21, 2009

-- Three instances of SQL Server 2008 Developer Edition CU3(Build 1787)on one virtual machine or test server
-- TECHTRIFECTA
-- TECHTRIFECTA\SQLMirror
-- TECHTRIFECTA\SQLWitness

-- Special Directory Structure on a Virtual Machine (don't do this on a production server!)
-- Default (Principal) instance directories
-- C:\SQLData            SQL Data files
-- C:\SQLLogs            SQL Log files
-- C:\SQLBackups        SQL Backups
-- C:\TempDB            TempDB files for default instance

-- Mirror instance directories
-- C:\SQLMirrorData        SQL Data files
-- C:\SQLMirrorLogs        SQL Log files
-- C:\SQLMirrorBackups    SQL Backups
-- C:\SQLMirrorTempDB    TempDB files for mirror instance

-- Witness instance directories
-- Just use the default directories during SQL Server setup


-- Prepare the mirror database
-- Make sure "mirror" server instance is on same build and edition of SQL Server 2008
-- Make sure you have identical drive letters and directory paths for SQL data and log files


-- Switch to "Principal" server instance (green) *****************************
USE [master]
GO

-- Make sure database is in Full recovery model
SELECT name,recovery_model_desc
FROM sys.databases
WHERE name = 'AdventureWorks'

-- Change to Full Recovery model if necessary
ALTER DATABASE AdventureWorks SET RECOVERY FULL WITH NO_WAIT


-- Full Compressed backup to SQLMirrorBackups directory
BACKUP DATABASE [AdventureWorks] TO  DISK = N'C:\SQLMirrorBackups\AdventureWorksFullCompressed.bak' 
WITH  DESCRIPTION = N'Full Compressed Backup', NOFORMAT, INIT,  
NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

-- Transaction Log Backup to SQLMirrorBackups directory
BACKUP LOG [AdventureWorks] TO  DISK = N'C:\SQLMirrorBackups\AdventureWorksTranCompressed.trn' 
WITH  DESCRIPTION = N'Compressed Transaction Log Backup', NOFORMAT, INIT,  
NAME = N'AdventureWorks-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO


-- Switch to "Mirror" server instance (red) *******************

-- Full Restore AdventureWorks database with NO RECOVERY
RESTORE DATABASE [AdventureWorks] 
FROM  DISK = N'C:\SQLMirrorBackups\AdventureWorksFullCompressed.bak' 
WITH  FILE = 1,  MOVE N'AdventureWorks_Data' TO N'C:\SQLMirrorData\AdventureWorks.mdf',  
MOVE N'AdventureWorks_Log' TO N'C:\SQLMirrorLogs\AdventureWorks.ldf',  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

-- Restore at least one transaction log backup with NO RECOVERY
RESTORE LOG [AdventureWorks] 
FROM  DISK = N'C:\SQLMirrorBackups\AdventureWorksTranCompressed.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

-- Mirror database is "prepared", end of script 1 **********************************
-- Glenn Berry
-- http://glennberrysqlperformance.spaces.live.com/
-- Rocky Mountain Tech Trifecta
-- Feb 21, 2009

-- Create Mirroring Endpoints and grant permissions on each instance
-- All databases on an instance share the same mirroring endpoint
-- Don't drop the endpoint if you have other databases mirrored on that instance!

-- Switch Connection to Principal Instance (green) ********

--Drop Endpoint on Principal if it exists
IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Mirroring') 
DROP ENDPOINT [Mirroring]
GO
-- Create Endpoint on Principal (RC4 Encryption is deprecated. Use AES Encryption)
CREATE ENDPOINT [Mirroring] 
    AUTHORIZATION [TECHTRIFECTA\Administrator]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

-- Partners under same domain user
-- Create a login for the witness server instance,
-- which is running as TECHTRIFECTA\SQLServerService:
USE master;
GO
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TECHTRIFECTA\SQLServerService')
DROP LOGIN [TECHTRIFECTA\SQLServerService]
GO
CREATE LOGIN [TECHTRIFECTA\SQLServerService] FROM WINDOWS;
GO
-- Grant connect permissions on endpoint to login account of witness.
GRANT CONNECT ON ENDPOINT::Mirroring TO [TECHTRIFECTA\SQLServerService];
GO




-- Switch Connection to Mirror Instance (red) ********

--Drop Endpoint on Mirror if it exists
IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Mirroring') 
DROP ENDPOINT [Mirroring]
GO
-- Create Endpoint on Mirror (RC4 Encryption is deprecated. Use AES Encryption)
CREATE ENDPOINT [Mirroring] 
    AUTHORIZATION [TECHTRIFECTA\Administrator]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

-- Partners under same domain user
-- Create a login for the witness server instance,
-- which is running as TECHTRIFECTA\SQLServerService:
USE master;
GO
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TECHTRIFECTA\SQLServerService')
DROP LOGIN [TECHTRIFECTA\SQLServerService]
GO
CREATE LOGIN [TECHTRIFECTA\SQLServerService] FROM WINDOWS;
GO
-- Grant connect permissions on endpoint to login account of witness.
GRANT CONNECT ON ENDPOINT::Mirroring TO [TECHTRIFECTA\SQLServerService];
GO



-- Switch Connection to Witness Instance (blue) ********

--Drop Endpoint on Witness if it exists
IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Mirroring') 
DROP ENDPOINT [Mirroring]
GO
-- Create Endpoint on Witness (RC4 Encryption is deprecated. Use AES Encryption)
CREATE ENDPOINT [Mirroring] 
    AUTHORIZATION [TECHTRIFECTA\Administrator]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

-- Create a login for the partner server instances,
-- which are both running as TECHTRIFECTA\SQLServerService:
USE master;
GO
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TECHTRIFECTA\SQLServerService')
DROP LOGIN [TECHTRIFECTA\SQLServerService]
GO
CREATE LOGIN [TECHTRIFECTA\SQLServerService] FROM WINDOWS;
GO
--Grant connect permissions on endpoint to login account of partners.
GRANT CONNECT ON ENDPOINT::Mirroring TO [TECHTRIFECTA\SQLServerService];
GO

-- Though considerably faster than AES, RC4 is a relatively weak algorithm, 
-- while AES is a relatively strong algorithm. 
-- Therefore, Microsoft recommends that you use the AES algorithm
-- The SSMS GUI uses the RC4 algorithm by default
-- Glenn Berry
-- http://glennberrysqlperformance.spaces.live.com/
-- Rocky Mountain Tech Trifecta
-- Feb 21, 2009
-- Actually create the Mirroring partnership
-- Note use of different port numbers because all instances are on the same machine
-- We are also not using FQDN since this server is not part of a domain

-- Switch to Mirror instance (red) ******************************
-- On Mirror Instance, set the Principal
ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://techtrifecta:5022'
GO



-- Switch to Principal instance (green) ******************************
-- On Principal Instance, set the mirror
ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://techtrifecta:5023'
GO

-- On Principal Instance, set the witness
ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://techtrifecta:5024'
GO



-- Check Mirroring Status
SELECT DB_NAME(database_id) AS 'database_name', mirroring_safety_level_desc, 
mirroring_witness_name, mirroring_witness_state_desc 
FROM sys.database_mirroring
WHERE database_id > 4

-- Check Mirroring Connections
SELECT *
FROM sys.dm_db_mirroring_connections

-- Manual Failover
-- Switch to current Principal instance  *************************
ALTER DATABASE AdventureWorks SET PARTNER FAILOVER; 


-- Automatic Failover
-- Stop SQL Server Service on Default Instance

-- Switch to current Principal instance (green) *************************
-- On Principal Instance, turn off Transaction Safety (Asynchronous Mode)
ALTER DATABASE AdventureWorks SET PARTNER SAFETY OFF

-- On Principal Instance, turn on Transaction Safety (Synchronous Mode)
ALTER DATABASE AdventureWorks SET PARTNER SAFETY FULL


-- Check Mirroring Status
SELECT DB_NAME(database_id) AS 'database_name', mirroring_safety_level_desc, 
mirroring_witness_name, mirroring_witness_state_desc 
FROM sys.database_mirroring
WHERE database_id > 4


-- On either partner, remove the Witness
ALTER DATABASE AdventureWorks SET WITNESS OFF


-- Add the Witness back
-- Switch to Witness instance (blue) *************************
-- On Witness instance, check to see if endpoint exists
SELECT role_desc, state_desc
FROM sys.database_mirroring_endpoints

-- Switch to Principal instance (green) *************************
ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://techtrifecta:5024'
GO

USE AdventureWorks
GO

-- Get cached SPs by execution count (to look at workload)
SELECT TOP (10) qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY qs.execution_count DESC


-- Remove Mirroring *******
-- Switch to Principal instance (green) *************************
ALTER DATABASE AdventureWorks SET PARTNER OFF;


-- Switch to Mirror instance (red) ******************************
-- Recover Mirror
RESTORE DATABASE AdventureWorks WITH RECOVERY;

-- Drop database and delete files
DROP DATABASE AdventureWorks;


-- Force Service with possible data loss
-- Switch to Mirror instance *************************
ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Technorati Tags:

This entry was posted in SQL Server 2008. Bookmark the permalink.

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