RSS

Category Archives: Log Shipping

Know How to Monitor Log Shipping in SQL Server

Log shipping in SQL Server is the process of enabling an automatic backup of the complete database and transaction files of the primary server and then restoring the backup on a secondary server. Being a high-availability solution, it increases the database availability of SQL Server by creating a backup server that can replace the primary server in case of data disasters.

Log Shipping involves 3 servers:

  1. Primary or the source server
  2. Secondary or destination server
  3. Monitor Server

Log Shipping in SQL Server allows sending of transactional log backups from primary instances residing on the primary server to one or more secondary instances residing on the secondary server. These transactional log backups are applied to each instance of the secondary database individually.

It is to be noted that Log shipping in SQL Server can also be configured without the installation of monitor server. In such a case, the history and the status of all the backup operations are stored on the primary server and the history and restore status is stored on the secondary server. However, when the remote monitor server is installed, this information also gets stored on it. It is also responsible for raising alerts whenever the backup process is not performed as per the schedule.

In case the monitor server has not been specified, the primary and secondary servers monitor the backup and restore operations, and raise the alert when an error is encountered.

Key Note for Monitoring Log Shipping in SQL Server

For monitoring the configuration of log shipping in SQL Server, the monitor server needs to be added at the time of enabling log shipping. In case the monitor server is added later on, the existing log shipping configuration must be removed and the new setup including monitor serves must be installed. In addition to this, any alterations in the monitor server can only be enabled after removing log shipping.

History Tables in SQL Server Log Shipping

The monitoring history tables of log shipping comprise metadata that gets stored on the monitor server. Moreover, primary and secondary server-specific information is also stored locally. For monitoring the status of the monitoring sessions, these history tables are brought in use. In order to check the status of log shipping, status and history of backup job, copy and restore functions need to be viewed.

Some of the tables that need to be checked for monitoring the log shipping in SQL Server are:

  1. log_shipping_monitor_alert
  2. This table stores the alert job ID.

  3. log_shipping_monitor_error_detail
  4. This table stores the details of the log shipping transactions. In addition to this, the users can also access the table for accessing errors in a session.

  5. log_shipping_monitor_history_detail
  6. This table gives the details of the history of the log shipping agents. Details regarding an agent session can also be viewed in this table.

  7. log_shipping_monitor_primary
  8. One monitor record for primary database of each log shipping configuration is stored in this table. This includes the information like the details of the last backup and the last restored file.

  9. log_shipping_monitor_secondary
  10. This table stores one monitor record of the secondary database like the last backup file and the last restored file that are used in monitoring.

Conclusion

Monitoring log shipping in SQL Server enables the administrator to monitor the entire configuration of the entire environment. In addition to the history tables, additional parameters like stored procedures enable to monitor log shipping in SQL Server.

 
2 Comments

Posted by on January 19, 2016 in General topics, Log Shipping

 

Tags:

SQL Server Transaction Log Shipping vs Mirroring – A Comprehensive Guide

SQL Server Transaction Logs record the entire information about the transactions and data modifications made by the transactions in the server. In order to create backup or ensure the availability of transaction logs at the instance of any disaster, SQL offers two processes- SQL Log Shipping and SQL Log Mirroring.

In this section, we will describe both these processes and the parameters in which they differ from each other. Read the rest of this entry »

 
 

Tags: ,

Log Shipping in SQL Server 2008R2 Step by Step

Introudaction

SQL Server Log Shipping Allow to Transfare Data Form one Database  (Primary) to a lot of Database (Secondry) So in Log Shipping we have three Srrvers (Primary , Secondry (optinal) monitor ) . the Secondry Server can be More than one Database

Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers

#######################################################################################

1-       Create Folder in Primary Server (LogShipping_Primary) aslo in Secondry Server Create Folder Name (LogShipping_Secondry) this two Folders must be sharing .

2-      Take Full and transaction log backup from the database (Log Shipping) in Primary Server.

3-      Restore this Database on Secondry Server with Stand By option.

logShippingDB after Restore

4-      On primary Server Write Click >>> properties  >>> Log Shipping n >>>> Enable log Shipping.

Enable Log Shipping

5-      Configure the backup Setting for the Transaction Log backups

  • Network backup path Folder : the Folder name LogShipping_Primary in Primary Server put the network path for this Folder \\Servername\Foldername
  • Local path : the local path for the folder in primary Server like C:\users\DBA\Desctop\LogShipping_Primary

PrimaryServer backup path

6-      Configure Sechudel job to the backup database

4

7-      Add Secondry Server

Add Secondry Server

8-      Connect to Select your Secondry Server

6

Select Secondry Server

9-      Initialize Secondry Database

  • First option         : if you want to take Full backup the restore it to the Secondry Server
  • Seconde Option : If you won’t to Restore the backup from the primary Server to the Secondry Server
  • Theird option      : if you Take the backup and already restore it to the secondry server   (like my example)

8-	Initialize Secondry Database

10-      Copy files : in this tabe you will put the path of the Folder in Secondry Server put you will put it like this \\Servername\Foldername

Copy Files

10-      Configure the Sehedule to take the backup files copy from the primary server to the secondry server

10-	Configure the Sehedule to take the backup

11-      Restore transaction log : we will select Standby option and Disconnect all user in restore operation

Restore transaction log

12-      Configure the Sehedule to restore the backup taken from the primary Server

12

13-      Then ok

14-      Optinal (Check on monitor Server instance to monitor the backup Status  and the job Status  for the Jobs in primary Server and Secondry Server

Configure the Sehedule to restore the backup

14

14-      last Step in Log Shiping Configuration

Finish

14-      After you finish from the Log Shipping go to the Primary Server to Check the new jobs

16

15-      Return to the Secondry Server to Check the new Jobs

17

 
2 Comments

Posted by on January 2, 2013 in Log Shipping