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.\u00a0 It will cover:<\/p>\n
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”:<\/p>\n
Boiling all this down, the bottom line justification for a third-party backup tool, from a DBA perspective, resolves to three fundamental issues:<\/p>\n
Let’s consider each of them in term, focusing exclusively for the time being on what these issues mean from the DBA perspective.<\/p>\n
Besides performance tuning, managing disk space is probably the single most important task for any SQL Server DBA.\u00a0 Some DBAs spend the majority of their time juggling space on their commodity-based servers in order to accommodate ever-expanding backup files.\u00a0 The constant shortage of disk space can be frustrating, especially when storage is so inexpensive.\u00a0 In addition to disk space, you also need to consider the space consumed by backup tapes.\u00a0 This important cost is often completely ignored by DBAs especially in larger shops, as it is usually borne by another support team.<\/p>\n
Backing up a server can eat up a lot of CPU cycles and a lot of a DBA’s time.\u00a0 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.<\/p>\n
This is time that most DBAs would dearly like to invest in other ways, for example in design and research.<\/p>\n
Security is an important part of every well-planned backup strategy.\u00a0 Do you need to encrypt the backups due to security concerns?\u00a0 Not necessarily, as most of us are in a relatively secure server environment behind firewalls and other defenses.\u00a0 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.\u00a0 Regulatory concerns may also require that you encrypt backups.<\/p>\n
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.<\/p>\n
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?<\/p>\n
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:<\/p>\n
SELECT\r\n\u00a0a.server_name as 'Server',\r\n\u00a0a.database_name as 'Database',\r\n\u00a0CONVERT(varchar(25), a.backup_start_date,100) AS 'Start Date',\r\n\u00a0CONVERT(varchar(25), a.backup_finish_date,100) AS 'Finish Date', \r\n\u00a0DATENAME(weekday, a.backup_finish_date) AS 'Day' ,\r\n\u00a0DATEDIFF(millisecond, a.backup_start_date, a.backup_finish_date) AS 'MSec' ,\r\n\u00a0DATEDIFF(second, a.backup_start_date, a.backup_finish_date) AS 'Sec' ,\r\n\u00a0DATEDIFF(minute, a.backup_start_date, a.backup_finish_date) AS 'Mins' ,\r\n\u00a0CAST(CAST(DATEDIFF(minute, a.backup_start_date, a.backup_finish_date) AS DECIMAL(8,3))\/60 AS DECIMAL(8,1)) AS 'Hours',\r\n\u00a0CASE\r\n\u00a0\u00a0 WHEN DATEDIFF(minute, a.backup_start_date, a.backup_finish_date) > 0\r\n\u00a0\u00a0 THEN CAST(CEILING(a.backup_size \/1048576) \/ DATEDIFF(minute, a.backup_start_date, a.backup_finish_date) as DECIMAL(8,1))\r\n\u00a0ELSE 0 \r\n\u00a0END AS 'Meg\/Min',\r\n\u00a0CEILING(a.backup_size \/1048576) AS 'Size Meg',\r\n\u00a0CAST((a.backup_size \/1073741824) AS DECIMAL(9,2)) AS 'Gig',\r\n\u00a0a.user_name,a.backup_size AS 'Raw Size'\r\nFROM msdb.dbo.backupset a\r\nJOIN msdb.dbo.backupset b \r\n\u00a0 ON a.server_name = b.server_name AND a.database_name = b.database_name \r\nWHERE a.type = 'D' AND b.type = 'D'\r\n\u00a0-- AND a.backup_start_date > '2005-01-01'\u00a0\u00a0\u00a0\u00a0 -- Optional data range\r\n\u00a0-- AND a.database_name = 'DatabaseName here'\u00a0 -- Optional select individual database\r\nGROUP BY a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name\r\nORDER BY a.server_name, a.database_name, a.backup_start_date DESC\r\n<\/pre>\nYou 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).<\/p>\n
“
\nYour choice of tool will
\ncome down to a balance
\nbetween the expense of
\nthe software, the
\nestimated time and disk
\nspace savings that it
\nwill provide, and the
\nvalue to you of any
\nadditional features that
\nit has to offer
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 “<\/p>\nAll 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.\u00a0 Depending on the vendor, encryption is also included.\u00a0 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.\u00a0 DBAs need to minimize space and optimize cost\u00a0as much as possible; with the correct tool this can be accomplished.<\/p>\n
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.\u00a0 Recent license-pricing decisions have made SQL 2008 a much more expensive option than adding a third-party tool.\u00a0 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.<\/p>\n
Translation Time: Identify the Benefit from the Business Perspective<\/h2>\n
Having done the above due diligence, you will probably feel that your case for buying the tool is more or less unanswerable.\u00a0 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.<\/p>\n
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.\u00a0 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.<\/p>\n
You need to translate the results of your investigations into direct cost benefits to the company.\u00a0 Extra disk space and tape space that is required due to a poorly designed backup strategy is a direct monetary cost to the company.\u00a0 Any of the time you “lose” due to a suboptimal backup strategy is an opportunity cost to the company.\u00a0 In other words, it is time that could be better utilized to help the company make money.<\/p>\n
You need to present the benefits of the proposed tool purchase directly in these terms.\u00a0 In other words:<\/p>\n
Savings = Cost of Space Returned + Cost of Time Saved + Cost Saved by Security.\u00a0 <\/i><\/p>\n
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.<\/p>\n
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.\u00a0 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.<\/p>\n
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:<\/p>\n
Space Cost Savings = Saved Cost for Disk + Saved Cost Tape Backup
\nwhere
\n\u00a0\u00a0\u00a0 Saved Cost for Disk = (Original Space used – Compressed Backup Space) * $y
\n\u00a0\u00a0\u00a0 Saved Cost Tape Backup = (Original Space used – Compressed Backup Space) * $z<\/p>\nwhere\u00a0 $y = cost per gigabyte of disk space
\n$z = cost per gigabyte of tape used\u00a0 <\/i><\/p>\nNotice that backup tape usage is firmly part of the equation.\u00a0 Disk space has its hard cost savings, but we will also be reducing the space required for backup tapes.\u00a0 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.<\/p>\n
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.\u00a0 When calculating saved server time do not forget to include:<\/p>\n
The total time cost savings to the business can then be calculated as follows:<\/p>\n
Time Cost Savings = (Database Availability * Business Cost Per Hour)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0+ (Time Saved by DBA * DBA Pay Rate)<\/i><\/p>\n
‘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.<\/p>\n
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.<\/p>\n
“
\nI can tell you right now
\nthat it’s highly unlikely
\nyou will be given
\napproval to purchase
\nthe tool if the calculated
\nsavings are less than
\nthe cost of the tool.
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 “<\/p>\n
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.\u00a0 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.\u00a0 The exception to this is if you have such a significant security risk that buying a tool that offers encryption, helps resolve the problem.<\/p>\n
The bottom-line that we need to present to management is the Return on Investment, or ROI, calculated as follows:<\/p>\n
ROI = Space Cost Saving + Time Cost Savings – Cost of Compression Tool<\/i><\/p>\n
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.<\/p>\n
Once your data is gathered, it’s time to put it all into presentation format and have it ready, as the unexpected usually happens.\u00a0 In other words what started out as an evaluation for a third-party backup solution can quickly turn into an immediate business need.<\/p>\n
The next thing to work on is your delivery.\u00a0 As the old adage goes ‘practice makes perfect’.\u00a0 This sounds trivial, but it is very important that you make a smooth presentation to management.\u00a0 Remember that, while you are trying to sell management on the benefits of the tool, you are also presenting yourself and your team.\u00a0 The more polished the presentation, the more your professionalism shines through and the more management will realize that you have done your homework.<\/p>\n
Often, when presenting to management LESS IS MORE!\u00a0 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.\u00a0 Instead, start with just a couple of slides that show the key indicators and the bottom-line cost savings for the business.<\/p>\n
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.\u00a0 Many of these slides may never be looked at during the presentation, but having them ready is still a good idea.<\/p>\n
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:<\/p>\n
In my 15 years as a DBA I have encountered space consumption issues more than a few times.\u00a0 In my experience, purchase of a third-party backup tool has helped to solve these issues.<\/p>\n
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.\u00a0<\/p>\n
All of the third-party backup tools on the market carry advantages and disadvantages.\u00a0 Our role as DBAs is to determine what’s best for our environment.\u00a0 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.<\/p>\n