SQLServerCentral / Editorials / SQLServerCentral.com / Data Loss or Downtime / Latest Posts

RE: Data Loss or Downtime

SQLRNNR — Thu, 13 Jan 2011 08:35:12 GMT

[quote][b]GilaMonster (1/7/2011)[/b][hr]It depends. Among other things it depends on what data is going to be missing.Thinking back to the bank there were some tables that we could do without during business hours but were critical for the overnight processes. There were other tables that we could do without for 3 weeks, but they had to be there (and complete) during the last week of the month. There were other tables where if the information in there was incomplete it was worse than if the system was completely offline.[/quote]I have run into very similar needs at several locations. Where possible, we created multiple filegroups and placed tables into appropriate filegroups and created a recovery plan based on that. The purpose was to get back online in the event of failure as quickly as possible so as to not lose further revenue. At the same time we knew we could get the remaining data back online with minimal loss to the business.

RE: Data Loss or Downtime

trubolotta — Wed, 12 Jan 2011 06:40:13 GMT

I think the answer "it depends" is inescapable but begs the question of how much downtime you can you afford (lost production, lost sales, customer inconvenience) versus the use of incorrect or incomplete data (incorrect pricing, erroneous decisions, production errors) and the consequential liablity. Lost production for a day may be more acceptable than the consequences of missing or incorrect data, at least that has been my experience. Others may have different experience or have applications more forgiving of errors. I see the real problem is not disaster recovery, but disaster avoidance.The fact we can pack more information into smaller spaces is not necessarily a good thing. The fact that we can run multiple virtual machines on a single piece of hardware simply means more machines go down when the hardware goes down. There is a false economy at work in reducing infrastructure costs when the probability and cost of catastrophic failure is not considered in the cost and benefit analysis. Similarly, that huge monolithic database may be a wonderful way to save the space required by duplication and physical partitioning, and the time required for transaction processing but the potential downtime cost should be considered as well.These are common issues in the protection of property (cost versus benefit) and the same principles should apply to disaster avoidance for information as well. Unfortunately, too many IT managers do not consider disaster avoidance but leap to lowest cost.

RE: Data Loss or Downtime

robert.miller-748628 — Sun, 09 Jan 2011 14:02:03 GMT

I have a customer where data is stored in retrieval system other than SQL and only meta-data is in SQL. For the other retrieval system, uptime is more important; but for the meta-data, completeness is more important.

RE: Data Loss or Downtime

myalias — Sat, 08 Jan 2011 04:44:13 GMT

I think my customers and my users always like to wait a little bit time more and begin to work with all data, so they don't need to check what data are lost or not and they don't have to enter data again ;-)

RE: Data Loss or Downtime

Indianrock — Fri, 07 Jan 2011 16:55:08 GMT

Although we have numerous data files and several filegroups in our main prod database, I don't think we could effectively restore a filegroup that would actually allow users to do work. So currently we'd have to wait for the full backup and subsequent differentials/incrementals to restore, which would be at least 6-8 hours assuming there was hardware available to restore onto.Obviously we're long overdue for a "hot/warm standby" type solution. We used to have log shipping to a standby server, but the decision was made to abandon that and move to Commvault for backups. Last night during a high cpu period Commvault somehow decided the log backups were not happening ( sql server says they were ), so it launched a full backup this morning during the business day and that is expected to run for 15 hours or more, during which time log backups won't run since Commvault thinks the log chain is broken. Fun eh?

RE: Data Loss or Downtime

Alan Vogan — Fri, 07 Jan 2011 13:06:35 GMT

"It depends." But for our company, I would guess the exec's would choose to wait for a full back-up. It wouldn't take that long and it would just mean that paperwork would pile-up.

RE: Data Loss or Downtime

Revenant — Fri, 07 Jan 2011 13:02:20 GMT

Oh well, I feel I am getting old.As several times before, I need to point out that the decison what is more important (to be up ASAP or to be exactly up to date) is not up to the IT staff, DBAs included. It depends on the industry and legal reponsibility of the company or division, so ultimately (and unfortunately) it is the lawyers who have the last word, like it or not. (I don't.)In my case, the picture is fortunately simple: I am on a BI project, and for my team data off by a day or two is no biggie, it is not accounting or CRM, so as long as we present a reliable picture of the business, we are OK.

RE: Data Loss or Downtime

LSCIV — Fri, 07 Jan 2011 12:28:10 GMT

[quote][b]bsheets 73864 (1/7/2011)[/b][hr][quote][b]SQLArcher (1/7/2011)[/b][hr]Hi,It depends on the nature of the system. I work in a financial institute with a lot of trading; in a [u]downtime vs. partial data loss situation[/u] business has to weigh the cost of being down until the data is recovered, risk of reputational loss and increased revenue loss against a smaller risk of reputational loss and less loss of revenue with partial data.In most cases ([i]in this scenario[/i]) it would be better to accept a partial data loss until it can be recovered, and get business up and running to mitigate the additional reputational and financial loss.[/quote]I also work in financial services, and I would disagree with this assessment. Having some customers log in and have missing transactions would be worse than keeping everyone offline until all the data is restored - imagine the number of support calls from customers with missing transaction data. Also, trying to merge the missing data with a database that has had numerous changes since returning to an online state could be problematic, and could result in duplicate key issues.Unless it was static historical data that is missing, staying offline until all is recovered would be better.[/quote]I think it would depend on whether or not the system is customer facing and revenue generating. If it is, I can't imagine any business-minded executive choosing to forego future revenue so the system can come up clean. Sure it would be nice if it did, but if it didn't that's fine too b/c it can be fixed. That's what we as DBA's get paid to do. I don't think anyone will be concerned about the number of support calls either. That's what CS gets paid to do and it's not revenue impacting so no biggie. In the end, like most business decisions, it's about the almighty dollar. Maximizing revenue and minimizing loss. The amount of work it causes you or I or customer service is irrelevant. For some service oriented companies there are SLA's that state for any and all downtime the service provider will share in the revenue loss with their customers. I worked for a financial company that had such a clause. As expected, uptime was their highest priority :-).

RE: Data Loss or Downtime

dma-669038 — Fri, 07 Jan 2011 11:55:44 GMT

It depends strongly on business needs. I work in healthcare, we need systems up 24x7 and people's lives literally depend on them, so Kimberly's statement is a valid one for me - although again the point Gail makes about what data is missing is also important. There is no point getting a db online with critical data to run the system is missing. Also to bear in mind many times is the cost of downtime vs loss of data. It costs money to maintain backups (disk space, off site storage, technology to do the backup or compression...). It even costs money if you consider the fact that i have to run a reindexing job in simple recovery mode and during that time i will not have backups. How much is all that compared to cost of loss of data? A good DR strategy is worked out with due consideration to all these factors and putting business needs in right order.

RE: Data Loss or Downtime

Eric M Russell — Fri, 07 Jan 2011 11:21:18 GMT

It not what's important to me personally as a DBA or developer, but what's important to the business: users, clients, and management. Technically speaking, a partial restore that's still functional is not even an option unless the database is partitioned in such a way that supports it. For example, if your data is partitioned across seperate file groups, where all of the critical operational data is contained sperately from historical or non-essential reference data, then you can present the option to business with a high degree of confidence that it will actually work as expected. You don't want to end up spinning you wheels and losing valuable time that could be better spent completing a full recovery. These are good questions, and it would be worthwhile to plan ahead and think about what data would be needed to support a minimal degree of application functionality, test the theory on a QA or staging server, and then have an alternate disaster revocery plan documented.

RE: Data Loss or Downtime

T_Peters — Fri, 07 Jan 2011 09:49:23 GMT

I primarily support internal finance systems. Since these are not order intake or revenue generating databases I'm going to assume that management would prefer less data loss with greater downtime, but this is a large assumption. While I have my DR plan on the mechanics of how to recover the data and applications what is missing is this decision by the business of what they thing is most important. Guess I have some meetings to schedule :( --Tim

RE: Data Loss or Downtime

Andy Leonard — Fri, 07 Jan 2011 09:41:55 GMT

Another great question Steve! I have to agree with those who've already responded with "It depends." That's not a cop-out, it's a valid question and each business needs to evaluate that question and the regularly review the decisions.:{>

RE: Data Loss or Downtime

RickObsitnik — Fri, 07 Jan 2011 09:06:22 GMT

[quote]I realized that most of the time, getting the site back up, having lookup and other types of ancillary data (like products, prices, etc), was the most important thing. Recovering other data such as older orders, was secondary.[/quote]historical data should be on separate systems and is far less critical than current transactional data such as prices and products that lead to sales systems. thereby, production transaction systems need to be very highly available. administrators should be focused on bringing these online immediately WITH good data, if that system goes down. historical data is what management uses to assist in analyzing where to go. current data is the life blood of an organization. that needs to be running all the time. management can afford to move slower than the front lines of an organization.with this in mind, the question isn't downtime v. data loss, all systems need to be up and running. the question is what data/systems should have priority in going back online.if it's affordable, having redundant systems and sites, eliminate this question. replicating data to warm sites makes this issue moot.

RE: Data Loss or Downtime

bsheets 73864 — Fri, 07 Jan 2011 08:45:17 GMT

[quote][b]SQLArcher (1/7/2011)[/b][hr]Hi,It depends on the nature of the system. I work in a financial institute with a lot of trading; in a [u]downtime vs. partial data loss situation[/u] business has to weigh the cost of being down until the data is recovered, risk of reputational loss and increased revenue loss against a smaller risk of reputational loss and less loss of revenue with partial data.In most cases ([i]in this scenario[/i]) it would be better to accept a partial data loss until it can be recovered, and get business up and running to mitigate the additional reputational and financial loss.[/quote]I also work in financial services, and I would disagree with this assessment. Having some customers log in and have missing transactions would be worse than keeping everyone offline until all the data is restored - imagine the number of support calls from customers with missing transaction data. Also, trying to merge the missing data with a database that has had numerous changes since returning to an online state could be problematic, and could result in duplicate key issues.Unless it was static historical data that is missing, staying offline until all is recovered would be better.

RE: Data Loss or Downtime

Joe Johnson-482549 — Fri, 07 Jan 2011 07:11:49 GMT

People working is first priority. Downtime in manufacturing is expensive and you only have to have one call from a VP or CEO to understand that insuring that people continue working is more important than your job. ;-)I would tend to agree with Nakul. Basic data to continue functioning. As much information for the past x days as possible in as quick a time as possible. Continue working to restore historical data more than x days old as opportunity presents. In my case, x would probably be 14 days rather than 30, even though some reports would be amiss.

RE: Data Loss or Downtime

Jeffrey Irish — Fri, 07 Jan 2011 07:00:20 GMT

For us, any VLDB that is critical has some kind of high-availalbility option like mirroring or active-passive clustering to help stave off outages. However, no solution is perfect.What we have started doing is warehousing data that is necessary to keep for processing, but not needed in the "day-to-day" shuffle of data.We can conglomerate data between the OLTP and warehouse databases for reporting, when needed and we have processes that pull data out of the warehouse for day-to-day operations when needed.One aside, how does one recover a database when it is damaged for use? How can you ensure that all of the information your application requires will be intact and has integrity?

RE: Data Loss or Downtime

TravisDBA — Fri, 07 Jan 2011 06:43:24 GMT

[quote][b]GilaMonster (1/7/2011)[/b][hr]It depends. Among other things it depends on what data is going to be missing.[/quote]I think that this is one of the most important points made so far. Also, how easily is the missing data to rebuild? A bad lookup table is one thing, but a table that holds one's personal account records and balance is quite a different animal. :-D

RE: Data Loss or Downtime

LSCIV — Fri, 07 Jan 2011 06:41:37 GMT

[quote][b]Nakul Vachhrajani (1/7/2011)[/b][hr]Hello!To me, the strategy that I have followed is:1. Get the database online as soon as possible - non-current data allowed2. Attempt to restore the most-recent data first - mostly OLTP systems need transactions from within the last month 80% of the time3. Attempt to restore the history data - the rest of the 80% data, used 20% of the timeGoing side-track here, but I find the above strategy useful in data cleanup projects as well.Have a great day![/quote]I agree with Nakul here. In most cases I'll go out on a limb and say uptime is more important than retrieving your existing data. I say that because downtime is putting future revenue at risk (assuming your system is revenue generating). At least if the system is up, even in a hobbled state and empty, future transactions can process. Also, data loss is defined by your backup strategy. If you take tlog backups every 30 mins then that's your potential data loss, up to 30mins worth of data. Everything else is just offline until it can be restored assuming you have good backups. There's a big difference between the two. I consider data lost if it's truly lost, meaning there's no way to recover it. Everything else is just temporarily offline.

RE: Data Loss or Downtime

Grant Fritchey — Fri, 07 Jan 2011 05:48:24 GMT

Obviously, I want my cake and I want to eat it too (and I'll have as much of yours as I can)... or at least that's how most businesses would approach it.When I'm setting up DR for various systems, I ask the business, how much data loss are you prepared to handle? My assumption is, if stuff goes south, you're going to lose data. So then, it's a question of minimizing down time. yeah, I try to get after the data too, but if an app is mission critical, the question quickly comes up, everyone off line, or one user with incomplete data? I know how most businesses are going to answer that one.

RE: Data Loss or Downtime

vineet.bhargava — Fri, 07 Jan 2011 05:45:59 GMT

It Depends...Like i use to work on the product which shows up the prices and then people do the purchasing from there.so if i make the thinks online without taking the downtime then may be customers will see the bad prices and which can create a bad impact over my site just because of the bad data.So its better that i take the downtime and then recover all of my data and then make the things back to normal again.Thanks Vineet

RE: Data Loss or Downtime

Mick Moses — Fri, 07 Jan 2011 04:48:34 GMT

Fortunately our OLTP Database fits on one tape, we also restore to a backup database for immediate rollover in case of failure.However recently we were hacked and although our DB was untouched some system files were corrupted so we could not load the DB.We dump all our major files to csv files overnight from our OLTP DB and these were initially used as a psuedo Datawarehouse until we got our current full blown MS SQL version. Because these files were available we knew our customers addresses, we knew what stock we had, we knew where the stock was and we knew all the prices.Now it wasn't as smooth and efficient as normal trading and the system says we didn't process anything that day but we got all the orders out that were due out. So the impact to the business was minimal and once the system files were restored all was well.So how well you prepare for downtime is as important as getting the data back.

RE: Data Loss or Downtime

Nakul Vachhrajani — Fri, 07 Jan 2011 04:09:25 GMT

Hello!To me, the strategy that I have followed is:1. Get the database online as soon as possible - non-current data allowed2. Attempt to restore the most-recent data first - mostly OLTP systems need transactions from within the last month 80% of the time3. Attempt to restore the history data - the rest of the 80% data, used 20% of the timeGoing side-track here, but I find the above strategy useful in data cleanup projects as well.Have a great day!

RE: Data Loss or Downtime

GilaMonster — Fri, 07 Jan 2011 04:00:56 GMT

It depends. Among other things it depends on what data is going to be missing.Thinking back to the bank there were some tables that we could do without during business hours but were critical for the overnight processes. There were other tables that we could do without for 3 weeks, but they had to be there (and complete) during the last week of the month. There were other tables where if the information in there was incomplete it was worse than if the system was completely offline.

RE: Data Loss or Downtime

SQLArcher — Fri, 07 Jan 2011 02:42:15 GMT

Hi,It depends on the nature of the system. I work in a financial institute with a lot of trading; in a [u]downtime vs. partial data loss situation[/u] business has to weigh the cost of being down until the data is recovered, risk of reputational loss and increased revenue loss against a smaller risk of reputational loss and less loss of revenue with partial data.In most cases ([i]in this scenario[/i]) it would be better to accept a partial data loss until it can be recovered, and get business up and running to mitigate the additional reputational and financial loss.

RE: Data Loss or Downtime

James Rimell — Fri, 07 Jan 2011 01:27:38 GMT

Being slightly pedantic I think the question:[quote]So what is more important to you: downtime or data loss?[/quote]is wrong and not a reflection on the spirit of the article, more correctly it should be along the lines of:So what is more important to you: downtime or partial/non-current data?But, in answer to the spirit of the article, the answer is as always, it depends :). I can think of several of the systems I have written/support that could function on a partial, non current data set, and others that are completely dependant upon the results that have happened within tens of seconds before. I think the defining requirement is to understand how the customer works with a system and the impacts of all scenarios such that some form of SLA is in place which allows the most efficient and cost effective re-instation of a fully working system.

Data Loss or Downtime

Steve Jones - SSC Editor — Thu, 06 Jan 2011 21:24:17 GMT

Comments posted to this topic are about the item [B]Data Loss or Downtime[/B]