使用了Sqlserver 2012 Always on技术后,假如采用的配置是默认配置,会出现Primary server CPU很高的情况发生,比如默认配置如下:

澳门微尼斯人手机版 1

需要自定义来解决这个问题。

 

Dos方法:

1、查询指定时间区间的工作日

我们先来看看上图中的这些选项的意义

主角色中的连接

  • 允许所有连接
    • 如果当前server是primary角色时,primary
      instance允许所有连接(如:读/写/管理)
  • 澳门微尼斯人手机版,允许读/写连接
    • 如果当前server是primary角色时,primary
      instance只允许读/写连接(如果通过ssms连接,将报错、sqlcmd也是报错)

可读辅助副本

    • 如果当前server是primary角色时,所有的secondary
      servers都是可以看的(通过ssms能看结构、数据,但不能更改)
  • 仅读意向
    • 如果当前server是primary角色时,所有的secondary
      servers只允许读连接(需要在建立连接时加入key来标明为只读连接:ApplicationIntent=ReadOnly)
    • 如果当前server是primary角色时,所有的secondary
      servers都不可以看(通过ssms能连接,但是看不了,会报错,如下)
    • 澳门微尼斯人手机版 2

 

 

这个主要难点是法定节假日,国家的法定节假日每年都不一样,还涉及到调休,所以我们设计一个假日表。主要字段有年份,类型(是否调休),假期日期。如下:

建立读写分离的方法:

第一种

    1. 设置某具体“可用性组”的属性为:可读副本为“是”
    2. 客户端通过直连副本方式实现将select的流量转发过去
    3. 暴露出去的ip地址至少2个:侦听器ip和副本ip(如果副本多个,则可用ip哈希来进行更多的自定义)

第二种

    1. 设置某具体“可用性组”的属性为:可读辅助副本为“仅读意向”
    1. 澳门微尼斯人手机版 3
    2. 执行sql脚本,建立read指针
    3. 执行sql脚本,建立primary, read db ur list关系
    4. 暴露出去的ip地址只有1个:侦听器IP

 

第一种方式能够进行更多地自定义,但是已经脱离sqlserver always
on技术了,因此不讨论了

第二种方式对于客户端来讲更傻瓜点,但是自定义力度小,全依托于ms未来怎么改进这块了,而且这里有些坑。。。

 

澳门微尼斯人手机版 4

 CREATE TABLE [dbo].[Holidays](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Holiday] [datetime2](7) NULL,--假期日期

[YearS] [char](4) NULL,--年份
[daytype] [int] NULL--类型

)

下面来说说这些坑:

坑1:UI图形界面设置后,还需要执行脚本来建立读写分离支持

建立read指针 – 在当前的primary上为每个sqlserver instance建立[instance
name=>instance tcp url] Map

澳门微尼斯人手机版 5

--由于这里有2个instance(包括了primary角色的), 因此在primary上分别为这2个instance建立关系

ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://LAB-SQL1.lab-sql.com:1433'))

ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL2' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://LAB-SQL2.lab-sql.com:1433'))

澳门微尼斯人手机版 6

 

建立primary, read db ur list关系 –
在当前的primary上为各个primary建立对应的read only url
列表(有优先级概念)

澳门微尼斯人手机版 7

--为每个可能成为primary角色的server,建立相应的只读列表,下面的代码由于互为readonly server,因此优先级都是1

ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('LAB-SQL1')));


ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('LAB-SQL2')));


--假如又增加了一台lab-sql3的secdonary,则sql可变为
ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('LAB-SQL1', 'LAB-SQL3')));


ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('LAB-SQL2', 'LAB-SQL3')));

--上述语句中的列表是有优先级关系的,排在前面的具有更高的优先级

澳门微尼斯人手机版 8

 

可以通过如下语句查看这个关系,以及相应的优先级:

澳门微尼斯人手机版 9

select ar.replica_server_name, rl.routing_priority,
 (select ar2.replica_server_name 
 from sys.availability_read_only_routing_lists rl2 
    join sys.availability_replicas AS ar2 ON rl2.read_only_replica_id = ar2.replica_id 
where rl.replica_id=rl2.replica_id and rl.routing_priority =rl2.routing_priority 
    and rl.read_only_replica_id=rl2.read_only_replica_id) as 'read_only_replica_server_name' 
    from sys.availability_read_only_routing_lists rl join sys.availability_replicas AS ar ON rl.replica_id = ar.replica_id

澳门微尼斯人手机版 10

澳门微尼斯人手机版 11

这里的routing_priority就是优先级

坑2:客户端需要指定访问的数据库以及加入ReadOnly关键字

C#连接字符串

    • server=侦听器IP;database=testDB3;uid=sa;pwd=111111;ApplicationIntent=ReadOnly

SSMS方式

    • 澳门微尼斯人手机版 12
    • 澳门微尼斯人手机版 13
    • 澳门微尼斯人手机版 14

坑3:Hosts文件设置

由于sql server always
on依赖于windows集群,而windows集群依赖于活动目录,而客户端程序所在server很可能没有加入域,因此这里的解析存在问题

由于这种读写分离的方式,实际上是客户端先连接到侦听器ip,然后通过协商后,让客户端再连接到具体的副本上(用tcp
url,使用了全名的,如:sql1.ad.com这种格式,在ad外部默认无法解析),因此需要修改hosts文件,为每个可能成为read的全名增加记录,如下:

192.168.0.1        LAB-SQL1.lab-sql.com
192.168.0.2        LAB-SQL2.lab-sql.com

 

依托于

  

总结

  1. 简单情况下的读写分离比较适用
  2. 只适用于粗粒度的读写分离,因为增加了一个额外的ConnectionString,而不是建立在普通连接字符串上的
  3. 如果读写分离的分发规则复杂,则不适用

澳门微尼斯人手机版 15

添加好当年的假期和调休日期

目录下的文件

写个方法计算出除开法定假日的工作日

澳门微尼斯人手机版 16

ALTER FUNCTION [dbo].[GetWorkerDays]

(

-- Add the parameters for the function here

 @StartTime DATETIME, -- 起始时间

 @EndTime DATETIME -- 结束时间,查询默认小于此时间

)

RETURNS INT

AS

BEGIN 

DECLARE @Total INT;

DECLARE @Temp INT;

DECLARE @Days INT;

DECLARE @Index INT;

SET @Days = DATEDIFF(DAY,@StartTime,@EndTime);

SET @Index = 0;

SET @Temp = 0;

SET @Total = 0;

WHILE @Index < @Days BEGIN 

SET @Temp = DatePart(WEEKDAY,DATEADD(DAY,@Index,@StartTime));

IF @Temp > 1 AND @Temp < 7 BEGIN 

SET @Total = @Total + 1;

END 

SET @Index = @Index + 1;

END 

RETURN ISNULL(@Total,0)

END

 

  

使用plsql developer 客户端软件进行连接

执行这个表值函数后加上调休日和减去法定假日就是工作日了,大家可以再写一个存储过程。

发表评论

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