数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。
要进行数据库镜像所需的最小需求包括了两个不同的SQL Server运行环境。主服务器被称为“主机”,第二个服务器被称作“备机”。主机数据库就是你实际用着的数据库,镜像数据库就是你的数据库的备用拷贝。当事务写入你的基本服务器的时候,他们也同样被传送到并写入你的镜像数据库中。
除了基本和镜像之外,你还可以引入另一个可选的组件,名为“见证”。见证服务器是第三个SQL Server 2005/2008运行实例,它是在判断什么时候进行错误恢复的时候,用于基本和镜像之间内部交流。只有当你想实现自动错误恢复的时候用到这个选项。它实现了2比1投票的能力,当我的一个组件不可达,并因此需要进行错误恢复的时候。见证服务器只有在你想实现自动错误恢复的时候才需要用到。
实现互通可以使用域或证书来实现,非域环境下使用证书配置数据库镜像(如不需要见证服务器,省略掉主体服务器和镜像服务器建立见证服务器的登录、见证服务器的配置、主体服务器设置见证服务器三个环节即可)。
1、 环境
主机名
主机操作系统
主机SQL配置
主机IP
初始镜像角色
host_A
Windows Server 2003
Sql Server 2005 SP3
192.168.1.21
主体服务器
host_B
Windows Server 2003
Sql Server 2005 SP3
192.168.1.254
镜像服务器
host_C
Windows Server 2003
Sql Server 2005 SP3
192.168.1.22
见证服务器
2、 配置出站连接
主要工作是为服务器实例制作证书:
(一)为主机配置出站连接:
/******************************************************** 此脚本在主体服务器执行 ********************************************************/ USE master; --DROP MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OA@asp.net'; GO --为主体服务器 host_A 实例制作一个证书。 --DROP CERTIFICATE host_A_cert CREATE CERTIFICATE host_A_cert WITH SUBJECT = 'host_A certificate',START_DATE = '05/01/2010',EXPIRY_DATE = '01/01/2099'; GO --使用该证书为服务器实例创建一个镜像端点。 --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 AES , ROLE = PARTNER ); GO --备份host_A 证书,并将其复制到镜像服务器 host_B 和见证服务器 host_C BACKUP CERTIFICATE host_A_cert TO FILE = 'D:/dbmirror/host_A.cer'; GO
(二)为镜像服务器配置出站连接
/*********************************************** 在镜像服务器 host_B 执行此脚本 ***********************************************/ USE master; --DROP MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OA@asp.net'; GO --为镜像服务器 host_B 实例制作一个证书。 --DROP CERTIFICATE host_B_cert CREATE CERTIFICATE host_B_cert WITH SUBJECT = 'host_B certificate',START_DATE = '05/01/2010',EXPIRY_DATE = '01/01/2099'; GO --在 host_B 中为服务器实例创建一个镜像端点。 --DROP ENDPOINT Endpoint_Mirroring 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 = PARTNER ); GO --备份 host_B 证书,并将其复制到主体服务器 host_A 和见证服务器 host_C 上 BACKUP CERTIFICATE oa-canoe_cert TO FILE = 'D:/dbmirror/host_B.cer'; GO
(三)为见证服务器配置出站连接
/**************************** 见证服务器 host_C 执行 *****************************/ --ALTER DATABASE MirrorDB SET PARTNER OFF USE master; --DROP MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OA@asp.net'; GO --为此服务器实例制作一个证书。 --DROP CERTIFICATE host_C_cert CREATE CERTIFICATE host_C_cert WITH SUBJECT = 'host_C certificate',START_DATE = '05/01/2010',EXPIRY_DATE = '01/01/2099'; GO --使用该证书为服务器实例创建一个镜像端点。 --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 AES , ROLE = WITNESS ); GO --备份 host_C 证书,并将其复制到主体服务器 host_A 和镜像服务器 host_C 上 BACKUP CERTIFICATE oa-tjcfw_cert TO FILE = 'D:/dbmirror/host_C.cer'; GO
注意:
1. 证书必须指定过期时间,如果未指定,则将 EXPIRY_DATE 设置为 START_DATE 一年之后的日期。
2. 如果出现 “默认情况下,数据库镜像是被禁用的。当前提供的数据库镜像仅供评估使用,并不应使用于生产环境中。若要以评估为目的启用数据库镜像,请在启动过程中使用跟踪标志1400。有关跟踪标志和启动选项的详细信息,请参阅SQL Server 联机丛书”错误,请执行 DBCC TRACEON(1400,-1) 并安装SP3补丁即可
3、 配置入站连接
(一)为主体服务器配置入站连接:
--在 host_A 上为镜像服务器 host_B 创建一个登录名。 USE master; --DROP LOGIN host_B_login CREATE LOGIN host_B_login WITH PASSWORD = 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP USER host_B_user CREATE USER host_B_user FOR LOGIN host_B_login; GO --使证书与该用户关联。 --DROP CERTIFICATE host_B_cert CREATE CERTIFICATE host_B_cert AUTHORIZATION host_B_user FROM FILE = 'D:/dbmirror/host_B.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login]; GO --在主体服务器 host_A 上为见证服务器 host_C 创建一个登录名。 USE master; --DROP LOGIN host_C_login CREATE LOGIN host_C_login WITH PASSWORD = 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP USER host_C_user CREATE USER host_C_user FOR LOGIN host_C_login; GO --使证书与该用户关联。 --DROP CERTIFICATE host_C_cert CREATE CERTIFICATE host_C_cert AUTHORIZATION host_C_user FROM FILE = 'D:/dbmirror/host_C.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login]; GO --主体服务器上创建用于本机的登录名(MS可以省略) USE master; --DROP LOGIN host_A_login CREATE LOGIN host_A_login WITH PASSWORD= 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP USER host_A_user CREATE USER host_A_user FOR CERTIFICATE host_A_cert; GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login]; GO
(二)为镜像服务器配置入站连结:
--在镜像服务器 host_B 上为主体服务器 host_A 创建一个登录名。 USE master; --DROP LOGIN host_A_login CREATE LOGIN host_A_login WITH PASSWORD = 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP USER host_A_user CREATE USER host_A_user FOR LOGIN host_A_login; GO --使证书与该用户关联。 --DROP CERTIFICATE host_A_cert CREATE CERTIFICATE host_A_cert AUTHORIZATION host_A_user FROM FILE = 'D:/dbmirror/host_A.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login]; GO --在镜像服务器 host_B 上为见证服务器 host_C 创建一个登录名。 USE master; --DROP LOGIN host_C_login CREATE LOGIN host_C_login WITH PASSWORD = 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP USER host_C_user CREATE USER host_C_user FOR LOGIN host_C_login; GO --使证书与该用户关联。 --DROP CERTIFICATE host_C_cert CREATE CERTIFICATE host_C_cert AUTHORIZATION host_C_user FROM FILE = 'D:/dbmirror/host_C.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login]; GO --在镜像服务器 host_B 上为本机创建一个登录名(MS可以省略) USE master; --DROP LOGIN host_B_login CREATE LOGIN host_B_login WITH PASSWORD = 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP USER host_B_user CREATE USER host_B_user FOR CERTIFICATE host_B_cert; GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login]; GO
(三)为见证服务器配置入站连接:
--在见证服务器 host_C 上为主体服务器 host_A 创建一个登录名。 USE master; --DROP LOGIN host_A_login CREATE LOGIN host_A_login WITH PASSWORD = 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP USER host_A_user CREATE USER host_A_user FOR LOGIN host_A_login; GO --使证书与该用户关联。 --DROP CERTIFICATE host_A_cert CREATE CERTIFICATE host_A_cert AUTHORIZATION host_A_user FROM FILE = 'D:/dbmirror/host_A.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login]; GO --在见证服务器 host_C 上为镜像服务器 host_B 创建一个登录名。 USE master; --DROP LOGIN host_B_login CREATE LOGIN host_B_login WITH PASSWORD = 'OA@asp.net'; GO --创建一个使用该登录名的用户。 --DROP USER host_B_user CREATE USER host_B_user FOR LOGIN host_B_login; GO --使证书与该用户关联。 --DROP CERTIFICATE host_B_cert CREATE CERTIFICATE host_B_cert AUTHORIZATION host_B_user FROM FILE = 'D:/dbmirror/host_B.cer' GO --授予对远程镜像端点的登录名的 CONNECT 权限。 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login]; GO
4、 配置镜像数据库用户信息
数据库镜像的缺点之一是无法维护登录名,所以需要我们手工维护登录。
通常来说数据库都将会有若干个用户作为访问数据库的用户,并且数据库会有相应的登录名,但是在备机中缺少与之相对应的登录名,例如某业务系统使用 'uhost' 作为登录名访问数据库,但是在
备机中没有 'uhost' 这个登录名,因此一旦主备切换,业务系统就无法登录数据库了,这种情况称为"孤立用户".
在主数据库中执行如下语句
USE master; select sid,name from syslogins;
查找出相应的用户名和sid,例如:上述的 'uhost'
在备数据库中执行如下语句:
USE master; exec sp_addlogin @loginame = '
这里的 'LoginName' 即主数据库中的登录名,sid即是上述通过SQL语句查找出的sid。
例如,查询得到的sid和name如下所示。
sid name
---------------------------------- -----------------
50x074477739DCA0E499C29394FFFC4ADE4 uhost
则建立登录名的SQL语句:
USE master; exec sp_addlogin @loginame = 'uhost', @passwd = 'OA@asp.net', @sid = 0x074477739DCA0E499C29394FFFC4ADE4;
到此为止可以认为备机数据库的环境已经与主机同步了
在主体服务器 host_A 上备份数据库,镜像只支持完全恢复模式,在备份数据库之前检查恢复的模式,需要同时完整备份数据库和事务日志。
USE MASTER; GO BACKUP DATABASE DBMirror TO DISK = 'D:/dbmirror/DB.bak' WITH INIT GO BACKUP LOG Credit TO DISK = 'D:/dbmirror/DB_log.bak' WITH INIT GO
将DB.bak、DB_log.bak复制到镜像服务器 host_B 上,复制到 host_B 的镜像服务器实例上以NORECOVERNY选项进行恢复.
注意:这里数据库必须备份两次,一次全备份和一次事务日志备份,在镜像服务器上还原数据库必须使用NORECOVERNY选项进行恢复.
5、 配置镜像伙伴
(一)在镜像服务器上配置
--在镜像服务器 host 实例上设置其伙伴,使其伙伴成为主体服务器 ALTER DATABASE DBMirror SET PARTNER = 'TCP://192.168.1.21:5022'; GO
(二)在主体服务器上配置
--必须要在镜像数据库中先设置好伙伴后,才能在主体服务器执行 --在主体服务器 host_A 实例上设置镜像服务器为伙伴 ALTER DATABASE DBMirror SET PARTNER = 'TCP://192.168.1.254:5022'; GO
(三)在主体服务器上配置见证服务器
--设置见证服务器,在主体服务器 host_A 实例上设置见证服务器 ALTER DATABASE DBMirror SET WITNESS = 'TCP://192.168.1.22:5022'; GO
至此,镜像服务器配置成功,测试操作:
1、主备互换
--主机执行: USE master; ALTER DATABASE
2、主服务器Down掉,备机紧急启动并且开始服务
--备机执行: USE master; ALTER DATABASE
3、原来的主服务器恢复,可以继续工作,需要重新设定镜像
--备机执行: USE master; ALTER DATABASE
默认情况下,事务安全级别的设置为 FULL,即同步运行模式,而且SQL Server 2005 标准版只支持同步模式。
关闭事务安全可将会话切换到异步运行模式,该模式可使性能达到最佳。
USE master; ALTER DATABASE
手机扫一扫
移动阅读更方便
你可能感兴趣的文章