Close

April 27, 2014

Database Mirroring

Prefered long term solution for this suggested by Microsoft is using AlwaysOn High Availablilty.

T-SQL Code for mirroring [note the port numbers have to be different.

/****** Object: Endpoint [mirroring] on Server1 Script Date: 4/27/2014 3:32:37 AM ******/
CREATE ENDPOINT [mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7575, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

/****** Object: Endpoint [mirroring] on Server2 Script Date: 4/27/2014 3:31:17 AM ******/
CREATE ENDPOINT [mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7595, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

/****** Object: Endpoint [mirroring] on Witness instance Script Date: 4/27/2014 3:30:20 AM ******/
CREATE ENDPOINT [mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7585, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

m1

m2

m3

m4

m5

m6

m7

m8

m9

On the Mirror Server, restore a backup with NO RECOVERY

USE [master]
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N’D:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLBackupAdventureWorks2012.bak’
WITH FILE = 5,
MOVE N’AdventureWorks2012_Data’
TO N’C:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLDATAAdventureWorks2012_Data.mdf’,
MOVE N’AdventureWorks2012_Log’
TO N’D:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLDataAdventureWorks2012_log.ldf’,
NORECOVERY,
NOUNLOAD,
STATS = 5

GO

Leave a Reply