Differences

This shows you the differences between two versions of the page.

--- mssql_log_shipping [2020/03/10 14:23] – andonovj
+++ mssql_log_shipping [2020/03/10 23:18] (current) – andonovj
@@ Line 4: / Line 4: @@
 {{ :log_shipping_sql_server.jpg?400 |}}
 There are couple things which has to be configured:
@@ Line 11: / Line 9: @@
 [[mssql_log_shipping_primary|On Primary]]
+  * Disable Current Transaction Log Backup
   * Create a schedule
   * Create the necessary jobs and attach them Schedule
@@ Line 21: / Line 20: @@
   * Restore the backup done on the primary
   * Start the log-shipping
-  * * In case edit the job if needed
-======Configure Secondary Server======
-Once all the backups and the configurations on the primary have been done, it is time to configure the secondary. Bear in mind that the secondary server SHOULD have access to the shared location or the backup should reside on the secondary server itself.
-=====Create Schedules=====
-We need 2 types of schedules for the log shipping configuration:
-  * Copy Schedule
-  * Restore Schedule
-====Copy schedule====
-The purpose of the copy schedule is to copy the log backup, performed by the primary server job, to the secondary server:
-Like with the primary server schedule, we can use the same settings:
-<sxh bash>
-EXEC sp_add_schedule
-    @schedule_name = N'DefaultCopyJobSchedule' ,
-    @freq_type=4,
-    @freq_interval=1,
-    @freq_subday_type=4,
-    @freq_subday_interval=5,
-    @active_start_time=000000,
-    @active_end_time=235959 ;
-</sxh>
-====Restore Schedule====
-The restore schedule and job are little bit specific, especially the job, since it depends if we will use the log restore as standby or as norecovery, as we are going to see later. The command for the schedule is more or less the same as the previous ones:
-<sxh bash>
-EXEC sp_add_schedule
-    @schedule_name = N'DefaultRestoreJobSchedule' ,
-    @freq_type=4,
-    @freq_interval=1,
-    @freq_subday_type=4,
-    @freq_subday_interval=5,
-    @active_start_time=000000,
-    @active_end_time=235959 ;
-</sxh>
-=====Restore=====
-Once the schedules are created, we can proceed and restore:
-<sxh bash>
-xp_cmdshell N'mkdir C:\data\path', NO_OUTPUT
-GO
-xp_cmdshell N'mkdir C:\logs\path', NO_OUTPUT
-GO
-xp_cmdshell N'mkdir C:\standby\path', NO_OUTPUT
-GO
-RESTORE DATABASE [casino]
-	FROM  DISK = N'\\10.80.5.101\backups\migration\casino.bak'
-	WITH  FILE = 1,
-	MOVE N'casino_data_01' TO N'C:\data\path\data_01.mdf',
-	MOVE N'casino_data_02' TO N'C:\data\path\data_02.mdf',
-	MOVE N'casino_data_03' TO N'C:\data\path\data_03.mdf',
-	MOVE N'casino_data_04' TO N'C:\data\path\data_04.mdf',
-	MOVE N'casino_log_01' TO N'C:\logs\path\log_01.ldf',
-	MOVE N'casino_log_02' TO N'C:\logs\path\log_02.ldf',
-	STANDBY = N'C:\standby\ROLLBACK_UNDO.BAK',  NOUNLOAD,  STATS = 10
-	--NORECOVERY if NEWER VERSION
-GO
-</sxh>
-Nice that for newer version, for example SQL2008 -> SQL2016 we have to use NORECOVERY instead of Standby.
-After the backup completes, it will not open the database and the database will appear as "Restoring"
-=====Create the necessary jobs=====
-The jobs are needed so we can sync everything and start the log shipping/apply processes:
-<sxh bash>
-DECLARE @LS_Secondary__CopyJobId	AS uniqueidentifier
-DECLARE @LS_Secondary__RestoreJobId	AS uniqueidentifier
-DECLARE @LS_Secondary__SecondaryId	AS uniqueidentifier
-DECLARE @LS_Add_RetCode	As int
-EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
-		@primary_server = N'AG-PH-CSQL2'
-		,@primary_database = N'casino'
-		,@backup_source_directory = N'\\shared_storage_ip\path\migration'
-		,@backup_destination_directory = N'C:\standby\path'
-		,@copy_job_name = N'LSCopy_casino'
-		,@restore_job_name = N'LSRestore_casino'
-		,@file_retention_period = 4320
-		,@overwrite = 1
-		,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
-		,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
-		,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
-IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
-BEGIN
-DECLARE @LS_SecondaryCopyJobScheduleUID	As uniqueidentifier
-DECLARE @LS_SecondaryCopyJobScheduleID	AS int
-EXEC msdb.dbo.sp_attach_schedule
-		@job_id = @LS_Secondary__CopyJobId
-		,@schedule_name = N'DefaultCopyJobSchedule'
-DECLARE @LS_SecondaryRestoreJobScheduleUID	As uniqueidentifier
-DECLARE @LS_SecondaryRestoreJobScheduleID	AS int
-EXEC msdb.dbo.sp_attach_schedule
-		@job_id = @LS_Secondary__RestoreJobId
-		,@schedule_name = N'DefaultRestoreJobSchedule'
-END
-DECLARE @LS_Add_RetCode2	As int
-EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
-		@secondary_database = N'casino'
-		,@primary_server = N'primary_hostname'
-		,@primary_database = N'casino'
-		,@restore_delay = 0
-		,@restore_mode = 1(1 for standby, 0 for norecovery)   <- Again, if newer version, we should put 0
-		,@disconnect_users	= 0
-		,@restore_threshold = 45
-		,@threshold_alert_enabled = 1
-		,@history_retention_period	= 5760
-		,@overwrite = 1
-IF (@@error = 0 AND @LS_Add_RetCode = 0)
-BEGIN
-EXEC msdb.dbo.sp_update_job
-		@job_id = @LS_Secondary__CopyJobId
-		,@enabled = 1
-EXEC msdb.dbo.sp_update_job
-		@job_id = @LS_Secondary__RestoreJobId
-		,@enabled = 1
-END
-</sxh>
-Congrats you have brand new log shipping configuration
-====Remove the configuration====
-Again if you mistake the configuration for some reason, you can use the following procedure to remove it:
-<sxh bash>
-sp_delete_log_shipping_secondary_database [ @secondary_database = ] 'tainops'
-</sxh>
-P.S. This will remove also any created jobs.
-======Misc======
-====Change restore option====
-If you made error of the restore type, you edit the restore job configuration as follows:
-<sxh bash>
+[[mssql_log_shipping_switchover|Switchover]]
-exec sp_change_log_shipping_secondary_database @secondary_database = 'casino', @restore_mode = 0 - Change a job to recover log from standby to norecovery;
-</sxh>
+  * Perform Tail-log Backup (On Primary)
+  * Restore with RECOVERY (On Standby)