Database Mirroring with SQLCMD


Finally, I build my script to configure Database Mirroring in High Safety Mode. Earlier, I had written a post on configuring Database Mirroring in High Availability Mode. However, this time, I had written a script to automate the complete process using SQLCMD. My script is divided into four different parts. The first part of the script declare all the variables, used in the script, as shown below. Make sure you modify the parameters details as per your requirement.

---------------------------------------
/* Declare Variables for the scripts */
---------------------------------------
-- Declare the Principal Server Name
:SETVAR PSERVER "DBS02.SKG.LOCAL"
-- Declare the Mirror Server Name
:SETVAR MSERVER "DBS08.SKG.LOCAL"
-- Declare the Database Name
:SETVAR DBNAME "DBMIRROR"
-- Declare the Logical DataFile Name
:SETVAR LogicalDataName "DBMIRROR_DATA"
-- Declare the Logical LogFile Name
:SETVAR LogicalLogName "DBMIRROR_LOG"
-- Declare the Physical DataFile Location on Principal Server
:SETVAR PhysicalDataFileLoc "E:\SQL SERVER\USERDB\DATA\DBMIRROR_DATA.MDF"
-- Declare the Physical LogFile Location on Principal Server
:SETVAR PhysicalLogFileLoc "E:\SQL SERVER\USERDB\LOG\DBMIRROR_LOG.LDF"
-- Database Backup Location
:SETVAR BackupLoc "\\DBS02.SKG.LOCAL\Migration\"
-- Declare the Physical DataFile Location on Mirror Server
:SETVAR MPhysicalDataFileLoc "E:\SQL SERVER\USERDB\DATA\DBMIRROR_DATA.MDF"
-- Declare the Physical LogFile Location on Mirror Server
:SETVAR MPhysicalLogFileLoc "E:\SQL SERVER\USERDB\LOG\DBMIRROR_LOG.LDF"

The second part of the script create a database on the Principal instance. Then it creates a table in the database and add some data to it. A Full backup of the database is created followed by a transaction log backup. Then the database was restored on the Mirror instance from the backups with NoRecovery Mode.

The third portion of the script creates the Database Mirroring Endpoints on both the instances. Then it grant required permissions to the Database Engine Service Accounts. Make sure you modify the Endpoint Name and Service Accounts details according to your requirement. The last portion of the script start the Database Mirroring session in High Safety Mode, make sure you modify the Mirroring Endpoint Listener Port . The complete script is given below:

--------------------------------------------
/* Database Mirroring Script Using SQLCMD */
--------------------------------------------
-- Author: Sudeepta Ganguly (@skganguly)

-- Execute the script in SQLCMD Mode
--------------------------------------------


---------------------------------------
/* Declare Variables for the scripts */
---------------------------------------
-- Declare the Principal Server Name
:SETVAR PSERVER "DBS02.SKG.LOCAL"
-- Declare the Mirror Server Name
:SETVAR MSERVER "DBS08.SKG.LOCAL"
-- Declare the Database Name
:SETVAR DBNAME "DBMIRROR"
-- Declare the Logical DataFile Name
:SETVAR LogicalDataName "DBMIRROR_DATA"
-- Declare the Logical LogFile Name
:SETVAR LogicalLogName "DBMIRROR_LOG"
-- Declare the Physical DataFile Location on Principal Server
:SETVAR PhysicalDataFileLoc "E:\SQL SERVER\USERDB\DATA\DBMIRROR_DATA.MDF"
-- Declare the Physical LogFile Location on Principal Server
:SETVAR PhysicalLogFileLoc "E:\SQL SERVER\USERDB\LOG\DBMIRROR_LOG.LDF"
-- Database Backup Location
:SETVAR BackupLoc "\\DBS02.SKG.LOCAL\Migration\"
-- Declare the Physical DataFile Location on Mirror Server
:SETVAR MPhysicalDataFileLoc "E:\SQL SERVER\USERDB\DATA\DBMIRROR_DATA.MDF"
-- Declare the Physical LogFile Location on Mirror Server
:SETVAR MPhysicalLogFileLoc "E:\SQL SERVER\USERDB\LOG\DBMIRROR_LOG.LDF"


--------------------------------------------------------------------
/* Create & Configure The Database for Database Mirroring Session */
--------------------------------------------------------------------

-- Connect Principal Server Instance
:CONNECT $(PSERVER)
GO
-- Create the Demo Database
USE [master]
GO
IF DATABASEPROPERTYEX('$(DBNAME)', 'VERSION') > 1
	DROP DATABASE $(DBNAME);
GO
CREATE DATABASE $(DBNAME) ON PRIMARY
(NAME=$(LogicalDataName), FILENAME='$(PhysicalDataFileLoc)')
LOG ON
(NAME=$(LogicalLogName), FILENAME='$(PhysicalLogFileLoc)')
GO
-- Change the Recovery Model of the Database
ALTER DATABASE $(DBNAME) SET RECOVERY FULL;
GO
-- Create a Table and Add Base data
USE $(DBNAME)
GO
CREATE TABLE [DBO].[TBLTEST] (
TBLTESTID		INT IDENTITY(1,1) NOT NULL,
TBLTESTCHAR		CHAR(1) DEFAULT 'A' )
GO
USE $(DBNAME)
GO
INSERT INTO [DBO].[TBLTEST] DEFAULT VALUES;
GO 10
SELECT * FROM [DBMIRROR].[DBO].[TBLTEST];
GO
-- Create a Full Database Backup
USE [master]
GO
BACKUP DATABASE $(DBNAME)
	TO DISK = '$(BACKUPLOC)$(DBNAME).BAK'
GO
BACKUP LOG $(DBNAME)
	TO DISK = '$(BACKUPLOC)$(DBNAME).TRN'
GO


-- Connect to the Mirror Server Instance
:CONNECT $(MSERVER)
GO
-- Restore the Database from the Backup File
USE [master];
GO
RESTORE DATABASE $(DBNAME)
	FROM DISK = '$(BACKUPLOC)$(DBNAME).BAK'
	WITH MOVE '$(LogicalDataName)' TO '$(MPhysicalDataFileLoc)',
	     MOVE '$(LogicalLogName)' TO '$(MPhysicalLogFileLoc)',
		 REPLACE, NORECOVERY;
GO
RESTORE DATABASE $(DBNAME)
	FROM DISK = '$(BACKUPLOC)$(DBNAME).TRN'
	WITH NORECOVERY;
GO


-------------------------------------------------------------
/* Configure The EndPoints for Database Mirroring Session  */
-------------------------------------------------------------

:CONNECT $(PSERVER)
-- Create Database Mirroring Endpoint 
USE [master];
GO
CREATE ENDPOINT [MIRRORING]
STATE=STARTED
AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING 
(ROLE=PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = SUPPORTED);
GO
-- Create Login SKG\BLR_DE_DBS08 (Database Engine Service Account of Mirror Instance)
USE [master];
GO
CREATE LOGIN [SKG\BLR_DE_DBS08] FROM WINDOWS;
GO
-- Grant Connect Permission to the Endpoint
USE [master];
GO
GRANT CONNECT TO ENDPOINT::MIRRORING TO [SKG\BLR_DE_DBS08];
GO

:CONNECT $(MSERVER)
-- Create Database Mirroring Endpoint 
USE [master];
GO
CREATE ENDPOINT [MIRRORING]
STATE=STARTED
AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING 
(ROLE=PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = SUPPORTED);
GO
-- Create Login SKG\BLR_DE_DBS02 (Database Engine Service Account of Principal Instance)
USE [master];
GO
CREATE LOGIN [SKG\BLR_DE_DBS02] FROM WINDOWS;
GO
-- Grant Connect Permission to the Endpoint
USE [master];
GO
GRANT CONNECT TO ENDPOINT::MIRRORING TO [SKG\BLR_DE_DBS02];
GO


----------------------------------------------
/* Initiate the Database Mirroring Sessions */
----------------------------------------------

-- Add the Restored Database to Database Mirroring Session
:CONNECT $(MSERVER)
GO
USE [master];
GO
ALTER DATABASE $(DBNAME)
	SET PARTNER = 'TCP://$(PSERVER):7028';
GO

-- Add the Source Database to Database Mirroring Session
:CONNECT $(PSERVER)
USE [master];
GO
ALTER DATABASE $(DBNAME)
	SET PARTNER = 'TCP://$(MSERVER):7028';
GO


Happy Learning Smile

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Automation in SQL Server, Database Mirroring, How To, Just Learned, SQL Server, SQLCMD, T-SQL and tagged , , , . Bookmark the permalink.

2 Responses to Database Mirroring with SQLCMD

  1. blakhani says:

    Good one!

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