none
SQL Server Mirroring Configuration RRS feed

  • 常规讨论

  • SQL Server Standby Configuration

     

    Primary Database

    Standby Database

     

     

     

     

     

     

     

     


    Primary192.168.1.1    Standby192.168.1.2   No witness server

     

    如果之前镜像没有配置成功或不能启动,把Endpoint、证书等信息都删掉;(若是全新安装不考虑此步骤)

    select * from master.sys.endpoints   //查看创建过的endpoint

    select * from master.sys.certificates  //查看创建过的证书

     

    根据查到的信息做以下删除操作,必须要按顺序删除,主从库都要进行此操作

    drop endpoint Endpoint_Mirroring //删除endpoint

    drop certificate HOST_1_cert  //删除证书

    drop certificate HOST_2_cert  //删除证书

    drop login HOST_1_login   //删除登录

    drop user HOST_1_user   //删除用户

    drop master key    //删除主Key

    配置主体出站(192.168.1.1):

    USE master;

    create master key encryption by password = ‘fuchuan@123456’;   --创建master key

    GO

    create certificate HOST_1_cert

    with subject = ‘HOST_1 certificate for database mirroring’;       --创建证书

    GO

    create endpoint Endpoint_Mirroring    --创建Endpoint,它是可共用的

    state = STARTED

    as tcp(

    listener_port=7024,listener_ip=ALL)

    for database_mirroring(

    authentication = CERTIFICATE HOST_1_cert,encryption = required algorithm AES,role = ALL);

    GO

    backup certificate HOST_1_cert to file = ‘E:\HOST_1_cert.cer’;  --备份证书

    GO

     

    配置镜像出站(192.168.1.2):

    use master;

    create master key encryption by password = ‘fuchuan@123456’;

    GO

    create certificate HOST_2_cert with subject = ‘HOST_2 certificate for database mirroring’;

    GO

    create endpoint Endpoint_Mirroring

    state = started

    as tcp(listener_port=70224,listener_ip=ALL)

    for database_mirroring(

    authentication = certificate HOST_2_cert,encryption = required algorithm AES,role = ALL);

    GO

    backup certificate HOST_2_cert to file = ‘e:\HOST_2_cert.cer’;

    GO

    将两个证书互相拷贝到对方的E盘;

    配置主体192.168.1.1入站:

    use master;

    create login HOST_2_LOGIN with password = ‘fuchuan@123456’;

    GO

    use master;

    create user HOST_2_USER for login HOST_2_LOGIN;

    GO

    create certificate HOST_2_cert authorization HOST_2_USER from file = ‘E:\HOST_2_cert.cer’;

    GO

    grant connect on endpoint::Endpoint_Mirroring to HOST_2_LOGIN

    GO

    配置镜像192.168.1.2入站

    use master;

    create login HOST_2_LOGIN with password = ‘fuchuan@123456’;

    GO

    use master;

    create user HOST_2_USER for login HOST_2_LOGIN;

    GO

    create certificate HOST_2_cert authorization HOST_2_USER from file =’E:\HOST_2_cert.cer’;

    GO

    grant connect on endpoint::Endpoint_Mirroring to HOST_2_LOGIN

    GO

    备份主体192.168.1.1数据库

    use master;

    GO

    alter database DAL set recovery full;

    GO

    backup database DAL to disk = ‘e:\DAL.bak’ with format

    GO

    backup log DAL to disk = ‘E:\DAL_log.bak’

    GO

    (务必用以上命令备份,否则可能因无法还原事务日志而镜像失败)

    还原数据库镜像到192.168.1.2

    restore database DAL from disk = ‘e:\DAL.bak’ with norecovery

    GO

    restore log DAL from disk = ‘e:\DAL_log.bak’ with file=1,norecovery

    GO

    (务必使用以上命令操作,如果在管理器里用还原选项操作,还原出来的数据不能用于镜像。)

    配置镜像

    a.先在镜像数据库192.168.1.2操作

    alter database DAL set partner = ‘TCP://192.168.1.1:70224’;

    b.再在主体数据库192.168.1.1操作

    alter database DAL set partner = ‘TCP://192.168.1.2:7024’;

    1. 数据库镜像取消命令:

    alter database DAL set partner off

     

    注意:加密和镜像是两码事儿!

     

    2. 加密过程:


    Michael Chiang

    2011年10月21日 6:02

全部回复

  • 你这相同的网段,按理说应该在同一个域中吧.在同一个域中的话,就不需要使用证书验证安全性吧.

     


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年10月21日 6:31