SQL Note--Mirror-白红宇

SQL Note--Mirror
阅读量：6941 次
发布时间：2019-06-27
本文共 12819 字，大约阅读时间需要 42 分钟。
--=========================================================================================================--在主服务器上运行以下脚本--=========================================================================================================USE master;GO--=========================================================================================================--创建Master keyIF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##')BEGINCREATE MASTER KEY ENCRYPTION BY PASSWORD ='master@key'ENDGOIF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)BEGINALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYENDGO--=========================================================================================================--创建和备份证书IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name='PrincipalServerCert' )BEGINCREATE CERTIFICATE PrincipalServerCert WITH SUBJECT ='Principal Server Cert'ENDGOBACKUP CERTIFICATE PrincipalServerCert TO FILE='D:\PrincipalServerCert.cer'GO--=========================================================================================================--创建镜像专用的端点，并使用证书加密--同一个实例上只能存在一个镜像端点IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)BEGINCREATE ENDPOINT DBMirrorEndPointSTATE=STARTED ASTCP(LISTENER_PORT=5022)FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE PrincipalServerCert, ENCRYPTION=REQUIRED,ROLE=ALL)END --=========================================================================================================--在镜像服务器上运行以下脚本--=========================================================================================================USE master;GO--=========================================================================================================--创建Master keyIF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##')BEGINCREATE MASTER KEY ENCRYPTION BY PASSWORD ='master@key'ENDGOIF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)BEGINALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYENDGO--=========================================================================================================--创建和备份证书IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name='MirrorServerCert' )BEGINCREATE CERTIFICATE MirrorServerCert WITH SUBJECT ='Mirror Server Cert'ENDGOBACKUP CERTIFICATE MirrorServerCert TO FILE='D:\MirrorServerCert.cer'GO--=========================================================================================================--创建镜像专用的端点，并使用证书加密--同一个实例上只能存在一个镜像端点IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)BEGINCREATE ENDPOINT DBMirrorEndPointSTATE=STARTED ASTCP(LISTENER_PORT=5023)FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE MirrorServerCert, ENCRYPTION=REQUIRED,ROLE=ALL)END GO--=========================================================================================================--在见证服务器上运行以下脚本--=========================================================================================================USE master;GO--=========================================================================================================--创建Master keyIF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##')BEGINCREATE MASTER KEY ENCRYPTION BY PASSWORD ='master@key'ENDGOIF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)BEGINALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYENDGO--=========================================================================================================--创建和备份证书IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name='WitnessServerCert' )BEGINCREATE CERTIFICATE WitnessServerCert WITH SUBJECT ='Witness Server Cert'ENDGOBACKUP CERTIFICATE WitnessServerCert TO FILE='D:\WitnessServerCert.cer'GO--=========================================================================================================--创建镜像专用的端点，并使用证书加密--同一个实例上只能存在一个镜像端点IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)BEGINCREATE ENDPOINT DBMirrorEndPointSTATE=STARTED ASTCP(LISTENER_PORT=5024)FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE WitnessServerCert, ENCRYPTION=REQUIRED,ROLE=ALL)END GO--=========================================================================================================--在主服务器上运行以下脚本--=========================================================================================================USE master;GO--=========================================================================================================--使用镜像服务器备份出来的证书来为镜像服务器连接创建用户和证书，并使用证书为新用户授权IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='MirrorServerLogin')BEGINCREATE LOGIN MirrorServerLogin WITH PASSWORD ='Auto@sql'ENDGOIF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'MirrorServerUser')BEGINCREATE USER MirrorServerUser FOR LOGIN MirrorServerLoginENDGOIF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'MirrorServerCert')BEGINCREATE CERTIFICATE MirrorServerCert AUTHORIZATION MirrorServerUser FROM FILE='D:\MirrorServerCert.cer'ENDGOGRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerLoginGO--=========================================================================================================--使用见证服务器备份出来的证书来为见证服务器连接创建用户和证书，并使用证书为新用户授权USE master;GOIF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='WitnessServerLogin')BEGINCREATE LOGIN WitnessServerLogin WITH PASSWORD ='Auto@sql'ENDGOIF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'WitnessServerUser')BEGINCREATE USER WitnessServerUser FOR LOGIN WitnessServerLoginENDGOIF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]='WitnessServerCert')BEGINCREATE CERTIFICATE WitnessServerCert AUTHORIZATION WitnessServerUser FROM FILE='D:\WitnessServerCert.cer'ENDGOGRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO WitnessServerLoginGO--=========================================================================================================--在镜像服务器上运行以下脚本--=========================================================================================================--=========================================================================================================--使用主服务器备份出来的证书来为主服务器连接创建用户和证书，并使用证书为新用户授权USE master;GOIF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='PrincipalServerLogin')BEGINCREATE LOGIN PrincipalServerLogin WITH PASSWORD ='Auto@sql'ENDGOCREATE USER PrincipalServerUser FOR LOGIN PrincipalServerLoginGOIF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]='PrincipalServerCert')BEGINCREATE CERTIFICATE PrincipalServerCert AUTHORIZATION PrincipalServerUser FROM FILE='D:\PrincipalServerCert.cer'ENDGOGRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerLogin--=========================================================================================================--使用见证服务器备份出来的证书来为见证服务器连接创建用户和证书，并使用证书为新用户授权USE master;GOIF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='WitnessServerLogin')BEGINCREATE LOGIN WitnessServerLogin WITH PASSWORD ='Auto@sql'ENDGOIF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'WitnessServerUser')BEGINCREATE USER WitnessServerUser FOR LOGIN WitnessServerLoginENDGOIF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]='WitnessServerCert')BEGINCREATE CERTIFICATE WitnessServerCert AUTHORIZATION WitnessServerUser FROM FILE='D:\WitnessServerCert.cer'ENDGOGRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO WitnessServerLogin --=========================================================================================================--在见证服务器上运行以下脚本--=========================================================================================================--=========================================================================================================--使用主服务器备份出来的证书来为主服务器连接创建用户和证书，并使用证书为新用户授权USE master;GOIF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='PrincipalServerLogin')BEGINCREATE LOGIN PrincipalServerLogin WITH PASSWORD ='Auto@sql'ENDGOCREATE USER PrincipalServerUser FOR LOGIN PrincipalServerLoginGOIF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]='PrincipalServerCert')BEGINCREATE CERTIFICATE PrincipalServerCert AUTHORIZATION PrincipalServerUser FROM FILE='D:\PrincipalServerCert.cer'ENDGOGRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerLoginGO--=========================================================================================================--使用镜像服务器备份出来的证书来为镜像服务器连接创建用户和证书，并使用证书为新用户授权USE master;GOIF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='MirrorServerLogin')BEGINCREATE LOGIN MirrorServerLogin WITH PASSWORD ='Auto@sql'ENDGOIF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'MirrorServerUser')BEGINCREATE USER MirrorServerUser FOR LOGIN MirrorServerLoginENDGOIF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'MirrorServerCert')BEGINCREATE CERTIFICATE MirrorServerCert AUTHORIZATION MirrorServerUser FROM FILE='D:\MirrorServerCert.cer'ENDGOGRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerLoginGO--=========================================================================================================--在主服务器上运行以下脚本--=========================================================================================================--=========================================================================================================--修改数据库恢复模式-USE [master]GOALTER DATABASE [Demo1] SET RECOVERY FULL WITH NO_WAITGO--=========================================================================================================--备份数据库BACKUP DATABASE [Demo1] TO  DISK = N'D:\Demo1.bak' WITH NOFORMAT, NOINIT,NAME = N'Demo1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO--=========================================================================================================--在镜像服务器上运行以下脚本--=========================================================================================================--=========================================================================================================--使用NORECOVERY来恢复数据库USE [master]GORESTORE DATABASE [Demo1] FROM  DISK = N'D:\Demo1.bak' WITH  FILE = 1,  MOVE N'Demo1' TO N'D:\Mirror\Demo1.mdf', MOVE N'Demo1_log' TO N'D:\Mirror\Demo1_Log.LDF',  NORECOVERY,  NOUNLOAD,  STATS = 10GO--=========================================================================================================--设置PARTNERUSE [master]GOALTER DATABASE Demo1 SET PARTNER='TCP://192.168.1.102:5022'--=========================================================================================================--在主服务器上运行以下脚本--=========================================================================================================--=========================================================================================================--设置PARTNERUSE [master]GOALTER DATABASE Demo1 SET PARTNER='TCP://192.168.1.102:5023'GO--=========================================================================================================--其他脚本--=========================================================================================================--=========================================================================================================--添加见证服务器USE [master]GOALTER DATABASE Demo1 SET WITNESS='TCP://192.168.1.102:5024'GO--=========================================================================================================-移除见证服务器USE [master]GOALTER DATABASE Demo1 SET WITNESS OFFGO--=========================================================================================================--修改高性能USE [master]GOALTER DATABASE Demo1 SET PARTNER SAFETY OFFGO--=========================================================================================================--修改为高安全USE [master]GOALTER DATABASE Demo1 SET PARTNER SAFETY FULLGO--=========================================================================================================--在高安全下手动转移镜像（在主服务器上）USE [master]GOALTER DATABASE Demo1 SET PARTNER FAILOVERGO--=========================================================================================================--在高性能下手动转移镜像（在从服务器上）,此时主服务器已停止USE [master]GOALTER DATABASE Demo1 SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSSGO--=========================================================================================================--此时主服务器停止后又重启时，主服务器会被挂起，使用以下SQL来恢复镜像USE [master]GOALTER DATABASE Demo1 SET PARTNER RESUMEGO--=========================================================================================================--关闭镜像USE [master]GOALTER DATABASE Demo1 SET PARTNER OFFGO--=========================================================================================================--将数据库从还原状态转化成正常模式USE [master]GORESTORE DATABASE Demo1 WITH RECOVERYGO
转载地址：http://uennl.baihongyu.com/
你可能感兴趣的文章
Lintcode: Binary Tree Serialization (Serialization and Deserialization Of Binary Tree)
查看>>
[设计模式] 9 装饰者模式 Decorator
查看>>
beetle.express针对websocket的高性能处理
查看>>
bat批处理设置Java JDK系统环境变量文件
查看>>
Javascript的setTimeOut()和setInterval()的定时器用法
查看>>