sql server 二〇〇九 数据库镜像(二卡塔尔—镜像实战

核心提示:这篇论坛文章(赛迪网技术社区)主要介绍了SQL Server
2005数据库镜像的配置脚本

通过证书方式创建数据库镜像

/*************************************************
*sql镜像的具体配置步骤说明
*Crate DataTime:
*2016-0205-2016-02-06研究成功
*Expian:
*本实例没有配置见证服务器,非域环境,数据库版本为SQL2008数据中心版
*两台机,系统版本为SERVER
2012,IP分别为:192.168.1.25(A)、192.168.1.26(B)
*其中A为主数据库,B为镜像库;
*遇到的问题记录如下:
*1)两台机都要放1433、5022
*2)主数据库备份时要用语句备份
*3)镜像服务器在恢复数据库时选择NORECOVERY(不回滚),默认为RECOVERY(回滚);
*恢复后会发现B数据中一直处于还原状态,属于正常现像。
*如果用的是RECOVERY,在双方镜像时会出现1416,没有为镜像配置XX数据库。
*后在国外一网站上查到原因了,顺利解决!
*—在网上找了很多,都没有解决这个问题,下面记录一下外国友人说的方法——
*I hope now it will be easy. First, backup the database on primary
server,
*thern copy the backup to the mirror server and then restore the
database with NORECOVERY option.
*Then backup the LOG (I hope your database is in FULL recovery mode) on
the primary database,
*copy to mirror and restore this log with NORECOVERY option. And then
try to start mirroring as soon as you can.
* – Alex_L Mar 28 ’12 at 7:43
***************************************************/
————-首先配置主服务A————-
–创建主密钥
USE master ;
CREATE MASTER KEY ENCRYPTION BY PASSWORD= ‘123456’;
GO
–创建证书
CREATE CERTIFICATE as_a_cert–证书名,自由命名
WITH SUBJECT=’as_a_cert’,
EXPIRY_DATE=’01/01/2099′ ;–终止日期,开始时间我这里没有写
GO
–用上面的证书创建镜像端点
–[databasemirroring]镜像端点名,可以自己命名
IF NOT EXISTS ( SELECT  1
                FROM    sys.database_mirroring_endpoints )
    BEGIN
        CREATE ENDPOINT [databasemirroring] STATE= STARTED AS TCP (
LISTENER_PORT=5022,
            LISTENER_IP= ALL ) FOR DATABASE_MIRRORING (
AUTHENTICATION=
            CERTIFICATE as_a_cert, ENCRYPTION= REQUIRED ALGORITHM AES,
ROLE=
            ALL ) ;
    END
—备份证书
BACKUP CERTIFICATE as_a_cert
TO FILE=’c:as_a_cert.cer’;–路径
GO

–==================================================================
–该文档主要用于内部配置模板
–场景:
–主服务器:192.168.3.32
–镜像服务器:192.168.3.33
–镜像数据库:DemoDB

这篇论坛文章(赛迪网技术社区)主要介绍了SQL Server
2005数据库镜像的配置脚本,详细内容请大家参考下文: SQL Server
2005数据库镜像配置脚本:

主数据库代码:

—备份主体数据库–依次执行
ALTER DATABASE news SET RECOVERY FULL;
–2)
BACKUP DATABASE news TO DISK=’c:news.bak’ WITH
format;–备份数据库主体
–3)
BACKUP LOG news TO DISK=’C:news.bak’;–备份日志

–=================================================================

示例如下:

永利官网ylg客户端 1永利官网ylg客户端 2Code
USE [master]

/*
*A上面证书、镜像端点已经完成了,B服务器步骤同上,A和B创建完成后,将证书相互对拷
*B服务器步骤同上,只是证书名要改为B
*将双方的证书对拷
*/
—注册B服务器的证书,创建用户并映射,下面语句要一起运行–在主服务器(A)上运行
CREATE LOGIN b_login WITH PASSWORD=’123456′;
CREATE USER b_user FOR LOGIN b_login;
CREATE CERTIFICATE as_b_cert AUTHORIZATION b_user FROM
FILE=’C:as_b_cert.cer’;
GRANT CONNECT ON
ENDPOINT::databasemirroring/*(镜像名字,可以自己命名)*/ TO
[b_login];
GO
—注册A服务器的证书,创建用户并映射,下面语句要一起运行–在主服务器(B)上运行
CREATE LOGIN a_login WITH PASSWORD=’123456′;
CREATE USER a_user FOR LOGIN a_login;
CREATE CERTIFICATE as_a_cert AUTHORIZATION b_user FROM
FILE=’C:as_a_cert.cer’;
GRANT CONNECT ON
ENDPOINT::databasemirroring/*(镜像名字,可以自己命名)*/ TO
[a_login];
GO
—-在B上恢复数据库,要选择”不回滚“,否会出错!恢复完成后会看到数据库一直处于“正在还原中”
—-恢复数据库(步骤省略)

开始配置

–在MIR-A上,创建数据库镜像端点

–创建数据库主密钥。
IF EXISTS(SELECT * FROM sys.databases WHERE name=’master’ and is_master_key_encrypted_by_server=1)    
    –drop master key;
    OPEN MASTER KEY DECRYPTION BY PASSWORD=’123456789′;
ELSE
    create MASTER KEY ENCRYPTION BY PASSWORD=’123456789′;
GO

—-创建镜像-先在镜像服务器(B)执行–
ALTER DATABASE news SET
PARTNER=’tcp://192.168.1.25:5022′;–与主服务器建立连接
–在主服务器(A)上运行
ALTER DATABASE news SET
PARTNER=’tcp://192.168.1.26:5022′;–与镜像服务器建立连接
–成功后会看到主服务器上显示”主体已同步”,B镜像服务器上显示“正在还原”
–到这一步就大功告成了,可以来测试了,通过故障转移来测试数据是否同步!
 –2016/02/06/14:53/福田国际电商务产业园/暾

  1. 在主服务器和镜像服务器上创建Master Key

    –=========================================================================================================
    –创建Master key
    –在主服务器和镜像服务器上运行
    USE master;
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name=’##MS_DatabaseMasterKey##’)
    BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD =’master@key123′
    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

  2. 在主服务器上创建证书并备份

    –=========================================================================================================
    –创建和备份证书
    –在主服务器上运行
    IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name=’HOST_3_32_cert’ )
    BEGIN
    CREATE CERTIFICATE HOST_3_32_cert WITH SUBJECT =’HOST_3_32_cert’,
    START_DATE = ’01/01/2010′ , EXPIRY_DATE = ’01/01/2199′;
    END
    GO
    BACKUP CERTIFICATE HOST_3_32_cert TO FILE=’D:HOST_3_32_cert.cer’
    GO

  3. 在镜像服务器上创建证书并备份

    –=========================================================================================================
    –创建和备份证书
    –在镜像服务器上运行
    IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name=’HOST_3_33_cert’ )
    BEGIN
    CREATE CERTIFICATE HOST_3_33_cert WITH SUBJECT =’HOST_3_33_cert’,
    START_DATE = ’01/01/2010′ , EXPIRY_DATE = ’01/01/2199′;
    END
    GO
    BACKUP CERTIFICATE HOST_3_33_cert TO FILE=’D:HOST_3_33_cert.cer’
    GO

  4. 在主服务器上创建镜像端点

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

  5. 在镜像服务器上创建镜像端点

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

  6. 将主服务器和镜像服务器上备份的证书相互复制到对方服务器上

  7. 在主服务器上创建登陆和用户以及证书,并授予连接镜像端点的权限


    –在主服务器上创建登陆和用户以及证书,并授予连接镜像端点的权限
    –在主服务器上运行
    USE master;
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] =’HOST_3_33_login’)
    BEGIN
    CREATE LOGIN HOST_3_33_login WITH PASSWORD =’Auto@sql’
    END
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= ‘HOST_3_33_User’)
    BEGIN
    CREATE USER HOST_3_33_User FOR LOGIN HOST_3_33_login
    END
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= ‘HOST_3_33_cert’)
    BEGIN
    CREATE CERTIFICATE HOST_3_33_cert AUTHORIZATION HOST_3_33_User FROM FILE=’D:HOST_3_33_cert.cer’
    END
    GO
    GRANT CONNECT ON ENDPOINT::[Endpoint_Mirroring] TO HOST_3_33_login
    GO

create endpoint DB_MirroringEP

SELECT * FROM sys.key_encryptions;
go

永利官网ylg客户端,8. 在镜像服务器上创建登陆和用户以及证书,并授予连接镜像端点的权限

AS tcp (listener_port = 5022)

–向数据库中添加证书。
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name=’Endpoint_Mirroring’)
    DROP ENDPOINT Endpoint_Mirroring
GO

---------------------------------------------------------------------
--在镜像服务器上创建登陆和用户以及证书,并授予连接镜像端点的权限
--在镜像服务器上运行
USE master;
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='HOST_3_33_login')
BEGIN
CREATE LOGIN HOST_3_33_login WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'HOST_3_33_User')
BEGIN
CREATE USER HOST_3_33_User FOR LOGIN HOST_3_33_login
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'HOST_3_33_cert')
BEGIN
CREATE CERTIFICATE HOST_3_33_cert AUTHORIZATION HOST_3_33_User FROM FILE='D:HOST_3_33_cert.cer'
END
GO
GRANT CONNECT ON ENDPOINT::[Endpoint_Mirroring] TO HOST_3_33_login
GO

for database_Mirroring (role = partner,encryption=supported);

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

  1. 在主服务器和镜像服务器上配置防火墙,开放5022端口

  2. 在镜像服务器上使用Telent来检查端口情况

    telnet 192.168.3.33 5022

  3. 在主服务器上使用Telent来检查端口情况

    telnet 192.168.3.32 5022

  4. 在主服务器上备份数据库和日志

    –数据库完整备份和日志备份
    BACKUP DATABASE DemoDB TO DISK=’DemoDB_FULL.BAK’ WITH COMPRESSION
    BACKUP LOG DemoDB TO DISK=’DemoDB_Log.BAK’ WITH COMPRESSION

go

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = ‘HOST_A certificate’,
    START_DATE = ‘2008-01-01’;
GO

 

–在MIR-B上,创建数据库镜像端点,用于伙伴通讯

SELECT * FROM sys.certificates;
GO

  1. 将主服务器上的备份复制到镜像服务器上,使用WITH
    NORECOVERY选项还原数据库备份和日志备份

    –使用数据库完整备份和日志备份还原
    –注意修改还原目录
    RESTORE DATABASE DemoDB FROM DISK=’DemoDB_FULL.BAK’ WITH NORECOVERY
    RESTORE LOG DemoDB FROM DISK=’DemoDB_Log.BAK’ WITH NORECOVERY

CREATE ENDPOINT Db_MirroringEP

–创建数据库端点
IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name=’Endpoint_Mirroring’)
    DROP ENDPOINT Endpoint_Mirroring;
GO

 

AS TCP (LISTENER_PORT = 5022)

CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED
    AS TCP ( LISTENER_PORT=5022 , 
        LISTENER_IP = ALL )
    FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_A_cert , 
        ENCRYPTION = REQUIRED ALGORITHM RC4 , 
        ROLE = PARTNER );
GO

  1. 在镜像服务器上配置伙伴


    –从服务器设置镜像
    USE [master]
    GO
    ALTER DATABASE DemoDB SET PARTNER=’TCP://192.168.3.32:5022′
    GO

FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED);

SELECT * FROM sys.database_mirroring_endpoints;
GO

15. 在主服务器上配置伙伴

GO

–备份证书
BACKUP CERTIFICATE HOST_A_cert TO FILE = ‘D:MIRRORHOST_A_cert.cer’;
GO

------------------------------------------------------------------
--主服务器设置镜像
--如果提示日志不足,请继续备份还原日志
USE [master]
GO
ALTER DATABASE DemoDB SET PARTNER='TCP://192.168.3.33:5022'
GO

ALTER ENDPOINT Db_MirroringEP STATE = STARTED

–在服务器之间手动拷贝证书,保证每个服务只器都有所有证书

  1. 如果希望日志运行在异步模式下,修改同步模式


    –在主服务器上运行
    USE [master]
    GO
    ALTER DATABASE DemoDB SET PARTNER SAFETY OFF
    GO

GO

–创建用户,用于访问MIRROR
IF EXISTS(select * from sys.certificates WHERE name=’HOST_B_cert’)
    DROP CERTIFICATE HOST_B_cert
GO

 

–在MIR-W上,创建数据库镜像端点,用于见证通讯

IF EXISTS(select * from sys.sql_logins WHERE name=’HOST_B_login’)
    DROP LOGIN HOST_B_login
GO

配置结束后,可以使用镜像监视器来查看

CREATE ENDPOINT Db_MirroringEP

IF EXISTS(select * from sys.database_principals WHERE  name=’HOST_B_user’)
    DROP USER HOST_B_user
GO

–===================================================================

AS TCP (LISTENER_PORT = 5022)

create LOGIN HOST_B_login WITH PASSWORD = ‘123456789’;
create USER HOST_B_user FOR LOGIN HOST_B_login;
create CERTIFICATE 
    HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = ‘D:MIRRORHOST_B_cert.cer’;
GO

惯例妹子镇邪

FOR DATABASE_MIRRORING (ROLE = WITNESS, ENCRYPTION = SUPPORTED);

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

永利官网ylg客户端 3

GO

–创建用户,用于访问WITNESS
IF EXISTS(select * from sys.certificates WHERE name=’HOST_C_cert’)
    DROP CERTIFICATE HOST_C_cert
GO

ALTER ENDPOINT Db_MirroringEP STATE = STARTED

IF EXISTS(select * from sys.sql_logins WHERE name=’HOST_C_login’)
    DROP LOGIN HOST_C_login
GO

发表评论

电子邮件地址不会被公开。 必填项已用*标注