如何创建SQL Server日志传送

In my last article, I show how to create Database Mirroring for high availability. This time, I will work on Log Shipping.

在上一篇文章中 ，我展示了如何创建数据库镜像以实现高可用性。 这次，我将从事日志传送。

The Log Shipping as the name says ships the Transactional Log from the Primary Servers to the Secondary Servers.

顾名思义，日志传送将事务日志从主服务器传送到辅助服务器。

The Log Shipping process consists in creating a Transaction Log Backup on the Primary Server, Copy the Log Backup to the secondary and restore the database.

日志传送过程包括在主服务器上创建事务日志备份，将日志备份复制到辅助服务器并还原数据库。

使用的服务器 ( Servers used )

The SQL Server Log Shipping, requires a Primary Server, which will be used as the main Server with a database. If it fails, the other server (Secondary Server) will be used to replace the Principal Server. Optionally you can use a third server to monitor the Log Shipping. Log shipping applies to a select database.

SQL Server日志传送需要主服务器 Server ，将用作数据库的主服务器。 如果失败，则将使用其他服务器（ 辅助 服务器 ）来替换主体服务器。 （可选）您可以使用第三台服务器来监视日志传送。 日志传送适用于选择的数据库。

Once the principal is fixed, you can return using the Principal server.

固定主体后，即可使用主体服务器返回。

If you want an automatic failover (when the principal server fails, the Mirror Server will be automatically activated to replace the principal server), a third Server is necessary.

如果要自动故障转移（当主体服务器发生故障时，镜像服务器将被自动**以替换主体服务器），则需要第三台服务器。

要求 ( Requirements )

1. You need three SQL Servers or at least three SQL Server Instances (three instances can be used for testing purposes only, but it is not recommended for production environments). In this sample we will use 2 Servers.
   您需要三个SQL Server或至少三个SQL Server实例（三个实例只能用于测试目的，但不建议在生产环境中使用）。 在此示例中，我们将使用2台服务器。
2. You can use SQL Server Enterprise, Standard, Business Intelligence or Web Edition for this article.
   您可以在本文中使用SQL Server Enterprise，Standard，Business Intelligence或Web Edition。
3. Create a folder and assign permissions to the SQL Server Agent Account to this folder on the principal server. In this example, the folder name will be logshipping in the c:\ drive.
   创建一个文件夹，然后将SQL Server代理帐户的权限分配给主体服务器上的该文件夹。 在此示例中，文件夹名称将在c：\驱动器中进行日志传送。
4. Create a folder and assign permissions to the SQL Server Agent Account to this folder on the secondary server. In this example, the folder name will be destination in the c:\ drive.
   创建一个文件夹，并将对SQL Server代理帐户的权限分配给辅助服务器上的该文件夹。 在此示例中，文件夹名称将位于c：\驱动器中。

入门 ( Getting Started )

1. In the SQL Server Primary Server, right click on the database to LogShip and select Tasks>Ship Transaction Logs.

   
   
   Figure 1. Ship Transaction Logs option

   在SQL Server主服务器中，右键单击数据库以选择LogShip，然后选择“任务”>“ Ship事务日志”。

   
   图1.发货交易日志选项

2. Press The Backup Settings option

   
   
   Figure 2. Ship Transaction Logs option

   按“备份设置”选项

   
   图2 。 运送交易日志选项

3. Specify the network path and the local path in the primary server of the folder where the Transaction log backups will be stored. You can rise an alert if the backup does not occur and delete older files within a determined time. The Job name is LSBackup_db (Log Shipping Backup). With the Schedule button you can Schedule de backup every to the time of your preference. By default, it is scheduled every 15 minutes. You can also Disable the job with this Window or compress the backup.

   
   
   Figure 3. Setting up the folder with backups

   在将存储事务日志备份的文件夹的主服务器中指定网络路径和本地路径。 如果未进行备份，则可以发出警报，并在确定的时间内删除较旧的文件。 作业名称为LSBackup_db（日志传送备份）。 使用计划按钮，您可以根据自己的喜好计划每个备份时间。 默认情况下，每15分钟安排一次。 您也可以使用此窗口禁用作业或压缩备份。

   
   图3 。 小号 ETTING了备份的文件夹

4. In order to add a secondary Server, press the Add button. You can add multiple Secondary Servers in Log Shipping. Additionally, you can use a third instance to monitor the Log Shipping process by checking the Use a monitor server instance and specifying the settings. In this sample, we will not include a Server Monitor. In this example, we are not going to include a Monitor.

   
   
   Figure 4. Setup the backup settings

   为了添加辅助服务器，请按添加按钮。 您可以在日志传送中添加多个辅助服务器。 此外，可以通过选择“使用监视服务器实例”并指定设置，使用第三个实例监视日志传送过程。 在此示例中，我们将不包括服务器监视器。 在此示例中，我们将不包括监视器。

   
   图4.设置备份设置

5. In the Secondary Database Setting Window, press Connect and specify your Secondary Server credentials. Once connected, the options to initialize the secondary database will be enabled.

   
   
   Figure 5. Connect to the secondary server

   在“辅助数据库设置”窗口中，按“连接”并指定辅助服务器凭据。 连接后，将启用用于初始化辅助数据库的选项。

   
   图5. 连接到辅助服务器

6. In the Initialize Secondary Database Tab, if you do not have a backup already created on the secondary machine from the primary machine, select the option yes to generate a full backup. If you already have a backup, select the option Yes, restore an existing backup. With the Restore Options, you can select the restore configurations. If you have an already restored database on the secondary server, select the No, the secondary database is initialized. In this example, the first option will be used.

   
   
   Figure 6. Initialize Secondary Database Settings.

   在“初始化辅助数据库”选项卡中，如果尚未从主计算机在辅助计算机上创建备份，则选择“是”以生成完整备份。 如果您已经有备份，请选择选项“是，还原现有备份”。 使用还原选项，您可以选择还原配置。 如果辅助服务器上的数据库已经还原，则选择“否”，将初始化辅助数据库。 在此示例中，将使用第一个选项。

   
   图6.初始化辅助数据库设置。

7. In the Copy Files tab, select the destination folder. In the destination folder, you will have the Transaction log backups created and copied from the primary server. You can delete copied files after a specified time. The folder was created in the requirements of this document. You can delete files copied after a specified period and schedule the copies according to your preferences. The job created will be LSCopy_MachineName_DatabaseName. By default, the copy is programmed every 15 minutes.

   
   
   Figure 7. Copy Files Tab

   在“复制文件”选项卡中，选择目标文件夹。 在目标文件夹中，您将创建并从主服务器复制事务日志备份。 您可以在指定时间后删除复制的文件。 该文件夹是在本文档的要求中创建的。 您可以删除指定时间后复制的文件，并根据自己的喜好安排复制时间。 创建的作业将是LSCopy_MachineName_DatabaseName。 默认情况下，每15分钟对副本进行一次编程。

   
   图7. 复制文件选项卡

8. The third tab is used to restore the transactional logs. There are two restoring options:
   • No recovery mode. Is an option to restore the Transaction Logs in a non-recovery status. This option is faster because it does not need to analyze the uncommitted transactions. This option does not allow querying the database from the Secondary Server.

   • Standby mode. This option allows you to have a read only Database on the secondary database, this option has a higher overhead that the non-recovery node, but it has the benefit that you can access the Database. You can also disconnect the users when restoring the backups to increase the performance. In this example, we will use this option.

   You can also have a delay restoring backups. That is in case that the principal server has an error and we do not want to restore the backup with that problem. You also can configure an alert if the restoration fails.

   The job name is LSRestore and you can schedule the restore option with the schedule button according to your needs.

   By default, the restoration is every 15 minutes.

   
   
   Figure 8. Restore Transaction Log settings
   • 没有恢复模式。 是用于以非恢复状态还原事务日志的选项。 此选项速度更快，因为它不需要分析未提交的事务。 此选项不允许从辅助服务器查询数据库。

   • 待机模式。 此选项使您可以在辅助数据库上拥有一个只读数据库，该选项的开销比非恢复节点高，但是它具有可以访问数据库的优点。 您还可以在还原备份时断开用户连接，以提高性能。 在此示例中，我们将使用此选项。

   您还可能会延迟恢复备份。 在这种情况下，如果主体服务器出现错误，并且我们不希望由于该问题而还原备份。 如果还原失败，您还可以配置警报。

   作业名称为LSRestore，您可以根据需要使用“计划”按钮计划还原选项。

   默认情况下，恢复是每15分钟进行一次。

   
   图8.恢复事务日志设置

9. If everything is OK, you will receive Success status messages. The typical error messages are related to folder permissions. If you have problems, make sure that the agent service has permissions to the folders configured.

   
   
   Figure 9. The success message.

   如果一切正常，您将收到成功状态消息。 典型的错误消息与文件夹权限有关。 如果遇到问题，请确保代理服务具有对配置的文件夹的权限。

   
   图9. 成功消息。

10. If everything is OK, you will be able to see a Standby / read only database on the secondary server.

    
    
    Figure 10. The read only database on the Secondary Server

    如果一切正常，您将能够在辅助服务器上看到一个Standby /只读数据库。

    
    图10. 辅助服务器上的 只读 数据库

Now you are ready. You have a Read only database that synchronizes with the primary Database.

现在您准备好了。 您有一个与主数据库同步的只读数据库。

If you update information on tables of the Primary Server in the db1 database, this information will be syncronized on the secondary server after some minutes (depending on the schedules that you programmed to backup, copy and restore).

如果您更新db1数据库中“主服务器”表上的信息，则几分钟后（取决于您计划备份，复制和还原的时间表），该信息将在辅助服务器上同步化。

If you go to the SQL Server Agent of the primary database, you will see LSAlert job which is an alert if the backup fails and the LSBackup to create a backup of the primary database.

如果转到主数据库SQL Server代理，您将看到LSAlert作业，如果备份失败，它将发出警报，并显示LSBackup创建主数据库的备份。

The secondary Server has 3 jobs associated, the LSAlert if the restoration fails, the LSCopy to copy the backups from the primary server to the secondary server and finally the LSRestor to restore the database.

辅助服务器具有3个关联的作业：如果还原失败，则执行LSAlert；通过LSCopy将备份从主服务器复制到辅助服务器，最后通过LSRestor来还原数据库。

翻译自: https://www.sqlshack.com/create-sql-server-log-shipping/