Log Shipping By Abi Chapagai (Revised)
In SQL Server, transaction log provides an ability to recover a database to a point in time. Log shipping is a process of copying the transaction log back up from a source server to a destination server and restore on the destination server. The main objective of implementing log shipping is to maintain two SQL Server database synchronized in separate locations for high availability.
Basic Components of Log shipping are:
Log Shipping Demo:
In this example, I have used two instances for log shipping.
Primary Server Instance: ABI-PC
Secondary Server Instance: ABI-PC\SQLINSTANCE
Monitor Server Instance: ABI-PC\SQLINSTANCE
3. In Database Properties Window, Click on Enable this as a Primary database in a log shipping configuration, as shown below.
4. Click on Backup Settings…. Here you can schedule the transaction log back up in the primary server.
5. Provide the backup folder where you can store the Transaction Log Backup of UserDB database on the primary server (in my case this is just one server but have different instance for Primary and Secondary database and have different drive for the backups). You can either keep in a Local Drive or Network Path. It is shown below.
6. Click on Schedule tab to schedule the transaction Log Back up on the Primary Server. I have scheduled this backup to run every 15 minutes as shown in the figure below.
7. Few things to note in Job Schedule Properties Window and they are:
8. Click OK.
9. Click OK on Transaction Log Backup Settings window.
Adding Secondary Database Server in Mirroring Session:
10. In the Database Properties Window, Click on Add button as shown below.
11. When you click Add, the Secondary Database Settings window comes where the secondary database instance comes, as shown below
12. Click Connect button on this Connect to Server Window and will be connected to the Secondary Instance.
13. There are three options on the Secondary Database Settings window as shown in the figure below.
In Initialize Secondary Database, restore of the primary database on the secondary database is initializing. In my case, I have generated full database backup on the Primary Server UserDB database and apply that backup in the Secondary Instance. In this option, if the secondary database does not exist, it will create a new UserDB database.
14. In the Second Tab, Copy files options will enable to copy the Transaction Log backup from the primary server to the secondary server. In my case, I have the same sever but kept another drive for this purpose. This will be a destination folder for the copied files, and usually this folder will be in the secondary server. Copy job will run to copy the transaction log backup to copy the files to the secondary server and it scheduled to run in every 15 minutes. Figure is shown below:
15. Restore transaction Log tab will have the transaction log backup details. There are two modes: No Recovery Mode and Standby Mode. Always, NO RECOVERY mode should be selected because in standby mode, SELECT statement can be used to retrieve some data from the UserDB database tables, therefore the restore will fail since the database will be in use. When the restore mode is in NO RECOVERY MODE users will not be able to connect to the database and transaction log restore will not fail. Restore of transaction log is scheduled to run every 15 minutes.
To add Monitor Server Instance:
16. In the database properties window, add the Monitor Server Instance, Check the Use Monitor Server Instance Check Box and then click on Settings as shown in the figure below. I have used my Secondary Server Instance as the Monitor Server Instance (ABI-PC\SQLINSTANCE). Monitor server is an optional server instance.
17. After you click Settings, Log Shipping Monitor Settings Window will come. In this Window, Click on Connect button, Monitor Server Instance comes, in my case, ABI-PC\SQLINSTANCE, is the monitor server instance, as shown in the figure below.
18. Backup, copy and restore jobs are connected on this server instance. Alert job alerts to the users or database administrators if the log shipping fails. Connection used in monitor server is by Impersonating the Proxy Account of the job. Alert job starts automatically when the SQL Server Agent starts.
19. Click OK on the Monitor Server Instance Settings.
20. Finally, Click OK on Database Properties window to initiate Log Shipping session, final window will look like the following:-
Finally, log shipping is set up and it should up and running now.
How to Check Log Shipping Status:
MSDB database tables on secondary store the information. SQL Server 2005 has a capability to provide a report on log shipping status on both primary and secondary servers. Following approach is used to check the log shipping status:
Fail Over to Secondary Server:
In the event of any hardware or Operating system failure in the primary server, following steps will be implemented to bring the secondary server database online. This process will be implemented in real disaster recovery scenario. Follow the following steps:
RESTORE DATABASE [UserDB] WITH RECOVERY
Log shipping technology is one of the methods for implementing High Availability solution. Log shipping is easy to set up, standby database can be available for reporting purpose, easy to maintain and troubleshoot, and multiple standby databases can be configured for log shipping in the same server or instance. One of the main issues or concern with log shipping is adding overhead to the disk subsystem because of frequent transaction log backup. More frequent the transaction log backup taken, the more overhead will be added to the server disk subsystem, and this leads system slow down.