虎口夺食—SQLServer运维

  数据是一个企业的命根子，尤其是在当今绝大部分的行业都实现了信息化的管理的时代，企业所有运营的数据、财务信息等都会存放到数据库中，在用户量和数据

量不断变大的情况下，如何保障这些信息的安全、保证随时随地的能被用户访问，同时还需要在用户不间断访问的情况下调整、修改这些数据和结构，用以满足业务的需

求变更，这本身一门很艺术的事了。

      这篇文章主要总结了SQLServer数据库运维时，在保证数据安全和DBA操作数据库时保证不影响用户访问方面的一些技巧，之所以将之形容为“虎口夺食”，是因为DBA

是个风险相当高的行业，在高并发、大数据量的系统中，很多小的失误或者操作不当，都将造成严重的后果，不仅影响系统的正常运行，甚至牵连到整个数据的安全，给企业造成不可估量的损失；我们粗算一笔账，以一个日交易量2000万的网站来说（中型规模），如果因你的操作造成网站当机一个小时，我们按一天20小时交易时间来算（另外4小时可能没多少交易量），一个小时就是100万的损失，这个只是显性成本，还有因网站故障导致用户流量的隐性损失，如果企业要与员工来算这边账的话，那技术吊丝一辈子估计都得卖给这个企业了；当然，一般企业不会这么去跟员工算计（否则没人敢干活了），但是一次这种失误发生的话，个人KPI年终奖、部门奖金等就别指望了，而且因为你的错误，你的顶头上司还会受到牵连，如果你犯错“频繁”（注意DBA一年范两次这样的错就算频繁了），公司不辞你，估计也没脸在公司待下去；我就见过一些犯过类似错误的DBA，在往后进行某些数据库操作时，口中会念念有词，手还会止不住的发抖（正所谓一朝被蛇咬，十年怕井绳）。

下面将分四个部分，共十个方面来说明我整理的在SQLServer数据库运维的一些技巧：

保障数据访问

      这个部分也可以叫在线容灾恢复，就是数据库发生问题后，在不中断用户访问（或者中断时间很短）的前提下，恢复数据库系统。

1.       Mirror+Replication 自动切换

      这种情况是在为核心数据库做了镜像，同时又做了复制的情况下，在主服务器发生问题时，系统能自动的将数据库切换到镜像机器，复制也同步切换过去（如果单纯只是做镜像切换的场景比较简单），也就是说核心服务器当机时，数据和同步链都不受影响，如果前端访问时是配置两个IP的（一个核心服务器IP，另一个是Mirror机器IP），那前端的访问就不会中断，在服务器出现问题时实现了对前端透明的切换过程；这种情况的详细过程请参考:SQLServer 数据库镜像+复制切换方案。

2.       群集在线添加节点

      如果您的数据库服务器做了群集（假设是双A群集），现在群集中的某台机器出现了问题，那另外一台将承受双业务的压力，此种情况下，为防止我们单台服务器压力

过大，或者再出现问题，我们应该尽快的替换掉出现问题的那台服务器，好的方法是在线添加一个新的节点，步骤如下：

• 新弄一台服务器，做好必要的配置（加域、加心跳线、配置HBA卡等）；

• 将新服务器添加到目前的Windows群集中；

• 将新服务器添加到SQLServer群集中；

• 将原本在出现问题机器上的业务切换到新添加的服务器中；

• 将出现问题的节点从群集中删除。

这个过程因为要在添加完节点后，将业务切换过来，所以会有短暂的业务暂停（相当于重启了一次SQLServer服务），但一般是可以接受的，这样我们就将损坏的机器做了

在线的替换，基本不影响业务的正常进行。

提高数据库访问能力

3.       在线扩充数据库读能力

      能在线扩充读能力的，一般都是需要做了数据库复制的环境，很多互联网企业在做企业的促销活动时，经常采取这种方案（尤其是电商）；在线扩充读能力一般分两种情况，种是提前准备好了读的服务器，数据也同步了，只需要在读出现瓶颈时，将准备好的服务器添加到负载均衡的缓冲池即可；另外一种是没有提前准备好读的服务器，而是临时添加，我们就来说下这个过程：

• 准备好需要添加的服务器；

• 将其他读的机器上的数据库备份，还原到新的机器；

• 从分发机器上创建到这台机器的同步链并暂时禁用（可以参考其他读机器同步链）；

• 同步链通过数据库比较工具（BCP、TableDiff等）修复缺失的数据，

• 启用同步链，跳过数据已经存在的冲突错误；

• 没有问题后，与其他只读机器比较数据量的情况，如果没问题即添加完成；

• 将新机器添加的负载均衡缓冲池，提供前端访问。

从整个过程来看，临时扩充系统读能力还是比较麻烦的，需要操作的DBA技能比较熟练，而且数据修复部分可能需要的时间比较长，一般应是提前准备的好，防止临时添加时

时间过紧而犯错。

4.       SSB + Replication

   SSB是SQLServer数据库的异步通信功能，如果企业对数据实时性要求不是那么高的话，是完全可以采用这项技术的，它的大好处就是缓解瞬时的高峰压力，将部分操

作异步处理，保证前端用户其他操作的顺畅；举个例子，我们在线预订酒店就可以用SSB技术，当你在网页上面提交订单后，系统不会马上告诉你订单是否成功，这时我们就用SSB技术传递一个消息给后端去处理，处理完成后再向用户手机发送一条信息，提示他是否预订成功，这样就缓解了网上大量用户在同一时刻下单时，造成网站阻塞的情况；如果能在结合复制技术，在读的机器上完成下单操作，然后通过SSB技术再到主服务器上去完成事务的处理，效果会更加（因为读的机器比较多，下单压力会分散到多台机器），这个技术大家可以参考:数据库架构  这篇文章。

5.       架构设计扩充系统读能力

    架构设计中，加快系统读能力的方案比较多（因为读比较频繁，容易出现瓶颈），比较普遍的是以空间换时间方案，按不同的维度将数据存放多份，在用户进行查找时根据查找的条件将查询定位到不同的服务器；例如我们将数据按三个维度来存放：普通维度、用户维度和产品维度，当前端写入数据时，就根据规则算法将数据按不同的维度写入不同的数据库服务器（前端写入的操作会变得复杂），而在用户查找时，就可以按用户选择的条件定位到相应的数据库上面去查找相关的数据了（如果还能结合Lucence等搜索引擎，效果会更好）。

在线数据库操作

6.       在线系统添加非空字段（带默认值）

     为在线系统的某个表添加一个非空的字段，这个操作是经常会遇到的，在数据量小，用户访问量不那么频繁时不存在什么问题，一旦表的数据量达到千万级别，而且用户对这个表访问频繁时，这个看似简单的操作就变得不是那么容易了；如果直接操作往往消耗时间很长，而且其他用户的操作被大量阻塞，甚至出现数据库假死现象；这种情况下处理这些操作的方式就必须改变，可能的方案如下：

• 选择业务低峰时间进行操作（如：晚上）；

• 加一个带默认值，且可以为NULL的字段，完成后将为NULL的数据改成默认值；

• 先加一个为NULL的字段，将数据填充后，改成带默认值的非NULL字段。

      从这三种方式来看，第二种方式是比较好的，因为它既能满足后面不断增加数据为非NULL值的需要，也不会造成大面积的阻塞，而且能满足时间上的需求（这种修改一

般可能要当场就进行，而不是等上一天）；当然在做NULL值数据修复时，需要分批进行（一次性操作又会遇到大量的阻塞和操作时间过长的问题），如按下面的代码形式

修改：

declare @rowcount int

if OBJECT_ID('tempdb.dbo.#temp1') is not null       

   drop table dbo.#temp1 

--更新临时表 

create table dbo.#temp1(id int primary key not null)  

while 1=1

  begin   

    --每次修改条记录  

    insert into dbo.#temp1(id)  

    select top 1000 id from dbo.Table1 where IsUpdate is null      

    update    dbo.Table1  set a.IsUpdate=  where ID in(select ID from #temp1)      

    set @rowcount=@@rowcount     

     if @rowcount=     

          break;  

     else     

          waitfor delay '00:00:00.500' --暂停.5秒        

   truncate table #temp1

end

  这类型的操作还有：添加索引，删除一个大表的部分数据等，操作的时候都需要使用一定的技巧才顺利完成。

7.       数据库收缩

   数据库变大，磁盘空间不足时，DBA往往都会采取收缩数据库或者数据文件的操作，但是这个操作在数据库很大时往往是一个相当耗时的过程，我们在对数据库（或者文件）收缩前好先看一下多能释放多少空间（下图的小值）

      然后选择能释放空间比较大的文件进行收缩，而且收缩时一定要分部进行，不要一次性收缩，可以每次选择收缩5G；刚开始收缩时是比较快的，越往后面收缩需要的

时间也越长（因为系统需要挪动的数据更多），如果觉得收缩时间过长了，我们可以终止掉，这样就不会有一次性收缩时造成数据库假死的现象；收缩的同时我们还需要查看

下阻塞的情况，如果有用户进程被收缩进程阻塞了，而且一定的时间内不能释放，应该马上终止收缩进程，防止影响业务的运行：

select * from sys.sysprocesses with(nolock) where blocked

8.     在线CPU调整

     在线系统如果CPU变得比较高了，我们在做调整时往往要对症下药，不能盲目处理，否则可能造成更严重的CPU性能问题；CPU 突然走高，70%的可能是因为新进来

的一些语句缺乏相关的索引，尤其是一些有GroupBy、 OrderBy 这类型的语句，那我们如何来找到这些语句呢？一个常见的方法是查找DMV，找出当时消耗CPU多的

语句，如下：

--总耗CPU多的前个20条SQL:

SELECT TOP 20

    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],

    qs.total_worker_time/qs.execution_count/1000 as [平均消耗CPU 时间(ms)],

    last_execution_time as [后一次执行时间],max_worker_time 1000 as [大执行时间(ms)],

    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 

        (case when qs.statement_end_offset = -1 

        then DATALENGTH(qt.text) 

        else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1) 

    as [使用CPU的语法], qt.text [完整语法],

    qt.dbid, dbname=db_name(qt.dbid),

    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName

FROM sys.dm_exec_query_stats qs with(nolock)

cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

where execution_count>1

ORDER BY  total_worker_time DESC

--平均耗CPU多的前个20个SQL:

SELECT TOP 20

    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],

    qs.total_worker_time/qs.execution_count/1000 as [平均消耗CPU 时间(ms)],

    last_execution_time as [后一次执行时间],min_worker_time /1000 as [小执行时间(ms)],

    max_worker_time /1000 as [大执行时间(ms)],

    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 

        (case when qs.statement_end_offset = -1 

        then DATALENGTH(qt.text) 

        else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1) 

    as [使用CPU的语法], qt.text [完整语法],

    qt.dbid, dbname=db_name(qt.dbid),

    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName

FROM sys.dm_exec_query_stats qs with(nolock)

cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

where  execution_count>1

ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

     不过DMV记录的是一些瞬时或者部分时间内系统收录的语句，往往不够全面，如果我们能每天在服务器上开一个Trace文件，通过Trace文件来分析就会比较全面了，

如下：

   如果你平时有对Trace文件进行分析和格式化并生成相关图表的话，那优化起来就更加有针对性了（格式化后，前十位消耗CPU的语句）：

    找到了语句，就可以来优化语句了，该加索引的加索引，该做调整的做调整，但是如果我们分析出来的语句该有的索引都有，该改进的地方也都改进了，它因为数据量

或者访问频繁等原因暂用CPU的资源依然相当高，那怎么办？我们还有其他的方法处理吗？还是有的，主要的途径有如下几种：

• 查找等待信息，如果CPU类型的等待排在前面（主要是CXPACKET和SOS_SCHEDULER_YIELD两类），我们就可以认为是CPU的处理能力不足，此时为不影响其他的用户进程，可以将CPU的并行开销(调大cost     threshold for parallelism)和并行度做调整(调小max degree of parallelism)；

• 限制相关的语句使用CPU的数量（加上Option参数），如限制语句多使用两颗CPU：select *     from Products with(nolock) option(maxdop 2)

• 看看数据库使用的内存是否还可以做调整，或者系统内存是不是大部分被数据库使用；

• 查看一下索引的碎片是否过大，过大的话需要做索引的重新整理（这个操作需要在业务低峰时进行）；

• 查看下统计信息是否是新的，如果不是，更新统计信息（这个操作往往比较有效）；

• 判断语句是否使用了正确的执行计划，如果不是，删除已有的执行计划，再重新执行；如果是存储过程可以加 WITH     RECOMPILE。

相信采取了这些措施后，应该能让服务器的CPU压力有所缓解，如果还是不能解决问题，那就需要更换更好的CPU了（或者是添加更多的内存）。

数据安全

9.       备份与还原

     备份与还原是DBA一个很重要的任务，再怎么强调都不过分，就像国家花大价钱培养军人一样，养兵千日，用兵一时，它是关键时刻后的救命稻草；备份还原也有一些使用的技巧，如：备份计划和频率的制定，需不需要远程和本地双重备份，备份时我们是否要加“CheckSum”参数，是否需要做压缩备份，是否需要定期做还原测试，还原有问题时加“CONTINUE_AFTER_ERROR”尽量挽救数据，是否只需要做页还原，以及尾日志的备份和还原等等，如果要保证备份的数据的安全可靠的话，做远程和本

地双备份，做定期的还原测试这些都是必要的；如果还有人问做了Mirror或者LogShipping之后，还需要做备份吗？那请想下，如果某个重要的表被某个不小心的人删除了，

你能通过Mirror或者LogShipping找回吗？

10.核心数据多层保障

     核心的数据做多重的保障是必要的（虽然成本会变高），毕竟核心的数据很大程度上对一个企业能否继续运营起着关键作用，在出现问题时，如果一套保护方案出现问

题，我们还有其他的方案来恢复数据，这些保护的投入是值得的，下图罗列了常用的保护方案，DBA可以根据企业的实际情况（主要是成本因素），选取其中的某些技术方案，来保障数据的安全。

   以上十点只是我个人在做DBA的过程中总结的一些技巧，如大家有其他的一些方面的技巧，欢迎留言交流。

本文来源https://www.modb.pro/db/159005