In-Depth

Goods for the Last Drop of Data

• By Kathleen Ohlson
• July 1, 2005

True Value wanted to build a data warehouse that supported an inventory control application, showing its members inventory and where it’s located 24 by 7. The company is a cooperative comprised of more than 6,400 independent retailers worldwide, including Taylor Rental Center and Home & Garden Showplace, with 12 regional distribution centers.

The application offers up-to-the-minute feedback on what inventory is selling and serves as a repository for historical data. The warehouse was built on an IBM pSeries p650 server, featuring an Oracle 8.1.7 database, 24-gig memory, with a 1.4-GHz of top speed. True Value also includes Microsoft SQL Server 2000 for its purchasing and Web applications, and IBM DB2 for other processing applications.

True Value expected the Oracle database to grow to 2 terabytes, but up to 3 years’ worth of members’ purchase information was ready to be added, as well as point-of-sale data.

True Value’s goal was to balance 24-hour access with as little downtime as possible, even if the Oracle database was due for system maintenance, but backup operations took more than 26 hours to perform. Homegrown scripts were written to maintain and support hot and cold backups, and changed when the environment altered.

“We were doing a lot of things in back up,” says Joe Chiu, True Value’s manager of database administration. “[Homegrown scripts] were hard to maintain. It was hard to recover anything. We would go through a lot of manual processing to back up and restore anything using homegrown scripts. When we’re trying to recover something, we want to do it real quick. We don’t have time to do research and look at the backup repository.”

True Value looked at different vendors, choosing BMC Software’s SQLBackTrack for Oracle—Enterprise Snapshot Edition. All scripting is done automatically, and downtime during backups takes a little over 3 minutes.

“Everything is handled by the software, and with the combined interface with our storage management software, we don’t have to worry about tape backups,” Chiu says. “It makes it easier for us to recover when the need arises. It frees up our database time to maintain our homegrown suites.”

Finding true value with tools
Enterprises are squeezing more out of their DBMSes, increasing system complexity to the point they’re devoting more staff resources to database administration. As a result, DB administrators and other IT staffers rely more on DBMS tools to support these large, complex environments—reducing human error and avoiding undue headaches.

Vendors are striving to fill in features, such as database encryption, automated recovery and archiving, integration across application stacks, and easy upgrades and patch deployments, to plug the gaps in database tools—and improve lackluster sales.

Forrester Research’s Noel Yuhanna expects security, specifically in security assessment, auditing and encryption, data replication and archiving, to be huge boon for database tool vendors. “There’s more demand for security tools than ever before,” Yuhanna says. “Right now most of the focus is on network security because it’s difficult to know if an internal user is a threat. Database security is an important criteria for IT implementation.”

Bryan O’Neil for JetAway, which offers discount hotel, flight and care reservations at premier casino resorts and leisure destinations, would like to see tools that handle permissions and security. “Data is a company’s greatest asset these days, and [security] is becoming more and more of an issue,” says O’Neil, JetAway’s senior data architect, DBA.

“There are many different types of systems—all of these data-driven—and you need to be able to manage communications effectively, and control that.”

Filling in the gaps
DBMSes offer basic features, such as replication, security, administration, performance and tuning, but their built-in tools aren’t robust enough to support mammoth structures.

JetAway’s O’Neil sought additional database tools when the company felt SQL Server’s management tools “weren’t up to snuff” for its developers to rapidly deliver data for its tailored vacation packages online. The data included all of the internally generated historical and current customer information, invoice and contact details, and partner information. JetAway’s system interfaces with its partners in airlines, hotels and car rental agencies to track inventory of their products that are used in JetAway’s packages.

“[SQL Server’s tools] kind of jump around different spots…and do crazy stuff,” says O’Neil. JetAway shopped around for database tool vendors, selecting Embarcadero Technologies’ database administration tool, DBArtisan, which included data modeling and change management.

“It just simplified things quite a bit,” O’Neil says. DBArtisan simplified migrating data, as well as allowing simple pointing and clicking to move database objects. “Microsoft tools seem [to need] a lot more work to get the same thing done.”

He also found Embarcadero’s customer relations to his liking. “They’re talking about their features and ask me about what enhancements I want to see in the tools,” O’Neil says. “They’re willing to listen to their customer base. Some say they listen to what [customers] say, but what they really want to say is: ‘Would you like to buy this [product]?”

Database vendors Oracle, IBM and Microsoft are offering tighter coupling between their developer tools and DBMS products to bolster sales. Microsoft recently announced Visual Studio 2005 and SQL Server 2005 will offer integrated development and debugging capabilities—features that already exist between Oracle JDeveloper and Oracle Database 10g. IBM offers integration between DB2 and both Java and Microsoft developer tools.

TSYS, a third-party processor of credit card transactions, is gearing up for SQL Server 2005’s integrated development and debugging features. “[Microsoft] extended the key components of database capability to people that were never before DBAs,” says Tim Kelly, director of the TSYS ProphIT Team. “They can write C# database access and other procedures, and really keep DBAs focused on high-end type things” such as database tuning and performance enhancements.

TSYS created ProphIT, real-time business intelligence reporting services, using Visual Basic .NET and SQL Server 2000 running on Windows Server 2003. It processes more than 6 billion transactions per year for large banks and retailers, and hosts this data for each of its customers on dedicated servers. TSYS initially deployed third-party and custom reporting products across a total data store of 1.3 terabytes, then replaced them with Microsoft software.

According to TSYS, SQL Server and its tools helped unite disparate silos of information, reducing development time by 75 percent and $500,000 in costs.

“It’s quite scalable for our needs,” Kelly says, “We don’t have any issues with database access. It’s stable with key application templates and [putting] metadata into SQL Server. It has complete capability to scale up and down, and management is quite simple; you can move $8,000 and $5,000 boxes in and out.”

Stacking up for the future
Database tools sales have lagged in recent years as sales for DBMSes slowed and database vendors Oracle, IBM and Microsoft improved administrative features at no additional cost, according to Forrester.

“Customers are looking for end-to-end tooling,” Yuhanna says. “It’s typically difficult to manage and understand behavior…all the way through the stack: network, storage and application. IT is looking forward to this.”

Within the next 5 years, data grids will offer transparent control for data storage as applications make calls to a data source and data grids will automatically retrieve and change data, so applications won’t be tied to a database type or location.

Virtualization is a challenge to show data integration across locations, according to Yuhanna. “There’s lots of globalization, and where [companies] get true data integrity across locations” is important, and grid computing will try to address this issue.

“Customers lose a half hour, a half-day or a day [for maintenance or a patch upgrade], or move version X to version Y to take a whole weekend, and they don’t have time to shut down the entire database,” Yuhanna says. “Every second, every minute counts. For some companies every minute is millions of dollars in lost revenue.” Oracle 10g includes a self-diagnostic engine called the Automatic Database Diagnostic Monitor that diagnoses and resolves performance issues, while IBM DB2 8.2 features self-healing through its Health Center Recommendations advisor tool, self-tuning, and automated backup and restore operations. Microsoft’s SQL Server 2005 is expected to offer more automation for installation, patch management, backup and data movement.

“Customers don’t realize automation saves more money than outsourcing,” he says.

Not to be outdone, third-party vendors will also look to fill in their own product gaps. For example, BMC lacks tools for security, archiving and replication, and it struck partnerships with IPLocks, Princeton Softech and GoldenGate Software to fill in these respective areas.

The Burton Group’s Peter O’Kelly expects the lines between designers, developers and administrators will blur, as well as database tools, including DBMSes and related tools providing native XML data model support.

“We’re on the verge of renaissance phase in all things content, document, database-related, and DBMS[es] will be at the center of the platform picture,” O’Kelly says.

Sidebar: An electric utility with an energy crisis
Chart: Database Tools, Beyond the Basics

• 0705adt_044 pdf

  File Type: .pdf File Size: 02.10 Mb