MS SQL Server - 快速指南

---

MS SQL Server - 概述

本章介绍 SQL Server，讨论其用法、优点、版本和组件。

什么是 SQL Server？

• 它是一个由微软开发的软件，它是根据RDBMS的规范实现的。

• 它也是一个 ORDBMS。

• 它依赖于平台。

• 它是基于 GUI 和命令的软件。

• 它支持SQL（SEQUEL）语言，SQL（SEQUEL）语言是IBM产品、非过程化、通用数据库、不区分大小写的语言。

SQL Server 的使用

• 创建数据库。
• 维护数据库。
• 通过 SQL Server Analysis Services (SSAS) 分析数据。
• 通过 SQL Server Reporting Services (SSRS) 生成报告。
• 通过 SQL Server Integration Services (SSIS) 执行 ETL 操作。

SQL Server 的版本

SQL Server 组件

SQL Server 采用客户端-服务器架构，因此它支持两种类型的组件：(a) 工作站和 (b) 服务器。

• 工作站组件安装在每个设备/SQL Server 操作员的计算机上。这些只是与服务器组件交互的接口。示例：SSMS、SSCM、Profiler、BIDS、SQLEM 等。

• 服务器组件安装在集中式服务器中。这些都是服务。示例：SQL Server、SQL Server Agent、SSIS、SSAS、SSRS、SQL 浏览器、SQL Server 全文搜索等。

SQL Server 实例

• 实例是 SQL Server 的安装。
• 实例是同一软件的精确副本。
• 如果我们安装“n”次，那么将创建“n”个实例。
• SQL Server 中有两种类型的实例：a) 默认 b) 命名。
• 一台服务器仅支持一个默认实例。
• 一台服务器将支持多个命名实例。
• 默认实例将以服务器名称作为实例名称。
• 默认实例服务名称是 MSSQLSERVER。
• 2000版本将支持16个实例。
• 2005年及以后的版本将支持50个实例。

实例的优点

• 在一台机器上安装不同版本。
• 以降低成本。
• 分别维护生产、开发和测试环境。
• 减少临时数据库问题。
• 分离安全权限。
• 维护备用服务器。

MS SQL Server - 版本

SQL Server 有多种版本。本章列出了多个版本及其特点。

• 企业版- 这是具有完整功能集的高端版本。

• 标准- 当不需要高级功能时，其功能少于企业版。

• 工作组- 这适用于较大公司的远程办公室。

• Web - 这是为 Web 应用程序设计的。

• 开发人员- 这与企业版类似，但仅授权给一名用户进行开发、测试和演示。它可以轻松升级到企业版，无需重新安装。

• Express - 这是免费的入门级数据库。它只能使用1个CPU和1GB内存，数据库的最大大小为10GB。

• 紧凑- 这是用于移动应用程序开发的免费嵌入式数据库。数据库的最大大小为 4 GB。

• 数据中心- 新的 SQL Server 2008 R2 的主要变化是数据中心版本。Datacenter 版本没有内存限制，并支持超过 25 个实例。

• 商业智能- 商业智能版是 SQL Server 2012 中的新推出。此版本包含标准版中的所有功能，并支持 Power View 和 PowerPivot 等高级 BI 功能，但缺乏对 AlwaysOn 可用性组等高级可用性功能的支持以及其他在线操作。

• 企业评估- SQL Server 评估版是获取功能齐全且免费的 SQL Server 实例以学习和开发解决方案的好方法。此版本的内置有效期为自安装之日起 6 个月。

MS SQL Server - 安装

SQL Server 支持两种类型的安装 -

• 独立式
• 基于集群

支票

• 检查服务器的 RDP 访问。
• 检查服务器的操作系统位、IP、域。
• 检查您的帐户是否在管理员组中以运行 setup.exe 文件。
• 软件位置。

要求

• 哪个版本、版本、SP 和修补程序（如果有）。
• 数据库引擎、代理、SSAS、SSIS、SSRS（如果有）的服务帐户。
• 命名实例名称（如果有）。
• 二进制文件、系统、用户数据库的位置。
• 认证方式。
• 排序规则设置。
• 功能列表。

2005年的先决条件

• 设置支持文件。
• .net框架2.0。
• SQL Server 本机客户端。

2008&2008R2 的先决条件

• 设置支持文件。
• .net框架3.5 SP1。
• SQL Server 本机客户端。
• Windows 安装程序 4.5/更高版本。

2012年和2014年的先决条件

• 设置支持文件。
• .net框架4.0。
• SQL Server 本机客户端。
• Windows 安装程序 4.5/更高版本。
• Windows PowerShell 2.0。

安装步骤

步骤 1 - 从http://www.microsoft.com/download/en/details.aspx?id=29066下载评估版

下载软件后，根据您的下载（32 位或 64 位）选项，将提供以下文件。

ENU\x86\SQLFULL_x86_ENU_Core.box

ENU\x86\SQLFULL_x86_ENU_Install.exe

ENU\x86\SQLFULL_x86_ENU_Lang.box

或者

ENU\x86\SQLFULL_x64_ENU_Core.box

ENU\x86\SQLFULL_x64_ENU_Install.exe

ENU\x86\SQLFULL_x64_ENU_Lang.box

注- X86（32 位）和 X64（64 位）

步骤 2 - 双击“SQLFULL_x86_ENU_Install.exe”或“SQLFULL_x64_ENU_Install.exe”，它将分别在“SQLFULL_x86_ENU”或“SQLFULL_x86_ENU”文件夹中提取安装所需的文件。

步骤 3 - 单击“SQLFULL_x86_ENU”或“SQLFULL_x64_ENU_Install.exe”文件夹，然后双击“SETUP”应用程序。

为了便于理解，这里我们使用了 SQLFULL_x64_ENU_Install.exe 软件。

步骤 4 - 单击“设置”应用程序后，将打开以下屏幕。

步骤 5 - 单击上述屏幕左侧的安装。

步骤 6 - 单击上面屏幕上右侧的第一个选项。将打开以下屏幕。

步骤 7 - 单击“确定”，弹出以下屏幕。

步骤 8 - 单击下一步以获取以下屏幕。

步骤 9 - 确保检查产品密钥选择，然后单击下一步。

步骤 10 - 选择复选框以接受许可证选项，然后单击下一步。

步骤 11 - 选择 SQL Server 功能安装选项，然后单击下一步。

步骤 12 - 选择数据库引擎服务复选框，然后单击下一步。

步骤 13 - 输入命名实例（这里我使用 TestInstance），然后单击“下一步”。

步骤 14 - 单击上面屏幕上的下一步，然后出现以下屏幕。

步骤 15 - 为上面列出的服务选择服务帐户名称和启动类型，然后单击排序。

步骤 16 - 确保选中正确的排序规则选择，然后单击下一步。

步骤 17 - 确保选中身份验证模式选择和管理员，然后单击数据目录。

步骤 18 - 确保选择上述目录位置，然后单击下一步。出现以下屏幕。

步骤 19 - 单击上面屏幕上的“下一步”。

步骤 20 - 单击上面屏幕上的“下一步”以获取以下屏幕。

步骤 21 - 确保正确检查上述选择并单击安装。

如上图所示，安装成功。单击“关闭”完成。

MS SQL Server - 架构

为了便于理解，我们将 SQL Server 的体系结构分为以下部分：

• 通用架构
• 内存架构
• 数据文件架构
• 日志文件架构

总体架构

客户端- 请求发起的地方。

查询- SQL 查询是高级语言。

逻辑单元- 关键字、表达式和运算符等。

N/W 数据包- 网络相关代码。

协议- 在 SQL Server 中，我们有 4 个协议。

• 共享内存（用于本地连接和故障排除目的）。

• 命名管道（用于 LAN 连接中的连接）。

• TCP/IP（适用于 WAN 连接中的连接）。

• VIA-虚拟接口适配器（需要供应商设置特殊硬件，并且从 SQL 2012 版本开始已弃用）。

服务器- SQL 服务安装和数据库驻留的地方。

关系引擎- 这是真正执行的地方。它包含查询解析器、查询优化器和查询执行器。

查询解析器（命令解析器）和编译器（翻译器） - 这将检查查询的语法并将查询转换为机器语言。

查询优化器- 它将通过将查询、统计信息和 Algebrizer 树作为输入来准备执行计划作为输出。

执行计划- 它就像一个路线图，其中包含作为查询执行的一部分要执行的所有步骤的顺序。

查询执行器- 这是在执行计划的帮助下逐步执行查询的地方，并且还将联系存储引擎。

存储引擎- 负责存储和检索存储系统（磁盘、SAN 等）上的数据、数据操作、锁定和管理事务。

SQL OS - 它位于主机（Windows 操作系统）和 SQL Server 之间。数据库引擎上执行的所有活动均由 SQL 操作系统负责。SQL OS提供各种操作系统服务，例如内存管理处理缓冲池、日志缓冲区以及使用阻塞和锁定结构的死锁检测。

检查点进程- 检查点是一个内部进程，它将所有脏页（修改页）从缓冲区高速缓存写入物理磁盘。除此之外，它还将日志记录从日志缓冲区写入物理文件。将脏页从缓冲区高速缓存写入数据文件也称为脏页强化。

它是一个专用进程，由 SQL Server 按特定时间间隔自动运行。SQL Server 分别为每个数据库运行检查点进程。检查点有助于减少 SQL Server 在意外关闭或系统崩溃\故障时的恢复时间。

SQL Server 中的检查点

在 SQL Server 2012 中有四种类型的检查点-

• 自动- 这是最常见的检查点，它作为后台进程运行，以确保 SQL Server 数据库可以在恢复间隔 - 服务器配置选项定义的时间限制内恢复。

• 间接- 这是 SQL Server 2012 中的新增功能。它也在后台运行，但为了满足已配置该选项的特定数据库的用户指定的目标恢复时间。一旦选择了给定数据库的 Target_Recovery_Time，这将覆盖为服务器指定的恢复间隔，并避免在此类数据库上进行自动检查点。

• 手动- 此语句的运行方式与任何其他 T-SQL 语句一样，一旦您发出检查点命令，它将运行直至完成。手动检查点仅针对当前数据库运行。您还可以指定可选的 Checkpoint_Duration - 此持续时间指定您希望检查点完成的时间。

• 内部- 作为用户，您无法控制内部检查点。针对具体操作发布，例如

  • 关闭会对所有数据库启动检查点操作，除非关闭不干净（使用 nowait 关闭）。

  • 如果恢复模式从完整\批量日志更改为简单。

  • 同时备份数据库。

  • 如果您的数据库处于简单恢复模式，则检查点进程会在日志达到 70% 已满时自动执行，或者根据服务器选项恢复间隔自动执行。

  • 用于添加或删除数据\日志文件的更改数据库命令也会启动检查点。

  • 当数据库的恢复模型被批量记录并执行最小记录操作时，也会发生检查点。

  • 数据库快照创建。

• 惰性写入器进程- 惰性写入器会出于完全不同的原因将脏页推送到磁盘，因为它需要释放缓冲池中的内存。当 SQL Server 面临内存压力时，就会发生这种情况。据我所知，这是由内部流程控制的，没有任何设置。

SQL Server 不断监视内存使用情况以评估资源争用（或可用性）；它的工作是确保始终有一定数量的可用空间。作为此过程的一部分，当它注意到任何此类资源争用时，它会触发 Lazy Writer 通过将脏页写入磁盘来释放内存中的一些页。它采用最近最少使用（LRU）算法来决定将哪些页面刷新到磁盘。

如果 Lazy Writer 始终处于活动状态，则可能表明存在内存瓶颈。

内存架构

以下是内存架构的一些显着特征。

• 所有数据库软件的主要设计目标之一是最大限度地减少磁盘 I/O，因为磁盘读取和写入是资源最密集的操作之一。

• Windows中的内存可以通过虚拟地址空间来调用，由内核模式（OS模式）和用户模式（如SQL Server的应用程序）共享。

• SQL Server“用户地址空间”分为两个区域：MemToLeave 和缓冲池。

• MemToLeave (MTL) 和缓冲池 (BPool) 的大小由 SQL Server 在启动期间确定。

• 缓冲区管理是实现 I/O 高效率的关键组件。缓冲区管理组件由两种机制组成：用于访问和更新数据库页的缓冲区管理器，以及用于减少数据库文件 I/O 的缓冲池。

• 缓冲池进一步分为多个部分。最重要的是缓冲区高速缓存（也称为数据高速缓存）和过程高速缓存。缓冲区高速缓存将数据页保存在内存中，以便可以从高速缓存中检索经常访问的数据。另一种方法是从磁盘读取数据页。从缓存中读取数据页可以最大限度地减少所需 I/O 操作的数量，从而优化性能，而 I/O 操作本质上比从内存中检索数据要慢。

• 过程缓存保留存储过程和查询执行计划，以最大限度地减少必须生成查询计划的次数。您可以使用 DBCC PROCCACHE 语句查找有关过程高速缓存中的大小和活动的信息。

缓冲池的其他部分包括 -

• 系统级数据结构- 保存有关数据库和锁的 SQL Server 实例级数据。

• 日志缓存- 保留用于读取和写入事务日志页。

• 连接上下文- 与实例的每个连接都有一小块内存区域来记录连接的当前状态。这些信息包括存储过程和用户​​定义的函数参数、光标位置等。

• 堆栈空间- Windows 为 SQL Server 启动的每个线程分配堆栈空间。

数据文件架构

数据文件架构具有以下组件 -

文件组

出于分配和管理目的，可以将数据库文件分组到文件组中。任何文件都不能属于多个文件组的成员。日志文件绝不是文件组的一部分。日志空间与数据空间分开管理。

SQL Server 中有两种类型的文件组：主文件组和用户定义的文件组。主文件组包含主数据文件和未专门分配给另一个文件组的任何其他文件。系统表的所有页都分配在主文件组中。用户定义的文件组是在createdatabase或alterdatabase语句中使用filegroup关键字指定的任何文件组。

每个数据库中的一个文件组作为默认文件组运行。当 SQL Server 将页分配给创建时未指定文件组的表或索引时，将从默认文件组中分配页。要将默认文件组从一个文件组切换到另一个文件组，它应该具有 db_owner 固定 db 角色。

默认情况下，主文件组是默认文件组。用户应具有 db_owner 固定数据库角色才能单独备份文件和文件组。

文件

数据库具有三种类型的文件 - 主数据文件、辅助数据文件和日志文件。主数据文件是数据库的起点，指向数据库中的其他文件。

每个数据库都有一个主数据文件。我们可以为主数据文件提供任何扩展名，但建议的扩展名是.mdf。辅助数据文件是该数据库中除主数据文件之外的文件。某些数据库可能有多个辅助数据文件。某些数据库可能没有单个辅助数据文件。辅助数据文件的推荐扩展名是.ndf。

日志文件保存用于恢复数据库的所有日志信息。数据库必须至少有一个日志文件。我们可以为一个数据库拥有多个日志文件。日志文件的推荐扩展名为.ldf。

数据库中所有文件的位置都记录在主数据库和数据库的主文件中。大多数时候，数据库引擎使用主数据库中的文件位置。

文件有两个名称 - 逻辑名称和物理名称。逻辑名称用于在所有 T-SQL 语句中引用该文件。物理名称是OS_file_name，它必须遵循OS的规则。数据和日志文件可以放置在 FAT 或 NTFS 文件系统上，但不能放置在压缩文件系统上。一个数据库中最多可以有 32,767 个文件。

范围

区是为表和索引分配空间的基本单位。一个范围是 8 个连续页或 64KB。SQL Server 有两种类型的扩展区 - 统一扩展区和混合扩展区。统一范围仅由单个对象组成。混合区最多由八个对象共享。

页数

它是 MS SQL Server 中数据存储的基本单位。页面大小为8KB。每个页面的开头是 96 字节的标头，用于存储系统信息，例如页面类型、页面上的可用空间量以及拥有该页面的对象的对象 ID。SQL Server中有9种类型的数据页。

• 数据- 包含除文本、ntext 和图像数据之外的所有数据的数据行。

• 索引- 索引条目。

• Text\Image - 文本、图像和 ntext 数据。

• GAM - 有关分配范围的信息。

• SGAM - 有关系统级别分配的范围的信息。

• 页面可用空间 (PFS) - 有关页面上可用空间的信息。

• 索引分配映射 (IAM) - 有关表或索引使用的范围的信息。

• 批量更改映射 (BCM) - 有关自上次备份日志语句以来批量操作修改的范围的信息。

• 差异更改映射 (DCM) - 有关自上次备份数据库语句以来已更改的范围的信息。

日志文件架构

SQL Server 事务日志在逻辑上运行就像事务日志是一串日志记录一样。每条日志记录均由日志序列号 (LSN) 标识。每个日志记录都包含其所属事务的 ID。

数据修改的日志记录要么记录所执行的逻辑操作，要么记录修改数据的前后图像。前像是执行操作之前数据的副本；后像是执行操作后数据的副本。

恢复操作的步骤取决于日志记录的类型 -

• 记录逻辑操作。
  • 要向前滚动逻辑操作，请再次执行该操作。
  • 为了回滚逻辑操作，执行相反的逻辑操作。
• 记录图像之前和之后。
  • 要向前滚动操作，将应用后像。
  • 要回滚操作，将应用之前的图像。

不同类型的操作都会记录在事务日志中。这些操作包括 -

• 每笔交易的开始和结束。

• 每次数据修改（插入、更新或删除）。这包括系统存储过程或数据定义语言 (DDL) 语句对任何表（包括系统表）所做的更改。

• 每个范围和页面分配或取消分配。

• 创建或删除表或索引。

回滚操作也会被记录。每个事务在事务日志上保留空间，以确保存在足够的日志空间来支持由显式回滚语句或遇到错误引起的回滚。当事务完成时，该保留空间将被释放。

日志文件中从成功数据库范围回滚必须存在的第一条日志记录到最后写入的日志记录的部分称为日志的活动部分或活动日志。这是完全恢复数据库所需的日志部分。活动日志的任何部分都不能被截断。第一条日志记录的 LSN 称为最小恢复 LSN (Min LSN)。

SQL Server 数据库引擎在内部将每个物理日志文件划分为多个虚拟日志文件。虚拟日志文件没有固定的大小，并且物理日志文件的虚拟日志文件的数量也没有固定。

数据库引擎在创建或扩展日志文件时动态选择虚拟日志文件的大小。数据库引擎尝试维护少量虚拟文件。管理员无法配置或设置虚拟日志文件的大小或数量。虚拟日志文件影响系统性能的唯一情况是物理日志文件由较小的大小和 Growth_increment 值定义。

size 值是日志文件的初始大小，growth_increment 值是每次需要新空间时添加到文件的空间量。如果日志文件由于许多小增量而增长到很大，则它们将具有许多虚拟日志文件。这会减慢数据库启动以及日志备份和恢复操作的速度。

我们建议您为日志文件分配一个接近最终所需大小的大小值，并且还具有一个相对较大的growth_increment值。SQL Server 使用预写日志 (WAL)，它保证在将关联的日志记录写入磁盘之前不会将任何数据修改写入磁盘。这维护了事务的 ACID 属性。

MS SQL Server-管理工作室

SQL Server Management Studio是一个工作站组件\客户端工具，如果我们在安装步骤中选择工作站组件，就会安装该工具。这允许您从图形界面连接和管理 SQL Server，而不必使用命令行。

为了连接到 SQL Server 的远程实例，您将需要此软件或类似的软件。它由管理员、开发人员、测试人员等使用。

以下方法用于打开SQL Server Management Studio。

第一种方法

开始 → 所有程序 → MS SQL Server 2012 → SQL Server Management Studio

第二种方法

转到“运行”并输入 SQLWB（适用于 2005 版本）SSMS（适用于 2008 及更高版本）。然后单击输入。

使用上述任一方法都将打开 SQL Server Management Studio，如以下快照所示。

MS SQL Server - 登录数据库

登录名是用于访问 SQL Server 的简单凭据。例如，您在登录 Windows 时提供用户名和密码，甚至提供您的电子邮件帐户。此用户名和密码构成凭据。因此，凭据只是用户名和密码。

SQL Server 允许四种类型的登录 -

• 基于 Windows 凭据的登录。
• 特定于 SQL Server 的登录名。
• 映射到证书的登录名。
• 映射到非对称密钥的登录名。

在本教程中，我们对基于 Windows 凭据的登录和特定于 SQL Server 的登录感兴趣。

基于 Windows 凭据的登录允许您使用 Windows 用户名和密码登录 SQL Server。如果您需要创建自己的凭据（用户名和密码），您可以创建特定于 SQL Server 的登录名。

要创建、更改或删除 SQL Server 登录名，您可以采用以下两种方法之一 -

• 使用 SQL Server Management Studio。
• 使用 T-SQL 语句。

以下方法用于创建登录 -

第一种方法 – 使用 SQL Server Management Studio

步骤 1 - 连接到 SQL Server 实例后，展开登录文件夹，如以下快照所示。

步骤 2 - 右键单击​​“登录”，然后单击“新建登录”，将打开以下屏幕。

步骤 3 - 填写登录名、密码和确认密码列，如上屏幕所示，然后单击确定。

将创建登录名，如下图所示。

第二种方法 – 使用 T-SQL 脚本

Create login yourloginname with password='yourpassword'

要使用 TestLogin 和密码“P@ssword”创建登录名，请运行以下查询。

Create login TestLogin with password='P@ssword'

MS SQL Server - 创建数据库

数据库是表、视图、存储过程、函数、触发器等对象的集合。

在 MS SQL Server 中，有两种类型的数据库可用。

• 系统数据库
• 用户数据库

系统数据库

当我们安装 MS SQL Server 时，系统数据库会自动创建。以下是系统数据库列表 -

• 掌握
• 模型
• 微软数据库
• 临时数据库
• 资源（2005年版本引入）
• 分发（仅用于复制功能）

用户数据库

用户数据库由用户（有权创建数据库的管理员、开发人员和测试人员）创建。

以下方法用于创建用户数据库。

方法 1 – 使用 T-SQL 脚本或恢复数据库

以下是在 MS SQL Server 中创建数据库的基本语法。

Create database <yourdatabasename>

或者

Restore Database <Your database name> from disk = '<Backup file location + file name>

例子

要创建名为“Testdb”的数据库，请运行以下查询。

Create database Testdb

或者

Restore database Testdb from disk = 'D:\Backup\Testdb_full_backup.bak'

注意- D:\backup 是备份文件的位置，Testdb_full_backup.bak 是备份文件名

方法 2 – 使用 SQL Server Management Studio

连接到 SQL Server 实例并右键单击数据库文件夹。单击新数据库，将出现以下屏幕。

在数据库名称字段中输入您的数据库名称（示例：创建名为“Testdb”的数据库），然后单击“确定”。将创建 Testdb 数据库，如以下快照所示。

MS SQL Server - 选择数据库

在继续使用以下任何方法之前，请根据您的操作选择数据库。

方法 1 – 使用 SQL Server Management Studio

例子

要运行查询来选择名为“msdb”的数据库上的备份历史记录，请选择 msdb 数据库，如以下快照所示。

方法 2 – 使用 T-SQL 脚本

Use <your database name>

例子

要运行查询来选择名为“msdb”的数据库上的备份历史记录，请通过执行以下查询来选择 msdb 数据库。

Exec use msdb

查询将打开 msdb 数据库。您可以执行以下查询来选择备份历史记录。

Select * from backupset

MS SQL Server - 删除数据库

要从 MS SQL Server 中删除数据库，请使用 drop database 命令。为此可以使用以下两种方法。

方法 1 – 使用 T-SQL 脚本

以下是从 MS SQL Server 删除数据库的基本语法。

Drop database <your database name>

例子

要删除数据库名称“Testdb”，请运行以下查询。

Drop database Testdb

方法 2 – 使用 MS SQL Server Management Studio

连接到 SQL Server 并右键单击要删除的数据库。单击删除命令，将出现以下屏幕。

单击“确定”从 MS SQL Server 中删除数据库（在本例中，名称为 Testdb，如上图所示）。

MS SQL Server - 创建备份

备份是数据/数据库等的副本。备份MS SQL Server 数据库对于保护数据至关重要。MS SQL Server 备份主要分为三种类型：完整备份或数据库备份、差异备份或增量备份以及事务日志备份或日志备份。

可以使用以下两种方法之一来备份数据库。

方法 1 – 使用 T-SQL

完整型

Backup database <Your database name> to disk = '<Backup file location + file name>'

差动型

Backup database <Your database name> to 
   disk = '<Backup file location + file name>' with differential

日志类型

Backup log <Your database name> to disk = '<Backup file location + file name>'

例子

以下命令用于将名为“TestDB”的数据库完整备份到位置“D:\”，备份文件名为“TestDB_Full.bak”

Backup database TestDB to disk = 'D:\TestDB_Full.bak'

以下命令用于将名为“TestDB”的数据库差异备份到位置“D:\”，备份文件名为“TestDB_diff.bak”

Backup database TestDB to disk = 'D:\TestDB_diff.bak' with differential

以下命令用于将名为“TestDB”的数据库备份到位置“D:\”，备份文件名为“TestDB_log.trn”

Backup log TestDB to disk = 'D:\TestDB_log.trn'

方法 2 – 使用 SSMS (SQL SERVER Management Studio)

步骤 1 - 连接到名为“TESTINSTANCE”的数据库实例并展开数据库文件夹，如以下快照所示。

步骤 2 - 右键单击​​“TestDB”数据库并选择任务。单击“备份”，将出现以下屏幕。

步骤 3 - 选择备份类型 (Full\diff\log) 并确保检查将创建备份文件的目标路径。选择左上角的选项以查看以下屏幕。

步骤 4 - 单击“确定”创建“TestDB”数据库完整备份，如以下快照所示。

MS SQL Server - 恢复数据库

恢复是从备份复制数据并将记录的事务应用于数据的过程。恢复是您对备份执行的操作。获取备份文件并将其恢复为数据库。

可以使用以下两种方法之一来完成“恢复数据库”选项。

方法 1 – T-SQL

句法

Restore database <Your database name> from disk = '<Backup file location + file name>'

例子

以下命令用于恢复名为“TestDB”的数据库，备份文件名为“TestDB_Full.bak”，如果您要覆盖现有数据库，该数据库可在“D:\”位置找到。

Restore database TestDB from disk = ' D:\TestDB_Full.bak' with replace

如果您使用此恢复命令创建新数据库，并且目标服务器中没有类似的数据路径、日志文件，则使用移动选项，如以下命令。

确保 D:\Data 路径存在，如以下数据和日志文件命令中使用的那样。

RESTORE DATABASE TestDB FROM DISK = 'D:\ TestDB_Full.bak' WITH MOVE 'TestDB' TO 
   'D:\Data\TestDB.mdf', MOVE 'TestDB_Log' TO 'D:\Data\TestDB_Log.ldf'

方法 2 – SSMS（SQL SERVER Management Studio）

步骤 1 - 连接到名为“TESTINSTANCE”的数据库实例，然后右键单击数据库文件夹。单击“还原数据库”，如以下快照所示。

步骤 2 - 选择设备单选按钮，然后单击椭圆以选择备份文件，如以下快照所示。

步骤 3 - 单击确定，弹出以下屏幕。

步骤 4 - 选择左上角的“文件”选项，如以下快照所示。

步骤 5 - 选择左上角的选项，然后单击确定以恢复“TestDB”数据库，如以下快照所示。

MS SQL Server - 创建用户

用户是指MS SQL Server数据库中用于访问数据库的帐户。

可以使用以下两种方法之一创建用户。

方法 1 – 使用 T-SQL

句法

Create user <username> for login <loginname>

例子

要创建用户名“TestUser”并映射到 TestDB 数据库中的登录名“TestLogin”，请运行以下查询。

create user TestUser for login TestLogin

其中“TestLogin”是作为登录创建的一部分创建的登录名

方法 2 – 使用 SSMS (SQL Server Management Studio)

注意- 首先，在创建用户帐户之前，我们必须使用任何名称创建登录名。

让我们使用名为“TestLogin”的登录名。

步骤 1 - 连接 SQL Server 并展开数据库文件夹。然后展开名为“TestDB”的数据库，我们将在其中创建用户帐户并展开安全文件夹。右键单击用户，然后单击新用户即可看到以下屏幕。

步骤 2 - 在用户名字段中输入“TestUser”，然后单击椭圆以选择名为“TestLogin”的登录名，如以下快照所示。

步骤 3 - 单击“确定”以显示登录名。再次单击“确定”以创建“TestUser”用户，如以下快照所示。

MS SQL Server - 分配权限

权限是指管理主体对安全对象的访问级别的规则。您可以在 MS SQL Server 中授予、撤销和拒绝权限。

要分配权限，可以使用以下两种方法之一。

方法 1 – 使用 T-SQL

句法

Use <database name>
Grant <permission name> on <object name> to <username\principle>

例子

要将“TestDB”数据库中名为“TestTable”的对象的选择权限分配给名为“TestUser”的用户，请运行以下查询。

USE TestDB
GO
Grant select on TestTable to TestUser

方法 2 – 使用 SSMS (SQL Server Management Studio)

步骤 1 - 连接到实例并展开文件夹，如以下快照所示。

步骤 2 - 右键单击​​ TestUser，然后单击属性。出现以下屏幕。

步骤 3单击搜索并选择特定选项。单击“对象类型”，选择表并单击“浏览”。选择“测试表”并单击“确定”。出现以下屏幕。

步骤 4选择“选择权限”下“授予”列的复选框，然后单击“确定”，如上面的快照所示。

步骤5选择授予“TestUser”对TestDB数据库的“TestTable”的权限。单击“确定”。

MS SQL Server - 监控数据库

监控是指检查数据库状态、设置，可以是所有者名称、文件名、文件大小、备份计划等。

SQL Server数据库主要通过SQL Server Management Studio或T-SQL进行监控，也可以通过创建代理作业、配置数据库邮件、第三方工具等多种方式进行监控。

可以检查数据库状态，无论它是在线还是处于任何其他状态，如以下快照所示。

根据上面的屏幕，所有数据库都处于“在线”状态。如果任何数据库处于任何其他状态，则该状态将显示如下快照所示。

MS SQL Server - 服务

MS SQL Server 提供以下两项数据库创建和维护所必需的服务。还列出了可用于不同目的的其他附加服务。

• SQL服务器
• SQL服务器代理

其他服务

• SQL服务器浏览器
• SQL Server 全文搜索
• SQL Server 集成服务
• SQL Server 报告服务
• SQL Server 分析服务

可以通过以下方法使用上述服务。

启动服务

要启动任何服务，可以使用以下两种方法之一。

方法 1 – Services.msc

步骤 1 - 转到“运行”，键入 services.msc，然后单击“确定”。出现以下屏幕。

步骤 2 - 要启动服务，请右键单击服务，然后单击“启动”按钮。服务将启动，如以下快照所示。

方法 2 – SQL Server 配置管理器

步骤 1 - 使用以下过程打开配置管理器。

开始 → 所有程序 → MS SQL Server 2012 → 配置工具 → SQL Server 配置管理器。

步骤 2 - 选择服务名称，右键单击并单击启动选项。服务将启动，如以下快照所示。

停止服务

要停止任何服务，可以使用以下三种方法之一。

方法 1 - Services.msc

步骤 1 - 转到“运行”，键入 services.msc，然后单击“确定”。出现以下屏幕。

步骤 2 - 要停止服务，请右键单击服务，然后单击停止。所选服务将停止，如下图所示。

方法 2 – SQL Server 配置管理器

步骤 1 - 使用以下过程打开配置管理器。

开始 → 所有程序 → MS SQL Server 2012 → 配置工具 → SQL Server 配置管理器。

步骤 2 - 选择服务名称，右键单击并单击“停止”选项。所选服务将停止，如下图所示。

方法 3 – SSMS（SQL Server Management Studio）

步骤 1 - 连接到实例，如以下快照所示。

步骤 2 - 右键单击​​实例名称，然后单击停止选项。出现以下屏幕。

步骤 3 - 单击“是”按钮，将打开以下屏幕。

步骤 4 - 单击上面屏幕上的“是”选项以停止 SQL Server 代理服务。服务将停止，如下图所示。

笔记

• 我们无法使用 SQL Server Management Studio 方法启动服务，因为由于服务已停止状态而无法连接。

• 我们不能排除在停止 SQL Server 服务的同时停止 SQL Service 代理服务，因为 SQL Server 代理服务是一个依赖服务。

MS SQL Server - HA 技术

高可用性 (HA) 是使应用程序\数据库在计划内或计划外停机情况下 24x7 可用的解决方案\流程\技术。

MS SQL Server 中主要有五个选项来实现\设置数据库的高可用性解决方案。

复制

源数据将通过复制代理（作业）复制到目标。对象级技术。

术语

• 发布者是源服务器。
• 分发者是可选的，它为订阅者存储复制的数据。
• 订阅者是目标服务器。

日志传送

源数据将通过事务日志备份作业复制到目标。数据库级技术。

术语

• 主服务器是源服务器。
• 辅助服务器是目标服务器。
• 监控服务器是可选的，将通过日志传送状态进行监控。

镜像

主数据将借助镜像端点和端口号通过网络事务复制到辅助数据。数据库级技术。

术语

• 主服务器是源服务器。
• 镜像服务器是目标服务器。
• 见证服务器是可选的，用于进行自动故障转移。

聚类

数据将存储在主服务器和辅助服务器根据服务器可用性使用的共享位置。实例级技术。共享存储需要 Windows 集群设置。

术语

• 活动节点是 SQL 服务运行的位置。
• 被动节点是 SQL 服务未运行的节点。

AlwaysON 可用性组

初级数据将通过网络交易基础复制到次级。数据库级技术组。需要 Windows 集群设置，无需共享存储。

术语

• 主副本是源服务器。
• 辅助副本是目标服务器。

以下是配置 HA 技术（镜像和日志传送）的步骤，集群、AlwaysON 可用性组和复制除外。

步骤 1 - 对源数据库进行一份完整备份和一份 T 日志备份。

例子

要将“TESTINSTANCE”中的数据库“TestDB”配置为主 SQL Server，将“DEVINSTANCE”配置为辅助 SQL Server，请编写以下查询以在源 (TESTINSTANCE) 服务器上进行完整备份和 T 日志备份。

连接到“TESTINSTANCE”SQL Server 并打开新查询并编写以下代码并执行，如以下屏幕截图所示。

Backup database TestDB to disk = 'D:\testdb_full.bak'
GO
Backup log TestDB to disk = 'D:\testdb_log.trn'

步骤 2 - 将备份文件复制到目标服务器。

本例中，我们只安装了一台物理服务器和两个SQL Server实例，因此不需要复制，但如果两个SQL Server实例位于不同的物理服务器中，我们需要将以下两个文件复制到物理服务器的任意位置安装“DEVINSTANCE”实例的辅助服务器。

步骤 3 - 使用“norecovery”选项在目标服务器中使用备份文件恢复数据库。

例子

连接到“DEVINSTANCE”SQL Server 并打开“新查询”。编写以下代码来恢复名为“TestDB”的数据库，该数据库与数据库镜像的主数据库（“TestDB”）同名。但是，我们可以为日志传送配置提供不同的名称。在本例中，我们使用“TestDB”数据库名称。使用“norecovery”选项进行两个（完整备份文件和 t-log 备份文件）恢复。

Restore database TestDB from disk = 'D:\TestDB_full.bak'
with move 'TestDB' to 'D:\DATA\TestDB_DR.mdf',
move 'TestDB_log' to 'D:\DATA\TestDB_log_DR.ldf',
norecovery
GO
Restore database TestDB from disk = 'D:\TestDB_log.trn' with norecovery

刷新“DEVINSTANCE”服务器中的数据库文件夹以查看恢复的数据库“TestDB”以及恢复状态，如以下快照所示。

步骤 4 - 根据您的要求配置 HA（日志传送、镜像），如以下快照所示。

例子

右键单击“TESTINSTANCE”SQL Server 的主数据库“TestDB”，然后单击“属性”。将出现以下屏幕。

步骤 5 - 根据您的要求，选择名为“镜像”或“事务日志传送”的选项，它们位于红色框中，如上面屏幕所示，然后按照系统本身引导的向导步骤完成配置。

MS SQL Server - 报告服务

报告是一个可显示的组件。

用法

报告主要用于两个目的 - 公司内部运营和公司外部运营。

报告服务

这是一项用于创建和发布各种报告的服务。

以下是编写任何报告所需的三个要求。

• 业务流程
• 布局
• 查询\过程\视图

BIDS（Business Intelligence Studio 直到 2008 R2）和 SSDT（SQL Server Data Tools 从 2012 年开始）是开发报告的环境。

以下是打开BIDS\SSDT环境来开发报告的步骤。

步骤 1 - 根据 Microsoft SQL Server 程序组中的版本打开 BIDS\SSDT。将出现以下屏幕。在这种情况下，SSDT已打开。

步骤 2 - 转到上面屏幕截图中左上角的文件。单击新建并选择项目。将打开以下屏幕。

步骤 3 - 在上面的屏幕中，选择左上角商业智能下的报告服务，如以下屏幕截图所示。

步骤 4 - 在上面的屏幕中，根据您开发报表的要求，选择报表服务器项目向导（它将逐步指导您完成向导）或报表服务器项目（它将用于选择自定义设置）。

MS SQL Server - 执行计划

执行计划将由查询优化器借助统计信息和 Algebrizer\processor 树生成。它是查询优化器的结果，告诉你如何做\执行你的工作\要求。

有两种不同的执行计划 - 估计的和实际的。

估计执行计划指示优化器视图。

实际执行计划指示执行查询的内容以及执行方式。

执行计划存储在称为计划缓存的内存中，因此可以重复使用。每个计划都存储一次，除非优化器决定查询执行的并行性。

SQL Server 中提供了三种不同格式的执行计划 - 图形计划、文本计划和 XML 计划。

SHOWPLAN 是想要查看执行计划的用户所需的权限。

实施例1

以下是查看估计执行计划的过程。

步骤 1 - 连接到 SQL Server 实例。在本例中，“TESTINSTANCE”是实例名称，如以下快照所示。

步骤 2 - 单击上面屏幕上的“新建查询”选项并编写以下查询。在编写查询之前，选择数据库名称。在本例中，“TestDB”是数据库名称。

Select * from StudentTable

步骤 3 - 单击上面屏幕上以红色框突出显示的符号以显示估计的执行计划，如以下屏幕截图所示。

步骤 4 - 将鼠标放在表扫描上，这是上面屏幕中红色框上方的第二个符号，以详细显示估计的执行计划。将出现以下屏幕截图。

实施例2

以下是查看实际执行计划的过程。

步骤 1连接到 SQL Server 实例。在本例中，“TESTINSTANCE”是实例名称。

步骤 2 - 单击上面屏幕上显示的“新建查询”选项并编写以下查询。在编写查询之前，选择数据库名称。在本例中，“TestDB”是数据库名称。

Select * from StudentTable

步骤 3 - 单击上面屏幕上以红色框突出显示的符号，然后执行查询以显示实际执行计划以及查询结果，如以下屏幕截图所示。

步骤 4 - 将鼠标放在表扫描上，这是屏幕上红色框上方的第二个符号，以详细显示实际的执行计划。将出现以下屏幕截图。

步骤 5 - 单击上述屏幕左上角的“结果”以获取以下屏幕。

MS SQL Server - 集成服务

该服务用于执行 ETL（提取、转换和加载数据）和管理操作。BIDS（2008 年 R2 之前的 Business Intelligence Studio）和 SSDT（2012 年以来的 SQL Server Data Tools）是开发包的环境。

SSIS基本架构

解决方案（项目集合）---> 项目（包集合）---> 包（ETL 和管理操作的任务集合）

在封装下，可以使用以下组件 -

• 控制流（容器和任务）
• 数据流（源、转换、目的地）
• 事件处理程序（发送消息、电子邮件）
• 包资源管理器（包中所有内容的单一视图）
• 参数（用户交互）

以下是打开 BIDS\SSDT 的步骤。

步骤 1 - 根据 Microsoft SQL Server 程序组中的版本打开 BIDS\SSDT。出现以下屏幕。

步骤 2 - 上面的屏幕显示 SSDT 已打开。转到上图中左上角的文件，然后单击新建。选择项目，然后将打开以下屏幕。

步骤 3 - 在上述屏幕左上角的“商业智能”下选择“集成服务”以获取以下屏幕。

步骤 4 - 在上面的屏幕中，根据您开发\创建包的要求选择 Integration Services 项目或 Integration Services 导入项目向导。

MS SQL Server - 分析服务

该服务用于分析大量数据并应用于业务决策。它还用于创建二维或多维业务模型。

在SQL Server 2000版本中，它被称为MSAS（Microsoft Analysis Services）。

从SQL Server 2005开始，它被称为SSAS（SQL Server分析服务）。

模式

有两种模式：本机模式（SQL Server 模式）和共享点模式。

楷模

有两种模型 - 表格模型（用于团队和个人分析）和多维度模型（用于公司分析）。

BIDS（2008 年 R2 之前的 Business Intelligence Studio）和 SSDT（2012 年以来的 SQL Server Data Tools）是使用 SSAS 的环境。

步骤 1 - 根据 Microsoft SQL Server 程序组中的版本打开 BIDS\SSDT。将出现以下屏幕。

步骤 2 - 上面的屏幕显示 SSDT 已打开。转到上图中左上角的文件，然后单击新建。选择项目，然后将打开以下屏幕。

步骤 3 - 在上面的屏幕中选择“商业智能”下的“分析服务”，如左上角所示。弹出以下屏幕。

步骤 4 - 在上面的屏幕中，根据您使用分析服务的要求从列出的五个选项中选择任一选项。