SQL Server

Log shipping Automation With PowerShell

- by Avanish Panchal - Leave a Comment

Hello Friends –

Back to you all with my second blog of year 2017 with Logshipping automation. This time picked up a little deviated topics like year 2016. Being a DBA background monitoring, configuring & managing high availability & disaster recovery solution were a day to day responsibility for me, like most of us have/had at some point in time in career. Recently I’d been notified with a challenge for a customer managing highly confidential data. So here is the problem statement.

“While we’re expanding our business, some of high confidential data needs to be procured in secured way from a highly skilled & specialized organization involved into business for years. Challenge is data procured from vendor needs to be up-to-date on daily basis in customer environment, of course without compromising a bit of security.”
First things comes to an experienced DBA in mind, what kind of security model/concerns are being refrained here.

  • Secured Bridge between vendor & customer
  • Method to push data
  • Access to data
  • Method of accessing data
  • Etc
  • Etc
  • ….

List is gone be unfathomable at moments. If proper & secured infrastructure is in place, most of the stuff comes to DBA in setting up pull data from vendor environment, push to customer in timely fashion so further analysis and respective tasks take place, seamlessly.

Here is quick flow of purposed solution from my side.

SecuredLogShipping_Diagram

It’s working perfectly for a while and let me provide required set of tools excluding SQL Server Engine, Database & SQL Agent. Pretty much everything is being used in this solution is freeware and doesn’t carry any security issues.

***Assumptions

  • Vendor is providing daily zip file with a suffix of YYYYMMDD format to distinguish file names.
  • SFTP download is running with PPK shared by vendor to customer.
  • SQL DB Engine & SQL Agent is running under domain & authentication is well placed.
  • Listed tools are installed correctly and able to run seamless. Better to do a test run with wizard mode.
  • A DBA database exists on SQL instance to hold historical sync information for back tracking. In my case DB name is SQLMaint.
  • Database Mail configuration is in place to send success & failure notification.

Completed PowerShell & T-SQL scripts are mentioned below.

PowerShell script does inline task.

  • Download T-log backups consolidated zip file securely from vendor sftp location. Following cases script gets terminated
    • SFTP folder doesn’t exists
    • Local folder doesn’t exists
    • Zip file already downloaded
    • Zip file is corrupted
  • Unzip downloaded file at given location.
  • Kick off T-SQL agent job if expected no of log files unzipped from daily zip file
try
{
    # Load WinSCP .NET assembly
    Add-Type -Path "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"

    $sessionOptions = New-Object WinSCP.SessionOptions
    $sessionOptions.Protocol = [WinSCP.Protocol]::Sftp
    $sessionOptions.HostName = "192.168.100.121" # Replace with actual host ip address for SFTP
    $sessionOptions.UserName = "SecuredData"
    $sessionOptions.Password = ""
    $sessionOptions.PortNumber = "15000"
    $sessionOptions.SshPrivateKeyPath = "D:\LogShippingData\SecureDataLinkFile.ppk"
    $sessionOptions.SshHostKeyFingerprint = "ssh-rsa 2048 d0:03:6g:ef:45:gg:66:3e:22:d5:82:ad:82:ac:10:10"

    $servername = "XYZ\ABC"

    $session = New-Object WinSCP.Session

    try
    {
        # Connect
        $session.Open($sessionOptions)

        # Variables declarati
        $stamp = ((Get-Date).AddDays(0).ToString("yyyMMdd"))
        $fileName = "SecuredData_$stamp.zip"
        $remotePath = "/VendorSFTPPath/$fileName"
        $localPath = "D:\LogShippingData\$fileName" #change path if NOT "D:\LogShippingData\" (for PROD run)
        
        $UnzipFolderUNCPath = "D:\LogInbox"
        $UnzipFolderUNCPathfiletype = "*.TRN"

        if ($session.FileExists($remotePath))
        {
            if (!(Test-Path $localPath))
            {
                Write-Host (
                    "File {0} exists, local backup {1} does not" -f
                    $remotePath, $localPath)
                $download = $True
            }
            else
            {
                $remoteWriteTime = $session.GetFileInfo($remotePath).LastWriteTime
                $localWriteTime = (Get-Item $localPath).LastWriteTime

                if ($remoteWriteTime -gt $localWriteTime)
                {
                    Write-Host (
                        ("File {0} as well as local backup {1} exist, " +
                         "but remote file is newer ({2}) than local backup ({3})") -f
                        $remotePath, $localPath, $remoteWriteTime, $localWriteTime)
                    $download = $True
                }
                else
                {
                    Write-Host (
                        ("File {0} as well as local backup {1} exist, " +
                         "but remote file is not newer ({2}) than local backup ({3})") -f
                        $remotePath, $localPath, $remoteWriteTime, $localWriteTime)
                    $download = $False
                }
            }

            # Ensuring download from FTP is completed w/o issues and executing following pieces.
            if ($download)
            {
                # Download the file and throw on any error
                $session.GetFiles($remotePath, $localPath).Check()

                Write-Host "Download to backup file done."

                # Ensuring destination of unzipped files is Empty
                $Files = Get-ChildItem $UnzipFolderUNCPath -include $UnzipFolderUNCPathfiletype -recurse #| where {$_.CreationTime  -le $DelDate_Backup }  

                if ( $Files -eq "" -or $Files.Count  -eq 0 ) 
                {
                    write-host "   No files to be deleted..."    #<-- this doesn't print when no files
                } 
                else 
                {
                    foreach ($File in $Files) 
                    {
                        write-host “Removing file [” $File "] from -->"  $UnzipFolderUNCPath 
                        Remove-Item $File | out-null
                    } 
                }

                #Unzipping latest downloaded zip file from eClinical SFTP
                write-host "   Unzipping today's downloaded file i.e. " $fileName
                $shell = new-object -com shell.application
                $zip = $shell.NameSpace($localPath)
                foreach($item in $zip.items())
                {
                    $shell.Namespace($UnzipFolderUNCPath).copyhere($item)
                    
                }

                $Files = Get-ChildItem  $UnzipFolderUNCPath -Recurse | Where-Object {!$_.PSIsContainer} | Measure-Object
                
                if ( $Files.Count -ge 90) 
                {
                    write-host "   Unzipping completed, kicking off Daily DB Sync job.."
                    #Kick of Daily Sync Job on [XYZ\ABC] SQL instance 

                    $SB = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=$servername"
                    $SQLcon = New-object system.data.sqlclient.SqlConnection
                    $SQLcon.ConnectionString = $SB
                    $SelectCMD = New-object system.data.sqlclient.SqlCommand
                    $SelectCMD.CommandTimeout = 30
                    $SelectCMD.Connection = $SQLCon
                    $SelectCMD.CommandText = "exec sp_start_job 'Daily Log Shipping Sync'"
                    $da = new-object System.Data.SqlClient.SQLDataAdapter($SelectCMD)
                    $ds = new-object System.Data.dataset
                    $da.fill($ds)
                    write-host "   Daily DB Sync job kicked off at SQL level, please look at [XYZ\ABC]... All steps completed under this execution."                  
                    $sqlcon.Close()
                }
             
            }
        }
        else
        {
            Write-Host ("File {0} does not exist yet" -f $remotePath)
        }
    }
    finally
    {
        # Disconnect, clean up
        $session.Dispose()
    }

    exit 0
}
catch [Exception]
{
    Write-Host $_.Exception.Message
    exit 1
}

Below T-SQL script does inline task.

   
  • Read all unzipped TRN backup files sequentially to avoid termination due to log sequence no.
  • Put header information in a table.
  • Restore T-Log backup to Log shipping database based upon sequence no.
  • Send alert on success & failure respectively.
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Daily SecuredDB Sync', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'This job reads all T-log backup files in sequential order and push information to Log shipping database.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Starting Email Notification', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE
				 @rptdate  datetime
				,@subject NVARCHAR(200)    
				,@message NVARCHAR(1000)
				,@emails as nvarchar(500)
				,@emailsCC as nvarchar(500)
				,@dt as varchar(20)

				SET @dt= REPLACE(CONVERT(NVARCHAR,CAST(getdate() AS DATETIME), 106), '' '', ''-'')
				--PRINt @dt

				SET @rptdate =  convert(datetime, convert(char, GETDATE(), 101))
				SET @subject = N''Daily DB Syc Started: '' + convert(char, GETDATE(), 101) 
				SET @emails = ''Avanish@sqlservergeeks.com''
				SET @emailsCC = ''Avanish@sqlservergeeks.com''
				SET @message = ''<body>
				<p><font color="#000080">Please</font> <font color="#FF0000"> DO NOT </font><font color="#000080"> use [SecuredDB] DB till futher notice.</p>
				<p><font color="#000080">An automated notification will go out based upon completion status.</p>
				<p><i>This is an automated email, please do not reply back to this email account.</i></p>
				</body>''
				EXEC msdb.dbo.sp_send_dbmail
					 @profile_name = ''SQL Monitor''
					,@body_format = HTML
					,@recipients = @emails
					,@copy_recipients = @emailsCC
					,@body = @message
					,@subject = @subject
				', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBSync', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=4, 
		@on_success_step_id=4, 
		@on_fail_action=4, 
		@on_fail_step_id=3, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'SET NOCOUNT ON
				USE [SQLMaint]
				GO
				--1. Truncate tables before any data load
				TRUNCATE TABLE [StageFiles]
				TRUNCATE TABLE [TransLog_Files]
				--DROP TABLE [HeaderMaster]

				-- Declare variables
				-- 
				DECLARE @InsertFileDetails as varchar(1000)
						,@LogFileName varchar(500)
						,@LogDate varchar(50)
						,@getTRNFileName  CURSOR
						,@getRestoreFile CURSOR

				DECLARE @headers table
				( 
					BackupName varchar(256),
					BackupDescription varchar(256),
					BackupType varchar(256),        
					ExpirationDate varchar(256),
					Compressed varchar(256),
					Position varchar(256),
					DeviceType varchar(256),        
					UserName varchar(256),
					ServerName varchar(256),
					DatabaseName varchar(256),
					DatabaseVersion varchar(256),        
					DatabaseCreationDate varchar(256),
					BackupSize varchar(256),
					FirstLSN varchar(256),
					LastLSN varchar(256),        
					CheckpointLSN varchar(256),
					DatabaseBackupLSN varchar(256),
					BackupStartDate varchar(256),
					BackupFinishDate varchar(256),        
					SortOrder varchar(256),
					CodePage varchar(256),
					UnicodeLocaleId varchar(256),
					UnicodeComparisonStyle varchar(256),        
					CompatibilityLevel varchar(256),
					SoftwareVendorId varchar(256),
					SoftwareVersionMajor varchar(256),        
					SoftwareVersionMinor varchar(256),
					SoftwareVersionBuild varchar(256),
					MachineName varchar(256),
					Flags varchar(256),        
					BindingID varchar(256),
					RecoveryForkID varchar(256),
					Collation varchar(256),
					FamilyGUID varchar(256),        
					HasBulkLoggedData varchar(256),
					IsSnapshot varchar(256),
					IsReadOnly varchar(256),
					IsSingleUser varchar(256),        
					HasBackupChecksums varchar(256),
					IsDamaged varchar(256),
					BeginsLogChain varchar(256),
					HasIncompleteMetaData varchar(256),        
					IsForceOffline varchar(256),
					IsCopyOnly varchar(256),
					FirstRecoveryForkID varchar(256),
					ForkPointLSN varchar(256),        
					RecoveryModel varchar(256),
					DifferentialBaseLSN varchar(256),
					DifferentialBaseGUID varchar(256),        
					BackupTypeDescription varchar(256),
					BackupSetGUID varchar(256),
					CompressedBackupSize varchar(256),        
					Containment varchar(256),
					-- Additional Filed to retain order by
					Seq int NOT NULL identity(1,1)
				); 


					CREATE TABLE #HeaderMaster  
					( 
						TRNFileName varchar(1000),
						DatabaseName varchar(256),
						BackupName varchar(256),
						BackupDescription varchar(256),
						BackupType varchar(256),        
						ExpirationDate varchar(256),
						Compressed varchar(256),
						BackupSize varchar(256),
						FirstLSN varchar(256),
						LastLSN varchar(256),        
						CheckpointLSN varchar(256),
						DatabaseBackupLSN varchar(256),
						BackupStartDate varchar(256),
						BackupFinishDate varchar(256),        
 						Seq int NOT NULL identity(1,1)
					); 

					--2. Load files into a staging table
					INSERT INTO [StageFiles] EXEC xp_cmdshell ''dir D:\LogShippingData\*.trn'';

					--3. Insert files into a better formatted table sorted by date. 
					INSERT INTO [TransLog_Files] 
					SELECT   CAST(left(Filename,20) as datetime) AS LogDate
					,REVERSE(SUBSTRING(REVERSE(Filename),0,CHARINDEX('' '',REVERSE(Filename)))) as filename
					FROM  [StageFiles] 
					WHERE isnumeric(left(Filename,1))=1       
					ORDER BY CAST(left(Filename,20) as datetime) asc 

					SET @getTRNFileName = CURSOR FOR
						SELECT * FROM  [TransLog_Files] 
						OPEN @getTRNFileName
						   FETCH NEXT
								  FROM @getTRNFileName INTO @LogDate,@LogFileName
								  WHILE @@FETCH_STATUS = 0
								  BEGIN
										 SET @InsertFileDetails =''RESTORE HEADERONLY FROM DISK = ''''D:\LogShippingData\''+ @LogFileName +''''''''
										 INSERT INTO @headers EXEC (@InsertFileDetails)
										 INSERT INTO #HeaderMaster 
										 (
												TRNFileName,
												DatabaseName,
												BackupName,
												BackupDescription,
												BackupType,
												ExpirationDate,
												Compressed,
												BackupSize,
												FirstLSN,
												LastLSN,
												CheckpointLSN,
												DatabaseBackupLSN,
												BackupStartDate,
												BackupFinishDate 
										 )
										SELECT	
												@LogFileName,
												DatabaseName,	
												BackupName,
												BackupDescription,
												BackupType,
												ExpirationDate,
												Compressed,
												BackupSize,
												FirstLSN,
												LastLSN,
												CheckpointLSN,
												DatabaseBackupLSN,
												BackupStartDate,
												BackupFinishDate 
										FROM @headers
										DELETE FROM @headers
										SET @InsertFileDetails =''''
								  FETCH NEXT
								  FROM @getTRNFileName INTO @LogDate,@LogFileName
								  END
							CLOSE @getTRNFileName
							DEALLOCATE @getTRNFileName

					DECLARE @TRNFileName varchar(1000)
							, @DatabaseName varchar(20)

					SET @getTRNFileName = CURSOR FOR
						   SELECT TRNFileName FROM  [#HeaderMaster] order by LastLSN asc
						   OPEN @getTRNFileName
						   FETCH NEXT
								  FROM @getTRNFileName INTO @LogFileName
								  WHILE @@FETCH_STATUS = 0
								  BEGIN         
								  SET @InsertFileDetails = ''RESTORE LOG [SecuredDB] '' + ''FROM  DISK = N''+ '''''''' + ''D:\LogShippingData\'' + @LogFileName + '''''''' + 
								  ''WITH  FILE = 1,  STANDBY = N'' + '''''''' + ''D:\Backup\'' + @LogFileName + ''.BAK'' + '''''''' + '','' + ''NOUNLOAD,  STATS = 10;''
								  --PRINT @InsertFileDetails
								  EXEC (@InsertFileDetails)
							FETCH NEXT	
							FROM @getTRNFileName INTO @LogFileName
							END
					CLOSE @getTRNFileName
					DEALLOCATE @getTRNFileName
					--DROP TABLE #HeaderMaster
					SET NOCOUNT OFF
					--SELECT * from #HeaderMaster order by LastLSN asc', 
							@database_name=N'master', 
							@output_file_name=N'D:\LOGFILES\DailyDBSync.log', 
							@flags=14
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Failed', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=2, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE
				 @rptdate  datetime
				,@subject NVARCHAR(200)    
				,@message NVARCHAR(1000)
				,@emails as nvarchar(500)
				,@emailsCC as nvarchar(500)
				,@dt as varchar(20)

				SET @dt= REPLACE(CONVERT(NVARCHAR,CAST(getdate() AS DATETIME), 106), '' '', ''-'')
				--PRINt @dt

				SET @rptdate =  convert(datetime, convert(char, GETDATE(), 101))
				SET @subject = N''Daily SecuredDB DB Syc :  '' + convert(char, GETDATE(), 101) 
				SET @emails = ''Avanish@sqlservergeeks.com''
				SET @emailsCC = ''Avanish@sqlservergeeks.com''

				SET @message = ''<body>
				<p><font color="#000080">DB Sync </font> <font color="#FF0000"> FAILED </font><font color="#000080"> for '' + @dt + ''.</p>
				<p><font color="#000080">DBA team will work for resolution. Please wait for communication.</p>
				<p><i>This is an automated email, please not reply back to this email account.</i></p>
				</body>''
				EXEC msdb.dbo.sp_send_dbmail
					 @profile_name = ''SQL Monitor''
					,@body_format = HTML
					,@recipients = @emails
					,@copy_recipients = @emailsCC
					,@body = @message
					,@subject = @subject
					', 
					@database_name=N'master', 
					@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Success', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE
				 @rptdate  datetime
				,@subject NVARCHAR(200)    
				,@message NVARCHAR(1000)
				,@emails as nvarchar(500)
				,@emailsCC as nvarchar(500)
				,@dt as varchar(20)

				SET @dt= REPLACE(CONVERT(NVARCHAR,CAST(getdate() AS DATETIME), 106), '' '', ''-'')
				--PRINt @dt

				SET @rptdate =  convert(datetime, convert(char, GETDATE(), 101))
				SET @subject = N''Daily SecuredDB DB Syc :  '' + convert(char, GETDATE(), 101) 
				SET @emails = ''Avanish@sqlservergeeks.com''
				SET @emailsCC = ''Avanish@sqlservergeeks.com''
				SET @message = ''<body>
				<p><font color="#000080">DB Sync completed </font> <font color="#437C17"> SUCCESSFULLY </font><font color="#000080"> for '' + @dt + ''.</p>
				<p><font color="#000080">Daily Process job kicked off. Please monitor same.</p>
				<p><i>This is an automated email, please do not reply back to this email account.</i></p>
				</body>''
				EXEC msdb.dbo.sp_send_dbmail
					 @profile_name = ''SQL Monitor''
					,@body_format = HTML
					,@recipients = @emails
					,@copy_recipients = @emailsCC
					,@body = @message
					,@subject = @subject
				GO
', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

I strongly believe, there is always chance of betterment, so suggestions are most welcome.

Happy learning.

Thank you!
Avanish Panchal
Regional Head – DataPlatformGeeks & DPS2017 Core Team Member
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Avanish Panchal on Twitter | Follow Avanish Panchal on FaceBook

   

Related Posts

SQL Server Query Cost, Memory Grant & SQLRESERVATIONS Clerk

SQL Server Memory Troubleshooting

Performance Monitor Counters

About Avanish Panchal

Avanish carries around 15 years of experience in IT industry. He is post graduate in Computer Science, followed by Masters in Business Administration. He has worked on multiple technologies like SQL Server, .net & Sybase with world largest bank(s)/IT consulting firm(s) like JPMorganChase, CapGemini, Datamatics etc. Currently holds position of Database Architect in BioPharma Global Leader. His area of focus are SQL Server DB Engine, Security, Storage, Virtualization & Infrastructure Consolidation. Passionate for delivering Database IT Infrastructure to satisfy and exceed the needs of the enterprise. You may find him active on various forums like SQLBangalore, SQLServerGeeks & many more. His SQL Server passion helped him to be the Core Team member of Asia's First SQL Server Conference in 2015 @ Bangalore (#SSGAS2015).

View all posts by Avanish Panchal →

Leave a Reply

Your email address will not be published.