SQLServer数据库管理维护规范2019年修改

1、SQLServer数据库管理维护规范北京云星宇科技服务有限公司2018年12月第一章目录第二章简介二-4第二章数据库系统物理与逻辑规划二-42.1 数据库系统存储规划二-42.2 数据库系统的CP5口内存规划二-52.3 数据库系统逻辑规划二-6第三章数据库账户管理二-73.1 数据库账户规则二-7第四章数据库的备份与恢复二-74.1 数据库的备份二-7第五章数据库的定期巡检和维护二-175.1 数据库的定期巡检二-171内存压力监控二-172CPIB力监控二-213对数据库IO监控二-22第6章数据库维护二-236.1 数据库完整性检查（重要每月）二-236.2 清空数据库日志（重要每月）二

2、-246.3 重组数据库表（重要季度）二-246.4 更新数据库统计信息（重要每月）二-26第七章数据库故障及标准处置方案二-277.1 数据库系统标准化启动与关闭流程二-277.2 人为操作失误造成的数据库故障二-287.3 数据库服务器故障（但磁盘阵列正常）.二-297.4 磁盘阵列故障（数据块故障）二-307.5 磁盘阵列故障（数据库无法访问）二-327.6 磁盘阵列故障（无法识别）二-32第八章规范的补充与修改二-34第二章简介数据库管理和维护是指为了保证业务系统的稳定高效运行，而对数据库系统进行全面周密的检查，维护优化。以保证数据库系统的稳定高效运行。通过使用规范的一致的数据库管理运

3、维方案，能给我们的系统带来以下优点，减轻数据库管理人员（DBA）的工作复杂度，可以提高数据库性能.可以有效的节省数据库系统排错时间，通过使用统一的数据库监控和恢复标准，可以迅速定位故障，并为处理错误节约时间，这在24*7的数据库系统中尤其有用。以下章节将介绍常用的数据库管理维护方案。2数据库物理与逻辑规划3数据库账户管理4数据库的备份与恢复5数据库性能监控6数据库维护7数据库故障及标准处置方案第二章数据库系统物理与逻辑规划2.1 数据库系统存储规划SQ激据库是传统关系型数据库，也就是建立在关系模型基础之上的数据库。关系型数据库本身基于IO的数据库，所以一个良好的数据库存储规划是关键，数据库从I

4、/O子系统获得最快响应非常重要。首先首发集团数据库存储系统使用磁盘阵列，RAIDS别J为5,RAID5是一种存储性能、数据安全和存储成本兼顾的存储解决方案。RAID5具有和RAID0相近似的数据读取速度，只是多了一个奇偶校验信息，写入数据的速度比对单个磁盘进行写入操作慢，在RAID5中有“写损失”，即每一次写操作将产生四个实际的读/写操作。所以如果解决IO问题带来的数据库瓶颈，在使用RAID瞰别的磁盘阵列时最少需要8块硬盘组成的阵列。2.2 数据库系统的CPlf口内存规划Server数据库所需的内存与正运行SQLServer的服务器上托管的内容数据库的大小直接关联。微软(Microsoft)官

5、方要求内存劲可能的大。分中心数据库系统属于中型数据库官方给的推荐配置为16G内容数据库的总计大小小型生产部署的最低要求针对运行SQLServer的计算机建议的RAM8GB中型生产部署的最低要求16GB（50徵上）建议最高2TB32GB在运行50G数据以上的服务器上，建议每个CPU的L2缓存最低为2MB以提高读取数据的性能。建议CPU勺使用率不能超过25%2.3数据库系统逻辑规划理想情况下，应在单独的存储系统上放置tempdb数据库、数据文件、事务日志文件。但是以目前的情况，无法达到，所以建议Tempdb数据库放置在数据库服务器的D盘，将数据文件，事务日志文件放置在磁盘阵列中。tempdb数据文

6、件数应等于或者小于CPU内核数（内核是物理）相同，数据文件的数量=CP激-1,而且tempdb数据文件应设置为同等大小500M数据文件的数目应小于或等于CPU内核的数目。数据文件的数量=CP敢-1,并且数据库文件创建大小相同的数据文件10G在规划超过建议大小(200GB)的内容数据库时，请将数据库自动增长值设置为固定兆字节(MB)数，而不是设置为百分比。这样做可以减少SQLServer增加文件大小的频率。第三章数据库账户管理3.1 数据库账户规则数据库账户是访问数据库资源的一种主体，在SQLServer中,通常的数据库账户是指SQLServer登录名以及相应数据库中的数据库用户的映射。数据库账

7、户的管理是整体数据库系统安全性管理的重要部分，对于保证整个应用系统安全有很重要的作用。建议使用以下的原则对数据库账户进行管理维护1首先禁用Window©佥证，需要在两个地方设置，服务器属性->安全性把里面的服务器身份验证选为SQLServer和Window弱份验证方式，安全性->登录名Administrators的属性中的服务器角色，把权限都去掉(勾全去掉)。2密码复杂度3账户最小权限原则，除了信息安全组长和数据库管理员，为系统运维人员设置只有数据查询权限的工号。4账户申请注销原则，无使用的账户注销。第四章数据库的备份与恢复4.1数据库的备份数据库备份和恢复技术是数据库系

8、统维护工作中的重要技术，生产环境的数据库，建议都要进行备份，而且要确保备份文件可用。对于数据库系统来说，当发生故障甚至是灾难性的故障的时候，数据库备份就是最有效的最后一道防线。对于数据库维护人员来说，备份与恢复技术的熟练运用，加之规范性的操作，是企业数据库系统正常运行的重要保障首发集团对于分中心数据库实施每周一次的全量备份，每月对数据库备份进行恢复测试.1数据库自动备份，日常工作中利用SQLSQLSERVER200的维护计划对数据库进行定期的备份，这样一方面可以对数据库进行备份保证数据安全另一方面也可以减轻对维护人员的负担。实现数据库的定时自动备份是利用SQLSERVER20明带的维护计划创建

9、一个计划对数据库进行备份，下面我们将SQLSERVER2008动备份图解教程介绍给大家。点击“维护计划向导”后跳出对话框，如图所示:轴蜒的计盘向与SQLServer维护计划向导何用山;向牛了可执打第引维护豆跖独据国标i-相有执行数据比备份"匕向店创建7T：1,左SflLS.vrNutAMtn4nlStudo中演姆4：*汽+切,扁转冠护计1，丛添加一些新任易或者在考场任秀之闰定义工柞淹.Wit)3点击“下一步”如图所示:注：名称可以自定义，根据分中心或者收费所所需自定义。填写好名称及相关说明作个记号，点击“更改”来设定维护计划，如图所示：可以为选择执的时间段，每天、每周、每月可以根据各

10、级SQIB据库的需求来制定备份的时间，这里作演示就选择在每天的0:00®行，点击“确定”再点“下一步”如图所示：注：我们一般选择完整备份分中心数据库;可以根据收费所的情况可以选用差异备份；选择你需要备份的任务，我这里就先择“备份数据库（完整、差异、事务日志）”，很明了点击“下一步”如图所示：出现刚刚所选择的三项你可以选择他们所执行的顺序，选好后点击“下一步”如图所示:注：这里是选择数据库名字的，根据需要选择；如：分中心有数据库和图片数据库，可以分别建立任务实现备份选择备份的数据库存放的目录，设置备份压缩：有默认服务器设置，压缩备份等选项，因为数据库较大所以就选择压缩，根据本分中心的实

11、际情况进行操作：点击"下一步”，下面的操作是对于这前我们所选择的“维护任务”操作和“上一步”一样这里就不截图说明，最后点击“下一步”如图所示:选择SQLSERVER2008动备份维护计划的报告文件所存放位置点击“下一步”如图所示点击“完成”这样就完成了SQLSERVER008自动备份图解注意：在利用SQLSQLSERVER2008维护计划对数据库进行定期的备份时要启动“SQLSERVE益理”服务2使用windows脚本实现数据库的完整备份将mybackup.batmybackup.sql放在DS根目录修改mybackup.bat文件以下是mybackup.bat文件rdtaihudb

12、/s/q注释：删除上次使用的目录mdtaihudb注释：建立一个目录，用于放备份出来的数据库setpath=%path%;d:ProgramFilesMicrosoftSQLServer100ToolsBinn注释：路径为SQ安装在那个盘路径就指定那个盘路径setdates=%date%time%echo%dates%>>D:logs.txtechoSql_Back_Start>>D:logs.txtecho.>>D:logs.txtSQLCMD.exe-S10.11.200.51-Usa-Psa-iD:mybackup.sql注释：IP为数据库服务器IPs

13、etdates=%date%time%echo%dates%>>D:logs.txtechoSql_Back_Finish>>D:logs.txtecho.>>D:logs.txtecho.>>D:logs.txtnetuse10.11.200.105T_BRANCH_old111111/user:"administrator"注释：修改IP为数据库备份服务器IP修改密码xcopytaihudb10.11.200.105T_BRANCH_old/s/e/y注释：IP为备份服务器IP在备份服务器上新建T_BRANCH_o血件夹，

14、并进行共享。文件夹名字请根据各分中心实际情况进行命名。Rem命令结束Exit- 以上是mybackup.bat文件修改mybackup.sql文件- 以下是mybackup.sql文件DECLAREnamevarchar(45)DECLAREdatetimechar(17)DECLAREpathvarchar(255)DECLAREbakfilevarchar(290)setname='t_MNG'注释：此处为备份数据库名字setdatetime=CONVERT(char(11),getdate(),120)+REPLACE(CONVERT(char(8),getdate(),

15、108),':',")setpath='D:taihudb'注释：此处为数据库备份路径，文件夹需自己手动建立setbakfile=path+'/'+name+'_'+datetime+'.BAK'BACKUDATABAST_MNGTODISK=bakfileWITHNOFORMAT,NOINIT,NAME=N'T_MNG-完整数据库备份',SKIP,NOREWIND,NOUNLOAD,STATS=10GOGO以上是mybackup.sql文件根据实际建立计划windows务第五章数据库的定

16、期巡检和维护5.1数据库的定期巡检1内存压力监控内存巡检主要分为外部物理内存压力和内部物理内存压力。进行检查的时候首选需要查看服务器是否存在外部物理内存压力。通过Window旺务管理器查看物理内存的占用情况，如下图所示：可以得到如下信息：物理内存（mb总数12000可用数4000目前系统尚有可用内存可用率33%因此可以确定服务器存在内存压力，需要增加内存。内存巡检sql数据库内部物理内存压力在SQLServer所占用的内存中，绝大部分应该是缓存的数据页和索引页。接下来我们将通过执行如下语句，查看SQLServer缓冲池中数据库缓存的页的情况：Selectpage_type,count(page

17、_type)Fromsys.dm_os_buffer_descriptorsGroupbypage_typeOrderbycount(page_type)desc通过计算可以得到该数据库在缓冲池中缓存的数据量约为435684x8KB+8613x8KB=3.3GB在了解了SQLServer缓冲池中缓存的数据页和索引页的数据量以后，我们接下来要查看两个很重要的性能指标，即每秒交换的页数和缓冲池的命中率。如果每秒钟内存和磁盘交换的页数过多且缓冲池的命中率过低，则表明内存大小不够，很多查询都无法在缓冲池中找到相应结果，只能从磁盘中定位相应数据页，并把相应数据页缓存到内存中，这样一来势必会影响性能。缓冲

18、池命中率过低，意味着缓存的数据页和索引页不够目前每秒交换的页数为0,且缓冲池白命中率为100%表明则说明数据库的SQ设计良好，无需优化SQ晤句内存巡检总结，首先通过性能监视器查看内存缓冲池的命中率，如果命中率高于85%则说明数据库的SQ设计良好，无需优化SQL语句，但是外部可用内存只有30嘀要增加内存2CPUS力监控通过任务管理器查看CP改用率通过任务管理器以及SQLServer活动监视器可以看到，目前CPU勺平均占用率在5艰内。目前服务器的CP讲不存压力一口四任其管署文件审）选嗔m互看g和助00应用程序逗程胴弟性能i族网用户fcru使用车cru停用记录内存（W6J存4包44255552227

19、0回4SO1-04-32218/23要已可空核心内存蒯）分贡蜀耒分页160谡近散开机时间提幺LGEJ睡散：8。一可候用M*一|柳理内存：3-查看CPUScheduler通过查看Scheduler的信息，我们可以了解到具体每一个CPU!程的压力情况。Scheduler是SQLOS一个非常重要的组件，用来在各个CPl±间分发负载。执彳f如下语句，查看Scheduler的当前信息:Selectscheduler_id,cpu_id,status,runnable_tasks_count,active_workers_count,load_factor,yield_countfromsys.

20、dm_os_schedulerswherescheduler_id<255该语句的返回结果如下图所示:歹Lj5型leggli电d*工rbi&*cluprag亏直白上rlDad._factorrfCQCL-."1i.ZH.；E.1kli*a3hrdu1et-dNSSie!Omff“口西|1mwivlAii2、.口中曲*，/ajrtlrwld-GMirt*1：1?VISGLFOS.5l.rJF6-7-113VISIBLEONLIJE。23>9S534D2XViseiJEZLNEQ22&2*29力115ViSiELEonlihe0225715MS5416Y3H上

21、gLHE。1264MJG517VISIBLEONUNEQ12划?柝IBVISiOLEONLINE012了ISVISREONIFJE02357衿？必9的VISIBLEOMLIJEQ221Q97B1W1092iViSBiJEOMLNE022775Z250JU化空visbleo*fje-a2"石的第1FT八）fh>尹mi*IFfW.I11M2JCPUI检总结，首先查看cpu的使用率和Scheduler信息，发现CPU无任何压力。3对数据库IO监控通过性能监视器查看磁盘IO压力针对磁盘IO是否存在压力，主要通过如下几个性能计数器来查看，目前环境中IO的读与写的值基本都保持在0,说明磁盘

22、IO不存在性能瓶颈。W4TESM3Avg.DiskQueue平均每块磁盘不超过2LengthAvg.Disksec/Read平均值小于20msAvg.Disksec/Write平均值小于20msCurrentDiskQueue平均每块磁盘不超过2Length此次巡检发现数据库状态完整统一，CPU!IO无压力，物理内存有明显的性能瓶颈，建议增加内存第6章数据库维护6.1 数据库完整性检查（重要每月）每季度对数据库进行完整性检查，所有对象分配和数据，结构完整性进行检查.使用DBCCCHECKDB（DB_NAME命令n宜工u°匕£ogn白勺OB-OO2吉FP"行对家ay

23、s_A.pp±1cza11.Erna'1JXJ白工fJoS11_CUECM纲DBCC结果*对蒙"£11_-H=CK*的1页中有=行心AS«_OR<iAfrr_r>OHN）DBCC结二期”对象HA3OaOAK-DOWW的O羽中有O亍金"七的3QCC结果.对象“_E"e=的eea而中有工”<争行*CHECKDB叠脸§库”T_mRMCH，王我现口金分理错谟扣口个一现由。执行言甚海艰输出森陵信jfc*密骂系统管理员联系.6.2 清空数据库日志(重要每月)首发集团的数据库7*24小时运行，产生大量日志，会大量

24、占用磁盘空间，导致硬盘IO性能下降，因此需要每月清空数据库日志。注意清空数据库日志以后，需要扩大到10G10%曾长。否则出现重大事务无法完成。Uset_branchselectname,sizefromsys.database_files;ALTERdatabaset_branchsetrecoverysimplewithno_wait;alterdatabaset_branchsetrecoverysimple;DBCCshrinkfile(N'T_BRANCH_log',0,truncateonly)alterdatabaset_branchsetrecoveryfullw

25、ithno_waitalterdatabaset_branchsetrecoveryfull6.3 重组数据库表（重要季度）首发集团的数据库7*24小时运行，产生大量数据，在进行了大量的数据更新操作后，索引页和数据页会被分成多块，形成严重的索引与数据页碎片，这样会降低扫描索引和数据页的速度，降低检索数据速度。所以数据库管理员需要每月对数据库索引和数据页进行重组。USET_BRANCH;DECLAREtablenamevarchar(100)DECLAREcommandnvarchar(4000)DECLAREauthors_cursorCURSORFORSelectnamefromsysobj

26、ectswherextype='u'orderbyidOPENauthors_cursorFETCHNEXTFROMauthors_cursorINTOtablenameWHILEFETCH_STATUS=0BEGINSET©command=N'ALTERINDEX'+N'ALL'+N'ON+tablename+N'REBUILD'EXEC(command);PRINTN'Executed:'+command;FETCHNEXTFROMauthors_cursorINTOtablenameENDd

27、eallocateauthors_cursor表重组的好处就是可以减少碎片，相同的数据，扫描更少的页DBCCSHOWCONTIG(L05T201710)已执行TABLE级别的扫描。- 扫描区数:154777- 扫描密度最佳计数：实际计数.:5.7%154019:286380ALTERINDEXALLONL05T201710REBUILDDBCCSHOWCONTIG(L05T201710)已执行TABLE级别的扫描。- 扫描区数:154777- 扫描密度最佳计数：实际计数.:99.51%154019:1547806.4 更新数据库统计信息(重要每月)对于SQ语句，SQLSERVER很多种方法来完

28、成它。有些方法适合于数据量比较小的时候，有些方法适合于数据量比较大的时候。同一种方法，在数据量不同的时候，复杂度会有非常大的差别。SQLSERVRE®需要知道每一种操作所要处理的数据量有多少，从而估算出复杂度，选取一个代价最小的执行计划。说得通俗一点，SQLSERVR能够知道数据是“长得什么样”的，才能用最快的方法完成指令。怎么能够让SQLSERVER道数据的分布信息呢？在数据库管理系统里有个常用的技术，就是数据的“统计信息”(STATISTICSoSQLSERVE匿通过它了解数据的分布情况的。Uset_branchGoExecsp_updatestats第七章数据库故障及标准处置方

29、案7.1 数据库系统标准化启动与关闭流程由于供电系统导致关闭与启动数据库系统，需要执行数据库标准化流程。关闭数据库系统标准化流程1关闭传输服务器传输服务，关闭WEB务器WE服务，关闭一切与数据库系统相关的应用系统。2全量备份数据库，把数据库备份传到备份服务器上，本地数据库服务器和备份服务器均保留一份备份3关闭双机系统中的备机4待双机系统软件检测备用服务器已经停机后，关闭SQL2008R2数据库服务，在MSSQLSERVER停止后，关闭主服务器5在备用服务器与主服务器完全关闭后，关闭磁盘阵列启动数据库系统标准化流程1首先确保与数据库系统相关的应用关闭2开机磁盘阵列，需要5分钟磁盘阵列启动正常，提

30、示ready3查看磁盘阵列状态，等待磁盘阵列提示ready以后，开启主机4主服务器开启后，打开双机软件，待主服务器识别磁盘阵列，开始数据库服务后，开启备用服务器，开启应用程序7.2 人为操作失误造成的数据库故障由于人为操作失误造成的数据库故障，如droptable,truncatetable等，或者update语句没有写上正确的where条件，导致系统数据出现问题。需要人员及备用设备信息安全组长，高级数据库工程师，备份服务器人为操作失误造成的数据库故障标准化流程1 立即关闭所有与数据库系统有关应用程序2 在备份服务器上，还原修改前的数据库备份3 根据数据库备份，恢复数据二-28/344 由信息

31、安全组长，高级数据库工程师审核以后开启所有应用7.3 数据库服务器故障（但磁盘阵列正常）首发集团的数据库系统，通过RoseHA?成双机系统，当系统出现故障时，如主服务系统宕机，RoseHA将确定故障原因，并采取相应对策，并将这些应用切换到备份服务器上。不需要管理员干预。本标准化流程处理2台数据库服务器同时出现故障。需要人员及备用设备信息安全组长，高级数据库工程师，备用服务器，信息安全笔记本电脑，内网专用LB,磁盘阵列存储线。数据库服务器故障（但磁盘阵列正常）标准化流程1 信息安全人员关闭所有与数据库连接的应用程序2 信息安全人员确定主数据库服务器与备用数据库服务器关机3 信息安全人员关闭磁盘阵

32、列4 信息安全组长通过笔记本电脑连接磁盘阵列，把SQL2008R2数据库文件与日志文件，拷贝到笔记本电脑中5 信息安全组长，把数据库文件与日志文件拷贝到备用服务器6 信息安全组长，修改备用服务器，主机名，IP地址，模拟数据库系统7 高级数据库工程师，在备用服务器上，修改备用服务器的数据库用sa密码与权限，附加数据库文件与日志文件8 数据库附加成功后，高级数据库工程师审核数据库状态，通过DBCCCHECKDB（数据库名”）命令，无错误后，开启数据库服务WfchCMIla。件IT唱gB>IVIVitl(wn*,*一一；-J,IS*We，ufe加）工仃，%Fnm7.4 磁盘阵列故障（数据块故障

33、）首发集团的数据库系统，数据库数据文件与日志文件存放在磁盘阵列王7低盘阵列在出现硬盘故窿据库故障时，需要信息安全组一|长与高级数据库工程师，启动应急预案，快速定位故障，恢复数据库系统。需要人员及备用设备信息安全组长，高级数据库工程师，备用服务器。磁盘阵列故障（数据块故障）标准化流程1 关闭与数据库系统连接的应用程序2 快速定位故障点，使用dbcccheckdb（“数据库名”）对象'Tb_Archives_File_1'有3777行，这些行位于172页中。CHECKDB发现了0个分配错误和2个一致性错误3 快递定位故障点以后，修改数据库为单用户模式，使用命令alterdataba

34、se数据库名setsingle_user4 修复故障点，使用数据库修复对象命令dbccchecktable('Tb_Archives_File_1'repair_allow_data_loss)5 修复了数据库故障以后，使用dbcccheckdb(“数据库名”)，检查数据库是否处于一致状态,数据库处于一致状态以后,CHECK现了0个分配错误和0个一致性错误，修改数据库为多用户模式，使用命令alterdatabase数据库名setmultiuser7恢复数据库系统，开启应用程序SflIJafi1MiD>jfiSIV)鱼1皿39=11fMH)EOOJt±£'.c)帮幼叱II5-J前建事涵如为必H苣myt，卜YW1国丁，心