Access has over 350 SQL Server databases (14 Instances) along with a few Oracle data warehouse instances installed on 14 database servers. Signifi-cant database issues and challenges existed when the CommitDBA team was brought in. These conditions were causing serious business activity processing delays and stoppages. Listed below were the major problems, issues, requirements and challenges:
1. A mixture of SQL Server 2008, 2005 and 2000 installations. 2. Legacy SQL Server versions were unsupported and unstable.
3. Nightly batch jobs were failing regularly including database procedures. 4. Backup and replication process were failing regularly.
5. Only one staff in-house Junior DBA available.
6. A requirement to upgrade to more powerful database servers and new disk storage technology.
7. A requirement for Virtualizing the new database servers. 8. A requirement to upgrade to SQL Server 2008 R2.
9. A requirement for upgraded proactive real-time database monitoring. The above challenges and unfulfilled requirements dramatically impacted Access business and services. For instance, when the nightly batch pro-cessing was late due to failures, this caused many key IT resources to be up and available each night attempting to remediate the issues. Business pro-cesses were not completing and IT resources were not at full capacity the following day. Without implementing actionable solutions for the database requirements and overcoming the problematic issues, Access would contin-ue to face increasing business execution and performance constraints. In addition, IT resources were putting excessive time into troubleshooting, im-plementing workarounds and attempting to fix problematic issues which were continuing to escalate. This downward cycle caused delays in strategic IT projects that were critical to the company and caused burnout among Ac-cess key staff.
CommitDBA is a specialized Database Managed Service Provider (DMSP) for enterprise database administration and engineering services.
CommitDBA provides 24x7 proactive database monitoring with best-in-class tool Foglight. Access Insurance Company (Access), located in Atlanta, GA, contacted CommitDBA in January 2013 to begin exploring remote database service offerings aimed at addressing serious gaps in database management bandwidth and expertise along with remediating their critical problems. CommitDBA was chosen over other specialized remote DBA firms to meet the immediate challenges within their large database domain.
C L I E N T
CommitDBA was chosen as the Solutions Provider of remote DBA services due to compelling reasons, many which are listed below:
1. CommitDBA senior level experience and the understanding of technolo-gy stacks in SQL Server and Oracle enterprise databases.
2. The CommitDBA partnership with Dell to utilize the best-in-class proac-tive database monitoring tool “Foglight” as part of the services offering. 3. The provision of database services industry best practices.
4. The company’s long history of database services and positive refer-ences about those services.
5. Dedicated primary and secondary DBAs assigned specifically to Access Insurance databases on a 24x7 schedule.
6. A fixed cost service model that covers over 200 In-Scope database tasks.
DBAManage remote services now provide sole support for the entire SQL database domain with a strong Service Level Agreement (SLA) in place for quick response time and remediation.
Access was assigned a Primary and Secondary senior DBA. The CommitDBA team came on site in June 2013 for a short period of time to initiate a discovery, analysis, database assessment and work with the Ac-cess IT stakeholders. This effort resulted in a significant understanding of the complex database architecture, application business processes, and extreme problematic conditions along with providing recommendations for improvements. The CommitDBA team collaborated with the Access staff for an action and triage plan to remediate the highest priority issues as quickly as possible. The most critical issues were:
1. Nightly batch jobs were failing regularly including database procedures. 2. SQL Server backup and replication process were failing regularly. Remediation solutions came through digging deep into the Microsoft/SQL Server settings and SQL Backup tool limitations and creating workarounds based on SQL version limitations. Once the major issues were analyzed, the DBAs worked on and remediated the nightly jobs running against various versions of SQL Server. These jobs were stabilized with a reduction of fail-ure decreased by over 80%. Most of the remaining failfail-ures were due to ad-ditional changes or modifications in processing that were introduced. Access engaged the CommitDBA team to then work on large projects that included a difficult transition to move the existing 350+ databases to upgrad-ed hardware and servers while at the same time Virtualizing the servers along with transitioning to a new co-location data center. These projects in-troduced greater risk for successful completion due to factors which includ-ed: (i) the complexity and dependencies of applications, databases, data-base procedures; (ii) problematic issues with SQL 2000 which is unsupport-ed by Microsoft; and (iii) moving to a new co-lo and migrating to new servers at the same time. Even though the migration would be performed in stages, this was a significant challenge for both the Access and CommitDBA teams.
S O L U T I O N
CommitDBA was chosen over other specialized DBA Service Providers due to their SQL and Oracle expertise, Foglight database monitoring tool, and industry best practice model. The DBAManage model of providing expert ser-vices for database projects along with 24x7 proactive support was able to meet all Access Insurance mission-critical require-ments and demands.
D E L I V E R Y
The CommitDBA team developed a plan of attack and worked diligently with Access staff on several weekends overcoming all technical obstacles and working straight through without sleep to accomplish the migration goals. The end result was a successful database migration limiting database down-time to a minimum as required for Access business process continuity.
“Prior to partnering with CommitDBA to proactively manage and monitor our production SQL Server and Oracle database systems, we found a gap in the skills and availability of our onsite database resources. This limitation prevented us from effectively moving forward with upgrades to our environ-ment, subsequently affecting our scalability. CommitDBA was able to pro-vide effective resources to quickly engage and assess our systems; helping us migrate recurring issues, identify query improvements, and stabilize our environment.”
— Mike Jones, Program Manager, Access Insurance Company
The CommitDBA team also helped identify major database performance issues. In one case, as part of the Foglight proactive database monitoring, we noticed and were alerted of excessive CPU usage and disk activity. The most critical SQL Server instance and server were being pegged by these conditions constantly. After reviewing the processes and detailed database activity, it was determined that an application was invoking a problematic SQL function continually. This activity became a prime candidate for root cause of the heavy database and CPU usage. However, for testing purpos-es, it was difficult to isolate the offending SQL query due to its tight integra-tion and deep embedding in the applicaintegra-tion. This consisted of many coalesc-ing dependencies but after investigatcoalesc-ing the procedure it was discovered that the application query was performing a full table scan repeatedly during business hours. A query against a high volume table that runs continually during business hours while performing full table scans is one of the worst scenarios for massive contention, excessive database load, high server re-source usage and overall performance drain. A full table scan means read-ing every row in the table instead of utilizread-ing appropriate indexes. All other applications running on the same server are affected negatively by these conditions even though they may not be accessing the same database or instance. The primary DBA assigned to Access pinpointed the offending functions and queries and worked with an Access developer to remediate the application flaws and implement a primary key index to eliminate the full table scans. This improved performance over 90% for this critical application function.
Another key DBAManage service delivery was the implementation of base administration best practices. The goal was to move the Access data-base environment to a fully optimized state in a roadmap to maturity. The introduction of DBA best practices and repeatability brings one of the key components in the roadmap to database environment maturity. One of the initial best practice implemented at Access was to solidify the SQL Server backups in a consistent manner and make sure each SQL Instance was be-ing backed up properly. Part of this included updatbe-ing maintenance plans to check for integrity (DBCC). This is the most critical “best practice” implemen-tation since reliable backups are absolutely essential in eliminating data loss and risk. In addition, CommitDBA rebuilt indexes on all databases due to heavy fragmentation. This accounted to around 80% of the total indexes established across the board in Access SQL databases. This performance “best practice” now keeps indexes contiguous and optimized.
D E L I V E R Y
The CommitDBA team was able to reduce application and data-base bottlenecks by diagnosing SQL queries and transactions im-proving some response times by over 90%. After getting the Access database environment and nightly processing into a stabilized condi-tion, the CommitDBA team implemented best practice policies along with a roadmap to maturity.
D B A B E S T P R A C T I C E S
Monitoring and Management
The DBAManage Service proactively monitors the Access Insurance SQL Server databases 24x7 using a best-in-class tool “Foglight” from Dell Soft-ware (formerly a Quest SoftSoft-ware product). The Foglight dashboard (Image 1) displays real-time and automated alerts and rules while initiating data col-lection and root cause analysis. This provides the CommitDBA team with one of the most powerful database technology tools available today.
Foglight monitoring comes bundled with the DBAManage service and saves organizations like Access thousands of dollars in license fees each year as opposed to purchasing the licenses themselves. In addition, Access IT staff can view the dashboard in real-time to see what type of database activity is occurring. Access is also able to review Foglight metrics via weekly reports sent to Access key staff along with a month-end summary report created by the CommitDBA team.
M O N I T O R I N G DBAManage installed the best-in-class proactive monitoring tool “Foglight” at Access Insurance. Foglight monitors real-time 24x7 with four distinct alert levels. This provides Access with the comfort of knowing DBAManage services have continu-al oversight in their large SQL database environment and that the CommitDBA team can respond quickly based on the severity level of alerts that were triggered.
A L E R T S
Foglight alerts have four categories based upon the severity level. CommitDBA configures the appropriate alerts based on each SQL Server environment and adjusts as necessary so that each database domain is fine tuned. The alerts are commu-nicated in real-time to the Primary and Sec-ondary DBA with re-sponse times dictated by the Service Level Agreement (SLA).
Foglight was configured by CommitDBA with a primary set of critical severi-ty rules and alerts before the start of monitoring. The critical and fatal level parameter thresholds were established as key for the DBA team to maintain stability, high availability, performance, security and recoverability for the SQL Server databases. Non-critical alert parameters and rules were also setup for lower level alerts. In addition, database metrics and topology ob-ject properties were setup for monitoring. After a short period of time, the CommitDBA team was able to fine-tune the alerts and rules for optimal noti-fications and metric gathering based on the SQL Server database configu-ration at Access.
Access SQL Server database downtime has been kept to a minimum since the DBAManage engagement started. The Foglight monitoring, which has been tweaked and fine-tuned over the last year, continues to proactively alert the CommitDBA team on problematic database conditions to be reme-diated with minimum Time-to-Resolution (TTR). Ongoing updates to Foglight throughout the year provide enhanced features and additional alerts.
A Roadmap to Maturity
CommitDBA continues to work with Access on a roadmap to maturity (see image below). Upgrades to legacy versions of SQL to SQL Server 2008 R2 are ongoing currently along with further improvements in performance, data replication and backups. Future plans include migrating all SQL 2008 to 2012. At CommitDBA, we have developed a database maturity model simi-lar to the ITIL maturity model. However instead of processes as in the ITIL model, the maturity ranking is based on database component optimization levels (Image 2). When the CommitDBA team first engaged with Access, they were at a maturity between Level 1 and Level 2. Currently Access is at maturity Level 4 but moving toward Level 5 in 2014 as the CommitDBA team continues to work with the Access team on their initiatives with hardware and database upgrades. This will accelerate Access business operations and processes to high end performance and execution.
M A T U R I T Y
CommitDBA believes in maturity models whether they be pro-cesses, services, or database frameworks. With a maturity model a roadmap is created with milestones to reach each level. The Access database envi-ronment is close to reaching the Maturity Level 5—Fully Opti-mized State.
DBAManage proactive services will keep Access databases at the highest maturity level which will accelerate business processing and profit-ability.
B U S I N E S S V A L U E
Outsourcing to a Data-base Managed Service Provider (DMSP) pro-vides business value in many areas such as operations, nightly processing, database availability and other areas. Economies of scale and efficiencies has improved perfor-mance with Cost sav-ings from utilizing CommitDBA services for staffing purposes, license fee savings on best-in-class tool Foglight along with other savings.
By outsourcing to a specialized DMSP like CommitDBA, Access has im-proved the nightly batch run, daily operations, database high availability, business processing and were able to make progress on their strategic IT initiatives. The maturity roadmap and streamlining has produced economies -of-scale and efficiencies to improve all IT areas that integrate with the SQL Server environment. In addition, cost savings from not having to license a best-class database tool (Foglight) and not having to engage a large in-ternal DBA staff has provided Access with exceptional business value. For more information on how CommitDBA Services can help your organization, please contact us.