sql2005镜像实现

admin管理员组

文章数量:1032555

sql2005镜像实现

-- ===========================================    -- 无论是主体服务器、镜像服务器, 还是见证服务器    -- 除特别说明外，均需要保证下面的操作在master库中执行    USE master    GO    -- ===========================================    --（1）  建立镜像主体数据库    -- 此操作主体服务器上执行    -- a. 建立测试数据库    CREATE DATABASE DB_Mirror    ON(    NAME = DB_Mirror_DATA,    FILENAME = N'C:\DB_Mirror.mdf'    )    LOG ON(    NAME = DB_Mirror_LOG,    FILENAME = N'C:\DB_Mirror.ldf'    )    ALTER DATABASE DB_Mirror SET    RECOVERY FULL    GO    -- b. 完全备份    BACKUP DATABASE DB_Mirror    TO DISK = N'C:\DB_Mirror.bak'    WITH FORMAT    GO    -- ===========================================    --（2）  初始化镜像主体数据库    -- 此操作镜像服务器上执行    -- 假设主体数据库的完全备份已经复制到 c:\DB_Mirror.bak    RESTORE DATABASE DB_Mirror    FROM DISK = N'C:\DB_Mirror.bak'    WITH REPLACE    , NORECOVERY    -- 如果镜像数据库文件要放在指定位置, 则启用下面的 Move 选项    -- , MOVE 'DB_Mirror_DATA' TO N'C:\DB_Mirror.mdf'    -- , MOVE 'DB_Mirror_LOG' TO N'C:\DB_Mirror.ldf'    GO    -- ===========================================    --（3）  主体服务器上的数据库镜像端点及身份验证用的证书    -- 此操作主体服务器上执行    -- a. 用于数据库镜像端点身份验证的证书    IF NOT EXISTS(  -- 使用数据库主密钥加密证书    SELECT * FROM sys.symmetric_keys    WHERE name = N'##MS_DatabaseMasterKey##')    CREATE MASTER KEY    ENCRYPTION BY PASSWORD = N'abc.123'    CREATE CERTIFICATE CT_Mirror_SrvA    WITH    SUBJECT = N'certificate for database mirror',    START_DATE = '19990101',    EXPIRY_DATE = '99991231'    GO    -- b. 备份证书, 以便在与此端点通信的另一端建立此证书    BACKUP CERTIFICATE CT_Mirror_SrvA    TO FILE = 'C:\CT_Mirror_SrvA.cer'    GO    -- c. 数据库镜像端点    CREATE ENDPOINT EDP_Mirror    STATE = STARTED    AS TCP(    LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口    LISTENER_IP = ALL)    -- 侦听的IP地址        FOR DATABASE_MIRRORING(    AUTHENTICATION = CERTIFICATE CT_Mirror_SrvA, -- 证书身份验证    ENCRYPTION = DISABLED,                      -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法    ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)    GO    -- ===========================================    --（4）  镜像服务器上的数据库镜像端点及身份验证用的证书    -- 此操作镜像服务器上执行    -- a. 用于数据库镜像端点身份验证的证书    IF NOT EXISTS(  -- 使用数据库主密钥加密证书    SELECT * FROM sys.symmetric_keys    WHERE name = N'##MS_DatabaseMasterKey##')    CREATE MASTER KEY    ENCRYPTION BY PASSWORD = N'abc.123'    CREATE CERTIFICATE CT_Mirror_SrvB    WITH    SUBJECT = N'certificate for database mirror',    START_DATE = '19990101',    EXPIRY_DATE = '99991231'    GO    -- b. 备份证书, 以便在与此端点通信的另一端建立此证书    BACKUP CERTIFICATE CT_Mirror_SrvB    TO FILE = 'C:\CT_Mirror_SrvB.cer'    GO    -- c. 数据库镜像端点    CREATE ENDPOINT EDP_Mirror    STATE = STARTED    AS TCP(    LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口    LISTENER_IP = ALL)    -- 侦听的IP地址        FOR DATABASE_MIRRORING(    AUTHENTICATION = CERTIFICATE CT_Mirror_SrvB, -- 证书身份验证    ENCRYPTION = DISABLED,                      -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法    ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)    GO    -- ===========================================    --（5）  在镜像服务器上完成主体服务器数据库镜像端点的传输安全模式配置    -- 此操作镜像服务器上执行    -- a. 建立主体服务器上的证书(假设主体服务器上备份的证书已经复制到 C:\CT_Mirror_SrvA.cer)    CREATE CERTIFICATE CT_Mirror_SrvA    FROM FILE = 'C:\CT_Mirror_SrvA.cer'    -- b. 建立登录    CREATE LOGIN LOGIN_Mirror_SrvA    FROM CERTIFICATE CT_Mirror_SrvA    -- c. 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvA    GO    -- ===========================================    --（6）  在主体服务器上完成镜像服务器数据库镜像端点的传输安全模式配置    -- 此操作主体服务器上执行    -- a. 建立主体服务器上的证书(假设镜像服务器上备份的证书已经复制到 C:\CT_Mirror_SrvB.cer)    CREATE CERTIFICATE CT_Mirror_SrvB    FROM FILE = 'C:\CT_Mirror_SrvB.cer'    -- b. 建立登录    CREATE LOGIN LOGIN_Mirror_SrvB    FROM CERTIFICATE CT_Mirror_SrvB    -- c. 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvB    GO    -- ===========================================    --（7）  在镜像服务器上启用数据库镜像    -- 此操作镜像服务器上执行    ALTER DATABASE DB_Mirror SET    PARTNER = 'TCP://SrvA:5022'    GO    -- ===========================================    --（8）  在主体服务器上启用数据库镜像(默认为高安全性模式,所以不用进行模式设置)    -- 此操作主体服务器上执行    ALTER DATABASE DB_Mirror SET    PARTNER = 'TCP://SrvB:5022'    GO    -- ===========================================    --（9）  配置见证服务器    -- 此操作在见证服务器上执行    -- a. 完成见证服务器上数据库镜像端点的传输安全模式配置    -- (a). 用于数据库镜像端点身份验证的证书    IF NOT EXISTS(  -- 使用数据库主密钥加密证书    SELECT * FROM sys.symmetric_keys    WHERE name = N'##MS_DatabaseMasterKey##')    CREATE MASTER KEY    ENCRYPTION BY PASSWORD = N'abc.123'    CREATE CERTIFICATE CT_Mirror_SrvWitness    WITH    SUBJECT = N'certificate for database mirror',    START_DATE = '19990101',    EXPIRY_DATE = '99991231'    GO    -- (b). 备份证书, 以便在与此端点通信的另一端建立此证书    BACKUP CERTIFICATE CT_Mirror_SrvWitness    TO FILE = 'C:\CT_Mirror_SrvWitness.cer'    GO    -- (c). 数据库镜像端点    CREATE ENDPOINT EDP_Mirror    STATE = STARTED    AS TCP(    LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口    LISTENER_IP = ALL)    -- 侦听的IP地址        FOR DATABASE_MIRRORING(    AUTHENTICATION = CERTIFICATE CT_Mirror_SrvWitness, -- 证书身份验证    ENCRYPTION = DISABLED,                            -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法    ROLE = ALL)                                        -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)    GO    -- b. 完成主体服务器上数据库镜像端点的传输安全模式配置    -- (a). 建立主体服务器上的证书(假设主体服务器上备份的证书已经复制到 C:\CT_Mirror_SrvA.cer)    CREATE CERTIFICATE CT_Mirror_SrvA    FROM FILE = 'C:\CT_Mirror_SrvA.cer'    -- (b). 建立登录    CREATE LOGIN LOGIN_Mirror_SrvA    FROM CERTIFICATE CT_Mirror_SrvA    -- (c). 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvA    GO    -- c. 完成镜像服务器上数据库镜像端点的传输安全模式配置    -- (a). 建立镜像服务器上的证书(假设镜像服务器上备份的证书已经复制到 C:\CT_Mirror_SrvB.cer)    CREATE CERTIFICATE CT_Mirror_SrvB    FROM FILE = 'C:\CT_Mirror_SrvB.cer'    -- (b). 建立登录    CREATE LOGIN LOGIN_Mirror_SrvB    FROM CERTIFICATE CT_Mirror_SrvB    -- (c). 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvB    GO    -- ===========================================    --（10）  在镜像服务器上完成见证服务器数据库镜像端点的传输安全模式配置    -- 此操作镜像服务器上执行    -- a. 建立见证服务器上的证书(假设见证服务器上备份的证书已经复制到 C:\CT_Mirror_SrvWitness.cer)    CREATE CERTIFICATE CT_Mirror_SrvWitness    FROM FILE = 'C:\CT_Mirror_SrvWitness.cer'    -- b. 建立登录    CREATE LOGIN LOGIN_Mirror_SrvWitness    FROM CERTIFICATE CT_Mirror_SrvWitness    -- c. 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvWitness    GO    -- ===========================================    --（11）  在主体服务器上完成见证服务器数据库镜像端点的传输安全模式配置    -- 此操作主体服务器上执行    -- a. 建立见证服务器上的证书(假设见证服务器上备份的证书已经复制到 C:\CT_Mirror_SrvWitness.cer)    CREATE CERTIFICATE CT_Mirror_SrvWitness    FROM FILE = 'C:\CT_Mirror_SrvWitness.cer'    -- b. 建立登录    CREATE LOGIN LOGIN_Mirror_SrvWitness    FROM CERTIFICATE CT_Mirror_SrvWitness    -- c. 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvWitness    GO    -- ===========================================    --（12）  在主体服务器上为数据库镜像启用见证服务器    -- 此操作主体服务器上执行    ALTER DATABASE DB_Mirror SET    WITNESS = 'TCP://SrvWitness:5022'    GO

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2014-09-18，如有侵权请联系 cloudcommunity@tencent 删除前往查看镜像配置数据库备份服务器

sql2005镜像实现

-- ===========================================    -- 无论是主体服务器、镜像服务器, 还是见证服务器    -- 除特别说明外，均需要保证下面的操作在master库中执行    USE master    GO    -- ===========================================    --（1）  建立镜像主体数据库    -- 此操作主体服务器上执行    -- a. 建立测试数据库    CREATE DATABASE DB_Mirror    ON(    NAME = DB_Mirror_DATA,    FILENAME = N'C:\DB_Mirror.mdf'    )    LOG ON(    NAME = DB_Mirror_LOG,    FILENAME = N'C:\DB_Mirror.ldf'    )    ALTER DATABASE DB_Mirror SET    RECOVERY FULL    GO    -- b. 完全备份    BACKUP DATABASE DB_Mirror    TO DISK = N'C:\DB_Mirror.bak'    WITH FORMAT    GO    -- ===========================================    --（2）  初始化镜像主体数据库    -- 此操作镜像服务器上执行    -- 假设主体数据库的完全备份已经复制到 c:\DB_Mirror.bak    RESTORE DATABASE DB_Mirror    FROM DISK = N'C:\DB_Mirror.bak'    WITH REPLACE    , NORECOVERY    -- 如果镜像数据库文件要放在指定位置, 则启用下面的 Move 选项    -- , MOVE 'DB_Mirror_DATA' TO N'C:\DB_Mirror.mdf'    -- , MOVE 'DB_Mirror_LOG' TO N'C:\DB_Mirror.ldf'    GO    -- ===========================================    --（3）  主体服务器上的数据库镜像端点及身份验证用的证书    -- 此操作主体服务器上执行    -- a. 用于数据库镜像端点身份验证的证书    IF NOT EXISTS(  -- 使用数据库主密钥加密证书    SELECT * FROM sys.symmetric_keys    WHERE name = N'##MS_DatabaseMasterKey##')    CREATE MASTER KEY    ENCRYPTION BY PASSWORD = N'abc.123'    CREATE CERTIFICATE CT_Mirror_SrvA    WITH    SUBJECT = N'certificate for database mirror',    START_DATE = '19990101',    EXPIRY_DATE = '99991231'    GO    -- b. 备份证书, 以便在与此端点通信的另一端建立此证书    BACKUP CERTIFICATE CT_Mirror_SrvA    TO FILE = 'C:\CT_Mirror_SrvA.cer'    GO    -- c. 数据库镜像端点    CREATE ENDPOINT EDP_Mirror    STATE = STARTED    AS TCP(    LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口    LISTENER_IP = ALL)    -- 侦听的IP地址        FOR DATABASE_MIRRORING(    AUTHENTICATION = CERTIFICATE CT_Mirror_SrvA, -- 证书身份验证    ENCRYPTION = DISABLED,                      -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法    ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)    GO    -- ===========================================    --（4）  镜像服务器上的数据库镜像端点及身份验证用的证书    -- 此操作镜像服务器上执行    -- a. 用于数据库镜像端点身份验证的证书    IF NOT EXISTS(  -- 使用数据库主密钥加密证书    SELECT * FROM sys.symmetric_keys    WHERE name = N'##MS_DatabaseMasterKey##')    CREATE MASTER KEY    ENCRYPTION BY PASSWORD = N'abc.123'    CREATE CERTIFICATE CT_Mirror_SrvB    WITH    SUBJECT = N'certificate for database mirror',    START_DATE = '19990101',    EXPIRY_DATE = '99991231'    GO    -- b. 备份证书, 以便在与此端点通信的另一端建立此证书    BACKUP CERTIFICATE CT_Mirror_SrvB    TO FILE = 'C:\CT_Mirror_SrvB.cer'    GO    -- c. 数据库镜像端点    CREATE ENDPOINT EDP_Mirror    STATE = STARTED    AS TCP(    LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口    LISTENER_IP = ALL)    -- 侦听的IP地址        FOR DATABASE_MIRRORING(    AUTHENTICATION = CERTIFICATE CT_Mirror_SrvB, -- 证书身份验证    ENCRYPTION = DISABLED,                      -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法    ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)    GO    -- ===========================================    --（5）  在镜像服务器上完成主体服务器数据库镜像端点的传输安全模式配置    -- 此操作镜像服务器上执行    -- a. 建立主体服务器上的证书(假设主体服务器上备份的证书已经复制到 C:\CT_Mirror_SrvA.cer)    CREATE CERTIFICATE CT_Mirror_SrvA    FROM FILE = 'C:\CT_Mirror_SrvA.cer'    -- b. 建立登录    CREATE LOGIN LOGIN_Mirror_SrvA    FROM CERTIFICATE CT_Mirror_SrvA    -- c. 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvA    GO    -- ===========================================    --（6）  在主体服务器上完成镜像服务器数据库镜像端点的传输安全模式配置    -- 此操作主体服务器上执行    -- a. 建立主体服务器上的证书(假设镜像服务器上备份的证书已经复制到 C:\CT_Mirror_SrvB.cer)    CREATE CERTIFICATE CT_Mirror_SrvB    FROM FILE = 'C:\CT_Mirror_SrvB.cer'    -- b. 建立登录    CREATE LOGIN LOGIN_Mirror_SrvB    FROM CERTIFICATE CT_Mirror_SrvB    -- c. 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvB    GO    -- ===========================================    --（7）  在镜像服务器上启用数据库镜像    -- 此操作镜像服务器上执行    ALTER DATABASE DB_Mirror SET    PARTNER = 'TCP://SrvA:5022'    GO    -- ===========================================    --（8）  在主体服务器上启用数据库镜像(默认为高安全性模式,所以不用进行模式设置)    -- 此操作主体服务器上执行    ALTER DATABASE DB_Mirror SET    PARTNER = 'TCP://SrvB:5022'    GO    -- ===========================================    --（9）  配置见证服务器    -- 此操作在见证服务器上执行    -- a. 完成见证服务器上数据库镜像端点的传输安全模式配置    -- (a). 用于数据库镜像端点身份验证的证书    IF NOT EXISTS(  -- 使用数据库主密钥加密证书    SELECT * FROM sys.symmetric_keys    WHERE name = N'##MS_DatabaseMasterKey##')    CREATE MASTER KEY    ENCRYPTION BY PASSWORD = N'abc.123'    CREATE CERTIFICATE CT_Mirror_SrvWitness    WITH    SUBJECT = N'certificate for database mirror',    START_DATE = '19990101',    EXPIRY_DATE = '99991231'    GO    -- (b). 备份证书, 以便在与此端点通信的另一端建立此证书    BACKUP CERTIFICATE CT_Mirror_SrvWitness    TO FILE = 'C:\CT_Mirror_SrvWitness.cer'    GO    -- (c). 数据库镜像端点    CREATE ENDPOINT EDP_Mirror    STATE = STARTED    AS TCP(    LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口    LISTENER_IP = ALL)    -- 侦听的IP地址        FOR DATABASE_MIRRORING(    AUTHENTICATION = CERTIFICATE CT_Mirror_SrvWitness, -- 证书身份验证    ENCRYPTION = DISABLED,                            -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法    ROLE = ALL)                                        -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)    GO    -- b. 完成主体服务器上数据库镜像端点的传输安全模式配置    -- (a). 建立主体服务器上的证书(假设主体服务器上备份的证书已经复制到 C:\CT_Mirror_SrvA.cer)    CREATE CERTIFICATE CT_Mirror_SrvA    FROM FILE = 'C:\CT_Mirror_SrvA.cer'    -- (b). 建立登录    CREATE LOGIN LOGIN_Mirror_SrvA    FROM CERTIFICATE CT_Mirror_SrvA    -- (c). 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvA    GO    -- c. 完成镜像服务器上数据库镜像端点的传输安全模式配置    -- (a). 建立镜像服务器上的证书(假设镜像服务器上备份的证书已经复制到 C:\CT_Mirror_SrvB.cer)    CREATE CERTIFICATE CT_Mirror_SrvB    FROM FILE = 'C:\CT_Mirror_SrvB.cer'    -- (b). 建立登录    CREATE LOGIN LOGIN_Mirror_SrvB    FROM CERTIFICATE CT_Mirror_SrvB    -- (c). 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvB    GO    -- ===========================================    --（10）  在镜像服务器上完成见证服务器数据库镜像端点的传输安全模式配置    -- 此操作镜像服务器上执行    -- a. 建立见证服务器上的证书(假设见证服务器上备份的证书已经复制到 C:\CT_Mirror_SrvWitness.cer)    CREATE CERTIFICATE CT_Mirror_SrvWitness    FROM FILE = 'C:\CT_Mirror_SrvWitness.cer'    -- b. 建立登录    CREATE LOGIN LOGIN_Mirror_SrvWitness    FROM CERTIFICATE CT_Mirror_SrvWitness    -- c. 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvWitness    GO    -- ===========================================    --（11）  在主体服务器上完成见证服务器数据库镜像端点的传输安全模式配置    -- 此操作主体服务器上执行    -- a. 建立见证服务器上的证书(假设见证服务器上备份的证书已经复制到 C:\CT_Mirror_SrvWitness.cer)    CREATE CERTIFICATE CT_Mirror_SrvWitness    FROM FILE = 'C:\CT_Mirror_SrvWitness.cer'    -- b. 建立登录    CREATE LOGIN LOGIN_Mirror_SrvWitness    FROM CERTIFICATE CT_Mirror_SrvWitness    -- c. 授予对数据库镜像端点的 connect 权限    GRANT CONNECT ON ENDPOINT::EDP_Mirror    TO LOGIN_Mirror_SrvWitness    GO    -- ===========================================    --（12）  在主体服务器上为数据库镜像启用见证服务器    -- 此操作主体服务器上执行    ALTER DATABASE DB_Mirror SET    WITNESS = 'TCP://SrvWitness:5022'    GO

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2014-09-18，如有侵权请联系 cloudcommunity@tencent 删除前往查看镜像配置数据库备份服务器

本文标签： sql2005镜像实现