最近公司在进行数据库容灾和备份上的工作,记录关于镜像配置的总结
步骤一:备份和恢复数据库 -- 以完整和事务日志备份主数据库 -- 从服务器上先后还原完整备份和事务日志备份,注意选择不对数据库执行任何操作(WITH NORECOVERY) 步骤二:主服务器A配置证书 -- 创建主密钥 CREATE master KEY ENCRYPTION BY password = 'PASSWORDA' ; GO -- 创建证书 CREATE CERTIFICATE Cert_A WITH subject = 'A证书' ; GO -- 创建端点 IF EXISTS( SELECT 1 FROM sys. endpoints WHERE name= '主镜像' ) DROP endpoint [主镜像] GO CREATE endpoint [主镜像] AUTHORIZATION [sa] STATE= started AS TCP (listener_port = 5022 , listener_IP = ALL) FOR data_mirroring (ROLE = partner, authentication = CERTIFICATE Cert_A, ENCRYPTION=required algorithm RC4) GO -- 导出证书 BACKUP CERTIFICATE Cert_A TO FILE = 'H:MyCertCert_A.cer' 步骤三:从服务器B配置证书 -- 创建主密钥 CREATE master KEY ENCRYPTION BY password = 'PASSWORDB' ; GO -- 创建证书 CREATE CERTIFICATE Cert_B WITH subject = 'B证书' ; GO -- 创建端点 IF EXISTS( SELECT 1 FROM sys. endpoints WHERE name= '从镜像' ) DROP endpoint [从镜像] GO CREATE endpoint [从镜像] AUTHORIZATION [sa] STATE= started AS TCP (listener_port = 5022 , listener_IP = ALL) FOR data_mirroring (ROLE = partner, authentication = CERTIFICATE Cert_B, ENCRYPTION=required algorithm RC4) GO -- 导出证书 BACKUP CERTIFICATE Cert_CY08 TO FILE = 'C:Cert_B.cer' 步骤四:交换证书 步骤五:主服务器A创建登陆用户 USE master ; -- 创建登录名 CREATE LOGIN B_login WITH PASSWORD = 'PASSWORDA'; GO -- 创建用户 CREATE USER CY08_user FOR LOGIN B_login; GO -- 还原B 证书 CREATE CERTIFICATE Cert_B AUTHORIZATION B_user FROM FILE = 'H:MyCertCert_B.cer' GO -- 授权端点给登陆名 GRANT CONNECT ON ENDPOINT::[ 主镜像] TO [B_login] ; 步骤六:从服务器A创建登陆用户 USE master ; -- 创建登录名 CREATE LOGIN A_login WITH PASSWORD = 'PASSWORDA'; GO -- 创建用户 CREATE USER A_user FOR LOGIN A_login; GO -- 还原B证书 CREATE CERTIFICATE Cert_A AUTHORIZATION A_user FROM FILE = 'C:Cert_A.cer' GO -- 授权端点给登陆名 GRANT CONNECT ON ENDPOINT::[从镜像] TO [A_login] ; 步骤七:开始镜像 -- 从数据库执行,连接镜像 USE master GO ALTER DATABASE CYEMS SET PARTNER = 'TCP://192.168.0.A:5022'; GO -- 主数据库执行 USE master GO ALTER DATABASE CYEMS SET PARTNER = 'TCP://192.168.0.B:5022'; GO 相关脚本 -- 停止镜像,之后可重新连接;配置过程中发生问题可用 ALTER DATABASE CYEMS SET PARTNER OFF; -- 高安全模式,OFF为异步模式 ALTER DATABASE CYEMS SET SAFETY FULL; --切换主备;可用于数据库升级维护 ALTER DATABASE [DBName] SET PARTNER FAILOVER; --当主库故障无法连接时,强制将镜像库设置为主库,允许数据丢失 ALTERDATABASE[DBName]SETPARTNERFORCE_SERVICE_ALLOW_DATA_LOSS; --恢复镜像 ALTER DATABASE [DBName] SET PARTNER RESUME; --设置数据库联机 ALTER DATABASE [DBName] SET ONLINE; -- 在主备执行:创建数据库镜像监视器作业 use msdb ; exec sys .sp_dbmmonitoraddmonitoring --exec sys.sp_dbmmonitorhelpmonitoring --exec sys.sp_dbmmonitorresults DBName,0,0 --exec sys.sp_dbmmonitorchangemonitoring --exec sys.sp_dbmmonitordropmonitoring
这种镜像备份的方式用于多台服务器同时运行上,在数据库的还原和备份上实现了短时间内的及时备份,在数据库文件隔天备份或者月度周度备份上又将维度扩大了,中小型数据库中有较好的使用。