MS SQL Server – 快速指南

MS SQL Server – 快速指南

MS SQL Server – 概述

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

什么是 SQL Server？

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

  • 它也是一个 ORDBMS。

  • 它依赖于平台。

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

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

SQL Server 的使用

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

SQL Server 的版本

Version 年 代码名称
6.0 1995年 SQL95
6.5 1996年 九头蛇
7.0 1998年 狮身人面像
8.0 (2000) 2000年 示罗
9.0 (2005) 2005年 育空地区
10.0 (2008) 2008年 卡特迈
10.5 (2008 R2) 2010年 乞力马扎罗
11.0 (2012) 2012年 德纳利
12 (2014) 2014年 Hekaton（最初），SQL 14（当前）

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 有各种版本。本章列出了多个版本及其功能。

  • Enterprise – 这是具有完整功能集的高端版本。

  • Standard – 当不需要高级功能时，它的功能比 Enterprise 少。

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

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

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

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

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

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

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

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

2005 2008年 2008 R2 2012年 2014年
Enterprise 是的 是的 是的 是的
Standard 是的 是的 是的 是的
Developer 是的 是的 是的 是的
Workgroup 是的 是的 不 不
Win Compact Edition – Mobile 是的 是的 是的 是的
Enterprise Evaluation 是的 是的 是的 是的
Express 是的 是的 是的 是的
Web 是的 是的 是的
Datacenter 不 不
Business Intelligence 是的

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-Virtual Interface Adapter（需要由供应商设置的特殊硬件，并且在 SQL 2012 版本中已弃用）。

服务器– SQL 服务安装和数据库所在的位置。

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

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

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

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

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

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

SQL OS – 这位于主机（Windows 操作系统）和 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，这将覆盖为服务器指定的恢复间隔并避免在此类数据库上自动检查点。

  • Manual – 这个语句就像任何其他 T-SQL 语句一样运行，一旦你发出 checkpoint 命令，它就会运行到它的完成。仅对当前数据库运行手动检查点。您还可以指定可选的 Checkpoint_Duration – 此持续时间指定您希望检查点完成的时间。

  • 内部– 作为用户，您无法控制内部检查点。针对特定操作发出，例如

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

    • 如果恢复模式从 Full\Bulk-logged 更改为 Simple。

    • 在备份数据库时。

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

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

    • 当数据库的恢复模型是大容量日志并执行最少日志操作时，也会发生检查点。

    • 数据库快照创建。

  • Lazy Writer Process – Lazy Writer 会出于完全不同的原因将脏页推送到磁盘，因为它需要释放缓冲池中的内存。当 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 操作的数量来优化性能，这些操作本身比从内存中检索数据要慢。

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

缓冲池的其他部分包括 –

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

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

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

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

数据文件架构

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

文件组

数据库文件可以组合成文件组以用于分配和管理目的。任何文件都不能属于多个文件组。日志文件从不属于文件组。日志空间与数据空间分开管理。

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

每个数据库中的一个文件组作为默认文件组运行。当 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) 语句对任何表（包括系统表）所做的更改。

  • 每个extent和page分配或de分配。

  • 创建或删除表或索引。

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

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

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

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

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 管理工作室。
  • 使用 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 &plus 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”数据库完整备份，如下面的快照所示。

完整备份

创建备份2

MS SQL Server – 恢复数据库

恢复是从备份复制数据并将记录的事务应用于数据的过程。还原就是您对备份所做的事情。获取备份文件并将其重新转换为数据库。

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

方法一——T-SQL

句法

Restore database <Your database name> from disk = '<Backup file location &plus 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 管理工作室）

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

管理工作室测试实例

步骤 2 – 选择设备单选按钮并单击椭圆以选择备份文件，如下面的快照所示。

选择备份设备

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

恢复数据库

步骤 4 – 选择左上角的文件选项，如下面的快照所示。

恢复数据库TestDB

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

恢复数据库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单击“搜索”并选择特定选项。单击对象类型，选择表并单击浏览。选择“TestTable”并单击“确定”。出现以下屏幕。

数据库测试用户

步骤 4选中 Select permission 下的 Grant 列的复选框，然后单击 OK，如上图所示。

数据库测试用户

步骤 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 分析服务

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

启动服务

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

方法 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 – 单击是按钮，将打开以下屏幕。

服务

Step 4 – 单击上面屏幕上的 Yes 选项以停止 SQL Server 代理服务。服务将停止，如下面的屏幕截图所示。

服务

笔记

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

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

MS SQL Server – HA 技术

高可用性 (HA) 是使应用程序\数据库在计划内或计划外中断情况下 24×7 可用的解决方案\过程\技术。

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

复制

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

术语

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

日志传送

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

术语

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

镜像

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

术语

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

聚类

数据将存储在共享位置，主服务器和辅助服务器都根据服务器的可用性使用该共享位置。实例级技术。共享存储需要 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'

HA技术

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

在这种情况下，我们只安装了一台物理服务器和两个 SQL Server 实例，因此不需要复制，但是如果两个 SQL Server 实例在不同的物理服务器上，我们需要将以下两个文件复制到安装了“DEVINSTANCE”实例的辅助服务器。

HA技术

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

例子

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

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

HA技术

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

HA技术

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

例子

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

HA技术

第 5 步– 根据您的要求选择名为“镜像”或“交易日志传送”的选项，它们以红色框显示，如上图所示，并按照系统本身指导的向导步骤完成配置。

MS SQL Server – 报告服务

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

用法

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

报告服务

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

以下是开发任何报告所必需的三个要求。

  • 业务流程
  • 布局
  • 查询\过程\查看

BIDS（Business Intelligence Studio 到 2008 R2）和 SSDT（SQL Server Data Tools from 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”是实例名称，如下面的快照所示。

执行计划

Step 2 – 单击上面屏幕上的 New Query 选项并编写以下查询。在编写查询之前，选择数据库名称。在这种情况下，“TestDB”是数据库名称。

Select * from StudentTable

执行计划

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

执行计划

Step 4 – 将鼠标放在上面屏幕中红色框上方的第二个符号 table scan 上，以详细显示估计的执行计划。出现以下屏幕截图。

执行计划

示例 2

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

步骤 1连接到 SQL Server 实例。在这种情况下，“TESTINSTANCE”是实例名称。

执行计划

Step 2 – 单击上面屏幕上看到的 New Query 选项并编写以下查询。在编写查询之前，选择数据库名称。在这种情况下，“TestDB”是数据库名称。

Select * from StudentTable

执行计划

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

执行计划

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

执行计划

第 5 步– 单击上面屏幕左上角的结果以获取以下屏幕。

执行计划

MS SQL Server – 集成服务

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

SSIS基本架构

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

在 Package 下，以下组件可用 –

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

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

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

集成服务

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

集成服务

步骤 3 – 在上面屏幕左上角的商业智能下选择集成服务以获得以下屏幕。

集成服务

步骤 4 – 在上面的屏幕中，根据您开发\创建包的要求选择集成服务项目或集成服务导入项目向导。

MS SQL Server – 分析服务

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

在 SQL Server 2000 版本中，它被称为 MSAS（Microsoft 分析服务）。

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

模式

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

楷模

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

BIDS（Business Intelligence Studio 到 2008 R2）和 SSDT（SQL Server Data Tools from 2012）是使用 SSAS 的环境。

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

分析服务

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

分析服务

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

分析服务

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

觉得文章有用？

点个广告表达一下你的爱意吧 ！😁