SQL Server 2005 镜像配置（三）
SQL Server 2005镜像高可用性模式（证书认证）
一、为主体数据库配置出站连接--创建数据库主密钥CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';GO --在MIR-A上为数据库实例创建证书
USE master;CREATE CERTIFICATE MIR_A_cert WITH SUBJECT = 'MIR_A certificate',START_DATE = '01/27/2008',EXPIRY_DATE = '01/01/2099'GO --在MIR-A上使用上面创建的证书为数据库实例创建镜像端点
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MIR_A_cert , ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = ALL );GO--备份MIR-A上的证书并拷贝到MIR-B，MIR-W上BACKUP CERTIFICATE MIR_A_cert TO FILE = 'C:\MIR_A_cert.cer';GO 二、为镜像数据库配置出站连接
--创建数据库主密钥
USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';GO--在MIR-B上为数据库实例创建证书CREATE CERTIFICATE MIR_B_cert WITH SUBJECT = 'MIR_B certificate for database mirroring',START_DATE = '01/27/2008',EXPIRY_DATE = '01/01/2099';GO--在MIR-B上使用上面创建的证书为数据库实例创建镜像端点CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5024, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MIR_B_cert, ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL );GO--备份MIR-B上的证书并拷贝到MIR-A，MIR-W上BACKUP CERTIFICATE MIR_B_cert TO FILE = 'C:\MIR_B_cert.cer';GO 三、为见证数据库配置出站连接
--创建数据库主密钥
USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';GO--在MIR-W上为数据库实例创建证书CREATE CERTIFICATE MIR_W_cert WITH SUBJECT = 'MIR_W certificate for database mirroring',START_DATE = '01/27/2008',EXPIRY_DATE = '01/01/2099';GO--在MIR-W上使用上面创建的证书为数据库实例创建镜像端点CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5024, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MIR_W_cert, ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL );GO--备份MIR-W上的证书并拷贝到MIR-A，MIR-B上BACKUP CERTIFICATE MIR_W_cert TO FILE = 'C:\MIR_W_cert.cer';GO 四、为主体数据库配置入站连接
--在MIR-A上为MIR-B创建登陆
USE master;CREATE LOGIN MIR_B_login WITH PASSWORD = '111111';GO--为以上登陆创建一个用户CREATE USER MIR_B_user FOR LOGIN MIR_B_login;GO--绑定证书到用户CREATE CERTIFICATE MIR_B_cert AUTHORIZATION MIR_B_user FROM FILE = 'C:\MIR_B_cert.cer'GO--在镜像端点上为登陆赋予CONNECT权限GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;GO --在MIR_A上为MIR_W创建登陆
USE master;CREATE LOGIN MIR_W_login WITH PASSWORD = '111111';GO--为以上登陆创建一个用户CREATE USER MIR_W_user FOR LOGIN MIR_W_login;GO--绑定证书到用户CREATE CERTIFICATE MIR_W_cert AUTHORIZATION MIR_W_user FROM FILE = 'C:\MIR_W_cert.cer'GO--在镜像端点上为登陆赋予CONNECT权限GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;GO 五、为镜像数据库配置入站连接
--在MIR-B上为MIR-A创建登陆
USE master;CREATE LOGIN MIR_A_login WITH PASSWORD = '111111';GO--为以上登陆创建一个用户CREATE USER MIR_A_user FOR LOGIN MIR_A_login;GO--绑定证书到用户CREATE CERTIFICATE MIR_A_cert AUTHORIZATION MIR_A_user FROM FILE = 'C:\MIR_A_cert.cer'GO--在镜像端点上为登陆赋予CONNECT权限GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;GO --在MIR-B上为MIR-W创建登陆
USE master;CREATE LOGIN MIR_W_login WITH PASSWORD = '111111';GO--为以上登陆创建一个用户CREATE USER MIR_W_user FOR LOGIN MIR_W_login;GO--绑定证书到用户CREATE CERTIFICATE MIR_W_cert AUTHORIZATION MIR_W_user FROM FILE = 'C:\MIR_W_cert.cer'GO--在镜像端点上为登陆赋予CONNECT权限GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;GO 六、为见证数据库配置入站连接 --在MIR-W上为MIR-A创建登陆USE master;CREATE LOGIN MIR_A_login WITH PASSWORD = '111111';GO--为以上登陆创建一个用户CREATE USER MIR_A_user FOR LOGIN MIR_A_login;GO--绑定证书到用户CREATE CERTIFICATE MIR_A_cert AUTHORIZATION MIR_A_user FROM FILE = 'C:\MIR_A_cert.cer'GO--在镜像端点上为登陆赋予CONNECT权限GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;GO --在MIR-W上为MIR-B创建登陆
USE master;CREATE LOGIN MIR_B_login WITH PASSWORD = '111111';GO--为以上登陆创建一个用户CREATE USER MIR_B_user FOR LOGIN MIR_B_login;GO--绑定证书到用户CREATE CERTIFICATE MIR_B_cert AUTHORIZATION MIR_B_user FROM FILE = 'C:\MIR_B_cert.cer'GO--在镜像端点上为登陆赋予CONNECT权限GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;GO 七、在MIR-A上备份数据库 --修改Northwind数据库使用完整恢复模式ALTER DATABASE Northwind SET RECOVERY FULL;GO--在 MIR_A 的主体服务器实例上备份数据库--通过安全方法，将 C:\Northwind.bak 复制到MIR_B上USE NorthwindBACKUP DATABASE Northwind TO DISK = 'C:\Northwind.bak' WITH FORMATGO 八、转到MIR-B上去恢复数据库 --在MIR-B的镜像服务器实例上还原数据库：RESTORE DATABASE Northwind FROM DISK = 'C:\Northwind.bak' WITH NORECOVERYGO 九、为镜像数据库配置镜像伙伴 --在 MIR-B 的镜像服务器实例上，将 MIR-A 上的服务器实例设置为伙伴ALTER DATABASE Northwind SET PARTNER = 'TCP://MIR-A:5024';GO 十、为主体数据库配置镜像伙伴和见证服务器 --在 MIR-A 的主体服务器实例上，将MIR-B上的服务器实例设置为伙伴ALTER DATABASE Northwind SET PARTNER = 'TCP://MIR-B:5024';GO--在 MIR-A 的主体服务器实例上，将 MIR-W 上的服务器实例设置为见证服务器ALTER DATABASE Northwind SET WITNESS = 'TCP://MIR-W:5024';GO 十一、配置数据库镜像事务安全级别 ALTER DATABASE Northwind SET SAFETY FULLGO十二、分别在MIR-A和MIR-B上查看数据库镜像的配置状态 -- 1.)通过Management studio 对象资源管理器，查看主体数据库、镜像数据库状态-- 2.)通过Management studio 对象资源管理器中的数据库属性查看状态-- 3.)通过系统目录视图查看数据库镜像配置情况 USE MASTER
GOSELECT * FROM sys.database_mirroring_endpoints;GOSELECT * FROM sys.database_mirroring WHERE database_id = (SELECT database_id FROM sys.databases WHERE name = 'Northwind')GO 十三、测试故障转移情况
页:
[1]