---

MS SQL Server - 快速指南

MS SQL Server - 概述

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

什么是 SQL Server?

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

• 它也是 ORDBMS。

• 它依赖于平台。

• 它既是基于 GUI 的软件，也是基于命令的软件。

• 它支持 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 代理、SSIS、SSAS、SSRS、SQL 浏览器、SQL Server 全文搜索等。

SQL Server 实例

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

实例的优势

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

MS SQL Server - 版本

SQL Server 有多种版本。本章列出了多个版本及其功能。

• 企业版 − 这是具有完整功能集的顶级版本。

• 标准版 − 当不需要高级功能时，此版本的功能比企业版少。

• 工作组版 − 此版本适用于大型公司的远程办公室。

• Web − 此版本专为 Web 应用程序而设计。

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

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

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

• Datacenter − 新 SQL Server 2008 R2 的主要变化是 Datacenter Edition。数据中心版没有内存限制，并支持超过 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 framework 2.0。
• SQL Server 本机客户端。

2008&2008R2 的先决条件

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

2012&2014 的先决条件

• 安装支持文件。
• .net framework 4.0。
• SQL Server 本机客户端。
• Windows installer 4.5/更高版本。
• Windows PowerShell 2.0。

安装步骤

步骤 1 −从 https://www.microsoft.com/en-us/evalcenter/download-sql-server-2019

下载评估版

下载软件后，将根据您的下载(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 OS)和 SQL Server 之间。数据库引擎上执行的所有活动均由 SQL OS 负责。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 服务器面临内存压力时，就会发生这种情况。据我所知，这是由内部进程控制的，没有设置。

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

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

内存架构

以下是内存架构的一些显著特点。

• 所有数据库软件的主要设计目标之一是尽量减少磁盘 I/O，因为磁盘读写是最耗费资源的操作之一。

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

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

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

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

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

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

缓冲池的其他部分包括 −

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

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

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

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

数据文件架构

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

文件组

数据库文件可以分组到文件组中，以便进行分配和管理。任何文件都不能成为多个文件组的成员。日志文件永远不会成为文件组的一部分。日志空间与数据空间分开管理。

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

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

默认情况下，主文件组是默认文件组。用户应该具有 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 和图像数据之外的所有数据的数据行。

• 索引 − 索引条目。

• Tex\Image − 文本、图像和 ntext 数据。

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

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

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

• 索引分配映射 (IAM) − 有关表或索引使用的扩展区的信息。

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

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

日志文件架构

SQL Server 事务日志的逻辑操作方式就好像事务日志是一串日志记录。每个日志记录都由日志序列号 (LSN) 标识。每个日志记录都包含其所属事务的 ID。

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

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

• 记录逻辑操作。
  • 要将逻辑操作向前回滚，需要再次执行该操作。
  • 要将逻辑操作回滚，需要执行反向逻辑操作。
• 记录前后映像。
  • 要将操作向前滚动，则应用后映像。
  • 要将操作回滚，则应用前映像。

事务日志中记录了不同类型的操作。这些操作包括 −

• 每个事务的开始和结束。

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

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

• 创建或删除表或索引。

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

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

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

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

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

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

MS SQL Server - Management Studio

SQL Server Management Studio is a workstation component\client tool that will be installed if we select workstation component in installation steps. This allows you to connect to and manage your SQL Server from a graphical interface instead of having to use the command line.

In order to connect to a remote instance of an SQL Server, you will need this or similar software. It is used by Administrators, Developers, Testers, etc.

The following methods are used to open SQL Server Management Studio.

First Method

Start → All Programs → MS SQL Server 2012 → SQL Server Management Studio

Second Method

Go to Run and type SQLWB (For 2005 Version) SSMS (For 2008 and Later Versions). Then click Enter.

SQL Server Management Studio will be open up as shown in the following snapshot in either of the above method.

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 脚本

创建登录 yourloginname，密码为"yourpassword"

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

Create login TestLogin with password='P@ssword'

MS SQL Server - 创建数据库

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

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

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

系统数据库

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

• 主数据库
• 模型数据库
• MSDB
• Tempdb
• 资源(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 <您的数据库名称>

示例

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

Exec use msdb

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

Select * from backupset

MS SQL Server - 删除数据库

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

方法 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

语法

从磁盘恢复数据库 <您的数据库名称> = '<备份文件位置 + 文件名>'

示例

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

使用替换从磁盘"D:\TestDB_Full.bak"恢复数据库 TestDB

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

确保以下命令中用于数据和日志文件的 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>

示例

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

USE TestDB
GO
Grant select on TestTable to TestUser

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

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

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

步骤 3 单击搜索并选择特定选项。单击对象类型，选择表，然后单击浏览。选择"TestTable"并单击确定。出现以下屏幕。

步骤 4 选中"选择权限"下"授予"列的复选框，然后单击"确定"，如上图所示。

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

MS SQL Server - 监控数据库

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

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

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

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

MS SQL Server - 服务

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

• SQL Server
• SQL Server 代理

其他服务

• SQL Server 浏览器
• 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 服务代理服务，因为 SQL Server 代理服务是一项依赖服务。

MS SQL Server - HA 技术

高可用性 (HA) 是一种解决方案\处理技术，可使应用程序\数据库在计划内或非计划内停机情况下全天候可用。

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

复制

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

术语

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

日志传送

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

术语

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

镜像

在镜像端点和端口号的帮助下，主数据将通过网络事务复制到辅助服务器。数据库级技术。

术语

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

集群

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

术语

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

AlwaysON 可用性组

主数据将通过网络事务复制到辅助数据。数据库级技术组。需要设置 Windows 群集，而无需共享存储。

术语

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

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

步骤 1 −对源数据库进行一次完整备份和一次 T-log 备份。

示例

要为"TESTINSTANCE"中作为主 SQL Server 的数据库"TestDB"和作为辅助 SQL Server 的"DEVINSTANCE"配置镜像\日志传送，请编写以下查询以在源 (TESTINSTANCE) 服务器上进行完整备份和 T-log 备份。

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

Backup database TestDB to disk = 'D:	estdb_full.bak'
GO
Backup log TestDB to disk = 'D:	estdb_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(2008 R2 之前的 Business Intelligence Studio)和 SSDT(2012 年起的 SQL Server Data Tools)是开发报告的环境。

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

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

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

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

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

MS SQL Server - 执行计划

执行计划将由查询优化器在统计数据和 Algebrizer\processor 树的帮助下生成。它是查询优化器的结果，并告诉您如何执行\perform 工作要求。

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

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

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

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

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(Business Intelligence Studio 直到 2008 R2)和 SSDT(SQL Server Data Tools 从 2012 开始)是开发包的环境。

SSIS 基本架构

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

在包下，以下组件可用 −

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

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

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

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

步骤 3 −在以上屏幕的左上角，选择 Business Intelligence 下的 Integration Services，即可获得以下屏幕。

步骤 4 − 在以上屏幕中，根据您的需求选择 Integration Services Project 或 Integration Services Import Project Wizard 来开发\创建包。

MS SQL Server - Analysis Services

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

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

从 SQL Server 2005 开始，它被称为 SSAS (SQL Server Analysis Services)。

模式

有两种模式 −本机模式(SQL Server 模式)和 Share Point 模式。

模型

有两种模型 − 表格模型(用于团队和个人分析)和多维模型(用于公司分析)。

BIDS(Business Intelligence Studio 直到 2008 R2)和 SSDT(SQL Server Data Tools 从 2012 开始)是使用 SSAS 的环境。

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

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

步骤 3 − 在上述屏幕的"Business Intelligence"下，选择"Analysis Services"，如左上角所示。将弹出以下屏幕。

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

---