4.7_SQL Server数据库管理.ppt

4.7数据库管理与保护,数据库运行的最小逻辑工作单位是事务，所有对数据库的操作，都以事务作为一个整体来执行或撤销。数据库管理系统以事务为基础对数据库提供了一些监控和管理功能，这些管理和保护技术主要包括：并发控制数据库恢复完整性控制安全性控制,4.7.1事务管理事务的概念事务：一个逻辑工作单元的数据库操作集合，称为事务。事务由一条或多条SQL语句构成。一个应用程序可以包含多个事务。,【例】在School数据库中完成一个学生退学的操作可以定义为一个事务，该操作包括：将成绩表中该生相关课程和成绩记录写入历史成绩表；删除成绩表中该生相关记录；将该生信息写入历史学生信息表；删除学生表中该生的记录；,如果多条SQL语句其中某些没有正确执行，那么数据库中的信息就可能会不一致。若把这些操作定义为一个事务，那么如果有语句执行错误，所有已经执行的语句都会回滚，恢复该事务执行前的状态，因此，这些语句要么所有都执行成功，要么所有执行失败，就可以避免数据的不一致。,2事务的特性事务具有原子性（Atomic）、一致性（Consistency）、隔离性（Isolation）和持久性（Durabiliy）四个特性，简称ACID。,事务的原子性：指组成一个事务的多个数据库操作是一个不可分隔的原子单元，只有所有的操作执行成功，整个事务才被提交。事务的一致性：指事务在完成时，必须使所有的数据都保持一致状态，仍然满足相关约束规则，以保持所有数据的完整性。事务的隔离性：指当有多个事务并发执行时，彼此互不干扰，与它们先后单独执行时的结果一样。事务的持久性：指一个事务完成之后，它对于数据库的所有修改永久性有效，即使出现系统故障造成数据错误或丢失也能恢复。,3.SQLServer的事务管理机制SQLServer通过强制事务管理和事务处理，保证每个事务符合ACID特性。主要管理机制有以下几个方面：锁定机制：通过加锁使事务相互隔离，保持事务的隔离性，支持多个事务并发执行。记录机制：将事务的执行记录在事务日志文件中，保证事务的持久性。即使服务器硬件、操作系统或SQLServer自身出现故障，SQLServer也可以在重新启动时使用事务日志，将所有未完成的事务自动地回滚到系统出现故障的位置。强制管理：强制保持事务的原子性和一致性。事务启动之后，就必须成功完成，否则SQLServer将撤消该事务启动之后对数据所作的所有修改。,4事务的定义和应用数据库管理系统可以按照系统默认的规定自动划分事务并强制管理，一般一条语句就是一个事务。,例如：修改School的T_Student表，将所有女生是否住校LiveInDom设置为1。UPDATET_StudentSETLiveInDom=1WHERESex=女这条语句影响表中多行数据，系统通过事务保证该语句正确完成对所有相关记录的修改。,用户在开发数据库应用程序时，可根据数据处理需要自己定义事务。BeginTransaction事务的定义开始；CommitTransaction提交事务；RollBackTransaction取消事务。,【例4-71】将4-70的SQL操作定义为一个事务，假定退学学生学号为”05101108”，历史学生信息表为T_HistoryStudent，历史学生成绩表为T_HistoryGrade。BeginTransactionINSERTINTOT_HistoryGradeSELECT*FROMT_GradeWHEREStudentCode=05101108DELETEFROMT_GradeWHEREStudentCode=05101108INSERTINTOT_HistoryStudentSELECT*FROMT_StudentWHEREStudentCode=05101108DELETEFROMT_StudentWHEREStudentCode=05101108CommitTransaction,该事务如果有其中任何一条语句执行错误，事务会回滚来撤消已执行的语句对数据库的更新操作结果。,4.7.2并发控制,2)数据获取错误：当一个事务恰好读取了另一个事务在回滚之前修改的数据，那么这个数据与数据库的实际数据不符合，该数据被称为“脏数据”。,3)不可重读：一个事务读取某数据后，该数据被另一事务更新，当该事务再读该数据进行校验时，数据不一致。,1.并发访问问题1)丢失修改：当两个事务同时对同一数据修改时，最后只有一个生效，另一个修改丢失。,允许数据库中的相同数据同时被多个事务访问。,例:两人同时定机票,修改剩余机票数。,例如，一个定票事务修改了剩余机票数后回滚，另一事务恰在其回滚前读取剩余机票，事务就获取了“脏数据”。,2.锁定管理数据库通过锁定机制解决并发访问的问题。锁定可以防止用户读取正在由其他用户更改的数据，并可以防止多个用户同时更改相同数据，解决数据库并发访问所带来的数据不一致问题。,锁定管理包括：加锁、锁定和解锁。事务在访问某数据对象之前，向系统申请加锁，加锁的数据对象在被其它事务访问时受到限制，待事务完成后锁被释放。,3.SQLServer的锁粒度和锁模式SQLServer具有多粒度锁定，允许一个事务锁定不同类型的数据对象。锁定在较小的粒度（例如行）可以增加系统并发访问能力但需要较大的系统开销；锁定在较大的粒度就并发而言是相当昂贵的，但要求的系统开销较低，因为需要维护的锁较少。,系统根据事务自动确定锁定粒度并实施锁定：,表4-18SQLServer可以锁定的资源粒度（按粒度增加的顺序列出）,SQLServer使用不同的锁模式锁定资源，这些锁模式用于确定并发事务访问资源的方式。,表4-19SQLServer使用的锁模式,4.SQLServer的并发控制方式（1）乐观并发控制：乐观并发控制假定不太可能在多个用户间发生资源冲突，允许不锁定任何资源而执行事务。（2）悲观并发控制：系统使用锁阻止事务执行可能对其他事务产生负面影响的操作。默认使用悲观并发控制。,SQLServer不必由用户指定和配置就可以自动动态确定每个SQL语句的锁的级别和执行锁定。但用户仍可以通过以下方法自定义应用程序中的锁定：处理死锁和设置死锁优先级。处理超时和设置锁超时持续时间。设置事务隔离级别。对SELECT、INSERT、UPDATE和DELETE语句使用表级锁定提示。配置索引的锁定粒度。游标设置,5.SQlServer的锁查看可以使用SQLServer企业管理器查看当前的锁。“控制台根目录”“已注册的数据库”“管理”“当前活动”。展开“锁/进程ID”可以查看每个连接的当前锁，展开“锁/对象”可以查看每个对象的当前锁,图4-70SQLServer企业管理器中的锁查看功能,4.7.3数据库备份和恢复磁盘的物理损坏、系统瘫痪、恶意破坏、数据操作失误等都有可能造成数据库损毁、数据不正确或部分数据丢失等。,1备份的对象：(1)数据库本身。一个数据库的每次备份都产生一个独立的数据库备份文件，它是数据库恢复的基础。(2)事务日志。对每一个数据库的事务日志的每次备份都会产生一个独立的日志备份文件。,备份：用来还原和恢复数据库的资料副本称为备份。,2备份的介质备份一般需要较大容量的存储设备，常用的备份介质：(1)磁盘设备(2)磁带设备,3备份策略的选择完整数据库备份备份是数据库的完整复本，可用它重建或恢复到备份时刻的数据库状态。占用存储空间大，备份所需时间较长。无法恢复到故障点。,(4)文件和文件组备份当一次进行完整数据库备份占据过大资源或耗时太长时，可以对部分数据库文件或文件组进行备份。同时必须进行单独的事务日志备份。在恢复一个文件备份后，使用事务日志将文件内容前滚，使其与数据库其余部分一致。,(3)事务日志备份事务日志备份转储上一次数据库备份之后的事务日志。在某一刻的数据库备份恢复之后，进一步依据事务日志前滚事务可恢复到最后一次备份事务日志中的某一时刻。如果在故障发生后，立即备份事务日志，可使数据库恢复到故障发生的那一刻。事务日志备份占用的资源比较小。,(2)数据库差异备份仅复制自上一次完整数据库备份之后修改过的数据库页。所占空间小，备份速度快，可频繁进行。在完整数据库备份恢复的基础上，差异备份能恢复数据库到最后一次差异备份时刻，但无法恢复到故障点。,可综合使用各种备份策略。一般至少包括数据库备份和日志备份两部分。,【例4-73】完整数据库备份与事务日志备份相结合的备份策略应用。1）对School数据库每天清晨0:00做完整备份，每隔4小时做事务日志备份；2）如果某天上午11:00数据库发生故障，使用完整数据库备份可恢复到当天0:00的数据库状态，再使用日志可以恢复到上午8:00的数据库状态；3）如果在数据库发生故障时，数据库管理系统可以工作，那么可以立即备份数据库日志，使用这个日志就可以将数据库恢复到故障点。,【例4-72】完整数据库备份及可恢复性：对School数据库的完整备份。School数据库每天清晨0:00做完整备份，如果某天上午11:00数据库发生故障，使用完整数据库备份只能恢复到当天0:00的数据库状态，当天0:00到11:00对数据库的操作全部丢失。,4数据库恢复数据库恢复是与备份相对应的操作，根据备份策略的不同可以选择不同的恢复策略，常用的策略有：,(2)完全恢复依据数据库备份和事务日志备份将数据库恢复到任意的时刻点。操作复杂，并且需要大量磁盘空间，对操作频繁且数据恢复非常重要的数据库才使用该策略。,(1)简单恢复依据完全数据库备份和增量数据库备份恢复数据库到最后一次备份的执行时刻。操作简单，备份操作点之后的数据丢失需要重建，适用于规模较小或数据不经常改变的数据库。,5SQLServer的数据库备份和恢复(1)人工进行数据库备份在可视化环境下完成当前时刻数据库的备份，其主要步骤包括选择备份数据库、选择备份策略、添加备份目的文件或设备、设置重写方式等。,【例4-74】将School数据库备份到磁盘文件D:SchoolBackup。,1)选中数据库并执行备份菜单。,或,2)选择备份策略为完全备份，点击添加按钮设置备份的目的设备；在选择备份目的窗口添加备份目的设备或文件，设置目的文件为：D:SchoolBackup。,3)选择重写方式为重写现有媒体，单击【确定】按钮开始备份，完成后出现提示对话框。,备份进度和完成提示信息,(2)系统定时自动进行数据库备份使用数据库维护计划工具可以定义数据库维护计划，让系统定时自动进行数据库备份。维护计划每次执行备份会根据当时时间生成一个新的备份文件名存放备份。,【例4-75】建立一个数据库维护计划，将School数据库每天0:00备份到磁盘目录D:下，事务日志每隔4小时备份一次到磁盘目录D:下。,选中数据库并执行数据库维护计划菜单。选中要备份的数据库School，单击右键找到“所有任务/维护计划”菜单；或选中数据库School后使用菜单栏中“工具/数据库维护计划器”菜单。,2)进入数据选择数据库页面，可以选择需要备份的一个或几个数据库。,3)进入“更新数据优化信息”页面和“数据库检查”页面，可以通过复选框选择各项任务并更改作业调度计划。,a)b)图4-75数据库维护计划选择需备份的数据库,a)b)图4-76数据库备份作业调度计划,4)进入“指定数据库备份计划”页面，选择【更改】按钮，修改作业执行的频度的时间为“每天一次”，“发生于0:00”。,5)进入“指定磁盘备份目录”页面，设置备份文件存放的目录位置。,6)进入“指定事务日志备份计划”，选择【更改】按钮进入“编辑反复出现的作业调度”页面，修改作业执行的频度和时间。,a)b)图4-77指定磁盘备份目录维护计划菜单,7)进入“指定事务日志的磁盘备份目录”，默认使用系统备份目录在SQLServer安装目录下，可修改存放位置。,a)b)图4-79作业调度报表和数据库维护计划命名,9)进入完成页面，列出了通过向导完成的所有备份计划设置，为维护计划重新命名“School维护计划1”，然后点击【完成】按钮。,10)如果数据库维护计划创建成功，会弹出提示对话框，如果不成功，会提示原因。,使用数据库维护计划时要注意：,SQLServerAgent必须启动正常运行。,2）系统为数据库、事务日志备份文件及作业调度报表文件自动命名，保证每次生成独立的文件。,3）可以使用数据库服务器“管理/SQLServer代理”文件夹下的作业对象查看和修改数据库维护计划，并查看作业执行情况。,(3)数据库的恢复SQLServer的数据库恢复可以由数据库管理员通过企业管理器提供的数据库还原工具人工完成，还可使用SQL语句完成。,【例4-75】使用School备份文件School_db_200711210000.Bak恢复数据库。,选中数据库服务器并执行数据库维护计划菜单。在控制台根目录窗格选中数据库，点右键找到“所有任务/还原数据库”菜单；或选中数据库后使用菜单栏中“工具/还原数据库”菜单；,2)在“还原数据库窗口”中，设置“还原为数据库”的数据库名，可以使用原数据库名，也可重新命名。选择“还原”的备份源，如果选择“数据库”，在显示数据库备份中选择备份的数据，只要将备份文件的复选框选中，按【确定】按钮就开始数据库恢复。,图4-83数据库还原过程,3)如果在“还原数据库”窗口选择“还原”的备份源时选择了“从设备”，则可选择本服务器上任何磁盘或磁带位置上存放的数据库备份或日志备份文件进行还原。,图4-84选择从磁盘或磁带备份文件还原数据库,4)如果还原的后数据库文件和日志文件的位置与原位置不同，需要进入”选项”窗口设置逻辑文件名及文件位置。,当把一台数据库服务器上生成的备份文件还原到另一台服务器上时,一般需要设置文件位置。,6系统群集备份技术群集备份技术采用网络上两个以上运行着相同数据库系统的服务器，当一个服务器出现故障后，其它服务器接管该服务器继续工作。,双机热备份技术：操作系统、数据库管理系统和应用程序安装在两台服务器的本地硬盘上，通过一个虚拟名称对外服务，某一时刻只有一台处于活动状态。系统信息以及数据库数据同时写入两台服务器，保证数据即时同步。双机侦测软件检测到当前活动的服务器故障发生时会自动激活备份服务器接管服务，保证系统服务不间断。,7数据导入和导出SQLServer通过数据转换服务(DataTransformationServices，DTS)提供数据导入/导出功能，可实现在同类或异类OLEDB和ODBC数据源之间的数据导入、导出、验证以及转换。,【例4-77】将School中班级表T_Class数据导出到Excel文件D:Class.xls。,1)选中数据库，点右键找到“所有任务/导出数据”菜单，进入DTS导入/导出/向导首页。,图4-85数据导出菜单及向导首页,2)分别在数据源窗口和目的窗口完成设置。选择数据源为：SQLServer的MicrosoftOLEDB提供程序，服务器为本机Local，数据库为School）。选择目的：MicrosoftExcel97-2000，文件名为D:Class。,3)在指定表复制或查询窗口选择“从源数据库复制表和视图”；在选择源表视图窗口在复选框中选中要导出的数据表T_Class。,4)立即执行或设置DTS调度计划以便以后系统自动执行。,图4-89数据导出完成,数据导入/导出对象可以是表、视图、存储过程和约束等，一般一次可同时导入/导出多个对象。,8附加和分离数据库在复制数据库前数据库必须脱离SQLServer，只能实现脱机复制。,【例4-77】将School数据库分离，然后将数据库文件School_Data.MDF复制为School_DataBak.MDF，再附加数据库School_DataBak.MDF。,1）分离数据库：在“数据库”文件夹找到School数据库，右击选择“所有任务/分离数据库”菜单。在“分离数据库”对话框中，当数据库状态显示“该数据库状态已就绪，可以分离”时，单击【确定】按钮。,2）复制数据库文件：利用Windows操作系统的“我的电脑”的文件复制功能将数据库文件School_Data.MDF文件复制为School_DataBak.MDF。,3）附加数据库：右击“数据库”，选择“所有任务/附加数据库”菜单。,4.7.4数据库安全性控制广义来讲，数据库系统安全性包括网络系统安全性、操作系统安全性和数据库管理系统、应用系统安全性等。DBMS的安全性控制，主要是指保护数据库，防止因用户非法使用数据库造成数据泄露、更改或破坏。,1数据安全控制技术（1）用户标识和认证：用户名及口令验证。（2）访问控制：DBMS通过授权可以定义合法用户访问数据库内各个对象的操作权限。访问权限包括两部分：数据对象：数据库、表、字段、视图、存储过程等。数据操作：读取、插入、修改、删除、执行存储过程等。（3）视图机制：在DBMS中，为不同的用户定义不同的视图，通过视图机制把要保密的数据对无权操作的用户隐藏起来。（4）审计方法：审计可以跟踪用户的全部操作记录下来放入审计日志文件中，可以利用审计跟踪的信息，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容等。（5）数据加密：根据一定的算法将原始数据（称为明文）变换为不可直接识别的数据格式（称为密文），从而使得不知道解密算法的人即使进入数据库也无法识别数据的内容。,2SQLServer的安全管理和控制SQLServer提供了对数据库中的数据对象的授权用户访问控制，可以使用的安全技术包括身份验证模式、登录、用户、角色、授权、废除和拒绝对象上的权限以及数据加密等。SQLServer在三个层次上进行安全管理:,图4-91数据库安全管理的三个层次,（1）SQLServer的身份认证模式Windows认证模式(集成身份认证模式)允许一个已登录Windows的网络用户不必再提供一个单独的登录账号就能登录到该系统中的一个SQLServer服务器，从而实现SQLServer服务器与Windows登录的安全集成SQLServer认证模式。用户必须输入有效的SQLServer登录账号及口令。这个登录账号是独立于操作系统的登录账号的，从而可以在一定程度上避免操作系统层上对数据库的非法访问。,图4-92登录认证模式设置,（2）设置身份认证模式系统在安装时会询问使用何种认证模式，也可使用企业管理器重设认证模式：1）在企业管理器中右击需要设置的SQL服务器，在弹出菜单中选择“编辑SQLServer注册属性”命令。2）在右图对话框的选择认证模式。,（3）登录账号管理登录账号是服务器级用户访问数据库系统的标识，登录账号的信息是系统信息，存储在master数据库中。SQLServer有三个默认的登录账号：sa被称为系统管理员(SystemAdministrator)，在SQLServer系统中它拥有全部权限，可以执行所有的操作。Builtin/Administrators是SQLServer为Windows操作系统系统管理员提供的默认账号，在SQLServer系统中它拥有全部权限，可以执行所有的操作。Guest：访问系统的默认用户帐号。,使用企业管理器在SQLServer【安全性】文件夹中可以创建、查看和管理登录账号。查看单击“登录”可以看到当前数据库服务器的合法登录用户的一些信息。,图4-93用户帐号查看,在新建登录对话框中填写名称“Wanghong”，密码“123456”，选择数据库访问标签后在可访问的数据库列表择“School”，选择常规标签后设置默认访问数据库“School”。,3）编辑或删除登录账号单击“登录”文件夹，在出现的显示登录账号的窗口中，右击需要操作的登录号：选择“属性”便可对该用户已设定内容进行重新编辑；选择“删除”便可删除该登录用户。,【例4-79】创建一个名称为“Wanghong”的SQLServer认证帐号，并为其设置密码“123456”，设置其默认访问的数据库为School。,2)创建登录账号,（4）数据库系统角色管理和权限管理角色可以简化其安全管理。将一组用户归类为一个角色，通过对角色的数据访问权限和操作权限管理，自动实现对属于该角色的所有用户成员的权限管理。一个用户可以是任意多个角色的成员，同时持有每个角色的权限，也可直接给用户授权，这些权限的累加就是该用户的访问权限集合。,图4-96数据库角色、用户及权限访问权限管理示意图,表4-21SQLServer主要数据对象和操作权限,SQLServer中有两类预定义角色：即固定服务器角色和固定数据库角色。这两个角色具有系统级别或数据库级的操作权限，如果有用户需要这些权限，则必须将其帐户添加到这些预定义角色。另外，数据库管理员也可根据需要为用户定义新的数据库角色。,用户自定义数据库角色：根据需要为用户定义新的数据库角色。,常用固定数据库角色：,【例4-80】为School数据库创建一个名称为“Teacher”的数据库角色，设置其对所有表具有Select权限，此外，对T_Course表还具有Insert、Update、Delete权限，禁止在T_Student和T_Class执行Delete操作，对T_Grade表仅对Grade列具有Update权限，其他列具有Select权限。,1）在企业管理器中打开要操作的数据库文件夹，右击“角色”文件夹，并在弹出的菜单中选择“新建数据库角色”命令，则出现新建数据库角色对话框，填写角色名称“Teacher”信息后，单击【确定】按钮即可完成角色创建。2）在角色文件夹下选择Teacher角色，右击可以弹出数据库角色属性对话框，按照需要选择该角色对数据库各个对象（除表T_Grade外）的操作权限，单击可在授权、禁止与撤消之间切换，如图4-97c）所示。：授予权限。表示授予该项操作权限。：禁止权限。表示禁止该项操作权限，且该禁止不能被其他角色的权限覆盖。空：撤消权限。表示不能执行该项操作，但可被其他角色的权限覆盖。1）在数据库角色属性对话框中选择T_Grade，如图4-98a）；然后选择窗体下部【列】按钮，设置各个列的操作权限，按【确定】按钮完成，如图4-98b）。,1）在企业管理器中打开要操作的数据库文件夹，右击“角色”文件夹，并在弹出的菜单中选择“新建数据库角色”命令，则出现新建数据库角色对话框，填写角色名称“Teacher”信息后，单击【确定】按钮即可完成角色创建。,2）在角色文件夹下选择Teacher角色，右击弹出数据库角色属性对话框，选择该角色对数据库各个对象（除表T_Grade外）的操作权限，单击可在授权、禁止与撤消之间切换。：授予权限。表示授予该项操作权限。：禁止权限。表示禁止该项操作权限，且该禁止不能被其他