SQL Server 数据库管理维护规范.docx
《SQL Server 数据库管理维护规范.docx》由会员分享，可在线阅读，更多相关《SQL Server 数据库管理维护规范.docx（19页珍藏版）》请在冰豆网上搜索。
SQLServer数据库管理维护规范
SQLServer数据库管理维护规范
Version1.0
2010-8-17
1修改记录
修改人
内容
日期
张泽府
初稿
2010-8-17
2简介
数据库管理和维护是指为了保证业务系统的稳定高效运行，而对数据库系统进行全面周密的可用性，可靠性，可扩展性等方面的措施，以保证数据库系统的稳定高效运行。
数据库系统的管理和维护主要包括：
Δ数据库用户以及权限的分配与维护
Δ数据库的备份与恢复的设置和演练
Δ数据库性能的定期巡检和优化
Δ数据库高可用性，可扩展性架构方面的不断研究和应用
Δ数据库方面新项目的可行性研究，根据预期规模确定合适架构
Δ数据库系统包括整体架构的监控
Δ不断学习和研究数据库领域最新技术，并适时投入应用
该数据库管理和维护标准是一般数据库系统的管理和维护的试行标准.通过此标准，来规范数据库系统的管理和维护.
我们认为在数据库系统的管理和维护时遵循一定的标准是非常重要并且是必要的.标准能使我们的DBA以统一的方式和风格进行数据库系统的管理维护,从而使我们的数据库系统运行更加规范，数据库结构更清晰，也使数据库系统具有高健壮性，高专业化.
该标准是一个关于数据库系统的管理和维护规范,我们的这个规范主要应用在SQLServer2005/2008上,经过适当的修改之后也可以应用在以前的版本,以后的版本.
SQLServer的数据库管理和维护是一个复杂的过程，包含了多种备份恢复技术，高可用技术，多服务器管理技术等，该文档不可能包含所有的基本内容，而只能从最常用的管理维护技术入手进行简单介绍。
我们非常欢迎你的补充反馈.如果你认为某个地方要被改进或者不满意,请与我们联系,我们会考虑把它们加到下一个版本中.
通过使用规范的一致的数据库管理运维方案，能给我们的系统带来以下优点：
♦减轻数据库管理人员（DBA）的工作复杂度，使他们可以很容易的从一个数据库系统的管理维护转移到新数据库系统的维护.
♦可以大幅加快数据库管理维护相关脚本或者设置的部署时间，尤其在维护庞大的数据库系统时.
♦可以有效的实现团队协作，在大规模的数据库系统环境中通常要有一个DBA团队进行数据库系统的管理维护，通过使用统一的标准，可以轻松实现协作工作.
♦可以有效的节省数据库系统排错时间，通过使用统一的数据库监控和恢复标准，可以迅速定位故障，并为处理错误节约时间，这在24*7的数据库系统中尤其有用。
以下章节将介绍常用的数据库管理维护方案。
3．数据库的物理环境
SQLServer的物理环境是指SQLServer所处的安装目录以及网络环境，数据库系统时整个业务系统的核心应用，在安装设置初期就要考虑其所处的环境，以避免安全性和可维护性上的问题。
3.1网络环境
对于SQLServer所处的网络环境，该文档建议使用以下基本原则：
ΔSQLServer服务器不使用公网IP地址，特殊情况除外，比如商务合作需求
Δ在局域网内，如果是低速的VPN环境，请慎重考虑SQLServer的高可用方案，原则上不建议使用镜像，复制等方案，但可以考虑ServiceBroker（异步）方案。
ΔSQLServer的服务端口不要使用默认端口1433，新端口设置后务必通知所有使用该数据库的开发人员
Δ配置Windows防火墙以开放SQLServer相应的服务端口
3.2目录设置
对于SQLServer的安装目录设置，该文档建议使用以下基本原则：
Δ用户数据库的数据目录要和日志目录存放在不同的磁盘驱动器上，尤其是业务比较繁忙的用户数据库
ΔTempdb数据库要单独存放在1个或2个磁盘驱动器上，尤其是业务比较繁忙的服务器实例
Δ数据库安装后要设置本地备份目录，原则上建议的目录结构如下
数据目录\实例名\数据库名\BackupFull
数据目录\实例名\数据库名\BackupDiff
数据目录\实例名\数据库名\BackupLog
保存备份的数据目录大小至少保证是数据库大小的10倍以上，或者至少保证能保留一周的备份文件
3.3文件设置
在建立数据库时的数据文件设置，该文档建议使用以下基本原则
Δ用户数据库
数据文件的数目建议和服务器CPU数目一致或者是CPU数目的1/2等，不建议使用默认的一个数据文件
注：
CPU数目是指逻辑CPU，数据文件的大小（size）要保持一致
日志文件要使用一个，所有类型的数据库日志文件都要保证是一个。
Δ系统数据库
对于系统数据库，尤其是tempdb数据库，要增加其数据文件的数目，一般建议设置为CPU数目的1/2
注：
CPU数目是指逻辑CPU，数据文件的大小（size）要保持一致
4．数据库账户管理
数据库账户是访问数据库资源的一种主体，在SQLServer中，通常的数据库账户是指SQLServer登录名以及相应数据库中的数据库用户的映射。
数据库账户的管理是整体数据库系统安全性管理的重要部分，在日常的开发和生产环境的数据库账户维护中，能够遵循一定的规范，对于保证数据库安全，进而保证整个应用系统安全有很重要的作用。
该文档建议使用以下的原则对数据库账户进行管理维护：
Δ账户最小权限原则
所有申请的数据库账户，要明确需要的权限，确保不需要的权限不要授予
Δ密码复杂度
所有账户要有合适的密码复杂度，尤其是生产环境，要保证足够的复杂度
Δ账户有效期限
记录所有账户的有效期限，在到期前通知用户是不是需要续期，尤其适用于产品策划人员等非数据库长期用户
Δ账户申请注销原则
所有数据库账户的申请要发邮件给数据库维护人员（DBA），并抄送相关主管领导人员，建议同时也使用纸质文档留存。
不再需要的数据库账户由数据库维护人员（DBA）发邮件给相应使用人员，通知账户注销。
Δ多账户原则
建议每一个或几个应用模块使用一个数据库账户，不要一个账户应用于所有应用模块。
以下是建议使用的数据库账户申请邮件格式：
1客户端服务器IP（本机使用可不填）：
2项目名称：
数据库名称:
3帐户名称（可由DBA填写）：
权限：
[]所有者[]读写[]只读[]其他
注:
最终用户名为，项目名称_数据库名称_账户名称
4账户截止有效期:
5．数据库备份和恢复
数据库备份和恢复技术是数据库系统维护工作中的重要技术，不论是开发测试环境的数据库，还是生产环境的数据库，建议都要进行备份，而且要确保备份文件可用。
对于数据库系统来说，当发生故障甚至是灾难性的故障的时候，数据库备份就是最有效的最后一道防线。
对于数据库维护人员来说，备份与恢复技术的熟练运用，加之规范性的操作，是企业数据库系统正常运行的重要保障。
创建SQLServer备份的目的是为了可以恢复已损坏的数据库。
但是，备份和还原数据必须根据特定环境进行自定义，并且必须使用可用资源。
因此，可靠使用备份和还原以实现恢复需要有一个备份和还原策略。
设计良好的备份和还原策略在考虑到特定业务要求的同时，可以尽量提高数据的可用性并尽量减少数据的丢失。
设计有效的备份和还原策略需要仔细计划、实现和测试。
测试是必需环节。
直到成功还原了还原策略中所有组合内的备份后，才会生成备份策略。
必须考虑各种因素。
其中包括：
∙您的组织对数据库的生产目标，尤其是对可用性和防止数据丢失的要求。
∙每个数据库的特性，包括：
大小、使用模式、内容特性以及数据要求等。
∙对资源的约束，例如：
硬件、人员、备份媒体的存储空间以及所存储媒体的物理安全性等。
设计备份和还原计划时，应根据您自身的特定环境和业务需求来考虑灾难恢复计划。
例如，假设失火了并且烧毁了您的24小时数据中心。
您是否有把握恢复数据？
恢复系统并保证系统运行需要多长时间？
您的用户能够承受丢失多少数据？
理想的情况是，灾难恢复计划应规定恢复所需的时间以及用户可以期望的最终数据库状态。
例如，可以确定在获取指定的硬件后，在48小时内完成恢复，并且保证最多能恢复到上周末时的数据。
灾难恢复计划可以通过多种方式构建，并且可以包含各种类型的信息。
灾难恢复计划类型包括：
∙获取硬件计划。
∙通信计划。
∙发生灾难时的联系人名单。
∙与负责处理灾难的人员的联系方式。
∙对计划拥有管理权的人员的信息。
∙每个恢复方案所需执行的任务的清单。
为了便于您检查灾难恢复的进度，将初始化已完成的任务，并在清单中指示任务完成时间。
以下是建议使用的数据库备份，恢复计划
5.1开发测试环境
对于开发和测试环境的数据库，一般不必采取过于严密的备份策略。
该文档建议使用如下原则设置备份恢复策略
Δ每天进行一次完全备份，备份保留3天
Δ根据开发人员需求，可调整备份策略，比如单独备份存储过程，6小时备份一次等
5.2生产环境
5.2.1用户数据库
生产环境的用户数据库是企业的重要数据资源，必须根据企业的要求采取严密的数据库备份恢复策略。
该文档建议按照以下原则设计生产环境用户数据库的备份恢复策略
Δ完全备份
每天一次，如果数据库特别大，可考虑进行文件组备份，或者适当减少备份频率
Δ差异备份
每4小时一次，如果数据库事务特别频繁，可考虑取消差异备份，用日志备份代替
Δ日志备份
每15分钟一次，可以根据实际数据库的事务量适当调整
Δ以上备份的保留时间原则上建议本地至少保留一周，根据企业实际需求可以适当调整
Δ根据某些数据库高可用架构的特点，可以适当调整以上备份策略，
例如，
当存在LogShipping架构使用的时候，就需要取消相应库的日志备份
当整体数据库系统或者某局部数据库系统是完全的分布式架构的时候，不必在每个数据库服务器都设置备份计划，而仅需在架构中的关键位置设计即可
5.2.2系统数据库
系统数据库为用户数据库功能的正常运行提供了必要的基础环境，因此系统数据库必须采取必要的备份，尤其是master,msdb数据库
该文档建议按照以下原则设计生产环境中系统数据库的备份策略
Δ完全备份
每天一次，备份至少保留一个月
5.2.3异地备份
设置异地备份是设计整个灾难恢复计划的重要步骤，“异地”主要是按照物理服务器所处位置决定的，可分为同机房，同城，异城。
该文档按照以下原则设计生产环境中异地备份策略
Δ采用拷贝或者其他同步工具软件
Δ采用数据库技术，日志传送或者ServiceBroker
5.2.4恢复计划
对于每一个备份计划都要知道相应的恢复计划，对于整体数据库系统，也有必要制定灾难性恢复计划。
6．数据库监控
监视数据库的目的是评估服务器的性能。
有效监视包括定期拍摄当前性能的快照来隔离导致问题的进程，以及连续收集数据来跟踪性能趋势。
MicrosoftSQLServer和MicrosoftWindows操作系统提供实用工具，使您可以查看数据库的当前状态并跟踪性能的状态变化。
监视SQLServer使您可以执行下列操作：
∙确定是否可以提高性能。
例如，通过监视常用查询的响应时间，可以确定是否需要更改表的查询或索引。
∙评估用户活动。
例如，通过监视尝试连接到SQLServer实例的用户，可以确定安全设置是否充分以及是否需要测试应用程序或开发系统。
例如，通过在执行SQL查询时对其进行监视，可以确定这些查询是否编写正确并生成预期的结果。
∙解决任何问题或调试应用程序组件（如存储过程）。
监视操作非常重要，因为SQLServer在动态环境中提供服务。
应用程序中的数据在变化。
用户需要的访问类型在变化。
用户连接的方式在变化。
甚至，访问SQLServer的应用程序的类型也可能在变化，而SQLServer自动管理系统级资源（如内存和磁盘空间），因此对广泛系统级手动优化的需要已降至最低。
但是，管理员可以通过监视来标识性能趋势来确定是否有必要进行更改。
若要有效监视SQLServer的任何组件，请按下列步骤进行操作：
1.确定监视目标。
2.选择相应工具。
3.标识要监视的组件。
4.选择那些组件的度量。
5.监视服务器。
6.分析数据
该文档建议使用如下方式监控数据库引擎的活动：
Δ数据库作业
使用Job定期执行存储过程，存储过程调用DMV
利用这种方式，可以查询数据库引擎的很多活动，比如索引使用，会话活动，事务活动等
Δ跟踪
利用Trace可以跟踪大部分的数据库引擎活动，包括登录活动，语句执行，错误等
ΔDDL触发器
利用DDL触发器可以监控数据库对象或者服务器对象的变化。
Δ事件通知
事件通知（EVENTNOTIFICATION）可对各种Transact-SQL数据定义语言（DDL）语句和SQL跟踪事件做出响应，并将这些事件的相关信息发送到ServiceBroker服务.
事件通知可以用来执行以下操作：
∙记录和检索发生在数据库上的更改或活动。
∙执行操作以异步方式而不是同步方式响应事件。
可以将事件通知用作替代DDL触发器和SQL跟踪的编程方法。
事件通知在事务范围以外异步运行。
因此，与DDL触发器不同，事件通知可以用于数据库应用程序中以响应事件而无需使用中间事务定义的任何资源。
建议对于数据库内的DDL事件可以采用此种方式监控。
Δ报警
根据监控的结果发报警信息，可以使用第三方通讯工具（发邮件或短信）。
Δ监控结果的展现
默认情况下监控结果要保持在数据库表中或者生产文件，根据实际情况可以借用一些开源框架展现监控结果，比如WebChart等
7．数据库维护
数据库维护是根据数据库系统承载的业务量的不断增加，以及数据库容量的不断增加，而不断进行性能分析，性能调整，架构分析，架构调整的过程。
数据库维护一定是长期的过程，需要维护人员不断的分析调整，不断的学习研究新技术，不断的优化。
7.1性能优化
性能优化是一个范围很广的题目，在此不叙述相关的技术，只介绍一些优化原则。
Δ根据监控结果，至少一周进行一次性能优化（调整索引，SQL语句优化）
7.2恢复或者切换演练
生产环境部署测试完毕后，要根据系统实际运行情况制定恢复和切换的演练策略，
比如2周进行一次
附录1数据库备份，验证备份脚本
CREATEPROC[dbo].[BackupDB]
（
@databasenamevarchar（100）=null,
@bcktypevarchar（100）=null,
@bckPosvarchar（8000）=null
）
WITHENCRYPTION
AS
SETNOCOUNTON
IF@databasenameisnull
BEGIN
RAISERROR（N'缺少@databasename参数,该参数不能为空',16,1）
RETURN-1
END
IF@bcktypeisnull
BEGIN
RAISERROR（N'缺少@bcktype参数,该参数不能为空（full,diff,log）',16,1）
RETURN-2
END
IF@bckPosisnull
BEGIN
RAISERROR（N'缺少@bckPos参数,不能为空备份目录',16,1）
RETURN-3
END
IF（SELECTmirroring_role_desc
FROMsys.database_mirroring
WHEREdatabase_id=db_id（@databasename））='MIRROR'
BEGIN
RAISERROR（N'镜像数据库不允许备份',16,1）
RETURN-4
END
IF@databasename='tempdb'
BEGIN
RAISERROR（N'Tempdb不允许备份',16,1）
RETURN-5
END
DECLARE@datevarchar（100）,@sqlvarchar（1000）,@sql1varchar（2000）
SELECT@date=CONVERT（varchar（20）,GETDATE（）,112）+REPLACE（CONVERT（varchar（5）,GETDATE（）,108）,':
',''）,
@sql='',@sql1=''
IF（@bcktype='full'）
SELECT@sql=
'BACKUPDATABASE['+@databasename+']TODISK=N'''+@bckPos+'\'+@databasename+'_Full_'+@date+'.bak'''
@sql1='RESTOREVERIFYONLYFROMDISK=N'''+@bckPos+'\'+@databasename+'_Full_'+@date+'.bak'''
IF（@bcktype='diff'）
SELECT@sql=
'BACKUPDATABASE['+@databasename+']TODISK=N'''+@bckPos+'\'+@databasename+'_Diff_'+@date+'.bak''WITHDIFFERENTIAL'''
@sql1='RESTOREVERIFYONLYFROMDISK=N'''+@bckPos+'\'+@databasename+'_Diff_'+@date+'.bak'''
IF（@bcktype='log'）
SELECT@sql=
'BACKUPLOG['+@databasename+']TODISK=N'''+@bckPos+'\'+@databasename+'_Log_'+@date+'.trn'''
@sql1='RESTOREVERIFYONLYFROMDISK=N'''+@bckPos+'\'+@databasename+'_Log_'+@date+'.trn'''
IF@sql>''AND@sql1>''
BEGIN
EXEC（@sql）
EXEC（@sql1）
END
ELSE
RAISERROR（N'请检查输入参数，备份目录...',16,1）
SETNOCOUNTOFF
附录2数据库恢复脚本
CREATEPROC[dbo].[RestoreDB]
（
@databasenamevarchar（100）=null,
@bcktypevarchar（100）=null,
@bckpathvarchar（8000）=null,
@islastlogbit='0'
）
WITHENCRYPTION
AS
SETNOCOUNTON
IF@databasenameisnull
BEGIN
RAISERROR（N'缺少@databasename参数,该参数不能为空',16,1）
RETURN-1
END
IF@bcktypeisnull
BEGIN
RAISERROR（N'缺少@bcktype参数,该参数不能为空（full,diff,log）',16,1）
RETURN-2
END
IF@bckpathisnull
BEGIN
RAISERROR（N'缺少@bckpath参数,请提供备份文件全路径',16,1）
RETURN-3
END
DECLARE@sqlvarchar（1000）
IF@islastlog='0'
BEGIN
IF@bcktypeIN（'full','diff'）
SELECT@sql='RESTOREDATABASE['+@databasename+']FROMDISK=N'''+@bckpath+'WITHNORECOVERY,STATS=10'''
IF@bcktypeIN（'log'）
SELECT@sql='RESTORELOG['+@databasename+']FROMDISK=N'''+@bckpath+'WITHNORECOVERY,STATS=10'''
END
ELSE
SELECT@sql='RESTORELOG['+@databasename+']FROMDISK=N'''+@bckpath+'WITHRECOVERY,STATS=10'''
IF@sql>''
BEGIN
EXEC（@sql）
END
ELSE
RAISERROR（N'请检查输入参数...',16,1）
SETNOCOUNTOFF
附录3清除备份文件脚本
CREATEPROC[dbo].[DelBackup]
（
@bckPosvarchar（8000）=null,
@bcktypevarchar（100）=null,
@Hoursint=1
）
WITHENCRYPTION
AS
SETNOCOUNTON
IF@bcktypeisnull
BEGIN
RAISERROR（N'缺少@bcktype参数,该参数不能为空（bak,trn）',16,1）
RETURN-1
END
IF@bckPosisnull
BEGIN
RAISERROR（N'缺少@bckPos参数,不能为空备份目录',16,1）
RETURN-2
END
declare@DeleteDatenvarchar（50）,@DateTimedatetime,@sqlvarchar（1000）
set@DateTime=DateAdd（Hour,-@Hours,GetDate（））
set@DeleteDate=（SelectCONVERT（varchar（10）,@DateTime,120）+'T'+Convert（nvarchar,@DateTime,108））
SET@sql='EXECUTEmaster.dbo.xp_delete_file0,N'''+@bckPos+''''+',N'''+@bcktype+''''+','''+@DeleteDate+''',1'
IF@sql>''
BEGIN
EXEC（@sql）
END
ELSE
RAISERROR（N'请检查输入参数...',16,1）
SETNOCOUNTOFF
附录4事件通知例子
CREATEQUEUE[//www.AAA.com/DBName/TabDDLQueue]
GO
CREATESERVICE[//www.AAA.com/DBName/TabDDLService]
ONQUEUE[//www.AAA.com/DBName/TabDDLQueue]
（
[http:
//schemas.mi
升级会员 