Close

June 22, 2014

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

 

 

 

 

Leave a Reply