For most of us IT workers, a purchase decision is a multi-step process that is well removed from our daily operations. With such limited time and opportunities to push for a much-needed tool, it pays to have performed due diligence and to have your research close at hand. In this article, I will provide some insights from a DBA who has been in the trenches on how to prepare the ground for a software purchase using a third-party backup tool as a case study. It will cover:
- The DBA Perspective: how will it benefit the server environment?
- The Business Perspective: how much time and money will it save the company?
- Presenting Your Case: how to give yourself the best chance of success.
Identify the Need from a DBA Perspective
The starting point for a proposal to purchase a backup tool is generally a DBA finding that the answer to one or several of the following questions is “yes”:
- Are the backups taking too long each night?
- Are they preventing tape backups?
- Am I constantly running out of space on servers because of backups?
- Am I spending my day making space decisions like which files to delete or move to make room?
- Are backups failing?
- Has an attempted restoration failed recently?
- Have I encountered recent intrusions or hacking attempts on the company data?
- Do I often provide application vendors with copies of production database backups?
- Do I have the time and resources to develop this myself?
Boiling all this down, the bottom line justification for a third-party backup tool, from a DBA perspective, resolves to three fundamental issues:
- Space – disk, tape backups
- Time – both CPU time and the DBA’s time
- Security – is there a need to encrypt backups?
Let’s consider each of them in term, focusing exclusively for the time being on what these issues mean from the DBA perspective.
Besides performance tuning, managing disk space is probably the single most important task for any SQL Server DBA. Some DBAs spend the majority of their time juggling space on their commodity-based servers in order to accommodate ever-expanding backup files. The constant shortage of disk space can be frustrating, especially when storage is so inexpensive. In addition to disk space, you also need to consider the space consumed by backup tapes. This important cost is often completely ignored by DBAs especially in larger shops, as it is usually borne by another support team.
Backing up a server can eat up a lot of CPU cycles and a lot of a DBA’s time. When estimating the time involved in creating backups, not only should you take into account how long backups are taking, but also the time that you as a DBA have to spend copying, moving, or rearranging files to ensure that backups are completed, and that there is enough space for the next backup cycle.
This is time that most DBAs would dearly like to invest in other ways, for example in design and research.
Security is an important part of every well-planned backup strategy. Do you need to encrypt the backups due to security concerns? Not necessarily, as most of us are in a relatively secure server environment behind firewalls and other defenses. Ultimately, this may be a business decision, but if you routinely copy production database backups to an FTP site, or need to send copies of your backups to the application vendor for improvements or troubleshooting, then you should consider backup encryption. Regulatory concerns may also require that you encrypt backups.
Many DBAs consider security to be less of a DBA problem and more of a server operations issue, but when your databases have been intercepted and confidential information has been compromised, the finger of blame will point to both sides of the aisle, and there may be more than one resume that needs to be updated.
Evaluating Backup Strategies: Native versus Third Party
So how do you set about estimating the current “cost” of backing up your databases and the benefit you will gain, in terms of the time it costs you and the disk space and CPU time it costs your servers, by purchasing a third party tool?
The real time it takes a DBA to prepare, monitor and perform backups is hard to measure, but you do need to make at least an estimate of this cost, both pre- and post-tool. For disk space and CPU time on a server, hard data is available from the msdb database. The following script provides the size in megabytes of the backup file as well as time spent by the server to make the backup happen:
a.server_name as 'Server',
a.database_name as 'Database',
CONVERT(varchar(25), a.backup_start_date,100) AS 'Start Date',
CONVERT(varchar(25), a.backup_finish_date,100) AS 'Finish Date',
DATENAME(weekday, a.backup_finish_date) AS 'Day' ,
DATEDIFF(millisecond, a.backup_start_date, a.backup_finish_date) AS 'MSec' ,
DATEDIFF(second, a.backup_start_date, a.backup_finish_date) AS 'Sec' ,
DATEDIFF(minute, a.backup_start_date, a.backup_finish_date) AS 'Mins' ,
CAST(CAST(DATEDIFF(minute, a.backup_start_date, a.backup_finish_date) AS DECIMAL(8,3))/60 AS DECIMAL(8,1)) AS 'Hours',
WHEN DATEDIFF(minute, a.backup_start_date, a.backup_finish_date) > 0
THEN CAST(CEILING(a.backup_size /1048576) / DATEDIFF(minute, a.backup_start_date, a.backup_finish_date) as DECIMAL(8,1))
END AS 'Meg/Min',
CEILING(a.backup_size /1048576) AS 'Size Meg',
CAST((a.backup_size /1073741824) AS DECIMAL(9,2)) AS 'Gig',
a.user_name,a.backup_size AS 'Raw Size'
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupset b
ON a.server_name = b.server_name AND a.database_name = b.database_name
WHERE a.type = 'D' AND b.type = 'D'
-- AND a.backup_start_date > '2005-01-01' -- Optional data range
-- AND a.database_name = 'DatabaseName here' -- Optional select individual database
GROUP BY a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
ORDER BY a.server_name, a.database_name, a.backup_start_date DESC
You can run this script with native backup and then again with a tool to get a reasonably accurate measure of the likely savings that the tool offers in terms of disk space and backup time (CPU).
Your choice of tool will
come down to a balance
between the expense of
the software, the
estimated time and disk
space savings that it
will provide, and the
value to you of any
additional features that
it has to offer
All of the major third-party products provide compression as part of their backup solution which addresses the space, and to a large extent the time factor. Depending on the vendor, encryption is also included. Your choice of tool will come down to a balance between the expense of the software, the estimated time and disk space savings that it will provide, and the value to you of any additional features that it has to offer, such as monitoring or scheduling features. DBAs need to minimize space and optimize cost as much as possible; with the correct tool this can be accomplished.
Microsoft has sought to address disk space issues by introducing backup compression to SQL Server 2008 Enterprise Edition, so this is another option to consider if you are lucky enough to be using this edition. However, unless your company is an early adopter, chances are that your shop still has the majority of your SQL servers running SQL 2005 and even SQL 2000. Recent license-pricing decisions have made SQL 2008 a much more expensive option than adding a third-party tool. Unless your company maintained the software assurance program with Microsoft (which provides upgrades without your company having to pay for additional licensing), upgrading to SQL 2008 Enterprise may not be viable.
Translation Time: Identify the Benefit from the Business Perspective
Having done the above due diligence, you will probably feel that your case for buying the tool is more or less unanswerable. However, it is at this point you need to take a step back and put yourself in the shoes of the business manager who must make the purchasing request.
Your valid arguments for wanting the tool – reduced load on your servers, less time spent moving files around, and so on – are not necessarily the ones that are going to convince your manager that they should allocate any budget to additional software. Managers always want to be seen to be doing “more with less”, and they will be mostly looking at the bottom line cost of the software. It is your job to prove that, by introducing efficiencies and improving reliability, the third-party backup software will indeed allow you to do more with less.
You need to translate the results of your investigations into direct cost benefits to the company. Extra disk space and tape space that is required due to a poorly designed backup strategy is a direct monetary cost to the company. Any of the time you “lose” due to a suboptimal backup strategy is an opportunity cost to the company. In other words, it is time that could be better utilized to help the company make money.
You need to present the benefits of the proposed tool purchase directly in these terms. In other words:
Savings = Cost of Space Returned + Cost of Time Saved + Cost Saved by Security.
That last item (Cost Saved by Security) is pretty nebulous, but encryption can keep intrusion or casual snooping to a minimum, and you can stress this when presenting.
Space and time savings can be estimated based on whitepapers provided by the vendor of the tool or, better yet, by downloading an evaluation copy of the tool and performing some tests using your own data, as described above. When money is being discussed, I have invariably found that factual data, based on your specific IT environment is much better received by management than whitepaper averages.
Having performed your tests and collected the data, you can translate the space savings into direct cost savings for the company using the following formula:
Space Cost Savings = Saved Cost for Disk + Saved Cost Tape Backup
Saved Cost for Disk = (Original Space used – Compressed Backup Space) * $y
Saved Cost Tape Backup = (Original Space used – Compressed Backup Space) * $z
where $y = cost per gigabyte of disk space
$z = cost per gigabyte of tape used
Notice that backup tape usage is firmly part of the equation. Disk space has its hard cost savings, but we will also be reducing the space required for backup tapes. Since most of the third-party SQL backup tools can compress in the order of 90%, this can turn into substantial cost savings if you have a couple of dozen servers or a large SAN environment.
You can then do the same thing for the time savings, factoring in both the reduced server time required for backups and the reduced DBA labor. When calculating saved server time do not forget to include:
- Time saved due to faster backups
- Time saved due to faster restores
- Time saved being able to restore specific objects within the database instead of the entire database (assuming your tool offers this feature)
The total time cost savings to the business can then be calculated as follows:
Time Cost Savings = (Database Availability * Business Cost Per Hour)
+ (Time Saved by DBA * DBA Pay Rate)
‘Database Availability’ is the difference between the time it takes to complete backups using native mode versus a third-party tool, taking into account all of the potential savings listed above. The ‘Business Cost Per Hour’ is an arbitrary number, determined by your company and reflecting the cost per hour impact for system availability.
When estimating a value for ‘Time Saved by DBA’, bear in mind that most of the third-party tools offer additional benefits, such as a central repository for management, monitoring and reporting, which can cut down the time required for these administrative tasks.
I can tell you right now
that it’s highly unlikely
you will be given
approval to purchase
the tool if the calculated
savings are less than
the cost of the tool.
The last number you need to have at your disposal is the overall cost of the SQL backup tool that you are considering as part of your backup strategy. I can tell you right now that it’s highly unlikely you will be given approval to purchase the tool if the calculated savings are less than the cost of the tool. The exception to this is if you have such a significant security risk that buying a tool that offers encryption, helps resolve the problem.
The bottom-line that we need to present to management is the Return on Investment, or ROI, calculated as follows:
ROI = Space Cost Saving + Time Cost Savings – Cost of Compression Tool
In all of these calculations keep an eye out for “hidden” costs and be sure to factor them in. Common examples include, “non-routine” backups, such as additional weekly/monthly/quarterly backups, or the longer-term costs associated with off-site backup tapes that are retained for extended periods for recovery.
Preparing the Presentation
Once your data is gathered, it’s time to put it all into presentation format and have it ready, as the unexpected usually happens. In other words what started out as an evaluation for a third-party backup solution can quickly turn into an immediate business need.
The next thing to work on is your delivery. As the old adage goes ‘practice makes perfect’. This sounds trivial, but it is very important that you make a smooth presentation to management. Remember that, while you are trying to sell management on the benefits of the tool, you are also presenting yourself and your team. The more polished the presentation, the more your professionalism shines through and the more management will realize that you have done your homework.
Often, when presenting to management LESS IS MORE! You need to have all of your research, calculations and findings prepared and ready to present, but do not simply throw it all in to a 30-slide PowerPoint presentation. Instead, start with just a couple of slides that show the key indicators and the bottom-line cost savings for the business.
In most cases, this will lead to additional questions from management as to how these savings were determined and that is where the “hidden” information comes into play. Many of these slides may never be looked at during the presentation, but having them ready is still a good idea.
One of your slides should list, concisely, all of the main technical reasons for, and benefits gained from, buying the software. Based on my experience as a DBA, here are my Top Ten reasons for buying a third-party backup tool:
- Backups are failing due to space shortages; third-party software creates smaller backup files, and most products provide a range of compression levels for you to experiment with (each level affects space and time of execution).
- Backups are taking too long to complete; third-party software reduces the time taken to complete.
- DBA is spending too much time on space issues; third-party software creates smaller backup files, leaving more free disk space.
- Competition for SAN storage from Exchange, ProjectServer, etc.; third-party software usually creates smaller backups with a shorter execution time, reducing bandwidth and duration of SAN storage traffic.
- There is often a need to copy large databases over the network; third-party software means less network traffic due to smaller backup files during the copy stage. Some products also provide backup mirroring (reducing DBA manual copy).
- Backups copied over the network are subject to snooping and therefore require encryption; most third-party software provides multiple encryption methods.
- Management requires metrics that the tool can provide to continue to justify costs; most third-party software provides a central repository, monitoring and reporting.
- Lack of time and/or resources to develop a robust backup strategy; most third-party software provides additional features along with the base backup processes. A few examples are monitoring, reporting, and metrics information from a centralized or individual system perspective. You will quickly find these little features added by the vendors to be invaluable for yourself and management.
- Restoring a full backup takes too long to meet service-level agreements; third-party software reduces restore times through either quicker full restores or object level restores.
- Often you only need to refresh one or two objects within a database; third-party software may provide object level recovery to assist you in this process.
In my 15 years as a DBA I have encountered space consumption issues more than a few times. In my experience, purchase of a third-party backup tool has helped to solve these issues.
As Microsoft only added compression into the Enterprise Edition of SQL Server 2008, the vendor-provided compression solutions for SQL 2000 and 2005 are more feasible from a cost perspective for many shops.
All of the third-party backup tools on the market carry advantages and disadvantages. Our role as DBAs is to determine what’s best for our environment. You need to carefully evaluate each tool, gather all of the necessary data, and then present it in terms of the cost benefit to the business.
In order to help you pull together and present all of this information, a sample “Purchase Proposal” document is available to download from the Red Gate SQL Backup Evaluation Center, where you can also access a trial download of their SQL Backup tool.
I hope that this article, while by no means a comprehensive tutorial, does at least provide you with some good ideas and a few take-aways on how to approach the software purchasing process.