SQL Server2005管理技巧培训技巧

1、Microsoft SQL Server 2005管理技巧培训管理技巧培训姓名：宋立桓背景：连续两届微软最有价值专家微软资深特约讲师微软产品企业护航专家联系方式：Email: l SQL Server2005框架结构框架结构l 如何正确升级如何正确升级SQL Server 2005 l 如何构建高可用的数据平台如何构建高可用的数据平台l 数据库备份恢复案例分析数据库备份恢复案例分析l SQL Server 2005 性能调优性能调优SQL Server 2005Relational Database Engine关系数据库引擎关系数据库引擎.NET CLRAnalysis Services分析

2、服务分析服务Native HTTP SupportService BrokerReplicationReporting Services报表服务报表服务Full-Text SearchNotification ServicesSQL Server Integration ServicesIS服务服务-集成服务集成服务 表分区 动态 AWE 内存管理 CLR集成 T-SQL语言功能的增强 联机索引操作 专用管理员连接（DAC） 数据库镜像 复制的增强 校验和 I/O 验证和读取重试 安全新特性, 內置加密接口 在线还原, 快速恢复 DMV, 数据库引擎顾问如何升级到如何升级到SQL Server

3、 2005SQL2005升级的理由升级的理由升级概述和方法升级概述和方法升级关系型数据库升级关系型数据库升级安全升级安全升级升级T-SQL升级升级DTS 组件, 并不是所有的组件都可以直接升级 对应用程序的影响 可用性 回退An in-place upgradeSQL Server2005可以自动的、直接的将sql7.0/2000的实例升级到sql2005,原有的不复存在(升级失败，不能回退)。A side-by-side upgrade(并行升级)Sql server2000/7.0实例和sql2005的实例并存在一台服务器上或两台服务器上，手动进行升级。（位需要） 组件使用 Upgrade

4、 Advisor（升级顾问）来分析要升级的 SQL Server 现有实例。 Microsoft SQL Server 2005 升级顾问是用于为升级到 SQL Server 2005 作准备的工具。升级顾问对已安装的 SQL Server 2000 或 SQL Server 7.0 组件进行分析，然后生成一个报表，报表中标识出升级到 SQL Server 2005 之前或之后必须或应解决的问题The sql server2005 setup prerequisites are installedSetup开始检查,如果有问题,则发出警告退出开始安装sql server2005可执行文件和支持

5、文件Setup停止原有的sql server serviceSql Server2005开始升级停止原有的实例文件, 新的sql server2005 instance可被使用在同一台或另外一台服务器上安装单独的SQL Server2005实例,原有的实例保持可用运行sql server2005 upgrade advisor来检查升级问题停止原有实例上的改写活动Transfer database objects from sql2000 instance to sql2005 instance验证新实例修改应用程序重新定向到新的实例 In-place Side-by-side Backup

6、and restore Detach and attach Copy database wizard Manual schema rebuild and data export/import 注意:已经升级到sql2005不能back到sql2000,你必须用export/import方法 Run upgrade advisor Run DBCC CheckDB 升级前auto_update_statistics设为on 确保数据库数据文件和日志文件自动增长,并有足够空间Execute DBCC UPDATEUSAGEExecute DBCC CHECKDB with data_purity配

7、置sql server实例,提高安全性, 使用外围配置器执行样本查询,确保关系数据库正常使用如果是side-by-side,还要修改应用程序数据库连接,配置job, alert更新统计信息 - 建议您在升级之后对所有数据库更新统计信息，以便优化查询性能。使用 sp_updatestats 存储过程更新 SQL Server 2005 数据库用户定义表中的统计信息。更新使用计数器 - 在 SQL Server 的早期版本中，表和索引的行计数与页计数的值可能会不正确。若要更正任何无效的行或页计数，建议在升级完成后对所有数据库运行 DBCC UPDATEUSAGE。 不管是SP还是trigger,

8、都不能自动升级 像2000的版本里原来应该写成 数据库名.dbo.数据表名，但是简写为数据库名.数据表名 的时候转移到sql 2005 会出错。 Upgrade advisor可以帮我们发现需要手工改写的部分 Package migration wizard 并非所有的sql2000 DTS都能被移植到sql2005 可能需要重新设计DTS包SQL Server 2000 分析服务群集迁移到分析服务群集迁移到 SQL Server Analysis Services 2005 群集群集 http:/ SQL Server 2005 故障转移群集（安装程序）故障转移群集（安装程序） http:/

9、 Server 2005 Upgrade Handbook http:/ SQL Server 2005 Upgrade Advisor http:/ 2005 book online可用性可以定义为系统或资源可以使用的时间可用性可以用数学表达式定义为： 可用性百分比可用性百分比= (总时间总时间 停机时间的总和停机时间的总和)/总时间总时间) 1. 热插拔内存热插拔内存/磁盘磁盘2. 群集服务支持群集服务支持 轮换升级轮换升级动态管理配置动态管理配置热添加内存热添加内存动态动态 AWE 内存管理内存管理2. 联机索引操作联机索引操作热插拔内存热插拔内存/磁盘磁盘 Microsoft Wind

10、ows Server2003 支持热切换 RAM 和 RAID 驱动器，可满足最常见的硬件升级场景：为系统增加内存和磁盘容量的需要。群集服务支持群集服务支持 轮换升级轮换升级 为了满足例行维护和所需的停机时间的需要，目前所有可用的企业数据库平台都支持多服务器集群和其他可用性功能，以使 IT 人员能够进行轮换升级。Microsoft Windows Clustering Services 支持进行轮换升级数据库维护是高效且高可用的数据不可或缺的数据库维护是高效且高可用的数据不可或缺的部分部分 动态配置动态配置 为了便于联机服务器和数据库维护，SQL Server 2005 允许对大部分 SQL

11、Server 系统属性进行动态配置。 动态 AWE 内存管理 热添加内存 联机索引操作联机索引操作 SQL Server2005 独有索引碎片整理语句，允许对表和视图上的聚集索引和非聚集索引进行联机碎片整理。 数据库镜像数据库镜像故障转移群集故障转移群集数据库端对端复制数据库端对端复制在线还原在线还原快速恢复快速恢复日志传送与时延日志传送与时延数据库快照数据库快照 数据库在进行部分还原时仍处于在线可用状态 为了使数据库处于在线状态，其主文件组必须处于在线状态，但其部分或所有辅助文件组（如果存在）可以处于离线状态。 只有 SQL Server 2005 Enterprise Edition 中允

12、许在线还原。 只有当前正在还原的数据不可用；仍然可以访问包含在其他文件组中的其他数据库数据。SQL Server2005 的快速恢复功能允许用户在事务日志前滚后立即重新连接到正在恢复的数据库，从而提供了数据库可用性 数据库快速恢复（数据库快速恢复（Fast Recovery） SQL Server 2000：数据库在“Undo”完成后才可用 在 SQL Server 2005 数据库引擎 中，可以使用日志传送将事务日志不间断地从一个数据库（主数据库）发送到另一个数据库（辅助数据库）。不间断地备份主数据库中的事务日志，然后将它们复制并还原到辅助数据库，这将使辅助数据库与主数据库基本保持同步。 可

13、以将日志传送配置为允许“时间窗”，以方便地从出现数据破坏的情况恢复。 现在，模式方面的更改会自动复制到订阅者处 Peer to Peer复制 支持Oracle发布到SQL Server 复制监视器 容易安装、配置和维护 支持多达 8 个节点的集群 支持分析服务、通知服务和复制 支持64位群集 镜像主服务器见证服务器Log应用程序SQL ServerSQL Server22451DataDataLog323镜像始终保持“Redoing”状态，确保数据同步Commit发生故障时，零数据丢失接近实时的故障转移，只需要几秒钟对于应用程序透明，现有应用程序无需特殊改动数据库镜像提供对磁盘故障的额外保护数

14、据库镜像没有距离限制，可以实现异地容灾数据库镜像可以与支持 SQL Server 的所有标准硬件一同工作，无需特定硬件配置SQL Server2005标准版支持数据库镜像 高级别保护模式 高可用性模式 高性能模式 SQL Server 2005 默认设置数据库镜像功能是被禁用，通过使用跟踪标志 1400 作为启动参数可以启用该功能。目前已经发布了SQL Server2005 SP1。在SQL Server 2005 SP 1 中已启用数据库镜像。前提: 做为数据库镜像的服务器加入到域中 准备镜像数据库 安全配置向导主体数据库的恢复模型必须为FULL执行主体数据库的完整备份和一个后续日志备份，并

15、使用 WITH NORECOVERY 将这两个备份还原到镜像服务器实例上。为使镜像正常运行，镜像数据库必须处于 RESTORING 状态。 - 1.在主体服务器上，对AdventureWorks数据库做完全备份 BACKUP DATABASE AdventureWorks TO DISK = C:Tools AdventureWorks.bak- 2.在镜像服务器上，恢复AdventureWorks数据库的备份，并使用NORECOVERY选项 RESTORE DATABASE AdventureWorks FROM DISK = C:Tools AdventureWorks.bak WITH

16、NORECOVERY Management studio 数据库属性镜像状态 系统视图系统视图 sys.database_mirroring_endpoints sys.database_mirroring sys.database.mirroring.witness Sql2005中提供了数据库镜像专用性能对中提供了数据库镜像专用性能对象象 常用性能计数器常用性能计数器 主体服务器主体服务器 Log Bytes Sent/sec Log Send Queue Transaction Delay 镜像服务器镜像服务器 Log Bytes Received/sec Redo Queue KB S

17、QL 2005 SP1提供的数据库镜像监视工具 Database Mirroring 可以与 SQL Server 2005 数据库快照组合，用于创建使用镜像服务器数据的报告服务器。 数据库某个时间点的快照数据库某个时间点的快照 即时创建的即时创建的, 只读性的只读性的基础数据库继续变化基础数据库继续变化 快照不影响、限制对基础数据库的更新快照不影响、限制对基础数据库的更新返回到以前创建的快照可挽救误操作返回到以前创建的快照可挽救误操作极其有效的空间管理极其有效的空间管理采用采用 “copy on write” 机制机制 无需复制数据的完整备份无需复制数据的完整备份 共享无变化的数据库页面共享

18、无变化的数据库页面 仅存储已变化的数据页仅存储已变化的数据页ValueRDBHJLY命令Create Northwind_SNAPNorthwindNorthwind_SNAPUpdate NorthwindValueDXRead Northwind_SNAP结果:DD 技术障碍技术障碍 服务器单元的任何组件都可能发性硬件故障。应用程序错误也会影响数据库访问。需要准备好恰当的数据库恢复机制来还原信息，否则数据将会被破坏。有计划的硬件升级和数据库维护也是可能降低系统可用性的因素。利用可用数据库技术可以减小或消除与计划维护相关的停机时间。最后，站点灾难对数据库可用性也有重大的影响 人员障碍人员障碍

19、 任何环境下引起停机的最大原因之一是人为错误 流程障碍流程障碍 制定合适的流程有助于消除不必要的停机时间，在服务中断时可以快速恢复 高可用性并非垂手可得，只有通过增强人员、流程和技术的组合才能实现。 对高可用性的需要由业务需求驱动，而非源于某项特定技术的存在。创建高可用性环境通常很有吸引力，但要牢记：所需的可用性水平越高，相关成本也就越高。 Microsoft SQL Server2005 能以较低的成本为您的组织提供企业级高可用性功能，而复杂要低。SQL Server 2005SQL Server 2005备份和还备份和还原原 备份是在数据库发生灾难时的最后一道防线 “备份、备份、再备份”

20、要有个备份策略 数据库的恢复模型控制备份和还原的行为 三种模型 完全 大容量日志记录 简单恢复 切换恢复模型 ALTER DATABASE Northwind SET RECOVERY Full 数据库恢复模型的选择完整恢复模型除了能还原到故障点和支持即时点还原，还支持文件文件组还原和页面还原。 完整数据库备份 差异数据库备份 备份和最近一次完全备份有不同的数据 新特性SQL2005中所有数据库完整备份和差异备份均包含日志记录 针对大型数据库，可以备份和还原数据库中的文件 仅还原已损坏的文件，而不必还原数据库的其余部分，但主文件组必须在线（在线还原） 增加了备份和还原管理的复杂性通过事务日志备

21、份可以将数据库恢复到即时点必须至少有一个完整数据库备份基础尾日志备份 在恢复数据库前保存尚未备份的日志数据选项No_truncate拷贝日志但不截断日志，在出现介质错误时使用该选项SQL Server2005要求先备份日志尾部，然后在还原当前的数据库 数据库还原阶段的过程 复制阶段 重做阶段 撤消阶段 数据库还原选项 Recovery和norecovery选项 还原前准备工作 限制数据库访问 备份事务日志通过执行页面还原对数据库中损坏的页进行修复获取页面损坏信息Restore database adventureworksPage=Adventureworks_data_1:832From A

22、Wbackup数据库在进行部分还原时仍处于在线可用状态Sql server2005企业版支持可以逐步恢复由多个文件组组成的数据库主文件组必须先还原只有正在还原的部分不可用 某公司每周的周一做一次数据库全备份，每天做一次差异备份，到周四下午四点，数据库文件所在的磁盘损坏，导致数据库置疑 某公司每周的周一做一次数据库全备份，每天做一次日志备份，在周四下午四点，管理员误操作，删除了几百行数据，现在想恢复到下午三点半，如何操作？ 数据库采用完全恢复模式，数据库日志非常大，希望减少日志文件大小l 仅复制备份u 进行备份通常会更改数据库，而这又会影响其他备份以及还原这些备份的方式。例如，常规数据备份可以用

23、作一个或多个后续差异备份的差异基准。但是，有时必须针对特殊目的执行备份，并且不应该影响数据库的整体备份和还原过程。为实现此目的，SQL Server 2005 引入了仅复制备份。仅复制备份与正常的备份序列无关。 u 若要创建或还原仅复制备份，必须使用 BACKUP 和 RESTORE Transact-SQL 语句。使用 COPY_ONLY 选项。通过 COPY_ONLY 选项执行的数据备份不能用作差异基准，因此不会影响任何现有的差异备份。l 支持数据库备份的即时点还原l 在sql server2000中只支持事务日志的及时点还原l 使用STOPAT选项指明时间点RESTORE DATABAS

24、E FROM Northwind from NWbackupWITH RECOVERY, STOPAT = Dec 10, 2006 10:00 AM l 尽管验证备份不是必需的，但却很有用。验证备份可以检查备份在物理上是否完好无损，以确保备份中的所有文件都是可读、可还原的，并且在您需要使用它时可以还原备份。l SQLERVER 2000的备份程序不验证数据库文件的数据完整性；如果你的数据文件有问题，备份时也不提示l SQL Server2000中的RESTORE VERIFYONLY 不尝试验证备份卷中的数据结构。l PAGE_VERIFY 当指定为 CHECKSUM 时，大大增强数据库的可

25、靠性，在SQL Server2000时是torn_page_detectionl 在 SQL Server 2005 中，RESTORE VERIFYONLY 功能得到了增强。如果备份是使用 WITH CHECKSUMS 创建的，则使用 WITH CHECKSUMS 验证备份可以很好地表明备份中数据的可靠性。l 镜像备份媒体通过提供冗余来提高备份的可靠性。BACKUP DATABASE Pubs TO Disk = c:pubsbakpubsbak-A.bak MIRROR TO Disk = E:pubsbakpubsbak-B.bak MIRROR TO Disk = G:pubsbakp

26、ubsbak-C.bak 误区1:没有定期备份 (不同于双机热备份、磁盘阵列与磁盘镜像不同于双机热备份、磁盘阵列与磁盘镜像)误区2:仅仅备份到同一服务器的磁盘里(?磁带?异地)误区3:没有定期检验备份误区4:只作完全备份,没有日志和差异备份策略误区5:只作人工备份(?Job自动化)误区6:仅仅备物理文件而不使用backup语句或企业管理器误区7:仅仅备份用户数据库而没有备份系统数据库误区8:没有人负责备份的制定,执行和维护 SQL Server2005 备份还原功能增强备份还原功能增强尽可能地提高吞吐量尽可能地减少响应时间 通过将网络流量、磁盘 I/O 和 CPU 时间减到最小，使每个查询的响

27、应时间最短并最大限度地提高整个数据库服务器的吞吐量。 系统硬件资源 操作系统 数据库服务器设置 数据库设计(包括物理设计和逻辑设计) 应用程序设计（查询与索引优化、使用存储过程等等）Performance MonitorSQL Trace SQL Profiler对性能的影响可能比较大对性能的影响可能比较大数据库引擎优化向导数据库引擎优化向导SQLdiagsp_who2&sp_lockDMV & DMF (动态管理视图和函数动态管理视图和函数)逻辑设计 （三级范式，数据库的完整性约束，OLAP数据库的反范式设计）物理设计 (数据分区，数据文件和日志文件的位置规划。) 硬件分区硬

28、件分区 多处理器: 允许多线程执行的多处理器，使得可以同时执行许多查询。RAID：允许数据在多个磁盘驱动器中条带化。 水平分区水平分区 垂直分区垂直分区 分区视图分区视图 处理器 内存 磁盘通过性能监视器, 可以帮助我们判断。通常，数据库服务器应达到以下配置： 1. 采用双路或者更多的处理器，以提高服务器的处理能力以及对数据库的并行操作，达到增加吞吐量的目的。 2. 对内存要求非常大，大容量的内存可以节省处理器访问硬盘的时间，提高数据库服务器的性能。 3. 需要大容量的存储空间，所以数据库服务器一般都采用SCSI硬盘作为存储介质，在数据存储方面通常都要实现RAID，当然，一些大中型企业还会建立

29、存储系统来满足需求。I/O 瓶颈通常比较容易发现瓶颈通常比较容易发现通过性能监视器，如果平均磁盘等待队列长度多于磁盘通过性能监视器，如果平均磁盘等待队列长度多于磁盘头数量的两倍，则磁盘成为瓶颈。头数量的两倍，则磁盘成为瓶颈。主要的解决方法是主要的解决方法是将日志、数据库文件分别放在不同的磁盘上。将日志、数据库文件分别放在不同的磁盘上。购买运行速度更快的磁盘和使用购买运行速度更快的磁盘和使用 RAIDtempdb数据库放置在一个单独的快速的硬盘数据库放置在一个单独的快速的硬盘对日志文件一定要小心对日志文件一定要小心使用独立的设备使用独立的设备使用使用RAID 1最近的测试结果是最近的测试结果是R

30、AID5的写性能比的写性能比RAID 0+1要差要差50%Tempdb 在 SS2005里使用量更大Tempdb 的配置更加重要建议(1)当系统自动调整tempdb大小时，对文件的读写将暂时的阻塞。所以事先就设置好一个合适的大小，tempdb自动增长会严重影响性能(2)放到一个独立的快速读写设备(3)增加 tempdb 数据文件具有相同尺寸的数量 (4)在tempdb有争抢分配页的问题，参见http:/ SELECT 权限以及 VIEW SERVER STATE 或 VIEW DATABASE STATE 权限。所有动态管理视图和函数都存在于 sys 架构中，并遵循 dm_* 命名约定。具体参

31、考: http:/ sum(user_object_reserved_page_count)*8 as user_objects_kb,sum(internal_object_reserved_page_count)*8 as internal_objects_kb,sum(version_store_reserved_page_count)*8 as version_store_kb,sum(unallocated_extent_page_count)*8 as freespace_kbfrom sys.dm_db_file_space_usagewhere database_id = 21

32、、用sp_helpdb查看tempdb现在存放的位置 2、用 T-SQLalter database tempdb modify file(name=tempdev,filename=e:temptempdb.mdf,size=500mb) alter database tempdb modify file(name=templog,filename=e:temptemplog.ldf,size=20mb) 3、关闭sqlserver重起 Net stop mssql$Net start mssql$ 4、可以删掉旧的tempdb文件 MemoryAvailable Bytes运行中的程序可用

33、的物理内存的大小，如果经常少于4M，需要增加内存。MemoryPage/sec程序需要的数据从硬盘的页面文件读取的数量。如果超过每秒20个页面以上，需要增加内存。commited bytes in uses内存使用量。如果经常超过70%，需要增加物理内存，不经常超过70%的话，增加内存对性能提升效果不明显。 使用性能监视器所要监视的对象和计数器使用性能监视器所要监视的对象和计数器 使用SQL Server的公司想更大地提高性能，那你就要改用64位架构？ 64位平台与32位系统相比，大大提高了内存访问能力。32位系统最多只能本地访问4GB的内存。如今的64位系统最多可本地访问512GB的内存。这

34、种增加内存的功能在许多情况下直接提高了性能。由于更多的数据保存在缓存里面，势必会减少磁盘的I/O操作。服务器内存设置由 SQL Server 根据工作负荷和可用资源自动配置，其大小在 min server memory 和 max server memory 之间动态变化。把 min server memory 和 max server memory 数量设置到一个范围段内。这种方法在系统或数据库管理员希望配置 SQL Server 实例，使其适应在同一台计算机上运行的其它应用程序的内存需求时很有用。 min server memory 保证了 SQL Server 实例使用的最小内存量。max server memory 则可防止 SQL Server 使用多于指定数量的内存，这样剩余的可用内存可以快速运行其它应用程序。不要把有抢夺资源的程序和服务与sql server在一起。1.打开系统中的大内存支持(windows)要启用 Windows 2000 Advanced Server 或 Windows 2000 Datacenter Server 支持大