SQL Note--Mirror

复制代码
--=========================================================================================================
--在主服务器上运行以下脚本
--=========================================================================================================

USE master;
GO
--=========================================================================================================
--创建Master key
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='master@key'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)
BEGIN
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END
GO

--=========================================================================================================
--创建和备份证书
IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name='PrincipalServerCert' )
BEGIN
CREATE CERTIFICATE PrincipalServerCert WITH SUBJECT ='Principal Server Cert'
END
GO
BACKUP CERTIFICATE PrincipalServerCert TO FILE='D:\PrincipalServerCert.cer'
GO

--=========================================================================================================
--创建镜像专用的端点，并使用证书加密
--同一个实例上只能存在一个镜像端点
IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)
BEGIN
CREATE ENDPOINT DBMirrorEndPoint
STATE=STARTED AS
TCP(LISTENER_PORT=5022)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE PrincipalServerCert, ENCRYPTION=REQUIRED,ROLE=ALL)
END 













--=========================================================================================================
--在镜像服务器上运行以下脚本
--=========================================================================================================

USE master;
GO
--=========================================================================================================
--创建Master key
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='master@key'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)
BEGIN
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END
GO

--=========================================================================================================
--创建和备份证书
IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name='MirrorServerCert' )
BEGIN
CREATE CERTIFICATE MirrorServerCert WITH SUBJECT ='Mirror Server Cert'
END
GO
BACKUP CERTIFICATE MirrorServerCert TO FILE='D:\MirrorServerCert.cer'
GO

--=========================================================================================================
--创建镜像专用的端点，并使用证书加密
--同一个实例上只能存在一个镜像端点
IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)
BEGIN
CREATE ENDPOINT DBMirrorEndPoint
STATE=STARTED AS
TCP(LISTENER_PORT=5023)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE MirrorServerCert, ENCRYPTION=REQUIRED,ROLE=ALL)
END 
GO







--=========================================================================================================
--在见证服务器上运行以下脚本
--=========================================================================================================
USE master;
GO
--=========================================================================================================
--创建Master key
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='master@key'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)
BEGIN
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END
GO

--=========================================================================================================
--创建和备份证书
IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name='WitnessServerCert' )
BEGIN
CREATE CERTIFICATE WitnessServerCert WITH SUBJECT ='Witness Server Cert'
END
GO
BACKUP CERTIFICATE WitnessServerCert TO FILE='D:\WitnessServerCert.cer'
GO

--=========================================================================================================
--创建镜像专用的端点，并使用证书加密
--同一个实例上只能存在一个镜像端点
IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)
BEGIN
CREATE ENDPOINT DBMirrorEndPoint
STATE=STARTED AS
TCP(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')
BEGIN
CREATE LOGIN MirrorServerLogin WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'MirrorServerUser')
BEGIN
CREATE USER MirrorServerUser FOR LOGIN MirrorServerLogin
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'MirrorServerCert')
BEGIN
CREATE CERTIFICATE MirrorServerCert AUTHORIZATION MirrorServerUser FROM FILE='D:\MirrorServerCert.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerLogin
GO
--=========================================================================================================
--使用见证服务器备份出来的证书来为见证服务器连接创建用户和证书，并使用证书为新用户授权

USE master;
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='WitnessServerLogin')
BEGIN
CREATE LOGIN WitnessServerLogin WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'WitnessServerUser')
BEGIN
CREATE USER WitnessServerUser FOR LOGIN WitnessServerLogin
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]='WitnessServerCert')
BEGIN
CREATE CERTIFICATE WitnessServerCert AUTHORIZATION WitnessServerUser FROM FILE='D:\WitnessServerCert.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO WitnessServerLogin
GO






--=========================================================================================================
--在镜像服务器上运行以下脚本
--=========================================================================================================
--=========================================================================================================
--使用主服务器备份出来的证书来为主服务器连接创建用户和证书，并使用证书为新用户授权
USE master;
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='PrincipalServerLogin')
BEGIN
CREATE LOGIN PrincipalServerLogin WITH PASSWORD ='Auto@sql'
END
GO
CREATE USER PrincipalServerUser FOR LOGIN PrincipalServerLogin
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]='PrincipalServerCert')
BEGIN
CREATE CERTIFICATE PrincipalServerCert AUTHORIZATION PrincipalServerUser FROM FILE='D:\PrincipalServerCert.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerLogin
--=========================================================================================================
--使用见证服务器备份出来的证书来为见证服务器连接创建用户和证书，并使用证书为新用户授权

USE master;
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='WitnessServerLogin')
BEGIN
CREATE LOGIN WitnessServerLogin WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'WitnessServerUser')
BEGIN
CREATE USER WitnessServerUser FOR LOGIN WitnessServerLogin
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]='WitnessServerCert')
BEGIN
CREATE CERTIFICATE WitnessServerCert AUTHORIZATION WitnessServerUser FROM FILE='D:\WitnessServerCert.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO WitnessServerLogin 










--=========================================================================================================
--在见证服务器上运行以下脚本
--=========================================================================================================
--=========================================================================================================
--使用主服务器备份出来的证书来为主服务器连接创建用户和证书，并使用证书为新用户授权
USE master;
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='PrincipalServerLogin')
BEGIN
CREATE LOGIN PrincipalServerLogin WITH PASSWORD ='Auto@sql'
END
GO
CREATE USER PrincipalServerUser FOR LOGIN PrincipalServerLogin
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]='PrincipalServerCert')
BEGIN
CREATE CERTIFICATE PrincipalServerCert AUTHORIZATION PrincipalServerUser FROM FILE='D:\PrincipalServerCert.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerLogin
GO
--=========================================================================================================
--使用镜像服务器备份出来的证书来为镜像服务器连接创建用户和证书，并使用证书为新用户授权
USE master;
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='MirrorServerLogin')
BEGIN
CREATE LOGIN MirrorServerLogin WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'MirrorServerUser')
BEGIN
CREATE USER MirrorServerUser FOR LOGIN MirrorServerLogin
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'MirrorServerCert')
BEGIN
CREATE CERTIFICATE MirrorServerCert AUTHORIZATION MirrorServerUser FROM FILE='D:\MirrorServerCert.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerLogin
GO









--=========================================================================================================
--在主服务器上运行以下脚本
--=========================================================================================================
--=========================================================================================================
--修改数据库恢复模式
-USE [master]
GO
ALTER DATABASE [Demo1] SET RECOVERY FULL WITH NO_WAIT
GO
--=========================================================================================================
--备份数据库
BACKUP DATABASE [Demo1] 
TO  DISK = N'D:\Demo1.bak' WITH NOFORMAT, NOINIT,
NAME = N'Demo1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO








--=========================================================================================================
--在镜像服务器上运行以下脚本
--=========================================================================================================
--=========================================================================================================
--使用NORECOVERY来恢复数据库
USE [master]
GO
RESTORE 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 = 10
GO
--=========================================================================================================
--设置PARTNER
USE [master]
GO
ALTER DATABASE Demo1 SET PARTNER='TCP://192.168.1.102:5022'







--=========================================================================================================
--在主服务器上运行以下脚本
--=========================================================================================================
--=========================================================================================================
--设置PARTNER
USE [master]
GO
ALTER DATABASE Demo1 SET PARTNER='TCP://192.168.1.102:5023'
GO





--=========================================================================================================
--其他脚本
--=========================================================================================================

--=========================================================================================================
--添加见证服务器
USE [master]
GO
ALTER DATABASE Demo1 SET WITNESS='TCP://192.168.1.102:5024'
GO



--=========================================================================================================
-移除见证服务器
USE [master]
GO
ALTER DATABASE Demo1 SET WITNESS OFF
GO

--=========================================================================================================
--修改高性能
USE [master]
GO
ALTER DATABASE Demo1 SET PARTNER SAFETY OFF
GO

--=========================================================================================================
--修改为高安全
USE [master]
GO
ALTER DATABASE Demo1 SET PARTNER SAFETY FULL
GO


--=========================================================================================================
--在高安全下手动转移镜像（在主服务器上）
USE [master]
GO
ALTER DATABASE Demo1 SET PARTNER FAILOVER
GO



--=========================================================================================================
--在高性能下手动转移镜像（在从服务器上）,此时主服务器已停止
USE [master]
GO
ALTER DATABASE Demo1 SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO


--=========================================================================================================
--此时主服务器停止后又重启时，主服务器会被挂起，使用以下SQL来恢复镜像
USE [master]
GO
ALTER DATABASE Demo1 SET PARTNER RESUME
GO


--=========================================================================================================
--关闭镜像
USE [master]
GO
ALTER DATABASE Demo1 SET PARTNER OFF
GO


--=========================================================================================================
--将数据库从还原状态转化成正常模式
USE [master]
GO
RESTORE DATABASE Demo1 WITH RECOVERY
GO
复制代码

 

posted on   笑东风  阅读(285)  评论(0)  编辑  收藏  举报

编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 25岁的心里话
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现

导航

点击右上角即可分享
微信分享提示
SQL AI 助手