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:
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.
