Configuring and Implementing Database Mirroring using SQLCMD

This article configures a database (uses AdventureWorksDW) for database mirroring – using the high availability configuration through a SQLCMD master script. SQLCMD replaces OSQL in SQL Server 2005 for better command-line automation by offering many new features including parameterization and ability to connect to different servers – all within one script. The true power can be seen by the fact that you can create complex multi-server scripts all within one master script. This script contains the steps necessary to setup mirroring through Transact-SQL. You will execute this script through the SQLCMD mode supported in SQL Server Management Studio. Please be sure to ONLY review the commands and their syntax, DO NOT EXECUTE any of the steps until the end – without the parameters set properly, this script will not execute correctly. This configuration is the High Availability configuration.

The below script (Database2Mirror.sql) is a SQLCMD script and by default, SQLCMD mode is not enabled. Enable SQLCMD command mode by choosing SQLCMD Mode from the Query menu (make sure to remember the icon shown in the drop-down menu since you could choose to use this instead). You can also toggle this mode off and on by using the SQLCMD mode icon in the toolbar. If you’re not sure of what an icon is, remember to use tool tips by hovering over an icon. Once you’ve enabled SQLCMD Mode, notice how your script changes. All SQLCMD lines are highlighted in gray.

-- Database2Mirror.sql

/*=============================================
File: Database2Mirror.sql

Summary: Setup the database for Mirroring - High Availability.

Date: August 2007

===============================================*/

:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
:SETVAR WitnessServer MSHEHADEHVPC\SQLExpress
:SETVAR Database2Mirror AdventureWorksDW
go

:ON ERROR EXIT
go

:CONNECT $(PrincipalServer)

-- Mirroring ONLY supports the FULL Recovery Model
ALTER DATABASE $(Database2Mirror)
SET RECOVERY FULL
go

USE $(Database2Mirror)
go

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5091)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT $(MirrorServer)

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5092)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT $(WitnessServer)

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5090)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO

:CONNECT $(PrincipalServer)

BACKUP DATABASE $(Database2Mirror)
TO DISK = 'C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak'
WITH INIT
GO

:CONNECT $(MirrorServer)

DECLARE @InstanceName sql_variant,
@InstanceDir sql_variant,
@SQLDataRoot nvarchar(512),
@ExecStr nvarchar(max)

SELECT @InstanceName = ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLServer')

EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
@InstanceName, @InstanceDir OUTPUT

SELECT @ExecStr = 'EXECUTE master.dbo.xp_regread '
+ '''HKEY_LOCAL_MACHINE'', '
+ '''SOFTWARE\Microsoft\Microsoft SQL Server\'
+ convert(varchar, @InstanceDir)
+ '\Setup'', ''SQLDataRoot'', @SQLDataRoot OUTPUT'

EXEC master.dbo.sp_executesql @ExecStr
, N'@SQLDataRoot nvarchar(512) OUTPUT'
, @SQLDataRoot OUTPUT

IF @SQLDataRoot IS NULL
BEGIN
RAISERROR ('Did not find the correct SQL Data Root Directory. Cannot proceed. Databases backed up but not yet restored.', 16, -1)
END

CREATE TABLE #BackupFileList
( LogicalName sysname NULL
, PhysicalName sysname NULL
, [Type] char(1)
, FileGroupName sysname NULL
, Size bigint
, MaxSize bigint
, FileId smallint
, CreateLSN numeric(25,0)
, DropLSN numeric(25,0)
, UniqueId uniqueidentifier
, ReadOnlyLSN numeric(25,0)
, ReadWriteLSN numeric(25,0)
, BackupSizeInBytes bigint
, SourceBlockSize bigint
, FileGroupId smallint
, LogGroupGUID uniqueidentifier
, DifferentialBaseLSN numeric(25,0)
, DifferentialBaseGUID uniqueidentifier
, IsReadOnly bit
, IsPresent bit
)

INSERT #BackupFileList
EXEC('LOAD FILELISTONLY FROM DISK = ''C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak''')

UPDATE #BackupFileList
SET PhysicalName
= @SQLDataRoot
+ N'\Data\'
+ REVERSE(SUBSTRING(REVERSE(PhysicalName)
, 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))

DECLARE @LogicalName sysname
, @PhysicalName sysname

DECLARE FileListCursor CURSOR FAST_FORWARD FOR
SELECT LogicalName, PhysicalName
FROM #BackupFileList

OPEN FileListCursor

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

SELECT @ExecStr = N'RESTORE DATABASE $(Database2Mirror)' +
N' FROM DISK = ''C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak''' +
N' WITH MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N''''

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName
+ ''' TO ''' + @PhysicalName + ''''
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END

-- NORECOVERY is required for Database Mirroring, replace is not.
-- Replace is used here solely to allow repetitive use of this script.
SELECT @ExecStr = @ExecStr + N' , NORECOVERY, REPLACE'

-- Useful for testing
-- Only return the string and then comment out the EXEC line below.
-- SELECT @ExecStr

EXEC (@ExecStr)

DEALLOCATE FileListCursor
GO

:CONNECT $(PrincipalServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns ONLINE
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go

:CONNECT $(MirrorServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORING
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go

:CONNECT $(MirrorServer)

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://MSHEHADEHVPC:5091'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5091'

GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://MSHEHADEHVPC:5092'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5092'

GO

ALTER DATABASE $(Database2Mirror)
SET WITNESS = 'TCP://MSHEHADEHVPC:5090'
-- SET WITNESS = 'TCP://Server.fully.qualified.dns.name:5090'

GO

SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')

--------------------------------------------------------------------
-- CLEAN UP SECTION
--------------------------------------------------------------------
-- This section will clean the database mirroring configuration
---- Begin cleanup...

-- If you want to clean the database mirroring configuration
-- just uncomment the following section

--:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
--:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
--:SETVAR WitnessServer MSHEHADEHVPC\SQLExpress
--:SETVAR Database2Mirror AdventureWorksDW
--go
--
--:CONNECT $(PrincipalServer)
--
--USE master
--GO
--
--SELECT * FROM sys.database_mirroring_endpoints
--SELECT * FROM sys.endpoints
--SELECT * FROM sys.tcp_endpoints
--GO
--
--ALTER DATABASE $(Database2Mirror)
-- SET PARTNER OFF
--GO
--
--DROP ENDPOINT Mirroring
--go
--
--DROP DATABASE $(Database2Mirror)
--go
--
--:CONNECT $(MirrorServer)
--
--DROP ENDPOINT Mirroring
--GO
--
--ALTER DATABASE $(Database2Mirror)
-- SET PARTNER OFF
--GO
--DROP DATABASE $(Database2Mirror)
--go
--
--:CONNECT $(WitnessServer)
--
--DROP ENDPOINT Mirroring
--go
--
------ End cleanup...