Customer Solution Case Study
/ / Mission-Critical Airline Reservation System deployed on SQL Server 2005
Overview
Country or Region:United States
Industry:Transportation
Customer Profile
Based in Minneapolis, MN, Navitaire provides its Open Skies and New Skies reservation systems to some50 airlines around the world.
Business Situation
Navitaire needed an operating system and database that would help it scale to meet the needs of its growing customer base.
Solution
Navitaire launched its new reservations platform on Microsoft® SQL Server™ 2005 Enterprise Edition (64-bit) database running on the Microsoft Windows Server® 2003 Enterprise Edition (64-bit) operating system.
Benefits
Scalability
Enhanced performance
Much faster development
Easier database management / “It’s fair to say we are developing our code much faster since moving to Visual Studio 2005, the .NET Framework, and SQL Server 2005.”
Mike Padgen, Principal Architect, Navitaire
The reservation system is at the core of an airline’s operations. Navitaire serves some 50 airlines around the globe with its reservation system, including industry leaders such as AirTran Airways, Jetstar, and Ryanair. When Navitaire’s existing reservation system, was approaching the limits of its processing capabilities for some of its largest customers, a change was needed. Navitaire upgraded to a solution using Microsoft® SQL Server™ 2005 Enterprise Edition (64-bit) running on Intel-based 8-CPU 64-bit database servers. To address this need, the company developed a new reservation system, using Microsoft Visual Studio® 2005 and the Microsoft .NET Framework. The solution gave Navitaire the scalability it required, enhanced performance, and simplified database management.
Situation
Navitaire's Open Skies airline reservation system, and New Skies, its newly launched reservation system, have been adopted by some 50 airlines around the world, includingAir Asia, AirTran Airways, GOL, JetBlue Airways, Jetstar, Ryanair, and SpiceJet. The company’s reservation systems are believed to be the world's largest airline Internet booking engine, handling more than US$8.5 billion in travel over the Internet last year.
Navitaire is a leading provider of business solutions to the airline industry and a pioneer in implementing information technology and business process solutions that enable substantial profitability gains for its customers. The company is headquartered in Minneapolis, MN and employs 750 people worldwide, with additional offices in Salt Lake City, Austin, Sydney, Manila, Auckland, London and Prague. Navitaire has enjoyed double-digit growth over the last several years, introducing new products and services that include a wide variety of mission critical airline systems covering the entire breadth of the airline revenue life-cycle. The company is a wholly owned subsidiary of Accenture, a recognized leader in providing global management consulting, technology services andoutsourcing to a wide variety of industries.
Navitaire is an application service provider (ASP), providing its customers with a complete solution for their reservation needs. Navitaire built its Open Skies reservation system infrastructure using HP3000 mini mainframe computers running HP operating system anddatabase products.Navitaire needed to replatform its Open Skies airline reservation system following HP’s announcement it would no longer be supporting the HP3000 within a few years. Additionally, some of Navitaire’s largest airlines’significant growth plans would require more processing power than the HP3000 and its database could comfortably support in the coming years.
As Navitaire prepared for future growth, it determined it would replace its Open Skies airline reservation system and create a new reservation solution that would run on a 64-bit architecture. As it sought the best platform to carry it into the future, it needed a solution with features including:
Scalability to keep pace with the growth of its customer base
Enhanced performance to meet its internal goals for customer service
A more efficient development environment
Solution
The company chose to use a Microsoft solution in creating New Skies, its next-generation reservation system. Navitaire developers used the Microsoft® Visual Studio® 2005 development system and the Microsoft .NET Framework version 2.0 to create the new application for 64-bit architecture. The .NET Framework is an integral component of the Microsoft Windows® operating system that provides a programming model and runtime for Web services, Web applications, and smart-client applications.
Although mainframes have traditionally been used to host airline reservation systems built by many of its competitors, Navitaire decided it could create the best platform for New Skies using the Microsoft Windows Server™ 2003 Enterprise Edition (64-bit) operating system and the Microsoft SQL Server™ 2005 Enterprise Edition (64-bit) database, hosted on Intel-based servers.
SQL Server 2005 is used for Navitaire’s demanding online transaction processing (OLTP) database. The Replication feature of SQL Server 2005 can be used to create an operational data store (ODS) on a separate instance of SQL Server 2005 to enable customers to access near real-time reporting without hitting the OLTP database with queries.
Navitaire’s OLTP databases are configured in active-passive two-node clusters using Microsoft Clustering technology to help ensure high availability.
Solution Architecture
Developers at Navitaire created New Skies using Visual Studio 2005 and the .NET Framework. New Skies was designed using a multi-tier architecture, which includes:
Client Tier. The New Skies client tier was created as a set of smart client applications, which provide rich functionality and are capable of working in a disconnected state connecting only to exchange data with the application tier. The client tier doesn’t include business logic.
Web Tier. The New Skies Web tier hosts Web-based user interfaces, TCP socket services, and a Web service API for accessing the business services layer of the application tier. It communicates with the business services of the Application Tier using the .NET Remoting component of the .NET Framework.
Application Tier. The application tier encapsulates all reservations and related business logic, business entity components, data access, and security of the core reservation system. This promotes consistent use of business logic at all tiers. The business services of the application tier access the database tier through a data access abstraction layer.
Database Tier. OLTP databases are hosted on SQL Server 2005; New Skies databases are protected through the use of a layer that governs authorized create, read, update, and delete access to the database. The database tier also includes the payments database, hosted on SQL Server 2005, for payment processing. A data access layer protects database resources from unauthorized access.
Reporting Tier. The Replication feature of SQL Server 2005 is used to update the ODS database to provide near real-time data for its customers’ access. Navitaire currently uses its own reporting systemto access the OLTP system.
This basic architecture can be deployed separately for each customer to give each airline its own reservation and reporting system. The tiers for each customer are deployed onIntel-based 8-CPU64-bit database servers, typically with eight dual-core, hyperthreaded processors and 32 gigabytes (GB) of RAM.
The ODS database, can be even larger than the OLTP databases. The ODS database for some of Navitaire’s larger customerscould be about 1 terabyte, and hosted on a single instance of SQL Server 2005. Total data stored by Navitaire on SQL Server databases could reach several terabytes once all of its customers have been migrated to the new system.
Benefits
Moving from the Open Skies platform to SQL Server 2005 running on Windows Server 2005, hosted on8-CPU64-bit database servers has given Navitaire a number of benefits, including scalability, enhanced performance, much faster development, and easier database management.
Scalability
The company’s Open Skies system’s infrastructure was reaching the limits of performance that it could provide for some of Navitaire’s largest clients. “Our old system was maxing out, and CPU utilization for our largest accounts was at about 80 percent during times of peak demand,” says Mike Padgen, Principal Architect, at Navitaire. “We needed more processing power and the ability to scale as quickly as possible to meet the needs of our fast growing customers.”
The move to SQL Server 2005 and Windows Server 2003 running on 64-bit hardware gave the company the ability to scale. “SQL Server 2005 commonly supports five times the number of transactions per second, and we’ve seen peak performance spike as high as 12 times the transactions per second,” says Chris Stirlen, Principal Database Architect at Navitaire. “Our CPU utilization has gone from 80 percent to just 20 percent.”
The company also gains scalability because of its multi-tier architecture. “With our Open Skies system, applications and reporting ran on the same CPUs as the database, which could impact our ability to scale.” says Stirlen. “With our new solution, we can just add computers to the client tier, Web tier, or anywhere else we need them. SQL Server 2005 supports up to a terabyte of memory, and so far we are using less than 32 gigabytes. This gives us huge scalability.”
Enhanced Performance
The new solution has achieved Navitaire’s performance goals.
“The response times we’re getting are even better than our goals,” says Padgen. “We recently converted a large customer over to SQL Server 2005 and the 64-bit architecture. One of our basic transactions is to look up flight schedules and fares. We call this Get Availability. Our goal has been to keep Get Availability response time to a minimum. With the new system, we process thesetransactions more than three times faster.”
“We tested the system at three times the normal workload of our largest customer, and SQL Server 2005 was still giving us the performance we needed and only running at 20 percent CPU utilization compared to 80 percent with the Open Skies system,” says Stirlen. “That gives us plenty of room to grow.”
Additional performance gains may be possible when Navitaire rewrites some of its code to take advantage of the native support SQL Server 2005, which provides for the common language runtime (CLR) component of the .NET Framework.
“We have one function that does string splitting because we need to pass in a common separated string for architectural reasons to get some data for updates,” says Stirlen. “When I tested this function I found that using CLR within SQL Server 2005, string splitting was 50 times faster than performing the string splitting the way we had been, using T-SQL.”
Much Faster Development
Visual Studio and the .NET Framework give Navitaire an integrated development environment that is easier to work with and that breaks down the programming language barriers that used to separate its developers.
“In the past, we had to involve multiple teams to make application changes,” says Padgen. “The application team used multiple languages and a proprietary source control system. The user interface was created using Microsoft Foundation Class tools, and our Web developers used yet another language. There was very little cross-over of our developers. Now that all of our developers work within Visual Studio 2005 and the .NET Framework, we are freed from the artificial programming language boundaries that used to exist. This makes it much easier to create, customize, and maintain our applications.”
Navitaire developers have enjoyed the move to Visual Studio and the .NET Framework, and noted an increase in productivity. “There was some initial anxiety about embracing these new development tools, just because the change was so big,” says Padgen. “But the benefits became immediately obvious. Debugging, for example, is so much easier in Visual Studio. Previously, we had to build our own XML parsers and XML builders. The developers had to write additional code from scratch that isn’t needed with our new development environment. It’s fair to say we are developing our code much faster since moving to Visual Studio 2005, the .NET Framework, and SQL Server 2005.”
The easier development benefits customers who need access to databases to create their own applications, such as airport check-in kiosks.
“Many of our customers want to access the functionality of our system so they can write some of their own supporting applications and processes,” says Padgen. “The fact that we can now easily expose functionality with a Web service is a big benefit to the airline. On the Open Skies system, we write our own from-scratch proprietary interfaces to the system, and it is a lot of work for both us and the customer. With New Skies, we can easily expose a standards-based Web service, which makes it a lot easier for the customer to integrate with our system.”
Easier Database Management
SQL Server 2005 is an easier database to manage and maintain than the Open Skies’ system’s database. “We have gained great manageability with our move to SQL Server 2005,” says Padgen. “Management Studio is extremely easy to work with. Everything—from basic database management through creating business intelligence—seems to be easier to do with SQL Server.”
Navitaire uses the Dynamic Management View (DMV) feature of SQL Server 2005 in fine-tuning query performance. “The new DMVs are great because they let me watch the system in production,” says Stirlen. “SQL Server has self-diagnostics that give me statistics on the server that I could never get before. I can use SQL Server to identify the slowest running queries, and use the Profiler to trace a slow query and improve its performance.”
The Online Indexing feature of SQL Server 2005 simplifies database management because re-indexing can be performed without taking the server down for maintenance. “Our operations team loves Online Indexing because occasionally we need to rebuild an index to maintain high performance,” says Stirlen. “We serve airlines around the world, so it can be challenging to find convenient maintenance windows.”
The company is also interested in the Table Partitioning feature of SQL Server 2005. “We need Table Partitioning because we have some large tables,” says Stirlen. “The Booking History table for some of our largest customerscan exceed 100 million rows.”
Microsoft Server Product Portfolio
For more information about the Microsoft server product portfolio, go to:
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: