sql server 2009 数据库镜像(二卡塔尔(قطر‎—镜像实战

这篇论坛文章(赛迪网技术社区)主要介绍了SQL Server,2005数据库镜像配置脚本,通过证书方式创建数据库镜像,主数据库代码,Expian,–==================================================================,–场景

图片 3

主导提醒:那篇论坛散文(Sadie网技巧社区State of Qatar首要介绍了SQL Server
2007数据库镜像的计划脚本

经过证书情势创立数据库镜像

/*************************************************
*sql镜像的绘身绘色安插步骤表达
*Crate DataTime:
*2014-0205-2014-02-06研商成功
*Expian:
*本实例未有配置亲眼看见服务器,非域境遇,数据库版本为SQL二〇〇九数量中央版
*两台机,系统版本为SERAV4VEHaval2011,IP分别为:192.168.1.25(A)、192.168.1.26(B)
*里头A为主数据库,B为镜像库;
*遇见的主题材料记录如下:
*1State of Qatar两台机都要放1433、5022
*2卡塔尔国主数据库备份时要用语句备份
*3卡塔尔国镜像服务器在平复数据库时精选NORECOVELX570Y(不回滚),默感觉RECOVE本田UR-VY(回滚);
*卷土而来后会发掘B数据中央直属机关接处于还原状态,属陈岚常现像。
*只要用的是RECOVE昂科拉Y,在双边镜像时会现身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/0四分一099′ ;–终止日期,带头时间本人这里未有写
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
–镜像数据库:德姆oDB

那篇论坛散文(Sadie网本领社区卡塔尔国首要介绍了SQL Server
二零零五数据库镜像的布署脚本,详细内容请我们参谋下文: SQL Server
二零零五数据库镜像配置脚本:

主数据库代码:

—备份主体数据库–依次试行
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’;–备份日志

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

事必躬亲如下:

图片 1图片 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/*(镜像名字,可以自身取名State of Qatar*/ 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上恢复生机数据库,要筛选”不回滚“,否会出错!复苏达成后会看到数据库一贯处于“正在还原中”
—-复苏数据库(步骤省略)

起来配备

–在MIRubicon-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)执行–
ALTE纳瓦拉 DATABASE news SET
PARTNE瑞鹰=’tcp://192.168.1.25:5022′;–与主服务器构建连接
–在主服务器(A)上运营
ALTEOdyssey DATABASE news SET
PARTNE奥迪Q5=’tcp://192.168.1.26:5022′;–与镜像服务器组建连接
–成功后会见到主服务器上呈现”主体已同步”,B镜像服务器上海展览中心示“正在恢复生机”
–到这一步就马到功成了,能够来测验了,通过故障转移来测验数据是或不是同步!
 –二零一四/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

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=’德姆oDB_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

 

–在MI哈弗-B上,创设数据库镜像端点,用于同伴通信

SELECT * FROM sys.certificates;
GO

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

    –使用数据库完整备份和日志备份还原
    –注意更改还原目录
    RESTORE DATABASE DemoDB FROM DISK=’德姆oDB_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:\MIRROR\HOST_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

–成立顾客,用于访谈MILX570RO奥迪Q3
IF EXISTS(select * from sys.certificates WHERE name=’HOST_B_cert’)
    DROP CERTIFICATE HOST_B_cert
GO

 

–在MI昂Cora-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:\MIRROR\HOST_B_cert.cer’;
GO

常规妹子镇邪

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

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

图片 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