Ms Sql Server 简明教程

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

  1. Publisher is source server.

  2. Distributor is optional and stores replicated data for the subscriber.

  3. Subscriber is the destination server.

Log Shipping

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

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

Terminology

  1. Primary server is source server.

  2. Secondary server is destination server.

  3. 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

  1. Principal server is source server.

  2. Mirror server is destination server.

  3. 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

  1. Active node is where SQL Services are running.

  2. 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

  1. Primary replica is source server.

  2. 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'
ha technology1

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.

ha technology2

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
ha technology3

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

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

ha technology4

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.

ha technology5

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.