Differences

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

--- mssql_log_shipping [2019/10/18 20:04] – external edit 127.0.0.1
+++ 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:
-On Primary:
+[[mssql_log_shipping_primary|On Primary]]
+  * Disable Current Transaction Log Backup
   * Create a schedule
   * Create the necessary jobs and attach them Schedule
   * Do a backup to a shared location
-On Standby:
+[[mssql_log_shipping_secondary|On Secondary]]
   * Create a schedules (1 for copy and 1 for restore)
@@ Line 21: / Line 20: @@
   * Restore the backup done on the primary
   * Start the log-shipping
-  * * In case edit the job if needed
-======Configuring Primary======
-Be sure that both primary and target destination have access to the shared location which you will use for the backup. Alternatively, you can backup directly on the target server in case you have a space:
-=====Create a schedule=====
-Schedule can be created in two ways:
-  * Using GUI - Too boring to show.
-  * Using the command line - Not boring to show :)
-This will create a schedule which can run jobs each 5 minutes daily:
-<sxh bash>
-EXEC sp_add_schedule
-    @schedule_name = N'Every5Minutes' ,
-    @freq_type=4,
-    @freq_interval=1,
-    @freq_subday_type=4,
-    @freq_subday_interval=5,
-    @active_start_time=000000,
-    @active_end_time=235959 ;
-</sxh>
-=====Create jobs=====
-Once we have the schedule, we have to create and add jobs to it as follows:
-<sxh bash>
-DECLARE @LS_BackupJobId	AS uniqueidentifier
-DECLARE @LS_PrimaryId	AS uniqueidentifier
-DECLARE @SP_Add_RetCode	As int
-EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
-		@database = N'%{database_name}'
-		,@backup_directory = N'\\shared_server_location_ip\path\%{database_name}'
-		,@backup_share = N'\\shared_server_location_ip\path\%{database_name}'
-		,@backup_job_name = N'LSBackup_%{database_name}'
-		,@backup_retention_period = 4320
-		,@backup_threshold = 60
-		,@threshold_alert_enabled = 1
-		,@history_retention_period = 5760
-		,@backup_job_id = @LS_BackupJobId OUTPUT
-		,@primary_id = @LS_PrimaryId OUTPUT
-		,@overwrite = 1
-IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
-BEGIN
-EXEC msdb.dbo.sp_attach_schedule
-		@job_id = @LS_BackupJobId
-		,@schedule_name = N'Every5Minutes'   <- Schedule we just created.
-EXEC msdb.dbo.sp_update_job
-		@job_id = @LS_BackupJobId
-		,@enabled = 1
-END
-EXEC master.dbo.sp_add_log_shipping_alert_job
-EXEC master.dbo.sp_add_log_shipping_primary_secondary
-		@primary_database = N'%{database_name}'
-		,@secondary_server = N'secondary_server_hostname'
-		,@secondary_database = N'%{database_name}'
-		,@overwrite = 1
-GO
-xp_cmdshell N'mkdir \\shared_server_location_ip\path\%{database_name}', NO_OUTPUT
-GO
-</sxh>
-=====Perform initial backup=====
-The backup will be done on shared location OR on the target server(in case you are rich and have money for storage :) ) Since we are poor people, let's do it on shared storage:
-<sxh bash>
-BACKUP DATABASE [%{database_name}] TO DISK= N'\\shared_server_location_ip\path\%{database_name}\%{database_name}.bak' WITH COMPRESSION
-</sxh>
-====Remove of the configuration====
-If for any reason you mistake the configuration, you can remove it as follows:
-<sxh bash>
-sp_delete_log_shipping_primary_database [ @secondary_database = ] 'tainops'
-</sxh>
-======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)