SQL Server AlwaysOn客户端连接

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介:

SQL Server AlwaysOn客户端连接

杜飞

   在前面的文章中介绍到,管理员可以创建一个可用性组侦听器来提供到可用性组的数据库的连接。通过一个虚拟网络名称,客户端可连接到此名称以访问可用性组的主副本或者是辅助副本中的数据库。可用性组侦听器由域名系统 (DNS) 侦听器名称、指定的侦听器端口以及一个或多个 IP 地址组成。可用性组侦听器仅支持 TCP 协议。 在域和 NetBIOS 中,侦听器的 DNS 名称也必须唯一。当创建新的可用性组侦听器时,该侦听器将成为群集中的资源,并具有关联的虚拟网络名称 (VNN)、虚拟 IP (VIP) 和可用性组依赖项。 客户端使用 DNS 将 VNN 解析为多个 IP 地址,然后尝试连接到每个地址,直到连接请求成功或超时。

   当配置可用性组侦听器时,必须指定一个端口。可以将默认端口配置为1433,以便允许使用客户端连接字符串以达到简化目的。 如果使用1433,则无需在连接字符串中指定端口号。此外,由于每个可用性组侦听器都将具有一个独立的虚拟网络名称,因此,在单个WSFC上配置的每个可用性组侦听器都可以配置为引用相同的默认端口1433。还可以指定一个非标准的侦听器端口,但是,这意味着还需要在连接到可用性组侦听器时,在您的连接字符串中显式指定一个目标端口。还需要为非标准端口打开对防火墙的权限。    
如果其中一个 SQL Server 实例已正在通过实例侦听器侦听TCP端口1433,且在侦听端口1433的计算机上没有任何其他服务(包括其他 SQL Server 实例),则不会与可用性组侦听器的端口导致冲突。这是因为,可用性组监听器在相同的服务过程中可以共享同一个 TCP 端口。但是,不应将多个 SQL Server 实例(并行)配置为侦听同一个端口。

使用侦听器连接到主副本

   若要使用可用性组侦听器连接到主副本以进行读写访问,连接字符串应指定可用性组侦听器DNS名称。如果可用性组主副本变为新副本,则将断开使用可用性组侦听器的网络名称的现有连接。然后,将到可用性组侦听器的新连接定向到新的主副本。如下所示是针对 ADO.NET 访问接口 (System.Data.SqlClient) 的基本连接字符串的一个示例:    
Server=tcp: AGListener,1433;Database=MyDB;IntegratedSecurity=SSPI     
当然,仍然可以选择直接引用主副本或辅助副本的 SQL Server 实例名称,而不使用可用性组侦听器服务器名称,但如果您选择这样做,将会丢失新连接(自动定向到当前主副本)的优势。 还将失去只读路由的优势。

   若要创建某个可用性组的第一个可用性组侦听器,强烈建议使用 SQL Server Management Studio、Transact-SQL 或 SQL Server PowerShell。  除非必要情况,例如创建附加侦听器,否则,应避免直接在 WSFC 群集中创建侦听器。

关于创建侦听器更多内容可以参考:http://technet.microsoft.com/zh-cn/library/hh213080.aspx

为可用性组配置只读路由

   用户可以使用Transact-SQL或PowerShell在SQL Server 2012中配置AlwaysOn可用性组的只读路由。“只读路由”指的是SQL Server将符合条件的只读连接请求路由到可用的AlwaysOn可读辅助副本(即配置为在辅助角色下运行时允许只读工作负荷的副本)的能力。为支持只读路由,可用性组必须具备可用性组侦听器。只读客户端必须将其连接请求定向到此侦听器,并且客户端的连接字符串必须将应用程序意向指定为“只读”。也就是说,它们必须是“读意向连接请求”。 要让只读操作能“透明”地被自动转向辅助副本,必须解决下面三个问题:   
1.客户端要标明自己发来的操作是“只读”操作。这个判定是程序开发员在编写程序的时候,通过ApplicationIntent关键字指定的。不是SQL Server端来判定的。    
2.辅助数据库要被配成可读模式。    
3.客户端的连接,要能够被重定向到可读辅助副本。AlwaysOn是用“只读路由”机制来实现的。

ApplicationIntent关键字
为了支持AlwaysOn可用性组的可读辅助数据库功能,连接SQLServer 2012实例的客户端可以使用新的关键字“ApplicationIntent”来表示客户端发出请求的类型:“读/写”或是“只读”。这个关键字只能被设置为以下两个值中的一个。    
ApplicationIntent=ReadOnly    
ApplicationIntent=ReadWrite    
目前支持该关键字的驱动有:    
· SQLNCLI11 ODBC和OLEDB    
· .NET Framework 4.5和4.0中System.Data.SqlClient    
· Microsoft JDBC Driver for SQL Server 4.0    
设置ApplicationIntent关键字的方法取决于你使用的客户端工具。如果是一个自己开发的应用程序,就需要在连接字符串中指定这个关键字,例如:    
Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover

如果是SQLServer Management Studio的话,可以在连接属性中加入ApplicationIntent关键字。

可用性副本的可读模式设定
   管理员可以为每个可用性副本设置其作为主角色时的连接访问类型和其作为辅助角色时的连接访问类型。在创建AlwaysOn可用性组的时候,就可以为每个副本设定其作为辅助角色的连接访问类型,在可用性组运行时也能随时对其进行更改。连接访问类型决定该辅助副本上的可用性数据库是否可以接受读操作。

辅助角色的连接访问类型分为3种:

1. NONE:辅助数据库不接受任何类型的数据访问。

2. READ_ONLY:当连接字符串中的Application Intent属性被设置成ReadOnly时,能被访问。

3. ALL。任何连接都可以连接辅助数据库,但是通过这个连接,只有读请求才能够成功执行。任何尝试写数据库的请求都会失败。这个选项主要是用于那些无法使用ApplicationIntent关键字的客户端。

ApplicationIntent并不能保证从该连接上发出的请求都一定是读操作。它只是表示了该连接上“应当”发生的请求类型。因此无论把访问类型设置为ALL或者READ_ONLY,客户端程序都可能通过这个连接发送写请求到辅助数据库上。

主副本上也有两种设置,只能在可用性组创建完毕后,以通过修改可用性副本的属性来完成。主角色的连接访问类型有两种。

1. ALL:主数据库同时允许读写连接和只读连接。这是主角色的默认行为。

2. READ_WRITE:当连接字符串的ApplicationIntent 关键字设置为 ReadWrite 或未设置时,允许此连接。不允许连接字符串ApplicationIntent关键字设置为 ReadOnly 的连接。

当主副本设置为Read_Write时,如果一个客户端连接字符串ApplicationIntent关键字设置为ReadOnly,又要指定连接主副本,那这个连接会被拒绝。这样的设计会保证主副本不被应该由辅助副本完成的应用负载所干扰。

只读路由

   所谓只读路由:当客户端连接使用侦听器名字来访问SQL Server实例时,只读路由功能可以将来自客户端的只读请求从主副本上自动重定向到可读的辅助副本上去执行。客户端应用程序只需要确保连接的服务器名是侦听器的名字,而无需去关心背后到底是哪个副本响应这个请求。这个功能可以自动分流一部分主副本的工作负载,使得主副本有更多的资源处理其他读写请求。

   当我们配置好辅助数据库之后,用户有两种方法连接到只读的辅助数据库。方法一就是应用程序可以指定辅助副本的真实实例名,直接连接到该实例上。当辅助副本的连接选项设置为ALL时,客户端的连接字符串可以直接使用真实实例名,方法一明显不是只读路由。

   方法二就是在辅助副本设置成Readonly时,客户端需要将applicationintent设置为ReadOnly。

   如果可用性组有多个副本的话,你无法保证只读路由始终将所有连接全部重定向到相同的只读副本上。副本的运行状态的变化,副本的路由配置发生更改等因素都可能导致客户端连接到不同的只读副本。若要确保所有只读请求都连接到相同的只读副本,请使用指定可读辅助副本的实例名的方式来进行连接,而不要依靠只读路由。

为支持只读路由,需要配置的副本属性

   1. 对于要支持只读路由的每个可读辅助副本,需要指定“只读路由 URL”。此URL仅在本地副本在辅助角色下运行时起作用。必须根据需要在逐个副本的基础上指定只读路由 URL。每个只读路由 URL 都用于将读意向请求路由到一个特定的可读辅助副本。通常,向每个可读辅助副本分配一个只读路由URL。

只读路由URL是一个如下形式的字符串:   
'TCP://servername.domain.com:1433'    
可以看出这个URL其实就是一个端点的形式。通常这个端点指向的就是副本本身。因此你要保证客户单可以通过URL中的服务器名和端口号来访问到这个副本。这个字符串会由主副本通过“重定向TDS”送回给客户端。

   2. 对于要在其作为主副本时支持只读路由的每个可用性副本,都需要指定一个“只读路由列表”。 一个给定的只读路由列表仅在本地副本在主角色下运行时才起作用。必须根据需要在逐个副本的基础上指定此列表。 通常,每个只读路由列表中将包含各只读路由 URL,并且在列表的末尾具有本地副本的URL。对于每个用Listener名字发出的只读连接,它会先到主副本上转一圈,拿到只读辅助副本的URL以后,再连接到真正的辅助副本上。

   只读路由列表是一个表示可用性副本优先级的列表。在列表中的副本的名字都使用短名字。假设有一个包含3个副本(Node1,Node2和Node3)的可用性组。在Node1上的路由列表可能是这样的:'Node3','Node2'   
这个列表表示只读请求会先被重定向到Node3。当Node3不可用的时候,请求就会被重定向到Node2。而Node2上的列表可能会是这样的:'Node3', 'Node1'必须先设置只读路由URL,然后才能配置只读路由列表。

   读意向连接请求将被路由到当前主副本的只读路由列表上的第一个可用可读辅助副本上,是没有负载平衡的。

性能注意事项
数据滞后时间

如果您的只读工作负荷可以容忍一定程度的数据滞后,则实现对辅助副本的只读访问很有用。 在数据滞后不可接受的情况下,请考虑对主副本运行只读工作负荷。

主副本将主数据库上的更改日志记录发送到辅助副本。 在每个辅助数据库上,专用重做线程应用这些日志记录。 在读访问权限的辅助数据库上,给定的数据更改不显示在查询结果中,直到包含更改的日志记录已应用到辅助数据库并且已在主数据库上提交事务。

这意味着在主副本和辅助副本之间将会存在一定程度的滞后时间,通常只需几秒钟。 但是,在极少数情况下,例如在网络问题降低了网络吞吐量的情况下,滞后时间可能会较长。 在存在 I/O 瓶颈和数据移动操作处于挂起状态时,将增加滞后时间。 为了监视挂起的数据移动,可以使用 AlwaysOn 面板或 sys.dm_hadr_database_replica_states 动态管理视图。

只读工作负荷的影响

为只读访问配置辅助副本时,辅助数据库上的只读工作负荷占用来自重做线程的系统资源,例如 CPU 和 I/O,特别是在只读工作负荷大量占用 I/O 的情况下。

此外,辅助副本上的只读工作负荷还会阻止通过日志记录应用的数据定义语言 (DDL) 发生更改。 虽然读取操作由于行版本控制而不会占用共享锁,但这些操作会占用架构稳定性 (Sch-S) 锁,这些锁可能会阻塞正在应用 DDL 更改的重做操作。应了解与生成查询有关的最佳实践,并且在辅助数据库中应用这些最佳实践。 例如,将需要长时间运行的查询(如数据聚合)安排在低活动期间进行。

索引

若要优化可读辅助副本上的只读工作负荷,您可能需要对辅助数据库中的表创建索引。 因为您无法在辅助数据库上进行架构或数据更改,所以应在主数据库中创建索引,并且允许更改通过重做进程传输到辅助数据库。

若要监视辅助副本上的索引使用活动,请查询 sys.dm_db_index_usage_stats 动态管理视图的 user_seeksuser_scans 和 user_lookups 列。

只读访问数据库的统计信息

表和索引视图的列的统计信息用于优化查询计划。 对于可用性组,作为应用事务日志记录操作的一部分,在主数据库上创建和维护的统计信息将自动保留在辅助数据库中。 但是,辅助数据库上的只读工作负荷需要的统计信息可能与在主数据库上创建的统计信息不同。 但是,因为辅助数据库被限制为只读访问,所以无法在辅助数据库上创建统计信息。

为了解决此问题,辅助副本在 tempdb 中创建和维护辅助数据库的临时统计信息。 将在临时统计信息名称后追加后缀 _readonly_database_statistic,以便将临时统计信息与主数据库永久保存的永久统计信息加以区分。

仅 SQL Server 能够创建和更新临时统计信息。 但是,可以借助用于永久统计信息的相同工具来删除临时统计信息和监视其属性:

  • 使用 DROP STATISTICS Transact-SQL 语句删除临时统计信息。

  • 使用 sys.stats 和 sys.stats_columns 目录视图监视统计信息。 sys_stats 包含一个 is_temporary 列,用于指示哪些统计信息是永久的,哪些统计信息是临时的。

以上详细内容,可以参考:http://technet.microsoft.com/zh-cn/library/hh213002.aspx







 本文转自 dufei 51CTO博客,原文链接:http://blog.51cto.com/dufei/1386333,如需转载请自行联系原作者


相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
4月前
|
SQL 开发框架 .NET
ASP.NET连接SQL数据库:详细步骤与最佳实践指南ali01n.xinmi1009fan.com
随着Web开发技术的不断进步,ASP.NET已成为一种非常流行的Web应用程序开发框架。在ASP.NET项目中,我们经常需要与数据库进行交互,特别是SQL数据库。本文将详细介绍如何在ASP.NET项目中连接SQL数据库,并提供最佳实践指南以确保开发过程的稳定性和效率。一、准备工作在开始之前,请确保您
377 3
|
4月前
|
SQL 数据库 索引
SQL语句实现投影连接:方法与技巧详解
在SQL数据库查询中,投影和连接是两个核心概念
|
4月前
|
SQL Java 数据库连接
如何使用`DriverManager.getConnection()`连接数据库,并利用`PreparedStatement`执行参数化查询,有效防止SQL注入。
【10月更文挑战第6天】在代码与逻辑交织的世界中,我从一名数据库新手出发,通过不断探索与实践,最终成为熟练掌握JDBC的开发者。这段旅程充满挑战与惊喜,从建立数据库连接到执行SQL语句,再到理解事务管理和批处理等高级功能,每一步都让我对JDBC有了更深的认识。示例代码展示了如何使用`DriverManager.getConnection()`连接数据库,并利用`PreparedStatement`执行参数化查询,有效防止SQL注入。
179 5
|
4月前
|
SQL 数据库 决策智能
SQL语句实现投影连接详解
在SQL中,投影(Projection)和连接(Join)是数据查询和处理中非常重要的两个操作
|
4月前
|
SQL 存储 数据可视化
SQL 数据库大揭秘:连接数字世界的魔法桥梁
在数字化时代,数据如繁星般璀璨,而 SQL 数据库则像强大的引力场,有序汇聚、整理和分析这些数据。SQL 数据库是一个巨大的数字宝库,装满各行各业的“宝藏”。本文将带你探索 SQL 数据库在电商、金融、医疗和教育等领域的应用。例如,在电商中,它能精准推荐商品;在金融中,它是安全卫士,防范欺诈;在医疗中,它是健康管家,管理病历;在教育中,则是智慧导师,个性化教学。此外,还将介绍如何利用板栗看板等工具实现数据可视化,提升决策效率。
|
4月前
|
SQL 开发框架 .NET
ASP连接SQL数据库:从基础到实践
随着互联网技术的快速发展,数据库与应用程序之间的连接成为了软件开发中的一项关键技术。ASP(ActiveServerPages)是一种在服务器端执行的脚本环境,它能够生成动态的网页内容。而SQL数据库则是一种关系型数据库管理系统,广泛应用于各类网站和应用程序的数据存储和管理。本文将详细介绍如何使用A
123 3
|
4月前
|
SQL 数据库 索引
SQL语句实现投影连接:技巧与方法详解
在SQL数据库操作中,投影连接(Projection Join)是一种常见的数据查询技术,它结合了投影(Projection)和连接(Join)两种操作
|
4月前
|
SQL 存储 监控
串口调试助手连接SQL数据库的技巧与方法
串口调试助手是电子工程师和软件开发人员常用的工具,它能够帮助用户进行串口通信的调试和数据分析
|
4月前
|
SQL 数据库 索引
内连接(INNER JOIN)在SQL中的简单应用与技巧
在SQL查询中,内连接(INNER JOIN)是一种基本且常用的连接类型,用于从两个或多个表中检索匹配的记录
|
4月前
|
SQL 开发框架 .NET
ASP.NET连接SQL数据库:实现过程与关键细节解析an3.021-6232.com
随着互联网技术的快速发展,ASP.NET作为一种广泛使用的服务器端开发技术,其与数据库的交互操作成为了应用开发中的重要环节。本文将详细介绍在ASP.NET中如何连接SQL数据库,包括连接的基本概念、实现步骤、关键代码示例以及常见问题的解决方案。由于篇幅限制,本文不能保证达到完整的2000字,但会确保