SQL Server transaction Log Transport

Basic concepts　

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 databases that use the full or bulk-logged recovery model.

Log shipping consists of three operations:

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.

Logs can be routed to multiple secondary server instances. In these cases, action 2 and action 3 are repeated for each secondary server instance.

The log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, you can bring any of the secondary databases online manually.

You can use the secondary database for reporting purposes.

Configuration process

　　1, first need to create in the secondary database for backup of the shared directory logbackup, mainly for storing from the main data backup log files, placed on the secondary database server convenient to read the backup log files, such as

　　

2, add the directory trust (this is important, because the shared directory after the primary data server can read and write to the shared directory, but later in the transfer of logs through SQL Server always because of the problem of permissions failure)

First, the xp_cmdshell feature of SQL Server is enabled and can be enabled through the command, and the following script is executed through Management Studio:

1 ' Show advanced Options ',12reconfigure3go4'xp_cmdshell  ',15reconfigure6Go

However, script execution only works this time, and the next time the database restarts is invalidated, the xp_cmdshell can be permanently enabled through the interface settings:

Right-click on "SQL Server Database"----Click general--Select surface Area Configurator--then change the Cmdshell property to True, as

　　

　　

After you have done the above steps, start the Directory Trust command through Management Studio:

　　

exec ' net use \\172.16.3.79\logbackup [email protected] #1/user:172.16.3.79\enadmin ';

\\172.16.3.79\logbackup--is a shared directory

172.16.3.79\enadmin--the secondary database server shared directory user

[Email protected] #1--User's password

3. Add firewall programs and port exceptions in the secondary database service (SQL Server studio and port 1433):

Go to Firewall--click Advanced Configuration--new rule--Step Add program and port exceptions, here is very simple to do not explain, the program selected directory (C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver \mssql\binn\sqlservr.exe).

4. Start execution of log transfer configuration:

First, open the properties interface of the primary database, select transaction log Transfer, and click to select the check box to enable log transfer.

　　

Then pop up the primary database log backup related configuration, the network path of the backup folder select the shared directory created before, the backup job can edit the selection according to the actual situation.

　　

Then, to add the secondary data, the secondary database can add more than one, in the Initialize secondary database interface, we choose to generate a full backup in the primary database, restore to the secondary database (the secondary data does not exist, it is created).

In the Restore option, you need to select a folder to hold the database files and log files, which are directories on the secondary database server.

　　

Some tasks for configuring replication in the Copy file interface, where the task is primarily to copy the log backup files of the primary database to the shared directory of the secondary database service, such as

　　

In the Restore transaction log interface, configure the associated configuration and task schedule for restoring the backup log, in which the database has 2 States, no recovery mode, and Standby mode, and the main difference is that no recovery mode allows users to connect to database queries. The standby mode allows the user to query the data (but not allow queries during a database restore). Generally select alternate mode, and select Disconnect users from the database when restoring a backup.

There are two ways to configure the secondary database in standby mode:
When you restore a transaction log backup, you can choose to disconnect the database user. If this option is selected, the Log shipping restore job will disconnect from the database each time it tries to restore the transaction log to the secondary database. Disconnecting will occur according to the schedule set for the restore job.
You can choose not to disconnect from the user. In this case, if the user connects to the secondary database, the restore job cannot restore the transaction log backup to the secondary database. Transaction log backups will accumulate until no users are connected to the database.

5, the last click on the "OK" button, the primary database will be backed up and restored to the secondary database server, this step may be performed for a long time, as long as no error is OK, and finally can see the secondary database server more than a "standby/read-only" database, such as:

　　

　　

6. Note that because backup, copy, and restore of all logs are scheduled tasks, SQL Server Agent is enabled on both the primary and secondary database servers.

　

　　

SQL Server transaction Log Transport