SQLMirroring[HotbackupwithDoublemachine](一)

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_minor 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
Alter 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]; 

-- select * from sys.certificates
SELECT * FROM SYS.DATABASE_PRINCIPALS

Alter 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

  --select * from sys.database_mirroring_endpoints 

Script in Mirror:

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

-- Step 2
Alter ENDPOINT Endpoint_Mirroring 
	STAT