Saturday, November 5, 2011

0 Log Shipping

Configuration showing backup, copy, & restore jobs

  • Log shipping will be deprecate in the future MSSQL Server release. User should consider to use Database Mirror to replace Log shipping.
  • Log shipping does not support in the SQL Express.
  • The database set up for log shipping must be FULL or BUIL INSERT mode.

Log shipping high level over view

  1. Restore the full db from primary to the secondary db. 2 modes can be choosing on the secondary db. Stand by or  non recovery. When use stand by mode, the secondary db can be serve as reporting purpose however, stand by mode can not be used for high available solution.
  2. Backup the log from primary db
  3. Restore the log to the secondary db

Log shipping will involves 4 jobs

  1. Backup job: backup the log from primary db
  2. Copy job: copy the tran dump file to the share
  3. Restore job: Apply the tran dump to the secondary db
  4. Alert job: only will be created when monitor server is configured.

SSMS can be used to generate the Script.

Setup the log backup jobs

Below example is setting up the log backup job for every 5mins

  1. DECLARE @LS_BackupJobId    AS uniqueidentifier
  2. DECLARE @LS_PrimaryId    AS uniqueidentifier
  3. DECLARE @SP_Add_RetCode    As int
  4.  
  5.  
  6. EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
  7.         @database = N'AdventureWorks'
  8.         ,@backup_directory = N'C:\ls_backup'
  9.         ,@backup_share = N'\\Sg11\ls_backup'
  10.         ,@backup_job_name = N'LSBackup_AdventureWorks'
  11.         ,@backup_retention_period = 4320
  12.         ,@backup_compression = 2
  13.         ,@backup_threshold = 60
  14.         ,@threshold_alert_enabled = 1
  15.         ,@history_retention_period = 5760
  16.         ,@backup_job_id = @LS_BackupJobId OUTPUT
  17.         ,@primary_id = @LS_PrimaryId OUTPUT
  18.         ,@overwrite = 1
  19.  
  20.  
  21. IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
  22. BEGIN
  23.  
  24. DECLARE @LS_BackUpScheduleUID    As uniqueidentifier
  25. DECLARE @LS_BackUpScheduleID    AS int
  26.  
  27.  
  28. EXEC msdb.dbo.sp_add_schedule
  29.         @schedule_name =N'LSBackupSchedule_sg11\MSSQL2008R21'
  30.         ,@enabled = 1
  31.         ,@freq_type = 4
  32.         ,@freq_interval = 1
  33.         ,@freq_subday_type = 4
  34.         ,@freq_subday_interval = 5
  35.         ,@freq_recurrence_factor = 0
  36.         ,@active_start_date = 20111104
  37.         ,@active_end_date = 99991231
  38.         ,@active_start_time = 0
  39.         ,@active_end_time = 235900
  40.         ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
  41.         ,@schedule_id = @LS_BackUpScheduleID OUTPUT
  42.  
  43. EXEC msdb.dbo.sp_attach_schedule
  44.         @job_id = @LS_BackupJobId
  45.         ,@schedule_id = @LS_BackUpScheduleID  
  46.  
  47. EXEC msdb.dbo.sp_update_job
  48.         @job_id = @LS_BackupJobId
  49.         ,@enabled = 1
  50.  
  51.  
  52. END

We can use msdb.dbo.sp_help_job  to review the job detail

Setup the alert job

  1. EXEC master.dbo.sp_add_log_shipping_alert_job

Enable the Log shipping on the primary database

  1. EXEC master.dbo.sp_add_log_shipping_primary_secondary
  2.         @primary_database = N'AdventureWorks'
  3.         ,@secondary_server = N'SG13\SG13MSSQL'
  4.         ,@secondary_database = N'AdventureWorks'
  5.         ,@overwrite = 1

Create the copy and restore job on the secondary database

  1.  
  2. DECLARE @LS_Secondary__CopyJobId    AS uniqueidentifier
  3. DECLARE @LS_Secondary__RestoreJobId    AS uniqueidentifier
  4. DECLARE @LS_Secondary__SecondaryId    AS uniqueidentifier
  5. DECLARE @LS_Add_RetCode    As int
  6.  
  7.  
  8. EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
  9.         @primary_server = N'sg11\MSSQL2008R2'
  10.         ,@primary_database = N'AdventureWorks'
  11.         ,@backup_source_directory = N'\\Sg11\ls_backup'
  12.         ,@backup_destination_directory = N'C:\LS_COPY'
  13.         ,@copy_job_name = N'LSCopy_sg11\MSSQL2008R2_AdventureWorks'
  14.         ,@restore_job_name = N'LSRestore_sg11\MSSQL2008R2_AdventureWorks'
  15.         ,@file_retention_period = 4320
  16.         ,@overwrite = 1
  17.         ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
  18.         ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
  19.         ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
  20.  
  21. IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
  22. BEGIN
  23.  
  24. DECLARE @LS_SecondaryCopyJobScheduleUID    As uniqueidentifier
  25. DECLARE @LS_SecondaryCopyJobScheduleID    AS int
  26.  
  27.  
  28. EXEC msdb.dbo.sp_add_schedule
  29.         @schedule_name =N'DefaultCopyJobSchedule'
  30.         ,@enabled = 1
  31.         ,@freq_type = 4
  32.         ,@freq_interval = 1
  33.         ,@freq_subday_type = 4
  34.         ,@freq_subday_interval = 15
  35.         ,@freq_recurrence_factor = 0
  36.         ,@active_start_date = 20111104
  37.         ,@active_end_date = 99991231
  38.         ,@active_start_time = 0
  39.         ,@active_end_time = 235900
  40.         ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
  41.         ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT
  42.  
  43. EXEC msdb.dbo.sp_attach_schedule
  44.         @job_id = @LS_Secondary__CopyJobId
  45.         ,@schedule_id = @LS_SecondaryCopyJobScheduleID  
  46.  
  47. DECLARE @LS_SecondaryRestoreJobScheduleUID    As uniqueidentifier
  48. DECLARE @LS_SecondaryRestoreJobScheduleID    AS int
  49.  
  50.  
  51. EXEC msdb.dbo.sp_add_schedule
  52.         @schedule_name =N'DefaultRestoreJobSchedule'
  53.         ,@enabled = 1
  54.         ,@freq_type = 4
  55.         ,@freq_interval = 1
  56.         ,@freq_subday_type = 4
  57.         ,@freq_subday_interval = 3
  58.         ,@freq_recurrence_factor = 0
  59.         ,@active_start_date = 20111104
  60.         ,@active_end_date = 99991231
  61.         ,@active_start_time = 0
  62.         ,@active_end_time = 235900
  63.         ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
  64.         ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT
  65.  
  66. EXEC msdb.dbo.sp_attach_schedule
  67.         @job_id = @LS_Secondary__RestoreJobId
  68.         ,@schedule_id = @LS_SecondaryRestoreJobScheduleID  
  69.  
  70.  
  71. END
  72.  
  73.  
  74. DECLARE @LS_Add_RetCode2    As int
  75.  
  76.  
  77. IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
  78. BEGIN
  79.  
  80. EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
  81.         @secondary_database = N'AdventureWorks'
  82.         ,@primary_server = N'sg11\MSSQL2008R2'
  83.         ,@primary_database = N'AdventureWorks'
  84.         ,@restore_delay = 0
  85.         ,@restore_mode = 0
  86.         ,@disconnect_users    = 0
  87.         ,@restore_threshold = 6   
  88.         ,@threshold_alert_enabled = 1
  89.         ,@history_retention_period    = 5760
  90.         ,@overwrite = 1
  91.  
  92. END
  93.  
  94.  
  95. IF (@@error = 0 AND @LS_Add_RetCode = 0)
  96. BEGIN
  97.  
  98. EXEC msdb.dbo.sp_update_job
  99.         @job_id = @LS_Secondary__CopyJobId
  100.         ,@enabled = 1
  101.  
  102. EXEC msdb.dbo.sp_update_job
  103.         @job_id = @LS_Secondary__RestoreJobId
  104.         ,@enabled = 1
  105.  
  106. END

Before the log shipping job actually start working, we will need to restore the database from primary to secondary with NONRECOVERY option. 

How to check which database has the log shipping set up?

msdb.log_shipping_primary_databases or  sp_help_log_shipping_primary_database

  1. Use msdb;
  2. select * from log_shipping_primary_databases
  1. use master
  2. go
  3. sp_help_log_shipping_primary_database  @database=N'AdventureWorks'

image

More store procedure or system table can be found here http://msdn.microsoft.com/en-us/library/ms175106.aspx

 

Remove Log shipping

On the primary server

Use On the primary server, sp_help_log_shipping_primary_secondary to exam the log shipping secondary db setup and execute sp_delete_log_shipping_primary_secondary to delete the information about the secondary database from the primary server.

  1. use master;
  2. go
  3. sp_help_log_shipping_primary_secondary [AdventureWorks]
  4. go
  5. sp_delete_log_shipping_primary_secondary @primary_database= [AdventureWorks] , @secondary_database = [AdventureWorks],@secondary_server = [SG13\SG13MSSQL]
  6. go

Use sp_help_log_shipping_primary_database  to exame the primary database setup and use sp_delete_log_shipping_primary_database to remove the primary database being as log shipping database. this will remove the backup log job too.

  1. use master;
  2. go
  3. sp_help_log_shipping_primary_database [AdventureWorks]
  4. go
  5. sp_delete_log_shipping_primary_database [AdventureWorks]

On the secondary server, use sp_help_log_shipping_secondary_database to exam the secondary information and use sp_help_log_shipping_secondary_database  to remove it. this will remove the restore job and copy job too.

  1. sp_help_log_shipping_secondary_database [AdventureWorks]
  2. go
  3. sp_delete_log_shipping_secondary_database @secondary_database = [AdventureWorks]

Notes

Why I can not find the log shipping option in the database property within the SSMS?

The log shipping and database mirror is only support in the non express version. You wont be able to find this option in the SQL Express SSMS.

From SQL Express SSMS

image From SQL Server Enterprise SSMS

image

 

Reference

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

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates