Data Domain Boost for SQL – Deployment Planning Considerations for DBA’s

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.

  1. Are backups managed by T-SQL in agent jobs?
  2. Are backups managed with maintenance plans?
  3. What automation is done in jobs? Particularly automation in restores.
  4. What logging and reporting is done today?
  5. Can TSQL backup routines be modified?
  6. 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.

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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

33 comments

  1. Joe says:

    How are Backup Administrators able to prevent DBAs from filling up the Data Domains, I suppose this is the advantage a 3rd party backup clients may have over this method, retention times are still controlled by the Backup Admin.

  2. dmuegge says:

    Joe,

    That is a great point. A quota can be set on the Data Domain to limit the space available to the DBA’s

    Thanks,

    Dave

  3. Chad says:

    Fantastic write-up, Dave… thanks! After heading down the Avamar path and being less than impressed with it, we’re rerouting down the DD Boost path. I’m just starting some POC efforts. It was great to read this post.

    I don’t see anything in the Admin Guide about an option to restore to a file on the regular OS disk (should I need to provide a backup to a vendor for diagnosis, for instance). If that’s not an option, I’m curious if taking an “out-of-cycle” (native SQL) backup will break the recovery chain the same way it does with Avamar, as that would be my only other way to provide an on-disk backup file.

    My organization has stringent policies against enabling xp_cmdshell so I’ll probably just write a powershell wrapper for the newer versions of SQL since it can be called as a job type under a SQL Agent job. Will probably write an SSIS package for the SQL2005 instances.

    Real curious to see what the performance of backup/restore with this is, too.

  4. dmuegge says:

    Chad,

    Thanks for the comment and I am glad you found it useful. Regarding the question about off cycle backups. I have also not found a way to produce a native .bak file backup from the DD Boost backup data. I believe as long as you do a full database backup using copy only with the native SQL backup it should not affect the sequence. Although, this particular situation is on my list for some additional testing, I think it should work.

    I would like to hear how things go with performance. With small databases, high change rate databases, and log backups you should see similar performance to native SQL backups using compression. On full backups for larger databases with a lot of static data you should see better and possibly much better performance because less data will be sent over the wire.

    The PowerShell option is another angle on my list too. I worked on the xp_cmdshell route first because we get more requests for this from our customers and many organizations have it enabled for other purposes. We generally see more restrictions on this in financial and other highly regulated industries.

    Regards,

    Dave

  5. LBerkholtz says:

    I am using xp_cmdshell to execute this command for backup on a clustered server:

    ddbmsqlsv.exe -c SQLBITST.smead.us -A SQLBITST.smead.us -l full -N “Master” -y “01/09/2015 10:54:32” -a “NSR_DFA_SI=TRUE” -a “NSR_DFA_SI_USE_DD=TRUE” -a “NSR_DFA_SI_DD_HOST=HADD2500.smead.us” -a “NSR_DFA_SI_DD_USER=ddboost” -a “NSR_DFA_SI_DEVICE_PATH=/SQL_Test” “MSSQL:master”‘

    When I run this from the SQL Services account I use directly on the current cluster active node, it works fine.

    However when I do this via xp_cmdshell on the cluster, I get this error:

    A_SI_DEVICE_PATH=/SQL_Test” “MSSQL:master”
    43708:(pid 4644):Start time: Wed Dec 10 11:34:34 2014
    43621:(pid 4644):Computer Name: SQLBITST User Name: SQLExec2
    29100:(pid 4644): Invalid SQL virtual server name SQLBITST.smead.us
    38563:(pid 4644): Invalid option -A
    37758:(pid 4644): Usage: ddbmsqlsv [] {path}
    options: [-GjqRTvkuHX] [-N name] [-b description]
    [-S count] [-l level]
    [-A virtual-server] [-c client] [-y retention]
    -a “NSR_DFA_SI=[TRUE|FALSE]” -a “NSR_DFA_SI_USE_DD=[TRUE|FALSE]” -a “NSR_DFA_SI_DEVICE_PATH=”
    -a “NSR_DFA_SI_DD_HOST=” -a “NSR_DFA_SI_DD_USER=”
    [-a “NSR_DFA_SI_DD_PASSWORD=”]
    path: d-path | i-path
    d-path: MSSQL: | [MSSQL:]s-path [[MSSQL:]s-path […]]
    i-path: MSSQL$inst-name: | MSSQL$inst-name:s-path […]
    s-path: database
    43709:(pid 4644):Stop time: Wed Dec 10 11:34:34 2014

    Any thoughts?

    Len

  6. dmuegge says:

    Len,

    I do not have a SQL cluster setup in my lab so I have not tested the virtual server option. The only advice I can give here is to make sure UAC is completely disabled on the cluster nodes. I have seen issues running the commands via xp_cmdshell when it is enabled. I would try it with and without the -c option to see if it makes a difference. Also I would try some different combinations of using the actual node names in the -c option vs the virtual node name. I have found the documentation could be better around the command line options so a lot of trial and error is required in some situations.

    Regards,

    Dave

    • Piotr says:

      Len,

      I got exactly same issue on Windows cluster. Did you or anybody else solve the problem on clustered environment and will share the solution how to run DD Boost via xp_cmdshell?

      Regards,
      Piotr

  7. Vinayaka Holla says:

    Please let me know if you are able to name the backup files as you need. I need to backup the DDboost backup to tape for long term retention. Hence there is a need to identify the DB backup filename.

    Alternatively i would like to specify the backup filename during the backup.

  8. dmuegge says:

    @Vinayaka,

    Data Domain Boost does not name files as such. You can only label the save set in the DDBoost storage unit. DD Boost for SQL backups are not well suited to be moved to tape. In the scenario you describe the best way to do your long-term tape backup would be to use a periodic (weekly/monthly/quarterly) copy only backup from native SQL Server or from another traditional backup application to be written to tape. DD Boost would be used for your more frequent near-term needs to maximize accessibility and space savings.

    Regards,

    Dave

  9. PhineasGage says:

    Great article.

    To reduce the security vulnerability of xp_cmdshell, I included a step at the beginning of the job to enable xp_cmdshell and then one at the end to disable it again. While not perfect, it reduces the risk compared to enabling xp_cmdshell on the instance permanently.

  10. […] Server tools such as T-SQL, maintenance plans, and agent jobs. I have posted about this previously here but found some organizations still needed additional help getting started. This led me to create […]

  11. robdev says:

    Hi Dave & All, We are currently implementing Data Domains into our organisation and I seek some clarification to whether the DDBoost SQL Snap-in can support backing up multiple database instances in the one sql script similar to the traditional SQL maintenance plans e.g *.bak?

    Our techie consultant also suggested as a work around to create a CIFS share on the Data Domain appliances and configure the traditional SQL maintenance plans to reference this UNC?

    I would be really interested in getting some thoughts on this process?

    Thank-you Rob

    • dmuegge says:

      Rob,

      Sorry for the late reply, had a notification issue with the blog. There are two downsides to using traditional CIFS shares on a DD for SQL backups. One is that CIFS does not perform as well and all data id sent over the wire with no compression. Two, because of this SQL admins will tend to turn on SQL compression on the backups which then greatly reduces the de-duplication of data on the DD. This is the primary reason for using the DDBoost for SQL application. The DDBoost plugin can be used to backup multiple databases, but to do this effectively the use of some T-SQL is needed. I have created a toolkit to help get DBA’s started with this. See the following links.

      SQL DD-Boost Toolkit
      DD-Boost Toolkit Github Project

      Regards,

      Dave

  12. Surya Mani Sahoo says:

    What can be done to solve this error in a SQL Server Agent job –
    SQL Server Agent Job Code –
    “C:\Program Files\EMC DD Boost Modules\DDBMA\bin\ddbmsqlsv.exe” -c XXX-SQL-XX.service.local -l full -a “NSR_DFA_SI=TRUE” -a “NSR_DFA_SI_USE_DD=TRUE” -a “NSR_DFA_SI_DD_HOST=10.XXX.XXX.XXX” -a “NSR_DFA_SI_DD_USER=ddboost” -a “NSR_DFA_SI_DEVICE_PATH=/SQL01SU1” “MSSQL:master”
    ———————————-
    Backing up master… nsr/db_apps/bsmsql/sqlxbsacompforsave.cpp(742):

    Unable to detect the Filegroup or Filelist of database master. Backup cannot proceed for this database. 53084:(pid 8960):Processing master failed, the

    item will be skipped. ddbmsqlsv: MSSQL:master level=full, 0 KB 00:00:00, 1-file(s)-processed, 0-file(s)-succeeded43709:(pid 8960):Stop time: Wed Mar

    09 21:31:23 2016. Process Exit Code 2. The step failed.
    ————————————

    • dmuegge says:

      Surya,

      I have not seen this before and it appears the message is coming from the DDBoost application itself. I would recommend verifying the same behavior from the DOS command line as a first step and then if you receive the same error I would open a ticket with EMC support.

      Regards,

      Dave

    • Devesh says:

      Hi Mani,

      Please add your SQL Agent account as Local admin and restart sql agent. Your job will not fail.

  13. DanC says:

    Dave,
    We have just started working with DD Boost in our SQL environment and your scripts have been a tremendous help. I am running into one problem where I need your assistance. When I try to run the following I get an error.
    Use DMadmin
    Exec dbo.DDBMAExpire @AppType = ‘mssql’,
    @ConfigFile = ‘C:\ddconfig.cfg’

    Could not open the log file ‘C:\Program Files\EMC DD Boost Modules\DDBMA\logs\ddbmindexadmin.messages.log’.
    6211:ddbmexptool:The LNM logger handle is NULL.
    No expired images found

    I’ve created the ddconfig.cfg file as described in the EMD DD Boost Admin Guide but I can’t find the ddbmindexadmin.messages.log file anywhere on the server.

    Thanks for your help.

    • dmuegge says:

      Daniel,

      I has been a while since I worked on this, but I have been updating my lab for just that purpose. Can you tell me if you are using the 1.0 or 2.0 plugin?

      Thanks,

      Dave

      • DanC says:

        Dave,
        Thank you so much for your reply. We are using the 2.0 plug in. We were able to find the problem. It was a permissions issue on the Config folder. Once we gave the service account used for the SQL agent modify permission to the folder the script worked perfectly.
        I want to thank you again for your scripts and the support you are providing through your blog for the DD Boost product. We will be putting this into production in the very near future and could not have done it without your assistance.

  14. BradC says:

    So I wanted to concur with what you found regarding the RESTORE syntax, and the fact that you can’t find the “true” timestamp of a particular backup by looking at the MSDB backup history table. Here is some further insight:

    If you give it a time that doesn’t exactly match a backup, it will actually attempt to do a point-in-time restore, which for a database in full recovery mode, will involve rolling forward through tran logs.

    For a database in SIMPLE recovery mode, however, it will simply restore the last full (or, I presume, differential) backup prior to the datestamp you give it. That actually makes it pretty easy to get the restore you want, by picking a time you know is after the backup completed.

  15. Nico says:

    Hi David,

    I went through your SPs and noticed that all it does is clean up backups which are expired from the repository it has created.
    Are backups removed from data domain automatically based on their expiry date?

    Also, if I had to restore one of these backups from a brand new server, do I need to simply install ddboost for SSMS plugin and connect to my existing data domain to view existing backups there if for some reason I don’t have the repository anymore?

    Thanks,

    Nico

  16. Mark Glazerman says:

    David,

    Any advice on leveraging this toolkit without using xp_cmdshell ? Our organization is heavily regulated and xp_cmdshell is seen as a security risk and therefore not permitted.

    We have been working with our DBA team to test the latest version of the ddboost plugin and have seen considerable performance gains over previous versions. However, without an easy way to integrate the scheduling and administration of their backups using the ddboost software with their native tools, we will likely be left using a horribly inefficient CIFS solution.

    Thanks in advance,

    Mark

    • dmuegge says:

      Mark,

      One thing I would look at is Version 3.0 of the DDBoost plugin. EMC has been working on their own stored procedures, which are implemented as CLR procedures. This will not require the use of xp_cmdshell. The other method would be to use PowerShell to drive the command line applications from agent jobs. This method does require newer versions of SQL that offer the PowerShell task in the agent.

      Regards,

      Dave

      • Mark Glazerman says:

        Dave,

        Thanks for your response. Since I posted my question our local EMC sales team reached out to one of their global technology team members and after some testing, they seem to have confirmed that xp_cmdshell can be disabled and backups can still be scripted using native SQL tools. The performance improvements of the 3.0 plugin over previous versions is pretty impressive and if standard SQL scripting can be used without requiring xp_cmdshell, we may be onto something !!

        Thanks again,

        Mark

  17. Shirley says:

    Hi,
    I keep getting the raiserror message at the end if though it is successful. I can restore from the backup. It is definitely successful, however still prints the error regardless.

    Please advise.

  18. Wesley says:

    I am trying to automate weekly restores to a different server. I would like to have this automated as much as possible. The downside appears that if a new dbf is added to the active db, then I have to modify the script on the restore as well, due to moving the files to the appropriate location. Is there anyway around this?

    Thanks

  19. Vinesh says:

    Hello All,

    Will there be any dependent files which will not get deleted after expiry date ?

    I can see many files after retention as well.

  20. Aijaz says:

    With DDBEA 3.5 Table level restore is possible.

  21. M. Shoaib says:

    Hi,
    I have one issue, when I have to restore (or refresh) database thru DDBOOST, I select Data Domain Server, then select SQL Server Host for Source Database then when I click on a database I want to refresh/restore it takes very long times to populate all the backups, like 15-25 minutes for a small database which takes less than 3 minutes to restore after I select a backup.

    Can anyone please let me know the solution, thanks in advance.

    • Mark Glazerman says:

      After doing some troubleshooting with our DBA’s and EMC yesterday I would insure that forward and reverse name resolution is working on your host. We had some weird errors during restore operations and it turned out that we didn’t have reverse lookups working properly. This was resolved with addition of a host file entry on the SQL client. That may not be the source of your problem but it’s something to check. Querying DNS could slow things down while waiting for some kind of timeout. For your information we are using v3.5 of the DDBoost For Microsoft applications plugin and DDOS 5.4.1.

  22. Shoaib says:

    Hi David,

    Thanks for the post. However, I have little different issue.

    We use DDBoost GUI to backup all databases including system DBs.
    Now for DR scenario, I want to restore Master first thru DDBoost Gui or command line but it doesn’t work since the Instance is not up. (As some posts suggest to install a new instance then restore Master (replace), I don’t want to do it).
    So I want pull down backup of Master to local disk then use SQLCMD command to restore master. But I don’t find any command with ddbmsqlrc.exe to copy backup file to local disk.

    Any Help would be appreciated.

    Thanks in advance.

    Shoaib

    • dmuegge says:

      Shoaib,

      When a database is backed up with DDBoost a .bak file is not created like when backed up via SQL server native tools. DDBoost uses what is called a storage unit which is core to how Data Domain does data reduction. You will have to restore to an online SQL Server. To accomplish what you are attempting you would need a temporary intermediary server. I would consider if that is the right approach to accomplish your end goal.

      Regards,

      Dave