How do you configure SQL Server Log Shipping?

Log shipping is a DR solution that provides recovery at the database level.  Log shipping includes one primary server, and one or more secondary servers. You don't have automatic fail-over, but if the primary becomes unavailable, the secondary can be brought online by restoring any remaining shipped logs to the database.  In short, it's the automated transfer and restore of log backups from primary to standby server(s).  In this post I am just going to show you a very basic implementation of Log Shipping.

Pre-requisites:  
  1. To set it up, you must be a sysadmin on the server
  2. Usable with SQL Server v2005 or later
  3. The same edition - Workgroup, Standard or Enterprise - must be installed on each instance within the log shipping solution.
  4. Your databases must be either Full or Bulk-logged recovery model
  5. You need a shared folder for the transaction log backup files
  6. Be sure the case sensitivity is the same across each server
Steps:

    1. Create a shared folder for primary and standby servers to access the transaction logs
    2. Make sure your recovery model is FULL or BULK_LOGGED - 
    USE msdb;
    SELECT recovery_model_desc 
    FROM sys.databases 
    WHERE name = 'dbname'
    3. Backup the database on the primary instance -
    USE master;
    BACKUP DATABASE dbname TO DISK='C:\Bkups\dbname_Full_Backup.bak'
    WITH INIT,FORMAT;
    4. Restore the database on the secondary instance -
    USE master;
    RESTORE DATABASE dbname 
    FROM DISK '\\Srv\Bkups\dbname_Full_Backup.bak'  
    WITH REPLACE, NORECOVERY;
    5. Configure log shipping at the primary with the following statements -
    DECLARE @BackupJobId AS UNIQUEIDENTIFIER 
    DECLARE @PrimaryId AS UNIQUEIDENTIFIER
    DECLARE @RetCode AS INT 

    EXEC @RetCode = master..sp_add_log_shipping_primary_database 
       @database = N'dbname', 
       @backup_directory = N'C:\Bkups', 
       @backup_share = N'\\Srv\Bkups', 
    @backup_job_name = N'Backup_dbname', 
  @backup_retention_period = 4320,
  @backup_threshold = 5, 
  @threshold_alert_enabled = 1,
  @history_retention_period = 5760,
 @backup_job_id = @BackupJobId OUTPUT, 
    @primary_id = @PrimaryId OUTPUT, 
    @overwrite = 1 

       IF (@@ERROR = 0 AND @RetCode = 0) 
     BEGIN 
    DECLARE @BackUpScheduleUID AS UNIQUEIDENTIFIER
          DECLARE @BackUpScheduleID AS INT

  EXEC msdb.dbo.sp_add_schedule 
               @schedule_name = N'Every5Min', 
                @enabled = 1, 
          @freq_type = 4, 
       @freq_interval = 1, 
             @freq_subday_type = 4, 
       @freq_subday_interval = 2, 
       @freq_recurrence_factor = 0, 
             @active_start_date = 20140905, 
             @active_end_date = 99991231,
       @active_start_time = 0, 
       @active_end_time = 235900, 
             @schedule_uid = @BackUpScheduleUID OUTPUT, 
       @schedule_id = @BackUpScheduleID OUTPUT 
EXEC msdb..sp_attach_schedule 
            @job_id = @BackupJobId, @schedule_id = @BackUpScheduleID  
EXEC msdb..sp_update_job @job_id = @BackupJobId, @enabled = 1 
END 
EXEC master..sp_add_log_shipping_alert_job 
           
       6. Configure log shipping at the secondary with the following statements - 
      DECLARE @CopyJobID    UNIQUEIDENTIFIER
      DECLARE @RestoreJobID UNIQUEIDENTIFIER
      DECLARE @SecondaryID  UNIQUEIDENTIFIER
      DECLARE @RetCode      INT
    EXEC @RetCode = master..sp_add_log_shipping_secondary_primary 
       @primary_server = N'PrimarySrvName', 
       @primary_database = N'dbname', 
       @backup_source_directory = N'\\PrimarySrvName\Logs', 
       @backup_destination_directory = N'C:\Bkups', 
       @copy_job_name = N'Copy_dbname',
       @restore_job_name = N'Restore_PrimarySrv_dbname',
       @file_retention_period = 300, 
       @overwrite = 1, 
       @copy_job_id = @CopyJobID OUTPUT, 
       @restore_job_id = @RestoreJobID OUTPUT, 
       @secondary_id = @SecondaryID OUTPUT
     IF(@@ERROR = 0 AND @RetCode = 0)
     BEGIN 
        DECLARE @SecondaryCopyJobScheduleUID UNIQUEIDENTIFIER
        DECLARE @SecondaryCopyJobScheduleID  INT
  EXEC msdb..sp_add_schedule 
  @schedule_name = N'Every5MinCopy',
  @enabled = 1, 
  @freq_type = 4, 
  @freq_interval = 1, 
  @freq_subday_type = 4, 
  @freq_subday_interval = 2, 
  @freq_recurrence_factor = 0, 
  @active_start_date = 20140905, 
  @active_end_date = 99991231, 
  @active_start_time = 0, 
  @active_end_time = 235900, 
  @schedule_uid = @SecondaryCopyJobScheduleUID OUTPUT,   @schedule_id = @SecondaryCopyJobScheduleID OUTPUT 
 
        EXEC msdb..sp_attach_schedule 
  @job_id = @CopyJobID, 
  @schedule_id = @SecondaryCopyJobScheduleID  

 DECLARE @SecondaryRestoreJobScheduleUID   UNIQUEIDENTIFIER
 DECLARE @SecondaryRestoreJobScheduleID   INT 
   
 EXEC msdb..sp_add_schedule 
  @schedule_name = N'Every5MinRestore',
         @enabled = 1, 
  @freq_type = 4, 
  @freq_interval = 1, 
           @freq_subday_type = 4, 
  @freq_subday_interval = 2, 
  @freq_recurrence_factor = 0, 
  @active_start_date = 20140905, 
  @active_end_date = 99991231, 
  @active_start_time = 0, 
  @active_end_time = 235900, 
 @schedule_uid = @SecondaryRestoreJobScheduleUID OUTPUT, 
 @schedule_id = @SecondaryRestoreJobScheduleID OUTPUT 

 EXEC msdb..sp_attach_schedule 
  @job_id = @RestoreJobID, 
  @schedule_id = @SecondaryRestoreJobScheduleID  
     END 
   
     DECLARE @RetCode2 INT
     IF(@@ERROR = 0 AND @RetCode = 0) 
     BEGIN 

   EXEC @RetCode2 = master..sp_add_log_shipping_secondary_database 
 @secondary_database = N'dbname', 
 @primary_server = N'PrimarySrvName', 
 @primary_database = N'dbname', 
 @restore_delay = 0, 
 @restore_mode = 0, 
 @disconnect_users = 0, 
 @restore_threshold = 5,  
 @threshold_alert_enabled = 1, 
 @history_retention_period = 5760, 
 @overwrite = 1 
   END 

   IF (@@ERROR = 0 AND @RetCode = 0) 
   BEGIN 
 EXEC msdb..sp_update_job @job_id = @CopyJobID, @enabled = 1 
     EXEC msdb..sp_update_job @job_id = @RestoreJobID, @enabled = 1    END 
    7. Lastly, execute this statement on the primary, to configure the secondary database
    EXEC master..sp_add_log_shipping_primary_secondary
      @primary_database = N'dbname',
      @secondary_server = N'SecondarySrvName', 
      @secondary_database = N'dbname', 
      @overwrite = 1 


Now take a look at each of these queries to gather general information regarding the status of the log shipping solution:
How many databases are being targeted with the log shipping?
  USE msdb;
  SELECT * FROM log_shipping_monitor_primary;
Where are the secondary, or standby servers?
  USE msdb;
  SELECT * FROM log_shipping_primary_secondaries;
General status for the Log Shipping?
  USE msdb;
  SELECT * FROM log_shipping_monitor_history_detail;
Any errors?
  USE msdb;
  SELECT * FROM log_shipping_monitor_error_detail;




That's about it.  Just a very basic method for using Log Shipping as your DR solution.  I encourage you to research and read a bit more.  See each of these for more detailed information.  


      About Log Shipping - http://msdn.microsoft.com/en-us/library/ms187103.aspx


      Log Shipping FAQs  - http://www.brentozar.com/archive/2013/03/log-shipping-faq/


Here's a great tip that I just found this evening, for catching up your secondary faster:


      http://www.brentozar.com/archive/2014/09/log-shipping-magic-using-differential-backup-delayed-secondary/



































































No comments:















Post a Comment


















        

      









Subscribe to:
Posts (Atom)