Optimizations in protecting SQL databases with high churn by DPM

[System-Center-Team]

First published on TECHNET on Aug 21, 2013
This article discusses two specific problems that you may encounter while protecting high churn databases using DPM. Both of these concern the amount of disk space used by the DPM log files in certain scenarios and how to you can mitigate the issues that can arise as a result.
Issue 1: Incremental backup job triggered by DPM transfers large transaction logs from SQL Server to the DPM Server.
When DPM initiates an incremental backup for a SQL database, it instructs SQL to dump all transaction log entries that were generated since the last incremental backup to a temporary log file. DPM then copies the new log file to the DPM server to make a new recovery point. One of the issues with this behavior is that sometimes the temporary transaction log file(s) can become extremely large and take up a considerable amount of space on the SQL server where the SQL DB is being stored. You can work around this by replacing the folder location where the temporary transaction logs are currently stored with a symbolic link pointing to a cheaper disk location, or to one with more available free space.

The temporary transaction log files are stored in a folder named “DPM_SQL_POTECT\” + “MachineName”+“SQL Server instance name\” + “Database name” + “_log.ldf\Backup\”. This folder is created in the same location as the log definition file location. The DPM_SQL_PROTECT folder can be replaced by symbolic link that points to a cheaper disk location or one with more available free space.

Example

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 temporary transaction log files are stored in C:\Program
  Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ DPM_SQL_PROTECT\MACHINENAME\SQLSRVINSTANCE\ProtSrvDB_log.LDF\Backup\.


• There is a cheaper disk array mounted on E:\

1. Move (cut and paste) C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DPM_SQL_PROTECT to E:\, then (optionally) rename the moved DPM_SQL_PROTECT folder to another name. In this example it was renamed to E:\BackupTest.

NOTE If you have multiple SQL instances or multiple different paths to the database log files, you will want to repeat these steps for each set of temporary logs you want to be redirected by using multiple linked folders.

2. Run the command below to remake the DPM_SQL_PROECT folder and link it to the E:\BackupTest folder.

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

Once this is complete, all temporary SQL log files will be redirected to the path under E:\BackupTest, thus freeing up the disk space that was required on C: volume.
Issue 2: The transaction log might fill up due to high churn, leading to transaction failure.
This issue can be avoided by placing an 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 the backup job expects them to be located. Here's an example:

In this example, we create an alert that can flush the transaction logs when the transactions log space is about to be full, then copy them to a location where the DPM job can find the logs.

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

First, create the job using this script:

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

Next, check the Job ID:

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

Now assign the alert with the relevant performance condition:

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

That's all there is to it.