Australian Defence Materiel Organisation (DMO) System Program Offices must retain and access maintenance and engineering records to help ensure the safety and airworthiness of hundreds of aircraft. Obtaining reports from the database took several days—potentially jeopardizing the Air Force’s responsiveness. Aging systems became costly and difficult to maintain, and DMO sought a solution. DMO contracted Apt Business Solutions to develop NetMAARS, an enterprise maintenance intelligence solution based on Microsoft® SQL Server™ 2005 Enterprise (64-bit) Edition and SQL Server Reporting Services. Four Apt Business Solutions programmers completed the solution in only 15 months. They experienced smoother development and debugging by using Microsoft Visual Studio® 2005 Professional Edition and the Microsoft .NET Framework 2.0. Users now report faster reporting and increased productivity.
Imagine trying to keep track of the usage and maintenance inventory of millions of items ranging in size and complexity from aircraft turbine wheels to engines made of thousands of parts, to entire aircraft. Such is the daily challenge facing the Australian Defence Materiel Organisation (DMO), part of the Australian Department of Defence, in its mission to equip and sustain the Australian Defence Force (ADF).
||New features in SQL Server Reporting Services, such as multiple select controls, give report developers a lot more flexibility in the way that they construct report requests.
||Aaron Thomas, Software Architect, Apt Business Solutions
To ensure airworthiness and safe operation of the Air Forces’ aircraft fleet, DMO follows a prescribed maintenance protocol and schedule. The ADF has 420 aircraft and more than 60 high-technology assets, such as simulators and automated test equipment, located across the Australian continent. Over the years, DMO has developed its methods of recording and storing aircraft maintenance data. The evolution has followed the typical path from pen and paper to electronic storage to increasingly sophisticated methods for records retention, data discovery, and sharing data across systems and databases.
As DMO and its number of assets grew, its systems aged and became taxed to their limits. Logistics engineers, maintenance staff, and others had difficulty accessing maintenance data in a timely manner.
“When users ran reports against the legacy mainframe system, they waited several days or longer for their report, depending on its complexity,” according to Peter Fox, Managing Director of Apt Business Solutions (a Defence contractor). “This information latency began to affect Defence’s ability to operate an agile air force and had cost ramifications, as well.”
Often, users had to further manipulate the reports to make them useful—a step that required considerable time, specialized expertise, and training.
Also, the cost and complexity of supporting the UNISYS mainframe computer that hosted the aircraft maintenance system became prohibitive. The Department of Defence sought an alternative.
DMO awarded a contract to Apt Business Solutions to develop NetMAARS (MAARS stands for Maintenance Activity Analysis and Reporting System). Apt Business solutions is registered in the Microsoft® Partner Program.
A team of four developers created NetMAARS in only 15 months. Programmers relied on Microsoft Visual Studio® 2005 Professional Edition development system. To help them meet Defence’s timeline and budget constraints, developers relied on the tight integration afforded between Visual Studio 2005 and Microsoft SQL Server™ 2005 Developer (64-bit) Edition along with classes and features available in the Microsoft .NET Framework 2.0 programming model. SQL Server (64-bit) is the enterprise-class database optimized to run on 64-bit or Itanium-based servers and Windows Server® 2003. In production, NetMAARS uses Microsoft SQL Server 2005 Enterprise (64-bit) Edition.
NetMAARS uses SQL Server Reporting Services as its infrastructure for creating, embedding, and managing enterprise reports. Additionally, NetMAARS now can combine the data management capabilities of SQL Server with familiar and powerful Microsoft Office system applications to deliver real-time information that supports daily operations and helps users make informed decisions.
Logistics engineering managers, maintenance staff, and Defence contractor employees access NetMAARS by using a Web application.
|Total number of records
||370 million and growing|
|Number of assets tracked in NetMAARS database, ongoing
||Approximately 1.1 million|
|Reduction in time required for nightly processing
||More than 50 percent|
|Size of the temporal database
||200 gigabytes and growing|
|Number of users
||1,500 and growing|
|Number of developers
||Microsoft Visual Studio 2005 Professional Edition|
||Microsoft SQL Server 2005 Enterprise (64-bit) Edition|
|Lines of C#
|Lines of ASP.NET
|Lines of Transact-SQL (T-SQL)
With its support for a wide range of common data sources, such as OLE DB and open database connectivity, as well as multiple output formats, such as Web browsers and Microsoft Office system applications, SQL Server Reporting Services benefits developers and NetMAARS users alike. By using Visual Studio 2005 Professional and the .NET Framework, developers were able to use existing systems, connect to custom data sources, and deliver reports in various formats to a variety of devices.
SQL Server Reporting Services manages report requests and data viewing privileges as well as processes and delivers scheduled reports.
NetMAARS currently runs on a single, physical Hewlett-Packard blade server with four dual-core 2.2 gigahertz (GHz) 64-bit AMD CPUs with 32 gigabytes (GB) of RAM. Altogether, there are eight blade servers, three of which are being used for the NetMAARS production platform: one for Microsoft Internet Information Services (IIS) as a powerful Web server, one as the application server, and one as the database tier. Figure 1 shows the NetMAARS functional architecture.
Fox says, “There is capacity in the blade server set to allow us to scale the SQL Server database across two or possibly even three machines if the processing or reporting demand increases, and we know that SQL Server will scale to match Defence’s needs.”
Response to NetMAARS has been overwhelmingly positive. According to Fox, “NetMAARS has been well received among all groups of users, most of whom report improvements in personal productivity from better reporting and a faster, more responsive system based on SQL Server 2005 and SQL Server Reporting Services." Together, SQL Server 2005 Enterprise (64-bit), the .NET Framework 2.0, and Visual Studio 2005 Professional provide a comprehensive, dependable, and productive application development framework and database on which Apt Business Solutions built a secure, high-performance, mission-critical solution.
Smoother Development and Debugging
Working in Visual Studio 2005 Professional and the .NET Framework, Apt Business Solutions developers benefited from a seamless development and debugging experience across all of the system components, so they could deliver NetMAARS sooner and spend less time managing it later. Technologies available in Visual Studio 2005 Professional and SQL Server 2005 Developer (64-bit) simplify development.
||The .NET Framework slashed the time required to deliver NetMAARS. The classes associated with both the .NET Framework and Visual Studio [and] … the advanced visual designers and debugging tools … helped us be more productive.
||Jonathan Captanis, Developer, Apt Business Solutions
The .NET Framework gives developers a choice of development languages, including Transact-SQL. Fox and the development team also appreciated Microsoft SQL Server Management Studio, an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. With SQL Server Management Studio, developers and administrators of all skill levels have access to a broad group of graphical tools with a number of rich script editors.
“I liked having all those tools and access to Reporting Services, Query Analyzer, and other SQL Server technologies available in a single, integrated development environment,” says Fox.
Says Jonathan Captanis, Developer at Apt Business Solutions, “The .NET Framework slashed the time required to deliver NetMAARS. The classes associated with both the .NET Framework and Visual Studio meant writing fewer lines of code in the first place and debugging fewer lines later, and led us to quicker development. The advanced visual designers and debugging tools in Visual Studio helped us be more productive.”
The choice of the .NET Framework and Visual Studio 2005 Professional will continue to produce benefits over the long term, for example, when it comes time for maintenance and enhancements. Says Aaron Thomas, Software Architect at Apt Business Solutions, “When Defence asks us to add a new system or increase our data capabilities, very little has to change in the back end; it’s a matter of plugging in a new module.”
“SQL Server Reporting Services is easy to configure for our report developer and provides a rich toolset,” comments Thomas. “New features in SQL Server Reporting
|Figure 1. NetMAARS functional architecture.|
Services, such as multiple select controls, give report developers a lot more flexibility in the way that they construct report requests, and report developers and users alike appreciate the rich graphing and charting toolset.”
Fox adds, “There is already a substantial queue of new work aimed at providing additional system enhancements, and there’s no question that we’re going to stay with the Microsoft development system.”
NetMAARS integrates seamlessly with the Microsoft productivity and collaboration software that Department of Defence users rely on every day. Virtually every desktop has Microsoft Office system programs, such as Microsoft Office Excel® 2003 , Microsoft Office Word 2003, and Microsoft Office Outlook® 2003. Users have a lot more flexibility in how they consume and share reports, too. SQL Server Reporting Services lets them view, print, or save to Office Excel 2003 spreadsheets or Office Word documents for use in weekly or monthly reports.
Users can also easily perform additional analysis by using Microsoft Office Access 2003 or Excel. The seamless integration puts information in the hands of the people who need it, in a suitable and useful format. Says Fox, “Users can quickly go from data to insight—to sharing that insight.”
With NetMAARS, users need to do far less manipulation of reports and, consequently, save time and require less specialized training.
Fox adds, “The previous system was limited in what users could do with reports. With SQL Server Reporting Services, we can deliver reports that give what they need, so they avoid having to massage the data. Because they’re working with and sharing data using familiar Microsoft Office programs, they improve productivity and need almost no training.”
“In the move to SQL Server 2005 and the 64-bit server, we’ve seen a dramatic reduction in the latency for data processing and reporting and an enormous increase in the responsiveness of the system,” says Fox. For example:
- Nightly data processing, which used to take approximately six hours, now completes in approximately two hours.
- One highly data-intensive report, which used to take two minutes to run, now executes in about 20 seconds.
- Another report, which required 40 seconds, now finishes in 5 seconds.
As users have experienced faster database responses and have had more types of reports from which to choose, the number of report requests has increased—a statistic that NetMAARS captures.
“In the weeks after we formally switched to SQL Server 2005 and the 64-bit server, the average weekly reporting count increased approximately fifty percent,” enthuses Fox. “Users have absolutely been hammering the system—further proof that SQL Server 2005 and NetMAARS are meeting or exceeding expectations.”
Microsoft Visual Studio 2005
Microsoft Visual Studio 2005 is the world’s most popular development environment for designing, developing, and testing next-generation Windows®-based solutions and Web applications and services. By improving the development experience for Windows, the Web, mobile devices, and Microsoft Office, Visual Studio 2005 helps organizations deliver a variety of solutions more productively than ever before. Visual Studio Team System expands the product line with new software tools that enable greater communication and collaboration throughout the development life cycle. With Visual Studio 2005, businesses can deliver modern service-oriented solutions more efficiently.
For more information about Visual Studio 2005, go to:
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: www.microsoft.com
For more information about the Australian Defence Materiel Office, visit the Web site at:
For more information about Apt Business Solutions Pty. Ltd. products and services, call (61) (2) 6280 3300 or visit the Web site at:
© 2006 Microsoft Corporation. This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.