info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
Log Shipping Real-Time Processing Engine v2
by BF (Principal Consultant; Architecture; Engineering)
2019-01-21
Execute on Log Shipping Secondary Server
- Replace DB1, DB2, DB3, DB4, DB57 with your Database Names
- Do not replace these: @IsDB4, @IsDB2, @IsDB3, @IsDB4, @IsDB5
Create Procedure dbo.spa_LS_Realtime_Processing
as
SET NOCOUNT ON
DECLARE @Debug bit = 0; --Set 1 returns top 25 backupset history
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @MSG NVARCHAR(1000)
DECLARE @fileList1 TABLE (backupFile NVARCHAR(255))
DECLARE @fileList2 TABLE (backupFile NVARCHAR(255))
DECLARE @backupFile NVARCHAR(500)
DECLARE @IsDB1 bit
DECLARE @IsDB2 bit
DECLARE @IsDB3 bit
DECLARE @IsDB4 bit
DECLARE @IsDB5 bit
DECLARE @IsDB6 bit
DECLARE @jobname varchar(150)
SET @backupPath = 'F:\LogShipping\Backups'
SELECT
reh.Destination_Database_Name, reh.Restore_Type, reh.User_Name, reh.Restore_Date,
bas.First_LSN, bas.Last_LSN, bas.Backup_Start_Date, bas.Backup_Finish_Date,
bmf.Physical_Device_Name,
LEFT(bmf.Physical_Device_Name,LEN(bmf.Physical_Device_Name) - charindex('\',reverse(bmf.Physical_Device_Name),1) + 1) as 'path',
RIGHT(bmf.Physical_Device_Name, CHARINDEX('\', REVERSE(bmf.Physical_Device_Name)) -1) as 'file_name',
bas.Server_Name, bas.Recovery_Model, bas.Compressed_Backup_Size,
bms.Is_Compressed, bas.Compatibility_Level
INTO #TMP1
FROM msdb.dbo.RESTOREHISTORY reh WITH (nolock)
Inner Join msdb.dbo.backupset bas on reh.backup_set_id = bas.backup_set_id
Inner Join msdb.dbo.backupmediaset bms on bas.backup_set_id = bms.media_set_id
Inner Join msdb.dbo.backupmediafamily bmf on bas.media_set_id = bmf.media_set_id
--Select * from #TMP1
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList1(backupFile)
EXEC master.sys.xp_cmdshell @cmd
Delete from @filelist1 where backupfile IS NULL
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - Log Shipping Real-Time Processing: Backup File --> Copy File --> Restore File', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - List File System Primary Transaction Log Backup Files Outstanding...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
If NOT EXISTS (Select BackupFile from @fileList1 where backupFile not in (Select [file_name] from #TMP1))
BEGIN
RAISERROR (' - No Backup Files to process', 0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('Finished !!!', 0, 1) WITH NOWAIT
DROP table #TMP1
RETURN
END
--Select BackupFile from @fileList1 where backupFile not in (Select [file_name] from #TMP1) order by backupFile asc
DECLARE @name1 nvarchar(128)
DECLARE cur1 CURSOR FOR
Select BackupFile from @fileList1 where backupFile not in (Select [file_name] from #TMP1) order by backupFile asc
OPEN cur1
FETCH NEXT FROM cur1 INTO @name1;
WHILE @@FETCH_STATUS = 0
BEGIN
IF CHARINDEX('DB1',@name1) > 0 Begin Set @IsDB1 = 1 RAISERROR (@name1, 0, 1) WITH NOWAIT End;
IF CHARINDEX('DB2',@name1) > 0 Begin Set @IsDB2 = 1 RAISERROR (@name1 , 0, 1) WITH NOWAIT End;
IF CHARINDEX('DB3',@name1) > 0 Begin Set @IsDB3 = 1 RAISERROR (@name1, 0, 1) WITH NOWAIT End;
IF CHARINDEX('DB4',@name1) > 0 Begin Set @IsDB4 = 1 RAISERROR (@name1 , 0, 1) WITH NOWAIT End;
IF CHARINDEX('DB5',@name1) > 0 Begin Set @IsDB5 = 1 RAISERROR (@name1 , 0, 1) WITH NOWAIT End;
FETCH NEXT FROM cur1 INTO @name1;
END
CLOSE cur1;
DEALLOCATE cur1;
WAITFOR DELAY '00:00:01';
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - Log Shipping Copy Job(s) on Secondary...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
PRINT CHAR(10)
If @IsDB1 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name--,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Copy%' AND sj.name like '%DB1%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Copy%' AND name like '%DB1%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End
WAITFOR DELAY '00:00:01';
If @IsDB2 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name--,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Copy%' AND sj.name like '%DB2%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Copy%' AND name like '%DB2%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End
WAITFOR DELAY '00:00:01';
If @IsDB3 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name--,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Copy%' AND sj.name like '%DB3%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Copy%' AND name like '%DB3%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End
WAITFOR DELAY '00:00:01';
If @IsDB4 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name--,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Copy%' AND sj.name like '%DB4%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Copy%' AND name like '%DB4%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End
WAITFOR DELAY '00:00:01';
If @IsDB5 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name--,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Copy%' AND sj.name like '%DB5%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Copy%' AND name like '%DB5%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End
WAITFOR DELAY '00:00:01';
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - Delay for 5s for Copy File processing to complete...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05';
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - Starting Log Shipping Restore Job(s) on Secondary...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
If @IsDB1 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Restore%' AND sj.name like '%DB1%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Restore%' AND name like '%DB1%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End
WAITFOR DELAY '00:00:01';
If @IsDB2 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Restore%' AND sj.name like '%DB2%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Restore%' AND name like '%DB2%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End
WAITFOR DELAY '00:00:01';
If @IsDB3 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Restore%' AND sj.name like '%DB3%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Restore%' AND name like '%DB3%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End
WAITFOR DELAY '00:00:01';
If @IsDB4 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Restore%' AND sj.name like '%DB4%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Restore%' AND name like '%DB4%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End
WAITFOR DELAY '00:00:01';
If @IsDB5 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Restore%' AND sj.name like '%DB5%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Restore%' AND name like '%DB5%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End
WAITFOR DELAY '00:00:01';
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - Delay for 5s for Restore File processing to complete...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05';
RAISERROR ('', 0, 1) WITH NOWAIT
SELECT
reh.Destination_Database_Name, reh.Restore_Type, reh.User_Name, reh.Restore_Date,
bas.First_LSN, bas.Last_LSN, bas.Backup_Start_Date, bas.Backup_Finish_Date,
bmf.Physical_Device_Name,
LEFT(bmf.Physical_Device_Name,LEN(bmf.Physical_Device_Name) - charindex('\',reverse(bmf.Physical_Device_Name),1) + 1) as 'path',
RIGHT(bmf.Physical_Device_Name, CHARINDEX('\', REVERSE(bmf.Physical_Device_Name)) -1) as 'file_name',
bas.Server_Name, bas.Recovery_Model, bas.Compressed_Backup_Size,
bms.Is_Compressed, bas.Compatibility_Level
INTO #TMP2
FROM msdb.dbo.RESTOREHISTORY reh WITH (nolock)
Inner Join msdb.dbo.backupset bas on reh.backup_set_id = bas.backup_set_id
Inner Join msdb.dbo.backupmediaset bms on bas.backup_set_id = bms.media_set_id
Inner Join msdb.dbo.backupmediafamily bmf on bas.media_set_id = bmf.media_set_id
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList2(backupFile)
EXEC master.sys.xp_cmdshell @cmd
Delete from @filelist2 where backupfile IS NULL
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - List File System Primary Log Backup Files Outstanding...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
If NOT EXISTS (Select BackupFile from @fileList2 where backupFile not in (Select [file_name] from #TMP2))
BEGIN
RAISERROR (' - No Backup Files to process', 0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('Finished !!!', 0, 1) WITH NOWAIT
If @Debug = 1 Begin
Select top 25 Destination_Database_Name, Restore_Type, Restore_Date, File_Name, Compressed_Backup_Size, Server_Name from #TMP2 order by Restore_Date DESC
End
DROP Table #TMP1
DROP Table #TMP2
RETURN
END
DECLARE @name2 nvarchar(128)
DECLARE cur2 CURSOR FOR
Select BackupFile from @fileList2 where backupFile not in (Select [file_name] from #TMP2) order by backupFile asc
OPEN cur2
FETCH NEXT FROM cur2 INTO @name2;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name2
FETCH NEXT FROM cur2 INTO @name2;
END
CLOSE cur2;
DEALLOCATE cur2;
If @Debug = 1
Begin
Select top 25 Destination_Database_Name, Restore_Type, Restore_Date, File_Name, Compressed_Backup_Size, Server_Name from #TMP2 order by Restore_Date DESC
End
Drop Table #TMP1
Drop Table #TMP2
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('- Re-run to process outstanding files !!!', 0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('Finished !!!', 0, 1) WITH NOWAIT
GO
Output:
-----------------------------------------------------------------------------------------------------
Starting - Log Shipping Real-Time Processing: Backup File --> Copy File --> Restore File
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
Starting - List File System Primary Transaction Log Backup Files Outstanding...
-----------------------------------------------------------------------------------------------------
DB1_20190121183001.trn
DB2_20190121183001.trn
DB3_20190121181500.trn
DB4_20190121181500.trn
DB5_20190121181500.trn
-----------------------------------------------------------------------------------------------------
Starting - Log Shipping Copy Job(s) on Secondary...
-----------------------------------------------------------------------------------------------------
Job 'LS_Copy_A1-NYC-PR_DB1' started successfully.
Job 'LS_Copy_A1-NYC-PR_DB2' started successfully.
Job 'LS_Copy_A1-NYC-PR_DB3' started successfully.
Job 'LS_Copy_A1-NYC-PR_DB4' started successfully.
Job 'LS_Copy_A1-NYC-PR_DB5' started successfully.
Job 'LS_Copy_A1-NYC-PR_DB6' started successfully.
-----------------------------------------------------------------------------------------------------
Starting - Delay for 5s for Copy File processing to complete...
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
Starting - Starting Log Shipping Restore Job(s) on Secondary...
-----------------------------------------------------------------------------------------------------
Job 'LS_Restore_A1-NYC-PR_DB1' started successfully.
Job 'LS_Restore_A1-NYC-PR_DB2' started successfully.
Job 'LS_Restore_A1-NYC-PR_DB3' started successfully.
Job 'LS_Restore_A1-NYC-PR_DB4' started successfully.
Job 'LS_Restore_A1-NYC-PR_DB5' started successfully.
Job 'LS_Restore_A1-NYC-PR_DB6' started successfully.
-----------------------------------------------------------------------------------------------------
Starting - Delay for 5s for Restore File processing to complete...
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
Starting - List File System Primary Log Backup Files Outstanding...
-----------------------------------------------------------------------------------------------------
- No Backup Files to process
Finished !!!
Version 1:
Log Shipping Real-Time Processing Engine (Version 1)
|
|
|
|
|
|
|
|
|
|