管理和配置数据.ppt

《管理和配置数据.ppt》由会员分享，可在线阅读，更多相关《管理和配置数据.ppt（77页珍藏版）》请在三一办公上搜索。

1、1,第四章 管理和配置数据库,管理和配置数据库,2,四个默认系统数据库,SQL Server 服务器安装完毕后，默认会建立6个数据库。Pubs和Northwind数据库为用户数据库，存储的是实例数据，供DBA和开发人员练习使用。其余的4个数据库为系统数据库，用于SQL Server 2000服务器的系统管理使用。,3,master数据库有什么作用,master数据库是SQL Server 中最重要的数据库，存储的是SQL Server 的系统信息，包括磁盘空间、文件分配和使用、系统级的配置参数、所有的登录账户信息、初始化信息和其他数据库的信息等。由于master数据库对SQL Server 的

2、重要性，所以禁止用户的直接访问，并要确保在修改之前有完整的备份。,4,model数据库有什么作用,model数据库存储了所有用户数据库和tempdb数据库的模板。它包含有master数据库的系统数据表的子集，这些子集用来创建用户数据库。用户可以修改model数据库的对象或者权限，这样新创建的数据库就将继承model数据库的对象和权限。,5,msdb数据库有什么作用,msdb数据库是SQL Server 代理服务使用的数据库。为警报、作业、任务调度和记录操作员的操作提供存储空间。,6,tempdb数据库有什么作用,tempdb数据库是一个临时数据库，它为所有的临时表、临时存储过程以及其他的临时操

3、作提供空间。每次SQL Server 服务器的重新启动都会重新建立tempdb数据库。tempdb数据库的数据是暂时的，不是永久存储的，每次重启都会导致以前数据的丢失。,7,为什么需要临时数据库呢,SQL查询语句处理和排序过程中会产生一些临时的中间结果，tempdb数据库就是用来保存这些中间结果的。保存中间结果的表称为临时表。临时表有以下两种类型。私有临时表：只能由创建临时表的用户使用，以#临时表名为标识。全局临时表：可以由所有的用户使用，以#临时表名为标识。,8,为什么需要临时数据库呢,DBA可以在tempdb数据库中显式创建临时表，也可以由tempdb数据库自动分配临时表。在【企业管理器】

4、的管理目标导航【树】下选择【控制台根目录】/【Microsoft SQL Servers】/【SQL Server组】/【MYNETSERVER】/【管理】/【当前活动】/【锁/对象】选项并单击，可在出现的列表中发现以tempdb命名的对象，这是一个用于管理锁信息的全局临时表,9,4个系统数据库是如何启动的,通过日志可以发现SQL Server 2005服务器的启动过程中，4个数据库的启动关系如下。启动master数据库；启动model数据库；启动msdb数据库；清除tempdb数据库；启动tempdb数据库。,10,创建用户数据库,要开发一个新的数据库应用系统，DBA应根据需要来建立用户数据

5、库。,11,谁可以创建用户数据库,在缺省情况下，创建数据库的权限默认授予sysadmin（系统管理员）和dbcreator（数据库创建者）服务器角色的成员，被授权的其他用户也可以创建数据库。创建数据库的用户将成为该数据库的所有者。,12,如何命名用户数据库,在一个SQL Server 服务器上，最多可以创建32767个数据库。数据库名称必须遵循SQL Server 2000的标识符命名规则。名称的长度可以从1128。名称的第一个字符必须是一个字母或者“_”、“”和“#”中的任意字符。在中文版SQL Server 中，可以直接使用中文名称。名称中不能有空格，不允许使用SQL Server的保留字

6、。,13,数据库的数据文件,数据文件包含数据库的启动信息，并用于存储数据。数据文件有以下两种类型。主要数据文件：主要数据文件只有1个每个数据库都有一个主要数据文件，推荐文件扩展名是.mdf。次要数据文件：次要数据文件大于等于0个，如果主要数据文件可以包含数据库中的所有数据，那么数据库就不需要次要数据文件。次要数据文件的推荐文件扩展名是.ndf。,14,数据库的日志文件,日志文件包含恢复数据库所需的所有日志信息每个数据库必须至少有一个日志文件，但可以不止一个日志文件的推荐文件扩展名是.ldf日志文件的存储结构不是页面，而是包含一系列日志记录。,15,评估数据文件的初始大小,SQL Server

7、2000采用的是先分配空间后使用的方法。如果不对文件的初始大小进行评估，就可能导致过小或者过大的空间分配。过小的空间分配会导致数据库不够用或者需要不停地动态分配。过大的空间又会产生浪费。所以在创建用户数据库之前，对数据库的数据文件的大小进行初步评估是一个很好的习惯。,16,数据文件的主要数据,数据文件主要的数据是表和索引。在实践过程中，通过评估这两者的数据量就可以评估出数据文件的初始大小。现有一个高校教学管理数据库，参数如下。,17,实例,学生人数为4000人，教师人数为1000人，学校共20个系，教学班共60个，每个班平均开设课程20门，每个教师给2个班上课。现要求设计一个SQL Serve

8、r 2000数据库并评估数据文件的初始大小。,18,评估数据部分大小,成绩表数据：4000*60*20=480 000数据。学生表数据：4000=4 000行数据。授课表数据：1000*60*2=120 000行数据。总的数据量：480+4+120=604 000行数据。考虑到评估的误差，考虑100 000行的数据。评估的数据量：704 000行数据。每行数据的大小：100字节。总的数据字节数：704 000行*100字节=70.4MB。这是一个典型的OLTP（联机事务处理）数据库。,19,评估索引部分大小,SQL Server中有两种类型的索引，即聚集和非聚集索引。对于聚集索引，索引大小为数

9、据大小的1%以下是一个比较合理的取值。对于非聚集索引，索引大小为数据大小的15%以下是一个比较合理的取值。,20,评估索引部分大小,所以如果建立聚集索引：索引的大小=70.4*1%=0.74MB。如果建立非聚集索引：索引的大小=70.4*15%=10.5MB。,21,评估数据文件的初始大小,将数据部分和索引部分相加就是数据文件的初始大小，约80MB左右。,22,数据库工作在什么状态,选择建立的数据库用鼠标右键单击，在出现的快捷菜单中选择【属性】选项。【状态】显示了数据库的工作状态。默认情况下为【普通】，表示数据库可以读和写。如果数据库状态显示为【待命】，表示数据库处于只读状态。,23,数据库当

10、前空间利用率是多少,在【常规】选项卡中可以获得的另外一个重要信息是数据库的当前空间利用率。这里的空间不包括日志文件，仅仅是所有的数据文件的空间利用率。大小：数据库的当前空间大小。可用空间：数据库的当前可用空间大小。数据库的空间利用率=（数据库的当前空间大小数据库的当前可用空间大小）/数据库的当前空间大小,24,怎样添加次要数据文件,换到【数据文件】选项卡。在【文件名】单元格中输入次要数据文件的逻辑名称可以在【位置】单元格中修改默认的文件路径。在【分配的空间】单元格中更改初始化大小。,25,怎样添加多个日志文件,【文件名】单元格中显示了日志文件的逻辑名称，可以在【位置】单元格中修改默认的文件路径

11、。在【分配的空间】单元格更改初始化大小。,26,什么是文件组,文件组是SQL Server 2000数据文件的一种逻辑管理单位，若干个分布在不同的硬盘驱动器上的数据文件可以组织成一个文件组。,27,建立文件组的目的,可以更好地分配和管理存储空间，通过控制在特定磁盘驱动器上放置数据和索引来提高数据库的性能。由于操作系统对物理文件的大小进行了限制，所以当某个磁盘上的数据文件超过单个文件允许的最大值时，可以使用文件组中存贮在其他驱动器上的数据文件扩充存储空间。,28,SQL Server 2000支持的文件组,主文件组（Primary）主文件组包含主要数据文件和任何没有明确指派给其他文件组的其他数据

12、文件。系统表的所有页面都分配在主文件组中。用户定义文件组用户定义文件组是在CREATE DATABASE或ALTER DATABASE 语句中，使用FILEGROUP关键字指定的文件组。,29,什么是默认的文件组,每个数据库都有一个文件组作为默认文件组运行。当 SQL Server 2000给创建时没有指定文件组的表或索引分配页面时，将从默认文件组中进行分配。一次只能有一个文件组作为默认文件组。如果没有指定默认文件组，则主文件组是默认文件组。,30,如何使用文件组,大多数数据库在只有单个数据文件和单个事务日志文件的情况下可以很好地运行。如果使用多个文件，应为附加文件创建第二个文件组，并将其设置

13、为默认文件组。这样，主文件将仅包含系统表和对象。,31,合理使用文件组 提高性能,若要获得最佳性能，应在尽可能多的可用本地物理磁盘上创建文件或文件组，将争夺空间最激烈的对象置于不同的文件组中。使用文件组以允许将对象放置在特定的物理磁盘上。,32,合理使用文件组 提高性能,将在同一联接查询中使用的不同表置于不同的文件组中。由于采用并行磁盘输入/输出对联接数据进行搜索，所以性能将得以改善。将最常访问的表和属于这些表的非聚集索引置于不同的文件组上。如果文件位于不同的物理磁盘上，由于采用并行输入/输出，所以性能得以提高。,33,合理使用文件组 提高性能,不要将日志文件与其他文件和文件组置于同一物理磁盘

14、上。,34,如何创建文件组,切换到【文件组】选项卡。在【名称】单元格中输入用户创建文件组的名称。在【数据文件】选项卡中添加数据文件后，在【文件组】单元格就可以选择该数据文件所属的文件组。,35,如何限制用户对数据库的访问,选择【限制访问】复选框可以指定能够访问数据库的用户类型，包括以下两种。db_owner、dbcreator或sysadmin的成员：只有这3种角色的成员才可以访问数据库。单用户：指定一次只能有一个用户访问数据库。选择【只读】复选框指定用户可以检索数据库中的数据，但不能修改。,36,什么是数据库的单用户模式,单用户模式就是数据库同时只允许一个用户使用。通常是DBA为了对数据库进

15、行维护时，不希望其他用户访问数据库，就需要转入单用户模式。,37,数据库的故障还原模型,备份与恢复是DBA为了确保数据库的安全而需要经常执行的操作。任何备份和恢复措施都很难达到100%的成功，只能是尽可能地减少损失。,38,数据库的故障还原模型,对于不同的数据库系统，DBA需要如何备份，利用备份进行恢复能够接受的数据损失可以到什么程度。SQL Server 2000提供了3种数据库恢复模型供DBA选择，,39,数据库的故障还原模型,简单模型完全模型大容量日志记录的,40,大容量日志记录的还原模型,优点：允许高性能大容量复制操作。大容量操作使用最少的日志空间损失：如果日志损坏，或者自最新的日志备

16、份后发生了大容量操作，则必须重做自上次备份后所做的更改，否则不丢失任何工作能否恢复到即时点：可以恢复到任何备份的结尾处。随后必须重做更改,41,简单模型还原模型,优点：允许高性能大容量复制操作，收回日志空间，使得空间要求最小损失：必须重做自最新的数据库或差异备份后所发生的更改能否恢复到即时点：可以恢复到任何备份的结尾处。随后必须重做更改,42,完全模型还原模型,优点：数据文件丢失或损坏不会导致工作损失。可以恢复到任意即时点（例如，应用程序或用户错误之前）损失：正常情况下没有。如果日志损坏，则必须重做自最新的日志备份后所发生的更改能否恢复到即时点：可以恢复到任何即时点,43,简单恢复还原模型概述

17、,简单恢复模型最容易操作，在这种恢复模型下，只能进行完全备份和差异备份，因为事务日志总是被截断，事务日志备份不可用。一般地，对于一个包含关键性数据的系统，不应该选择简单恢复模型，因为它不能够帮助我们把系统还原到故障点。,44,简单恢复还原模型概述,使用这种恢复模型时，我们最多只能把系统恢复到最后一次成功进行完全备份和差异备份的状态。进行恢复时，我们首先要恢复最后一次成功进行的完全备份，然后在此基础上恢复差异备份。,45,完全恢复还原模型概述,完全恢复模型具有把数据库恢复到故障点或特定即时点的能力。对于保护那些包含关键性数据的环境来说，这种模型很理想，但它提高了设备和管理的代价，因为如果数据库访

18、问比较频繁的话，系统将很快产生庞大的事务日志记录。,46,大容量日志记录还原模型概述,大容量日志记录恢复模型中，大容量复制操作的数据丢失程度要比完全恢复模型严重。完全恢复模型记录大容量复制操作的完整日志，但在大容量日志记录恢复模型下，SQL Server只记录这些操作的最小日志，而且无法逐个控制这些操作。在大容量日志记录恢复模型中，数据文件损坏可能导致要求手工重做工作。,47,数据库的兼容性,在【兼容性】区域的【级别】下拉列表框中可以设置数据库的兼容性。该参数主要用于多版本的SQL Server 数据库环境。兼容性级别越低，就越兼容老版本。,48,如何设置用户访问数据库的权限,切换到【权限】选

19、项卡，在其中可以设置用户/角色对数据库的权限。,49,分离用户数据库,SQL Server 2000服务器由若干个数据库组成，除了master、model和tempdb这3个系统数据库外，其余的数据库都可以从服务器的管理中分离出来，脱离服务器的管理，同时保持数据文件和日志文件的完整性和一致性，这样分离出来的数据库的日志文件和数据文件可以附加到其他SQL Server 2000服务器上构成完整的数据库，附加的数据库和分离时完全一致。,50,附加用户数据库,附加数据库可以很方便地在SQL Server 2000服务器之间利用分离后的数据文件和日志文件组织成新的数据库。在实际工作中，分离数据库作为对

20、数据基本稳定的数据库的一种备份的办法来使用。,51,如何分离数据库,在【企业管理器】的管理目标导航【树】下选择刚建立的数据库上单击鼠标右键，在出现的快捷菜单中选择【所有任务】/【分离数据库】选项。在【数据库状态】区域可以查看是否有客户机连接到数据库，如果有连接则不能进行分离操作，单击 按钮可以断开客户机的连接。,52,如何附加数据库,在管理目标导航【树】下选择【控制台根目录】/【Microsoft SQL Servers】/【SQL Server组】/【MYNETSERVER】/【数据库】选项，用鼠标右键单击，在出现的快捷菜单中选择【所有任务】/【附加数据库】选项。单击 按钮将要附加数据库的主

21、要数据文件添加到【要附加数据库的MDF文件】文本框中。单击 按钮可以验证数据文件和日志文件是否一致。在【附加为】文本框中可以输入附加后的数据库名称。在【指定数据库所有者】下拉列表框中可以选择数据库的新所有者。,53,分离和附加数据库,分离和附加数据库的操作可以将数据库从一台计算机移到另一台计算机，而不必重新创建数据库当附加到数据库上时，必须指定主数据文件的名称和物理位置。主文件包含查找由数据库组成的其他文件所需的信息。如果存储的文件位置发生了改变，就需要手工指定次要数据文件和日志文件的存储位置,54,如何脱机数据库,如果想暂时关闭用户数据库的服务，就可以选择脱机方式。选择要脱机的数据库后用鼠标

22、右键单击，在出现的快捷菜单中选择【所有任务】/【脱机】选项,55,如何联机数据库,对于暂时关闭的数据库重新启动服务，就可以选择联机操作。选择要联机的数据库后用鼠标右键单击，在出现的快捷菜单中选择【所有任务】/【联机】选项,56,什么是数据库的维护计划,确保数据库能够正常运转是DBA日常非常重要的任务。作为一名DBA，日常到底要做一些什么样的核心维护操作呢？SQL Server 2000提供了称为“数据库维护计划”的向导工具，可以帮助DBA轻松胜任数据库的日常核心维护工作。,57,数据库维护计划的主要工作,设置新的索引填充因子，重建索引以重新组织数据和索引页面上的数据。确保了数据页面的数据量和可

23、用空间分布均匀，从而优化数据库的性能。通过删除空的数据页面压缩数据文件的空间。,58,数据库维护计划的主要工作,更新索引的统计信息，以确保查询优化器能够按照新的统计信息进行基于代价的查询优化。虽然 SQL Server 2000自动定期更新索引的统计信息，但是维护计划可以强制更新统计信息。,59,数据库维护计划的主要工作,对数据库内的数据和数据页面执行内部一致性检查，以确保没有损坏的数据。按照指定的目录、保存时间来自动备份数据库和事务日志文件。,60,数据库维护计划的主要工作,设置日志传送。日志传送允许将事务日志从一个数据库（源）不断地传送到另一个数据库（目的）。通过使目的数据库与源数据库保持

24、同步，用于建立高可用性环境的备用服务器。定时自动调度并执行的数据库维护计划需要代理服务的支持。,61,创建数据库维护计划,【检查数据库完整性】复选框：运行 DBCC CHECKDB语句检查数据库中用户和系统表及索引的分配及结构完整性。【包含索引】单选钮：在完整性测试时同时检查数据库中的数据和索引页面。,62,创建数据库维护计划,【尝试修复所有小问题】复选框：系统将数据库置于单用户模式下，这样在数据库完整性测试过程中自动尝试修复所检测到的任何小故障。【排除索引】单选钮：完整性测试时只检查数据库中的数据页面，不检查索引页面。,63,创建数据库维护计划,【备份之前执行这些检查】复选框：在备份数据库或

25、事务日志之前执行数据库完整性测试。如果完整性测试检测到不一致，将不进行随后的数据库或事务日志备份。完成设置后单击 按钮。,64,为什么要收缩数据库,SQL Server 2000采取预先分配空间的方法来建立数据库的数据文件或者日志文件比如数据文件的空间分配了100MB，而实际上只占用了50MB空间，这样就会造成存储空间的浪费。SQL Server 2000提供了收缩数据库的功能，允许对数据库中的每个文件进行收缩，删除已经分配但没有使用的页。,65,收缩数据库的最小大小,不能将整个数据库收缩到比其原始大小还要小。如果数据库创建时的大小为 10 MB，后来增长到100MB，则该数据库最小能够收缩到

26、10MB不能在备份数据库时收缩数据库，也不能在收缩数据库时创建或备份数据库。,66,什么是自动收缩和手动收缩,SQL Server 2000支持对数据库实行自动收缩和手工收缩。在数据库属性的【选项】选项卡中选择【自动收缩】复选框将实现自动收缩。SQL Server 2000服务器每30分钟检查数据库的空间使用情况。如果发现大量闲置的空间，就会自动缩小数据库的文件大小。利用企业管理器或DBCC SHRINKDATABASE 和 DBCC SHRINKFILE 可以手工收缩操作。,67,收缩数据文件和日志文件的不同,不论是收缩数据文件还是日志文件，都会确实减少物理文件的大小，两者有不同数据文件可以

27、作为文件组或单独地进行手工收缩。日志文件收缩分别考虑每个文件，基于整个日志文件进行收缩。日志文件的收缩将删除非活动的VLF。,68,收缩数据库的案例,在【企业管理器】的管理目标导航【树】下选择数据库，单击鼠标右键，在出现的快捷菜单中选择【所有任务】/【收缩数据库】选项。在【数据库大小】区域可以查看【已经分配的空间】和【可用的空间】，这是决定是否进行收缩的重要依据。,69,收缩数据库的案例,在【收缩操作】区域的【收缩后文件中的最大可用空间】文本框中设置收缩数据库后数据文件中的最大可用空间百分比。选择【在收缩前将页移到文件起始位置】指定在收缩数据库前将页移到文件的起始位置，选择该选项可能会影响性能

28、。在【调度】区域选择【根据调度收缩数据库】复选框，单击 按钮可以设置自动收缩数据库的时间。,70,收缩数据库的案例,在【收缩文件】区域单击 按钮，出现【收缩文件】，可以对数据文件或日志文件设置更精细的收缩策略。在【数据库文件】下拉列表框中可以选择要收缩的数据文件或者日志文件。在【收缩操作】区域可以设置文件的具体收缩操作，包括是否截断、压缩页以及收缩后的文件大小等。在【延迟收缩】区域可以设置自动收缩的时间。完成设置后单击 按钮。,71,收缩数据库的案例,收缩数据库可以删除已经分配但尚未使用的页，从而更好地利用存储空间。与收缩相对应的是扩充数据库，即修改数据库的属性即可。收缩后的可用空间应该留有一

29、定的空闲空间，这样当数据发生更改时不必额外分配空间。,72,删除用户数据库,在【企业管理器】的管理目标导航【树】下选择数据库，单击鼠标右键，在出现的快捷菜单中选择【删除】选项出现【删除数据库】界面。默认选择【为数据库删除备份并还原历史记录】复选框表示将同时删除该数据库的备份。单击 按钮完成删除。删除数据库时，【企业管理器】将自动删除数据库的日志文件和数据文件。,73,重命名数据库,在重命名数据库之前确保没有人使用该数据库数据库设置为单用户模式调用存储过程sp_renamedb,74,删除数据文件,只有数据文件上不存在已有的数据，文件必须完全为空后才能删除数据文件使用DBCC SHRINKFIL

30、E指定EMPTYFILE子句将数据从一个数据文件迁移到同一文件组中的其他文件DBCC SHRINKFILE(数据文件名,EMPTYFILE)使用ALTER DATABASE语句删除数据文件ALTER DATABASE 数据库名 REMOVE FILE 数据文件名,75,删除日志文件,通过将事务日志数据从一个日志文件迁移到另一个日志文件中以删除事务日志文件是不可能的若要从事务日志文件清除非活动的事务，必须截断或者备份该事务日志一旦事务日志文件不再包含任何活动或者不活动的事务，该日志文件就可以从数据库中删除,76,删除日志文件,DBCC SHRINKFILE(日志文件名,TRUNCATEONLY)ALTER DATABASE 数据库名 REMOVE FILE 日志文件名,77,查看数据库空间使用情况,Sp_spaceused可以显示行数、保留的磁盘空间以及当前数据库中的表所使用的磁盘空间，或者显示由整个数据库保留和使用的磁盘空间如果没有给定参数，sp_spaceused 则报告整个当前数据库所使用的空间 Sp_spaceusedSp_spaceused 表名,