--=========================================================================================================
--在主服务器上运行以下脚本
--=========================================================================================================
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
