News

Data warehouse disaster recovery: What's the plan?

James M. Connolly

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, analyse it, and if disaster struck the data warehouse, it would simply be repopulated on a new system.

Not anymore. With data warehousing and business intelligence (BI) increasingly tied to mission-critical applications, it's not always certain that a disaster recovery (DR) plan 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. "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 planning. Not only are companies more dependent on BI, but the systems are also serving new, 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 disaster recovery planners. Wayne Eckerson,

    Requires Free Membership to View

director of research at The Data Warehousing Institute, 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 centre operation. In a lot of cases, those data centres 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 and Oracle 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.

However, such tools alone don't address two other challenges that data warehouses present. The first challenge is how an organisation 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 planning.

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, 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 recognise 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 minimises 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 first 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 plan, 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.