1、第8章 SQL Server 系统管理,8.1安全性管理,8.3 数据库恢复,8.2数据库备份,8.4 数据复制,8.5 事务,8.6 锁,8.7 SQL Server 自动化管理,8.8 数据的导入与导出,主要内容,SQL Server 2005的安全机制数据库管理系统相关知识 备份与恢复数据库SQL SERVER 2005的新特性 了解事务和锁 掌握SQL Server 2005数据导入与导出的方法,8.1安全性管理,8.1.1 认证模式与访问权限SQL Server 2005支持两种身份验证模式：Windows身份验证模式和混合身份验证模式 Windows认证模式：采用Windows认证
2、进程来确认用户的身份。 SQL Server认证模式:用户必须提供SQL Server管理员为其提供的登录名和登录密码，用户身份的验证由SQL Server自身完成。,8.1.2 用户管理用户账号与登录账号:一个合法的登录账号只表明该账号通过了Windows认证或SQL Server认证，但不能表明其可以对数据库数据和数据对象进行某种或某些操作。一个登录账号总是与一个或多个数据库用户账号相对应，这样才可以访问数据库。 用户账号操作: 添加用户 删除用户 授权,8.1.3 角色管理角色提供了一种把用户汇集成一个单元，以便进行许可管理的方法。一个角色包含了许多成员，这些成员都继承了角色所拥有的许可
3、。在SQL Server中，角色有两种主要类型：服务器角色和数据库角色。 1）Public角色 2）固定服务器角色 3）固定数据库角色 4）用户自定义的角色 5）应用程序角色,8.1.4 架构,架构是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。在 SQL Server 2005 中，架构独立于创建它们的数据库用户而存在。可以在不更改架构名称的情况下转让架构的所有权。 完全限定的对象名称现在包含四部分： server.database.schema.object。 默认架构：用于解析未使用其完全限定名称引用的对象的名称。,8.2数据库备份为了防止计算机出
4、现灾难事故而导致数据库被破坏，对数据库的备份工作是不可忽视的，要采取一定的备份策略来保证数据库的安全，一旦计算机发生事故时，能够采取及时的恢复操作。,备份就是制作数据库结构和数据的副本，以便在数据库遭到破坏的时候能够修复数据库。会造成数据库破坏的常见原因包括： 用户不正确的操作破坏了数据或数据被病毒破坏 硬件故障，如磁盘损坏等 遭遇自然灾害 遭遇盗窃等意外事故备份的内容不仅包括用户的数据库内容，还包括系统数据库的内容。,8.2.1 备份简介,8.2.2 备份许可及介质,可以备份数据库的角色成员有： 固定的服务器角色sysadmin 固定的数据库角色db_owner 固定的数据库角色db_bac
5、kupoperator 用户自定义角色，并且授权这些角色执行备份数据库的许可 备份介质： 磁盘 磁带 网络,8.2.3 备份类型,数据库备份包括完整备份和完整差异备份。完整备份包含数据库中的所有备份，并且可以用作完整差异备份的“基准备份”。完整差异备份仅记录自前一完整备份发生更改的数据扩展盘区数据。在SQL server中，主要的备份类型有： 完整备份 完整差异备份 文件和文件组备份 文件差异备份 事务日志备份,8.2.4执行备份,1完整备份,2. 完整差异备份 打开SQL Server Management Studio，在【对象资源管理器】中展开【数据库】，选择【教务管理】数据库，右键单击
6、鼠标，在弹出的快捷菜单上选择【任务】、【备份】选项。进入数据库备份窗口。在【备份数据库教务管理】窗口中，选择备份类型为【差异】，在下面的【目标】窗格中，默认为【备份到：磁盘】，指定备份的文件位置，例如指定为D:diffBackUp，然后单击【确定】按钮。 3使用backup命令备份 例8-6 使用backup命令完整备份 “教务管理”数据库。 Backup database 教务管理 To disk=f:MSSQLBACKUP教务管理备份.bak 例8-7 使用backup命令对 “教务管理”数据库进行差异备份。 Backup database 教务管理 To disk=f:MSSQLBACK
7、UP教务管理差异备份1.bak with differential,在对数据库进行备份时可以选择某种备份策略来进行备份。选择备份策略，一方面要基于数据库本身特点来考虑，另一方面要考虑性能。 数据库本身比较小的情况：若此数据库的数据是只读的或很少修改，则选用完整数据库备份策略是合适的。 频繁修改的数据库：完整数据库备份和事务日志备份相结合的策略是比较流行的备份选择。 需要很短的时间内恢复数据库：可以选择差异备份的策略。 海量数据的数据库：选用文件或文件组备份比较适合。,8.2.5 备份策略及性能考虑,8.3.1 恢复的概念及模式 恢复是在数据库遭遇破坏后从一个或多个备份中还原数据库。还原的操作是
8、在“恢复模式”下进行的。在SQL Server 2005数据库管理系统中有三种恢复模式： 简单恢复模式 完整恢复模式 大容量日志恢复模式,8.3 数据库恢复,8.3.2 恢复数据库注意事项准备工作：1、恢复的权限 2、检查系统 3、检查文件 4、限制用户对数据库的访问，并备份事务日志,1还原完整备份2还原完整差异备份： 还原完整差异备份的步骤与还原完整备份的步骤大体相同，只是在进入【还原数据库】的【常规】页窗口后，指定用于还原的备份集为先前进行了备份的差异备份集,8.3.3 在SQL Server Management Studio中恢复数据库,8.3.4 用T-SQL语句恢复系统数据库RES
9、TORE DATABASE语句用于恢复数据库，其语法格式为： RESTORE DATABASE database_name | database_name_var FROM ,.n WITH CHECKSUM | NO_CHECKSUM , CONTINUE_AFTER_ERROR | STOP_ON_ERROR , FILE = file_number | file_number , KEEP_REPLICATION , MEDIANAME = media_name | media_name_variable , MEDIAPASSWORD = mediapassword |mediapas
10、sword_variable , MOVE logical_file_name TO operating_system_file_name ,.n , PASSWORD = password | password_variable , RECOVERY | NORECOVERY | STANDBY = standby_file_name | standby_file_name_var ,8.3.5 建立自备份的维护计划SQL Server 2005的数据库维护计划可以指定作业运行的时间，多个作业可以根据一个计划运行，多个计划也可以应用到一个作业。根据作业运行的时间可将计划类型分为以下如图8.2
11、2所示的四种：,8.4.1 复制的概念将数据和数据库对象从一个数据库复制和分发到另一个数据库，然后在数据库间进行同步，以维持一致性。 1发布服务器 发布服务器是一种数据库实例，它通过复制向其他位置提供数据。 2分发服务器 分发服务器也是一种数据库实例，它起着存储区的作用，用于复制与一个或多个发布服务器相关联的特定数据。 3订阅服务器 订阅服务器是接收复制数据的数据库实例。 4项目 项目用于识别发布中包含的数据库对象。 5发布 发布是来自一个数据库的一个或多个项目的集合，将多个项目分组成一个发布更便于指定一组作为一个单元复制的、具有逻辑关系的数据库对象和数据。 6订阅 订阅是把发布副本传递到订阅
12、服务器的请求。,8.4 数据复制,8.4.2复制类型Microsoft SQL Server提供3种复制类型：1事务性复制2合并复制3快照复制,8.4.3复制代理复制使用许多称为代理的独立程序执行与跟踪更改和分发数据关联的任务。默认情况下，复制代理作为SQL Server代理安排的作业运行。 1SQL Server 代理SQL Server 代理保存并安排复制中使用的代理，并提供运行复制代理的简便方法。SQL Server 代理还控制和监视复制之外的操作。 2快照代理快照代理准备已发布表的架构和初始数据文件以及其他对象、存储快照文件并记录分发数据库中的同步信息。3日志读取器代理它将发布服务器上
13、的事务日志中标记为复制的事务移至分发数据库中。 4分发代理它将初始快照应用于订阅服务器，并将分发数据库中保存的事务移至订阅服务器。 5合并代理合并代理与合并复制一起使用。它将初始快照应用于订阅服务器，并移动和协调所发生的增量数据更改。 6队列读取器代理队列读取器代理与包含排队更新选项的事务性复制一起使用。该代理运行于分发服务器，并将订阅服务器上所做更改移回至发布服务器。,8.4.4 发布服务器与发布默认情况下，分发服务器与发布服务器是同一台服务器（本地发布服务器），也可以是与发布服务器不同的服务器（远程发布服务器）。创建本地发布服务器的配置步骤： 启动【新建发布向导】 选择要发布数据的数据库
14、选择发布类型 选择发布项目 设置筛选表行和快照代理，创建快照并设置快照运行时间 设置快照代理安全性 完成发布向导，创建本地发布,8.4.5 订阅服务器与订阅订阅是对发布中的数据和数据库对象的副本进行请求。订阅定义将接收哪个发布以及接收的时间和位置。创建本地订阅的步骤： 通过【订阅向导】生成本地订阅 选择发布服务器中的数据库和发布 选择发布代理位置 选择订阅服务器，指定订阅数据库 设置分发代理安全性，指定同步此订阅时，运行分发代理服务器的域或计算机账户 指定代理的同步计划 初始化订阅 创建订阅,8.5.1 事务及工作原理 1事务 事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是
15、一个不可分割的工作单位。 2事务特性 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持续性(Durability)。这四个特性简称为ACID特性。 3SQL Server通过记录事务日志保持事务的持久性。 数据库系统一般采用记录日志和恢复机制来保证事务的一致性；采用并发控制机制来保证多个事务的交叉运行不影响这些事务的原子性。 4事务的工作原理（COMMIT,ROLLBACK,UNDO,REDO）,8.5 事务,8.5.2执行事务及执行模式 1事务的执行应用程序主要通过指定事务启动和结束的时间来控制事务。 2事务的类型根据运行模式，SQL Serv
16、er 2005将事务分为4种类型：自动提交事务、显式事务、隐式事务和批处理事务。 1） 显式事务显式事务指每个事务通过API函数或通过发出Transact-SQL BEGIN TRANSACTION 语句来显式启动事务，以COMMIT或ROLLBACK语句显式结束。 2） 自动提交事务自动提交事务是指每条语句都是一个事务。 3） 隐式事务隐式事务是指在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。 4） 批范围的事务只适用于多个活动的结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显式或隐式事务将变成批范围的事务。,8.5
17、.3 编写事务控制事务用到的T-SQL语句: 1BEGIN TRANSACTION 语句 2COMMI T TRANSACTION语句 3ROLLBACK TRANSACTION 或 ROLLBACK WORK 语句例8-12 定义一个事务，将所有外聘教师的考评成绩定为良好，并提交该事务。 BEGIN TRANSACTION EX_teacher_grade UPDATE 考评 SET 得分情况=良好 WHERE 档案类型=外聘 GO COMMIT TRANSACTION GO,8.5.4 事务保存点保存点提供了一种机制，用于回滚部分事务。在创建保存点后，可以执行 ROLLBACK TRANS
18、ACTION savepoint_name 语句以回滚到保存点，而不是回滚到事务的起点。 语法格式为： SAVE TRAN | TRANSACTION savepoint_name | savepoint_variable ; ,8.5.5事务隔离级别隔离级别定义一个事务必须与其他事务所进行的资源或数据更改相隔离的程度。 Microsoft SQL Server 2005的隔离级别由低到高包括： 未提交读（隔离事务的最低级别，只能保证不读取物理上损坏的数据） 已提交读（数据库引擎的默认级别） 可重复读 可序列化（隔离事务的最高级别，事务之间完全隔离）Transact-SQL使用 SET TRA
19、NSACTION ISOLATION LEVEL 语句设置隔离级别,8.6.1 锁及其作用 当多个用户并发地存取数据库时会产生多个事务同时存取同一数据的情况，如果对并发操作不加控制就可能会存取和存储不正确的数据，破坏事务的一致性和数据库的致性。 由多用户并发事务执行所导致的数据不一致可能会导致下面几种情况： 1丢失修改 2不可重复读 3脏读 (Dirty Read) 锁是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。封锁是指一个事务在对某个数据对象操作之前，先向系统提出请求，对其加锁，在事务结束之后释放锁。在事务释放它的锁之前，其他事务不能更新此数据对象。,8.6 锁,8.6.2
20、 锁的对象与模式1锁的对象SQL Server具有多粒度锁定，允许一个事务锁定不同类型的资源。锁的对象包括下表所列的资源：,8.6.2 锁的对象与模式2锁模式 1） 共享锁 共享锁（S 锁）允许并行事务读取同一种资源。 2）排他锁 排他锁（X 锁）可以防止并发事务对资源进行访问。 3） 更新锁 更新锁（U 锁）可以防止常见的死锁。一次只有一个事务可以获得资源的更新锁（U 锁）。如果事务修改资源，则更新锁（U 锁）转换为排他锁（X 锁）。 4） 意向锁 数据库引擎使用意向锁来保护共享锁（S 锁）或排他锁（X 锁）放置在锁层次结构的底层资源上。 意向锁有两种用途： 防止其他事务以会使较低级别的锁无
21、效的方式修改较高级别资源。 提高数据库引擎在较高的粒度级别检测锁冲突的效率。,8.6.3 死锁问题死锁是有两个或以上的事务处于等待状态，每个事务都在等待另一个事务解除封锁，它才能继续执行下去，结果任何一个事务都无法执行，这种现象就是死锁。 下面两种情况下可能会出现死锁： 1两个事务同时锁定了两个单独的对象，并且第一个事务要求在另外一个事务锁定的对象上获得一锁； 2在一个数据库中有若干个长时间运行的事务执行并操作。 降低死锁的原则： 1按同一顺序访问对象 2避免事务中的用户交互 3保持事务简短并在一个批处理中 4使用低隔离级别,8.6.4 查看锁信息Microsoft SQL Server 20
22、05 提供的获取有关数据库引擎实例中的当前锁活动的信息的方法：使用 SQL Server Profiler，可以指定用来捕获有关跟踪中锁事件的信息的锁事件类别。在系统监视器中，可以从锁对象指定计数器来监视数据库引擎实例中的锁级别。 操作步骤： 进入SQL Server Profiler窗口 在SQL Server Profiler窗口中，选择【文件】菜单【新建跟踪】。 在【跟踪属性】对话框中设置跟踪，设置跟踪名称和使用模板，可以将跟踪指定到文件存储，也可以保存到指定的表。 完成跟踪的建立后，即可在SQL Server Profiler窗口中查处跟踪的事件。,8.7.1 作业管理 作业是一系列由
23、 SQL Server 代理按顺序执行的指定操作。一个作业可以执行各种类型的活动，作业可以运行重复或可计划的任务，然后它们可以通过生成警报来自动通知用户作业状态。必须启动SQL Server 代理作业才能自动运行。 创建作业的步骤： 1在【SQL Server 代理】选择【新建作业】 2设置作业名称 3在【新建作业步骤】窗口设置各项内容,8.7 SQL Server 自动化管理,8.7.2警报管理对事件的自动响应称为【警报】。可以针对一个或多个事件定义警报，指定希望 SQL Server 代理如何响应发生的这些事件。 1定义警报2创建警报,8.7.3 通知在发生警报时可以使用下列一种或多种方法
24、通知操作员：电子邮件通知、寻呼通知、net send 通知操作员。SQL Server 代理可以使用数据库邮件或 SQL Mail 功能发送电子邮件。8.7.4 维护计划计划管理作业就是定义使作业开始运行的条件。可以计划任何类型的作业。多个作业可以使用同一个作业计划。用户可以将计划附加到作业，也可以从作业分离计划。,8.8.1 导入导出向导 SQL Server 2005提供了大容量地导入和导出数据的功能。“导出”是指将数据从SQL Server表复制到数据文件，“导入”是指将数据从数据文件加载到SQL Server表。 例8-14 把教务管理数据库中的数据导出到Excel文件中。例8-15 从Microsoft Access数据库导入数据到SQL Server中。,8.8 数据的导入与导出,8.8.2数据的导入与导出及数据类型转换1导出数据的要求：导出文件、权限、记录顺序2导入数据的要求：格式、数据类型兼容性等3数据类型转换：数据不兼容时的自动数据转换处理,
