在数据库镜像中将SQL Server Express版配置为SQL见证服务器

culuo4781

于 2020-07-19 05:41:29 发布

阅读量419

点赞数

文章标签：数据库 mysql oracle 分布式 java

原文链接：https://www.sqlshack.com/configuring-sql-server-express-edition-as-sql-witness-server-in-database-mirroring/

版权

本文详述如何在数据库镜像中配置SQL Server Express作为见证服务器，实现高可用性和自动故障转移。虽然数据库镜像已被弃用，但仍有组织使用。配置包括在现有镜像服务器上设置见证服务器，确保镜像数据库与主体数据库同步，并验证自动故障转移功能。

摘要由CSDN通过智能技术生成

In this article, I am going to explain how we can use the SQL Server Express edition for the SQL witness server of the database mirroring setup. Database mirroring is a cost-effective and easy to configure high availability solution. Unfortunately, this feature has been deprecated, but still, some organizations use database mirroring as a high availability solution.

在本文中，我将解释如何将SQL Server Express版本用于数据库镜像设置SQL见证服务器。数据库镜像是一种经济高效且易于配置的高可用性解决方案。不幸的是，此功能已被弃用，但仍然有些组织将数据库镜像用作高可用性解决方案。

Database mirroring can be configured in SQL Server Standard edition. It supports manual failover and automatic failover using the witness server (High Safety with automatic failover). The following conditions must be fulfilled to perform a successful failover from the principal database to the mirrored database:

可以在SQL Server标准版中配置数据库镜像。它支持使用见证服务器的手动故障转移和自动故障转移（具有自动故障转移的“高安全性”）。必须满足以下条件，才能从主体数据库成功执行故障转移到镜像数据库：

High Safety with automatic failover 具有自动故障转移功能的高安全性
The mirrored database must be fully synchronized with the principal database. All logs should be sent from the principal database to the mirror database and those should be written to the disks
镜像数据库必须与主体数据库完全同步。应将所有日志从主体数据库发送到镜像数据库，并将这些日志写入磁盘
The primary database lost the communication with the mirroring configuration and but the mirror and witness must be online to retain the quorum
主数据库失去了与镜像配置的通信，但是镜像和见证服务器必须在线才能保留仲裁

To configure an automatic failover, we must set up a SQL witness server that keeps an eye on principle database, and in case of database outage, it failover to the mirrored database without manual intervention. Now, to save the license cost, we can use the SQL Server Express edition as a witness server. I have explained the step by step process for adding a witness server in the existing database mirror.

要配置自动故障转移，我们必须建立一个关注见证数据库SQL见证服务器，并且在数据库中断的情况下，无需手动干预即可将其故障转移到镜像数据库。现在，为了节省许可证成本，我们可以将SQL Server Express版本用作见证服务器。我已经解释了在现有数据库镜像中添加见证服务器的分步过程。

在现有的镜像服务器中配置见证服务器 (Configure the witness server in the existing mirroring server)

To demonstrate the process, I have created three virtual machines. The following are the details:

为了演示该过程，我创建了三个虚拟机。详细信息如下：

Host name	SQL Server version	Role
SQL02	SQL Server Developer edition	SQL principle server
SQL03	SQL Server Developer edition	SQL mirrored server
SQL04	SQL Server Express edition	SQL witness server

主机名	SQL Server版本	角色
SQL02	SQL Server开发人员版	SQL主体服务器
SQL03	SQL Server开发人员版	SQL镜像服务器
SQL04	SQL Server Express版本	SQL见证服务器

I have created a demo database named DBA, which we are going to use to set up the mirror. The article: What is SQL Server database mirroring? explains the step by step process of deployment of SQL Server Database Mirroring.

我已经创建了一个名为DBA的演示数据库，我们将使用该数据库来建立镜像。文章：什么是SQL Server数据库镜像？介绍了SQL Server数据库镜像部署的分步过程。

To add the SQL Server Express edition as a SQL witness server first, we must configure its security of the mirror. To do that, connect to SQL02 (principal server), open SQL Server Management Studio, connect to Database Engine, right-click on the DBA database, and select Properties. See the following image:

若要首先将SQL Server Express版本添加为SQL见证服务器，我们必须配置其镜像安全性。为此，请连接到SQL02 （主服务器），打开SQL Server Management Studio，连接到数据库引擎 ，右键单击DBA数据库，然后选择“ 属性” 。见下图：

Connect to database engine and open database properties

On the properties dialog box, from the left pan, select Mirroring. On the right pan, click on Configure Security. See the following image:

在属性对话框的左侧窗格中，选择“ 镜像” 。在右窗格中，单击“ 配置安全性” 。见下图：

Configure security option in Database properties window

The configuration wizard called Configure Database Mirroring Security Wizard opens. The first screen provides basic information about the wizard and the tasks that are performed by the wizard. Click on Next:

将打开名为“ 配置数据库镜像安全向导 ”的配置向导。第一个屏幕提供有关向导以及向导执行的任务的基本信息。点击下一步：

On Include Witness Server screen, you can choose to configure the witness server or not. We are adding SQL Server Express edition as a witness server; hence select Yes. Click on Next:

在“ 包括见证服务器”屏幕上，可以选择是否配置见证服务器。我们将SQL Server Express版本添加为见证服务器。因此选择是。点击下一步 ：

On the Choose Server to Configure screen, we can choose the server on which you want to configure the security. We are configuring the witness server hence tick the Witness server instance checkbox. See the below image and click on Next:

在“ 选择要配置的服务器”屏幕上，我们可以选择要在其上配置安全性的服务器。我们正在配置见证服务器，因此请选中见证服务器实例复选框。请参见下图，然后单击“ 下一步” ：

On Principle Server Instance screen, you can select the SQL Server instance, which you want to use as the principal server. We have already configured the mirror, hence the options Principle server instance drop-down box, Listener port, and Endpoint name text boxes are greyed out. See the following image and click on Next:

在“主体服务器实例”屏幕上，可以选择要用作主体服务器SQL Server实例。我们已经配置了镜像，因此选项Principled Server instance下拉框， Listener端口和Endpoint name文本框为灰色。请参见下图，然后单击下一步 ：

Now, to configure the SQL Server Express edition as the SQL witness server, we must connect it with appropriate permission. We have installed SQL Server Express edition on SQL04; hence on the Witness server instance screen, select the SQL04 from Witness server instance drop-down box and click on Connect:

现在，要将SQL Server Express版配置为SQL见证服务器，我们必须以适当的权限连接它。我们已经在SQL04上安装了SQL Server Express版本。因此，在“ 见证服务器实例”屏幕上，从“ 见证服务器实例”下拉框中选择“ SQL04 ”，然后单击“ 连接” ：

On the Connect to Server window, provide appropriate credentials, and click on Connect:

在“ 连接到服务器”窗口上，提供适当的凭据，然后单击“ 连接” ：

If the connection is established successfully, the Connect to Server dialog box would close. Back to the Witness Server Instance screen, you can provide the desired port number and the endpoint name. We can also encrypt the data which is going to be transported through the defined endpoint. To do that, tick the Encrypt data sent through this endpoint checkbox. We will keep the options unchanged. See the following image and click on Next:

如果成功建立连接，将关闭“连接到服务器”对话框。返回“见证服务器实例”屏幕，您可以提供所需的端口号和端点名称。我们还可以加密将要通过定义的端点传输的数据。为此，请选中“ 加密通过此终结点发送的数据”复选框。我们将保持选项不变。请参见下图，然后单击下一步 ：

On the Service Accounts screen, provide the credentials of the SQL Server service account. These credentials will be used to connect the principle, witness, and mirror server. We have configured the service account named “dclocal\administrator,” which is used as a SQL Server service account across all the servers hence enter the “dclocal\administrator” in “Principal”, “Witness”, and “Mirror” text box:

在“ 服务帐户”屏幕上，提供SQL Server服务帐户的凭据。这些凭据将用于连接主体服务器，见证服务器和镜像服务器。我们已经配置了名为“ dclocal \ administrator ”的服务帐户，该帐户用作所有服务器上SQL Server服务帐户，因此在“ 主体 ”，“ 见证 ”和“ 镜像 ”文本框中输入“ dclocal \ administrator ”：

Service accounts of Witness , Primary and mirror.

On the Complete the Wizard screen, you can review the list of the task that is going to be performed by the wizard. It is advisable to review them once and click on Finish. See the below image:

在“ 完成向导”屏幕上，您可以查看向导将要执行的任务列表。建议对其进行一次检查，然后单击“ 完成” 。见下图：

If the endpoints are configured correctly, then you can see the “Success” on configuring endpoints screen. Click on Close:

如果正确配置了端点，则可以在“配置端点”屏幕上看到“成功”。点击关闭：

验证配置 (Verify configuration)

To verify that the witness server is configured successfully, open the database properties of the DBA database, and select the mirroring from the database properties dialog box. See the following image:

要验证见证服务器已成功配置，请打开DBA数据库的数据库属性，然后从“数据库属性”对话框中选择镜像。见下图：

Database properties of SQL witness server after setup

As you can see in the above image, the endpoint of the witness server has been created. You can see its network name in the “Witness” text box. Also, notice that the operation mode of the mirror has been changed. Before we configured the mirror, the operational mode was “High safety without an automatic failover (Synchronous),” and now it is “High safety with automatic failover (Synchronous).”

如上图所示，见证服务器的端点已创建。您可以在“ 见证 ”文本框中看到其网络名称。另外，请注意后视镜的操作模式已更改。在配置镜像之前，操作模式为“ 无自动故障转移的高安全性（同步） ”，现在为“ 具有自动故障转移的高安全性（同步）”。 ”

使用SQL见证服务器测试自动故障转移 (Test automatic failover using SQL witness server)

As explained above, in the case of database outage, the SQL witness server automatically transfers all the connections to the mirrored instance. To test the failover, before I set up the mirror, I had created a table named employee and added a few records in the table by executing the following script:

如上所述，在数据库中断的情况下，SQL见证服务器会自动将所有连接转移到镜像实例。为了测试故障转移，在设置镜像之前，我已经创建了一个名为employee的表，并通过执行以下脚本在表中添加了一些记录：

CREATE TABLE EMPLOYEE
    (
       ID           INT IDENTITY(1, 1),
       EMPLOYEENAME VARCHAR(50),
       DEPARTMENT   VARCHAR(150)
    )
GO
INSERT INTO EMPLOYEE
              (EMPLOYEENAME,
               DEPARTMENT)
VALUES      ('NISARG UPADHYAY',
               'IT'),
              ('NIRALI UPADHYAY',
               'HR'),
              ('SONALI BHATT',
               'HR'),
              ('SHAILESH UPADHYAY',
               'IT')
GO

Now, let’s test the process. To do that, perform following steps:

现在，让我们测试一下该过程。为此，请执行以下步骤：

连接到主体数据库 (Connect to principal database)

First, we must connect to the principal SQL Server. To do that, launch SSMS and connect to the SQL02 node. In the SQL Server Management Studio, you can see on the principal server, the DBA database is in the synchronized state. See the following image:

首先，我们必须连接到主体SQL Server。为此，启动SSMS并连接到SQL02节点。在SQL Server Management Studio中，您可以看到在主体服务器上，DBA数据库处于同步状态。见下图：

模拟数据库中断 (Simulate database outage)

Before we simulate the database failover, first execute the following queries to insert the data in the employee table:

在模拟数据库故障转移之前，首先执行以下查询以将数据插入到employee表中：

USE DBA
GO
INSERT INTO EMPLOYEE
            (EMPLOYEENAME,
             DEPARTMENT)
VALUES      
            ('BHARTI UPADHYAY',
             'IT'),
             ('DIXIT UPADHYAY',
             'IT')
GO

Once data is inserted, stop the SQL Server services to simulate the database outage. To do that, open SQL Server Configuration Manager, expand SQL Server Services, right-click on SQL Server (MSSQLSERVER) and click on Stop:

插入数据后，停止SQL Server服务以模拟数据库中断。为此，请打开“ SQL Server配置管理器”，展开“ SQL Server服务” ，右键单击“ SQL Server（MSSQLSERVER）” ，然后单击“ 停止” ：

验证自动故障转移 (Verify automatic failover)

When we configure the database mirroring, the state of the mirror database is set to <Database Name> (Mirror, Synchronized / Restoring..). See the following image of mirrored instance SQL03:

当我们配置数据库镜像时，镜像数据库的状态设置为<Database Name>（镜像，同步/还原..） 。请参见镜像实例SQL03的以下图像：

Once services are shut down, let us verify that the principal instance is failed over to the mirrored instance; to do that, connect to the SQL03, open SQL Server Management Studio, connect to the Database Engine and expand Databases:

服务关闭后，让我们验证主体实例是否已故障转移到镜像实例；为此，连接到SQL03 ，打开SQL Server Management Studio，连接到数据库引擎并展开Databases ：

After failover, SQL03 server is primary server

As you can see that the state of DBA database on the mirrored instance SQL03 has been changed from <Database Name> (Mirror, Synchronized / Restoring..) to <Database Name> (Principal, Synchronized). Let us try to run the following query to verify that the data has been copied to the mirror database:

如您所见，镜像实例SQL03上的DBA数据库的状态已从<数据库名称>（镜像，已同步/正在还原。）更改为<数据库名称>（主体，已同步） 。让我们尝试运行以下查询以验证数据已复制到镜像数据库：

USE DBA
GO
    
SELECT *
FROM   DBA..EMPLOYEE

The following is the output:

以下是输出：

摘要 (Summary)

In this article, I have explained step by step process of utilizing the SQL Server Express edition to configure the SQL witness server in the existing Database Mirroring.

在本文中，我逐步介绍了利用SQL Server Express版本在现有数据库镜像中配置SQL见证服务器的过程。