当前位置：Gxlcms > 数据库问题 > 非域环境下SQL Server搭建Mirror（镜像）的详细步骤

非域环境下SQL Server搭建Mirror（镜像）的详细步骤

时间：2021-07-01 10:21:17 帮助过：9人阅读

，所以把搭建证书放在第一步。有些资料上会把备份还原操作放在证书搭建之前，但是根据个人经验，当磁盘IO、网络性能不佳的时候，备份、传输、还原都会浪费大量的时间（个人操作过2个小时），并且期间服务器几乎不能操作。这种时候，我会选择先搭建好，再还原，然后马上进行同步，减少主从差异，需要同步更多的数据。

第一部分  创建证书：

【如果服务器使用Local System作为SQL Server服务账号，就需要使用证书授权。】

使用证书搭建镜像的步骤如下：

（1）创建数据库主密钥（如果主密钥不存在）。

（2）在Master数据库中创建证书并用主密钥加密。

（3）使用证书授权创建端点（endpoint）。

（4）备份证书成为证书文件。

（5）在服务器上创建登录账号，用于提供其他实例访问。

（6）在master库中创建用户，并映射到上一步的登录账号中。

（7）把证书授权给这些用户。

（8）在端点上授权。

（9）设置镜像服务器的主体伙伴。

（10）设置主体服务器的镜像伙伴。

（11）配置见证服务器。

Step 1：创建数据库主密钥

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

  1. <span style="color: #0000ff;">USE</span><span style="color: #000000;"> master   
  2. </span><span style="color: #0000ff;">GO</span>   
  3. <span style="color: #0000ff;">CREATE</span> MASTER <span style="color: #0000ff;">KEY</span> ENCRYPTION <span style="color: #0000ff;">BY</span> PASSWORD <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Pa$$w0rd</span><span style="color: #ff0000;">‘</span>;

技术图片

通过系统表查看，确认。

技术图片

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

Step 2：创建证书，并用主密钥加密

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

  1. <span style="color: #0000ff;">USE</span><span style="color: #000000;"> master   
  2. </span><span style="color: #0000ff;">GO</span>   
  3. <span style="color: #0000ff;">CREATE</span><span style="color: #000000;"> CERTIFICATE Host_A_Cert    
  4. </span><span style="color: #0000ff;">WITH</span> Subject <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Host_P Certificate</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,   
  5. Expiry_Date </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2050-1-1</span><span style="color: #ff0000;">‘</span>; <span style="color: #008080;">--</span><span style="color: #008080;">过期日期 </span>

技术图片

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

Step 3：创建端点

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

  1. <span style="color: #008080;">--</span><span style="color: #008080;">使用Host_A_Cert证书创建端点   </span>
  2. <span style="color: #0000ff;">IF</span> <span style="color: #808080;">NOT</span> <span style="color: #808080;">EXISTS</span> ( <span style="color: #0000ff;">SELECT</span>  <span style="color: #800000; font-weight: bold;">1</span>   
  3.                 <span style="color: #0000ff;">FROM</span><span style="color: #000000;">    sys.database_mirroring_endpoints )   
  4.     </span><span style="color: #0000ff;">BEGIN</span>   
  5.         <span style="color: #0000ff;">CREATE</span> ENDPOINT <span style="color: #ff0000;">[</span><span style="color: #ff0000;">DatabaseMirroring</span><span style="color: #ff0000;">]</span> STATE <span style="color: #808080;">=</span> STARTED <span style="color: #0000ff;">AS</span> TCP ( LISTENER_PORT <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">5022</span><span style="color: #000000;">,   
  6.             LISTENER_IP </span><span style="color: #808080;">=</span> <span style="color: #808080;">ALL</span> ) <span style="color: #0000ff;">FOR</span> DATABASE_MIRRORING ( AUTHENTICATION <span style="color: #808080;">=</span><span style="color: #000000;">   
  7.             CERTIFICATE Host_A_Cert, ENCRYPTION </span><span style="color: #808080;">=</span> REQUIRED Algorithm AES, ROLE <span style="color: #808080;">=</span>   
  8.             <span style="color: #808080;">ALL</span><span style="color: #000000;"> );   
  9.     </span><span style="color: #0000ff;">END</span>

技术图片

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

Step 4：备份证书

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

  1. <span style="color: #0000ff;">BACKUP</span><span style="color: #000000;"> CERTIFICATE Host_A_Cert   
  2. </span><span style="color: #0000ff;">TO</span> <span style="color: #0000ff;">FILE</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">D:\ShareFoldersMirror\Host_A_Cert.cer</span><span style="color: #ff0000;">‘</span>;

技术图片

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

 技术图片

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

技术图片

Step 5：创建登录账号

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

  1. <span style="color: #0000ff;">CREATE</span> LOGIN Host_B_Login <span style="color: #0000ff;">WITH</span> PASSWORD <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Pa$$w0rd</span><span style="color: #ff0000;">‘</span>;

技术图片

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

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

在主体服务器上运行：

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> Host_B_User <span style="color: #0000ff;">For</span> Login Host_B_Login;

技术图片

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

Step 7：使用证书授权用户

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

  1. <span style="color: #0000ff;">CREATE</span><span style="color: #000000;"> CERTIFICATE Host_B_Cert   
  2. </span><span style="color: #0000ff;">AUTHORIZATION</span><span style="color: #000000;"> Host_B_User   
  3. </span><span style="color: #0000ff;">FROM</span> <span style="color: #0000ff;">FILE</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">D:\ShareFoldersMirror\Host_B_Cert.cer</span><span style="color: #ff0000;">‘</span>;

技术图片

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

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

  1. <span style="color: #0000ff;">GRANT</span> CONNECT <span style="color: #0000ff;">ON</span> ENDPOINT::<span style="color: #ff0000;">[</span><span style="color: #ff0000;">DatabaseMirroring</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Host_B_Login</span><span style="color: #ff0000;">]</span>;

技术图片

镜像服务器也一样。

到此为止，配置镜像的步骤已经完毕，后续会给出尽可能自动化的配置脚本。

第二部分  备份还原数据库：

这一部分没有什么特别强调的，在此次试验过程中，使用了界面配置。

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

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

技术图片

 还原成功

技术图片

第三步：启动镜像

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

在镜像Server上线运行

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">DATABASE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Test_Mirror</span><span style="color: #ff0000;">]</span> 
  2.     <span style="color: #0000ff;">SET</span> PARTNER <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">TCP://172.83.XXX.XXX:5022</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  3. </span><span style="color: #0000ff;">GO</span>

技术图片

在主Server上运行

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">DATABASE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Test_Mirror</span><span style="color: #ff0000;">]</span> 
  2.     <span style="color: #0000ff;">SET</span> PARTNER <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">TCP://172.73.XXX.XXX:5022</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  3. </span><span style="color: #0000ff;">GO</span>

技术图片

 

 配置成功，此时显示如下：

主体服务器上显示

技术图片

镜像服务器上DB显示

技术图片

 

4. 补充说明

以下内容用来学习

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

  1. <span style="color: #008080;">--</span><span style="color: #008080;">-sys.databases的is_master_key_encrypted_by_server得到是否有加密</span>
  2. <span style="color: #0000ff;">select</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">100</span> is_master_key_encrypted_by_server,<span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> sys.databases
  3. </span><span style="color: #008080;">--</span><span style="color: #008080;">--如果没有就看不到数据【需定义到数据库】 </span><span style="color: #008080;">
  4. --</span><span style="color: #008080;">--解释说明：##MS_ServiceMasterKey##----是说的整个服务，而##MS_DatabaseMasterKey## 是说的Master数据库，需留意。</span><span style="color: #008080;">
  5. --</span><span style="color: #008080;">--我们  使用证书搭建镜像 是需要在master数据库上创建数据库主密钥（如果主密钥不存在）。</span>
  6. <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> 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上去做

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">DATABASE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">YYYY_Mob</span><span style="color: #ff0000;">]</span> 
  2.     <span style="color: #0000ff;">SET</span> PARTNER <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">TCP://172.87.XXX.XX2:10001</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  3. </span><span style="color: #0000ff;">GO</span>

技术图片

 然后再在主DB上运行

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">DATABASE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">YYYY_Mob</span><span style="color: #ff0000;">]</span> 
  2.     <span style="color: #0000ff;">SET</span> PARTNER <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">TCP://172.89.XXX.XX4:10002</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  3. </span><span style="color: #0000ff;">GO</span>

技术图片

问题3 删除主密钥

技术图片

解决方案：

DROP CERTIFICATE 证书名

但此时 还有报错了

 技术图片

Step 1 删除映射的登录账号和用户名

查看登入名 

技术图片 

删除标识的登入名 ，此时执行还会报同样的错误。

注意登入名和用户名是2个概念，

  1. <span style="color: #0000ff;">DROP</span> LOGIN   For_HOST_B_user

（有时还要查询  select top 100* from sys.sysusers是否还有这个用户，有的话，还要执行 DROP User For_HOST_B_user）

Step 2 删除端口 

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> sys.endpoints e <span style="color: #0000ff;">WHERE</span> e.name <span style="color: #808080;">=</span> N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Endpoint_Mirroring</span><span style="color: #ff0000;">‘</span>

技术图片

存在则删除端口

技术图片 

Step 3  删除

此时，就OK了。去删除证书和主密钥

技术图片

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

如果按照网址上介绍的步骤 ，现在主服务器上执行，设置伙伴。

 技术图片

则可能报错，提示的错误信息如下：

技术图片

我们先在Mirror服务器上执行

技术图片

然后再在主服务器中执行，则不报错

技术图片

 

参考文献

http://blog.csdn.net/dba_huangzj/article/details/27652857

 

非域环境下SQL Server搭建Mirror（镜像）的详细步骤

标签：serve   png   服务器角色   文件中   start   rest   实现   pre   tab   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！