To help ensure a consistent, reliable online customer experience as it expands the workload of the underlying web database, Progressive Insurance is turning to Microsoft SQL Server 2014 and its In-Memory OLTP feature. The software quadrupled
data performance in company tests, without the need to upgrade hardware. Progressive is also using the Microsoft Analytics Platform System to support predictive analysis of Big Data.
Progressive Insurance, a Fortune 500 company, has long made customer service a competitive strength. Today, the online experience of its customers is crucial to its continued success. Central to that experience is the company’s policy-serving web app, through
which customers do just about everything it’s possible to do with an auto insurance policy: buy it, cancel it, renew it, add or remove cars, and change account information.
As it updated the app, Progressive planned to add its Special Lines business such as insuring motorcycles, recreational vehicles, boats, and even Segway electric scooters. However, Progressive needed to know that the additional workloads wouldn’t put a damper
on the customer experience.
Managers were concerned about overtaxing the session-state database associated with the policy-serving app. That database, like most at Progressive, runs on a version of Microsoft SQL Server software. It holds the XML files that the app writes for every
active web page that a customer visits. The app uses the files to synchronize data so the customer sees consistent information from page to page and has a consistent experience across the website.
Progressive projected that the database couldn’t handle the increased load without a prohibitively expensive increase in hardware. Some of the company’s other session-state databases had experienced outages, and Progressive wanted to head off that problem
The company had other, unrelated, data concerns. As it consolidated its data environment on SQL Server, it wanted to handle increasingly massive amounts of data for predictive analysis. Could SQL Server meet this need?
||Our IT leadership team gave us the numbers we had to meet to support the increased database workload, and we far exceeded those numbers using Microsoft In-Memory OLTP.
| Craig Lanford
Progressive has long made Microsoft technologies its standard, so it was natural for the company to see whether the newest version of SQL Server—SQL Server 2014—could address its database issues.
In particular, Progressive was interested in In-Memory OLTP capability, which is one of the SQL Server 2014 features designed to boost mission-critical data-center performance. In-Memory OLTP can host online transaction processing (OLTP) tables and databases
in a server’s working memory. There, an application can access them directly, without the potential bottlenecks of hard disk input and output. The result is better performance, CPU utilization, and reliability.
The company tested In-Memory OLTP even before SQL Server 2014 became commercially available. Modifying the policy-serving app for the test was relatively straightforward, according to Craig Lanford, IT Manager at Progressive.
The company modified eight natively compiled stored procedures, using already-documented code. (For a planned production deployment, Progressive expects to modify up to five additional stored procedures.) It then put the application through a range of stress
tests designed to simulate increasing amounts of user activity on the website. The tests were conducted on a Cisco UCS B200-M3 server computer with 256 GB RAM.
In those tests, In-Memory OLTP boosted the processing rate from 5,000 transactions per second to 21,000—a 320 percent increase.
Meanwhile, to better support predictive analysis, the company acquired a Big Data appliance with Microsoft Analytics Platform System (APS) capabilities. Progressive also updated an existing Hortonworks Hadoop cluster to work with the system.
The company has moved two of its structured analytics applications to the APS, and plans to move up to 20 more over the next few years. The APS infrastructure consists of a full rack for production and a quarter rack for test.
Progressive sees SQL Server 2014 as a very practical way to increase database performance and promote great customer and employee experiences online. Meanwhile, it uses the SQL Server-based data appliance to expand its work in predictive analytics.
Performance Quadruples to Power Business Growth
Lanford and his colleagues were delighted that the session-state database performance proved four times as fast with SQL Server 2014. “Our IT leadership team gave us the numbers we had to meet to support the increased database workload, and we far exceeded
those numbers using Microsoft In-Memory OLTP,” says Lanford. “We expect this to give us the ability to scale the application on our standard hardware.”
The company will use the throughput gain to support the addition of its Special Lines business to its policy-servicing app and session-state database. With its use of SQL Server 2014, Progressive can run a single, larger database reliably and avoid the cost
of multiple databases. Lanford envisions rolling out In-Memory OLTP across the infrastructure to boost throughput without requiring corresponding increases in hardware.
Helps to Ensure Great Customer and Employee Experiences Online
By using SQL Server 2014 to integrate auto and special-lines business into a single online app, Progressive will give its customers and employees a better online experience, according to Mary J. Groth, Database Analyst Consultant at Progressive.
“Both customers and employees use the policy-servicing app; it’s the central UI to Progressive,” she says. “We need to make sure that we continue to provide a great online experience even as we continue to grow. We can use SQL Server to help make that possible.”
1,900 Percent Processing Gain Supports Predictive Analytics on Big Data
Progressive achieved even greater processing increases in its tests of the Microsoft Big Data appliance. The APS boosted query processing by 1,900 percent compared to queries on Progressive systems. Concurrent processing on the appliance showed an 807 percent
increase. It also enabled Progressive to integrate its Hadoop and relational data.
“We achieved jaw-dropping increases in processing speed with the Analytics Platform System,” says Lanford. “There were queries that we couldn’t otherwise complete on-premises that ran in just two minutes on the appliance.”
Progressive uses the APS to power a major increase in its work with predictive analytics. Lanford says, “The analyses we’ll run on the APS will help us to increase sales and retain customers.”
This case study is for informational purposes only.