How common is database corruption (1 Viewer)

I see these questions all the time regarding Access files. But what about other DB systems? I've never had a single corruption issue with MYSQL databases (which I always operated on a shared server) or Oracle. And I've literally caused PHP/MYSQL connection strings to throw hundreds of errors over the years so I would think I would've seen at least something. I've also caused errors to occur on numerous query operations on MYSQL tables and nothing has ever happened. Access has been the only application I've seen this happen with. Anyone else?

Most of the problems are caused by the way updates are handled. When Access is "native" on FE and BE, all of the work is done on the machine hosting the FE, and the file updates are handled with SMB protocol that grabs a chunk of the BE file at a time. The corruption occurs when an update is underway and that update gets aborted before fully writing out the buffer holding the changes. Thus you have a bunch of updated blocks followed by some that didn't get updates and now are inconsistent with the previous contents of the front of that buffer.

The other databases you named do their transactions locally, not using SMB but just doing a direct disk write that isn't subject to network glitches (unless they are using NAS drives). Further, most of those are defined to implement their updates in a way to automatically do a roll-back if the query fails. They are less susceptible to corruption because the window of opportunity for an accidental screw-up is smaller.

A recent client managed to corrupt a database on sql server, don't know how.

A client from a few years ago had frequent corruptions on an Oracle database - after some months of investigations, it was determined that the corruptions were occurring when the database was being backed up (between 1 and 2pm every day). Why they didn't do them at 3 in the morning is anyone's guess - their solution was to ban users from entering data or running reports whilst the backup as being run (performance was pretty poor anyway)

In 20+ years I've never had a SQL Server db corrupt. I have a client with an Access FE/BE that I didn't design but have helped him add functionality to (there are a lot of things I don't like about the design). Their BE was corrupting fairly regularly (at least weekly).

I helped him move the BE to SQL Server a few months ago and they haven't had a problem since. I think the design made it susceptible. Probably due to Doc's description, SS handles it better than Access did.

I have also been having corruption problems with Access 2010 recently. Since it is a small company with a relatively small DB, less than 100 MB, I thought about using SQL Server 2019 Express for the backend. Is this a relatively simple process and will all my forms, queries, modules, etc. still work?


Thanks.

Eljefegeneo said:

Is this a relatively simple process and will all my forms, queries, modules, etc. still work?

Click to expand...

I'd say there are 4 possible answers:

Yes/yes
Yes/no
No/yes
No/no



Seriously, it's a simple process if you have some familiarity with SQL Server and its tools (primarily SSMS and SSMA I would think for you). If you don't have that familiarity, there will be a learning curve.

As to everything working, it depends. I've seen situations where everything works after you link to the SQL Server tables (linked with the same names they had before). There can be gotchas, I suspect somebody will post a list so I won't tax my old brain trying to think of them all.

Guess I will try it on a sample of my db and see what happens. Noting ventured, nothing gained.

Sounds like a good plan. Post back if you get stuck on anything.

pbaldy said:

I'd say there are 4 possible answers:

Yes/yes
Yes/no
No/yes
No/no

Click to expand...

that's almost as bad as occam's razor:

the best solution is the simplest answer that meets all requirements

Click to expand...

CJL's comments about ORACLE reminded me of the time we were first implementing our Navy COOP solution (Continuity Of Operations Procedure) that involved nighttime backups that started about 0200 and ran until they finished, usually about 0530 to 0545.

The ORACLE gurus had indicated that for maximum stability of operation, we would do better to have our DB (back-end) files distributed on several smaller disks rather than one big file on one humongous virtual disk. Since we were using network-attached storage that was based on virtual drives anyway, it didn't matter to the NAS managers. However, this had the side effect of increasing the amount of time required to perform the backup.

So in October 2002, we had a minor hurricane come through New Orleans and we "flew the COOP" so to speak. I hadn't implemented the backup solution; we did what the NAS guys had said to do. I told them we hadn't tested restoration and that, on reflection, I didn't think it was going to work. The problem was "mixed instantiation numbers" or non-consistent ISNs, as ORACLE called it. We took a snapshot of a "moving target" and the target was moving faster than the snapshot mechanism - so the image blurred, metaphorically speaking.

I remember getting into a techie argument with Vance, the NAS rep, over whether we would be able to recover. I bet "NO" and he bet "YES" with the stakes being steaks. Winner bought the loser a steak dinner at the Ft. Worth "Cattleman's Restaurant." He bought me a really nice rib-eye, by the way, medium-well with baked sweet potato and mixed veggies. Yum!

That is as close as we ever got to a "corrupt database" with ORACLE. However, it is probably a decent analog for what happens to an Access back-end that becomes corrupt. In essence, it becomes internally de-synchronized.