SQL Server2008 R2镜像
一． 前期准备
1.主机备份数据库
1. 完整备份
2. 事务日志备份
2.镜相机还原数据库
1.完全还原
2.事务日志还原
二．主库操作1
1.创建主密钥
USE master;
go
create MASTER KEY ENCRYPTION BY PASSWORD='copypassword';
SELECT name, is_master_key_encrypted_by_server FROM sys.databases WHERE name='master' ;
l is_master_key_encrypted_by_server=1时创建成功
2.数据库中添加证书
IF EXISTS(select * from sys.certificates WHERE name='HOST_C_CERT')
DROP CERTIFICATE HOST_C_CERT;
CREATE CERTIFICATE HOST_C_CERT
WITH SUBJECT = 'HOST_C_CERTIFICATE',
Expiry_Date = '2888-12-12';
SELECT * FROM sys.certificates WHERE name='HOST_C_CERT';
l HOST_C_CERT证书名称
3.备份证书
backup certificate HOST_C_CERT to file='F:\backup\HOST_C_CERT.cer';
l 备份证书路径F:\backup\HOST_C_CERT.cer
4.创建数据库端点
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring ;
CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP (
LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE HOST_C_CERT ,
ENCRYPTION = REQUIRED ALGORITHM RC4 ,
ROLE = all );
select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring';
l 端点名称Endpoint_Mirroring
三．镜像库操作1
1.创建主密钥
USE master;
go
create MASTER KEY ENCRYPTION BY PASSWORD='copypassword';
SELECT name, is_master_key_encrypted_by_server FROM sys.databases WHERE name='master' ;
l is_master_key_encrypted_by_server=1时创建成功
2.数据库中添加证书
IF EXISTS(select * from sys.certificates WHERE name='HOST_A_CERT')
DROP CERTIFICATE HOST_A_CERT;
CREATE CERTIFICATE HOST_A_CERT
WITH SUBJECT = 'HOST_A_CERTIFICATE',
Expiry_Date = '2888-12-12';
SELECT * FROM sys.certificates WHERE name='HOST_A_CERT';
l HOST_A_CERT证书名称
3.备份证书
backup certificate HOST_A_CERT to file='D:\share\HOST_A_CERT.cer';
l 备份证书路径D:\share\HOST_A_CERT.cer
4.创建数据库端点
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring ;
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 RC4 ,
ROLE = all );
select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring';
l 端点名称Endpoint_Mirroring
四．主库操作2
1.创建账户HOST_A_LOGIN
创建登录账户HOST_A_LOGIN连接镜像库
IF EXISTS(select * from sys.sql_logins WHERE name='HOST_A_LOGIN')
DROP LOGIN HOST_A_LOGIN ;
create LOGIN HOST_A_LOGIN WITH PASSWORD = '111111';
select * from sys.sql_logins WHERE name='HOST_A_LOGIN';
2.创建用户HOST_A_USER
给账户HOST_A_LOGIN创建HOST_A_USER
IF EXISTS(select * from sys.database_principals WHERE name='HOST_A_USER')
DROP USER HOST_A_USER ;
create USER HOST_A_USER FOR LOGIN HOST_A_LOGIN;
select * from sys.database_principals WHERE name='HOST_A_USER';
3.证书HOST_A_CERT
给用户HOST_A_USER授权访问镜像库证书HOST_A_CERT
IF EXISTS(select * from sys.certificates WHERE name='HOST_A_CERT')
DROP CERTIFICATE HOST_A_CERT;
create CERTIFICATE HOST_A_CERT
AUTHORIZATION HOST_A_USER
FROM FILE = 'F:\backup\HOST_A_CERT.cer';
select * from sys.certificates WHERE name='HOST_A_CERT';
l 镜像库证F:\backup\HOST_A_CERT.cer;
4.授权给HOST_A_LOGIN
镜像端点Endpoint_Mirroring授权给登录账户HOST_A_LOGIN
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_A_LOGIN;
五．镜像库操作2
1.创建账户HOST_C_LOGIN
IF EXISTS(select * from sys.sql_logins WHERE name='HOST_C_LOGIN')
DROP LOGIN HOST_C_LOGIN ;
create LOGIN HOST_C_LOGIN WITH PASSWORD = '111111';
select * from sys.sql_logins WHERE name='HOST_C_LOGIN';
2.创建用户HOST_C_USER
IF EXISTS(select * from sys.database_principals WHERE name='HOST_C_USER')
DROP USER HOST_C_USER ;
create USER HOST_C_USER FOR LOGIN HOST_C_LOGIN;
select * from sys.database_principals WHERE name='HOST_C_USER';
3.证书HOST_C_CERT
IF EXISTS(select * from sys.certificates WHERE name='HOST_C_CERT')
DROP CERTIFICATE HOST_C_CERT;
create CERTIFICATE HOST_C_CERT
AUTHORIZATION HOST_C_USER
FROM FILE = 'D:\share\HOST_C_CERT.cer';
select * from sys.certificates WHERE name='HOST_C_CERT';
l 镜像库证D:\share\HOST_C_CERT.cer;
4.授权给HOST_C_LOGIN
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_C_LOGIN;
六．设置伙伴
1.给镜像库设置伙伴
use master
go
alter database test set partner='TCP://199.0.15.102:5022';
2.给主库设置伙伴
use master
go
alter database test set partner='TCP://199.0.125.12:5022';
l 必须先在镜像库上设置伙伴
l 主库IP：199.0.15.102，镜像库IP：199.0.125.12
七．故障转移
主库--任务--镜像--故障切换
八、关闭镜像
1.从库关闭镜像
alter database test set partner off;
2.可访问状态
RESTORE database test with recovery;
