Update: This work has been expanded upon in the following post – DD Boost T-SQL Scripting Toolkit
EMC recently released Data Domain Boost for Microsoft Applications. This tool includes a SQL Management Studio plugin that allows SQL backups via a familiar SQL GUI or a CLI application.
EMC whitepaper – The business value of Data Domain Boost
This tool does a good job of keeping control of backups in the hands of a DBA and taking advantage of the benefits of Data Domain.
While helping a client recently, I had a chance to dig into DD Boost for SQL and do some testing. This post outlines some key things I learned about the architectural aspects of the software. Also some potential operational impact organizations should be aware of when planning an implementation. The amount of impact on your particular organization will vary depending on how SQL backups are managed today. In most cases the backups are currently managed by the SQL DBA’s. This is a common reason organizations choose DD Boost for SQL.
In this scenario there are some important questions to ask.
- Are backups managed by T-SQL in agent jobs?
- Are backups managed with maintenance plans?
- What automation is done in jobs? Particularly automation in restores.
- What logging and reporting is done today?
- Can TSQL backup routines be modified?
- Can xp_cmdshell be enabled on servers?
Many or most DBA’s have T-SQL procedures running the backups. These procedures commonly have additional logic for other operations, logging and reporting. The Data Domain boost plugin for SQL server runs as a command line executable. It has a GUI front end to help create the commands or run the process directly.
The backup program is ddbmsqlsv.exe and the restore program is ddbmsqlrc.exe. The DD Boost administrator guide recommends using windows task scheduler to execute theses commands and manage backup and restore jobs. This presents an issue for many DBA’s because they have a much greater set of functionality in the SQL Agent job engine. Additionally, agent jobs and T-SQL procedures have easy access to all relevant data in the SQL environment needed to drive the logic of the DBA’s operational processes.
This hurdle can be overcome, but there is tradeoff and compromise involved. The DD Boost plugin can be run from T-SQL scripts using the xp_cmdshell extended stored procedure. This does allow the DBA’s to keep the backup jobs and processes running in T-SQL and the agent. The trade-off will be the increased attack surface for SQL server. The following command examples show the use of xp_cmdshell with the DD Boost applications.
|
|
exec xp_cmdshell 'ddbmsqlsv.exe -c SQL2012-01.vlab.local -l full -N "Test03" -b "Test03" -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=dd-01.vlab.local" -a "NSR_DFA_SI_DD_USER=ddboost" -a "NSR_DFA_SI_DEVICE_PATH=/SQL" "MSSQL$INST1:DB1"' exec xp_cmdshell 'ddbmsqlrc.exe -c sql2012-01.vlab.local -f -S normal -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=dd-01.vlab.local" -a "NSR_DFA_SI_DD_USER=ddboost" -a "NSR_DFA_SI_DEVICE_PATH=/SQL" "MSSQL$INST1:DB1"' |
This method should work for most organizations, which are OK with the trade-offs. One of the trade-offs will likely be some logic change in the SQL agent jobs for backup. One question a DBA posed to me was a way to run the backup from a SQL Agent job and be able to report on success while keeping a log. Here is the quick solution I came up with. This should be a good start for most DBA’s and I am sure many can improve on this greatly.
The first step is to enable xp_cmdshell on the server and there are many places on the internet to show how to do this. Then we need to create a job for the backup.
The job has a single step to run the T-SQL.
The following T-SQL code runs the job.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
declare @cur cursor; declare @line nvarchar(4000); declare @rcode bit; Set @rcode = 1; DECLARE @t TABLE (LINE NVARCHAR(4000)) INSERT INTO @t exec xp_cmdshell 'ddbmsqlsv.exe -c SQL2012-01.vlab.local -l full -N "Test05" -b "Test05" -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=dd-01.vlab.local" -a "NSR_DFA_SI_DD_USER=ddboost" -a "NSR_DFA_SI_DEVICE_PATH=/SQL" "MSSQL$INST1:DB1"' Set @cur = Cursor for SELECT LINE FROM @t open @cur fetch next from @cur into @line while @@fetch_status = 0 begin IF @line like '%Backing up of DB1 succeeded.' BEGIN Set @rcode = 0 END print @line fetch next from @cur into @line end close @cur; Deallocate @cur; if @rcode = 1 raiserror('An error occured with the backup',16,1) |
This code uses an in memory table and a cursor to loop through the output of the DD Boost command. This allows the detection of success strings and if they are not found an error is raised to the job engine. This script also prints the command output, which can get captured for the history logs. I am sure there are many SQL gurus out there who can improve this code. Please post your improvements, they are welcome.
The screen shot below shows the job step advanced properties page. The “include step output in history” option will allow the output to be seen in the job activity history.
The screenshot below shows the job activity monitor log viewer, which has both successful and failed jobs.
The output of the backup command is stored in the message field in the history. This provides a way to go back and find out what happened to the backup.
I believe this method should be sufficient for most organizations except those with strict security requirements and a high level of automation in database restores. The security requirement is due to the use of xp_cmdshell, which some organizations may not want to use. The level of automation in restores could pose an issue because of how specific backup sets are accessed via the DD Boost application.
There is at least one situation I know of that could present issues. If automated database restores of particular point in times are required this may be very difficult to script reliably. With a normal SQL backup the information about that backup is recorded in tables in the MSDB database. When DD Boost for SQL does a backup it does invoke a SQL backup which logs the job information to the MSDB database. Although, the DD Boost plugin GUI appears to retrieve the backup image data from the Data Domain file system.
This causes an issue in scripting a DD Boost restore of a specific image. The command line tool allows for two modes to identify the backup image used for restore. It will restore the last backup by default or if the –t switch is supplied with a timestamp of the backup image it will use that particular image. The problem is the timestamp comes from the Data Domain file system timestamp for the backup image(I think, someone from EMC correct me if I am wrong). There may also be a way to query this, but it would be via SSH and I have not gone down that road.
In many environments DBA’s will use backup set or file naming schemes in their processes. Since the DD Boost app only allows timestamp to identify the backup the lack of timestamp correlation between DD and SQL causes an issue. The command and output below illustrates the issue.
ddbmsqlsv.exe -c SQL2012-01.vlab.local -l full -a “NSR_DFA_SI=TRUE” -a “NSR_DFA_SI_USE_DD=TRUE” -a “NSR_DFA_SI_DD_HOST=dd-01.vlab.local” -a “NSR_DFA_SI_DD_USER=ddboost” -a “NSR_DFA_SI_DEVICE_PATH=/SQL” “MSSQL$INST1:DB1”
Abbreviated Log Output
43708:(pid 3208):Start time: Wed Aug 06 10:55:44 2014
43621:(pid 3208):Computer Name: SQL2012-01 User Name: administrator
….
43709:(pid 3208):Stop time: Wed Aug 06 10:56:05 2014
This job does get logged in the MSDB database like other SQL jobs, but unfortunately the timestamps do not match up to the DD Boost data.
When looking at the DD Boost restore for the above backup. The time shown on this screen does not show the same backup start and end times as the msdb.backupset table. This is an issue because a backup could be found by description in a query, but DD Boost save sets cannot be referenced properly from DBA’s TSQL maintenance scripts as timestamps do not correlate.
ddbmsqlrc.exe -c sql2012-01.vlab.local -f -t “08/06/2014 10:55:45” -S normal -a “NSR_DFA_SI=TRUE” -a “NSR_DFA_SI_USE_DD=TRUE” -a “NSR_DFA_SI_DD_HOST=dd-01.vlab.local” -a “NSR_DFA_SI_DD_USER=ddboost” -a “NSR_DFA_SI_DEVICE_PATH=/SQL” “MSSQL$INST1:DB1”
It is not likely many organizations will have a level of automation in their SQL database restores to be caught by this, but it is one to be aware of if considering a DD Boost for SQL deployment. I am sure features will be added in future releases that will correct or eliminate this issue. In the meantime EMC, could you please provide a way for DBA’s to reference the DD Boost jobs/image files from T-SQL.
Regards,
Dave
