SQL Server 2008 R2 データベースミラーリング導入マニュアル（デュアルコンピュータ） SQL Server 2014 も適用可能です。

--host executes.
USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456'; 
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , 
START_DATE = '2012-08-02', 
EXPIRY_DATE = '2099-08-02'; 

-- The standby machine executes.

USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456'; 
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 
START_DATE = '2012-08-02', 
EXPIRY_DATE = '2099-08-02'; 

-- Host executes. 

CREATE ENDPOINT Endpoint_Mirroring 

STATE = STARTED 

AS 

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 

FOR 

DATABASE_MIRRORING 

( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); 

 

-- The standby machine executes.

CREATE ENDPOINT Endpoint_Mirroring 

STATE = STARTED 

AS 

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 

FOR 

DATABASE_MIRRORING 

( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

-- Host executes.

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\SQLBackup\HOST_A_cert.cer'; 


-- The backup machine executes.

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\SQLBackup\HOST_B_cert.cer';

-- Host execution.
CREATE LOGIN HOST_B_login WITH PASSWORD = '123456'; 
CREATE USER HOST_B_user FOR LOGIN HOST_B_login; 
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'C:\SQLBackup\HOST_B_cert.cer'; 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]; 
 
-- The standby machine executes.
CREATE LOGIN HOST_A_login WITH PASSWORD = '123456'; 
CREATE USER HOST_A_user FOR LOGIN HOST_A_login; 
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'C:\SQLBackup\HOST_A_cert.cer'; 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; 

-- Execute the following statement in the standby machine.

ALTER DATABASE shishan SET PARTNER = 'TCP://192.168.1.155:5022';

Note: shishan is the database name, which needs to be modified according to the actual. 192.168.1.155 is the host IP address, which needs to be modified according to the actual.

--Host executes.

ALTER DATABASE shishan SET PARTNER = 'TCP://192.168.1.156:5022'; 

-- switch between master and standby 
use master;
alter database testdb set partner failover;
 
-- Forced switchover of the standby
use master;
alter database testdb set partner force_service_allow_data_loss;
 
--restore mirror
use master;
alter database testdb set partner resume;
 
 
--un-witness server
ALTER DATABASE testdb SET WITNESS OFF ;
 
--unmirror
ALTER DATABASE testdb SET PARTNER OFF;
 
--set mirror database restore to normal
RESTORE DATABASE testdb WITH RECOVERY;