DPM is a great tool for protecting SQL Server databases for many reasons, but one in particular is that it can provide protection down to every 15 minutes. By backing up the transaction log every 15 minutes, a SQL database can be restored to virtually any point in time, which is extremely useful, and has saved me on a number of occasions.
One of the issues with protecting SQL Server in this way is that no other functionality that uses the logs can be used at the same time, for example log shipping. Although the DPM documentation states that it supports protecting databases that are configured for log shipping, what that means is that log shipping will use the logs and DPM will only provide VSS based backups (Express Full and Recovery Point). This in turn means that it is not possible to get 15 minutes backups, as a synchronization against the database transaction log cannot be performed.
So, it’s a choice, log ship every 15 minutes (or whatever interval you choose), or protect using DPM every 15 minutes. I most cases, it’s a simple choice to log ship and backup less regularly, as you have a copy of the transaction logs on another SQL Server as part of log shipping anyway…. But, what if you want both?
It is possible to achieve both log shipping and DPM protection of the transaction log, but it isn’t quite as efficient with the storage on the DPM server. To do it, leave the SQL Server configured to log ship with whatever configuration is required, then configure protection in DPM to perform only Express Full/Recovery Point backups of the database, and this provides everything except a DPM backup of the transaction logs. To get the transaction log backups as well, a new protection group will need creating, and it will need to be configured to protect the file location of the transaction logs on the primary log ship server. By doing this, DPM will have a backup of the transaction logs, but log shipping will not be affected, so the database is very well protected.
I mentioned that this isn’t quite as efficient as backing up the logs as part of the database protection, and this is because you are now performing file backups of the logs instead of them being protected as part of the application. Every time the file location is protected, the files are backed up at whatever state they are at that time, so if a log is still being written to, it will still be backed up! This won’t cause any corruption issues due to the way DPM performs backups, however it may mean that some of the backups contains transaction logs that aren’t complete, however depending on your log ship and backup schedules, it is likely that a second backup of the file could occur as well!
Overall, this solution requires a bit more administration to get up and running, but my tests have proved it works very well, and I would recommend it if you have this ultra-protective requirement.
In summary, these are the steps that need to be performed to protect s SQL database, and it’s logs, when using log shipping:
- Configure log shipping as required.
- Create a protection group in DPM to provide Express Full/Recovery Point backups of the database.
- Create a second protection group to protect the folder/share where the transaction logs are stored.
With that all done, you’re good to go!