Call Before You Dig—Scenario #4 with Log Shipping
As you saw in Chapter 3, “Choosing High Availability,” the call before you dig business scenario (scenario #4) combined hardware redundancy, shared disk RAID arrays, MSCS, SQL clustering, and log shipping. This is a very critical system during its planned hours of operation, but has low performance goals, and low cost of downtime (when it is down). Regardless, it is highly desirable for this system to be up and running as much as possible. Having that warm standby with log shipping provides just enough of an insurance policy in case the whole SQL cluster configuration fails. After building this HA solution, the uptime goal was achieved easily. In fact, after three months, the log shipping configuration was disabled. Two days after the log shipping was disabled, the whole SQL cluster configuration failed (Murphy's law). The log shipping was rebuilt and this configuration has remained in place since then. Performance has been exceptional and this application continuously achieves its availability goals! Figure 6.12 shows the current HA solution with log shipping. The availability that this application requires is
15 hours per day (5:00 a.m.–8:00 p.m.)
6 days per week (closed on Sunday)
312 days per year.
Figure 6.12. Call before you dig high availability “live solution” with log shipping.
Planned Downtime: 0%
Unplanned Downtime: .5% (less than 1%) will be tolerable
The ROI for the call before you dig scenario is calculated as follows:
We had previously estimated the total incremental costs to be in the range of between $10K and $100K, which included
Three new four-way servers (with 4GB RAM, local SCSI disk system RAID 10, two ethernet NICs, additional SCSI controller [for shared disk]) at $25K per server
Two MS Windows 2000 Advanced Server licenses ~ $3K per server (Windows 2003 Enterprise Edition $4K per server)
One Shared SCSI disk system with RAID 5 (minimum of 10 drives per SCSI disk system) ~ $5K
Four–five days of additional training costs for system admin personnel ~ $12K
Two new SQL Server licenses (SQL Server 2000, Enterprise Edition) at $5K per server
Total incremental costs to upgrade to this SQL clustering with log shipping high availability solution is approximately $108,000. Just slightly over the earlier estimates.
Now, let's work through the complete ROI calculation with these incremental costs along with the cost of downtime:
Maintenance cost (for a one-year period):
$12K (estimate)—yearly system admin personnel cost (additional time for training of these personnel)
$16K (estimate)—recurring software licensing cost (of additional HA components; (2) OS + (2) SQL Server 2000)
Hardware cost:
$80K hardware cost (of additional HW in the new HA solution)
Downtime cost (for a one-year period):
If you kept track of last year's downtime record, use this number; otherwise produce an estimate of planned and unplanned downtime for this calculation. We estimated the cost of downtime/hour to be $2K/Hour.
Planned downtime cost (revenue loss cost) = planned downtime hours × cost of hourly downtime to the company. Should be $0.
Unplanned downtime cost (revenue loss cost) = unplanned downtime hours × cost of hourly downtime to the company:
.5% (estimate of unplanned downtime percentage in one year) × 8760 hours in a year = 43.8 hours of unplanned downtime
43.8 hours × $2K/hr (hourly cost of downtime) = $87,600/year cost of unplanned downtime.
ROI totals:
Total costs to get on this HA solution = $128,000 (for the year—slightly higher than the immediate incremental costs stated above)
Total of downtime cost = $87,600 (for the year)
The incremental cost is about 123% of the downtime cost for one year. In other words, the investment of the HA solution will pay for itself in one year and three months! This is well within the ROI payback they were looking for. And provided a solid HA solution for years to come.