Sqlserver2008R2配置数据库镜像之我的经验总结

-- 主机备份
USE master
GO

BACKUP DATABASE [TestSync] TO DISK = N'D:\SqlserverMirrorCer\TestSync.bak'
WITH FORMAT, INIT, NAME = N'TestSync-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO


BACKUP LOG [TestSync] TO DISK = N'D:\SqlserverMirrorCer\TestSync.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestSync-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO 





-- 镜像恢复
USE master
GO
RESTORE DATABASE [TestSync] FROM DISK = N'D:\SqlserverMirrorCer\TestSync.bak'
WITH FILE = 1,
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO

RESTORE LOG [TestSync] FROM DISK = N'D:\SqlserverMirrorCer\TestSync.bak'
WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10
GO

------------------------------------------------------------------------
--============================ 主机上执行 ============================--
------------------------------------------------------------------------
USE master
GO

--创建证书，并备份
IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)    
    OPEN MASTER KEY DECRYPTION BY PASSWORD='PWD_DBMirror986252588';
ELSE
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='PWD_DBMirror986252588';
GO

IF EXISTS(select * from sys.certificates WHERE name='Cert_Host')
    DROP CERTIFICATE Cert_Host;
GO
CREATE CERTIFICATE Cert_Host

WITH SUBJECT=N'Cert_Host Certificate',START_DATE='20120405',EXPIRY_DATE='20990405';

BACKUP CERTIFICATE Cert_Host TO FILE=N'D:\SqlserverMirrorCer\Cert_Host.cer';
GO

--创建镜像端口
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Host')
    DROP ENDPOINT Endpoint_Host
GO
CREATE ENDPOINT Endpoint_Host
STATE = STARTED
AS TCP
(
    LISTENER_PORT=5022,
    LISTENER_IP=ALL
)

FOR DATABASE_MIRRORING
(
    AUTHENTICATION=CERTIFICATE Cert_Host,
    ENCRYPTION=REQUIRED ALGORITHM AES,
    ROLE=PARTNER
)

GO


------------------------------------------------------------------------
--============================ 镜像机上执行 ============================--
------------------------------------------------------------------------
USE master
GO

--创建证书，并备份
IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)    
    OPEN MASTER KEY DECRYPTION BY PASSWORD='PWD_DBMirror986252588';
ELSE
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='PWD_DBMirror986252588';
GO

IF EXISTS(select * from sys.certificates WHERE name='Cert_Mirror')
    DROP CERTIFICATE Cert_Mirror;
GO
CREATE CERTIFICATE Cert_Mirror

WITH SUBJECT=N'Cert_Mirror Certificate',START_DATE='20120405',EXPIRY_DATE='20990405';

BACKUP CERTIFICATE Cert_Mirror TO FILE=N'D:\SqlserverMirrorCer\Cert_Mirror.cer';
GO

--创建镜像端口
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirror')
    DROP ENDPOINT Endpoint_Mirror
GO
CREATE ENDPOINT Endpoint_Mirror
STATE = STARTED
AS TCP
(
    LISTENER_PORT=5022,
    LISTENER_IP=ALL
)

FOR DATABASE_MIRRORING
(
    AUTHENTICATION=CERTIFICATE Cert_Mirror,
    ENCRYPTION=REQUIRED ALGORITHM AES,
    ROLE=PARTNER
)

GO



------------------------------------------------------------------------
--============================ 见证机上执行 ============================--
------------------------------------------------------------------------
USE master
GO

--创建证书，并备份
IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)    
    OPEN MASTER KEY DECRYPTION BY PASSWORD='PWD_DBMirror986252588';
ELSE
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='PWD_DBMirror986252588';
GO

IF EXISTS(select * from sys.certificates WHERE name='Cert_Witness')
    DROP CERTIFICATE Cert_Witness;
GO

CREATE CERTIFICATE Cert_Witness
    WITH SUBJECT=N'Cert_Witness Certificate',START_DATE='20120405',EXPIRY_DATE='20990405';

BACKUP CERTIFICATE Cert_Witness TO FILE=N'D:\SqlserverMirrorCer\Cert_Witness.cer';
GO

--创建镜像端口
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Witness')
    DROP ENDPOINT Endpoint_Witness
GO
CREATE ENDPOINT Endpoint_Witness
STATE = STARTED
AS TCP
(
    LISTENER_PORT=5022,
    LISTENER_IP=ALL
)

FOR DATABASE_MIRRORING
(
    AUTHENTICATION=CERTIFICATE Cert_Witness,
    ENCRYPTION=REQUIRED ALGORITHM AES,
    ROLE=WITNESS
)

GO

-- 把上面三个步骤中备份的证书COPY到每台机，确保每台机都有此三个证书。

------------------------------------------------------------------------
--============================ 主机上执行 ============================--
------------------------------------------------------------------------
USE master
GO

--为镜像机访问主机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Mirror WITH PASSWORD=N'PWD_DBMirror986252588';
CREATE USER User_For_Mirror FOR LOGIN Login_For_Mirror;
CREATE CERTIFICATE Cert_For_Mirror AUTHORIZATION User_For_Mirror FROM FILE=N'D:\SqlserverMirrorCer\Cert_Mirror.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Host TO Login_For_Mirror;
GO

--为见证机访问主机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Witness WITH PASSWORD=N'PWD_DBMirror986252588';
CREATE USER User_For_Witness FOR LOGIN Login_For_Witness;
CREATE CERTIFICATE Cert_For_Witness AUTHORIZATION User_For_Witness FROM FILE=N'D:\SqlserverMirrorCer\Cert_Witness.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Host TO Login_For_Witness;

GO

------------------------------------------------------------------------
--============================ 镜像机上执行 ============================--
------------------------------------------------------------------------
USE master
GO

--为主机访问镜像机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Host WITH PASSWORD=N'PWD_DBMirror986252588';
CREATE USER User_For_Host FOR LOGIN Login_For_Host;
CREATE CERTIFICATE Cert_For_Host AUTHORIZATION User_For_Host FROM FILE =N'D:\SqlserverMirrorCer\Cert_Host.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirror TO Login_For_Host;
GO

--为见证机访问镜像机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Witness WITH PASSWORD=N'PWD_DBMirror986252588';
CREATE USER User_For_Witness FOR LOGIN Login_For_Witness;
CREATE CERTIFICATE Cert_For_Witness AUTHORIZATION User_For_Witness FROM FILE =N'D:\SqlserverMirrorCer\Cert_Witness.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirror TO Login_For_Witness;
GO


------------------------------------------------------------------------
--============================ 见证机上执行 ============================--
------------------------------------------------------------------------
USE master
GO

--为主机访问见证机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Host WITH PASSWORD=N'PWD_DBMirror986252588';
CREATE USER User_For_Host FOR LOGIN Login_For_Host;
CREATE CERTIFICATE Cert_For_Host AUTHORIZATION User_For_Host FROM FILE=N'D:\SqlserverMirrorCer\Cert_Host.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Witness TO Login_For_Host;
GO

--为镜像机访问见证机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Mirror WITH PASSWORD=N'PWD_DBMirror986252588';
CREATE USER User_For_Mirror FOR LOGIN Login_For_Mirror;
CREATE CERTIFICATE Cert_For_Mirror AUTHORIZATION User_For_Mirror FROM FILE=N'D:\SqlserverMirrorCer\Cert_Mirror.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Witness TO Login_For_Mirror;
GO

-- 镜像机上执行：
-- 建立 主机 合作
ALTER DATABASE [TestSync] SET PARTNER =N'TCP://SqlHost.jike.cn:5022'; 

-- 主机上执行：
-- 建立 镜像机 合作
ALTER DATABASE [TestSync] SET PARTNER=N'TCP://SqlMirror.jike.cn:5022';
-- 建立 见证机 合作
ALTER DATABASE [TestSync] SET WITNESS=N'TCP://SqlWitness.jike.cn:5022';