SQL Server 数据库管理维护规范.docx

1、SQL Server 数据库管理维护规范SQL Server 数据库管理维护规范 Version 1.0 2010-8-17 1 修改记录修改人内容日期张泽府初稿2010-8-172 简介数据库管理和维护是指为了保证业务系统的稳定高效运行，而对数据库系统进行全面周密的可用性，可靠性，可扩展性等方面的措施，以保证数据库系统的稳定高效运行。数据库系统的管理和维护主要包括： 数据库用户以及权限的分配与维护 数据库的备份与恢复的设置和演练 数据库性能的定期巡检和优化 数据库高可用性，可扩展性架构方面的不断研究和应用 数据库方面新项目的可行性研究，根据预期规模确定合适架构 数据库系统包括整体架构的监控

2、不断学习和研究数据库领域最新技术，并适时投入应用该数据库管理和维护标准是一般数据库系统的管理和维护的试行标准.通过此标准，来规范数据库系统的管理和维护. 我们认为在数据库系统的管理和维护时遵循一定的标准是非常重要并且是必要的.标准能使我们的DBA以统一的方式和风格进行数据库系统的管理维护,从而使我们的数据库系统运行更加规范，数据库结构更清晰，也使数据库系统具有高健壮性，高专业化. 该标准是一个关于数据库系统的管理和维护规范,我们的这个规范主要应用在SQLServer2005/2008上,经过适当的修改之后也可以应用在以前的版本,以后的版本. SQLServer的数据库管理和维护是一个复杂的过程

3、，包含了多种备份恢复技术，高可用技术，多服务器管理技术等，该文档不可能包含所有的基本内容，而只能从最常用的管理维护技术入手进行简单介绍。我们非常欢迎你的补充反馈.如果你认为某个地方要被改进或者不满意,请与我们联系,我们会考虑把它们加到下一个版本中. 通过使用规范的一致的数据库管理运维方案，能给我们的系统带来以下优点： 减轻数据库管理人员(DBA)的工作复杂度，使他们可以很容易的从一个数据库系统的管理维护转移到新数据库系统的维护. 可以大幅加快数据库管理维护相关脚本或者设置的部署时间，尤其在维护庞大的数据库系统时. 可以有效的实现团队协作，在大规模的数据库系统环境中通常要有一个DBA团队进行数据

4、库系统的管理维护，通过使用统一的标准，可以轻松实现协作工作. 可以有效的节省数据库系统排错时间，通过使用统一的数据库监控和恢复标准，可以迅速定位故障，并为处理错误节约时间，这在24*7的数据库系统中尤其有用。以下章节将介绍常用的数据库管理维护方案。3 数据库的物理环境SQLServer的物理环境是指SQLServer所处的安装目录以及网络环境，数据库系统时整个业务系统的核心应用，在安装设置初期就要考虑其所处的环境，以避免安全性和可维护性上的问题。3.1 网络环境对于SQLServer所处的网络环境，该文档建议使用以下基本原则： SQLServer服务器不使用公网IP地址，特殊情况除外，比如商务

5、合作需求 在局域网内，如果是低速的VPN环境，请慎重考虑SQLServer的高可用方案，原则上不建议使用镜像，复制等方案，但可以考虑ServiceBroker(异步)方案。 SQLServer的服务端口不要使用默认端口1433，新端口设置后务必通知所有使用该数据库的开发人员 配置Windows防火墙以开放SQLServer相应的服务端口3.2 目录设置对于SQLServer的安装目录设置，该文档建议使用以下基本原则： 用户数据库的数据目录要和日志目录存放在不同的磁盘驱动器上，尤其是业务比较繁忙的用户数据库 Tempdb数据库要单独存放在1个或2个磁盘驱动器上，尤其是业务比较繁忙的服务器实例 数

6、据库安装后要设置本地备份目录，原则上建议的目录结构如下数据目录实例名数据库名BackupFull数据目录实例名数据库名BackupDiff 数据目录实例名数据库名BackupLog保存备份的数据目录大小至少保证是数据库大小的10倍以上，或者至少保证能保留一周的备份文件3.3 文件设置在建立数据库时的数据文件设置，该文档建议使用以下基本原则 用户数据库数据文件的数目建议和服务器CPU数目一致或者是CPU数目的1/2等，不建议使用默认的一个数据文件注：CPU数目是指逻辑CPU，数据文件的大小(size)要保持一致日志文件要使用一个，所有类型的数据库日志文件都要保证是一个。 系统数据库对于系统数据库

7、，尤其是tempdb数据库，要增加其数据文件的数目，一般建议设置为CPU数目的1/2注：CPU数目是指逻辑CPU，数据文件的大小(size)要保持一致4 数据库账户管理数据库账户是访问数据库资源的一种主体，在SQLServer中，通常的数据库账户是指SQLServer登录名以及相应数据库中的数据库用户的映射。数据库账户的管理是整体数据库系统安全性管理的重要部分，在日常的开发和生产环境的数据库账户维护中，能够遵循一定的规范，对于保证数据库安全，进而保证整个应用系统安全有很重要的作用。该文档建议使用以下的原则对数据库账户进行管理维护： 账户最小权限原则所有申请的数据库账户，要明确需要的权限，确保不

8、需要的权限不要授予 密码复杂度所有账户要有合适的密码复杂度，尤其是生产环境，要保证足够的复杂度 账户有效期限记录所有账户的有效期限，在到期前通知用户是不是需要续期，尤其适用于产品策划人员等非数据库长期用户 账户申请注销原则所有数据库账户的申请要发邮件给数据库维护人员(DBA)，并抄送相关主管领导人员，建议同时也使用纸质文档留存。不再需要的数据库账户由数据库维护人员(DBA)发邮件给相应使用人员，通知账户注销。 多账户原则建议每一个或几个应用模块使用一个数据库账户，不要一个账户应用于所有应用模块。以下是建议使用的数据库账户申请邮件格式：1 客户端服务器IP(本机使用可不填)：2 项目名称：数据库

9、名称:3 帐户名称(可由DBA填写)：权限： 所有者 读写 只读 其他注:最终用户名为，项目名称_数据库名称_账户名称4 账户截止有效期:5 数据库备份和恢复数据库备份和恢复技术是数据库系统维护工作中的重要技术，不论是开发测试环境的数据库，还是生产环境的数据库，建议都要进行备份，而且要确保备份文件可用。对于数据库系统来说，当发生故障甚至是灾难性的故障的时候，数据库备份就是最有效的最后一道防线。对于数据库维护人员来说，备份与恢复技术的熟练运用，加之规范性的操作，是企业数据库系统正常运行的重要保障。创建 SQL Server 备份的目的是为了可以恢复已损坏的数据库。但是，备份和还原数据必须根据特定

10、环境进行自定义，并且必须使用可用资源。因此，可靠使用备份和还原以实现恢复需要有一个备份和还原策略。设计良好的备份和还原策略在考虑到特定业务要求的同时，可以尽量提高数据的可用性并尽量减少数据的丢失。设计有效的备份和还原策略需要仔细计划、实现和测试。测试是必需环节。直到成功还原了还原策略中所有组合内的备份后，才会生成备份策略。必须考虑各种因素。其中包括： 您的组织对数据库的生产目标，尤其是对可用性和防止数据丢失的要求。 每个数据库的特性，包括：大小、使用模式、内容特性以及数据要求等。对资源的约束，例如：硬件、人员、备份媒体的存储空间以及所存储媒体的物理安全性等。设计备份和还原计划时，应根据您自身的

11、特定环境和业务需求来考虑灾难恢复计划。例如，假设失火了并且烧毁了您的 24 小时数据中心。您是否有把握恢复数据？恢复系统并保证系统运行需要多长时间？您的用户能够承受丢失多少数据？理想的情况是，灾难恢复计划应规定恢复所需的时间以及用户可以期望的最终数据库状态。例如，可以确定在获取指定的硬件后，在 48 小时内完成恢复，并且保证最多能恢复到上周末时的数据。灾难恢复计划可以通过多种方式构建，并且可以包含各种类型的信息。灾难恢复计划类型包括：获取硬件计划。通信计划。发生灾难时的联系人名单。与负责处理灾难的人员的联系方式。对计划拥有管理权的人员的信息。每个恢复方案所需执行的任务的清单。为了便于您检查灾难

12、恢复的进度，将初始化已完成的任务，并在清单中指示任务完成时间。以下是建议使用的数据库备份，恢复计划5.1 开发测试环境对于开发和测试环境的数据库，一般不必采取过于严密的备份策略。该文档建议使用如下原则设置备份恢复策略 每天进行一次完全备份，备份保留3天 根据开发人员需求，可调整备份策略，比如单独备份存储过程，6小时备份一次等5.2 生产环境5.2.1 用户数据库生产环境的用户数据库是企业的重要数据资源，必须根据企业的要求采取严密的数据库备份恢复策略。该文档建议按照以下原则设计生产环境用户数据库的备份恢复策略 完全备份每天一次，如果数据库特别大，可考虑进行文件组备份，或者适当减少备份频率 差异备

13、份每4小时一次，如果数据库事务特别频繁，可考虑取消差异备份，用日志备份代替 日志备份每15分钟一次，可以根据实际数据库的事务量适当调整 以上备份的保留时间原则上建议本地至少保留一周，根据企业实际需求可以适当调整 根据某些数据库高可用架构的特点，可以适当调整以上备份策略，例如，当存在LogShipping架构使用的时候，就需要取消相应库的日志备份当整体数据库系统或者某局部数据库系统是完全的分布式架构的时候，不必在每个数据库服务器都设置备份计划，而仅需在架构中的关键位置设计即可5.2.2 系统数据库系统数据库为用户数据库功能的正常运行提供了必要的基础环境，因此系统数据库必须采取必要的备份，尤其是m

14、aster,msdb数据库该文档建议按照以下原则设计生产环境中系统数据库的备份策略 完全备份每天一次，备份至少保留一个月5.2.3 异地备份设置异地备份是设计整个灾难恢复计划的重要步骤，“异地”主要是按照物理服务器所处位置决定的，可分为同机房，同城，异城。该文档按照以下原则设计生产环境中异地备份策略 采用拷贝或者其他同步工具软件 采用数据库技术，日志传送或者ServiceBroker5.2.4 恢复计划对于每一个备份计划都要知道相应的恢复计划，对于整体数据库系统，也有必要制定灾难性恢复计划。6 数据库监控监视数据库的目的是评估服务器的性能。有效监视包括定期拍摄当前性能的快照来隔离导致问题的进程

15、，以及连续收集数据来跟踪性能趋势。Microsoft SQL Server 和 Microsoft Windows 操作系统提供实用工具，使您可以查看数据库的当前状态并跟踪性能的状态变化。监视 SQL Server 使您可以执行下列操作：确定是否可以提高性能。例如，通过监视常用查询的响应时间，可以确定是否需要更改表的查询或索引。评估用户活动。例如，通过监视尝试连接到 SQL Server 实例的用户，可以确定安全设置是否充分以及是否需要测试应用程序或开发系统。例如，通过在执行 SQL 查询时对其进行监视，可以确定这些查询是否编写正确并生成预期的结果。解决任何问题或调试应用程序组件（如存储过程）

16、。监视操作非常重要，因为 SQL Server 在动态环境中提供服务。应用程序中的数据在变化。用户需要的访问类型在变化。用户连接的方式在变化。甚至，访问 SQL Server 的应用程序的类型也可能在变化，而 SQL Server 自动管理系统级资源（如内存和磁盘空间），因此对广泛系统级手动优化的需要已降至最低。但是，管理员可以通过监视来标识性能趋势来确定是否有必要进行更改。若要有效监视 SQL Server 的任何组件，请按下列步骤进行操作：1.确定监视目标。2.选择相应工具。3.标识要监视的组件。4.选择那些组件的度量。5.监视服务器。6.分析数据该文档建议使用如下方式监控数据库引擎的活动

17、： 数据库作业使用Job定期执行存储过程，存储过程调用DMV利用这种方式，可以查询数据库引擎的很多活动，比如索引使用，会话活动，事务活动等 跟踪利用Trace可以跟踪大部分的数据库引擎活动，包括登录活动，语句执行，错误等 DDL触发器利用DDL触发器可以监控数据库对象或者服务器对象的变化。 事件通知事件通知(EVENT NOTIFICATION) 可对各种 Transact-SQL 数据定义语言 (DDL) 语句和 SQL 跟踪事件做出响应，并将这些事件的相关信息发送到 Service Broker 服务.事件通知可以用来执行以下操作：记录和检索发生在数据库上的更改或活动。执行操作以异步方式而

18、不是同步方式响应事件。可以将事件通知用作替代 DDL 触发器和 SQL 跟踪的编程方法。事件通知在事务范围以外异步运行。因此，与 DDL 触发器不同，事件通知可以用于数据库应用程序中以响应事件而无需使用中间事务定义的任何资源。建议对于数据库内的DDL事件可以采用此种方式监控。 报警根据监控的结果发报警信息，可以使用第三方通讯工具(发邮件或短信)。 监控结果的展现默认情况下监控结果要保持在数据库表中或者生产文件，根据实际情况可以借用一些开源框架展现监控结果，比如WebChart等 7 数据库维护数据库维护是根据数据库系统承载的业务量的不断增加，以及数据库容量的不断增加，而不断进行性能分析，性能调

19、整，架构分析，架构调整的过程。数据库维护一定是长期的过程，需要维护人员不断的分析调整，不断的学习研究新技术，不断的优化。7.1 性能优化性能优化是一个范围很广的题目，在此不叙述相关的技术，只介绍一些优化原则。 根据监控结果，至少一周进行一次性能优化(调整索引，SQL语句优化)7.2 恢复或者切换演练生产环境部署测试完毕后，要根据系统实际运行情况制定恢复和切换的演练策略，比如2周进行一次附录1 数据库备份，验证备份脚本CREATE PROC dbo.BackupDB ( databasename varchar(100) = null, bcktype varchar(100) = null,

20、bckPos varchar(8000)= null ) WITH ENCRYPTION AS SET NOCOUNT ONIF databasename is null BEGIN RAISERROR(N缺少databasename 参数,该参数不能为空, 16, 1) RETURN -1END IF bcktype is null BEGIN RAISERROR(N缺少bcktype 参数,该参数不能为空(full,diff,log), 16, 1) RETURN -2END IF bckPos is null BEGIN RAISERROR(N缺少bckPos参数,不能为空备份目录, 1

21、6, 1) RETURN -3END IF (SELECT mirroring_role_desc FROM sys.database_mirroring WHERE database_id=db_id(databasename)=MIRRORBEGIN RAISERROR(N镜像数据库不允许备份, 16, 1) RETURN -4END IF databasename=tempdbBEGIN RAISERROR(NTempdb不允许备份, 16, 1) RETURN -5END DECLARE date varchar(100),sql varchar(1000),sql1 varchar(

22、2000) SELECT date = CONVERT(varchar(20),GETDATE(),112)+REPLACE(CONVERT(varchar(5),GETDATE(),108),:,), sql = ,sql1= IF (bcktype = full) SELECT sql = BACKUP DATABASE +databasename+ TO DISK = N+bckPos+databasename+_Full_+date+.bak,sql1=RESTORE VERIFYONLY FROM DISK = N+bckPos+databasename+_Full_+date+.b

23、ak IF (bcktype = diff) SELECT sql = BACKUP DATABASE +databasename+ TO DISK = N+bckPos+databasename+_Diff_+date+.bak WITH DIFFERENTIAL,sql1=RESTORE VERIFYONLY FROM DISK = N+bckPos+databasename+_Diff_+date+.bak IF (bcktype = log) SELECT sql = BACKUP LOG +databasename+ TO DISK = N+bckPos+databasename+_

24、Log_+date+.trn,sql1=RESTORE VERIFYONLY FROM DISK = N+bckPos+databasename+_Log_+date+.trn IF sql AND sql1 BEGIN EXEC(sql) EXEC(sql1) END ELSE RAISERROR(N请检查输入参数，备份目录., 16, 1) SET NOCOUNT OFF 附录2 数据库恢复脚本CREATE PROC dbo.RestoreDB ( databasename varchar(100) = null, bcktype varchar(100) = null, bckpath

25、varchar(8000)= null, islastlog bit=0 ) WITH ENCRYPTION AS SET NOCOUNT ONIF databasename is null BEGIN RAISERROR(N缺少databasename 参数,该参数不能为空, 16, 1) RETURN -1END IF bcktype is null BEGIN RAISERROR(N缺少bcktype 参数,该参数不能为空(full,diff,log), 16, 1) RETURN -2END IF bckpath is null BEGIN RAISERROR(N缺少bckpath参数

26、,请提供备份文件全路径, 16, 1) RETURN -3END DECLARE sql varchar(1000) IF islastlog=0BEGIN IF bcktype IN(full,diff) SELECT sql = RESTORE DATABASE +databasename+ FROM DISK = N+bckpath+ WITH NORECOVERY,STATS = 10 IF bcktype IN(log) SELECT sql = RESTORE LOG +databasename+ FROM DISK = N+bckpath+ WITH NORECOVERY,STA

27、TS = 10 ENDELSESELECT sql = RESTORE LOG +databasename+ FROM DISK = N+bckpath+ WITH RECOVERY,STATS = 10 IF sql BEGIN EXEC(sql) END ELSE RAISERROR(N请检查输入参数., 16, 1) SET NOCOUNT OFF附录3 清除备份文件脚本CREATE PROC dbo.DelBackup ( bckPos varchar(8000)= null, bcktype varchar(100) = null, Hours int=1 ) WITH ENCRYP

28、TION AS SET NOCOUNT ON IF bcktype is null BEGIN RAISERROR(N缺少bcktype 参数,该参数不能为空(bak,trn), 16, 1) RETURN -1END IF bckPos is null BEGIN RAISERROR(N缺少bckPos参数,不能为空备份目录, 16, 1) RETURN -2END declare DeleteDate nvarchar(50),DateTime datetime,sql varchar(1000)set DateTime = DateAdd(Hour, -Hours, GetDate()

29、set DeleteDate = (Select CONVERT(varchar(10),DateTime,120) + T + Convert(nvarchar,DateTime,108)SET sql=EXECUTE master.dbo.xp_delete_file 0,N+bckPos+,N+bcktype+,+DeleteDate+,1 IF sql BEGIN EXEC(sql) END ELSE RAISERROR(N请检查输入参数., 16, 1) SET NOCOUNT OFF附录4 事件通知例子CREATE QUEUE /www.AAA.com/DBName/TabDDLQueueGO CREATE SERVICE /www.AAA.com/DBName/TabDDLServiceON QUEUE /www.AAA.com/DBName/TabDDLQueue(http:/schemas.mi