Data warehouses: Tips for building a disaster recovery plan

It used to be that when you had a data warehouse, an analyst would run a bunch of queries against an offline copy of two years' worth of data, analyze it, and if disaster struck the data warehouse, it would simply be repopulated on a system.

Not anymore. With data warehouses and business intelligence (BI) increasingly tied to mission-critical applications, it's not always certain that building a disaster recovery (DR) accounts for your data warehouse.

"Data warehousing and business intelligence did start out as being not mission critical," said Claudia Imhoff, president and founder of Intelligent Solutions Inc., a consultancy in Boulder, Colo. "People would extract data out of the operational system and run away into their own environment, completely separated from operations. But in the past 10 years, analytics have become quite critical."

Imhoff said businesses today directly tie data warehouses to key applications, such as stock price analysis, fraud detection and inventory . Not only are companies more dependent on BI, but the systems are also serving , less technology-savvy audiences than when BI was used largely by analysts, Imhoff said. "If something goes wrong, they don't know how to fix it, and it really does cripple them in terms of making good business decisions.''

A key decision point is whether a data warehouse should be absorbed into the IT infrastructure, where it can be better addressed by those building a disaster recovery . Wayne Eckerson, director of research at The Data Warehousing Institute in Renton, Wash., said, "The litmus test here is whether a data warehouse becomes so mission critical that when it goes down, people begin to have problems. Then you want to move it into a data center operation. In a lot of cases, those data centers already exist, and they are more than happy to provide an environment for you with 24-by-7 backup and restore and all that.''

Built-in DR

Imhoff added that warehousing software from leading vendors such as IBM, Teradata Corp. and Oracle Corp. is designed to provide redundancy and failover for even the hundreds of terabytes of data that enterprises manage. She said even some small vendors that offer data warehouse appliances include effective disaster recovery tools.

In the past
10 years, analytics have become quite critical.

Claudia Imhoff, president and founder, Intelligent Solutions Inc.

However, such tools alone don't address two other challenges that data warehouses present. The challenge is how an organization deals with operational and business processes, ranging from identifying which employees are needed for a recovery, to determining where they need to be and which elements should be monitored in the data warehousing system.

However, Eckerson and Imhoff pointed out that a second challenge for CIOs is ensuring that "renegade'' data warehouses, as Imhoff refers to them, are brought into disaster recovery .

Eckerson said, "If departmental applications become successful, they have to scale them up. Their processing requirements go up, and their maintenance requirements go up. It becomes a big burden for the department. They have to decide if they are going to invest in resources for it so they can deliver a mission-critical environment. If they don't invest, will they deliver poor quality of service? Or they may decide to outsource it to somebody like corporate IT.''

Just identifying those departmental systems that sit outside the IT infrastructure can be difficult, with IT sometimes discovering them only when there is a problem.

Arup Nanda, senior director for database engineering and architecture at Starwood Hotels & Resorts Worldwide Inc., said his company hasn't had had a problem with renegade data warehouses, but IT workers keep an eye open for them. Nanda said the IT team watches for signs of data warehouse activity on end-user systems, such as use of Microsoft SQL Server and query tools commonly used with SQL Server. When such tools are found, the team works with users to determine whether a database should be brought into the IT infrastructure.

Nanda helps to manage a 64 terabyte data warehouse that supports a variety of critical applications, including email marketing. However, when it comes to disaster recovery, Starwood differentiates between mission-critical applications and business-critical systems.

For Starwood, mission-critical applications are tied to day-to-day needs, such as hotel operations, reservations, compensation and guest loyalty programs. These don't draw on the data warehouse; rather, they pull data directly from the online transaction processing systems. "The data warehouse is business-critical, but not mission-critical. If it's down for some time, we don't really lose revenue. But if it is down for more than 24 hours, the loss of certain reports affects management,'' he said.

Know your data warehouse

Nanda advises CIOs to be clear about the role of the data warehouse when developing disaster recovery plans, and recognize that the warehouse holds historical data, while the online transaction processing systems hold current data -- in his case nothing older than 18 months. In addition, he said the data warehouse should be read-only, which limits the warehouse to a single copy of data and minimizes the backup time requirements.

Nanda, who has had only one minor outage related to a corrupt disk, advocates careful documentation of every procedure associated with recovering a data warehouse, and defining which applications will be recovered in the event of an outage. In that regard, he works with 25 business systems analysts throughout the world, who act as bridges among user departments and IT.

Experts advise that you take inventory on the data warehouses you have, check if they are part of your DR , and note if they are tied to mission-critical applications. Once that is determined, you can know what will be affected if disaster strikes, and be prepared.

James M. Connolly is a freelance technology and business writer based in Norwood, Mass.