D2008-4.7_SQL Server数据库管理.ppt

1、4.7管理和保护数据库，数据库执行的最小逻辑工作单位是事务，对所有数据库的操作作为整个事务执行或撤消。 数据库管理系统为基于事务的对数数据库提供了一些监控和管理功能，这些管理和保护技术主要控制数据库的恢复完整性控制安全性控制，以及4.7.1事务管理事务的概念事务：逻辑工作一个应用程序可以包含多个事务。 【例】在School数据库中完成学生退学的操作可以包括将成绩表的学生相关课程和成绩记录写入履历成绩表，以及写入履历成绩表。 从成绩表中删除该学生相关记录选择将该学生信息写入履历学生信息表的步骤和从学生表中删除该学生的记录。 如果多个SQL语句中的某些语句执行不正确，则数据库中的信息可能不一致。

2、如果将这些操作定义为一个事务，则如果有语句执行错误，则所有已经执行的语句都将回退，并返回到该事务执行之前的状态。 因此，如果这些语句全部成功或全部失败，就可以避免数据不一致。 2事务的特性事务具有原子性、一致性、隔离性和持久性4个特性，简称为ACID。 事务原子：组成一个事务的多个数据库操作是不可分离的原子单元，仅当所有操作成功时才提交整个事务。 事务完整性：事务完成后，所有数据必须保持一致，并且必须满足相关的约束规则以维护所有数据的完整性。 事务的隔离性：如果多个事务同时执行，就像它们单独执行一样，它们不会相互干扰。 事务持久性：事务完成后，对数据库中的所有更改都持久有效，即使系统故障导致数

3、据错误或丢失也可以恢复。 3. SQL Server的事务管理机制SQL Server通过强制执行事务管理和事务处理，确保每个事务都符合ACID特性。 主要的管理机制包括锁定机制，通过锁定将事务相互隔离，保持事务的隔离性，并支持多个事务的并行执行。 记录机制：在事务日志文件中记录事务的执行，以确保事务的持久性。 即使服务器硬件、操作系统或SQL Server本身出现故障，SQL Server也可以在重新启动时使用事务日志将未完成的事务自动回退到出现系统故障的位置。 强制管理：强制维持事务的原子性和一致性。 事务开始后，必须成功完成。 否则，SQL Server将撤消事务开始后对数据所做的任何更

4、改。 4事务的定义和应用数据库管理系统可以根据系统的默认规定自动分割和强制管理事务。 一般的句子是交易。 例如，修改School的T_Student表，以确保所有女孩都住在学校，并将LiveInDom设置为1。 updatet _ studentsetliveindom=1where sex=女性语句会影响表中多行的数据，从而确保事务正确完成对所有相关记录的修改。 用户在开发数据库应用程序时，可以根据数据处理的需要自己定义事务。 开始定义begin事务处理。 提交事务提交事务roll back事务取消该事务。 将4-70的SQL操作定义为一个事务，将退学学生的学生编号假定为“05101108”

5、，将历史学生信息表假定为T_HistoryStudent，将历史学生成绩表假定为t_historygraddentbegintransactioninsertintot _ historygradeselect * fromt _ gradewherestudentcode=05101108删除fromt _ gradewherestudentcode=05101108插入插入t _ historystudent fromt _ studentwhh fromt _ studentwherestudentcode=05101108 commit transaction，如果存在此事务，则为4.

6、7.2并发控制，2 )数据检索错误：一个事务回滚另一个事务3 )不可重读：一个事务读取了一个数据后，该数据由另一个事务更新，当该事务重读并验证该数据时，数据不匹配。 1 .并发访问问题1 )丢失更改：如果两个事务同时更改同一数据，则最后只有一个有效，另一个更改丢失。 使数据库中的相同数据可以从多个事务中同时访问。 例：人同时定机票，修改剩馀侑预机票的数量。 例如，如果一个确定票数的事务处理修改剩馀票数并回退，而另一个事务处理在回退之前读取剩馀票数，则该事务处理将获取“脏数据”。 2 .锁定管理数据库通过锁定机制解决并发访问问题。 锁定可以防止用户读取其他用户修改的数据，防止多个用户同时修改相同

7、的数据，同时访问数据库的锁定管理包括锁定、锁定和解锁事务在访问一个数据对象之前向系统申请锁定，被锁定的数据对象在从其他事务访问时受到限制，在事务完成之前，3. SQL Server的锁定粒度和锁定桃锁定到细微粒度(如行)会增加系统的并发访问，但开销会增加。锁定到大粒度会同时花费成本，但需要维护的锁定较少，所以需要的开销会降低。系统根据事务自动确定锁的粒度并执行锁定：表4-18 SQL Server可以锁定的资源的粒度(粒度从高到低的顺序)、SQL Server使用不同的锁定模式锁定资源，并发事务为资源、表4-19 SQL Server使用的锁定模式、4. SQL Server的并发控制方法(1

8、)乐观并发控制：乐观并发控制假设多个用户之间不容易发生资源冲突，并且可以在不锁定资源的情况下执行事务。 (2)悲观并发控制：系统使用锁定防止事务执行可能对其他事务产生负面影响的操作。 缺省情况下，使用悲观的并发控制。 SQL Server可以自动确定每个SQL语句的锁定级别并执行锁定，而无需用户指定和配置。 但是，用户可以通过处理死锁和设置死锁优先级来自定义应用程序的锁。 处理超时和锁定超时期间的设定。 设置事务处理隔离级别。 对SELECT、INSERT、UPDATE和DELETE语句使用表级锁提示符。 设置索引锁的粒度。 光标设置。 5. SQl Server的锁定显示可以使用sqlser

9、verenterprisemanager显示当前锁定。 “控制台根目录”“已注册的数据库”“管理”“当前活动”。 展开锁定/进程id显示每个连接的当前锁定，展开锁定/对象显示每个对象的当前锁定，图4-70sqlserverenterprisemanager的锁定显示功能，4.7.3数据库为每个库备份生成一个独立的库备份文件，这是库恢复的基础。 (2)事务日志。 为每个数据库的事务日志备份生成一个独立的日志备份文件。 备份用于恢复和恢复数据库的数据副本称为备份。、2备份的媒体备份一般需要大容量的存储设备，一般的备份媒体是(1)磁盘设备(2)磁带设备，选择3备份策略完整数据库备份是数据库的完整拷贝

10、，备份时间的数据库存储容量大，备份时间长。 无法恢复到故障点。 (4)文件和文件组的备份如果同时执行完整数据库备份需要大量资源或时间过长，则还需要备份部分数据库文件或文件组并执行单独的事务日志备份在恢复文件备份之后，使用事务日志前滚文件的内容，以匹配数据库的雄辩部分。 事务日志备份事务日志备份转储自上次数据库备份以来的事务日志。 如果在恢复了某个时间点的数据库备份之后，基于事务日志前滚事务，则在上次备份事务日志中的某个时间点发生恢复故障之后立即备份事务日志，会导致故障事务日志的备份会消耗相对较小的资源。 (2)数据库差分备份只复制自上次完整数据库备份以来发生变更的数据库页面。 占用空间小，备份

11、速度快，操作频繁。 差异备份根据完整数据库备份的恢复将数据库恢复到上次差异备份时间，但无法恢复到故障点。 各种备份策略可以结合使用。 一般来说，它至少包括两个部分：数据库备份和日志备份。【例4-73】将完整数据库备份和事务日志备份结合起来的备份策略的应用。 每天早上完全备份School数据库0336000，每4小时进行一次事务日志备份2 )如果某天上午11:00数据库出现故障，请使用完全数据库备份当天0336000数据库可使用日志恢复到上午8:00的数据库状态3 )数据库发生故障时，如果数据库管理系统工作，可立即备份数据库日志，使用该日志将数据库恢复到故障点。【例4-72】完整数据库备份和可恢

12、复性： School数据库的完整备份。 School数据库每天早上在0336000进行完全备份，如果某天上午11:00数据库发生故障，使用完全数据库备份只能恢复到当天0336000的数据库状态。 当天0:00到11336000对数据库的所有操作都丢失，4数据库恢复数据库恢复是备份支持的操作，可以根据备份策略选择不同的恢复策略，操作复杂，日期大(1)简单的恢复基于完整数据库备份和增量数据库备份将数据库恢复到上次备份的执行时间。 操作简单，备份操作点后数据丢失需要重建，非常适合于小型或数据不经常更改的数据库。 5SQL Server数据库备份和恢复(1)在手动备份数据库的可视化环境下完成当前数据库

13、的备份。 主要步骤有：选择备份数据库、选择备份策略、添加备份目标文件和设备、【示例4-74】将school数据库备份到磁盘文件D:SchoolBackup。 1 )选择数据库并运行备份菜单。 或者，2 )选择备份策略作为完全备份，然后单击“添加”按钮设置备份的目标设备。在“选择备份目标”窗口中添加备份目标设备或文件。 目标文件设置为d 3360静态备份。 3 )选择改写方式改写现有媒体时，点击【ok】按钮开始备份，完成后显示提示对话框。 可以使用备份进度和完成消息，(2)自动系统计划数据库备份数据库维护修订工具定义自动系统计划数据库备份。 每次执行备份时，维护时间表都会基于当前时间生成新的备份

14、文件名并保存备份【例4-75】建立了每天将school数据库备份到0:00盘目录d 3360，每隔4小时将事务日志备份到盘目录d 3360的数据库维护修订画面。 然后选择数据库并运行数据库维护计划菜单。 选择要备份的数据库方案，右键单击，找到“所有任务/维护计划”菜单，或者选择数据库时滞，然后使用菜单栏上的“工具/数据库维护计划”菜单2 )进入数据选择数据库页面，可以选择需要备份的一个或多个数据库。 3 )转到“更新数据优化信息”和“检查数据库”页，您可以使用复选框选择任务并修改作业调度。 a) b )图4-75数据库维护修订画面选择要备份的数据库，a) b )图4-76数据库备份作业时间表，

15、4 )进入“指定数据库备份修订画面”页面，选择“变更”按钮，然后选择作业转到“指定磁盘备份目录”页面，设置存储备份文件的目录位置。 6 )进入“指定事务日志备份调度”，选择【更改】按钮，进入“编辑重复出现的作业调度”页面，更改作业执行的频率和时间。 (a) b )指定图4-77磁盘备份目录的维护修订画面菜单，7 )转到“指定事务日志的磁盘备份目录”，默认情况下使用系统备份目录安装SQL Server b )图4-79作业计划报告和数据库维护修订版命名，9 )进入完成页，列出向导中已完成的所有备份修订版设置，更改维护修订版的“School维护修订版1”，然后单击“完成”按钮10 )成功创建数据库

16、维护计划后，将显示提示对话框，失败后将显示原因。 使用数据库维护计划时请小心：SQL server代理必须正常运行。 2 )自动命名数据库、事务日志备份文件和作业计划报告文件，以确保每次生成独立的文件。 3 )可以使用数据库服务器的“管理/SQL Server代理”文件夹下的作业对象查看和修改数据库维护计划，以及查看作业执行情况。 (3)数据库恢复SQL Server的数据库恢复可以由数据库管理员使用企业管理员提供的数据库恢复工具手动进行，也可以使用SQL语句进行。 使用school备份文件School_db_200711210000.Bak恢复数据库。 选择数据库服务器，然后运行数据库维护计划菜单。 在控制站根窗格中选择数据库，右键单击以查找“所有任务/还原数据库”菜单，或者选择数据库并使用菜单栏上的“工具/还原数据库”菜单选择“还原”的备份源，选择“数据库”，在显示数据库备份中选择要备份的数据，只需选中备份文件的复选框，按