September 22, 2015 at 2:52 pm
erichansen1836 (9/22/2015)
you skipped over answering ever time anyone mentioned what if something happens to you, what are the backup and restore capabilities.
if I get struck by lightning, a decent consultant or DBA can be hired to pickup where i left off. Sadly, since i follow best practices and all that, someone could swoop in and run with it pretty easily.
This is all documented on Microsoft website.
The JetComp.exe utility has been around a long time.
If you have any more questions regarding backup and restore and compact/repair,
I invite you to visit Microsoft's website and some of its partners like
EverythingAccess.com to name 1 of many.
This is simple DBA work a Developer can do, or a semi-technical person in any company department such as accounting or marketing, etc.
Are you unsure of how to stick a thumb drive into a USB port and use Windows Explorer to copy files?
Someone close by should be able to assist you.
If you wish to implement Text File databases using Perl SDBM databases as indexes, you can take what I have told you and google Perl forums.
I have seen what kind of DBA work a Developer can do, that's why I have a job. You honestly believe that a semi-technical person in any company department could do the work that many of us do on a daily basis? I wish you had invited me to that party. Yes, with SQL server these individuals can manage to do some of what we do at a very basic level. But as a database system grows and matures, it usually becomes more than they can handle and then they need professional help. It could be a contractor coming in a few hours every week or it could be hiring a full time DBA. Thing is, it usually happens at some point.
September 22, 2015 at 2:55 pm
Access databases (.mdb at least, not totally sure about .accdb) were notorious for corrupting if you didn't treat them nicely. I wouldn't trust an .mdb for anything that required security, recoverability etc any further than I could throw it. Putting much of anything in an Access DB and thinking it's going to be secure is a recipe for disaster. Want your data stolen? Great, put it in an Access database. Hope you don't mind HIPPA violations and fines, because you'll get them in spades, because users need FULL access to the folder containing the database.
I guess you could use this kind of technology for larger databases, but why? One violation would no doubt be more expensive than a SQL Server license. (Heck, you could even use a FREE version of SQL Server!!)
The problem with Access databases is that they have no security in the newer versions. It's all in SQL Server...
But if you want to expose yourself to serious security risks with your data, go right ahead.
They aren't ACCESS databases, they are Jet Engine Databases.
ACCESS and JET ENGINE are not synonymous.
Access uses Jet Engine, that's where the notorious corruption takes place, using ACCESS as the front-end.
ODBC enabled interfaces (Batch or GUI) are extremely stable, and I have never had an issue in all my years since 1998 using this type system for several corporations.
Don't use VB ODBC implementation though because it is flawed.
Win32 PERL from ActiveState and the Win32 Perl ODBC module are great (Dave Roth creator).
I am sure there are other stable ODBC implementations or else ODBC would have been a flop.
You are wrong about FOLDER permissions.
Garry Robinson(2004 APress) has demonstrated you are wrong.
September 22, 2015 at 3:07 pm
he problem is you don't want a true discussion. You want people to see your solution and proclaim your greatness. The problem is, you came to a site where database professionals live and as I keep telling, are job is to protect and defend the data that support the business.
No, your job, as you see it, is job security.
And just like the Apollo 13 Engineers, they didn't want to lose their jobs if the LEM engines didn't start on/off, on/off intermittently for CSM course correction (and to gain speed/momentum towards Earth).
Gene Kranz told them, "I know what you are trying to do" i.e. protect your jobs.
"If it lights it lights" Gene said.
Gene assured them that their precious engineering jobs wouldn't be lost if the LEM failed to do what needed to be done to rescue the astronauts.
It is this sort of IN THE BOX THINKING and SELFISHNESS
that Gene Kranz couldn't stand.
QUADRUPLE FAILURE, that can't happen?
Yes it can, and if you don't have a contingency plan with SQL SERVER when it goes under, then how is your business going to have a successful failure instead of a total failure?
MS-JET/ODBC databases into the billions of rows is one(1) possible option.
Fixed-Length Record Text File Databases with PERL SDBM indexing is a 2nd option.
You can put a U.S. Government, square CO2 filter cartridge into a Round CO2 filter cartridge hole with a little ingenuity. If you can think outside the box.
September 22, 2015 at 3:37 pm
erichansen1836 (9/22/2015)
he problem is you don't want a true discussion. You want people to see your solution and proclaim your greatness. The problem is, you came to a site where database professionals live and as I keep telling, are job is to protect and defend the data that support the business.
No, your job, as you see it, is job security.
And just like the Apollo 13 Engineers, they didn't want to lose their jobs if the LEM engines didn't start on/off, on/off intermittently for CSM course correction (and to gain speed/momentum towards Earth).
Gene Kranz told them, "I know what you are trying to do" i.e. protect your jobs.
"If it lights it lights" Gene said.
Gene assured them that their precious engineering jobs wouldn't be lost if the LEM failed to do what needed to be done to rescue the astronauts.
It is this sort of IN THE BOX THINKING and SELFISHNESS
that Gene Kranz couldn't stand.
QUADRUPLE FAILURE, that can't happen?
Yes it can, and if you don't have a contingency plan with SQL SERVER when it goes under, then how is your business going to have a successful failure instead of a total failure?
MS-JET/ODBC databases into the billions of rows is one(1) possible option.
Fixed-Length Record Text File Databases with PERL SDBM indexing is a 2nd option.
You can put a U.S. Government, square CO2 filter cartridge into a Round CO2 filter cartridge hole with a little ingenuity. If you can think outside the box.
The problem is you don't realize you are in a BOX.
You have absolutely no idea what it is we do as production and development DBAs. You seem to think that we are unwilling to change, only interested in securing our own jobs. Too bad you are lost in your own reality. Many of us try hard to make ourselves dispensable. We willing share our knowledge with follow workers. We work hard to create processes that manage our database in a proactive manner. We try to use best practices so that any SQL Server professional could come in and take over for us. ANY ONE who thinks they are indispensable is living in a fools paradise.
You again have shown no desire to have real conversation regarding the pros and cons of your solution. You rely completely on documentation of others to support your position and have shown absolutely no independent thought in defense of your solution.
I think we are done here.
September 22, 2015 at 5:00 pm
Just a quick question and apologies for not following/reading all of the posts that have occurred since the last time I dropped in on this thread...
Do you actually have a system like what you've been talking about up and running in a production environment?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2015 at 3:51 am
As I understand it you have a "full backup" mde when all users are our of the system, e.g. at end-of-business. But it is not clear to me if you also have some other incremental backup during the day, so my questions are how do you:
1. Restore to a point-in-time?
2. Recover from a total failure of disk drives / server/building destroyed etc. during the working day? (with minimal data loss)
3. Recover from a failed data file corruption / disk controller failure etc? (with zero data loss - a "Tail log backup" in my earlier example)
4. Migrate to a new (bigger / better) server. Is this scheduled downtime for the entire duration of copying the files? (which, if they are massive, will take "a long time") or is there an incremental-recovery process that can be done whilst the original system is still in use, followed by a very short period of scheduled downtime? (to transfer the final incremental-slice of data)
5. How do you manage a Hot Standby system (which can take over in the event of a failure of your Primary System "instantly")? (Or even a "warm standby")
September 23, 2015 at 8:40 am
We work hard to create processes that manage our database in a proactive manner.
Oh, but I thought you all have been arguing that SQL SERVER does everything.
You are doing nothing different than I am doing, creating processes to manage our database.
September 23, 2015 at 8:42 am
I think the debate and discussion has gotten somewhat off track. I'd ask that we not learn towards getting frustrated or angry and explore the possibilities (pro and con) of using a JET Engine for database work. If you find yourself upset or insulted, please don't post and walk away. You can unsubscribe from this thread: http://www.sqlservercentral.com/Forums/TopicSubscriptions1.aspx
A few things to be aware of.
- The JET Engine powered Exchange for years. Might still do so. Those are some very large, and very active databases.
- Some of the other RDBMSes, like MySQL, use files for tables and indexes,relaying on the underlying OS to help manage access.
- Time is not free. If you want to compare the cost of a custom solution with JET to SQL Server, you cannot only look at the cost of the licenses, but also the cost of implementing programming and administration.
- There are real considerations in many, but not all, companies about transactions and ACID compliance. AFAIK, this does not exist in JET as a native feature, and some programming must support this.
- If you work in a domain where you have more control over the application and user actions, then life is easier, JET or SQL Server.
- Recovery and restore is very important in the data world. It isn't always needed, but when it is, being able to recover data is very important to most businesses, and DBAs. People do get fired when they can't recover data. However, when disaster strikes, sometimes companies understand something is lost. It's not necessarily a given that if a few hours of work is lost that your job is at risk or the company fails. I wouldn't want to bet on it, but don't assume others wouldn't. Respectfully disagree if this is your issue.
- It is helpful to debate specific situations, and since there are more SQL proponents than JET proponents here, understand that it might take time to get a response.
- Documentation is important, and certainly there are lots of words on MSDN and similar sites. However, documentation doesn't tell the story of how an application works or is written. Most of us would see the code for SQL Server, or Excel, or SSC.com and we wouldn't know how it works for a substatial amount of time. Do not discount the internal knowledge of individuals. The more you build yourself, the less easy and more time required for someone else to understand things.
- What works at small scale does not work at large scale. We see this over and over in the world of computer science where a prototype or POC doesn't scale up. I'd be wary of assuming tests on one machine apply to larger scales.
Keep debating. It is interesting when this is on point and not emotional.
September 23, 2015 at 9:05 am
erichansen1836 (9/23/2015)
We work hard to create processes that manage our database in a proactive manner.
Oh, but I thought you all have been arguing that SQL SERVER does everything.
You are doing nothing different than I am doing, creating processes to manage our database.
We are using the tools and capabilities provided by SQL Server. You have to write most of what we are given as part of the cost of SQL Server.
That's where the difference between what you have developed and what we use. MS SQL Server provides more for us to work with than you have implemented so far.
You wanted a discussion between your solution and MS SQL Server. The problem is you still haven't answered any of the questions you have been asked regarding things we depend on SQL Server providing for us to protect and defend the data. For backup/restore for example, you have not answered how you would accomplish a Point in Time restore of your database. How your process would ensure that the database was restored to a consistent state. All you have said is that "we" would have to write the Point in Time restore process. With SQL Server I don't have to write a Point in Time restore process. Based on my backup strategy and whether I was able to accomplish a tail log backup of the database (assuming a hard drive failure took out the mdf file and the ldf file was still accessible) I just need to Identify the full/differential/t-log backups needed to restore the database and run them in the proper order. SQL Server handles rolling forward completed changes and rolling back those transactions that had not been committed at the time of the failure.
I know there are many other types of failures that can occur, and given the funds necessary we can mitigate different types of failure. This could be off-site storage of backups warm/hot off-site backup servers, whatever. There is a cost with every thing we do. If a business says that can't afford to lose any data or have any down time, they better be ready write some pretty big checks, and we aren't talking about just for SQL Server. In fact SQL Server licensing could be the smaller cost in some DR/HA solutions.
September 23, 2015 at 9:22 am
Recovery and restore is very important in the data world. It isn't always needed, but when it is, being able to recover data is very important to most businesses, and DBAs. People do get fired when they can't recover data. However, when disaster strikes, sometimes companies understand something is lost. It's not necessarily a given that if a few hours of work is lost that your job is at risk or the company fails. I wouldn't want to bet on it, but don't assume others wouldn't. Respectfully disagree if this is your issue.
I use to use ZIP drives for backup of these MS-Jet Engine databases.
Perhaps thumb drives can be used today just as easy.
Some even come with their own backup/compression software on the thumb drive.
Last time I checked there were 128 GIG capacity, but it has likely gone to 1 Terabyte by now.
I haven't researched this in a while, so can't tell you the fastest/most stable mode of backup onto removable media. USB has gone from 1.0 to 2.0 to 3.0 and perhaps higher now, and the data transfer speeds increased accordingly.
If daily backup are made, a company is pretty safe from hardware or file system failure or vandelism.
I mentioned using SQL restore logs with restore points in time for JET databases in earlier posts.
This is a new feature I would be implementing in my next database system.
It has also been suggested/recommended by Microsoft or EverythingAccess.com that data dumps to fixed-length or variable-length delimited text files be made from the database file system daily or at intervals during the day. This is helpful in case at some point in time the database is migrated and this would simplify the data migration. And it is an extra safe guard against data corruption of binary MDB files. EverythingAccess.com has special tools and services for MDB data recovery. Please keep in mind that it is MS-Access software working in conjunction with MS-Jet Engine that has been notorious for data file corruption issues. The latest JetComp.exe compact/repair utility (free Microsoft download) can correct most issues like this. VB implementation of ODBC I believe has also been notorious for data corruption issues. Win32 Perl ODBC implementation (by Dave Roth) is what I have used since 1998, and I have never had a data corruption issue. I have also been asked by companies to rewrite their MS-Access Forms-based applications to Win32 Perl native GUI/ODBC application interfaces, using their existing MS-Access database data, because in a Network/Multi-User environment, they experienced daily MDB file corruption and multi-user concurrency issues with MS-Access software.
MS-Access and MS-Jet Engine are not synonymous. Although they have been portrayed that way.
I hope Win32 Perl (ActiveState's ActivePerl for Windows O/S) is not the only stable ODBC implementation.
Surely other programming languages like C or C++ or Java or Delphi etc. have such stable ODBC implementations for the Windows O/S platform.
ODBC was built as an industry standard, although some are moving away from it just like some have moved from EDI to XML data exchange format.
September 23, 2015 at 9:30 am
erichansen1836 (9/23/2015)
Recovery and restore is very important in the data world. It isn't always needed, but when it is, being able to recover data is very important to most businesses, and DBAs. People do get fired when they can't recover data. However, when disaster strikes, sometimes companies understand something is lost. It's not necessarily a given that if a few hours of work is lost that your job is at risk or the company fails. I wouldn't want to bet on it, but don't assume others wouldn't. Respectfully disagree if this is your issue.
I use to use ZIP drives for backup of these MS-Jet Engine databases.
Perhaps thumb drives can be used today just as easy.
Some even come with their own backup/compression software on the thumb drive.
Last time I checked there were 128 GIG capacity, but it has likely goine to 1 Terabyte by now.
I haven't researched this in a while, so can't tell you the fastest/most stable mode of backup onto removable media. USB has gone from 1.0 to 2.0 to 3.0 and perhaps higher now, and the data transfer speeds increased accordingly.
If daily backup are made, a company is pretty safe from hardware or file system failure or vandelism.
I mentioned using SQL restore logs with restore points in time for JET databases in earlier posts.
This is a new feature I would be implementing in my next database system.
It has also been suggested/recommended by Microsoft or EverythingAccess.com that data dumps to fixed-length or variable-length delimited text files be made from the database file system daily or at intervals during the day. This is helpful in case at some point in time the database is migrated and this would simplify the data migration. And it is an extra safe guard against data corruption of binary MDB files. EverythingAccess.com has special tools and services for MDB data recovery. Please keep in mind that it is MS-Access software working in conjunction with MS-Jet Engine that has been notorious for data file corruption issues. The latest JetComp.exe compact/repair utility (free Microsoft download) can correct most issues like this. VB implementation of ODBC I believe has also been notorious for data corruption issues. Win32 Perl ODBC implementation (by Dave Roth) is what I have used since 1998, and I have never had a data corruption issue. I have also been asked by companies to rewrite their MS-Access Forms-based applications to Win32 Perl native GUI/ODBC application interfaces, using their existing MS-Access database data, because in a Network/Multi-User environment, they experienced daily MDB file corruption and concurrency issues with MS-Access software.
MS-Access and MS-Jet Engine are not synonymous. Although they have been portrayed that way.
I hope Win32 Perl (ActiveState's ActivePerl for Windows O/S) is not the only stable ODBC implementation.
Surely other programming languages like C or C++ or Delphi etc. have such ODBC implementations.
... It has also been suggested/recommended by Microsoft or EverythingAccess.com that data dumps to fixed-length or variable-length delimited text files be made from the database file system daily or at intervals during the day. ...
This is not practical for a large database.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 23, 2015 at 9:37 am
erichansen1836 (9/23/2015)
Recovery and restore is very important in the data world. It isn't always needed, but when it is, being able to recover data is very important to most businesses, and DBAs. People do get fired when they can't recover data. However, when disaster strikes, sometimes companies understand something is lost. It's not necessarily a given that if a few hours of work is lost that your job is at risk or the company fails. I wouldn't want to bet on it, but don't assume others wouldn't. Respectfully disagree if this is your issue.
I use to use ZIP drives for backup of these MS-Jet Engine databases.
Perhaps thumb drives can be used today just as easy.
Some even come with their own backup/compression software on the thumb drive.
Last time I checked there were 128 GIG capacity, but it has likely goine to 1 Terabyte by now.
I haven't researched this in a while, so can't tell you the fastest/most stable mode of backup onto removable media. USB has gone from 1.0 to 2.0 to 3.0 and perhaps higher now, and the data transfer speeds increased accordingly.
If daily backup are made, a company is pretty safe from hardware or file system failure or vandelism.
I mentioned using SQL restore logs with restore points in time for JET databases in earlier posts.
This is a new feature I would be implementing in my next database system.
It has also been suggested/recommended by Microsoft or EverythingAccess.com that data dumps to fixed-length or variable-length delimited text files be made from the database file system daily or at intervals during the day. This is helpful in case at some point in time the database is migrated and this would simplify the data migration. And it is an extra safe guard against data corruption of binary MDB files. EverythingAccess.com has special tools and services for MDB data recovery. Please keep in mind that it is MS-Access software working in conjunction with MS-Jet Engine that has been notorious for data file corruption issues. The latest JetComp.exe compact/repair utility (free Microsoft download) can correct most issues like this. VB implementation of ODBC I believe has also been notorious for data corruption issues. Win32 Perl ODBC implementation (by Dave Roth) is what I have used since 1998, and I have never had a data corruption issue. I have also been asked by companies to rewrite their MS-Access Forms-based applications to Win32 Perl native GUI/ODBC application interfaces, using their existing MS-Access database data, because in a Network/Multi-User environment, they experienced daily MDB file corruption and concurrency issues with MS-Access software.
I hope Win32 Perl (ActiveState's ActivePerl for Windows O/S) is not the only stable ODBC implementation.
Surely other programming languages like C or C++ or Delphi etc. have such ODBC implementations.
Interesting that you are only focusing on one aspect of data corruption. Even SQL Server has to deal with file corruption. When it happens it is usually a problem in the IO subsystem. One of the usual suspects there are out of date device drivers. Not something in SQL Server itself. This why there are tools for detecting database corruption provided by SQL Server. Is this something else that you need to write to detect in your solution? If so, more cost involved in the time to design, implement, debug, and support. Again, Microsoft supports this code so we can concentrate on other things of importance to our clients/employers.
September 23, 2015 at 9:44 am
For backup/restore for example, you have not answered how you would accomplish a Point in Time restore of your database.
Yes I have. I mentioned writing out successful comitted SQL maintenance statements (those not ROLLED BACK) to a restore log file (can be a MDB file with no indexing, can be a fixed-length or var-length delimited text file).
I would have {DATE, TIME, USER, PC_NODE_ID, SQL_STATEMENT} as fields in these records.
I would write a utility that could apply all or any of these SQL STATEMENTS to the database which have been submitted since the last backup/restore. With the ability for certain records (SQL STATEMENTS) to be *bypassed and others applied.
* From an exclusion list based upon time of day, user name, and the location of the user (PC NODE ID).
This is not as sophisticated as SQL SERVER, but serves the purpose well.
September 23, 2015 at 10:03 am
Interesting that you are only focusing on one aspect of data corruption. Even SQL Server has to deal with file corruption. When it happens it is usually a problem in the IO subsystem. One of the usual suspects there are out of date device drivers. Not something in SQL Server itself. This why there are tools for detecting database corruption provided by SQL Server. Is this something else that you need to write to detect in your solution? If so, more cost involved in the time to design, implement, debug, and support. Again, Microsoft supports this code so we can concentrate on other things of importance to our clients/employers.
My understanding is that the lastest version of MS-Jet Engine fixes many data file corruption issues on the fly as it is being used. And JetComp.exe utility could correct most of the remainder. But you will not likely see data corruption, as I have not seen data corruption, if you follow certain guidelines.
Wanted to share the below from another developer:
One developer (See below) claims that 99.99% of MS-Jet Engine database corruption is linked to the MS-Access front-end. That is why I use Win32 Perl/ODBC/GUI for front-end. An MDB file with only TABLE objects and no FORMS, REPORTS, etc OBJECTS is quite stable. MS-Jet Engine was designed for a multi-user environment in mind.
FROM http://stackoverflow.com/questions/763888/is-ms-access-jet-suitable-for-multiuser-access
=============================================================
There is so much misinformation in the answers in this thread that I don't know where to start. I just spent 4 points in reputation voting down the answers with misleading and wrong information in them.
the Jet database engine (which is all that's involved here, as the OP clarified with an edit) is by default multi-user -- it was built from the ground up to be that way.
sharing a Jet data store is very reliable when the network is not substandard. This means not a WAN and not wireless, because the bandwidth has to be sufficient for Jet to maintain the LDB file (for multi-user locking), which means a ping by your local PC's instance of the Jet database engine once per second (with default settings), and because Jet can't recover from a dropped connection (which is quite common in a wireless environment).
the situation where Access falls down is when a front-end Access application MDB is shared (which is not the case for this poster). The reason it fails is because you're sharing things that can't be reliably shared and have no reason to be shared. Because of the way Access objects are stored in an MDB file (the entire Access project is stored in a single BLOB field in one record in one of the system tables), it's very prone to corruption if multiple users open it. In my estimation, sharing an Access front end (or an unsplit MDB with the tables and forms/reports/etc. all in one MDB) is the source for 99.99% of corruptions of Access/Jet files.
September 23, 2015 at 10:15 am
Sounds like this would take a lot of programming and testing before it could be considered safe and reliable. So much for your earlier comment saying this would require no manhours.
As CIO, I would consider your proposal for using this as company database rejected.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 181 through 195 (of 245 total)
You must be logged in to reply to this topic. Login to reply