Tag: high availability

SQL Server Mirroring

This post will describe steps for setting up SQL Server 2012 mirroring.

Note: Microsoft recommends focusing on Always On for high availability as database mirroring will most likely be removed from MS SQL Server in future.

Setup – on my laptop [called K9] with 3 SQL instances, default [primary], MIR instance and the secondary/target is called SVR12:

m1

Backup on primary instance – database been used for this: AdventureWorks2012

BACKUP DATABASE [AdventureWorks2012]
TO DISK = N’d:AW2012.bak’ WITH NOFORMAT, NOINIT,
NAME = N’AdventureWorks2012-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Create same login on all 3 instances:

USE [master]
GO
CREATE LOGIN [k9sqluser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Restore the backup on SVR12 instance – the secondary/target instance:

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

GO

on Primary, right click on the database, tasks, mirroring

below shapshots show the next steps.

m3

m2

m4 m5 m6 m7 m8 m9 m10 m11 m12

 

 

 

 

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