SQL Server Log Shipping
SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.
In this Topic:
In this Topic:
- Benefits
- Terms and Definitions
- Log Shipping Overview
- Interoperability
- Related Tasks
Benefits:---
- primary server
- The instance of SQL Server that is your production server.
- primary database
- The database on the primary server that you want to back up to another server. All administration of the log shipping configuration through SQL Server Management Studio is performed from the primary database.
- secondary server
- The instance of SQL Server where you want to keep a warm standby copy of your primary database.
- secondary database
- The warm standby copy of the primary database. The secondary database may be in either the RECOVERING state or the STANDBY state, which leaves the database available for limited read-only access.
- monitor server
- An optional instance of SQL Server that tracks all of the details of log shipping, including:
- When the transaction log on the primary database was last backed up.
- When the secondary servers last copied and restored the backup files.
- Information about any backup failure alerts.
Important Once the monitor server has been configured, it cannot be changed without removing log shipping first. - Backup job
- A SQL Server Agent job that performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. When log shipping is enabled, the job category "Log Shipping Backup" is created on the primary server instance.
- Copy job
- A SQL Server Agent job that copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. When log shipping is enabled on a database, the job category "Log Shipping Copy" is created on each secondary server in a log shipping configuration.
- Restore job
- A SQL Server Agent job that restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information. When log shipping is enabled on a database, the job category "Log Shipping Restore" is created on the secondary server instance.
- Alert job
- SQL Server Agent job that raises alerts for primary and secondary databases when a backup or restore operation does not complete successfully within a specified threshold. When log shipping is enabled on a database, job category "Log Shipping Alert" is created on the monitor server instance.
Tip For each alert, you need to specify an alert number. Also, be sure to configure the alert to notify an operator when an alert is raised.
Log Shipping Overview
The following figure shows a log shipping configuration with the primary server instance, three secondary server instances, and a monitor server instance. The figure illustrates the steps performed by backup, copy, and restorejobs, as follows:- The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. In this figure, the backup folder is on a shared directory—the backup share.
- Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.
- Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.
Logshipping
1) What is Log shipping and purpose of Log shipping?
To achieve high availability and high protection, Log shipping copies transactions from a ‘primary server’ to one or more ‘secondary servers’. In Log shipping, T.Log backups are sent to one or more secondary servers and then restored to the destination servers individually. If the Primary database becomes unavailable, any of the secondary database can brought into online manually. The Secondary server acts as a Backup server and provides read-only query processing to reduce the load on the Primary server. (For query processing, secondary servers should be configure in stand-by mode).
2) What is Primary Server, Secondary Server & Monitor Server?
a) Primary Server:- Primary Server is a Production server which holds the original copy of the database. Log shipping configuration and administrating will be done from Primary Server.
b) Secondary Server:- Secondary servers hold the standby copy of the database. We must initialize the DB on a secondary server by restoring a backup from the Primary server using either NORECOVERY option or the STANDBY option. By using STANDBY option Users can have read-only access to it.
c) Monitor Server:- An optional Server is called as Monitor Server that records history and status of backup, copy and restore operations and raises alerts if any operations fail. The Monitor Sever should be on separate server to avoid losing critical information. Single Monitor Server monitors multiple Log shipping configurations.
3) What are the Jobs running for Log shipping and explain them?
Log shipping having four operations which are handled by SQL Server Agent Job.
a) Backup Job: - Backup job is created on Primary Server instance and it performs backup operation. It logs history on the local server and monitor severs and deletes old backup files and history information.
b) Copy Job: - Copy Job is created on Secondary server instance and it performs copies the backup files from primary sever to secondary server. It logs history on the secondary server and monitor server.
c) Restore Job: - Restore Job is created on the Secondary server instance and it performs restore operation. It logs history on the local server and monitor sever and deletes old files and history information.
d) Alert Job: - If a Monitor Server is used, the Alert Jobs is created on the Monitor server instance and it raises Alerts if any operations have not completed successfully.
4) Requirements for Log shipping?
a) SQL Server 2005 Standard Edition, Workgroup Edition, or Enterprise Edition must be installed on all server instances involved in log shipping.
b) All servers should have the same case sensitivity settings.
c) The databases must use the full recovery model or bulk-logged recovery model.
5) How to configure Log shipping?
a. Choose Primary Server, Secondary Servers, and optional Monitor server.
b. Create a file share to keep Transaction log backups (best to place on a separate computer)
c. Create a folder on each Secondary server where transaction log backups can be copied.
d. Choose Backup Schedule for Primary Database.
e. Choose Copy and Restore Schedules for Secondary Database
f. Choose Alert Job schedule for Monitor Server if configured
6) What are permissions required for Log shipping?
We must have sysadmin on each server instance to configure Log shipping.
7) In Logshipping which Recovery Models can we used?
We can use either full or bulk logged recovery model for log shipping.
8) Where you monitoring Log shipping and how?
The following methods can use for monitoring Log shipping.
a) Monitor server (History Tables):- Monitor Server tracks all statistics, status and errors that could be happen during Log shipping.
1) Log_shipping_monitor_primary:- Stores primary server status
2) Log_shipping_monitor_secondary:- Stores secondary servers status
3) Log_shipping_monitor_history_detail:- Contains history details for logshipping agents.
4) Log_shipping_monitor_error_detail:- Stores error details for log shipping jobs.
5) Log_shipping_monitor_alert:- Stores Alert Job ID
b) System Stored Procedures (MSDB):- System Stored procedures gives the history information about the specified server that are configured in Log shipping.
1) sp_help_log_shipping_monitor (Run at Monitor Server)
2) sp_help_log_shipping_monitor_primary @Primary_Database = ‘DBName’ (Run at MS)
3) sp_help_log_shipping_monitor_secondary @ Secondary_Database = ‘DBName’ (Run at MS)
4) sp_help_log_shipping_alert_job (Run at Mon Server)
5) sp_help_log_shipping_primary_database @ Database = ‘DBName’ (Run at Primary Server)
6) sp_help_log_shipping_secondary_database @ Database = ‘DBName’ (Run at Sec Server)
c) Transaction Log shipping Status report (Summary Reports):- This report shows the status of log shipping configurations for which this server instance is a primary, secondary or monitor.
d) SQL Server Agent Job Histor:- Right click on Jobs > View history
e) Checking the SQL Server Log
9) How to failover secondary server, when the Primary Server fails?
If the Primary Server will become un-available, do the following steps.
a) Take the Tail of Log from Primary server if possible.
b) Restore Tail of log into all Secondary Database
c) Remove Log-shipping configuration from Primary Server
d) Select any one of Secondary server and bring into online with Alter Database DBName set Online
e) Right click on Primary Database and Generate script for Users and Logins.
f) Then move the script to Secondary server to create Users and Logins
g) Re-configure log shipping from New Server (Secondary server)
10) What are errors occurred in Log shipping?
There are two errors are occurred during Log shipping
1) 14420:- This error occurs when the Backup job fails
2) 14421:- This error occurs when the Restoring job fails
