SQL Transaction Log Transport

Original: SQL transaction log Transport

Overview

You can use log shipping to send transaction logs uninterrupted from one database (primary database) to another (secondary database). Continuously back up the transaction logs in the primary database, and then copy and restore them to the secondary database, which basically keeps the secondary database in sync with the primary database. The target server acts as a backup server, and the query processing can be reassigned from the primary server to one or more read-only secondary servers. Log shipping can be used with a database that uses the full or bulk-logged recovery model; With log shipping, you can automatically send transaction log backups from the primary database on the primary server instance to one or more secondary databases on a separate secondary server instance. Transaction log backups are applied to each secondary database, respectively. An optional third server instance, called the monitoring server, records the history and status of backup and restore operations, and can also raise alerts when these operations cannot be performed on a schedule

Operation composition

1. Back up the transaction log in the primary server instance.
2. Copy the transaction log files to the secondary server instance.
3. Restore the log backups in the secondary server instance.

Primary server and Database

The primary server in the log shipping configuration is an instance of the SQL Server database engine that is the production server. The primary database is the database on the primary server that you want to back up to a different server. All log shipping configuration management through SQL Server Management Studio is performed in the primary database.

The primary database must use the full or bulk-logged recovery model, and switching the database to the simple recovery model will cause log shipping to stop working.

Secondary server and database

The secondary server in the log shipping configuration is the server where you want to keep the standby copy of the primary database. A secondary server can contain backup copies of databases from several different primary servers. For example, a department might have five servers, and each server would run a critical database system. In this case, you can use only one secondary server without having to use five separate secondary servers. Backups on five primary systems can be loaded into this backup system, reducing the amount of resources required and saving money. It is unlikely that multiple primary systems will fail at the same time. In addition, to cope with the rare cases where multiple primary systems are not available at the same time, the secondary server can have a higher specification than the primary server.

The secondary database must be initialized by means of a full backup of the primary database. You can use the NORECOVERY or STANDBY option when restoring. This can be done either manually or through SQL Server Management Studio.

Monitoring Server

The monitoring server is optional and can track all the details of log shipping, including:

• • The time the transaction log was last backed up in the primary database.
  • The last time the secondary server copied and restored the backup file.
  • Information about any backup failure alerts.

The monitoring server should be independent of the primary and secondary servers to avoid loss of critical information and interrupt monitoring due to loss of primary or secondary servers. A single monitoring server can monitor multiple log shipping configurations. In this case, all log shipping configurations that use the monitor server will share an alert job.

Step method

Before you configure the database, first make a full backup of the primary database, and then restore it on the secondary server, and restore the script as follows, keeping the database in the restoring state (NORECOVERY)

RESTORE DATABASE [Test]  from  DISK =N'D:\Microsoft SQL Server\mssql10. Mssqlserver\mssql\backup\test.bak'  with  FILE = 1, MOVE N'Test'  toN'D:\Microsoft SQL Server\mssql10. Mssqlserver\mssql\usedata\test.mdf', MOVE N'Test_log'  toN'D:\Microsoft SQL Server\mssql10. Mssqlserver\mssql\usedata\test_1.ldf', NORECOVERY, Nounload, STATS= Ten

1. Configure the primary database

Select the database that requires log transfer, right-click Properties-Transaction Log Transfer-tick "enable this database as the primary database in the log transfer configuration"

Click "Backup Settings", this process is to create a master database of the backup plan (lsback_test123), before you have to create a local computer for the main database of the backup folder, and you want to share this folder for the secondary server access, Folder permissions are set to everyone to read and write, and finally add a guest group's read permissions, configured as follows, and you can also schedule backup jobs, depending on the backup requirements

Backup Schedule

The main thing to note about configuring the primary database is the read and write permissions of the shared folder.

2. Configuring the secondary server and database

Click Add to add a secondary database

Connect the secondary server (I am here to test the connection is the local server, actually need to connect to the secondary server)

Select Copy file (This step creates a replication schedule "lscopy_c-3a89048af8df4_test123" to copy the log backup file for the primary database to the folder set in the secondary server)

First, in the secondary server (if it is: 192.168.1.3) to establish a shared folder, the folder permissions need to be able to read and write, can be set to everyone can read and write permissions

The planned schedule for this step can actually be set to the shortest time, that is, the primary database backup completes immediately replication

Restore the transaction log (this step creates a "lsrestore_c-3a89048af8df4_test123" of the original plan, restores the copied log file to the secondary server, and optionally selects whether the database state truncates the user connection)

Also the original plan time can be set to a shorter interval, can be the first time to restore the backup

The entire secondary server is configured to complete, so the same problem is the secondary server folder sharing problems, there will be no copy of the problem

3. Monitoring Server Configuration

Monitoring server is not a required option that can be configured without configuration, this process is to create a monitoring job (alert job) "LSALERT_C-3A89048AF8DF4"

Summary

Transaction log transport compared to replication subscriptions: Transaction log transfer operations are simpler and replication subscriptions are more specific;

Transaction log transfers are compared to database mirroring: Transaction log transfers are less expensive than database mirroring, and transaction log transfers can also be performed on a single DB instance,

State diagram:

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

SQL Transaction Log Transport