Ms Sql Server 简明教程

MS SQL Server - Quick Guide

MS SQL Server - Overview

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

This chapter introduces SQL Server, discusses its usage, advantages, versions, and components.

What is SQL Server?

It is a software, developed by Microsoft, which is implemented from the specification of RDBMS.
It is also an ORDBMS.
It is platform dependent.
It is both GUI and command based software.
It supports SQL (SEQUEL) language which is an IBM product, non-procedural, common database and case insensitive language.

Usage of SQL Server

To create databases.
To maintain databases.
To analyze the data through SQL Server Analysis Services (SSAS).
To generate reports through SQL Server Reporting Services (SSRS).
To carry out ETL operations through SQL Server Integration Services (SSIS).

Versions of SQL Server

Version	Year	Code Name
6.0	1995	SQL95
6.5	1996	Hydra
7.0	1998	Sphinx
8.0 (2000)	2000	Shiloh
9.0 (2005)	2005	Yukon
10.0 (2008)	2008	Katmai
10.5 (2008 R2)	2010	Kilimanjaro
11.0 (2012)	2012	Denali
12 (2014)	2014	Hekaton (initially), SQL 14 (current)

SQL Server Components

SQL Server 在客户机-服务器架构中运行，因此它支持两种类型的组件-（a）工作站和（b）服务器。

SQL Server works in client-server architecture, hence it supports two types of components − (a) Workstation and (b) Server.

Workstation components are installed in every device/SQL Server operator’s machine. These are just interfaces to interact with Server components. Example: SSMS, SSCM, Profiler, BIDS, SQLEM etc.
Server components are installed in centralized server. These are services. Example: SQL Server, SQL Server Agent, SSIS, SSAS, SSRS, SQL browser, SQL Server full text search etc.

Instance of SQL Server

An instance is an installation of SQL Server.
An instance is an exact copy of the same software.
If we install 'n' times, then 'n' instances will be created.
There are two types of instances in SQL Server a) Default b) Named.
Only one default instance will be supported in one Server.
Multiple named instances will be supported in one Server.
Default instance will take the server name as Instance name.
Default instance service name is MSSQLSERVER.
16 instances will be supported in 2000 version.
50 instances will supported in 2005 and later versions.

Advantages of Instances

To install different versions in one machine.
To reduce cost.
To maintain production, development, and test environments separately.
To reduce temporary database problems.
To separate security privileges.
To maintain standby server.

MS SQL Server - Editions

SQL Server 可用于各种版本。本章列出了具有其功能的多个版本。

SQL Server is available in various editions. This chapter lists the multiple editions with its features.

Enterprise − This is the top-end edition with a full feature set.
Standard − This has less features than Enterprise, when there is no requirement of advanced features.
Workgroup − This is suitable for remote offices of a larger company.
Web − This is designed for web applications.
Developer − This is similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation.
Express − This is free entry level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB.
Compact − This is free embedded database for mobile application development. The maximum size of the database is 4 GB.
Datacenter − The major change in new SQL Server 2008 R2 is Datacenter Edition. The Datacenter edition has no memory limitation and offers support for more than 25 instances.
Business Intelligence − Business Intelligence Edition is a new introduction in SQL Server 2012. This edition includes all the features in the Standard edition and support for advanced BI features such as Power View and PowerPivot, but it lacks support for advanced availability features like AlwaysOn Availability Groups and other online operations.
Enterprise Evaluation − The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. This edition has a built-in expiry of 6 months from the time that you install it.

2005

2008

2008 R2

2012

2014

Enterprise

Yes

Standard

Yes

Developer

Yes

Workgroup

Yes

Win Compact Edition - Mobile

Yes

Enterprise Evaluation

Yes

Express

Yes

Web

Yes

Datacenter

Business Intelligence

Yes

MS SQL Server - Installation

SQL Server 支持两种类型的安装

SQL Server supports two types of installation −

Standalone
Cluster based

Checks

Check RDP access for the server.
Check OS bit, IP, domain of server.
Check if your account is in admin group to run setup.exe file.
Software location.

Requirements

Which version, edition, SP and hotfix if any.
Service accounts for database engine, agent, SSAS, SSIS, SSRS, if any.
Named instance name if any.
Location for binaries, system, user databases.
Authentication mode.
Collation setting.
List of features.

Pre-requisites for 2005

Setup support files.
.net framework 2.0.
SQL Server native client.

Pre-requisites for 2008&2008R2

Setup support files.
.net framework 3.5 SP1.
SQL Server native client.
Windows installer 4.5/later version.

Pre-requisites for 2012&2014

Setup support files.
.net framework 4.0.
SQL Server native client.
Windows installer 4.5/later version.
Windows PowerShell 2.0.

Installation Steps

Step 1 - 从 https://www.microsoft.com/en-us/evalcenter/download-sql-server-2019 下载评估版

Step 1 − Download the Evaluation Edition from https://www.microsoft.com/en-us/evalcenter/download-sql-server-2019

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

Once the software is downloaded, the following files will be available based on your download (32 or 64 bit) option.

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

Note − X86（32 位）和 X64（64 位）

Note − X86 (32 bit) and X64 (64 bit)

Step 2 − 双击“SQLFULL_x86_ENU_Install.exe”或“SQLFULL_x64_ENU_Install.exe”，它将分别在“SQLFULL_x86_ENU”或“SQLFULL_x86_ENU”文件夹中解压安装所需的文件。

Step 2 − Double-click the “SQLFULL_x86_ENU_Install.exe” or “SQLFULL_x64_ENU_Install.exe”, it will extract the required files for installation in the“SQLFULL_x86_ENU” or “SQLFULL_x86_ENU” folder respectively.

Step 3 − 单击“SQLFULL_x86_ENU”或“SQLFULL_x64_ENU_Install.exe”文件夹，并双击“SETUP”应用程序。

Step 3 − Click the “SQLFULL_x86_ENU” or “SQLFULL_x64_ENU_Install.exe” folder and double-click “SETUP” application.

为便于理解，我们在本文中使用的是 SQLFULL_x64_ENU_Install.exe 软件。

For understanding, here we have used SQLFULL_x64_ENU_Install.exe software.

Step 4 − 一旦我们单击“安装”应用程序，将打开以下屏幕。

Step 4 − Once we click on 'setup' application, the following screen will open.

Step 5 − 单击位于上述屏幕左侧的安装。

Step 5 − Click Installation which is on the left side of the above screen.

Step 6 − 单击上述屏幕上右侧显示的第一个选项。将打开以下屏幕。

Step 6 − Click the first option of the right side seen on the above screen. The following screen will open.

Step 7 − 单击确定，然后弹出以下屏幕。

Step 7 − Click OK and the following screen pops up.

Step 8 − 单击下一步以获取以下屏幕。

Step 8 − Click Next to get the following screen.

Step 9 − 务必检查产品密钥选择，然后单击下一步。

Step 9 − Make sure to check the product key selection and click Next.

Step 10 − 选中复选框以接受许可证选项，然后单击下一步。

Step 10 − Select the checkbox to accept the license option and click Next.

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

Step 11 − Select SQL Server feature installation option and click Next.

Step 12 − 选中数据库引擎服务复选框，然后单击下一步。

Step 12 − Select Database engine services checkbox and click Next.

Step 13 − 输入命名的实例（此处我使用 TestInstance），然后单击下一步。

Step 13 − Enter the named instance (here I used TestInstance) and click Next.

Step 14 − 在上述屏幕上单击下一步，随后会出现以下屏幕。

Step 14 − Click Next on the above screen and the following screen appears.

Step 15 − 选择服务帐户名称和上述列出的服务启动类型，然后单击排序规则。

Step 15 − Select service account names and start-up types for the above listed services and click Collation.

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

Step 16 − Make sure the correct collation selection is checked and click Next.

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

Step 17 − Make sure authentication mode selection and administrators are checked and click Data Directories.

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

Step 18 − Make sure to select the above directory locations and click Next. The following screen appears.

Step 19 - 在上述屏幕上单击“下一步”。

Step 19 − Click Next on the above screen.

Step 20 - 在上述屏幕上单击“下一步”以获取以下屏幕。

Step 20 − Click Next on the above screen to the get the following screen.

Step 21 - 确保正确检查上述选择，然后单击“安装”。

Step 21 − Make sure to check the above selection correctly and click Install.

正如在上述屏幕中所示，安装成功。单击“关闭”完成。

Installation is successful as shown in the above screen. Click Close to finish.

MS SQL Server - Architecture

为了便于理解，我们将 SQL Server 的架构分为以下部分 −

We have classified the architecture of SQL Server into the following parts for easy understanding −

General architecture
Memory architecture
Data file architecture
Log file architecture

General Architecture

Client − 请求发起的地方。

Client − Where the request initiated.

Query − 作为高级语言的 SQL 查询。

Query − SQL query which is high level language.

Logical Units − 关键字、表达式和运算符等。

Logical Units − Keywords, expressions and operators, etc.

N/W Packets − 与网络相关的代码。

N/W Packets − Network related code.

Protocols − 在 SQL Server 中我们有 4 个协议。

Protocols − In SQL Server we have 4 protocols.

Shared memory (for local connections and troubleshooting purpose).
Named pipes (for connections which are in LAN connectivity).
TCP/IP (for connections which are in WAN connectivity).
VIA-Virtual Interface Adapter (requires special hardware to set up by vendor and also deprecated from SQL 2012 version).

Server − SQL 服务已安装的位置以及数据库所在的目录。

Server − Where SQL Services got installed and databases reside.

Relational Engine − 此处将执行实际执行。它包含查询解析器、查询优化器和查询执行器。

Relational Engine − This is where real execution will be done. It contains Query parser, Query optimizer and Query executor.

Query Parser (Command Parser) and Compiler (Translator) − 这将检查查询的语法，并将查询转换为机器语言。

Query Parser (Command Parser) and Compiler (Translator) − This will check syntax of the query and it will convert the query to machine language.

Query Optimizer − 采用查询、统计和 Algebrizer 树作为输入，准备执行计划作为输出。

Query Optimizer − It will prepare the execution plan as output by taking query, statistics and Algebrizer tree as input.

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

Execution Plan − It is like a roadmap, which contains the order of all the steps to be performed as part of the query execution.

Query Executor − 在此，将借助执行计划逐步执行查询，还将联系存储引擎。

Query Executor − This is where the query will be executed step by step with the help of execution plan and also the storage engine will be contacted.

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

Storage Engine − It is responsible for storage and retrieval of data on the storage system (disk, SAN, etc.,), data manipulation, locking and managing transactions.

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

SQL OS − This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure.

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

Checkpoint Process − Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk. Apart from this, it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.

这是一个专用进程，并且由 SQL Server 在特定时间间隔内自动运行。SQL Server 为每个数据库单独运行检查点进程。检查有助于缩短 SQL Server 在发生意外关机或系统崩溃/故障时的恢复时间。

It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each database individually. Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash\Failure.

Checkpoints in SQL Server

在 SQL Server 2012 中有四种类型的 checkpoints -

In SQL Server 2012 there are four types of checkpoints −

Automatic − This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval − Server Configuration Option.
Indirect − This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected, this will override the Recovery Interval specified for the server and avoid automatic checkpoint on such DB.
Manual − This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual checkpoint runs for your current database only. You can also specify the Checkpoint_Duration which is optional - this duration specifies the time in which you want your checkpoint to complete.
Internal − As a user you can’t control internal checkpoint. Issued on specific operations such as Shutdown initiates a checkpoint operation on all databases except when shutdown is not clean (shutdown with nowait). If the recovery model gets changed from Full\Bulk-logged to Simple. While taking backup of the database. If your DB is in simple recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval. Alter database command to add or remove a data\log file also initiates a checkpoint. Checkpoint also takes place when the recovery model of the DB is bulk-logged and a minimally logged operation is performed. DB Snapshot creation.
Lazy Writer Process − Lazy writer will push dirty pages to disk for an entirely different reason, because it needs to free up memory in the buffer pool. This happens when SQL server comes under memory pressure. As far as I am aware, this is controlled by an internal process and there is no setting for it.

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

SQL server constantly monitors memory usage to assess resource contention (or availability); its job is to make sure that there is a certain amount of free space available at all times. As part of this process, when it notices any such resource contention, it triggers Lazy Writer to free up some pages in memory by writing out dirty pages to disk. It employs Least Recently Used (LRU) algorithm to decide which pages are to be flushed to the disk.

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

If Lazy Writer is always active, it could indicate memory bottleneck.

Memory Architecture

以下是内存体系结构的一些显着特征：

Following are some of the salient features of memory architecture.

One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations.
Memory in windows can be called with Virtual Address Space, shared by Kernel mode (OS mode) and User mode (Application like SQL Server).
SQL Server "User address space" is broken into two regions: MemToLeave and Buffer Pool.
Size of MemToLeave (MTL) and Buffer Pool (BPool) is determined by SQL Server during startup.
Buffer management is a key component in achieving I/O highly efficiency. The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer pool to reduce database file I/O.
The buffer pool is further divided into multiple sections. The most important ones being the buffer cache (also referred to as data cache) and procedure cache. Buffer cache holds the data pages in memory so that frequently accessed data can be retrieved from cache. The alternative would be reading data pages from the disk. Reading data pages from cache optimizes performance by minimizing the number of required I/O operations which are inherently slower than retrieving data from the memory.
Procedure cache keeps the stored procedure and query execution plans to minimize the number of times that query plans have to be generated. You can find out information about the size and activity within the procedure cache using DBCC PROCCACHE statement.

缓冲池的其他部分包括 −

Other portions of buffer pool include −

System level data structures − Holds SQL Server instance level data about databases and locks.
Log cache − Reserved for reading and writing transaction log pages.
Connection context − Each connection to the instance has a small area of memory to record the current state of the connection. This information includes stored procedure and user-defined function parameters, cursor positions and more.
Stack space − Windows allocates stack space for each thread started by SQL Server.

Data File Architecture

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

Data File architecture has the following components −

File Groups

数据库文件可以组合在文件组中以用于分配和管理。没有文件可以成为多个文件组的成员。日志文件从不作为文件组的一部分。日志空间与数据空间分开管理。

Database files can be grouped together in file groups for allocation and administration purposes. No file can be a member of more than one file group. Log files are never part of a file group. Log space is managed separately from data space.

SQL Server 中有两种类型文件组，主文件组和用户自定义文件组。主文件组包含主数据文件和未具体指定到其他文件组的任何其他文件。系统表的所有页都在主文件组中分配。用户自定义文件组是使用创建数据库或更改数据库语句中的文件组关键字指定的任何文件组。

There are two types of file groups in SQL Server, Primary and User-defined. Primary file group contains the primary data file and any other files not specifically assigned to another file group. All pages for the system tables are allocated in the primary file group. User-defined file groups are any file groups specified using the file group keyword in create database or alter database statement.

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

One file group in each database operates as the default file group. When SQL Server allocates a page to a table or index for which no file group was specified when they were created, the pages are allocated from default file group. To switch the default file group from one file group to another file group, it should have db_owner fixed db role.

默认情况下，主文件组是默认文件组。用户应具有 db_owner 固定数据库角色，以便分别备份文件和文件组。

By default, primary file group is the default file group. User should have db_owner fixed database role in order to take backup of files and file groups individually.

Files

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

Databases have three types of files - Primary data file, Secondary data file, and Log file. Primary data file is the starting point of the database and points to the other files in the database.

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

Every database has one primary data file. We can give any extension for the primary data file but the recommended extension is .mdf. Secondary data file is a file other than the primary data file in that database. Some databases may have multiple secondary data files. Some databases may not have a single secondary data file. Recommended extension for secondary data file is .ndf.

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

Log files hold all of the log information used to recover the database. Database must have at least one log file. We can have multiple log files for one database. The recommended extension for log file is .ldf.

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

The location of all the files in a database are recorded in both master database and the primary file for the database. Most of the time, the database engine uses the file location from the master database.

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

Files have two names − Logical and Physical. Logical name is used to refer to the file in all T-SQL statements. Physical name is the OS_file_name, it must follow the rules of OS. Data and Log files can be placed on either FAT or NTFS file systems, but cannot be placed on compressed file systems. There can be up to 32,767 files in one database.

Extents

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

Extents are basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages or 64KB. SQL Server has two types of extents - Uniform and Mixed. Uniform extents are made up of only single object. Mixed extents are shared by up to eight objects.

Pages

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

It is the fundamental unit of data storage in MS SQL Server. The size of the page is 8KB. The start of each page is 96 byte header used to store system information such as type of page, amount of free space on the page and object id of the object owning the page. There are 9 types of data pages in SQL Server.

Data − Data rows with all data except text, ntext and image data.
Index − Index entries.
Text\Image − Text, image and ntext data.
GAM − Information about allocated extents.
SGAM − Information about allocated extents at system level.
Page Free Space (PFS) − Information about free space available on pages.
Index Allocation Map (IAM) − Information about extents used by a table or index.
Bulk Changed Map (BCM) − Information about extents modified by bulk operations since the last backup log statement.
Differential Changed Map (DCM) − Information about extents that have changed since the last backup database statement.

Log File Architecture

SQL Server 的事务日志在逻辑上将事务日志当作一系列的日志记录。每个日志记录由日志序列号 (LSN) 识别。每个日志记录都包含它所属的事务的 ID。

The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by Log Sequence Number (LSN). Each log record contains the ID of the transaction that it belongs to.

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

Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

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

The steps to recover an operation depend on the type of log record −

Logical operation logged. To roll the logical operation forward, the operation is performed again.To roll the logical operation back, the reverse logical operation is performed.
Before and after image logged. To roll the operation forward, the after image is applied.To roll the operation back, the before image is applied.

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

Different types of operations are recorded in the transaction log. These operations include −

The start and end of each transaction.
Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.
Every extent and page allocation or de allocation.
Creating or dropping a table or index.

取消操作也会被记录。每个事务保留事务日志上的空间，以确保存在足够的日志空间来支持因明确取消语句或遇到错误而导致的取消。此保留空间在事务完成后被释放。

Rollback operations are also logged. Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. This reserved space is freed when the transaction is completed.

从必须用于成功对整个数据库执行取消以恢复的第一个日志记录到最后写入的日志记录的对数文件部分称为日志的活动部分或活动日志。这是数据库完全恢复所需的日志部分。活跃日志的任何部分都不能被截断。此第一个日志记录的 LSN 被称为最小恢复 LSN（最小 LSN）。

The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. This is the section of the log required to a full recovery of the database. No part of the active log can ever be truncated. LSN of this first log record is known as the minimum recovery LSN (Min LSN).

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

The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file.

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

The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size or number of virtual log files cannot be configured or set by administrators. The only time virtual log files affect system performance is if the physical log files are defined by small size and growth_increment values.

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

The size value is the initial size for the log file and the growth_increment value is the amount of space added to the file every time new space is required. If the log files grow to a large size because of many small increments, they will have many virtual log files. This can slow down database startup and also log backup and restore operations.

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

We recommend that you assign log files a size value close to the final size required, and also have a relatively large growth_increment value. SQL Server uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.

MS SQL Server - Management Studio

SQL Server Management Studio 是一款工作站组件\客户端工具，如果我们在安装步骤中选择工作站组件，它将被安装。这允许您通过图形界面连接到 SQL Server 并对其进行管理，而无需使用命令行。

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.

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

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.

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

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

First Method

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

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

Second Method

进入运行，键入 SQLWB（2005 版）SSMS（2008 版及以后的版本）。然后点击 Enter。

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

以上任一方法都会打开 SQL Server Management Studio，如下方的快照所示。

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

MS SQL Server - Login Database

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

A login is a simple credential for accessing SQL Server. For example, you provide your username and password when logging on to Windows or even your e-mail account. This username and password builds up the credentials. Therefore, credentials are simply a username and a password.

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

SQL Server allows four types of logins −

A login based on Windows credentials.
A login specific to SQL Server.
A login mapped to a certificate.
A login mapped to asymmetric key.

在本文档中，我们感兴趣的是基于 Windows 凭据的登录和 SQL Server 特有的登录。

In this tutorial, we are interested in logins based on Windows Credentials and logins specific to SQL Server.

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

Logins based on Windows credentials allow you to log in to SQL Server using a Windows username and password. If you need to create your own credentials (username and password,) you can create a login specific to SQL Server.

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

To create, alter, or remove a SQL Server login, you can take one of two approaches −

Using SQL Server Management Studio.
Using T-SQL statements.

使用以下方法创建登录 -

Following methods are used to create Login −

First Method – Using SQL Server Management Studio

Step 1 - 在连接到 SQL Server 实例后，展开登录文件夹，如下面的快照所示。

Step 1 − After connecting to SQL Server Instance, expand logins folder as shown in the following snapshot.

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

Step 2 − Right-click on Logins, then click Newlogin and the following screen will open.

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

Step 3 − Fill the Login name, Password and Confirm password columns as shown in the above screen and then click OK.

登录将如以下图像所示创建。

Second Method – Using T-SQL Script

Create login yourloginname with password='yourpassword'

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

To create login name with TestLogin and password ‘P@ssword’ run below the following query.

Create login TestLogin with password='P@ssword'

MS SQL Server - Create Database

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

Database is a collection of objects such as table, view, stored procedure, function, trigger, etc.

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

In MS SQL Server, two types of databases are available.

System databases
User Databases

System Databases

系统数据库在安装 MS SQL Server 时自动创建。下面是系统数据库列表 -

System databases are created automatically when we install MS SQL Server. Following is a list of system databases −

Master
Model
MSDB
Tempdb
Resource (Introduced in 2005 version)
Distribution (It’s for Replication feature only)

User Databases

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

User databases are created by users (Administrators, developers, and testers who have access to create databases).

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

Following methods are used to create user database.

Method 1 – Using T-SQL Script or Restore Database

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

Following is the basic syntax for creating database in MS SQL Server.

Create database <yourdatabasename>

或

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

Example

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

To create database called ‘Testdb’, run the following query.

Create database Testdb

或

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

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

Note − D:\backup is location of backup file and Testdb_full_backup.bak is the backup file name

Method 2 – Using SQL Server Management Studio

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

Connect to SQL Server instance and right-click on the databases folder. Click on new database and the following screen will appear.

在数据库名称字段中输入数据库名称（示例：要使用名称“Testdb”创建数据库），然后单击确定。Testdb 数据库将创建，如下面的截图所示。

Enter the database name field with your database name (example: to create database with the name ‘Testdb’) and click OK. Testdb database will be created as shown in the following snapshot.

MS SQL Server - Select Database

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

Select your database based on your action before going ahead with any of the following methods.

Method 1 – Using SQL Server Management Studio

Example

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

To run a query to select backup history on database called ‘msdb’, select the msdb database as shown in the following snapshot.

Method 2 – Using T-SQL Script

Use <your database name>

Example

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

To run your query to select backup history on database called ‘msdb’, select the msdb database by executing the following query.

Exec use msdb

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

The query will open msdb database. You can execute the following query to select backup history.

Select * from backupset

MS SQL Server - Drop Database

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

To remove your database from MS SQL Server, use drop database command. Following two methods can be used for this purpose.

Method 1 – Using T-SQL Script

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

Following is the basic syntax for removing database from MS SQL Server.

Drop database <your database name>

Example

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

To remove database name ‘Testdb’, run the following query.

Drop database Testdb

Method 2 – Using MS SQL Server Management Studio

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

Connect to SQL Server and right-click the database you want to remove. Click delete command and the following screen will appear.

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

Click OK to remove the database (in this example, the name is Testdb as shown in the above screen) from MS SQL Server.

MS SQL Server - Creating Backups

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

Backup is a copy of data/database, etc. Backing up MS SQL Server database is essential for protecting data. MS SQL Server backups are mainly three types − Full or Database, Differential or Incremental, and Transactional Log or Log.

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

Backup database can be done using either of the following two methods.

Method 1 – Using T-SQL

Full Type

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

Differential Type

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

Log Type

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

Example

以下命令用于对名为“TestDB”的数据库进行完全备份，将“TestDB_Full.bak”备份文件存入“D:\”位置。

The following command is used for full backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_Full.bak'

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

以下命令用于对名为“TestDB”的数据库进行差分备份，将“TestDB_diff.bak”备份文件存入“D:\”位置。

The following command is used for differential backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_diff.bak'

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

以下命令用于对名为“TestDB”的数据库进行日志备份，将“TestDB_log.trn”备份文件存入“D:\”位置。

The following command is used for Log backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_log.trn'

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

Method 2 – Using SSMS (SQL SERVER Management Studio)

Step 1 - 连接到名为“TESTINSTANCE”的数据库实例，然后展开数据库文件夹，如下方的快照所示。

Step 1 − Connect to database instance named 'TESTINSTANCE' and expand databases folder as shown in the following snapshot.

Step 2 - 右键点击“TestDB”数据库，然后选择任务。点击备份，将会看到以下画面。

Step 2 − Right-click on 'TestDB' database and select tasks. Click Backup and the following screen will appear.

Step 3 - 选择备份类型（完全/差分/日志），并务必检查备份文件即将创建的目标路径。选择左上角的选项，以查看以下画面。

Step 3 − Select backup type (Full\diff\log) and make sure to check destination path which is where the backup file will be created. Select options at the top left corner to see the following screen.

Step 4 - 点击确定创建“TestDB”数据库的完全备份，如下方的快照所示。

Step 4 − Click OK to create 'TestDB' database full backup as shown in the following snapshot.

MS SQL Server - Restoring Databases

Restoring 是从备份中复制数据并将记录的交易应用于数据的过程。还原是您对备份执行的操作。获取备份文件并将其重新转变成数据库。

Restoring is the process of copying data from a backup and applying logged transactions to the data. Restore is what you do with backups. Take the backup file and turn it back into a database.

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

The Restore database option can be done using either of the following two methods.

Method 1 – T-SQL

Syntax

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

Example

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

The following command is used to restore database called 'TestDB' with backup file name 'TestDB_Full.bak' which is available in 'D:\' location if you are overwriting the existed database.

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

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

If you are creating a new database with this restore command and there is no similar path of data, log files in target server, then use move option like the following command.

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

Make sure the D:\Data path exists as used in the following command for data and log files.

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'

Method 2 – SSMS (SQL SERVER Management Studio)

Step 1 - 连接到名为“TESTINSTANCE”的数据库实例，右键单击数据库文件夹。单击恢复数据库，如下面的快照中所示。

Step 1 − Connect to database instance named 'TESTINSTANCE' and right-click on databases folder. Click Restore database as shown in the following snapshot.

Step 2 - 选择设备单选按钮，并单击椭圆点以选择备份文件，如下面的快照中所示。

Step 2 − Select device radio button and click on ellipse to select the backup file as shown in the following snapshot.

Step 3 - 单击确定，将弹出以下屏幕。

Step 3 − Click OK and the following screen pops up.

Step 4 - 如下面的快照中所示，选择左上角的文件选项。

Step 4 − Select Files option which is on the top left corner as shown in the following snapshot.

Step 5 − 选择左上角的“选择选项”，然后单击“确定”以还原“TestDB”数据库，如下图所示。

Step 5 − Select Options which is on the top left corner and click OK to restore 'TestDB' database as shown in the following snapshot.

MS SQL Server - Create Users

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

User refers to an account in MS SQL Server database which is used to access database.

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

Users can be created using either of the following two methods.

Method 1 – Using T-SQL

Syntax

Create user <username> for login <loginname>

Example

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

To create user name 'TestUser' with mapping to Login name 'TestLogin' in TestDB database, run the following query.

create user TestUser for login TestLogin

其中“TestLogin”是在登录名创建中创建的登录名

Where 'TestLogin' is the login name which was created as part of the Login creation

Method 2 – Using SSMS (SQL Server Management Studio)

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

Note − First we have to create Login with any name before creating a user account.

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

Let’s use Login name called 'TestLogin'.

Step 1 − 连接 SQL Server 并展开数据库文件夹。然后展开“TestDB”数据库（我们将在其中创建用户帐户），并展开安全文件夹。右键单击用户并单击新建用户以查看以下屏幕。

Step 1 − Connect SQL Server and expand databases folder. Then expand database called 'TestDB' where we are going to create the user account and expand the security folder. Right-click on users and click on the new user to see the following screen.

Step 2 − 在用户名字段中输入“TestUser”并单击省略号以选择名为“TestLogin”的登录名，如下面的快照所示。

Step 2 − Enter 'TestUser' in the user name field and click on ellipse to select the Login name called 'TestLogin' as shown in the following snapshot.

Step 3 − 单击确定以显示登录名。再次单击确定以创建“TestUser”用户，如下面的快照所示。

Step 3 − Click OK to display login name. Again click OK to create 'TestUser' user as shown in the following snapshot.

MS SQL Server - Assign Permissions

Permissions 参照涉及主体对可保护对象的访问级别规则。用户可以在 MS SQL Server 中授予、撤销和拒绝权限。

Permissions refer to the rules governing the levels of access that principals have to securables. You can grant, revoke and deny permissions in MS SQL Server.

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

To assign permissions either of the following two methods can be used.

Method 1 – Using T-SQL

Syntax

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

Example

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

To assign select permission to a user called 'TestUser' on object called 'TestTable' in 'TestDB' database, run the following query.

USE TestDB
GO
Grant select on TestTable to TestUser

Method 2 – Using SSMS (SQL Server Management Studio)

Step 1 - 连接到实例，然后展开文件夹，如下方的快照所示。

Step 1 − Connect to instance and expand folders as shown in the following snapshot.

Step 2 - 右键点击 TestUser，然后点击属性。将会看到以下画面。

Step 2 − Right-click on TestUser and click Properties. The following screen appears.

Step 3 点击搜索，并选择具体选项。点击对象类型，选择表格，然后点击浏览。选择“TestTable”，然后点击确定。将会看到以下画面。

Step 3 Click Search and select specific options. Click Object types, select tables and click browse. Select 'TestTable' and click OK. The following screen appears.

Step 4 如上方的快照所示，选择选择权限下授予列的复选框，然后点击确定。

Step 4 Select checkbox for Grant column under Select permission and click OK as shown in the above snapshot.

Step 5 选择授予“TestUser”对“TestDB”数据库中的“TestTable”的权限。点击确定。

Step 5 Select permission on 'TestTable' of TestDB database granted to 'TestUser'. Click OK.

MS SQL Server - Monitor Database

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

Monitoring refers to checking database status, settings which can be the owner’s name, file names, file sizes, backup schedules, etc.

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

SQL Server databases can be monitored mainly through SQL Server Management Studio or T-SQL, and also can be monitored through various methods like creating agent jobs and configuring database mail, third party tools, etc.

可以检查数据库的状态，即它是否在线或处于任何其他状态，如下面的截图所示。

Database status can be checked whether it is online or in any other state as shown in the following snapshot.

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

As per the above screen, all databases are in 'Online' status. If any database is in any other state, then that state will be shown as shown in the following snapshot.

MS SQL Server - Services

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

MS SQL Server provides the following two services which is mandatory for databases creation and maintenance. Other add-on services available for different purposes are also listed.

SQL Server
SQL Server Agent

Other Services

SQL Server Browser
SQL Server Full Text Search
SQL Server Integration Services
SQL Server Reporting Services
SQL Server Analysis Services

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

The above services can be availed using the following method.

Start Services

若要启动其中任何服务，可以使用下列两种方法：

To start any of the services, either of the following two methods can be used.

Method 1 – Services.msc

Step 1 − 转到运行，键入 services.msc 然后单击确定。将出现以下屏幕。

Step 1 − Go to Run, type services.msc and click OK. The following screen appears.

Step 2 − 若要启动服务，右键单击服务，单击启动按钮。服务将启动，如下面快照所示。

Step 2 − To start service, right-click on service, click Start button. Services will start as shown in the following snapshot.

Method 2 – SQL Server Configuration Manager

Step 1 − 使用以下过程打开配置管理器。

Step 1 − Open configuration manager using the following process.

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

Start → All Programs → MS SQL Server 2012 → Configuration Tools → SQL Server configuration manager.

Step 2 − 选择服务名称，右键单击，然后单击启动选项。服务将启动，如下面快照所示。

Step 2 − Select the service name, right-click and click on start option. Services will start as shown in the following snapshot.

Stop Services

若要停止其中任何服务，可以使用下列三种方法：

To stop any of the services, either of the following three methods can be used.

Method 1 - Services.msc

Step 1 − 转到运行，键入 services.msc 然后单击确定。将出现以下屏幕。

Step 1 − Go to Run, type services.msc and click OK. The following screen appears.

Step 2 − 若要停止服务，右键单击服务并单击停止。已选择的服务将停止，如下面快照所示。

Step 2 − To stop services, right-click on service and click Stop. The selected service will be stopped as shown in the following snapshot.

Method 2 – SQL Server Configuration Manager

Step 1 − 使用以下过程打开配置管理器。

Step 1 − Open configuration manager using the following process.

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

Start → All Programs → MS SQL Server 2012 → Configuration Tools → SQL Server configuration manager.

Step 2 − 选择服务名称，右键单击并单击停止选项。已选择的服务将停止，如下面快照所示。

Step 2 − Select the service name, right-click and click Stop option. The selected service will be stopped as shown in the following snapshot.

Method 3 – SSMS (SQL Server Management Studio)

Step 1 − 连接到实例，如下面快照所示。

Step 1 − Connect to the instance as shown in the following snapshot.

Step 2 − 右键单击实例名称并单击停止选项。将出现以下屏幕。

Step 2 − Right-click on instance name and click Stop option. The following screen appears.

Step 3 − 单击是按钮，将打开以下屏幕。

Step 3 − Click Yes button and the following screen will open.

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

Step 4 − Click Yes option on the above screen to stop SQL Server agent service. The services will be stopped as shown in the following screenshot.

Note

We cannot use the SQL Server Management Studio method to start the Services as unable to connect due to services already stopped state.
We cannot exclude stopping SQL Service agent service while stopping SQL Server service as SQL Server Agent Service is a dependent service.

MS SQL Server - HA Technologies

高可用性 (HA) 是在计划内或计划外中断下使应用程序/数据库 24x7 可用的解决方案/进程/技术。

High Availability (HA) is the solution\process\technology to make the application\database available 24x7 under either planned or un-planned outages.

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

Mainly, there are five options in MS SQL Server to achieve\setup high availability solution for the databases.

Replication

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

The source data will be copied to destination through replication agents (jobs). Object level technology.

Terminology

Publisher is source server.
Distributor is optional and stores replicated data for the subscriber.
Subscriber is the destination server.

Log Shipping

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

The source data will be copied to destination through Transaction Log backup jobs. Database level technology.

Terminology

Primary server is source server.
Secondary server is destination server.
Monitor server is optional and will be monitored by log shipping status.

Mirroring

源数据将通过镜像端点和端口号在网络事务基础上复制到辅助数据库。数据库级别技术。

The primary data will be copied to secondary through network transaction basis with the help of mirroring endpoint and port number. Database level technology.

Terminology

Principal server is source server.
Mirror server is destination server.
Witness server is optional and used to make automatic failover.

Clustering

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

The data will be stored in shared location which is used by both primary and secondary servers based on availability of the server. Instance level technology. Windows Clustering setup is required with shared storage.

Terminology

Active node is where SQL Services are running.
Passive node is where SQL Services are not running.

AlwaysON Availability Groups

源数据将通过网络事务基础复制到辅助服务器。数据库级别技术组。需要设置 Windows 群集而不用共享存储。

The primary data will be copied to secondary through network transaction basis. Group of database level technology. Windows Clustering setup is required without shared storage.

Terminology

Primary replica is source server.
Secondary replica is destination server.

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

Following are the steps to configure HA technology (Mirroring and Log shipping) except Clustering, AlwaysON Availability groups and Replication.

Step 1 − 对源数据库进行一次完全备份和一次事务日志备份。

Step 1 − Take one full and one T-log backup of source database.

Example

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

To configure mirroring\log shipping for the database 'TestDB' in 'TESTINSTANCE' as primary and 'DEVINSTANCE' as secondary SQL Servers, write the following query to take full and T-log backups on Source (TESTINSTANCE) server.

连接到 “TESTINSTANCE” SQL 服务器，打开新的查询并编写以下代码，然后执行，如下面的屏幕截图所示。

Connect to 'TESTINSTANCE' SQL Server and open new query and write the following code and execute as shown in the following screenshot.

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

Step 2 − 将备份文件复制到目标服务器。

Step 2 − Copy the backup files to destination server.

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

In this case, we have only one physical server and two SQL Servers Instances installed, hence there is no need to copy, but if two SQL Server instances are in different physical server, we need to copy the following two files to any location of the secondary server where 'DEVINSTANCE' instance is installed.

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

Step 3 − Restore the database with backup files in destination server with 'norecovery' option.

Example

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

Connect to 'DEVINSTANCE' SQL Server and open New Query. Write the following code to restore the database with the name 'TestDB' which is the same name of primary database ('TestDB') for database mirroring. However, we can provide different name for log shipping configuration. In this case, let’s use 'TestDB' database name. Use 'norecovery' option for two (full and t-log backup files) restores.

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”，还原状态如下图所示。

Refresh the databases folder in 'DEVINSTANCE' server to see restored database 'TestDB' with restoring status as shown in the following snapshot.

Step 4 − 根据需要配置 HA（日志传送、镜像），如下面的屏幕截图所示。

Step 4 − Configure the HA (Log shipping, Mirroring) as per your requirement as shown in the following snapshot.

Example

右键单击主 “TESTINSTANCE” SQL 服务器的 “TestDB” 数据库并单击“属性”。将出现以下屏幕。

Right-click on 'TestDB' database of 'TESTINSTANCE' SQL Server which is primary and click Properties. The following screen will appear.

Step 5 − 选择名为 “镜像” 或 “事务日志传送” 的选项，它们以红色框显示，正如上面的屏幕所示，并按照系统本身指导的向导步骤完成配置。

Step 5 − Select the option called either 'Mirroring' or 'Transaction Log Shipping' which are in red color box as shown in the above screen as per your requirement and follow the wizard steps guided by system itself to complete configuration.

MS SQL Server - Reporting services

Report 是一个可显示组件。

Report is a displayable component.

Usage

报告基本上用于两个目的——公司内部运营和公司外部运营。

Report is basically used for two purposes - Company Internal Operations and Company External Operations.

Reporting Services

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

This is a service which is used to create and publish various kinds of reports.

以下三项要求对于开发任何报告都是必需的。

Following are the three requirements necessary to develop any report.

Business process
Layout
Query\Procedure\View

BIDS（2008 R2 之前的商业智能工作室）和 SSDT（2012 年起的 SQL Server 数据工具）是开发报表的环境。

The BIDS (Business Intelligence Studio till 2008 R2) and SSDT (SQL Server Data Tools from 2012) are environment to develop reports.

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

Following are the steps to open BIDS\SSDT environment to develop reports.

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

Step 1 − Open either BIDS\SSDT based on the version from the Microsoft SQL Server programs group. The following screen will appear. In this case, SSDT has opened.

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

Step 2 − Go to file at the top left corner in the above screenshot. Click New and select project. The following screen will open.

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

Step 3 − In the above screen, select reporting services under business intelligence at the top left corner as shown in the following screenshot.

Step 4 − 在上图屏幕中，根据您开发报表的需要，选择报表服务器项目向导（它将通过向导逐步为您指导）或报表服务器项目（将用于选择自定义设置）。

Step 4 − In the above screen, select either report server project wizard (it will guide you step by step through wizards) or report server project (it will be used to select customized settings) based on your requirement to develop the report.

MS SQL Server - Execution Plans

查询优化器将在统计信息和代数/处理器树的帮助下生成执行计划。这是查询优化器的结果，并说明如何完成/执行你的工作/要求。

Execution plan will be generated by Query optimizer with the help of statistics and Algebrizer\processor tree. It is the result of Query optimizer and tells how to do\perform your work\requirement.

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

There are two different execution plans - Estimated and Actual.

Estimated execution plan 表示优化器视图。

Estimated execution plan indicates optimizer view.

Actual execution plan 表示执行了查询的内容以及如何执行。

Actual execution plan indicates what executed the query and how was it done.

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

Execution plans are stored in memory called plan cache, hence can be reused. Each plan is stored once unless optimizer decides parallelism for the execution of the query.

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

There are three different formats of execution plans available in SQL Server - Graphical plans, Text plans, and XML plans.

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

SHOWPLAN is the permission which is required for the user who wants to see the execution plan.

Example 1

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

Following is the procedure to view the estimated execution plan.

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

Step 1 − Connect to SQL Server instance. In this case, 'TESTINSTANCE' is the instance name as shown in the following snapshot.

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

Step 2 − Click on New Query option on the above screen and write the following query. Before writing the query, select the database name. In this case, 'TestDB' is database name.

Select * from StudentTable

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

Step 3 − Click the symbol which is highlighted in red color box on the above screen to display the estimated execution plan as shown in the following screenshot.

Step 4 - 将鼠标悬停在扫描表上，这是上面屏幕上红色框上方第二个符号，以查看详细的估计执行计划。出现以下截图。

Step 4 − Place the mouse on table scan which is the second symbol above the red color box in the above screen to display the estimated execution plan in detail. The following screenshot appears.

Example 2

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

Following is the procedure to view the actual execution plan.

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

Step 1 Connect to SQL Server instance. In this case, 'TESTINSTANCE' is the instance name.

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

Step 2 − Click New Query option seen on the above screen and write the following query. Before writing the query, select the database name. In this case, 'TestDB' is database name.

Select * from StudentTable

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

Step 3 − Click the symbol which is highlighted in red color box on the above screen and then execute the query to display the actual execution plan along with the query result as shown in the following screenshot.

Step 4 - 将鼠标悬停在屏幕上红色框上方第二个符号扫描表上，以查看详细的实际执行计划。出现以下截图。

Step 4 − Place the mouse on the table scan which is the second symbol above the red color box on the screen to display the actual execution plan in detail. The following screenshot appears.

Step 5 - 点击上面屏幕左上角的结果以获取以下屏幕。

Step 5 − Click Results which is on the left top corner on the above screen to get the following screen.

MS SQL Server - Integration Services

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

This service is used to carry out ETL (Extraction, Transform and Load data) and admin operations. The BIDS (Business Intelligence Studio till 2008 R2) and SSDT (SQL Server Data Tools from 2012) are the environments to develop packages.

SSIS Basic Architecture

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

Solution (Collection of projects) --→ Project (Collection of packages) --→ Package (Collection of tasks for ETL and admin operations)

在包下，以下组件可用：

Under Package, the following components are available −

Control Flow (Containers and Tasks)
Data Flow (Source, Transformations, Destinations)
Event Handler (Sending of messages, Emails)
Package Explorer (A single view for all in package)
Parameters (User interaction)

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

Following are the steps to open BIDS\SSDT.

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

Step 1 − Open either BIDS\SSDT based on the version from the Microsoft SQL Server programs group. The following screen appears.

Step 2 - 上述屏幕显示已打开 SSDT。转到上述图像左上角的文件，然后单击“新建”。选择项目并打开以下屏幕。

Step 2 − The above screen shows SSDT has opened. Go to file at the top left corner in the above image and click New. Select project and the following screen opens.

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

Step 3 − Select Integration Services under Business Intelligence on the top left corner in the above screen to get the following screen.

Step 4 - 在上述屏幕中，根据您开发\创建包的要求，选择“集成服务项目”或“集成服务导入项目向导”。

Step 4 − In the above screen, select either Integration Services Project or Integration Services Import Project Wizard based on your requirement to develop\create the package.

MS SQL Server - Analysis Services

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

This service is used to analyze huge amounts of data and apply to business decisions. It is also used to create two or multidimensional business models.

在 SQL Server 2000 版本中，该服务称为 MSAS（Microsoft Analysis Services，Microsoft 分析服务）。

In SQL Server 2000 version, it is called MSAS (Microsoft Analysis Services).

从 SQL Server 2005 开始，该服务称为 SSAS（SQL Server Analysis Services，SQL Server 分析服务）。

From SQL Server 2005, it is called SSAS (SQL Server Analysis Services).

Modes

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

There are two modes − Native Mode (SQL Server Mode) and Share Point Mode.

Models

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

There are two models − Tabular Model (For Team and Personal Analysis) and Multi Dimensions Model (For Corporate Analysis).

BIDS（商业智能设计工作室，直至 2008 R2）和 SSDT（SQL Server 数据工具，自 2012 年起）是用于 SSAS 的环境。

The BIDS (Business Intelligence Studio till 2008 R2) and SSDT (SQL Server Data Tools from 2012) are environments to work with SSAS.

Step 1 - 根据 Microsoft SQL Server 程序组中的版本，打开 BIDS\SSDT。将显示以下屏幕。

Step 1 − Open either BIDS\SSDT based on the version from the Microsoft SQL Server programs group. The following screen will appear.

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

Step 2 − The above screen shows SSDT has opened. Go to file on the top left corner in the above image and click New. Select project and the following screen opens.

Step 3 - 在上图左上角的商业智能下，选择上述屏幕中的分析服务。将弹出一个以下屏幕。

Step 3 − Select Analysis Services in the above screen under Business Intelligence as seen on the top left corner. The following screen pops up.

Step 4 - 在上图中，根据您的分析服务工作要求从所列的五个选项中选择一个选项。

Step 4 − In the above screen, select any one option from the listed five options based on your requirement to work with Analysis services.