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-PC\SQLINSTANCE

Monitor Server Instance: ABI-PC\SQLINSTANCE

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-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.

 Checking Log-Shipping Status

The secondary server stores information on log shipping in internal system tables. SQL Server provides a report to view the current status of the servers and other relevant information; this should be checked on a regular basis.

• Open SQL Server Management Studio
• Connect to the Secondary Server
• In the object explorer right click on the server name
• Navigate to Reports -> Standard Reports
• Click “Transaction Log Shipping Status”
• Use the scroll bar to view the entire report

 Fail-over to secondary server Procedure:

In the event of a hardware or other failure with the primary server implement the following steps to manually activate the secondary database. This process will take a few minutes, and the secondary database is not an exact copy of the primary database (as some changes could have been made in the primary database after the log was backed up and applied to the secondary server, database mirroring would be an exact copy) therefore this process should only be implemented in a true disaster recovery scenario and not as part of normal downtime such as patching. The steps are:

• Restore the secondary database:
  • Open SQL Server Management Studio
  • Connect to the secondary server
  • Click “New Query” in the upper left menu
  • Type the following in the query window:

RESTORE DATABASE [UserDB] WITH RECOVERY

•  
  • Click  “Execute” (or the F5 key)
  • You should see a success message in the query results pane
• Disable the Log shipping jobs on the secondary server
  • To use the graphical interface:
    • Expand the SQL Server Agent folder in Object Explorer
    • Expand “Jobs”
    • Right-click “” and choose disable on all three log shipping jobs

 Some Useful Information

The secondary server checks the directory on the primary for new log backups and copies those files to a local directory on the secondary server. If there is an extended network outage the secondary server can copy several files and recover automatically once the network has recovered as long as the log backups are still available. If the log file chain is broken and log shipping can no longer apply new log backups, restore the latest full backup from the primary server to the secondary server. One primary database can be shipped to multiple secondary servers. In the event of accidental data deletion on the primary server, if the mistake is realized before the log has been restored to the secondary server it may be possible to pause log shipping, restore the secondary database, copy the data back to the primary, and then resume log shipping.