Progressive, with annual revenues of more than U.S.$14 billion, is one of the largest insurers of private passenger automobiles in the United States. Its internally developed application for managing policies is key to its operations. As the company prepared to replace its mainframe-based application—first deployed in 1979, and frequently updated over the years—it needed enterprise-grade technology that would scale and provide the flexibility to easily deploy new solutions. Progressive is creating its new policy management application, using Microsoft® SQL Server® 2005 Enterprise Edition (64-bit), with development being done using Microsoft Visual Studio® 2005 and the Microsoft .NET Framework 2.0. The company’s 27,000 employees, 30,000 independent insurance agencies, and millions of customers will interact with policy data through Web-based access supported by Microsoft technology.
Since its founding in 1937, the Progressive Group of Insurance Companies has been an innovator, initiating 24-hour, in-person and online services, and other user-friendly ways to better meet customer needs. The company, which offers insurance to personal and commercial auto drivers throughout the United States, has grown to be the third largest U.S. auto insurance group with annual revenues of more than U.S.$14 billion.
The company’s innovation played a role in the group being named two years in a row to BusinessWeek magazine’s list of Best Places to Launch a Career, ranking alongside such employers as Walt Disney, Google, NASA, and General Electric.
An early adopter of computer technology, in 1979 Progressive created a policy management application called Proteus that was based on VSAM records, mainframe computing, IBM 3270 screens, and batch processing. Proteus has served the company well, but over its nearly three decades of life, technology and business processes have evolved dramatically. Proteus, written in the COBOL language, was created for processing paper policies that were mailed in by agents.
||Moving from the mainframe to the Microsoft Application Platform is going to significantly enhance our ability to respond to the needs of our customers.
IT Director, Progressive
Increasingly, Proteus, deployed across several mainframes, was creating legacy issues within the application environment—such as hard-coded business processes, brittle applications, disconnected data sources, and complex development tools—all of which hampered Progressive IT efforts to support business goals. Integration issues with the Proteus application led to Lengthy time-to-market for new or modified insurance products, and limited competitive advantage and/or delayed profitability derived from same.
True to its name, Progressive continually upgraded what Proteus could support by integrating leading edge technology, rolling out object-oriented applications, creating code generators, supporting mobile claims needs, integrating real-time with external data providers, building customer databases, extending component-based product specifications, achieving application integration via XML, and taking market leadership of insurance over the Internet.
Yet the continual enhancements to Proteus—achieved by grafting the new technologies onto the core batch system—greatly heightened the complexity of the system, making each new enhancement more costly. Progressive determined the key was to connect the applications together in ways that supported their required business scenarios—through service orientation and the mapping of core business processes. This was especially important for the company because at least a hundred downstream applications are dependent on this major line of business system. The company also needed an infrastructure that was connected and adaptable to the evolving needs of its internal business partners.
Progressive decided it was time to create a completely new application to replace Proteus. An integral part of the new solution would be a data mart to store policy information to give analysts and other managers a better view into the company’s operations to better serve its customers.
But as the company prepared for this move, it knew that it needed a solution that provided:
- Enterprise-grade reliability, as the application is mission critical.
- Scalability to meet future growth.
- Empowerment for internal developers, providing them with the agility to rapidly respond to customer needs and industry trends.
“Proteus, which is hosted across several mainframes, has served us well for nearly 30 years, but it wasn’t designed as a customer-facing system,” says Mark Stehlik, IT Director at Progressive. “It was becoming increasingly difficult to meet the expectations of our internal business customers, as well as our external customers. You can’t provide real-time responses to customers when dealing with a batch-processing application. We needed a new platform to help us respond to current and future
Progressive created a completely new application to manage all events within a policy’s lifecycle, and integrate with existing Progressive applications to process business such as new policy creation, endorsements, and reinstatements. The new policy management application is being built using the Microsoft® Application Platform to give its IT and development teams the tools they need to build connected systems that bring together their people, processes, and information.
The solution is based on Microsoft SQL Server® 2005 Enterprise Edition (64-bit) database software running on the Windows Server® 2003 Enterprise Edition (64-bit) operating system. Progressive’s 27,000 employees, its more than 30,000 independent insurance agencies, and millions of customers will interact with the new policy management application through Web-based access supported by Microsoft technology. This feature enables real-time policy processing and interaction with thousands of non-Progressive employees—independent sales contractors—enabling greater employee and partner satisfaction in sales and service processes, which translates into enhanced customer satisfaction.
The mainframe and DB2-based Proteus application will have served Progressive Insurance for three decades by the time it is retired. Choosing the successor technology was a significant undertaking. Progressive evaluated SQL Server 2005 for use as the new policy management application database, and found that it meets the company’s functional, performance, and availability requirements.
Final system testing for the migration work has already begun and the first deployment will go in production later in 2008. Progressive plans to convert its operations from the current mainframe-based Proteus application to its new policy management system using a state-by-state deployment. When full deployment is completed, the new policy management application is expected to have about two terabytes of data hosted on SQL Server, with growth expected to approach 10 terabytes.
Built upon the Microsoft Application Platform, the new policy management application will deliver a flexible solution that can respond rapidly to changes in business demand, product design, and customer needs. The application is designed to ensure that consistent but flexible rules are applied, information is stored accurately, and business events are communicated instantly to all affected systems.
||The data mart will enable us to perform analytics within minutes that with our old system could require months of data assembly.
IT Director, Progressive
Some of the basic architectural elements include:
- Policy Management Application. The new policy management application is being created using Microsoft Visual Studio® 2005 development system and the Microsoft .NET Framework version 2.0, an integral component of Windows® that provides a programming model and runtime for Web services, Web applications, and smart client applications. The new application will deliver all of the business rules for orchestrating policy events. It controls transaction workflow, and manages data exchange with more than 100 separate systems across the enterprise. As shown in Figure 1, the new policy management application manages each phase of the policy lifecycle, from receiving quotes and creating new policies, to adding endorsements, processing cancellations, reinstatements, renewals, expirations, and deletions.
- Policy Ops Database. The PolicyOps database is at the foundation of the solution, hosting all of the company’s insurance policies, and supporting the policy management application. Each policy is stored as an individual compressed large object, using the Varbinary(max) datatype, new for SQL Server 2005. Completely storing each policy avoids the need to adjust database tables to accommodate schema changes should policy formats be updated to capture new data elements. The database is expected to have 100 million rows of data upon full deployment, with near-term growth exceeding 500 million rows. To ensure scalability, the PolicyOps database will be federated across several instances of SQL Server 2005 using data-dependent routing federation. The Table Partitioning feature of SQL Server 2005 will be used to simplify database maintenance. Table Partitioning enables fast data loads and simplified maintenance for very large tables by giving database administrators the ability to treat multiple tables as a single entity.
- Policy PubSub Database. The Policy PubSub database supports publication of data to more than 100 subscribing systems. Every time the policy management application updates a policy, it writes the new data to the PolicyOps database as well as to the Policy PubSub database which subscribing systems regularly check for updates, through a .NET based distributed API.
- Policy Instrumentation Database. The solution includes an Instrumentation database to temporarily store application performance data for analysts to use in monitoring and tuning performance of the policy management application. In full verbose mode, the application will generate about 300 transactions per second into this database. The data will be pulled every five minutes into a staging database and then into a multidimensional cube created with SQL Server 2005 Analysis Services, the online analytical processing (OLAP) component in SQL Server.
- PolicyPro Data Mart. The PolicyPro Data Mart contains point-in-time history of all policies and the details of the transactions against those policies. The data mart is populated with information from the PolicyOps Database, through the PubSub Database. Data will be retained for 7 years to support reporting requirements by both support and business personnel. “We anticipate a data mart size of about 10 terabytes and 1.9 billion rows by the time we grow it to 7 years of policy data,” says Nadia Gordon, Manager Data Architecture Team at Progressive. “Having policy information in a relational data mart will greatly enhance our efficiency by providing near-real time access to information that was extremely difficult to access for reporting with our old system.”
All databases will be protected for high availability by using Microsoft clustering technology, with each database deployed in an active/passive two-node configuration. SAN replication—which is the current standard for all mission critical databases, will also be used for SQL Server disaster recovery.
All of the above architectural features continue to enhance the speed-to-market, reliability, and future-proofing of technical challenges from prior platforms. This new architecture creates new competitive advantages around policy/product-development and customer-service experiences for Progressive. While future-proofing their technical infrastructure, they are future-proofing their business positions as well.
“The new policy management application that we’ve created using Microsoft development tools and are deploying on SQL Server 2005, is absolutely essential to our operations,” says Stehlik. “As we prepared to replace our mainframe-based Proteus application, which had served us well for more than two decades, we knew that we needed to build our new solution on a solid foundation that would carry us into the future. Our past success with Microsoft products and technology, and the working relationship we’ve enjoyed with the company, made it easy to choose Microsoft.”
The project is significant. “This is the largest IT project that Progressive has ever undertaken,” says Stehlik.
Choosing the Microsoft Application Platform
After reviewing other solutions, and packaged applications, Progressive chose the Microsoft Application Platform for creating and deploying its new policy management application. Over the past several years the Progressive and Microsoft relationship has grown significantly. The local Microsoft account team and the Progressive team have worked closely to align people, processes and programs—including consultation with the Microsoft product teams—to help Progressive create the solution to meet its current and future needs.
Progressive has steadily been moving line of business applications from its mainframe environment to Microsoft technology. “We moved our extremely important quoting system and our front-end applications from the mainframe to Microsoft-based solutions over eight years ago,” says Stehlik. “We’ve been very happy with the performance and reliability we’ve enjoyed from the earlier migrations, so our policy now is to continue to move line of business applications off the mainframe whenever we are doing an application redesign. It simply doesn’t make sense for us to create a new mainframe-based application and the Microsoft Products and People continue to help us derive real value from
||With the old system, the policy data was updated during end-of-month batch runs on the mainframe … With the Policy Data Mart running on SQL Server we are working with near real- time information.
Business Systems Consultant, Progressive
the platform.” Benefits
Deploying its data on SQL Server 2005 and creating its policy management application using Visual Studio 2005 and the .NET Framework, will provide Progressive Insurance with a number of benefits, including a better view into the business, easier data management, enterprise-grade reliability, scalability, ease of integration, and the agility to significantly reduce application development time.
Better View into the Data
The PolicyPro Data Mart gives Progressive a better view into its business by hosting, on a relational database, information that was previously stored as VSAM records on a mainframe system. By storing historical data, the Data Mart will also do away with the need to retrieve old information stored in VSAM files.
“The Data Mart will enable us to perform analytics within minutes that with our old system could require months of data assembly,” says Stehlik. “We will be able to perform analysis that in some cases simply wouldn’t have been possible to do with our old system.”
Storing policy information in the PolicyPro Data Mart removes the performance problems that used to occur when queries were launched against production systems. Query requests could be complicated because some of the required data might have been in VSAM records, while other data was stored on a DB2 mainframe database.
“With the old system, the policy data was updated during end-of-month batch runs on the mainframe, and query requests had to be sent to a specialist to run, usually at night to reduce impact on the production systems,” says Kim Berliner, Business Systems Consultant at Progressive. “Depending upon the complexity, a request could take from days to weeks, or more to be fulfilled. With the PolicyPro Data Mart running on SQL Server we are working with near real-time information, and with SQL Server 2005 Reporting Services and Analysis Services, users will be able to create their own reports and queries, which greatly enhances productivity and efficiency.”
Underscoring the value of users being able to launch their own immediate queries against near-real time data, was the frustration that could come from waiting days for a data set, only to find it wasn’t the exact information required.
“The PolicyPro Data Mart removes the frustration of having to wait for someone else to retrieve your data,” says Berliner. “Now that lag time is removed and users will be able to explore the data they need on their own.”
Brian Surtz, IT Senior Consultant at Progressive, describes the difference this way: “Queries will be resolved thousands of times faster.”
Easier Data Management
||Queries will be resolved thousands of times faster.
IT Senior Consultant, Progressive
Creating its PolicyPro solution, hosted on SQL Server, has made it easier for Progressive to manage its data. SQL Server 2005 features the company is taking advantage of include:
- SQL Server Integration Services. “We are impressed with SQL Server 2005 Integration Services because it represents a more complete ETL [extract, transform, load] solution than the earlier product,” says David Wilson, IT Database Analyst Consultant at Progressive. “Integration Services enables us to work more programmatically in creating our ETL packages, and it is very robust.”
- Full-Text Indexing. “We’ve implemented full text indexes on all of the tables and dimensions in the data mart, which allows you to see very easily what changes from row to row,” says Brian Durkin, IT Applications Programmer Lead at Progressive. “This makes it much easier to create and run complex queries.”
- Error Handling. SQL Server 2005 modernized the transactional capabilities of queries by adding an error handling capability in form of the TRY/CATCH Transact-SQL construct. “The error handling with TRY/CATCH in SQL Server 2005 is absolutely wonderful,” says Durkin. “It allows us to provide a very consistent way of handling errors in all of our data mart stored procedures and in our operational systems. This means if there is any sort of technical error or a business error, we have pre-registered error messages that all get handled by one error handler and all the messages are distributed to our downstream caller application in a very consistent and formatted manner. Being able to easily track down any errors we might have with the SQL queries has been a big win for us.”
- Table Partitioning. “The Table Partitioning feature makes it easier to manage large tables and indexes,” said Wilson. “It is far easier to manage a 1-terabyte table when you can break it into partitions. We also use Table Partitioning to create sliding windows to help automate deletions that we would otherwise be doing with a loop process that takes forever and causes lots of blocking.”
- Dynamic Management Views. “Dynamic Management Views makes it easier for us to fine-tune queries and troubleshoot conditions that could slow performance,” says Wilson. “We can set our dashboard to show us, for example, the top 10 missing indexes, the 10 longest running queries, and other relevant metrics that save us a lot of time compared to running traces and manually tracking down problems.”
- Online Indexing. Progressive is using the SQL Server Online Indexing feature to perform defragmentation and other maintenance without taking the database offline. “Online Indexing helps us reduce the need for scheduled downtime,” says Durkin. “We can perform index maintenance at the same time that users are querying against the database. This is a big win for us because it is always tough to find time for offline maintenance.”
||Online Indexing helps us reduce the need for scheduled downtime. We can perform index maintenance at the same time that users are querying against the database.
IT Applications Programmer Lead, Progressive
Those who have worked closely with the original mainframe-based system, know how central Proteus is to the core functionality of Progressive—and how rock solid its successor application must be.
The PolicyPro application is absolutely essential to Progressive’s continued business success, so the company has extensively have stress tested and endurance tested SQL Server. “We performed extensive prototyping and testing, and everything showed that SQL Server 2005 is enterprise-grade and ready,” says Stehlik. “We are now building our solution.”
Already the nation’s third largest private passenger auto insurance group, Progressive needs the ability to scale its policy management solution to meet growth for years into the future.
“One of the great benefits with SQL Server is the ability to scale up [adding more processing power] and scale out [adding more servers],” says Gordon. “We are implementing a scale-out solution, and the ease of scalability was a large factor in the decision to create our solution using SQL Server 2005. SQL Server gives us the flexibility that we need for both scale-out and scale-up situations and solutions.”
While scaling up the processing resources was an option for the mainframe-based Proteus system, Gordon notes that “we see the cost of incrementally scaling up or out is considerably less using a solution based on SQL Server 2005. We anticipate significant growth as a company, and that requires easy scalability.”
Ease of Integration
With more than 100 other systems to interact with, including billing systems hosted on the mainframe, ease of integration was a key consideration when choosing the best technology for creating and deploying the policy management application and supportive infrastructure.
“The Microsoft Application Platform makes life easier for us because it removes the complexity that we might otherwise face in having to integrate third-party technologies,” says Gordon. “Without having to worry about plumbing disparate code together, we have more time to focus on delivering the best solutions to our business partners.”
Surtz likes the ease with which Visual Studio and the .NET Framework enable integration with existing components. “It’s a lot easier to program in C# than it was in C++, but we still have a lot of legacy code in C++,” says Surtz. “To handle message queuing up to our mainframe billing system, we just wrote a wrapper in C# to use the native C++ components. The .NET Framework deals with this seamlessly.”
Significantly Reducing Application Development Time
||“We don’t want to spend our time auditing code to ensure it meets our specifications. Policy-based Management will help us enforce policy without requiring the time we now put in to code auditing.”
IT Database Analyst Consultant, Progressive
The new policy management application, with its foundation of SQL Server and the .NET Framework, will make it much easier for Progressive to respond to market needs. “We have great business partners within Progressive who are continually innovating, and coming to IT asking for help in offering new services to our customers,” says Berliner. “When they ask for support, we need to be able to come through and help them achieve their goals for enhancing customer offerings.”
Previously the complexity of integrating new solutions with the old code base was so prohibitive from both a cost and time standpoint that IT couldn’t always support new initiatives.
“In the past the difficulty of integrating new applications with our old infrastructure could become a roadblock,” says Stehlik. “By roadblock, we mean integration efforts that could require months, or more. Those problems go away with our new solution. With Visual Studio, the .NET Framework, and SQL Server, we will be able to help our business partners keep Progressive ahead of the industry in what we offer our customers.”
Over the years it has become more difficult for the company to find the COBOL programmers it needed to maintain its existing Proteus system and write the code required to integrate with new applications. “It’s not just a matter of COBOL programmers retiring,” Stehlik says. “Many individuals who began their careers with COBOL have long since moved on to other languages, and more efficient development environments. Our programmers feel much more empowered working with the .NET Framework and Visual Studio.”
All of this greatly reduces the time it takes to create new solutions. “The Microsoft Application Platform gives us the agility to more quickly respond to the needs of our internal business partners and the changing demands of the market,” says Stehlik. “Moving from the mainframe to the Microsoft Application Platform is going to significantly enhance our ability to respond to the needs of our customers. We’ll able to pursue new business opportunities that simply would have been too expensive to code before.”
Looking Ahead to SQL Server 2008
Progressive has begun evaluating SQL Server 2008, and is looking forward to taking advantage of several new features in the product, including:
- Resource Governor. SQL Server 2008 enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. Organizations can use Resource Governor to define resource limits and priorities for different workloads, and to help ensure resources can’t be unduly impacted by poorly constructed queries or other unusual workloads. “We have users very excited to start launching queries against the data mart,” says Wilson. “From the perspective of a database administrator, the Resource Governor is a much needed and highly valued tool to make sure that a poorly constructed query won’t be allowed to dominate the physical resources.”
- Data Compression. SQL Server 2008 data compression enables organizations to store data more effectively and reduce storage requirements. “We haven’t started testing the Database Compression feature yet,” says Wilson. “But because we are talking about several terabytes of data, any compression we could get would lead to significant savings.”
- Policy-Based Management. New for SQL Server 2008, Policy-Based Management is a framework that helps organizations set and enforce compliance with policies for system configuration, SQL Server databases, and other SQL Server objects. Administered from the SQL Server Management Studio, Policy-Based Management can be used to set and enforce policy for internal and external database developers and administrators. “We have a lot of home-grown scripts, so the idea of Policy-Based Management is very attractive,” says Wilson. “We don’t want to spend our time auditing code to ensure it meets our specifications. Policy-based Management will help us enforce policy without requiring the time we now put in to code auditing.”
Microsoft Server Product Portfolio
For more information about the Microsoft server product portfolio, go to: www.microsoft.com/servers/default.mspx
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is comprehensive, integrated data management and analysis software that enables organizations to reliably manage mission-critical information and confidently run today’s increasingly complex business applications. By providing high availability, security enhancements, and embedded reporting and data analysis tools, SQL Server 2005 helps companies gain greater insight from their business information and achieve faster results for a competitive advantage. And, because it’s part of Windows Server System, SQL Server 2005 is designed to integrate seamlessly with your other server infrastructure investments.
For more information about SQL Server 2005, go to: www.microsoft.com/sqlserver
For More Information
For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234 in the United States or (905) 568-9641 in Canada. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to: http://www.microsoft.com/
For more information about Progressive products and services, visit the Web site at: http://www.progressive.com/
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Document published June 2008