图片 14

SQL Server常见问题总结

  在此之前和众多群友聊天发掘对贰零壹陆的无域和负载均衡满心期待,毕竟能够大约搭建况兼能够不适用第三方负载均衡器,SQL自个儿能够负载了。windows二〇一四早就足以下载应用了,那么这回终于得以报料令人憧憬恋慕的AlwaysOn2016负载均衡集群的私人民居房面纱了。

写在后边

  在QQ群,微信群,论坛中时时救助使用SQL
Server数据库的情侣解决难题,不过有局地最常见最宗旨的标题,每一日都有人问,回答多了也不想再解答了,索性把那么些主题素材整治一下,再有人问到直接发链接。

   有时主见而写那篇小说,难题或许不完美,后续会一贯更新。

  本篇主要呈报个人集群搭建中遇见的坑和一部分注意事项,以及2015无域负载均衡的简便体验测量试验。

基础难点访谈

搭建体验

能源下载

  描述:XX版本数据库操作系统在何地下载?

  答:  里面相当多东西,有意思味的本人看吗

基础境况

  想要不使用域情状来搭建AlwaysON 必需使用windows 贰零壹伍 和sql server贰零壹陆

  本篇作者利用3台虚构机(主假诺为了测量检验负载均衡,不然2台就可以),为了搭建的纯洁性,我3台机械都是单身设置,没有运用虚机复制。

  

主机名 IP地址
sql16node1 192.168.3.113
sql16node2 192.168.3.114
sql16node3 192.168.3.115
sqlcluster2016(windows集群) 192.168.3.120
L_KK_AWO2016(AlwaysOn监听) 192.168.3.121
16操作系统介质
ed2k://|file|cn_windows_server_2016_x64_dvd_9327743.iso|6020876288|58F585A340248EF7603A48F832F08B6D|/
SQL16介质
ed2k://|file|cn_sql_server_2016_enterprise_x64_dvd_8699450.iso|2452795392|D8AFD8D6245F518F53F720C48E2819C0|/

 

 

一而再难点

  描述:数据库连接不上

  图片 1

  答:请确认SQL服务是或不是运行,客户密码是不是科学,连接的实例名称,端口是不是科学

  图片 2

  

欣逢的标题

  全部的系统,故障转移集群,AlwaysOn搭建都和2013和二零一六并未有太大距离,所以这里只介绍多少个搭建时的非正规注意点。

  1.因为尚未域所以需求在”Computer属性“增添Computer的DNS后缀。

  图片 3

 

 

  2.亟需做域名分析(域名分析运维——>drivers,各类节点都亟需配置)

  图片 4

 

 

  3.图形化创制会冒出难点,导出脚本查看开采脚本不全

  上面是创造AWO的剧本(前提是种种节点已经还原的备份文件)

  注: 以下脚本能够通过SSMS工具切换来SQLCMD情势运作

  图片 5

 

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect sql16node1

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END


GO

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [NT Service\MSSQLSERVER]

GO

:Connect sql16node1

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect sql16node2

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END


GO

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [NT Service\MSSQLSERVER]

GO

:Connect sql16node2

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect sql16node3

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END


GO

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [NT Service\MSSQLSERVER]

GO

:Connect sql16node3

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect sql16node1

USE [master]

GO

CREATE AVAILABILITY GROUP [KK_AG_2016]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)
FOR DATABASE [AWO_2016]
REPLICA ON N'SQL16NODE1' WITH (ENDPOINT_URL = N'TCP://SQL16NODE1:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
    N'SQL16NODE2' WITH (ENDPOINT_URL = N'TCP://SQL16NODE2:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
    N'SQL16NODE3' WITH (ENDPOINT_URL = N'TCP://SQL16NODE3:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

GO

------------这部分代码是图形化工具中缺失的步骤

:Connect sql16node2

ALTER AVAILABILITY GROUP [KK_AG_2016] JOIN;

GO
ALTER DATABASE [AWO_2016] SET HADR AVAILABILITY GROUP = [KK_AG_2016];
GO

:Connect sql16node3

ALTER AVAILABILITY GROUP [KK_AG_2016] JOIN;

GO
ALTER DATABASE [AWO_2016] SET HADR AVAILABILITY GROUP = [KK_AG_2016];
GO

 

成立监听

:Connect sql16node1

USE [master]

GO

ALTER AVAILABILITY GROUP [KK_AG_2016]
ADD LISTENER N'L_KK_AWO2016' (
WITH IP
((N'192.168.3.121', N'255.255.255.0')
)
, PORT=8000);

GO

 

日志难题

  描述:系统日志LDF满了 或 日志文件丰硕大 怎么着减弱?

  答:轻松恢复生机格局下SQL
Server会自动截断日志文件,完整情势下须求日志备份

  恢复生机形式查看

  图片 6

  日志备份的措施

  图片 7

  裁减日志

  图片 8

 

测试

查询比较久慢

  描述:查询比较久都查不出数据,比极慢!

  答:那样的状态出现一般是查询语句被别的语句不通。在查询中加多 select
* from table with (nolock)要是能查出来注脚阻塞

  具体的隔开情状 能够选择sp_who2 或者
sys.dm_exec_requests 视图查询

  具体脚本(查看语句运营状态)

 1 WITH sess AS
 2 (
 3     SELECT
 4         es.session_id,
 5         database_name = DB_NAME(er.database_id),
 6         er.cpu_time,
 7         er.reads,
 8         er.writes,
 9         er.logical_reads,
10         login_name,
11         er.status,
12         blocking_session_id,
13         wait_type,
14         wait_resource,
15         wait_time,
16         individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1),
17         parent_query = qt.text,
18         program_name,
19         host_name,
20         nt_domain,
21         start_time,
22         DATEDIFF(MS,er.start_time,GETDATE()) as duration,
23         (SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)) AS query_plan
24     FROM
25         sys.dm_exec_requests er
26         INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
27         CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
28     WHERE
29         es.session_id > 50
30         AND es.session_Id NOT IN (@@SPID)
31 )
32 SELECT
33     *
34 FROM
35     sess
36 UNION ALL SELECT
37     es.session_id,
38     database_name = '',
39     0,
40     0,
41     0,
42     0,
43     login_name,
44     es.status,
45     0,
46     '',
47     '',
48     '',
49     qt.text,
50     parent_query = qt.text,
51     program_name,
52     host_name,
53     nt_domain,
54     es.last_request_start_time,
55     DATEDIFF(MS,es.last_request_start_time,GETDATE()) as duration,
56     NULL AS query_plan
57 FROM
58     sys.dm_exec_sessions es
59     INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
60     CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt
61 WHERE
62     ec.most_recent_session_id IN
63     (
64         SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)
65     )
66 ORDER BY
67     1, 2

  

高可用测量检验

  轻松的看起来与前面从未怎么异样,依旧依附故障转移会集,仲裁的的点子与二零一二Evoque2基本一样,能够行使决策磁盘、仲裁文件夹或动态节点投票(偶数个节点照旧会动态分配投票权),值得关切的是在windows二零一六的集群中增多了云仲裁,这些云端配置先不说,真是对于在此以前计划的跨机房的集群的佛法。

图片 9

 

分区表难题

  描述:数据量千万等级了利用分区表升高品质

   答:分区表的选拔处境首假使治本数据,而升格质量首若是靠IO并行,要求客观规划多块物理磁盘,大许多的场馆下几千万多少单一的格局查询只要求加多正确的目录就能够。

  

负载均衡测量检验

配备负载均衡(此处只是简短的做了眨眼间间主节点的载荷)

alter availability group kk_ag_2016
modify replica on 'sql16node1'
with
(
primary_role
(
read_only_routing_list = (('sql16node2','sql16node3'),'sql16node1')
)
)



alter availability group kk_ag_2016
modify replica on N'sql16node1'
with
(
    secondary_role (read_only_routing_url = N'tcp://sql16node1.kk.com:1433')
)
go
alter availability group kk_ag_2016
modify replica on N'sql16node2'
with
(
    secondary_role (read_only_routing_url = N'tcp://sql16node2.kk.com:1433')
)
go
alter availability group kk_ag_2016
modify replica on N'sql16node3'
with
(
    secondary_role (read_only_routing_url = N'tcp://sql16node3.kk.com:1433')
)
go

 

  测量检验的例证比较轻便,也没动用什么压力工具就径直用SSMS管理工科具。

  使用监听名称连接或监听IP加端口

  图片 10

  其余必得在一而再参数中钦命ApplicationIntent=ReadOnly

  图片 11

 

  开了5个窗口举行语句:

  图片 12

 

  使用profiler抓取结果

  图片 13

 

  图片 14

 

  只是看看能还是不可能负载均衡,咋一看微软还真没骗人。可是要打听那只是只读副本的负载,实际不是写入也足以负载!

  其余要小心AlwaysOn的数据同步是一时间推迟的(就到底一齐方式)!那一点能够瞻昂笔者另一篇的测验:AlwaysOn
同步时间的测量检验

 

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

原来的文章地址: 

如有转发请保留最先的文章地址! 

 


小结 :
小说只是简短的搭建和测量检验了弹指间未曾做深远的切磋,可能会给风野趣理解2015AlwaysOn集群的意中人或多或少帮手吗,极度是搭建进度,网络找到的中坚都是英语文档,对于小编这种看见鹰就发触的健儿,也总算一点福音吧。

  二〇一四的AlwaysOn可用组没有须求域情形,能够负载均衡,还确实是有相当大的重力。

高可用的取舍

  答:SQL自带的高可用或读写分离技巧首要有:故障转移集结、发表订阅、镜像、日志传送、AlwaysON可用组(具体能够在进级难题的素材中详尽查看)

  一般选择读写分离必要依靠差别的场合和供给,譬就如步的实时性,读写分离作用的内需情状

  首要列出多少个优劣势:

  故障转移集结:主备方式,单活(援救机不可读),硬件能源浪费,首要场景是数据库的高可用。

  发表订阅:读写分离常用形式,配置灵活,别本节点能够八个,能够发布订阅部分数据(即能够对数据筛选),并提供各类揭露订阅格局,短处:维护相比较麻烦,一般不能用作高可用。

  镜像:主备方式,单活(帮忙机不可读),硬件能源浪费,主要场景是数据库的高可用。相对于故障转移集合镜疑似数据库品级的高可用。在镜像中得以采纳快照的艺术贯彻读写分离。

  日志传送:重要用来灾备,在备用机上可读,但短处是日记还原时不能够读,读时不能够重整旗鼓。

  AlwaysON可用组:综合性方案,满意高可用、读写分离等急需,须求:SQL
Server二〇一一 以上版本

  第三方产品:moebius负载均衡集群,达成双活,读负载均衡、读写分离等。短处实时同步不合乎类似收罗系统的广大写入系统。

 

服务无法运转

  答:服务不能运转有众多缘由,须要实际难题具体定位,借使遭受此类难点要率先查看日志定位难题,日志首要两部分,SQL运维日志和windows日志,上面给出两篇优异解析SQL运转的篇章:

  您所不知晓的SQL
Server数据库运维进程(客商数据库加载进程的疑难杂症)

  您所不知道的SQL
Server数据库运行进度,以及运维不起来的种种主题素材的分析及缓和手艺

  

数据库设计,表设计的主题素材

  大大多那样的标题,在QQ群里问是向来得不到答案的,相当多业务场景不是几句话可以描述清楚的。