Optimizations in protecting SQL databases with high churn by DPM

This blog discusses solutions for the two issues,which might be encountered while protecting high churn databases using DPM.

 

Incremental backup job triggered by DPM transfers transaction logs from SQL Server to the DPM Server.

 

One of the issues with this behaviour is, at times these transaction logs might be huge that they occupy considerable space on the SQL server where SQL DB is being stored.  This issue can be resolved by replacing the folder location, where the transaction logs are stored, with the symbolic link pointing to cheaper disk location.

 

Transaction log files are stored in a folder name “DPM_SQL_POTECT\” + “SQL Server instance name\” + “Database name” + “_log.ldf\Backup\”. This folder is created at the same location as the log definition file location.  DPM_SQL_PROTECT can be replaced by symbolic link which points to cheaper disk location.

 

Example:

Note:
  Assumptions

  •   SQL Server instance name is “SQLSRVINSTANCE”
  •   The DB protected by DPM is “ProtSrvDB”
  •   The log definition files are stored at
      C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
  •   The transaction log files are stored in C:\Program
      Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ DPM_SQL_PROTECT\SQLSRVINSTANCE\ProtSrvDB_log.LDF\Backup\.
     
  •   There is a cheaper disk array mounted on E:\
  •   SQL Server instance name is “SQLSRVINSTANCE”
  •   The DB protected by DPM is “ProtSrvDB”

 

 

         

mklink /D “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DPM_SQL_PROTECT”
    “E:\BackupTest”

cd E:\BackupTest      

mkdir \SQLSRVINSTANCE\ProtSrvDB_log.LDF\Backup

   

 

 

 

Other issue is, at times the transaction log space might be filed up due to high churn. This might lead to transaction failure. This issue can be avoided by placing appropriate check on the transaction log space being filled. Whenever the transaction log space is about to fill flush the transaction logs to the place where backup job expects them to be located. The below example demonstrates the same.

 Example: Create below alert which can flush the transaction logs as and when the transactions log space is about to be filled. These get copied to location where the DPM Job can pick the logs.

 

Note:
  The below example assumes the symbolic link mentioned above. If above symbolic link has not been created, then replace “E:\BackupTest” with location of “DPM_SQL_Protect”.

 

Create a Job as mentioned below 

      

 

-- create backup log job      

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'backup
    log ProtSrvDB',  

            @enabled=1, 

            @notify_level_eventlog=0,   

            @notify_level_email=0,   

            @notify_level_netsend=0,   

            @notify_level_page=0,   

            @delete_level=0,   

            @description=N'Backup log.',   

            @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'create folder',

            @step_id=1,

            @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='EXEC
    master.dbo.xp_cmdshell ''MD " E:\BackupTest\SQLSRVINSTANCE\ProtSrvDB_log.LDF\Backup"''',
            @database_name=N'ProtSrvDB',

            @flags=0

IF (@@ERROR <> 0 OR
    @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode =
    msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'backup log',

            @step_id=2,

            @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 @file_name
    varchar(255)

set @file_name = ''
    E:\BackupTest\SQLSRVINSTANCE\ProtSrvDB_log.LDF\Backup\anything'' +
    replace(replace(convert(varchar(100),getdate(),126),''-'',''''),'':'','''') + ''.log''

   

backup log ProtSrvDB to disk
    = @file_name',  @database_name=N'ProtSrvDB',

        @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   

   

 

 

Check the Job ID

    

---- Get the Job ID --

SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name =  N'backup log ProtSrvDB'

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

 

 

 

Assign the alert with relevant performance condition

      
   

 -- create alert   

 USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert
    @name=N'alert log ProtSrvDB1',

           @message_id=0,

            @severity=0,

            @enabled=1,

            @delay_between_responses=60,

          @include_event_description_in=5,

          @category_name=N'[Uncategorized]',

         @performance_condition=N'Databases|Percent Log Used|ProtSrvDB|>|75',
         
    @job_id=N'534FE82D-5B7E-4A6F-ADA2-1067B6981D06'

GO   

 

 

No Comments