图片 9

Server中DAC连接及其它,JSON原生支持实例说明

背景

Microsoft SQL Server
对于数据平台的开发者来说越来越友好。比如已经原生支持XML很多年了,在这个趋势下,如今也能在SQLServer2016中使用内置的JSON。尤其对于一些大数据很数据接口的解析环节来说这显得非常有价值。与我们现在所做比如在SQL中使用CLR或者自定义的函数来解析JSON相比较,新的内置JSON会大大提高性能,同时优化了编程以及增删查改等方法。

   
那么是否意味着我们可以丢弃XML,然后开始使用JSON?当然不是,这取决于数据输出处理的目的。如果有一个外部的通过XML与外部交互数据的服务并且内外的架构是一致的,那么应该是使用XML数据类型以及原生的函数。如果是针对微型服务架构或者动态元数据和数据存储,那么久应该利用最新的JSON函数。

DAC(Dedicated Admin Connection)是SQL Server
2005引入的一个东西,目的是在SQL
Server发生严重性能问题的时候仍保留有限的资源保证管理员能够执行一些简单的命令用于问题诊断、释放资源、杀死肇事进程等。微软官方对DAC的说明:使用专用管理员连接.aspx)。对于DAC使用的一般情况,有两个不错的Blog值得推荐:

  说到高可用,看官们会想到很多方案,也许是自亲身经历过系统从单机变成高可用的痛苦过程,也许有的看官只是在自己的虚机上搭建过测试的玩具。今天本篇用我自己的真实经历给大家讲述,不管怎么样实战和测试玩耍还是很大的区别的!可能你觉得搭建一套高可用方案很简单,配置配置就OK了,但在真正的复杂系统中一切就没有那么轻松了! 

实例

    当使用查询这些已经有固定架构的JSON的数据表时,使用“FOR
JSON”
提示在你的T-SQL脚本后面,用这种方式以便于格式化输出。一下实例我使用了SQLServer
2016 Worldwide Importers sample
database,可以在GitHub上直接下载下来(下载地址)。看一下视图Website.customers。我们查询一个数据并格式化输出JSON格式:

SELECT [CustomerID]
      ,[CustomerName]
      ,[CustomerCategoryName]
      ,[PrimaryContact]
      ,[AlternateContact]
      ,[PhoneNumber]
      ,[FaxNumber]
      ,[BuyingGroupName]
      ,[WebsiteURL]
      ,[DeliveryMethod]
      ,[CityName]

 ,DeliveryLocation.ToString() as DeliveryLocation
      ,[DeliveryRun]
      ,[RunPosition]
  FROM [WideWorldImporters].[Website].[Customers]
  WHERE CustomerID=1
  FOR JSON AUTO

  

 

请注意我们有一个地理数据类型列(DeliveryLocation),这需要引入两个重要的变通方案(标黄):

首先,需要转换一个string字符,否则就会报错:

FOR JSON cannot serialize CLR objects. Cast CLR types explicitly into one of the supported types in FOR JSON queries.

其次,JSON采用键值对的语法因此必须指定一个别名来转换数据,如果失败会出现下面的错误:

Column expressions and data sources without names or aliases cannot be formatted as JSON text using FOR JSON clause. Add alias to the unnamed column or table.

确认了这些,改写的格式化输出如下:

[
    {
        "CustomerID": 1,
        "CustomerName": "Tailspin Toys (Head Office)",
        "CustomerCategoryName": "Novelty Shop",
        "PrimaryContact": "Waldemar Fisar",
        "AlternateContact": "Laimonis Berzins",
        "PhoneNumber": "(308) 555-0100",
        "FaxNumber": "(308) 555-0101",
        "BuyingGroupName": "Tailspin Toys",
        "WebsiteURL": "http://www.tailspintoys.com",
        "DeliveryMethod": "Delivery Van",
        "CityName": "Lisco",
        "DeliveryLocation": "POINT (-102.6201979 41.4972022)",
        "DeliveryRun": "",
        "RunPosition": ""
    }
]

  

 

当然也可以使用JSON作为输入型DML语句,例如INSERT/UPDATE/DELETE
语句中使用“OPENJSON”。因此可以在所有的数据操作上加入JSON提示。

如果不了解数据结构或者想让其更加灵活,那么可以将数据存储为一个JSON格式的字符类型,改列的类型可以使NVARCHAR
类型。Application.People 表中的CustomFields
列就是典型这种情况。可以用如下语句看一下表格格式这个列的内容:

declare @json nvarchar(max)

SELECT @json=[CustomFields]
FROM [WideWorldImporters].[Application].[People]
where PersonID=8

select * from openjson(@json)

  

 

结果集在表格结果中的显示:

图片 1

 

用另一种方式来查询这条记录,前提是需要知道在JSON数据结构和关键的名字,使用JSON_VALUE
和JSON_QUERY
函数:

  SELECT
       JSON_QUERY([CustomFields],'$.OtherLanguages') as OtherLanguages,
       JSON_VALUE([CustomFields],'$.HireDate') as HireDate,
       JSON_VALUE([CustomFields],'$.Title') as Title,
       JSON_VALUE([CustomFields],'$.PrimarySalesTerritory') as PrimarySalesTerritory,
       JSON_VALUE([CustomFields],'$.CommissionRate') as CommissionRate
  FROM [WideWorldImporters].[Application].[People]
  where PersonID=8

  

 

在表格结果集中展示表格格式的结果:

图片 2

 

这个地方最关心就是查询条件和添加索引。设想一下我们打算去查询所有2011年以后雇佣的人,你可以运行下面的查询语句:

SELECT personID,fullName,JSON_VALUE(CustomFields,'$.HireDate') as hireDate
FROM [WideWorldImporters].[Application].[People]
where IsEmployee=1
and year(cast(JSON_VALUE(CustomFields,'$.HireDate') as date))>2011

  

 

切记JSON_VALUE
返回一个单一的文本值(nvarchar(4000))。需要转换返回值到一个时间字段中,然后分离年来筛选查询条件。实际执行计划如下:

图片 3

 

为了验证如何对JSON内容创建索引,需要创建一个计算列。为了举例说明,Application.People
表标记版本,并且加入计算列,当系统版本为ON的时候不支持。我们这里使用Sales.Invoices表,其中ReturnedDeliveryData
中插入json数据。接下来获取数据,感受一下:

SELECT TOP 100 [InvoiceID]
      ,[CustomerID]
      ,JSON_QUERY([ReturnedDeliveryData],'$.Events')
  FROM [WideWorldImporters].[Sales].[Invoices]

  

 

发现结果集第一个event都是“Ready for collection”:

图片 4

 

然后获取2016年3月的发票数据:

SELECT [InvoiceID]
      ,[CustomerID]
      ,CONVERT(datetime, CONVERT(varchar,JSON_VALUE([ReturnedDeliveryData],'$.Events[0].EventTime')),126)
  FROM [WideWorldImporters].[Sales].[Invoices]
  WHERE CONVERT(datetime, CONVERT(varchar,JSON_VALUE([ReturnedDeliveryData],'$.Events[0].EventTime')),126)
       BETWEEN '20160301' AND '20160331'

  

实际执行计划如下:

图片 5

 

    加入一个计算列叫做“ReadyDate”, 准备好集合表达式的结果:

ALTER TABLE [WideWorldImporters].[Sales].[Invoices]
ADD ReadyDate AS CONVERT(datetime, CONVERT(varchar,JSON_VALUE([ReturnedDeliveryData],'$.Events[0].EventTime')),126)

  

 

之后,重新执行查询,但是使用新的计算列作为条件:

SELECT [InvoiceID]
      ,[CustomerID]
      ,ReadyDate
  FROM [WideWorldImporters].[Sales].[Invoices]
  WHERE ReadyDate BETWEEN '20160301' AND '20160331'

  

 

执行计划是一样的,除了SSMS建议的缺失索引:

图片 6

 

因此,根据建议在计算列上建立索引来帮助查询,建立索引如下:

/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.272%.
*/
CREATE NONCLUSTERED INDEX IX_Invoices_ReadyDate
ON [Sales].[Invoices] ([ReadyDate])
INCLUDE ([InvoiceID],[CustomerID])
GO

  

 

我们重新执行查询验证执行计划:

图片 7

 

有了索引之后,大大提升了性能,并且查询JSON的速度和表列是一样快的。

上面的两篇blog涉及到的基本是DAC访问单机单实例的情况。本文试图对DAC访问单机多实例的情况也做个探讨。

  文章主要讲述升级并搭建AlwaysOn高可用的过程,以实施的思路为主。文中并没有搭建集群的步骤,搭建步骤请自行学习(个人认为会搭建可用组并不是关键,而一系列的调研细节才是项目成功的关键)

总结:

本篇通过对SQL2016 中的新增的内置JSON进行了简单介绍,主要有如下要点:

 

  • JSON能在SQLServer2016中高效的使用,但是JSON并不是原生数据类型;
  • 如果使用JSON格式必须为输出结果是表达式的提供别名;
  • JSON_VALUE 和 JSON_QUERY 
    函数转移和获取Varchar格式的数据,因此必须将数据转译成你需要的类型。
  • 在计算列的帮助下查询JSON可以使用索引进行优化。

1)单机单SQL Server实例,且SQL Server实例使用默认端口(1433)

————–博客地址—————————————————————————————

--以下的形式都可以访问
sqlcmd -S myServer -U myUser -P myPassword -A
sqlcmd -S ADMIN:myServer -U myUser -P myPassword
sqlcmd -S myServer,1434 -U myUser -P myPassword
sqlcmd -S xxx.xxx.xxx.xxx -U myUser -P myPassword -A
sqlcmd -S xxx.xxx.xxx.xxx,1434 -U myUser -P myPassword

说明:
a) sqlcmd命令行中参数与参数值之间可以有空格也可以没有;如果你的密码中有空格,你可以用双引号把你的密码引起来;
b) sqlcmd命令中的“-S”其实也可以用"/S",其它参数也一样;
c) 1434是SQL Server连接的默认端口号;
d) 在服务器前加"ADMIN:"也是为了指定进行DAC连接;
e) “-A”是在sqlcmd命令行中指定DAC连接的参数;
f) "-A","ADMIN:",",1434"不能在一条命令中出现两个或以上,否则会报错;
g) 不在命令行中加入这3个参数("-A","ADMIN:",",1434")的任何一个,登录也能成功,差别在于你使用的连接是普通连接,不是DAC连接。一般来说,普通连接能用的资源更多,但是当系统性能出现严重问题的时候普通连接可能没法建立,这也是引入DAC的初衷;再就是DAC连接下能看到一些有助于诊断的秘密视图(参见上面推荐的第一个blog)。

原文地址: 

2)单机单SQL Server实例,SQL Server实例使用非默认端口

如有转载请保留原文地址! 

我通过测试得到的结论是:对于单机单SQL Server实例,使用非默认端口时候的DAC访问跟使用默认端口1433时候完全一样。网上的一些论坛说要确保“SQL Server Browser”在运行,似乎不是必要的,至少我测试(用的SQL Server 2008 R2 SP3)过程中“SQL Server Browser”是不是在运行不影响访问。

 

3)单机多SQL Server实例

 

通过DAC来访问单机多SQL Server实例的情况要复杂一些。上面的几条命令行在这种情况下都会失效。原因在两个:
a) DAC访问是实例级别的,服务端得有办法知道你要访问的是哪个实例;
b) 在单机多实例的情况下监视DAC访问的是随机端口,而不再是默认的1434(当然,具体的端口号在SQL Server启动的时候是确定的,可以在SQL Server启动的Log中找到:打开SSMS--->连接到数据库实例--->Management--->SQL Server Logs--->Current,在里面找到类似”Dedicated admin connection support was established for listening locally on port 50458.“)

--怎么破?
我们在访问数据库引擎的时候,碰到单机多实例的情况有两种办法,一种是在配置S参数的时候加上实例名,一种是加实例端口号。命令行的形式类似下面:
sqlcmd -S myServer\InstanceName -U myUser -P myPassword
sqlcmd -S xxx.xxx.xxx.xxx\InstanceName -U myUser -P myPassword
sqlcmd -S myServer,6xxx -U myUser -P myPassword
sqlcmd -S xxx.xxx.xxx.xxx,6xxx -U myUser -P myPassword

先从实例名着手:
sqlcmd -S myServer\InstanceName -U myUser -P myPassword -A
sqlcmd -S xxx.xxx.xxx.xxx\InstanceName -U myUser -P myPassword -A
sqlcmd -S ADMIN:myServer\InstanceName -U myUser -P myPassword
sqlcmd -S ADMIN:xxx.xxx.xxx.xxx\InstanceName -U myUser -P myPassword

经测试确认,以上4种连接方式都是OK的。注意一点,对于InstanceName的解析是服务器上的“SQL Server Browser”进行的,如果这个服务不在运行,DAC的访问是要失败的。流程是:Browser根据“myServer\InstanceName”或者“xxx.xxx.xxx.xxx\InstanceName”找到你要访问的实例,然后根据“-A”或者“ADMIN:”找到你要访问的端口。

既然这样可以进行DAC访问,那用类似访问数据库引擎的方式,把上面命令中的“\InstanceName”改成",xxxx"格式的端口号是不是也行呢?
sqlcmd -S myServer,xxxx -U myUser -P myPassword -A
sqlcmd -S xxx.xxx.xxx.xxx,xxxx -U myUser -P myPassword -A
sqlcmd -S ADMIN:myServer,xxxx -U myUser -P myPassword
sqlcmd -S ADMIN:xxx.xxx.xxx.xxx,xxxx -U myUser -P myPassword

如果你在几个命令行中配的端口号跟访问数据库引擎时候配置的端口号是一样的话,答案是不行。原因在哪里呢?那个端口是数据库引擎的访问端口,并不是被监听的DAC端口,因为不在一个频道上DAC还不知道你想访问。我的理解,在命令行中指定了端口号的情况下,Browser认为那就是你想访问的端口,结果因为它并不是那个随机的DAC端口而导致了失败。

DAC访问侦听跟数据库引擎一样,从根本上来说也是一个tcp服务(关于这一点你可以查看sys.endpoints来确认)。是服务,我们如果能知道它侦听的端口号就应该能解决问题。但不幸也在这儿,如上面b)所说,在单机多实例的情况下这个被监听的端口是随机的。视图sys.endpoints是能查到当前SQL Server实例上的tcp服务信息的,每个endpoint都有一条记录,比如你就能在这里查到用于镜像的5022,但遗憾的是对于DAC,端口那一列却显示的是0.通过端口访问的这条路我没能走通。

废话不多说,直接开整—————————————————————————————–

4)DAC访问与防火墙

背景

  客户的现有方案是一套使用发布订阅构建的读写分离方案,总体来说系统构建的很不错。也是在SQL2012之前很常见的一套架构。

  架构图如下:

   图片 8

 

  图片 9

 

 

 

  客户的需求:SQL server 2008 R2 升级到SQL SERVER 2014 使用AlwaysOn
替换现有发布订阅架构。实现本地高可用、读写分离,异地灾备等,并应用部分2014的新功能,如内存优化表等提升系统性能和并发能力等。

如果有人通过我上面提到的有效的方式进行DAC访问却不幸失败了,也请不要奇怪。抛开端口劫持等特殊情况,DAC访问失败最常见的就是受到防火墙设置的拦截。对于上面提到的两种单机单实例的情况,只要确认服务端配置了允许对tcp1434端口的访问,DAC连接应该是没有问题的;复杂的情况仍然是单机多实例。

对于单机多实例的情况,由于DAC侦听的端口是随机的,不能指定,所以我们没法在防火墙上给它开口子,除非关闭防火墙。事实上,我在测试的时候就是让服务器上的Windows防火墙对域范围内的访问不起作用(关闭针对域内部访问的拦截)的。那要想从外网访问怎么办?总不能为了一个DAC连接把这台服务器赤裸裸的暴露出来(给它配外网IP)或者关掉公司的级别的防火墙吧?这倒不必,可以用VPN或者端口映射从防火墙上开个小口子,这样你就能连接到局域网,从那进行DAC访问。

前期调研

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签:
网站地图xml地图