linux中mysql群集_阅读无群集可用性组中的Scale可用性组

linux中mysql群集

In this article, we will explore configuring Read Scale Availability Group that does not require a failover clustering configuration. It helps to scale read-only connections to the secondary replica in a cluster less configuration.

在本文中，我们将探索配置Read Scale可用性组，该组不需要故障转移群集配置。 它有助于在无群集配置中扩展与辅助副本的只读连接。

介绍 (Introduction)

SQL Server Always On is a high availability and disaster recovery solution available starting from SQL Server 2012. It requires two or more replica servers configured in Windows Server Failover Cluster. Suppose we have a three replica node SQL Always On. In this case, each SQL Server should be a part of a failover cluster. SQL Server Always on Availability Groups uses failover clustering to determine the role of an available replica and determine the failover conditions. Each availability group is a resource in the failover cluster, and it continuously monitors the health of the primary replica.

SQL Server Always On是从SQL Server 2012开始提供的高可用性和灾难恢复解决方案。它需要在Windows Server故障转移群集中配置两个或多个副本服务器。 假设我们有一个三个副本节点SQL Always On。 在这种情况下，每个SQL Server应该是故障转移群集的一部分。 SQL Server始终可用性组使用故障转移群集来确定可用副本的角色并确定故障转移条件。 每个可用性组都是故障转移群集中的资源，并且它连续监视主副本的运行状况。

Recently I came up with a scenario in which a client wants to use SQL Server Always on the feature but do not want to use failover clustering between replicas. It raises a question to me –

最近，我想到了一种方案，其中客户端希望在功能上使用SQL Server Always，但是不想在副本之间使用故障转移群集。 这向我提出了一个问题–

Can we configure Clusterless Availability group in SQL Server?

我们可以在SQL Server中配置无群集可用性组吗？

Let’s consider it is possible to create a clusterless availability group replica for SQL Server. Client further requires to redirect all read-only connections coming to primary replica to the secondary replica. It again raises a few questions. We might need multiple secondary replicas without failover clusters. Our motive is to offload the load from the secondary read-only replica as well.

让我们考虑可以为SQL Server创建无群集可用性组副本。 客户端还需要将所有进入主副本的只读连接重定向到辅助副本。 它再次提出了一些问题。 我们可能需要没有故障转移群集的多个辅助副本。 我们的动机是也要从辅助只读副本中减轻负载。

• Can we redirect read-only connections to Secondary replica in a clusterless availability group configuration?
  我们可以在无群集可用性组配置中将只读连接重定向到辅助副本吗？
• Can we configure multiple replicas for reporting purpose in a clusterless availability group configuration?
  我们可以在无集群可用性组配置中配置多个副本以用于报告目的吗？
• Can we configure Read Scale Availability Group?
  我们可以配置读取规模可用性组吗？

SQL Server failover clustering increases the complexity to deploy and sometimes we do not want to configure it. For example, suppose we have a system in the demilitarized zone (DMZ) for reporting purpose. We do not want to configure failover clustering as it involves opening multiple ports between networks.

SQL Server故障转移群集增加了部署的复杂性，有时我们不想对其进行配置。 例如，假设我们在非军事区（DMZ）中有一个用于报告目的的系统。 我们不希望配置故障转移群集，因为它涉及在网络之间打开多个端口。

In SQL Server we can configure Read-Scale availability group which do not need failover clustering between servers. The point to consider is that it is not useful for high availability solution. We can use it to scale out a read-only workload.

在SQL Server中，我们可以配置Read-Scale可用性组，该组不需要服务器之间的故障转移群集。 需要考虑的一点是，它对高可用性解决方案没有用。 我们可以使用它来扩展只读工作负载。

无群集可用性组中的扩展规模可用性组 (Read-Scale availability group in a Clusterless Availability Group)

In SQL Server 2017 we can configure clusterless Availability Group in SQL Server without failover cluster configuration on participating replicas. It can be done on both Windows as well as Linux based SQL Servers.

在SQL Server 2017中，我们可以在SQL Server中配置无群集可用性组，而无需在参与副本上配置故障转移群集。 它可以在Windows以及基于LinuxSQL Server上完成。

Suppose we have two standalone SQL Instances SQLA and SQLB. We want to configure Read Scale availability group in between these standalone SQL instances. We further want to redirect all read transactions on the secondary replica.

假设我们有两个独立SQL实例SQLA和SQLB。 我们要在这些独立SQL实例之间配置Read Scale可用性组。 我们还想重定向辅助副本上的所有读取事务。

在无群集可用性组配置中配置Read-Scale可用性组的步骤 (Steps to configure Read-Scale availability groups in a clusterless availability group configuration)

Step 1: We need to enable SQL Always On feature on both standalone SQL instances. To do so, RDP to each server and Open SQL Server Configuration Manager. In this go to SQL Server instance properties and Enable Always On Availability Groups.

步骤1：我们需要在两个独立SQL实例上启用SQL Always On功能。 为此，将RDP连接到每个服务器并打开SQL Server配置管理器 。 在此转到SQL Server实例属性，然后启用“始终启用可用性组”。

We do not have a failover cluster between SQLA and SQLB instances. In the screenshot also, you can see that it asks for Windows failover cluster name. We can enable it in SQL Server 2017 or above without failover cluster. SQL Server takes SQL instance name by default in this clusterless availability group replica for Read Scale Availability Group.

我们在SQLA和SQLB实例之间没有故障转移群集。 同样在屏幕截图中，您可以看到它询问Windows故障转移群集名称。 我们可以在没有故障转移群集SQL Server 2017或更高版本中启用它。 默认情况下，SQL Server在此无集群可用性组副本中采用SQL实例名称作为Read Scale Availability Group。

Step 2: In this step, we need to configure SQL Server Availability Groups between both instances. Connect to the instance which will work as a Primary replica.

步骤2：在此步骤中，我们需要在两个实例之间配置SQL Server可用性组。 连接到将用作主副本的实例。

• Note: I am using SSMS v18 in this article. You should use the latest SSMS version else you might not get all options in GUI mode.
• 注意 ：本文使用的是SSMS v18。 您应该使用最新的SSMS版本，否则可能无法在GUI模式下获得所有选项。

Go to Always On High Availability and click on New Availability Group Wizard.

转到始终处于高可用性状态 ，然后单击新建可用性组向导。

It launches a wizard to configure the availability group.

它启动一个向导来配置可用性组。

• Availability group name: Specify a suitable name for the availability group
  可用性组名称：为可用性组指定一个合适的名称
• • Windows Server Failover Cluster: Choose this value if SQL instances (replicas) are in a failover cluster configuration
    Windows Server故障转移群集：如果SQL实例（副本）在故障转移群集配置中，请选择此值
  • External: if we use external cluster topology for SQL instances, we need to select this value. Example: Linux
    外部：如果我们将外部群集拓扑用于SQL实例，则需要选择此值。 示例：Linux

  • None: If SQL instances are not configured in a failover cluster and we need to configure Read Scale Availability Group, select the cluster type as NONE

    无：如果未在故障转移群集中配置SQL实例，而我们需要配置Read Scale可用性组，则将群集类型选择为NONE

    

In the next page, we need to select a database for the Read Scale Availability Group. It should meet following pre-requisites.

在下一页中，我们需要为Read Scale Availability组选择一个数据库。 它应满足以下先决条件。

• It should be a user database in read-write mode
  它应该是处于读写模式的用户数据库
• It should be in full recovery model
  应该处于完全恢复模式
• We should have taken at least one full backup
  我们应该至少进行了一次完整备份
• It should not be configured with Auto_close option
  不应使用Auto_close选项进行配置

If database meets prerequisites, we get status Meets prerequisites.

如果数据库满足先决条件，我们将获得状态满足先决条件。

Step 3: In the next page, we specify the following things for Read Scale Availability Group.

步骤3：在下一页中，我们为Read Scale Availability Group指定以下内容。

• Availability Replicas: Specify all available replicas we want to add in an availability group 可用性副本：指定我们要在可用性组中添加的所有可用副本
• Initial Role: Specify the initial role (primary or secondary) on each replica. We can have only one primary replica 初始角色：在每个副本上指定初始角色（主要或次要）。 我们只能有一个主副本
• Failover Mode: We can have only 故障转移模式： Read Scale Availability组只能有“ Manual failover mode for Read Scale Availability Group 手动”故障转移模式
• Availability Mode: Select the data synchronization mode between both replicas. We can choose either Synchronous or Asynchronous data commit availability mode 可用性模式：选择两个副本之间的数据同步模式。 我们可以选择同步或异步数据提交可用性模式

• Readable Secondary: In the secondary role of a replica, we can allow all connections for read access with Readable Secondary- Yes

  可读辅助服务器：在副本服务器的辅助角色中，我们可以使用可读辅助服务器允许所有连接进行读取访问- 是

  

SQL Server automatically creates the Endpoints for both replicas automatically using this wizard. The default format for endpoints is TCP://[SQL Instance Name]: Port.

SQL Server使用此向导自动为两个副本自动创建端点 。 端点的默认格式为TCP：// [SQL实例名称]：端口。

• Note: SQL Server services should be running with a service account. This service account acts as an Endpoint owner also.
• 注意： SQL Server服务应与服务帐户一起运行。 此服务帐户还充当端点所有者。

We will skip other options as of now. We need to configure them in the later part of this article.

到目前为止，我们将跳过其他选项。 我们需要在本文的后面部分中对其进行配置。

Step 4: In the next step, we configure initial data synchronization for Read Scale Availability Group. We will choose Automatic Seeding in Read Scale Availability Group. In this SQL Server automatically creates a database on secondary replica and sync it with the primary replica. You should have similar database drives (default path) on both instances. You can read more about automatic seeding in SQL Server 2016 Always On Availability Group with Direct Seeding article.

步骤4：在下一步中，我们为Read Scale Availability Group配置初始数据同步。 我们将在“读取规模”可用性组中选择“自动播种”。 在此SQL Server中，将在辅助副本上自动创建一个数据库，并将其与主副本同步。 两个实例上都应具有类似的数据库驱动器（默认路径）。 您可以在带有直接播种SQL Server 2016永远在线可用性组文章中阅读有关自动播种的更多信息。

Step 5: It performs certain validation checks such as free disk space on the secondary replica, the existence of a secondary database, compatibility of database file locations. If there are any errors, we need to fix them before we move on.

步骤5：它执行某些验证检查，例如辅助副本上的可用磁盘空间，辅助数据库的存在，数据库文件位置的兼容性。 如果有任何错误，我们需要先修复它们，然后再继续。

In the following screenshot, we have a warning for SQL listener configuration because we have not created it yet. We can proceed with this warning at this point for Read Scale Availability Group.

在下面的屏幕截图中，我们为SQL侦听器配置提供了警告，因为我们尚未创建它。 此时，我们可以对Read Scale Availability Group进行此警告。

In the last step, we can review the configuration and click on finish to start setting up read- scale availability groups. We can also generate scripts to review. Let’s generate script and see the action with the t-SQL.

在最后一步中，我们可以检查配置，然后单击“完成”以开始设置读取规模的可用性组。 我们还可以生成脚本进行审查。 让我们生成脚本并查看t-SQL的操作。

Now you can open right click on availability group and view dashboard to look database synchronization in a read-scale availability group. We can check synchronization status using the following query on primary replica also.

现在，您可以打开鼠标右键单击可用性组并查看仪表板，以在读规模的可用性组中查看数据库同步。 我们还可以在主副本上使用以下查询来检查同步状态。

 SELECT 
  ar.replica_server_name,
  adc.database_name, 
  ag.name AS ag_name, 
  drs.is_local, 
  drs.is_primary_replica, 
  drs.synchronization_state_desc, 
  drs.is_commit_participant, 
  drs.synchronization_health_desc, 
  drs.recovery_lsn, 
  drs.truncation_lsn
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc 
  ON drs.group_id = adc.group_id AND 
  drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
  ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar 
  ON drs.group_id = ar.group_id AND 
  drs.replica_id = ar.replica_id
ORDER BY 
  ag.name, 
  ar.replica_server_name, 
  adc.database_name;

We can see that both the replicas in Read Scale Availability Group are synchronized and healthy.

我们可以看到，Read Scale Availability Group中的两个副本都是同步且运行状况良好。

As of now, we have created Read Scale Availability groups in a clusterless configuration. In this cluster less availability group, we cannot have automatic or planned manual failover. If we try to do failover using failover wizard (right click on primary replica and failover), we get the following error message.

到目前为止，我们已经在无集群配置中创建了“读取规模可用性”组。 在此群集较少可用性组中，我们无法进行自动或计划的手动故障转移。 如果我们尝试使用故障转移向导进行故障转移（右键单击主副本并进行故障转移），则会收到以下错误消息。

We need to initiate force failover with allow data loss in a clusterless availability group. We might have data loss in this depending upon the lag between the primary and secondary replica.

我们需要启动强制故障转移，并允许无群集可用性组中的数据丢失。 根据主副本和辅助副本之间的延迟，我们可能会有数据丢失。

To initiate forced failover in a Read Scale Availability Group, connect to secondary replica and execute the following command.

要在Read Scale Availability组中启动强制故障转移，请连接到辅助副本并执行以下命令。

 ALTER AVAILABILITY GROUP TestAG FORCE_FAILOVER_ALLOW_DATA_LOSS;

It does the force failover from a primary replica to secondary replica with possible data loss. Execute the command to check synchronization on the primary replica, and we can see the status as Not Synchronizing.

它会执行从主副本到次副本的强制故障转移，并可能导致数据丢失。 执行命令以检查主副本上的同步，我们可以看到状态为Not Synchronizing 。

Once failover is done for clusterless availability group, connect to the secondary replica and expand Availability Databases.

对无集群可用性组完成故障转移后，连接到辅助副本并展开“ 可用性数据库” 。

Right click on the availability database and Resume Data Movement to synchronized data again between both replicas in a clusterless availability group.

右键单击可用性数据库，然后单击“ 继续数据移动”以再次在无集群可用性组中的两个副本之间同步数据。

It opens Resume Data Movement wizard. Click on Continue executing after error and Ok.

它打开恢复数据移动向导。 单击出现错误后继续执行，然后单击确定 。

Rerun the query and we can see the status is Synchronized again.

重新运行查询，我们可以看到状态再次为“已同步” 。

无集群可用性组中的只读路由 (Read-Only Routing in Clusterless Availability Group)

In the article, How to Configure Read-Only Routing for an Availability Group in SQL Server 2016, we explored configuring Read-only routing for an availability group having a failover clustering. In the previous section, we configured cluster less Always On Availability Groups.

在如何在SQL Server 2016中为可用性组配置只读路由的文章中，我们探讨了为具有故障转移群集的可用性组配置只读路由。 在上一节中，我们为群集配置了较少的Always On可用性组。

We need to have a SQL Listener to create the Read-Only Routing list. Usually, we require a cluster resource name to configure a SQL Listener. It creates a computer object in the active directory holding an IP address of replicas.

我们需要有一个SQL侦听器来创建只读路由列表。 通常，我们需要群集资源名称来配置SQL侦听器。 它在活动目录中创建一个计算机对象，其中包含副本IP地址。

We can still configure a SQL Listener in a clusterless availability group. We need to use the following things in SQL Listener configuration.

我们仍然可以在无群集可用性组中配置SQL侦听器。 我们需要在SQL Listener配置中使用以下内容。

• IP Address of primary replica
  主副本的IP地址
• Port number of the primary replica
  主副本的端口号

Here is a catch. In an availability Group, SQL Listener contains the IP address and port number of the primary replica. If we do a force failover, we need to drop and create the SQL listener to have an IP address and port of the new primary replica. We might also need to register SQL Listener again in the DNS to point out the correct IP address.

这是一个陷阱。 在可用性组中，SQL侦听器包含主副本的IP地址和端口号。 如果执行强制故障转移，则需要删除并创建SQL侦听器，以具有新主副本的IP地址和端口。 我们可能还需要在DNS中再次注册SQL侦听器，以指出正确的IP地址。

Execute the command on the primary replica to configure SQL Listener with IP address and port of primary replica in Read-Scale Availability Group.

在主副本上执行命令以使用Read-Scale Availability Group中的主副本的IP地址和端口配置SQL Listener。

USE [master]
GO
ALTER AVAILABILITY GROUP [TestAG]
ADD LISTENER N'ClusterLessAG' (
WITH IP
((N'10.xxx.xxx.xx', N'255.255.255.0')
)
, PORT=1433);
GO

在只读规模可用性组中配置到辅助副本的只读路由 (Configure Read-Only Routing to the secondary replica in Read Scale Availability Group)

We need to configure Read-Only Routing URL and Routing List for redirecting read-only connections to the secondary replica in a clusterless Read Scale Availability Group. We can use Alter Availability Group command in this case.

我们需要配置只读路由URL和路由列表，以将只读连接重定向到无群集读规模可用性组中的辅助副本。 在这种情况下，我们可以使用Alter Availability Group命令。

In the following queries, you can notice the following things.

在以下查询中，您会注意到以下内容。

• For a replica with a secondary role in Read-Scale Availability Group, it’s Read-Only Routing URL pointing to the same instance. We need to note here that the port in the routing URL is the port for SQL Services. You can verify that from the SQL Server configuration manager and TCP\IP properties. Do not put endpoint port number in this else you get following error message

  对于在只读规模可用性组中具有辅助角色的副本，它是指向同一实例的只读路由URL。 在这里我们需要注意，路由URL中的端口是SQL Services的端口。 您可以从SQL Server配置管理器和TCP \ IP属性中进行验证。 不要在此放置端点端口号，否则会收到以下错误消息

  

• For a replica with a primary role, it’s Read-Only Routing URL pointing to secondary replica instance for Read Scale Availability Group
  对于具有主要角色的副本，它是指向Read Scale Availability组的辅助副本实例的只读路由URL。

Execute the following command on Primary replica.

在主副本上执行以下命令。

Alter Availability Group [TestAG]
Modify Replica On ‘DB01’ 
with(Secondary_Role(Read_Only_Routing_URL=N'TCP://DB01.Domain1.com:1433'))
 
Alter Availability Group [TestAG]]
Modify Replica On ‘DB01’
with(Secondary_Role(Read_Only_Routing_URL=N' N'TCP://DB02.Domain1.com:1433'))
 
Alter Availability Group [TestAG]]
Modify Replica On ‘DB01’
with(Primary_Role(Read_Only_Routing_List=(N'DB02')))
 
Alter Availability Group [TestAG]]
Modify Replica On 'DB02' 
with(Primary_Role(Read_Only_Routing_List=(N'DB01')))

You can execute the following query to get a list of a replica (Primary, secondary), Read-only Replica and Routing URL.

您可以执行以下查询以获取副本列表（主副本，辅助副本），只读副本和路由URL。

SELECT    AVGSrc.replica_server_name AS SourceReplica   
    , AVGRepl.replica_server_name AS ReadOnlyReplica
    , AVGRepl.read_only_routing_url AS RoutingURL
    , AVGRL.routing_priority AS RoutingPriority
FROM sys.availability_read_only_routing_lists AVGRL
INNER JOIN sys.availability_replicas AVGSrc ON AVGRL.replica_id = AVGSrc.replica_id
INNER JOIN sys.availability_replicas AVGRepl ON AVGRL.read_only_replica_id = AVGRepl.replica_id
INNER JOIN sys.availability_groups AV ON AV.group_id = AVGSrc.group_id
ORDER BY SourceReplica

In the following query, you can see that for source replica DB01 (Primary replica) routes all DB connections to secondary replica DB02 using RoutingURL.

在以下查询中，您可以看到，对于源副本DB01（主副本），使用路由URL将所有数据库连接路由到辅助副本DB02。

SourceReplica	ReadOnlyReplica	RoutingURL	RoutingPriority
DB01	DB02	TCP://DB02.indigo.in:1433	1
DB02	DB01	TCP://DB01.indigo.in:1433	1

来源副本	只读副本	路由URL	路由优先级
DB01	DB02	TCP：//DB02.indigo.in：1433	1个
DB02	DB01	TCP：//DB01.indigo.in：1433	1个

Let’s verify the connection redirection in read scale clusterless availability group. In this case, we cannot use SQL Listener to connect with the primary replica. We need to use the primary replica instance name to connect.

让我们验证读比例无集群可用性组中的连接重定向。 在这种情况下，我们不能使用SQL Listener来连接主副本。 我们需要使用主副本实例名称进行连接。

Open the command prompt and execute the following command to connect SQL Server with SQLCMD using windows authentication and Read-only mode.

打开命令提示符并执行以下命令，以使用Windows身份验证和只读模式将SQL Server与SQLCMD连接。

>sqlcmd -S DB01,1433 -E -d adventureworks2014 -K Read-only

Run the query Select @@Servername, and it returns the secondary server name because the connection redirects to the secondary replica.

运行查询Select @@ Servername，它返回辅助服务器名称，因为连接重定向到辅助副本。

结论 (Conclusion)

In this article, we explored the configuration of Read Scale Availability Group on clusterless availability group. It is a useful enhancement available from SQL Server 2017 onwards. You should explore this scenario in your environment. If you had comments or questions, feel free to leave them in the comments below.

在本文中，我们探讨了无集群可用性组上的Read Scale可用性组的配置。 从SQL Server 2017起，它是一项有用的增强功能。 您应该在您的环境中探索这种情况。 如果您有任何意见或问题，请随时将其留在下面的评论中。

翻译自: https://www.sqlshack.com/read-scale-availability-group-in-a-clusterless-availability-group/

linux中mysql群集