SQLServer数据库技术概述

1、SQLServer 数据库技术概述目 录Contents1.简介架构和权限备份和还原高可用性01 简介01SQL server发展历程1988年 微软、Sybase和Ashton-Tate合作，在Sybase的基础上生产出了在OS/2操作系统上使用的SQL Server 1.0；1995年 SQL Server 6.0发布,是一款完全由微软独立完成的产品；2000年 SQL server 2000发布（8.0），使用方便，伸缩性好，集成度高；2005年 SQL server 2005发布（9.0），全面的数据库平台，加入了分析报表和集成服务等功能；2008年 SQL server 2008发布

2、（10.0），增加了许多新特性并改进了关键性功能；2008年 SQL server 2008 R2发布（10.5），在2008的基础上强化了业务模块的规模，增强了开发能力等； 2012年 SQL server 2012发布（11.0），添加了always on等新功能；2017年 SQL server 2017发布（14.0），首个支持windows、Linux、macOS、Docker的SQL server版本；01SQL server2019新特性数据虚拟化PloyBase（hadoop、Azure blob、oracle 、Teradata、 mongodb等）大数据集群（ HDFS 存

3、储池）智能数据库（内存数据库，近似处理查询，智能查询（内存）高可用性更新开发人员图型化支持01SQL server 各版本比较Enterprise Edition 企业版Standard Edition 标准版Workgroup Edition 工作组版Developer Edition 开发者版Express Edition 学习版01SQL server 发展形势02.1 架构02SQL server服务器结构SQL Server服务器可以看成是由实例及数据库构成。 实例包括SQL Server占用的内存及后台进程。SQL Server服务器=一个SQL Server实例+多个数据库SQL

4、 Server数据相关文件：数据文件和重做日志文件 重做日志文件：包含事务日志数据以及undo数据 没有控制文件、初始化参数文件、口令文件02服务器架构层次Sql server层面： Sql server登录名， 服务器角色角色数据库层面： 数据库用户， 角色，证书，schema，Schema层面： Table，view， function， procedure， queue，type02Sql server的内存结构LazyWriter与CheckPoint进程02一条sql的生命周期02一条sql的生命周期02SQL server 系统数据库Master: 保存整个服务器的系统信息Msdb

5、: 当配置了数据库的自动化管理时，msdb数据库 保存自动化作业的配置信息。Tempdb: 用于保存临时表以及数据库运行过程中的排 序或散列操作产生的临时数据。Model: 是数据库的模板，当用户创建新的数据库时， SQLServer复制model数据库的结构作为新数据库的开始Resource: 保存sys架构中的数据，主要是数据字典数据。02.2 权限02Sql server有关权限数据库服务器登录名数据库服务器角色数据库用户数据库角色02数据库服务器登录名03数据库服务器角色Sysadmin： Serveradmin ：可以在服务器上执行任何活动。可以更改服务器范围的配置选项和关闭服务器。

6、Securityadmin： 可以管理登录名及其属性。Processadmin： 可以终止在SQL Server 实例中运行的进程。Setupadmin： Bulkadmin ： Diskadmin ： Dbcreator： Public：可以使用 Transact-SQL 语句添加和删除链接服务器。 可以运行BULK INSERT 语句。用于管理磁盘文件。可以创建、更改、删除和还原任何数据库。每个SQL Server 登录名都属于public 服务器角色。02数据库用户02数据库角色db_owner可以执行数据库的所有配置和维护活动，还可以删除 SQL Server中的数据库。db_secu

7、rityadmin可以仅修改自定义角色的角色成员资格和管理权限。db_accessadmin可以为Windows 登录名、Windows 组和 SQL Server 登录 名添加或删除数据库访问权限。db_backupoperator可以备份数据库。db_ddladmin可以在数据库中运行任何数据定义语言 (DDL) 命令。db_datawriter可以在所有用户表中添加、删除或更改数据。db_datareader可以从所有用户表中读取所有数据。db_denydatawriter不能添加、修改或删除数据库内用户表中的任何数据。db_denydatareader不能读取数据库内用户表中的任何数据

8、。02数据库角色03 备份和还原04备份恢复为什么要备份？提高数据的安全性宕机之后恢复的高可用性备份的对象有哪些？数据库 文件 磁盘04备份恢复什么时候需要备份系统数据库？修改master数据库之后master数据库记录了系统的所有系统信息。包括实例范围的元数据（如登陆账户）、端点、链接服务器和系统配置设置。还记录了其他数据库是否存在 及这些数据库文件的位置。还记录了sql server 的初始化信息。修改model数据库之后model数据库用作在实例上创建所有数据库的模板。每次启动sql server 时都 会创建tempdb，所以model数据库必须始终存在于sql server 系统中。

9、如果修改model数据库，之后创建的所有的数据库都将继承这些修改。修改msdb数据库之后msdb数据库包含sql server 代理用来计划警报和作业的信息。04备份恢复什么时候需要备份用户数据库？创建数据库之后创建索引之后清理事务日志之后执行大容量的数据操作之后04备份恢复数据库的恢复模式简单模式（Simple） Checkpoint with truncate log 非紧要最彻底的收缩日志方式完 整 模 式 （Full） Checkpoint without truncate log 紧要大容量日志恢复（Bulk-logged ） Bulk load operations (bcp an

10、d BULK INSERT) SELECT INTOCreate/drop/rebuild index04备份恢复sql server 提供如下四种数据备份类型完整备份差异备份日志备份文件和文件组备份04备份恢复sql server 备份加密1、在使用 BACKUP 语句时，添加 PASSWORD 参数【此方法适用于sql server 2012 以前的版本（不包含 2012）】2、对数据库启用透明数据加密（TDE）【此方法适用于 sql server2008 及以后的版本（含2008）】3、直接对备份进行加密【此方法适用于sql server 2014 及以后的版 本】04备份恢复sql s

11、erver 备份选项04备份恢复sql server 备份的一些限制1、无法备份离线数据2、完整备份过程中的限制在完整备份的过程中，不允许执行下列操作：创建或删除数据库文件在收缩数据库操作过程中截断文件04备份恢复sql server 还原参数04备份恢复系统数据库还原master数据库损坏时 重新初始化还原master数据库还原msdb、model数据库master数据库没损坏还原master数据库还原msdb、model数据库将master所有对象生成脚本、目标端执行04备份恢复-数据库多的时候采用游标获取用户数据库还原-1：完整备份declare dbname varchar(100)

12、declare sql nvarchar(max)set dbname = DataBaseName set sql = -+dbname+_fullBACKUP DATABASE +dbname+TO DISK = D:DBBackup+dbname+_full.bakWITH NOFORMAT, NOINIT, NAME = +dbname+-完整数据库备份, SKIP, NOREWIND, NOUNLOAD, STATS = 10GOprint sql04备份恢复用户数据库还原-2：完整备份还原declare dbname varchar(100) declare sql nvarcha

13、r(max)set dbname = DataBaseName set sql = -RESTORE +dbname+_full RESTORE DATABASE +dbname+FROM DISK = D:DBBackup+dbname+_full.bak WITH FILE = 1,MOVE NDataBase_Name TO ND:DataBase+dbname+.mdf,MOVE NDataBase_Name_log TO ND:DataBase+dbname+_log.ldf, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10GOprint sql0

14、4备份恢复-3：差异备份declare dbname varchar(100) declare sql nvarchar(max)set dbname = DataBaseName set sql = -+dbname+_diffBACKUP DATABASE +dbname+TO DISK = ND:DBBackup+dbname+_diff.bakWITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N+dbname+-差异数据库备份, SKIP, NOREWIND, NOUNLOAD, STATS = 10GOprint sql04备份恢复用户数据库还

15、原-4：差异备份还原declare dbname varchar(100) declare sql nvarchar(max)set dbname = DataBaseName set sql = -RESTORE +dbname+_fullRESTORE DATABASE +dbname+FROM DISK = D:DBBackup+dbname+_diff.bak WITH FILE = 1, NOUNLOAD, STATS = 10GOprint sql04 高可用性04高可用性概述故障转移群集镜像（有见证服务器和无见证） 3.Alwayson集群（域环境下和无域环境）04故障转移群集应用场景：硬件故障转移。服务故障转移。人工故障或各种其它原因等。优点：根据业务进行灵活的群集拓扑结构 快速且自动故障转移缺点:群集非活跃节点浪费硬件资源群集节点间共用的共享存储，仍然存在潜在的单点故障隐患。 没有负载能力，不是提升性能的技术。要求:基于windows故障转移04镜像应用场景：要求高服务可用性。要求实现自动故障转移。 确保数据的完整。优点：根据业务可实现同步和异步镜像。 快速