January 26, 2016 at 5:22 am
Hi all,
Here's something I keep thinking about and I was wondering what your opinion would be.
Case:
We are running the databases of our ERP system in a simple recovery mode. I have doubts whether we should change this to a full recovery model (I think it's obvious these databases are mission critical). A little while ago I had a chat with a consultant who knows a lot about this system and he indicated this is usually not done for the ERP system we are using (they even don't recommend doing so). The main reason for this decision is the system is not written to work with transactions. Therefore, a point-in-time recovery could potentially lead to a transactionally inconsistent database. The consultant indicated they usually advice to take a full backup at night and an incremental backup during lunch time (kicking everybody out of the system). Because there's nobody working in the system you can then ensure you are taking a transactionally consistent backup of the databases. At this moment we are only taking a full backup during the night giving us a potential data loss of up to 24 hours.
Question:
What do you think, is the argument put forward by the consultant a good reason to stick with the simple recovery model?
I'm looking forward to hear your opinion 🙂
Best regards,
Johan
January 26, 2016 at 5:55 am
Summarised consultant comment: "The system is written badly, so you'll have to accept lots of data loss if there's a disaster"
The full vs simple decision is one of data loss. If the server and storage go up in a ball of smoke, how much data loss can the business tolerate? It's not a technical decision, it's a business decision. The less data loss they're willing to tolerate, the more expensive and more complex the solution needed
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2016 at 6:02 am
If the system "is not written to work with transactions", then you run the risk of having a transactionally inconsistent database any time the machine or SQL Server or the application fails... regardless of whether you are in simple or full recovery mode.
January 26, 2016 at 6:28 am
Therefore, a point-in-time recovery could potentially lead to a transactionally inconsistent database
I'd push back on this statement and try to get a better understanding of exactly what they mean. Let's assume that you have internal and external storage in this ERP system. Any given database transaction (and regardless of whether or not the system is written to "deal with transactions" SQL Server is still absolutely doing them) is going to complete, or not, and then be recovered or rolled back as part of the restore recovery process, full or log restores, doesn't matter. You may have external storage that didn't recover in the same way (a common problem when recovering SharePoint, although there are ways around this) and that can lead to inconsistencies with the external storage. However, that's not going to lead to an internally inconsistent database. I'd really want to hear a detailed story from this consultant on how that failure is likely to occur (and yeah, I know that this failure can occur during any restore if the stars align against you, the implication here is that you're extremely likely to arrive at a failed restore).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 26, 2016 at 10:34 am
Johan Buis (1/26/2016)
Hi all,Here's something I keep thinking about and I was wondering what your opinion would be.
Case:
We are running the databases of our ERP system in a simple recovery mode. I have doubts whether we should change this to a full recovery model (I think it's obvious these databases are mission critical). A little while ago I had a chat with a consultant who knows a lot about this system and he indicated this is usually not done for the ERP system we are using (they even don't recommend doing so). The main reason for this decision is the system is not written to work with transactions. Therefore, a point-in-time recovery could potentially lead to a transactionally inconsistent database. The consultant indicated they usually advice to take a full backup at night and an incremental backup during lunch time (kicking everybody out of the system). Because there's nobody working in the system you can then ensure you are taking a transactionally consistent backup of the databases. At this moment we are only taking a full backup during the night giving us a potential data loss of up to 24 hours.
Question:
What do you think, is the argument put forward by the consultant a good reason to stick with the simple recovery model?
I'm looking forward to hear your opinion 🙂
Best regards,
Johan
But, the system DOES work with transactions, CONSTANTLY! Every INSERT, UPDATE, DELETE, and MERGE has it's own transaction. And, to be sure, (you're not using 2012+ so no need to worry about Hekaton) every one of these transactions are written to the log file even in the SIMPLE recovery model. They're just kept only long enough to be able to do a ROLLBACK if something goes haywire.
There may be something absolutely crazy about the database that I'm not aware of but I can't come up with any reason on this good green Earth why this system couldn't be set to use the FULL recovery model and, provided the correct transaction log file backups are made, why you couldn't recover to a point in time.
Perhaps the consultant is thinking of some other RDBMS engine but, with what you've stated about the problem, I'm in the camp of finding a different consultant... if you use DBCC TIMEWARP, you might be able to eliminate that particular problem altogether. 😉
--Jeff Moden
January 26, 2016 at 12:44 pm
Johan Buis (1/26/2016)
Question:What do you think, is the argument put forward by the consultant a good reason to stick with the simple recovery model?
My blunt answer is that the argument put forward by the consultant is to fire the consultant and buy a better ERP.
This ERP brings lots of risks to the business. If for wahtever reason (data entry error, water in the server room, accidentally dropping a table, etc) you need to restore from a backup, you will have to go back to the last full or differential and lose everything that happened after that. Orders were placed, and you lose them. Shipments were sent out but after the restore they are pending and you'll ship them again. Etc etc. The possible disaster scenarions are countless. And even if the staff has perfect recall of everything they did, they'll still lose a ton of time entering all the data again.
Plus, forcing everyone out over lunch just so you can make a backup? Too ridiculous for words.
If I ran a business that needs an ERP and I was offered to use this bad joke of a product for free, I'd refuse. I'd even refuse if they paid me to use it.
January 26, 2016 at 12:48 pm
Forgot to add this. Despite what the consultant says, there's nothing that holds you from using full recovery and taking log backups. As Jeff already mentions, SQL Server will always use transaction even if the application code doesn't.
But if the application has to make two modifications that are logically connected (e.g. shipping an order and reducing the stock level) and the application sends them as separate transactions, then restoring to a point in time does open you up to the risk of restoring one of the two modifications but not the other. That's a direct result of the terrible application design from the ERP manufacturer, and there's nothing that you, we, or SQL Server can do about that.
January 26, 2016 at 12:52 pm
Does this consultant mean that the DB is typically used in a read only manner or does he really think it doesn't use transactions? If it's really a read only DB and any modifications are something that can be easily reapplied from the source then running in simple recovery mode might be okay but on the other hand there's also no harm in running in full recovery mode either.
January 26, 2016 at 2:20 pm
The consultant indicated they usually advice to take a full backup at night and an incremental backup during lunch time (kicking everybody out of the system).
I guess this consultant still lives in the 1980s. Given globalization, modern systems -- particularly, one would think, ERP systems -- do not require "kicking everybody out" at any time, least of all just to be able to take a usable backup.
Personally, if I were you, I would still take differential backups (SQL Server doesn't have incremental backups, only differential) during the day. In an emergency, the saved data may be far more valuable than the time it takes to re-sync that data to a consistent state that is usable.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 27, 2016 at 8:18 am
Been there. By chance does the vendor company name start with a T and is five letters? The vendor name of the ERP system that I had to deal with does not and never will appear on my resume.
Find out what their plan is if the system barfs at 11:55am before the noon backup is done. Do you lose all the work done in the morning? Perhaps they have some sort of transactional log file outside of SQL Server that they can replay and recreate the work. Does your boss and the highest levels know what kind of data loss they might be looking at?
The ERP system that I worked on that was similar garbage used an application server and had no stored procedures: all transactions were cursors stepping through the database, including billing runs that ran for hours. When I was told they'd fixed their transaction log problem, I went to the ERP manager and told him that I wanted to perform a test: set up a duplicate system, start a billing run, then pull the power plug on the database server and see how it recovers. Oddly he wasn't enthusiastic about doing that. The term 'apoplectic' comes to mind.
One word of warning: be prepared to replace your DB server every 2-3 years. Performance probably will not scale, and as your database grows, things will slow. Our database was not small, it was over 150 gig and growing at 5% or so per month. We pulled statistics for a week that showed that at no time was the server coming close to exhausting memory or pegging CPU, yet we were told that we needed to replace the server. "It runs fast on our server which is faster than yours!" was the reply we always got from them. We could never get a satisfactory explanation from them because it would force them to admit that their product was built upon a bad paradigm of using a 'universal' programming language that violates everything that SQL Server is supposed to be, i.e. ACID.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply