• SQL Server非域(跨域)环境下镜像(Mirror)的搭建步骤及注意事项


    在实际的生产环境下,我们经常需要跨域进行数据备份,而创建Mirror是其中一个方案。但跨域创建Mirror要相对复杂的多,需要借助证书进行搭建。

    下面我们将具体的步骤总结如下:

    第一部分 创建证书

    Step 1:创建数据库主密钥

    主密钥的用处在这里是用于加密证书,当然主密钥不仅仅只有这个作用。对数据库主密钥的密码及存储保护要小心,这是实力级别的对象,影响面非常广。可以使用下面语句来创建:

    USE master  

    GO  

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';

    通过系统表查看,确认

    select top 100 is_master_key_encrypted_by_server,*  from sys.databases

     

    使用相同方式在镜像服务器创建数据库主密钥。

    Step 2:创建证书,并用主密钥加密

     创建证书时,默认在创建日期开始一年后过期,所以针对证书的创建,要注意其过期时间。下面是在“主体服务器”上创建HOST_P_cert证书的创建

    USE master  

    GO  

    CREATE CERTIFICATE Host_A_Cert   

    WITH Subject = 'Host_P Certificate',  

    Expiry_Date = '2050-1-1'; --过期日期

    使用相同的方法在镜像服务器上实现对HOST_S_cert证书的创建

    Step 3:创建端点

    可以使用下面的代码在主体服务器中创建端点,并且指定使用5022,端口,端口在镜像配置过程中不强制使用特定端口(被占用或者特定端口如1433除外)。

    --使用Host_A_Cert证书创建端点  

    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 Host_A_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE =  

                ALL );  

        END 

    在镜像服务器对证书名稍作修改,创建镜像服务器的端点。

    Step 4:备份证书

    备份证书的目的是发送到别的服务器并导入证书,以便别的服务器能通过证书访问这台服务器(主体服务器)。

    BACKUP CERTIFICATE Host_A_Cert  

    TO FILE = 'D:ShareFoldersMirrorHost_A_Cert.cer';  

    同理,在镜像服务器上重复一次,注意证书名和路径。备份之后可以在目标文件夹上看到有一个cer文件:

    备份证书文件互相Copy至对方文件中。

    Step 5:创建登录账号

    针对每个服务器单独创建一个服务器登录账号,这里只需要创建一个登录给镜像服务器即可:

    CREATE LOGIN Host_B_Login WITH PASSWORD = 'Pa$$w0rd';

    同理,在镜像服务器上创建Host_A_Login给主体服务器。 

    Step 6:创建用户,并映射到Step 5中创建的登录账号中

     在主体服务器上运行:

    CREATE USER Host_B_User For Login Host_B_Login;

    同理在镜像服务器也创建。

    Step 7:使用证书授权用户

     创建一个新的证书,并使用从伙伴服务器中复制过来的证书导入,然后映射step 6中的账号到这个新证书上。

    CREATE CERTIFICATE Host_B_Cert  

    AUTHORIZATION Host_B_User  

    FROM FILE = 'D:ShareFoldersMirrorHost_B_Cert.cer';

    注意镜像服务器上也同样。

    Step 8:把Step 5中的登录账号授权访问端口

    GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_B_Login];

    镜像服务器也一样。

    到此为止,镜像所需的证书已经配置完毕。

     

    第二部分 备份还原数据库

    如果是想按照界面操作的话,可以采用如下方式

    step1 创建数据库,选择【还原文件和文件组】

    step 2  手动输入目标数据库名称 选择 【源设备】,选择对应的文件

    注意:本次还原是为Mirror做准备,所以,点击   【选项】  按钮 。

    所以需要选择【不对数据库执行任何操作,不回滚未提交的事务….】

    还原成功。

    第三部分 启动镜像

    依次分别在镜像Server和主Server上运行以下命令就可以了【最好在Master DB上执行以下命令】

    在镜像Server上线运行

    ALTER DATABASE [Test_Mirror]

        SET PARTNER = 'TCP://172.XXX.XXX.93:5022';

    GO

    在主Server上运行

    ALTER DATABASE [Test_Mirror]

        SET PARTNER = 'TCP://172.XXX.XX.106:5022';

    GO

    配置成功,此时显示如下:

    主体服务器上显示

     

    镜像服务器上DB显示

    第四部分 补充部分

    问题1  查询判断数据库是否已添加主密钥

    ---sys.databases的is_master_key_encrypted_by_server得到是否有加密

    select top 100 is_master_key_encrypted_by_server,* from sys.databases

     

    ----如果没有就看不到数据【需定义到数据库】

    ----解释说明:##MS_ServiceMasterKey##----是说的整个服务,而##MS_DatabaseMasterKey## 是说的Master数据库,需留意。

    ----我们  使用证书搭建镜像 是需要在master数据库上创建数据库主密钥(如果主密钥不存在)。

    SELECT * FROM sys.symmetric_keys

    以下截图查询的数据显示Master数据库尚未创建主密钥。

    以下截图的数据显示Master数据库已有主密钥

    问题2 由日志传送更改为镜像。

      希望直接更改,即不再需要备份和还原。

    Step 1 【注意:此时先手动执行一下此DB的Log 备份的Job,然后停掉此Job,接下来再执行Copy Log 文件的Job(如果有此Job的话),再停掉此Job,最后执行Restore 此Log 文件的Job,接着停掉此Job】

    Step 2 选择指定DB,取消【将此数据库启用为日志传送配置中的主数据库…】,就是把 勾 去掉。

    Step 3 开始建立伙伴关系

    先在备份Server的DB上去做

    ALTER DATABASE [数据库名称]

        SET PARTNER = 'TCP://172.XXX.XXX.6:10001';

    GO

    然后再在主DB上运行

    ALTER DATABASE [数据库名称]

        SET PARTNER = 'TCP://172.XXX.XXX.4:10002';

    GO

    问题3 在建立伙伴关系时,需注意设置伙伴的顺序

    我们需要先在Mirror服务器上执行,然后再在主服务器中执行,则不报错。

    否则,提示错误:

    问题删除主密钥相关问题

     删除主密钥 需先删除由它加密的证书;而删除证书需要先删除由它映射的用户。

    问题搭建前的检查项

    (1) 网络是否能联通,并且端口可用;(2)SQL Server数据库的恢复模式是否为完整;(3)SQL Server上是否有常规的备份作业,特别是日志备份是否已经暂停。

  • 相关阅读:
    Laravel5.0学习--01 入门
    MySQL账户安全设置
    360路由器c301最新固件支持万能中继
    PsySH:PHP交互运行环境
    PHP-CS-Fixer:格式化你的PHP代码
    JVM 类加载机制详解
    Java虚拟机(JVM)概述
    聊一聊 Spring 中的线程安全性
    Java 里如何实现线程间通信
    Java 数据结构
  • 原文地址:https://www.cnblogs.com/xuliuzai/p/9617679.html
Copyright © 2020-2023  润新知