高级工程师hc统一存储与db应用

1、修订记录课程编码适用产品产品版本课程版本ISSUEHC1309118统一存储V1V1.0开发/优化者时间审核人开发类型（新开发/优化）张海艳2014-03-12张博新开发本页不打印HC1309118 统一存储与DB应用2SQL Server学习完本章节后，您将能够：理解SQL Server数据库原理掌握SQL Server数据库的组件和安装掌握SQL Server数据库基本操作掌握SQL Server关键参数和最佳实践目标目录SQL Server数据库原理SQL Server数据库的组件和安装SQL Server数据库基本操作SQL Server数据库关键参数和最佳实践SQL Server概述

2、SQL Server是由Microsoft开发和推广的关系数据库管理系统（DBMS），它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，并于1988年推出了第一个OS/2版本。SQL Server数据库的特点：真正的客户机/服务器体系结构。图形化用户界面，使系统管理和数据库管理更加直观、简单。提供数据仓库功能，只有Oracle及更昂贵的DBMS才有。SQL Server发展史SQL Server数据库版本发展历史：1996年，Microsoft推出了SQL Server 6.5版本；1998年，SQL Server 7.0版本和用户见面；SQL Server

3、 2000是Microsoft公司于2000年推出；SQL Server 2005于2005年推出；SQL Server 2008于2008年推出；目前最新版本是2012年3月份推出的SQL SERVER 2012。说明：本课件的截图以常用的SQL Server 2005为例。数据库引擎的架构数据库引擎(即数据库实例)由系统数据库和用户数据库组成。系统数据库master数据库msdb数据库model数据库Resource数据库tempdb数据库用户数据库系统数据库(1/2)master数据库参数配置，登陆帐户等系统级信息以及其他数据库的信息。msdb数据库存储计划信息。存储备份及恢复相关的信息

4、。model数据库所有数据库的模板数据库。修改model数据库，新创建的数据库会继承这种修改。系统数据库(2/2)resource数据库Resource数据库是只读数据库，它包含了SQL Server 2005中的所有系统对象。Resource数据库是隐藏的，通常应该由Microsoft客户服务专家来打开，用于查找问题和进行客户支持。 tempdb数据库tempdb 保存所有临时表和临时存储过程。每次启动SQL Server时，都要重新创建tempdb。 数据库文件每个SQL Server 数据库(包括系统数据库和用户数据库)至少具有两个操作系统文件：一个数据文件和一个日志文件。 数据文件主数

5、据文件是存放数据和对象的文件，扩展名为.mdf，一个数据库有且仅一个。次数据文件是仅存放数据的文件，扩展名为.ndf，一个数据库有0或多个。 日志文件保存用于恢复数据库的日志信息，扩展名为.ldf。每个数据库必须至少有一个日志文件，也可以为多个。文件组每个数据库至少包含一个PRIMARY文件组，包含了.mdf主数据文件。可创建文件组包含一个或多个数据文件。日志文件不属于任何文件组。数据写入文件的流程(1/2)数据写入文件的流程(2/2)Redo对事务进行前滚。在系统掉电或数据库崩溃后，利用redo保证数据的准确性。 Undo对事务进行回滚。在系统掉电或数据库崩溃，数据库再次启动时会利用undo

6、对未提交的事务进行回滚。 Checkpoint将数据页面从内存拷贝至磁盘的内部处理点，保证checkpoint之前已提交事务的数据写入磁盘。目录SQL Server数据库原理SQL Server数据库的组件和安装SQL Server数据库基本操作SQL Server数据库关键参数和最佳实践SQL Server数据库组件SQL Server数据库组件：数据库引擎：包括数据库引擎、复制、全文搜索以及管理数据的工具。分析服务(Analysis Services)：用于创建和管理OLAP及数据挖掘的工具。集成服务(Intergaration Servieces)：一组图形工具，用于移动、复制、转换数据

7、。报表服务(Reporting Services)：用于创建、管理、部署报表。SQL Server数据库的安装准备安装SQL Server数据库安装的硬件要求操作系统支持及软件要求基本安装选项安装注意事项安装过程安装后的服务检查安装硬件要求、操作系统等要求计划安装SQL Server 数据库时，不仅要选择正确的SQL Server 数据库版本，而且还要确保安装数据库的计算机满足SQL Server 的硬件的最小需求，并能够适应当前和未来数据库的发展需求。下面介绍安装SQL Server数据库时要求的硬件环境。处理器要求内存要求磁盘空间要求附加硬件要求SQL Server 数据库安装前，要求对操

8、作系统及相关软件进行检测，只有满足其最低的版本要求后，才能进行安装。否则，可能造成组件安装不全，或者系统安装失败。对操作系统的要求对环境的要求基本安装选项在实际安装过程中，可以使用安装向导的“功能选择”页，来选择SQL Server 2005安装中要包括的组件。默认情况下不选择树中的任何功能，如下图所示。安装注意事项在准备安装SQL Server 数据库前，用户还需要注意以下事项：要使用具有管理员权限的账户来安装SQL Server。要安装SQL Server的硬盘分区必须是未经压缩的硬盘分区。安装时建议不要运行任何杀毒软件。SQL Server实例SQL Server实例(也称为数据库引擎)

9、，每个SQL Server实例各有一套不为其它实例共享的系统数据库及用户数据库。实例的身份验证模式在安装过程中必须为数据库实例选择身份验证模式，有“Windows身份验证模式”及“混合身份验证模式”两种。SQL Server安装后的服务(1/2)SQL Server 安装后，系统服务中将创建有关SQL Server 的众多服务。有两种方式对服务进行管理，包括启动、关闭、查看或修改服务的属性。控制面板管理工具服务。SQL Server安装后的服务(2/2)微软提供的SQL Server Configuration Manager： 开始所有程序Microsoft SQL Server配置工具SQ

10、L Server配置管理器。目录SQL Server数据库原理SQL Server数据库的组件和安装SQL Server数据库基本操作SQL Server数据库关键参数和最佳实践管理数据库创建数据库查看数据库属性修改数据库收缩数据库删除数据库创建数据库(SSMS图形化方式)使用SSMS(SQL Server Management Studio)创建数据库：在对象资源管理器中”数据库”上右击 , 在弹出菜单中单击”新建数据库”，则出现“新建数据库”对话框。创建数据库(SQL命令)创建数据库语句CREATE DATABASE database_name ON PRIMARY ,.n , ,.n L

11、OG ON ,.n COLLATE collation_name WITH 示例：create database studenton primary (name=student_data,filename=E:SQL Serverstudent_data.mdf, size=3, maxsize=unlimited, filegrowth=1) log on (name=student_log, filename=E:SQL Serverstudent_log.ldf, size=1, maxsize=20, filegrowth=10%)查看数据库属性选定数据库右击，选择”属性”，弹出”数据

12、库属性”窗口。使用视图等查看数据库属性视图函数存储过程和其他语句sys.databasesDATABASE_PRINCIPAL_IDsp_databasessys.database_filesDATABASEPROPERTYEXsp_helpdbsys.data_spacesDB_IDsp_helpfilesys.filegroupsDB_NAMEsp_helpfilegroupsys.allocation_unitsFILE_IDsp_spaceusedsys.master_filesFILE_IDEXDBCC SQLPERFsys.partitionsFILE_NAMEsys.partit

13、ion_functionsFILEGROUP_IDsys.partition_parametersFILEGROUP_NAMEsys.partition_range_valuesFILEGROUPPROPERTYsys.partition_schemesFILEPROPERTYsys.dm_db_partition_statsfn_virtualfilestats修改数据库在SSMS利用数据库属性设置更改数据库各项参数。使用SQL 语句修改数据库。ALTER DATABASE database_name | | | MODIFY NAME = new_database_name | COLLA

14、TE collation_name 示例：alter database mytest modify name= mytest1将数据库名mytest更改为mytest1。alter database test1 remove file test1_data2 删除test1数据库的数据库文件。alter database test1 modify file (name=test1_data2, filename=e:test2.ndf) 更改文件位置。收缩数据库需要收缩数据库的空闲空间时，可通过数据库选项将“自动收缩”改为true。删除数据库在SSMS通过数据库右键菜单删除数据库。使用DROP

15、 DATABASE删除数据库。其语法格式为： DROP DATABASE database_name ,.n 目录SQL Server数据库原理SQL Server数据库的组件和安装SQL Server数据库基本操作SQL Server数据库最佳实践SQL Server架构SQL Server内存结构负载特性(基于存储区域)数据文件：数据文件存放表和索引，其I/O特征与负载类型相关。数据库业务大致分为OLTP（Online Transaction Processing）和OLAP（Online Analytical Processing）两种类型： OLTP：小数据块随机读写 OLAP：大数据

16、块顺序读 日志文件：日志文件存放事务日志，负载特征为顺序写，数据块较小，数据块大小从512B到44KB不等，绝大多数在16KB以下，写操作为同步写。 TEMPDB：TEMPDB存放临时数据和排序的中间结果，负载特征为随机读写。 负载应用(基于不同应用)OLTP（Online Transaction Processing）业务的特征为大量用户并行查询更新，数据文件上的I/O表现为随机读写，绝大多数数据块为8KB或16KB，读比例在40% - 80%之间，对响应时间要求较高。 OLAP(Online Analytical Processing)业务的特征为少量用户进行批量查询，数据文件上的I/O表

17、现为多路顺序读，数据块一般为64KB。备份恢复：SQL Server备份恢复的负载特征为多路流大IO顺序读写。 BULK LOAD批量导入：主要为顺序写。数据文件：大IO顺序写； 日志文件：若数据库处于“完整”恢复模式，日志文件为大IO顺序写。若数据库没处于“完整”恢复模式，日志文件上基本没有写操作。 DBCC SQL Server数据一致性检查命令，可以针对整个数据库或者某个表进行一致性检查。IO特征为单并发顺序读，数据块为64KB。 SQL Server最佳实践了解业务情况规划存储子系统配置存储系统主机参数设置数据库参数设置了解业务情况在部署数据库之前，先进行性能采样和配置分析，以了解业务

18、和负载信息，确定性能和容量需求。了解负载特征：针对SQL SERVER数据库I/O子系统进行性能采样，了解诸如读写比例、IOPS、带宽、响应时间、队列深度等信息，了解数据库业务是OLTP类型还是OLAP类型，了解事务负载特征；了解数据库容量：查看生产系统信息，了解现有数据库的容量，并预估未来业务增长趋势，以此估算新系统的存储容量； 了解备份策略：数据库的备份策略与数据库性能、可靠性、容量均有较大关系，部署数据库之前应首先制定好备份策略，合理利用存储系统带宽，将备份对业务的影响降低到最小。 规划存储子系统(1/5)SQL Server存储子系统的规划包括容量、性能、可用性三个部分。数据库容量规划

19、表及索引容量根据业务及增长需求设置。日志：对于FULL及Bulk-logged模式，日志LUN大小至少满足25%的数据LUN大小。TEMPDB：OLTP业务其大小为最大索引的两倍，OLAP业务建议为数据文件大小的1/3。备份：每周一次全备，每天一次增备。备份设备的大小至少满足三次全备的总量。数据库可用性规划故障分离：建议将数据、日志、备份文件等放在不同的RAID上。分区：单张表的记录超过1亿，建议对其进行分区。故障转移：建议部分多服务器、交换机及路径的故障转移。SQL Server高级特性：建议开启SQL Server的镜像和复制来实现对数据库的保护。存储高级特性：建议开启存储系统的LUN拷贝

20、、镜像、远程复制等增值功能来实现保护。规划存储子系统(2/5)OLTP性能规划OLTP业务特性为随机读写，应首先分析估算事务IOPS需求，再根据单个硬盘的IOPS及RAID类型计算出需求的硬盘数。不同类型硬盘的OLTP IOPS如下图：硬盘类型OLTP IOPSNL-SAS50SAS 10K RPM100SAS 15K RPM150SSD2000规划存储子系统(3/5)不同类型的RAID的OLTP IOPS如下图：RAID组类型计算公式RAID0RIOPSRAID0 = WIOPSRAID0 = IOPSDISK * NRAID10RIOPSRAID10 = IOPSDISK * NWIOPS

21、RAID10 = 1/2 * IOPSDISK * NRAID5RIOPSRAID5 = IOPSDISK * (N-1)WIOPSRAID5 = 1/4 * IOPSDISK * NRAID6RIOPSRAID6 = IOPSDISK * (N-2)WIOPSRAID6 = 1/6 * IOPSDISK * N规划存储子系统(4/5)OLAP性能规划OLAP的业务特征为多路顺序读和批量顺序写，OLAP业务应首先分析估算带宽需求，再根据单个硬盘的带宽、I/O通道带宽、RAID类型计算出需求的硬盘数。不同类型硬盘的OLAP IOPS如下图：硬盘类型OLAP带宽NL-SAS20SAS 10K RP

22、M100SAS 15K RPM150SSD100规划存储子系统(5/5)不同类型RAID的OLAP IOPS如下图：RAID组类型计算公式RAID0RMBPSRAID0 = WMBPSRAID0 = MIN ( MBPSPATH , MBPSDISK * N )RAID10RMBPSRAID10 = MIN ( MBPSPATH , MBPSDISK * N )WMBPSRAID10 = 1/2 * MIN ( MBPSPATH , MBPSDISK * N )RAID5RMBPSRAID5 = MIN ( MBPSPATH , MBPSDISK * N )WMBPSRAID5 = (N-1)

23、/N * MIN ( MBPSPATH , MBPSDISK * N )RAID6RMBPSRAID6 = MIN ( MBPSPATH , MBPSDISK * N )WMBPSRAID6 = (N-2)/N * MIN ( MBPSPATH , MBPSDISK * N )配置存储系统硬盘类型选择(用户根据性能、容量及预算选择)一般OLTP业务选择读写性能高的SAS硬盘，如果容量需求小性能要求高可选择SSD硬盘。而OLAP业务也选择SAS硬盘。当用作备份时宜选择价格低廉的NL-SAS或SATA硬盘。RAID配置OLTP有大量的随机写，以及重要性高的日志文件，建议选择RAID10。OLAP有

24、大量的顺序读且容量大，以及用作备份时，建议选择RAID5。RAID5宜选择奇数盘，推荐使用5、7、9、22等成员盘数。RAID10宜选择偶数盘，推荐使用4、6、8、10、12等成员盘数。LUN配置归属控制器：将负载较大的LUN的归属控制器均分至AB控，保证负载均衡。分条深度：RAID5建议选择64KB或128KB，RAID10建议选择512KB。预取：LUN通常配置为智能预取，OLAP下建议配置为固定预取。写策略：数据库业务的LUN通常配置为”回写镜像”，可靠性要求高时配置为”透写”。主机参数设置设置I/O对齐创建MBR分区用于存放分区信息，部分分区隐藏时会引起I/O不对齐，性能下降。创建分区时，可选择跳过一个分条单元或整个分条的大小，建议使用512K的偏移量。NTFS块大小SQL Server页大小为8KB，NTFS文件系统的块大小不应小于8KB，考虑