MS SQL 2005 2008 2012 Mirroring

How to mirror MSSQL Database

=== PRINCIPAL ===
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
GO


---
USE MASTER
GO
CREATE CERTIFICATE Principal_Cert
WITH SUBJECT = 'Principal Certificate';
GO
---

CREATE ENDPOINT End_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE Principal_Cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO
---


USE MASTER
GO
BACKUP CERTIFICATE Principal_Cert
TO FILE = 'C:\Cert\Principal_Cert.cer'
GO




=======MIRROR=========
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
GO


---
USE MASTER
GO
CREATE CERTIFICATE Mirror_Cert
WITH SUBJECT = 'Mirror Certificate';
GO
---
CREATE ENDPOINT End_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE Mirror_Cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO
---


USE MASTER
GO
BACKUP CERTIFICATE Mirror_Cert
TO FILE = 'C:\Cert\Mirror_Cert.cer'
GO




===== COPY Mirror_Cert.cer to Principal ====
================ PRINCIPAL ----
USE MASTER
GO
CREATE LOGIN Mirror_login WITH PASSWORD = 'password'
GO
---
USE MASTER
GO
CREATE USER Mirror_User FOR LOGIN Mirror_login
GO
----
USE MASTER
GO
CREATE CERTIFICATE Mirror_Cert
AUTHORIZATION Mirror_User
FROM FILE = 'C:\Cert\Mirror_Cert.cer'
GO
----
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Mirror_login]
GO
---


===== COPY Principal_Cert.cer to Mirror ====
============== MIRROR ==============
USE MASTER
GO
CREATE LOGIN Principal_login WITH PASSWORD = 'Password'
GO
---
USE MASTER
GO
CREATE USER Principal_User FOR LOGIN Principal_login
GO
----
USE MASTER
GO
CREATE CERTIFICATE Principal_Cert
AUTHORIZATION Principal_User
FROM FILE = 'C:\Cert\Principal_Cert.cer'
GO
----
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Principal_login]
GO
---






=== PRINCIPAL ====
USE MASTER
GO
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'C:\certificate\AdventureWorks_Full_01012011.bak'
GO
BACKUP LOG AdventureWorks2008R2
TO DISK = 'C:\certificate\AdventureWorks_Log_01012011.trn'
GO
----


=== MIRROR ===
USE MASTER
GO
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'C:\certificate\AdventureWorks_Full_01012011.bak'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks2008R2
FROM DISK = 'C:\certificate\AdventureWorks_Log_01012011.trn'
WITH NORECOVERY
GO
---
ALTER DATABASE AdventureWorks2008R2
SET PARTNER = 'TCP://principal:5022'
GO


======== pRINCIPAL==
ALTER DATABASE AdventureWorks2008R2
SET PARTNER = 'TCP://mirror:5023'
GO