SqlServer log transmission and sqlserver log Transmission

SqlServer log transmission and sqlserver log Transmission

You can use Log transfer to continuously send transaction logs from one database (primary database) to another database (secondary database ). The transaction logs in the primary database are continuously backed up, and then copied and restored to the secondary database, which basically keeps the secondary database synchronized with the primary database. The target server acts as the backup server and can re-allocate query processing from the master server to one or more read-only secondary servers. Log transmission can be used with databases that use the full or large-capacity log recovery mode.

Log transmission involves four tasks processed by a dedicated SQL Server proxy job. These jobs include backup jobs, copy jobs, restore jobs, and alert jobs.

Backup job:Create a backup job for each primary database on the primary server instance. It performs backup operations, records historical information to the local server and monitoring server, and deletes old backup files and historical records.

 

Copy a job:Creates a copy job for each secondary server instance. This job copies the backup file from the master server to the secondary server, and records the history in the secondary server and monitoring server.

 

Restore job:Create a restoration job for each log shipper configuration on the secondary server instance. This job restores the copied backup file to the secondary database. It records historical records on the local server and monitoring server, and deletes old files and historical records.

 

Alarm job:There are two scenarios for enabling alarm jobs: one is to use alarm jobs on a separate server, and the other is to use alarm jobs in the primary and secondary libraries without the alarm server.

Basic Organization

Note: The database reply mode must be in full or large capacity mode because log backup is required. Therefore, if you have other backup plans, you should consider full backup or differential backup. Do not back up logs!

Note that the Proxy account has the permission to read and write files.

Now configure log Shipper:

1. Right-click the database to which log transfer will be enabled and click Properties to openDatabase attributes, SelectEnable this database as the master database in the log shipper ConfigurationThat is, log transmission is enabled. First, configure backup. ClickBackup Settings.

2. OpenedTransaction Log Backup Settings, Enter the backup folder, and the log Backup will be backed up to this folder. ClickEdit a jobTo set the backup plan for the transaction date. You can set itCompressed backup. ConfirmBackup Settings.

3. ConfigureSecondary Database, InSecondary DatabaseClickAddYou can add multiple secondary databases.

4. ConnectSecondary database instanceAnd selectSecondary DatabaseIf the secondary database does not exist, you can back up and initialize the secondary database. InInitialize secondary DatabaseSelect "generate primary database backup and restore ",Restore optionsYou can set the storage directories of data files and log files in the secondary database.

5. ClickCopy a fileOption.Copy the target folder of the fileAnd copyPlan.

6. ClickRestore transaction logsOption. The secondary database has two status settings.

No recovery mode:The secondary database cannot be accessed, and the database remains restored.

Backup mode:Secondary databases are readable, but there are also two cases during restoration. One is to force the user to be disconnected when the database is restored, and the other is to restore the database only when the user is disconnected.

If you change the recovery mode of the existing secondary database (for example, from the "no recovery" mode to the "standby" mode), the change takes effect only after the next log backup is restored to the database.

7. After the configuration is complete, confirm to exit the database attribute box. If the monitor is enabled, selectMonitor server instances, ClickSet.

8. Enable the log shipper monitor settings and connectMonitor server instances, Confirm to exit.

9. Confirm to exitDatabase attributes, Log transmission is enabled and configured!

10. Now you can seeSecondary DatabaseAfter being created,Restoring..."Status, four related jobs are also created and running,Log transfer configuration is complete!

11. To view the current execution status of log shipper, you can use the system's built-in report to view it.(Right-click) server instance -- Report -- flag report -- transaction log transmission status

You can use tables in msdb to view basic information:

-- Select * from msdb for the primary database. dbo. log_shipping_monitor_primary -- select * from msdb. dbo. log_shipping_primary_secondariesselect * from msdb. dbo. log_shipping_secondaryselect * from msdb. dbo. log_shipping_secondary_databasesselect * from msdb. dbo. log_shipping_monitor_secondary -- log Transfer Alarm job idselect * from msdb. dbo. log_shipping_monitor_alertselect * from msdb. dbo. sysjobs where job_id = ''-- Detailed operation history select * from msdb. dbo. log_shipping_monitor_history_detail -- select * from msdb. dbo. log_shipping_monitor_error_detail

If you delete log shipper, deselect the check box. Enable this database as the master database in the log shipper Configuration!

Reference: log Transmission