配置工作组可用性组Configure a workgroup availability group

本文将会说明创建与 Active Directory 域无关的群集(该群集启用了 Always On 可用性组,也称为工作组群集)所要执行的步骤。This article explains the steps necessary to create an Active Directory domain-independent cluster with an Always On availability group; this is also known as a workgroup cluster. 本文将重点说明与准备和配置工作组及可用性组相关的步骤,而不会重述其他文章中已介绍过的步骤,例如创建群集或部署可用性组。This article focuses on the steps that are relevant to preparing and configuring the workgroup and availability group, and glosses over steps that are covered in other articles, such as how to create the cluster, or deploy the availability group.

先决条件Prerequisites

若要配置工作组可用性组,需要准备好以下各项:To configure a workgroup availability group, you need the following:

  • 至少两个运行 SQL Server 2016(或更高版本)的 Windows Server 2016(或更高版本)虚拟机,这些虚拟机已使用静态 IP 地址部署到同一个可用性集或不同的可用性区域。At least two Windows Server 2016 (or higher) virtual machines running SQL Server 2016 (or higher), deployed to the same availability set, or different availability zones, using static IP addresses.
  • 一个本地网络,其子网中至少有 4 个可用 IP 地址。A local network with a minimum of 4 free IP addresses on the subnet.
  • 管理员组中的每台计算机上各有一个帐户,该帐户在 SQL Server 中也拥有 sysadmin 权限。An account on each machine in the administrator group that also has sysadmin rights within SQL Server.
  • 打开端口:TCP 1433、TCP 5022、TCP 59999。Open ports: TCP 1433, TCP 5022, TCP 59999.

为便于参考,本文使用了以下参数,但可根据需要对其进行修改:For reference, the following parameters are used in this article, but can be modified as is necessary:

名称Name 参数Parameter
Node1Node1 AGNode1 (10.0.0.4)AGNode1 (10.0.0.4)
Node2Node2 AGNode2 (10.0.0.5)AGNode2 (10.0.0.5)
群集名称Cluster name AGWGAG (10.0.0.6)AGWGAG (10.0.0.6)
侦听器Listener AGListener (10.0.0.7)AGListener (10.0.0.7)
DNS 后缀DNS Suffix ag.wgcluster.example.comag.wgcluster.example.com
工作组名称Work group name AGWorkgroupAGWorkgroup
   

设置 DNS 后缀Set DNS suffix

此步骤为两个服务器配置 DNS 后缀。In this step, configure the DNS suffix for both servers. 例如,ag.wgcluster.example.comFor example, ag.wgcluster.example.com. 这样,你便可以使用所要连接到的对象的名称作为网络中的完全限定地址,例如 AGNode1.ag.wgcluster.example.comThis allows you to use the name of the object you want to connect to as a fully qualified address within your network, such as AGNode1.ag.wgcluster.example.com.

若要配置 DNS 后缀,请执行以下步骤:To configure the DNS suffix, follow these steps:

  1. 通过 RDP 连接到第一个节点,然后打开服务器管理器。RDP into your first node and open Server Manager.

  2. 选择“本地服务器”,然后在“计算机名”下选择你的虚拟机名称。 Select Local Server and then select the name of your virtual machine under Computer name.

  3. 选择“若要重命名此计算机...”下的“更改...”。 Select Change... under To rename this computer....

  4. 将工作组名称更改为有意义的名称,例如 AGWORKGROUPChange the name of the workgroup name to be something meaningful, such as AGWORKGROUP:

    更改工作组名称

  5. 选择“更多...”打开“DNS 后缀和 NetBIOS 计算机名”对话框。 Select More... to open the DNS Suffix and NetBIOS Computer Name dialog box.

  6. 在“此计算机的主 DNS 后缀”下键入 DNS 后缀的名称(例如 ag.wgcluster.example.com),然后选择“确定”: Type the name of your DNS suffix under Primary DNS suffix of this computer, such as ag.wgcluster.example.com and then select OK:

    添加 DNS 后缀

  7. 确认“计算机全名”现在显示了 DNS 后缀,然后选择“确定”以保存更改: Confirm that the Full computer name is now showing the DNS suffix, and then select OK to save your changes:

    添加 DNS 后缀

  8. 根据提示重新启动服务器。Reboot the server when you are prompted to do so.

  9. 在要用于可用性组的任何其他节点上重复这些步骤。Repeat these steps on any other nodes to be used for the availability group.

编辑主机文件Edit host file

由于没有 Active Directory,无法对 Windows 连接进行身份验证。Since there is no active directory, there is no way to authenticate windows connections. 因此,请通过使用文本编辑器编辑主机文件来分配信任。As such, assign trust by editing the host file with a text editor.

若要编辑主机文件,请执行以下步骤:To edit the host file, follow these steps:

  1. 通过 RDP 连接到虚拟机。RDP into your virtual machine.

  2. 使用“文件资源管理器”转到 c:\windows\system32\drivers\etcUse File Explorer to go to c:\windows\system32\drivers\etc.

  3. 右键单击“hosts”文件,并选择使用记事本(或任何其他文本编辑器)打开该文件。 Right-click the hosts file and open the file with Notepad (or any other text editor).

  4. 在该文件的末尾,以 IP Address, DNS Suffix #comment 格式为每个节点、可用性组和侦听器各添加一个条目,如下所示:At the end of the file, add an entry for each node, the availability group, and the listener in the form of IP Address, DNS Suffix #comment like:

    10.0.0.4 AGNode1.ag.wgcluster.example.com #Availability group node
    10.0.0.5 AGNode2.ag.wgcluster.example.com #Availability group node
    10.0.0.6 AGWGAG.ag.wgcluster.example.com #Cluster IP
    10.0.0.7 AGListener.ag.wgcluster.example.com #Listener IP
    

    在主机文件中添加 IP 地址、群集和侦听器的条目

设置权限Set permissions

由于未使用 Active Directory 来管理权限,因此需要手动允许非内置的本地管理员帐户创建群集。Since there is no Active Directory to manage permissions, you need to manually allow a non-builtin local administrator account to create the cluster.

为此,请在每个节点上的管理 PowerShell 会话中运行以下 PowerShell cmdlet:To do so, run the following PowerShell cmdlet in an administrative PowerShell session on every node:


new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1

创建故障转移群集Create the failover cluster

此步骤将创建故障转移群集。In this step, you will create the failover cluster. 如果你不熟悉这些步骤,请参阅故障转移群集教程If you're unfamiliar with these steps, you can follow them from the failover cluster tutorial.

该教程中的步骤与应该对工作组群集执行的操作存在很大的差别:Notable differences between the tutorial and what should be done for a workgroup cluster:

  • 运行群集验证时取消选中“存储”和“存储空间直通”。 Uncheck Storage, and Storage Spaces Direct when running the cluster validation.
  • 将节点添加到群集时,请添加完全限定的名称,例如:When adding the nodes to the cluster, add the fully qualified name, such as:
    • AGNode1.ag.wgcluster.example.com
    • AGNode2.ag.wgcluster.example.com
  • 取消选中“将所有符合条件的存储添加到群集中”。 Uncheck Add all eligible storage to the cluster.

创建群集后,分配静态群集 IP 地址。Once the cluster has been created, assign a static Cluster IP address. 为此,请执行以下步骤:To do so, follow these steps:

  1. 在某个节点上打开“故障转移群集管理器”,选择群集,右键单击“群集核心资源”下的“名称: <ClusterNam>”,然后选择“属性”。 On one of the nodes, open Failover Cluster Manager, select the cluster, right-click the Name: <ClusterNam> under Cluster Core Resources and then select Properties.

    针对该群集名称启动属性配置

  2. 在“IP 地址”下选择 IP 地址,然后选择“编辑”。 Select the IP address under IP Addresses and select Edit.

  3. 选择“使用静态”,提供群集的 IP 地址,然后选择“确定”: Select Use Static, provide the IP address of the cluster, and then select OK:

    为群集提供静态 IP 地址

  4. 验证设置是否正确,然后选择“确定”以保存设置: Verify that your settings look correct, and then select OK to save them:

    验证群集属性

创建云见证Create a cloud witness

此步骤配置云共享见证。In this step, configure a cloud share witness. 如果你不熟悉这些步骤,请参阅故障转移群集教程If you're unfamiliar with the steps, see the failover cluster tutorial.

启用可用性组功能Enable availability group feature

此步骤启用可用性组功能。In this step, enable the availability group feature. 如果你不熟悉这些步骤,请参阅可用性组教程If you're unfamiliar with the steps, see the availability group tutorial.

创建密钥和证书Create keys and certificate

此步骤创建 SQL 登录名在加密的终结点上使用的证书。In this step, create certificates that a SQL login uses on the encrypted endpoint. 在每个节点上创建一个文件夹(例如 c:\certs)用于保存证书备份。Create a folder on each node to hold the certificate backups, such as c:\certs.

若要配置第一个节点,请执行以下步骤:To configure the first node, follow these steps:

  1. 打开“SQL Server Management Studio”并连接到第一个节点,例如 AGNode1Open SQL Server Management Studio and connect to your first node, such as AGNode1.

  2. 打开“新建查询”窗口,在更新为复杂的安全密码后运行以下 Transact-SQL (T-SQL) 语句: Open a New Query window and run the following Transact-SQL (T-SQL) statement after updating to a complex and secure password:

    USE master;  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWOrd123!';  
    GO
    
    --create a cert from the master key
    USE master;  
    CREATE CERTIFICATE AGNode1Cert   
      WITH SUBJECT = 'AGNode1 Certificate';  
    GO  
    
    --Backup the cert and transfer it to AGNode2
    BACKUP CERTIFICATE AGNode1Cert TO FILE = 'C:\certs\AGNode1Cert.crt';  
    GO  
    
  3. 接下来创建 HADR 终结点,并运行以下 Transact-SQL (T-SQL) 语句来使用证书进行身份验证:Next, create the HADR endpoint, and use the certificate for authentication by running this Transact-SQL (T-SQL) statement:

    --CREATE or ALTER the mirroring endpoint
    CREATE ENDPOINT hadr_endpoint  
      STATE = STARTED  
      AS TCP (  
         LISTENER_PORT=5022  
         , LISTENER_IP = ALL  
      )   
      FOR DATABASE_MIRRORING (   
         AUTHENTICATION = CERTIFICATE AGNode1Cert  
         , ENCRYPTION = REQUIRED ALGORITHM AES  
         , ROLE = ALL  
      );  
    GO  
    
  4. 使用“文件资源管理器”转到证书所在的文件位置,例如 c:\certsUse File Explorer to go to the file location where your certificate is, such as c:\certs.

  5. 在第一个节点上手动创建证书的副本(例如 AGNode1Cert.crt),并将其传输到第二个节点上的同一位置。Manually make a copy of the certificate, such as AGNode1Cert.crt, from the first node, and transfer it to the same location on the second node.

若要配置第二个节点,请执行以下步骤:To configure the second node, follow these steps:

  1. 使用“SQL Server Management Studio”连接到第二个节点,例如 AGNode2Connect to the second node with SQL Server Management Studio, such as AGNode2.

  2. 在“新建查询”窗口中,在更新为复杂的安全密码后运行以下 Transact-SQL (T-SQL) 语句: In a New Query window, run the following Transact-SQL (T-SQL) statement after updating to a complex and secure password:

    USE master;  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWOrd123!';  
    GO 
    
    --create a cert from the master key
    USE master;  
    CREATE CERTIFICATE AGNode2Cert   
      WITH SUBJECT = 'AGNode2 Certificate';  
    GO  
    --Backup the cert and transfer it to AGNode1
    BACKUP CERTIFICATE AGNode2Cert TO FILE = 'C:\certs\AGNode2Cert.crt';  
    GO
    
  3. 接下来创建 HADR 终结点,并运行以下 Transact-SQL (T-SQL) 语句来使用证书进行身份验证:Next, create the HADR endpoint, and use the certificate for authentication by running this Transact-SQL (T-SQL) statement:

    --CREATE or ALTER the mirroring endpoint
    CREATE ENDPOINT hadr_endpoint  
      STATE = STARTED  
      AS TCP (  
         LISTENER_PORT=5022  
         , LISTENER_IP = ALL  
      )   
      FOR DATABASE_MIRRORING (   
         AUTHENTICATION = CERTIFICATE AGNode2Cert  
         , ENCRYPTION = REQUIRED ALGORITHM AES  
         , ROLE = ALL  
      );  
    GO  
    
  4. 使用“文件资源管理器”转到证书所在的文件位置,例如 c:\certsUse File Explorer to go to the file location where your certificate is, such as c:\certs.

  5. 在第二个节点上手动创建证书的副本(例如 AGNode2Cert.crt),并将其传输到第一个节点上的同一位置。Manually make a copy of the certificate, such as AGNode2Cert.crt, from the second node, and transfer it to the same location on the first node.

如果群集中包含任何其他节点,请在这些节点上重复上述步骤,同时请修改相应的证书名称。If there are any other nodes in the cluster, repeat these steps there also, modifying the respective certificate names.

创建登录名Create logins

证书身份验证将用来跨节点同步数据。Certificate authentication is used to synchronize data across nodes. 为实现此目的,请为另一个节点创建登录名,为该登录名创建用户,为该登录名创建证书以使用备份证书,然后在镜像终结点上授予连接权限。To allow this, create a login for the other node, create a user for the login, create a certificate for the login to use the backed-up certificate, and then grant connect on the mirroring endpoint.

为此,请先在第一个节点(例如 AGNode1)上运行以下 Transact-SQL (T-SQL) 查询:To do so, first run the following Transact-SQL (T-SQL) query on the first node, such as AGNode1:

--create a login for the AGNode2
USE master;  
CREATE LOGIN AGNode2_Login WITH PASSWORD = 'PassWord123!';  
GO  

--create a user from the login
CREATE USER AGNode2_User FOR LOGIN AGNode2_Login;  
GO  

--create a certificate that the login uses for authentication
CREATE CERTIFICATE AGNode2Cert  
   AUTHORIZATION AGNode2_User  
   FROM FILE = 'C:\certs\AGNode2Cert.crt'  
GO 

--grant connect for login
GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [AGNode2_login];  
GO

接下来,在第二个节点(例如 AGNode2)上运行以下 Transact-SQL (T-SQL) 查询:Next, run the following Transact-SQL (T-SQL) query on the second node, such as AGNode2:

--create a login for the AGNode1
USE master;  
CREATE LOGIN AGNode1_Login WITH PASSWORD = 'PassWord123!';  
GO  

--create a user from the login
CREATE USER AGNode1_User FOR LOGIN AGNode1_Login;  
GO  

--create a certificate that the login uses for authentication
CREATE CERTIFICATE AGNode1Cert  
   AUTHORIZATION AGNode1_User  
   FROM FILE = 'C:\certs\AGNode1Cert.crt'  
GO 

--grant connect for login
GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [AGNode1_login];  
GO

如果群集中包含任何其他节点,请在这些节点上重复上述步骤,同时请修改相应的证书名称和用户名。If there are any other nodes in the cluster, repeat these steps there also, modifying the respective certificate and user names.

配置可用性组Configure availability group

此步骤配置可用性组并向其添加数据库。In this step, configure your availability group, and add your databases to it. 目前请不要创建侦听器。Do not create a listener at this time. 如果你不熟悉这些步骤,请参阅可用性组教程If you're not familiar with the steps, see the availability group tutorial. 确保启动故障转移和故障回复,以验证一切是否按预期方式进行。Be sure to initiate a failover and failback to verify that everything is working as it should be.

Note

如果在同步过程中发生失败,可能需要暂时授予 NT AUTHORITY\SYSTEM sysadmin 权限以便在第一个节点(例如 AGNode1)上创建群集资源。If there is a failure during the synchronization process, you may need to grant NT AUTHORITY\SYSTEM sysadmin rights to create cluster resources on the first node, such as AGNode1 temporarily.

配置负载均衡器Configure load balancer

最后一个步骤使用 Azure 门户PowerShell 配置负载均衡器In this final step, configure the load balancer using either the Azure portal or PowerShell