sql server 2008 r2 镜像配置操作

http://www.cnblogs.com/hoojo/category/276251.html  --sql server学习

http://www.cnblogs.com/wlb/archive/2009/04/10/1433125.html  --镜像配置操作

http://cnming.blog.51cto.com/324450/558734 --镜像配置操作

http://www.bugnotes.net/database/sqlserver2008_mirror.html  --镜像配置操作遇到的问题

http://www.cnblogs.com/downmoon/archive/2011/03/10/1979786.html  --sql server 2008 r2 主密钥

 

SQL 2008镜像配置
SQL 2008配置有证书镜像的步聚共有四步：

1. 配置出站连接：创建主秘钥、证书和端点，备份证书并拷贝到另一台服务器

2. 配置入站连接：分配登陆用户、赋予登陆用户connect本机端点的权限

3. 创建镜像数据：备主数据库，还原镜像数据库

4. 配置镜像伙伴关系：顺序：镜像----主体

5. 测试镜像：切换镜像

测试环境：
主数据库服务器： 192.168.1.222
镜像数据库服务器： 192.168.1.128

主数据库配置：

--============================================================================
--一．配置出站：配置完证书后，分别互存到各数据库服务器的对应路径，注意端口
--============================================================================
--出站
USE  master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'wwhf@2009'
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '05/08/2012'
GO

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 )
GO

BACKUP  CERTIFICATE HOST_A_cert TO  FILE  =  'c:\HOST_A_cert.cer'
GO

--=============================================================================
--二．入站镜像连接配置服务器：注意证书的路径，登录账户的命名
--============================================================================
--入站配置
CREATE  LOGIN HOST_B_login WITH  PASSWORD  =  'wwhf@2009'
GO
CREATE USER HOST_B_user FOR LOGIN HOST_B_login
GO
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'c:\HOST_B_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]
GO


--====================================================================
--三．配置数据库：备份包括数据库和日志
--=====================================================================
--配置主数据库

USE master
ALTER DATABASE test set recovery full
GO
backup database test to disk='c:\test.bak' with init
GO
backup log test to disk='c:\test_log.bak'
GO

--===========================================================================
--四．配置镜像：顺序：镜像----主体
--===========================================================================

--配置镜像

ALTER  DATABASE test SET  PARTNER  =  'TCP://192.168.1.128:5022'
GO

--删除镜像
ALTER DATABASE test SET PARTNER OFF
GO

镜像数据库配置：

--出站
USE  master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'wwhf@2009'
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',
START_DATE = '05/08/2012'
GO

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 )
GO

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

--入站配置
CREATE  LOGIN HOST_A_login WITH  PASSWORD  = 'wwhf@2009'
GO
CREATE USER HOST_A_user FOR LOGIN HOST_A_login
GO
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'c:\HOST_A_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]
GO

--还原镜像数据库
USE master
restore database test 
    from disk='c:\test.bak'
    with replace,NORECOVERY
GO
restore log test
    from disk='c:\test_log.bak'
    with file=1,NORECOVERY
GO

--配置镜像

ALTER  DATABASE test SET  PARTNER  =  'TCP://192.168.1.222:5022'
GO

手动故障转移代码：

 

总结：以上是在没有验证服务器的情况下，两台数据库做的镜像，经测试在强制切换后数据完整，符合镜像数据的要求