The Ireland Department of Social Protection (DSP) required a Public Service Identification (PSI) application to replace an older Oracle system that often took too long to process queries for citizens seeking their PSI number. Taking advantage of the integrated full-text search feature of Microsoft SQL Server 2008 R2, DSP migrated to an innovative mission-critical solution that reduced search times from minutes to less than a second. Business Needs
The Ireland Department of Social Protection (DSP) is responsible for the delivery of a range of cradle-to-grave social insurance and assistance programs that cover needs including maternity, caring, illness, unemployment, widowhood, retirement, and old age. DSP oversees annual expenditures of about U.S.$27.8 billion, and it makes payments to nearly 950,000 people each week, with more than 1.5 million people directly benefiting from those payments.
||If one of our systems goes down and people don’t get their weekly checks, it could be headline news. We require 99.999 percent uptime, which is why we deployed PSI using the Microsoft platform.
Ireland Department of Social Protection
DSP employees across Ireland use a Public Service Identification (PSI) application when serving citizens who don’t know their DSP personal identification number. “About 30 to 40 percent of people contacting DSP for services don’t know this number,” says Paul O’Meara, Chief Architect at the Department of Social Protection. “Some just forget, or it could be a mother seeking services for an adult son or a number of other situations.” The database searches required to match the person with the correct number are difficult because of the numerous OR statements used in the queries to deal with ambiguities from typing errors, mistakes in spelling names or addresses, and other variables.
The department’s IT infrastructure, which has evolved over the decades, includes mainframe systems, UNIX/Oracle solutions, and an ever-growing use of the Microsoft platform. To determine a PSI number, DSP used a Basis Technology information retrieval application running against a database hosted on a UNIX/Oracle system. O’Meara says, “This system, which held a subset of our total information, served us, but because we couldn’t optimize beyond a full table scan, each query had to hit all 7 million records [one record for every child and adult in the country], and this was being done in parallel with concurrent users. The result was that one identification query could take minutes to run.”
A faster solution was needed, but it had to be rock solid because the PSI solution is mission critical to DSP.Solution
DSP has a long and successful history of using the Microsoft platform, and for the past several years it has used Microsoft technology whenever possible when deploying new solutions and upgrading or replacing existing ones. So DSP wanted to create its new PSI solution using Microsoft SQL Server data management software running on the Windows Server operating system. However, because PSI is such a critically important part of its operations—used by its social service agents across Ireland—DSP had to prove to itself that SQL Server could handle identity queries at least as fast as the existing solution.
The department had actually considered using SQL Server some five years earlier, but at the time it felt that the software lacked the ability to support the level of performance that PSI required for searching through full text. This thinking changed after the IT group at DSP began working with SQL Server 2008 R2 Enterprise running on Windows Server 2008 R2 Enterprise. DSP was impressed by the integrated full-text search feature introduced with SQL Server 2008 R2 because it provides a seamless transition between full-text searches and relational data searches—exactly what PSI required.
Working with a consultant from Microsoft Services and a handful of its own internal developers, DSP created a completely new PSI application, using the Microsoft Visual Studio 2010 development system and the Microsoft .NET Framework 4. The user interfaces were created using the Naked Objects Framework and Windows Presentation Foundation. “Development went smoothly,” O’Meara says. “The new application took only 30 developer days.” The innovative application provides a more efficient way of processing queries, reducing wait times for identity searches from what was often a minute or more to less than a second in full-load testing. DSP is preparing to deploy the solution across its operations.
DSP has been so impressed with the efficiency of creating solutions on the Microsoft platform, and the ease with which it is able to manage them, that it continues to evaluate new candidates for infrastructure and application migration.Benefits
Developing the PSI application with Microsoft tools and deploying it on the Microsoft platform helped the Department of Social Protection create an innovative solution that boosts query performance, while enhancing interoperability and meeting its needs for mission-critical availability. Innovative Query Solution
Building on SQL Server 2008 R2 helped DSP to boost query performance. “Working with SQL Server 2008 R2, we were able to create an innovative, more efficient application that provides our users with sub-second responses for queries that previously could require minutes to run,” O’Meara says. “The new full-text search capability of SQL Server 2008 R2, combined with performance optimizations we’ve seen in the newest version of SQL Server, made our previous problems with query length disappear.”Enhanced Interoperability
The new PSI application is providing dividends for DSP because of the ease with which the solution interoperates with other elements of the organization’s IT infrastructure, especially those parts that have already been moved to the Microsoft platform. “For some years we’ve been moving as much of our operations as we can to Microsoft applications and technology because by consolidating our disparate technologies onto one platform we gain value across the organization,” O’Meara says. “Using SQL Server 2008 R2 to support PSI makes it easy to use our identity solution in any number of other applications.”
Describing the importance of the department’s applications, O’Meara says: “If one of our systems goes down and people don’t get their weekly checks, it could be headline news. We require 99.999 percent uptime, which is why we deployed PSI using the Microsoft platform.”This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.