SQL Mirroring[Hot back up with Double machine]

Background:

It's fairly common for businesses to want to provide some high availability for their SQL Server databases, and one option is to have two SQL Server databases on separate machines with a SQL Server database mirrored. Microsoft provides mirroring out of the box in SQL Server 2005 and SQL Server 2008, and is a much cheaper alternative than going down the clustering/failover route, but does provide some protection. In mirroring, there is always one Principal database which serves the requests, and a standby Mirror that is always synchronizing. If the Principal database goes down, then the Mirror can be forced to become the Principal, and will then serve the requests. Once the original Principal is available again, it will become the new Mirror.

 

Restrictions on Backup and Restore During Mirroring

While a database mirroring session is active, the following restrictions apply:

• Backup and restore of the mirror database are not allowed.

• Backup of the principal database is allowed, but BACKUP LOG WITH NORECOVERY is not allowed.

• Restoring the principal database is not allowed. 

 

Script in Principal:

-- Step 1
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Password01!';
CREATE CERTIFICATE HOST_pri_cert WITH SUBJECT ='HOST_pri certificate' , 
START_DATE = '01/11/2014', EXPIRY_DATE = '01/11/2015';


-- Step 2
alter ENDPOINT Endpoint_Mirroring 
	STATE = STARTED 
	AS		TCP ( LISTENER_PORT=50221 , LISTENER_IP = ALL ) 
	FOR		DATABASE_MIRRORING 
	( AUTHENTICATION = CERTIFICATE HOST_pri_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )

--SELECT * FROM sys.endpoints;
-- drop ENDPOINT Mirroring_Endpoint2 
Go

-- Step 3
BACKUP CERTIFICATE HOST_pri_cert TO FILE = 'D:\Hot backup\HOST_pri_cert.cer';

-- Step 4
use master
Create LOGIN HOST_min_login WITH PASSWORD ='Password01!';
CREATE USER HOST_min_user FOR LOGIN HOST_min_login;
--drop CERTIFICATE HOST_min_cert
CREATE CERTIFICATE HOST_min_cert AUTHORIZATION HOST_min_user FROM FILE ='D:\Hot backup\HOST_min_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_min_login]; 

Create LOGIN HOST_pro_login WITH PASSWORD ='Password01!';
CREATE USER HOST_pro_user FOR LOGIN HOST_pro_login;
-- drop CERTIFICATE HOST_pro_cert
CREATE CERTIFICATE HOST_pro_cert AUTHORIZATION HOST_pro_user FROM FILE ='D:\Hot backup\HOST_pro_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pro_login];

-- select * from sys.database_principals
GO

--  Step 5
USE master;
ALTER DATABASE TestBackup01
SET RECOVERY FULL;
Go

--  Step 6
USE TestBackup01

--back up data
GO
BACKUP DATABASE [TestBackup01] TO  DISK = N'D:\Hot backup\testbackup01.bak' WITH NOFORMAT, NOINIT,  NAME = N'TestBackup01-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP LOG [TestBackup01] TO  DISK = N'D:\Hot backup\TestBackup01.trn' WITH NOFORMAT, NOINIT,  NAME = N'TestBackup01-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Script in Mirror:

-- Step 1
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Password01!';
CREATE CERTIFICATE HOST_min_cert WITH SUBJECT ='HOST_mirror certificate' , 
START_DATE = '01/11/2014', EXPIRY_DATE = '01/11/2015';

-- Step 2
Alter ENDPOINT Endpoint_Mirroring 
	STATE = STARTED 
	AS TCP ( LISTENER_PORT=50221 , LISTENER_IP = ALL ) 
	FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_min_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )
	-- drop endpoint Mirroring 
    -- SELECT * FROM sys.endpoints;
Go

-- Step 3
BACKUP CERTIFICATE HOST_min_cert TO FILE = 'D:\Hot backup\HOST_min_cert.cer';
--SELECT * FROM sys.certificates;
Go

-- Step 4
CREATE LOGIN HOST_pri_login WITH PASSWORD ='Password01!';
CREATE USER HOST_pri_user FOR LOGIN HOST_pri_login;
drop CERTIFICATE HOST_pri_cert
CREATE CERTIFICATE HOST_pri_cert AUTHORIZATION HOST_pri_user FROM FILE ='D:\Hot backup\HOST_pri_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pri_login];      

CREATE LOGIN HOST_pro_login WITH PASSWORD ='Password01!';
CREATE USER HOST_pro_user FOR LOGIN HOST_pro_login;
drop CERTIFICATE HOST_pro_cert
CREATE CERTIFICATE HOST_pro_cert AUTHORIZATION HOST_pro_user FROM FILE ='D:\Hot backup\HOST_pro_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pro_login];

 -- Step 5
create database TestBackup01USE master;
ALTER DATABASE TestBackup01SET RECOVERY FULL;
RESTORE DATABASE TestBackup01 FROM DISK = 'D:\Hot backup\backup_TestBackup01.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD;
RESTORE LOG TestBackup01 FROM DISK = 'D:\Hot backup\backup_TestBackup01.trn' WITH FILE = 2, NORECOVERY, NOUNLOAD

Script in Witness:

-- Step 1
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Password01!';
CREATE CERTIFICATE HOST_pro_cert WITH SUBJECT = 'HOST_Witness certificate' , 
START_DATE = '01/11/2014', EXPIRY_DATE = '01/11/2015';

-- Step 2
Create ENDPOINT Endpoint_Mirroring 
	STATE = STARTED 
	AS TCP ( LISTENER_PORT=50221 , LISTENER_IP = ALL ) 
	FOR DATABASE_MIRRORING 
    ( AUTHENTICATION = CERTIFICATE HOST_pro_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )
	select * from sys.endpoints
Go

-- Step 3
BACKUP CERTIFICATE HOST_pro_cert TO FILE = 'D:\Hot backup\HOST_pro_cert.cer';  --Note: Full Name

-- Step 4
CREATE LOGIN HOST_min_login WITH PASSWORD ='Password01!';
CREATE USER HOST_min_user FOR LOGIN HOST_min_login;
CREATE CERTIFICATE HOST_min_cert AUTHORIZATION HOST_min_user FROM FILE ='D:\Hot backup\HOST_min_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_min_login];   

CREATE LOGIN HOST_pri_login WITH PASSWORD ='Password01!';
CREATE USER HOST_pri_user FOR LOGIN HOST_pri_login;
CREATE CERTIFICATE HOST_pri_cert AUTHORIZATION HOST_pri_user FROM FILE ='D:\Hot backup\HOST_pri_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pri_login];
--select * from sys.database_principals
Go

-- Step 5
Create database TestBackup01

USE master;
ALTER DATABASE TestBackup01
SET RECOVERY FULL;
RESTORE DATABASE TestBackup01 FROM DISK = 'D:\Hot backup\backup_TestBackup01.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD;
RESTORE LOG TestBackup01 FROM DISK = 'D:\Hot backup\backup_TestBackup01.trn' WITH FILE = 2, NORECOVERY, NOUNLOAD
Go

 

Note:

1. copy the certificates over to the other server(Mirror, Witness and Principal).

2. After coping the certificates over I setup the logins.

3. Back up and restore db with log.

• Perform a full backup of the database on the Principal server.
• Perform a Transaction Log backup on the Principal server.
• Copy the backup file to the Mirror.
• Important: Do a restore of the full backup into a new step, but before doing the restore, go to Options, then ensure you check the No Recovery option! This is vital!
• Perform another restore of the Transaction Log, also with the No Recovery option. (This is important, otherwise you'll get an error when starting the mirror - See Gotchas section for explanation).