SQL Log Shipping – by Abi Chapagai

 

SQL Server 2008 High Availability Solution: Log Shipping

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:

1. Primary server
2. Secondary Server
3. Monitor Server

 Primary Server:

• Server where the primary database resides.
• It is the main database server and the primary database will be accessible for the applications.
• Database in this server should be either in full recovery model or bulk-logged recovery model for the transaction log shipping.
• Full backup of the database is taken on this server and copied over to stand by server.
• Transaction log backup are taken on periodic basis and copied over to the secondary server.

Secondary Server:

• This is another server used in the log shipping technology.
• This server will have a back up copy of the primary database used for log shipping.
• Secondary database server can be one or more than one as per the need of the business.
• Secondary database can be in Standby Mode or No Recovery mode.
• Secondary database is not accessible during the transaction log shipping process.
• Always,  NO RECOVERY mode should be used so that transaction logs can be restored on the secondary database since in Standby mode users can use SELECT command and logs will not be restored if the users are connected to the database which is in restoring state.

Monitor Server:

• This is another database engine server that tracks the log shipping process.
• It contains set of jobs and sends alert if the transaction log shipping sessions is perceived to be out of sync.
• This server is an optional component of log shipping.

 

Log Shipping Demo:

In this example, I have used two instances for log shipping.

Primary Server Instance:  ABI-PC

Secondary Server Instance: ABI-PCSQLINSTANCE

Monitor Server Instance: ABI-PCSQLINSTANCE

1.  
   1. Go to the Primary Instance Management Studio.
   2. Point to the UserDB database, Right Click on UserDB and Click on Tasks and then click on Ship Transaction Logs….

 

 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:

• Name: This is a job name which is LSBackupSchedule_ABI-PC1
• Schedule Type: Recurring
• Frequency: Runs daily that runs every 15 minutes.

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-PCSQLINSTANCE). 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-PCSQLINSTANCE, 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:

• Connect to the Secondary Server/Instance from Management Studio
• Right click on the server name (Primary/Secondary) in the object explorer
• Navigate to Reports  and then to  Standard Reports
• Click  on “Transaction 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 UserDB on secondary database using the following command in the query analyzer:

    RESTORE DATABASE [UserDB] WITH RECOVERY

• Disable the Log shipping jobs on the secondary server by right clicking each job and click disable on Copy, Alert, and Restore job.

Conclusion:

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.