4-page Case Study
Posted: 4/13/2006
2048
Rate This Evidence:

State of Alaska Department of Revenue, Permanent Fund Dividend Division New Data Warehouse to Buoy 7 Terabytes for State of Alaska

The Alaska Department of Revenue, Permanent Fund Dividend (PFD) Division, managed a multitude of disconnected and continually failing technologies, housing several terabytes of data, with only a six-person IT staff. It needed a new data infrastructure to support its operations, which includes the distribution of between U.S.$500 million and a billion in dividends to approximately 630,000 Alaskans each year. Using Microsoft® SQL Server™ 2005 Enterprise Edition, the organization’s new data warehouse contains 15 million images of historical and current documents, and has the ability to scale to support the roughly 3 million images added each year. After deployment, the PFD Division was able to integrate disparate technologies, increase infrastructure reliability and scalability, greatly improve operations and IT efficiency, and will ultimately save hundreds of thousands of dollars per year.

Situation

It was a messy situation that was quickly getting worse: a hodgepodge of 66 technologies from a variety of mainframe, mid-tier server, and software vendors; 15 million images and other files occupying several terabytes of storage space; 2 million lines of code; 200 tables of data, 80 percent of which went unused because they were not correlated; approximately 3 million new images to process each year; regular, and mounting, hardware and software failures—and only a six-person IT staff. 

This was the circumstance challenging the Alaska Department of Revenue, Permanent Fund Dividend (PFD) Division. “We desperately needed a change,” recalls Annette G.E. Smith, Data Processing Manager for the PFD Division. “Few of our old systems worked together, but ironically, when one piece would fail, everything else would go down. And that happened all the time.”

More than 630,000 Alaskans apply each year for the state’s oil revenue–based dividend. The PFD Division IT staff is responsible for facilitating the documentation, processing, storage, and backup of all applications, communications, and transactions between the state and its residents. Although the organization may be small, its data requirements necessitate an enterprise-scale data warehousing solution.

*
*Oracle is too proprietary in their tools, didn’t offer greater performance or reliability than SQL Server 2005, and is very, very expensive. We didn’t have a lot of confidence in Sybase or Informix due to past experiences. It was unclear whether DB2 would be able to handle our volume of work. And in my experience, open source is cost prohibitive. SQL Server 2005 seemed the obvious solution, and we’ve been thrilled with the results.*
Annette G.E. Smith,
Data Processing Manager, Alaska Department of Revenue, Permanent Fund Dividend Division
*

With continual database breakdowns, Smith’s IT staff was constantly troubleshooting failures and resolving problems in order to keep the infrastructure and organization running. “When our database goes down, our entire operation halts,” Smith says. “It’s as mission-critical as you can get.”

Accordingly, PFD Division managers wanted to consolidate and upgrade infrastructure technologies with a new data warehouse that would provide better stability and scalability, improve operational efficiency, simplify IT management, and cut costs.

“Our IBM mainframe is expensive, our Sun servers couldn’t handle the load, and our database software was crashing,” Smith notes.

After evaluating database solutions from Microsoft, Oracle, Informix, Sybase, and IBM, the PFD Division decided to deploy and standardize on Microsoft® SQL Server™ 2005 Enterprise Edition, part of Microsoft Windows Server System™ integrated server software.

“Oracle is too proprietary in their tools, didn’t offer greater performance or reliability than SQL Server 2005, and is very, very expensive. We didn’t have a lot of confidence in Sybase or Informix due to past experiences. It was unclear whether DB2 would be able to handle our volume of work. And in my experience, open source is cost prohibitive,” Smith explains. “SQL Server 2005 seemed the obvious solution, and we’ve been thrilled with the results.”

Solution

In deploying SQL Server 2005, the Alaska Permanent Fund Dividend Division is enjoying a powerful database solution with enterprise-class data management and integrated business intelligence tools. The organization’s new data warehouse delivers a complete data solution through a comprehensive and familiar feature set, interoperability with existing systems, and automation of routine tasks that reduce the complexity of creating, managing, using, and safeguarding enterprise data.

The PFD Division installed a Dell/EMC CX500 storage system, two Dell PowerEdge 6650 server computers to support SQL Server 2005 Enterprise Edition running on the Microsoft Windows Server™ 2003 Enterprise Edition operating system (also part of Windows Server System), and five Dell PowerEdge 750 server computers for automated tasks such as document recognition and automatic file linking.

“Most significant database deployments require exorbitant consulting fees, installation costs, premium hardware, and software upgrades,” advises Mike Shaw, Senior Network Specialist in the Alaska PFD Division. “Although Microsoft was extremely helpful, we were essentially able to handle this deployment on our own, with off-the-shelf hardware, in a matter of weeks. It was a remarkably smooth transition.”

According to Smith, the advanced data partitioning capability of SQL Server 2005 was a big factor in the selection decision. SQL Server enables the partitioning of a table into smaller groupings based on a partitioning scheme, which eases the management of large databases.

The organization’s new database consists of approximately 45 narrow tables, each hosting distinct data types. The “image table,” by far the largest, is used to store images (primarily in tagged image file or ".TIF" format) of every document received, processed, and transmitted since 2000.

Because the image table has more than a terabyte of raw historical data, with roughly 3 million 50-kilobyte images being added each year, the SQL Server partitioning capability proved invaluable. The image table currently contains nine partitions, segmenting the data by year. The partitions facilitate the administration of the database in smaller, more manageable chunks while also speeding up queries and improving backup and recovery.

To minimize backup resource requirements, the PFD Division is using SQL Server 2005 file group–level of detail. Archived partitions (historical data) have their file groups set to read-only and are backed up once. Active partitions (current data) have their file groups backed up nightly, with activity logs taken on an hourly basis throughout the day. 

“In the past, backing up our data was arduous at best,” Smith points out. “To back up our current files, we had to back up more than five years of static, outdated files as well. It was painstaking and inconsistent, and we were always holding our breath in anticipation of a crash.

“The partitioning capability of SQL Server 2005 enables us to segment our database into read-only and active files, which can be managed, queried, and backed up separately,” she continues. “As a result, we are more efficient as an IT staff, our organization has faster access to the most pressing files, and our data is better protected.”

Smith adds that although its new data warehouse availability has been flawless, the PFD Division is planning to implement the Database Mirroring functionality of SQL Server 2005 to further enhance the organization’s disaster recovery preparedness.

Benefits

According to Matt Skerbitz, Senior Programmer for the Alaska Permanent Fund Dividend Division, the reliability, scalability, efficiency improvements, and cost savings that the new data warehouse has delivered have been impressive.

The solution has performed impeccably, proving amply stable during the heavy load period from January through March, when Alaska residents submit their dividend applications and approximately 3 million images are added to the database. Skerbitz expects SQL Server 2005 to easily accommodate the entire 7 terabytes of data the division manages, the remainder of which is currently being ported from its mainframe, as well as future data.

“We need to house not only the past five years of images and two decades of data but also files that we accumulate in the coming years,” Skerbitz notes. “We now have a data warehouse in place that will grow with our data.”

In addition to providing high availability and scalability, SQL Server 2005 has improved operational efficiency through tight integration with Windows Server and automation of routine processes. Skerbitz suggests this seamless integration and automation would not have been as easy to implement with proprietary tools.

“With our old data infrastructure, 100 percent of our files necessitated manual data entry and processing. Each document had to be opened, reviewed, copied, entered in the correct file, and linked to a number of other files for dividend disbursement,” says Skerbitz. “Today, 70 percent of those documents are never touched by a human, thanks to the automation features of SQL Server 2005.”

These efficiency gains have enabled the PFD Division to reduce its temporary data entry work force from 18 individuals to 11.

The stability of the data management solution, in addition to the familiar Windows® operating system interface and ease of use, has greatly enhanced the productivity of IT operations as well. Instead of continually troubleshooting failures and recovering from system crashes, the IT staff now is able to focus on applications and processes that further boost overall operations efficiency, such as reporting and documentation.

Lastly, the PFD Division has dramatically reduced its costs while improving its data infrastructure. The licensing and maintenance expenses associated with the organization’s previous technologies had become unacceptable. “When compared with the expense of our old systems and the potential cost of other database solutions that we considered, it’s safe to say we will ultimately save several hundred thousand dollars per year,” says Smith.

“We have been amazed at the sheer brawn and reliability of SQL Server 2005, which is deftly handling 1 terabyte of images and poised to take on many more,” Smith concludes. “It hasn’t faltered once.”

Microsoft Windows Server System
Microsoft Windows Server System is a line of integrated and manageable server software designed to reduce the complexity and cost of IT. Windows Server System enables you to spend less time and budget on managing your systems so that you can focus your resources on other priorities for you and your business.

For more information about Windows Server System, go to:
www.microsoft.com/windowsserversystem

Microsoft SQL Server
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:
www.microsoft.com

For more information about the Alaska Department of Revenue, Permanent Fund Dividend Division, visit the Web site at:
www.pfd.state.ak.us

© 2006 Microsoft Corporation. All rights reserved.
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft, Windows, the Windows logo, Windows Server, and Windows Server System are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are property of their respective owners.
Solution Overview



Organization Size: 100 employees

Organization Profile

Established for the benefit of current and future Alaskans, the Permanent Fund is a U.S.$33 billion savings account fed by certain mineral revenues. A 100-person organization with six IT staff members, the Alaska Department of Revenue, Permanent Fund Dividend (PFD) Division, distributes a portion of earnings from this savings account to eligible Alaskans each year.  


Business Situation

With 66 disparate, failing technologies and a small IT staff, the PFD Division needed a powerful, reliable, cost-effective, and easy-to-use data warehouse to store its 7 terabytes of historical, current, and future data files.


Solution

The PFD Division deployed a new data warehouse using Microsoft® SQL Server™ 2005 Enterprise Edition running on Dell hardware.


Benefits

  • High reliability and scalability
  • Improved operations and IT efficiency
  • Significant cost savings


Hardware

  • Dell/EMC CX500 storage system
  • Five Dell PowerEdge 750 server computers
  • Two Dell PowerEdge 6650 server computers


Software and Services
  • Microsoft SQL Server 2005
  • Microsoft Windows Server 2003, Enterprise Edition (32-Bit X86)

Vertical Industries
Government

Country/Region
United States

Languages
English

RSS