Perform Automatic Switchover in Log Shipping

-

 DR drill is one of the major activities of every DBA’s life. And if log shipping is in the picture, then we have to follow a long checklist. Many things we have to check like sync status, disable and top LS jobs on primary and secondary, get tail log backup, copy and restore the same, etc. 

In such drills, achieving RTO and RPO is very important. Most of us might have come across the situation due to a small miss switch over or switch back got delayed.

What if we could automate switchover and switch back activity in log shipping. If this could happen then we can easily achieve the RPO and RTO also human error can be avoided.

I have tried to automate this process. I’m not too good in script writing. But I tried my best to complete this. I have written the scripts which perform the switchover without manual intervention. We just need to provide the database name for which switchover needs to be performed and then simply execute the script.

There are two scripts written. One script needs to execute on the primary server and another one needs to execute on a secondary server.

What do those scripts? we will discuss it here.

The working of the primary server script is as below.

  1. Provide the database name in a script for which switchover needs to be performed.
  2. Once, the database name is provided, the script will check whether the database is configured in log shipping or not. If not, then it will notify the same.  If the database is configured in log shipping, then further checks will perform.
  3. The script will check whether the database is in sync or not. If not, then the script will notify the same and skip further operations.
  4. Once the sync status is validated and found to be good, the script will check active sessions on a database. If there are any, then it will kill the same.
  5. Further, the script will fetch the LS_Backup job name and disable the same.
  6. Once the LS_Backup job is disabled, then the script will execute that job to ensure data must be backed up till that point.
  7. If in case LS_Backup job is running, the script will wait for its completion.
  8. Further, the script will take tail log backup in the folder which is configured for log backup on the primary server and get the database in STAND_BY mode.  Before taking the tail log backup, the script will ensure the LS_Backup job is not running.
  9. Once the tail log backup is taken, the script will print the last log backup date and time. We have to match the same for the secondary database.
  10. I would recommend to save the output after the execution of the script.
  11. Script for the secondary server is nothing but executing LS_Copy and LS_Restore job. But it has also a few checks which are given below.
  12. In the secondary script also have to provide the database name.
  13. Same as the primary server script, it will check whether the database is configured in log shipping or not. Also, it will ensure that sync status is good.
  14. Next, the script will get details of LS_Copy and LS_Restore jobs and disable those.
  15. Once jobs are disabled, the script will change the log shipping restoring mode to STAND_BY.
  16. Next, LS_Backup and LS_Restore jobs will get executed. If those are running, then the script will wait for its completion.
  17. The script will print the date and time of the last log restored to a secondary database.   

Note, we can execute the second script multiple times till the last restored date and time are not matching with primary databases.

Scripts have been tested for the above scenarios and found to be working fine.

Below is the code given for the script. You can test the same at your end and provide your feedback.

Please note, use the below scripts in production after detailed testing and at your own risk.

Script 1:

To be executed on a Primary Server:

USE [master]

------------------------------------------------------------------------------------

--Take log backup with standby

------------------------------------------------------------------------------------

SET NOCOUNT ON

Declare @BackupPath as varchar(100)

Declare @db_name as varchar(100)

-----------CHANGE HERE --------------------

set @db_name = 'AdventureWorks'  -- Exm : AdventureWorks

set @BackupPath=(select backup_directory from 

msdb..log_shipping_primary_databases where primary_database=@db_name)

--------------------------------------------

DECLARE @BackupFile as VARCHAR(250)

DECLARE @UndoFile as VARCHAR(250)

DECLARE @SQLSTR AS NVARCHAR(500)

DECLARE @DateTime as VARCHAR(50)

DECLARE @YYYY AS VARCHAR(4)

DECLARE @M AS VARCHAR(2)

DECLARE @D AS VARCHAR(2)

DECLARE @HH AS VARCHAR(2)

DECLARE @MIN AS VARCHAR(2)

DECLARE @PJOB AS NVARCHAR(500)

DECLARE @kill AS varchar(8000) = ''; --kill session for db

DECLARE @DatabaseID AS SMALLINT = DB_ID(@db_name);

DECLARE @SQLKILL AS NVARCHAR(10)

DECLARE @DB_Lst_logbkp AS DATETIME

DECLARE @Curdate AS DATETIME

DECLARE @last_backup_min AS int

DECLARE @backup_threshold AS int

set @Curdate=getdate()

set @last_backup_min=(select DATEDIFF(mi, last_backup_date,@Curdate) from msdb..log_shipping_monitor_primary where primary_database=@db_name)

set @backup_threshold=(select backup_threshold from msdb..log_shipping_monitor_primary where primary_database=@db_name)

if exists (SELECT primary_database As 'Databases_For_Logshipping' FROM msdb..log_shipping_primary_databases where primary_database=@db_name)

begin

if (@last_backup_min>@backup_threshold)

begin

Print 'Log shipping for database ' +@db_name+ ' is not in synced. Kindly get it sync on priority !!!'

Print 'Backup is not performed since, ' +CONVERT( VARCHAR, @last_backup_min, 109)+ ' Mins'

Print 'Switch over activity has been cancelled.'

end

else

begin

---Kill Sessions

Print '*********************Killing sessions of primary database if any*********************'

WHILE EXISTS ( SELECT 1 FROM sys.dm_exec_sessions WHERE database_id = @DatabaseID )

BEGIN;

SET @SQLKILL = (SELECT TOP 1

N'kill ' + CAST(session_id AS NVARCHAR(5)) + ';'

FROM

sys.dm_exec_sessions

WHERE

database_id = @DatabaseID

);

EXEC sys.sp_executesql @SQLKILL;

print 'Sessions '+@SQLKILL+' on '+@db_name

END;

Print '*********************Disabling LS Backup job*********************'

set @PJOB=(

select b.name

from msdb..log_shipping_primary_databases a

join msdb..sysjobs b on b.job_id=a.backup_job_id

where primary_database=@db_name and b.name like 'LSBackup%')

  --print @PJOB

EXEC msdb.dbo.sp_update_job @job_name=@PJOB,@enabled = 0

print 'SQL JOB '+@PJOB+' has been disabled'

Print '*********************Executing LS Backup job*********************'

--Execute LS backup Job

BEGIN

WHILE 1 = 1

BEGIN

IF

EXISTS (SELECT 1 FROM msdb.dbo.sysjobactivity b

INNER JOIN msdb.dbo.sysjobs a ON b.job_id = a.job_id

WHERE b.start_execution_date IS NOT NULL

AND b.stop_execution_date IS NULL AND a.name=@PJOB)

BEGIN

WAITFOR DELAY '00:00:10';

CONTINUE;

END;

ELSE

BEGIN

EXEC msdb.dbo.sp_start_job @PJOB ;

print 'SQL JOB '+@PJOB+' has been executed successfully'

BREAK;

END;

END;

END;

--Take tail log backup

Print '*********************Initiating tail log backup*********************'

IF CAST(serverproperty ('ProductVersion') AS VARCHAR(1)) = '8'

BEGIN

--*** CREATING DATE PART

SET @YYYY = DATEPART(YYYY, GETDATE())

SET @M = DATEPART(m, GETDATE())

IF @M BETWEEN 1 AND 9

SET @M = '0'+@M

SET @D = DATEPART(d, GETDATE())

IF @D BETWEEN 1 AND 9

SET @D = '0'+@D

SET @HH = DATEPART(HH, GETDATE())

IF @HH BETWEEN 0 AND 9

SET @HH = '0'+@HH

SET @MIN = DATEPART(MINUTE, GETDATE())

IF @MIN BETWEEN 0 AND 9

SET @MIN = '0'+@MIN

SET @DateTime = @YYYY + @M + @D + @HH + @MIN

SET @BackupFile = @BackupPath+ '\' + @db_name + '_tlog_' + @DateTime 

+ '.TRN'

SET @UndoFile = @BackupPath+ '\' + @db_name + '_undo_' + @DateTime + '.dat'

END

ELSE

BEGIN

--*** CREATING DATE PART

SET @YYYY = DATEPART(YYYY, GETUTCDATE())

SET @M = DATEPART(m, GETUTCDATE())

IF @M BETWEEN 1 AND 9

SET @M = '0'+@M

SET @D = DATEPART(d, GETUTCDATE())

IF @D BETWEEN 1 AND 9

SET @D = '0'+@D

SET @HH = DATEPART(HH, GETUTCDATE())

IF @HH BETWEEN 0 AND 9

SET @HH = '0'+@HH

SET @MIN = DATEPART(MINUTE, GETUTCDATE())

IF @MIN BETWEEN 0 AND 9

SET @MIN = '0'+@MIN

SET @DateTime = @YYYY + @M + @D + @HH + @MIN + '00'

SET @BackupFile = @BackupPath+ '\' + @db_name + '_' + @DateTime + '.trn'

SET @UndoFile = @BackupPath+ '\' + @db_name + '_undo_' + @DateTime + '.dat'

END

set @SQLSTR='BACKUP LOG [' + @db_name + '] TO DISK = ''' + @BackupFile +''' WITH STANDBY = ''' + @UndoFile +''' , STATS = 25'

BEGIN

WHILE 1 = 1

BEGIN

IF

EXISTS (SELECT 1 FROM msdb.dbo.sysjobactivity b

INNER JOIN msdb.dbo.sysjobs a ON b.job_id = a.job_id

WHERE b.start_execution_date IS NOT NULL

AND b.stop_execution_date IS NULL AND a.name=@PJOB)


BEGIN

WAITFOR DELAY '00:00:10';

CONTINUE;

END;

ELSE

BEGIN

print @SQLSTR

EXECUTE  sp_executesql @SQLSTR

BREAK;

END;

END;

END;

set @DB_Lst_logbkp=(select  max(backup_finish_date)

from msdb..backupset

where database_name=@db_name

GROUP BY database_name)


Print '*********************Check date and time of last log backup*********************'

print 'Last log backup date of Database '+@db_name+' is ' +CONVERT( 

VARCHAR, @DB_Lst_logbkp, 109)

END

END

ELSE

begin

print 'Database '+@db_name+' is not configured for log shipping.'

print 'Switch over actvity can not be performmed.'

END


Script 2 :

To be executed on a Secondary server:


USE [master]

------------------------------------------------------------------------------------

-- Restore log backup

-- Script can be execute multiple time till data dosnt get sync

------------------------------------------------------------------------------------

SET NOCOUNT ON

Declare @db_name as varchar(100)

-----------CHANGE HERE --------------------

set @db_name = 'AdventureWorks' -- Exm : AdventureWorks

--------------------------------------------

DECLARE @SQLSTR AS NVARCHAR(500)

DECLARE @CopyJOB AS NVARCHAR(500)

DECLARE @RestoreJOB AS NVARCHAR(500)

DECLARE @DB_Lst_logbkp AS datetime

DECLARE @last_restored_file AS nvarchar(1000)

DECLARE @Curdate AS DATETIME

DECLARE @last_restored_min AS int

DECLARE @restore_threshold AS int


set @Curdate=getdate()

set @Curdate=getdate()

set @last_restored_min=(select DATEDIFF(mi, last_restored_date,@Curdate) from msdb..log_shipping_monitor_secondary where secondary_database=@db_name)

set @restore_threshold=(select restore_threshold from msdb..log_shipping_monitor_secondary where secondary_database=@db_name)


if exists (SELECT secondary_database As 'Databases_For_Logshipping' FROM msdb..log_shipping_secondary_databases where secondary_database=@db_name)


begin

if (@last_restored_min>@restore_threshold)

begin

Print 'Log shipping for database ' +@db_name+ ' is not in synced. Kindly get it sync on priority !!!'

Print 'Log is not restored from ' +CONVERT( VARCHAR, @last_restored_min, 109)+ ' Mins'

Print 'Switch back activity has been cancelled.'

END;


else

begin

--Disable LS backup Job

Print '*********************Disabling LS Copy/Restore job*********************'

set @CopyJOB=(

select b.name

from msdb..log_shipping_secondary a

join msdb..sysjobs b on b.job_id=a.copy_job_id

where a.primary_database=@db_name and b.name like 'LScopy%')

--print @CopyJOB

 set @RestoreJOB=(

 select b.name

 from msdb..log_shipping_secondary a

 join msdb..sysjobs b on b.job_id=a.restore_job_id

 where a.primary_database=@db_name and b.name like 'LSRestore%')


  --print @RestoreJOB

EXEC msdb.dbo.sp_update_job @job_name=@CopyJOB,@enabled = 0

EXEC msdb.dbo.sp_update_job @job_name=@RestoreJOB,@enabled = 0

print 'SQL JOB '+@CopyJOB+' has been disabled'

print 'SQL JOB '+@RestoreJOB+' has been disabled'


--Get Restoration mode in standby

Print '*********************Changing restoration mode to STAND BY*********************'

Begin

EXEC sp_change_log_shipping_secondary_database

@secondary_database =@db_name,

@restore_delay = 0,

@restore_all = 1,

@restore_mode = 1,

@disconnect_users = 1

print 'Restoration mode of '+@db_name+' has been changed to STANDBY MODE'

END;

Print '*********************Executing LS Copy/restore job*********************'

BEGIN

WHILE 1 = 1

BEGIN

IF

EXISTS (SELECT 1 FROM msdb.dbo.sysjobactivity b

INNER JOIN msdb.dbo.sysjobs a ON b.job_id = a.job_id

WHERE b.start_execution_date IS NOT NULL

AND b.stop_execution_date IS NULL AND a.name=@CopyJOB)


BEGIN

WAITFOR DELAY '00:00:10';

CONTINUE;

END;

ELSE

BEGIN

EXEC msdb.dbo.sp_start_job @CopyJOB ;

print 'SQL JOB '+@CopyJOB+' has been executed successfully'

BREAK;

END;

END;

END;


BEGIN

WHILE 1 = 1

BEGIN

IF

EXISTS (SELECT 1 FROM msdb.dbo.sysjobactivity b

INNER JOIN msdb.dbo.sysjobs a ON b.job_id = a.job_id

WHERE b.start_execution_date IS NOT NULL

AND b.stop_execution_date IS NULL AND a.name=@RestoreJOB)


BEGIN

WAITFOR DELAY '00:00:10';

CONTINUE;

END;

ELSE

BEGIN

EXEC msdb.dbo.sp_start_job @RestoreJOB ;

print 'SQL JOB '+@CopyJOB+' has been executed successfully'

BREAK;

END;

END;

END;

set @DB_Lst_logbkp=(select  max(backup_finish_date)

from msdb..backupset

where database_name=@db_name

GROUP BY database_name)


Print '*********************Check date and time of last log backup*********************'

print 'Last log backup date of Database '+@db_name+' is ' +CONVERT( 

VARCHAR, @DB_Lst_logbkp, 109)

END;

END;

ELSE

begin

print 'Database '+@db_name+' is not configured for log shipping.'

print 'Switch back actvity can not be performmed.'

END;

Below are snapshots of the script output.





Comments

Post a Comment

Popular posts from this blog

What is DACPAC and how to use it?

-
Image
Few days before someone asked me a question about DACPAC and BACPAC. To be honest I was really not aware of it and I replied that I have heard these terms for the first time. That person replied DACPAC is used for deploying and upgrading database objects. Even after the clear statement, my reply was like developers would answer this better. Even though DBAs are responsible for migration and deployments. After few days, I got a requirement to migrate database schema (Only Schema) to another instance. In such a case most of us would have used the traditional way to script out the database objects and run the script on another instance. But what if there are many objects? I was exploring the easiest way to migrate the objects and got to know about DAC. After reading about DAC, I felt very sorry myself that I was not aware of such a simplest way of migrating and deploying database objects. I went to my friends who are senior developers and asked this question. None of them was aware ...
Read more

Backdoor in SQL server. Is this hacking, loop hole or Feature ?

-
Image
DBAs should always be ready to face any kind of disaster in SQL environment. That doesn't matter whether it is PROD or Non- PROD.  Recently I have been asked regarding recovering the sysadmin password. I couldn't understand the question hence investigated more to understand actual problem. Then I came to know that there is one SQL Server for which admin login and password is lost. I was Like seriously ? How one can be that much of irresponsible? But rather than getting into that kind of discussion I quickly got ready to help the team.  In SQL Server there is a way to access SQL Instance without login. Lets check that out. Steps : 1) Open SQL Server Configuration  Manager. 2) Right click on SQL Server service and go to Properties. 3) Go to "Startup Parameters" Tab and Add -m.   This will Start the server in Single user mode.  4) Click Apply and Ok. This change would require Service restart.  5) Once Service is restarted then, open CMD as Administrator. ...
Read more

Always on setup end to end - Part 1 || Pre-requisites

-
Image
Hello readers,  Welcome to the new blog after a long time. In this blog I would like to discuss complete end to end always on setup guide for beginners. I will be posting couple of blogs regarding always on in SQL server. I believe that would be helpful to understand Always on in SQL server in easy way. Always on setup involved multiple steps. Hence one may find it difficult. However, if you could plan it well then you may find that always on is much easier to configure and handle. So, without wasting the time lets proceed further. Firstly, we will see what is Always on in SQL Server ? Always on is the high availability and disaster recovery solution introduced in SQL server 2012. As the name suggest, Always on means availability of database all the time even in case of disaster. We can say it is the alternative of mirroring. Only thing is windows failover cluster service also required to be configured as a pre-requisite for always on. In this blog, we will only d...
Read more