September 24, 2015 at 10:56 am
t seems you find a case that doesn't work and you believe that person over others. Developer Edition allows more than a 10GB database. Perhaps the purchaser didn't install this correctly.
Please don't look at the rumors or notes a few people have reported as being truthful. I'd have to look back at all of your posts, but I know there have been a few things you've put out there with regards to file limitations and scale that are not true. You can't claim this solution works great for things you believe, but be vague for limitations.
Attached is my developer edition. Got a db to 12GB with random data. BOL also shows Developer edition as having all the capabilities of Enterprise. Only the license is different. All the capabilities are the same.
https://msdn.microsoft.com/en-us/library/ms144275.aspx%5B/quote%5D
I never said they are truthful.
I said one purchaser had reported it did not work for him, and I gave a link.
That is not promoting it as truth.
If you want to jump on someone, jump on Wikipedia for the lies they perpetuate about MS-JET saying that you need to upgrade to flagship SQL SERVER for large databases.
I had to make an edit for them to explain that is not so.
https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine
Database connect for Microsoft JET stands for Joint Engine Technology, sometimes being referred to as Microsoft JET Engine or simply Jet. Microsoft Access and Visual Basic use or have used Jet as their underlying database engine. It has since been superseded for general use, however, first by Microsoft Desktop Engine (MSDE), then later by SQL Server Express. For larger database needs, Jet databases can be upgraded (or, in Microsoft parlance, "up-sized") to Microsoft's flagship database product, SQL Server.
However, this does not mean that a MS Jet (Red) database cannot match MS SQL Server in storage capacity. A 5 billion record MS Jet (Red) database with compression and encryption turned on requires about 1 terabyte of disk storage space, comprising hundreds of (*.mdb) files, each acting as partial table, and not as a database in itself.
Over the years, Jet has become almost synonymous with Microsoft Access, to the extent that many people refer to a Jet database as an "Access database".
September 24, 2015 at 11:10 am
erichansen1836 (9/24/2015)
In the below while LOOP, as each SQL INSERT is performed (Not committed yet), I would build my RESTORE log within an array, then if the complete set of INSERTS are COMMITTED, then I would output the array of INSERT statements executed successfully to the RESTORE log FILE. As I build this array, I obtain the date/time and place it within the array along with USER, PC_NODE, and SQL_STATEMENT. This way, the SQL statements are kept in chronological order for use as a RESTORE POINT going forward or between a range such as between 10AM and 1030AM only....
Besides, I like the ALL or NOTHING approach wrapping groups of related updates within an ODBC Transaction.
ALL changes take place, or NONE do.
That may be a safer approach for a database like JET used across a NETWORK.
You reference "would" a lot, so this leads me to think you haven't built this. It may work, but what you've described is a single threaded operation, and potentially a single operation that ignores concurrency. This is fine for some operations, like loading a large amount of data (census, bible, etc).
However it's not necessarily good for multi user scenarios. If you can control locking of singleton records, that's a narrow domain for a problem. Many systems, especially those with external users like a website or application exposed to customers, do not allow for the exclusive locking.
This doesn't mean your approach is wrong, but let's debate that scenario, not try to extend your solution to handle others. SQL Server is a general purpose database, and it could be overkill for the system you've built.
You approach for capturing insert statements in a log for restore is what the transaction log in SQL does. However you are doing a write first, log second, which means an issue in storing your log records could result in incorrect data. If you store the next line of the census in the mdb, and get the acknowledgement in your app, what happens if the network or your machine, or the server fails before you can write a log record? IF you have one client, perhaps you can store it locally. However better practices dictate your log exists on a network share of some sort, separate from the mdb share. What data is correct? The mdb? Or the log? If I go to restore, and I have a discrepancy, how do I decide what's write? How am I sure that what was written to the mdb, which could be a parent but not a child update, is correct?
SQL Server writes the log first, and then the data. That way if we have a failure between, we take the log as the correct value and reapply (or rollback) the changes. Those operations are not necessarily trivial, and while you might implement them in your Perl/ODBC/etc language, you might not do it correctly or have a bug. Especially as your application grows. Perhaps you will, but will another maintainer? There is some value in having well tested, rock solid transactional code that works in SQL Server. The ODBC transactions may or may not work well, depending on what you've included inside of them. Those transactions also typically depend, IIRC, on the underlying engine handling the transaction well. That should be OK for JET or SQL Server, but I'm not sure about your logging mechanism.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 24, 2015 at 11:13 am
You wrote:
"
I checked on this at:
But found one unhappy purchaser who wrote:
"
implying that developer won't work. If you don't see the implication, I think you're not communicating well in English. By stating this without noting it isn't correct, you imply you believe this is an issue.
I can't speak to the value of multiple MDBs functioning as a database or maintaining integrity between them with transactions and application logic, but there are no shortage of instances in the SQL Server/MySQL/Oracle/other RDBMS world where people have had issues because they depended on application logic to maintain data integrity and bugs existed because the programmers did not implement integrity the same and consistently everywhere. PKs and FKs do this 100% of the time.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 24, 2015 at 11:17 am
Jeff Moden (9/22/2015)
Do you actually have a system like what you've been talking about up and running in a production environment?
Probably not any more, as most of those companies have been dissolved.
Metrocall Wireless became USA Mobility after Corporate downsizing and perhaps selling the company.
The Dept I worked in in Dallas, TX was closed after the regional billing system was done away with while still named Metrocall.
Amerwood International, Inc., a wholesale supplier, was sold to one of their out-of-State clients in the finished product Broom/Brush/Mop manufacturing industry.
Control Point Solutions (Telecom expense management) shutdown their Dallas, TX regional office and outsourced all their IT overseas to India.
Zale Corporation (Retail Fine Jewlery) might still be using my MS-JET/ODBC/Perl application?
I mentioned this all in more detail in an earlier post and gave examples of the types of applications, so I won't repeat here.
LYNN PETTIS is going to say that it is because of me that these companies are no longer operating because my MS-JET/ODBC/PERL applications are so bad, and she will argue that they have all moved to SQL SERVER.
You may be right Lynn. Can you prove it with a LINK to an outside source?
Steve Jones will be on your back if you can't prove and show us a LINK of proof and not just a random opinion like Wikipedia has done with their MS-Jet(Red) Engine webpage.
September 24, 2015 at 11:23 am
but there are no shortage of instances in the SQL Server/MySQL/Oracle/other RDBMS world where people have had issues because they depended on application logic to maintain data integrity and bugs existed because the programmers did not implement integrity the same and consistently everywhere.
Steve, you can't see that you are implying that my solution won't work?
Are you also implying that there are not plenty of instances where MS SQL SERVER has failed to deliver?
Can you doument for us where Microsoft or it partners or major IT magazines, etc. have determined that SQL SERVER delivers 100% of the time?
September 24, 2015 at 11:26 am
You reference "would" a lot,
Sorry Steve, that is a typo. I meant all this time to say "wood"
September 24, 2015 at 11:31 am
You reference "would" a lot, so this leads me to think you haven't built this. It may work, but what you've described is a single threaded operation, and potentially a single operation that ignores concurrency. This is fine for some operations, like loading a large amount of data (census, bible, etc).
However it's not necessarily good for multi user scenarios. If you can control locking of singleton records, that's a narrow domain for a problem. Many systems, especially those with external users like a website or application exposed to customers, do not allow for the exclusive locking.
This doesn't mean your approach is wrong, but let's debate that scenario, not try to extend your solution to handle others. SQL Server is a general purpose database, and it could be overkill for the system you've built.
You approach for capturing insert statements in a log for restore is what the transaction log in SQL does. However you are doing a write first, log second, which means an issue in storing your log records could result in incorrect data. If you store the next line of the census in the mdb, and get the acknowledgement in your app, what happens if the network or your machine, or the server fails before you can write a log record? IF you have one client, perhaps you can store it locally. However better practices dictate your log exists on a network share of some sort, separate from the mdb share. What data is correct? The mdb? Or the log? If I go to restore, and I have a discrepancy, how do I decide what's write? How am I sure that what was written to the mdb, which could be a parent but not a child update, is correct?
SQL Server writes the log first, and then the data. That way if we have a failure between, we take the log as the correct value and reapply (or rollback) the changes. Those operations are not necessarily trivial, and while you might implement them in your Perl/ODBC/etc language, you might not do it correctly or have a bug. Especially as your application grows. Perhaps you will, but will another maintainer? There is some value in having well tested, rock solid transactional code that works in SQL Server. The ODBC transactions may or may not work well, depending on what you've included inside of them. Those transactions also typically depend, IIRC, on the underlying engine handling the transaction well. That should be OK for JET or SQL Server, but I'm not sure about your logging mechanism.
Steve said,
You approach for capturing insert statements in a log for restore is what the transaction log in SQL does. However you are doing a write first, log second, which means an issue in storing your log records could result in incorrect data.
Good point.
Lets use MDB files then for the RESTORE LOG as well as the database.
an MDB file (new one daily) with no indexes and no OBJECTS except for one TABLE object.
Include the inserts to the MDB restore log file within the ODBC transaction, so that those
writes are COMMITTED (from memory) to the database at the same time.
What do you think of that idea? any problems forseen?
what you've described is a single threaded operation, and potentially a single operation that ignores concurrency. This is fine for some operations, like loading a large amount of data (census, bible, etc).
However it's not necessarily good for multi user scenarios. If you can control locking of singleton records, that's a narrow domain for a problem.
I have previously stated that all BATCH operations(read or write) occur Server-side during non-business hours, while no users are logged in.
I have also previously stated that I limit the number of records a user updates to a handful such as in a parent/child relationship. In this table/row relationship, a single user locks those records for edit so that no other user can concurrently edit them.
I have my THREADS property set to 512 in my ODBC File DSN. Default is 3. Microsoft recommends bumping to higher value if a MDB file is to receive lots of traffic. I have not taken this to the extreme, but I have run 66 concurrent UPDATE processes to a single MDB file with 100% total accuracy of updates on many test runs. The 66 update processes updated different logical groups of records in the database table so that the updates did not STEP ON each other. This simulates my manual record locking strategy to lock small groups of related records(parent/child) by a single user for update.
And I have run 510 concurrent SQL Query processes concurrently to the same MDB file which produced reliable SQL report output (to external TEXT files) everytime.
This doesn't mean your approach is wrong, but let's debate that scenario, not try to extend your solution to handle others. SQL Server is a general purpose database, and it could be overkill for the system you've built.
I am not retrying to extend my solution to handle others, what many are doing here is trying to extend my solution to what they do with SQL SERVER, and act like I am trying to convince them to turn away from SQL SERVER to something like my solution. There may be folks here evaluating SQL SERVER as a solution for their company. Microsoft gives you this choice: MS-Access Databases to 2 GIG, SQL SERVER Express Databases to 10GIG, SQL SERVER databases to 1000s and 1000s of Terabytes (virtually unlimited).
There appears to be a NICHE for a solution in the 11 GIG to 100s of GIG to perhas several Terabyte RANGE.
SQL SERVER "might" be overkill for many of these midrange databases, but MS-JET could be well suited.
Very well suited for single user databases. Very well suited for READONLY databases.
Well suited for READ/WRITE databases with Low to Moderate concurrent and controlled traffic.
Perhaps doable for READ/WRITE databases with High traffic with lots of controls.
Not suitable for READ/WRITE databases over WAN, wi-fi, or with uncontrolled and concurrent HEAVY traffic.
September 24, 2015 at 12:10 pm
Lynn Pettis (9/24/2015)
patrickmcginnis59 10839 (9/24/2015)
Kristen-173977 (9/23/2015)
patrickmcginnis59 10839 (9/23/2015)
The classroom solution is one user adding stock arrivals, which increase the Quantity on Hand, and the other user doing Sales Order Processing which decrease the Quantity on Hand.
So what do we do when inventory goes negative? Just toss the sales order?
Sorry, wasn't very clear. I am assuming that Quantity on Hand is not allowed to be negative (and there is a constraint which enforces that)
Fair enough. Aside from my objection about negative inventory, you have a really good point.
Transactions have been posted today which honour that constraint - sufficient stock receipts have been processed before any orders that then depleted the stock. Those transactions were logged.
The point I wanted to make was if the Log Playback fails to replay the transactions in exactly the same order we might wind up with a situation where the Orders are processed before the Stock Receipts.
At the end of the run the Quantity on Hand will be the same whatever the sequence, but the constraint not allowing Quantity on Hand to be negative would have kicked in, because the transactions are replayed in a different order, and thus that transaction is
aborted and the final result of the replayed run is thus different to the original.
If he's strictly recording transactions, he can let inventory go south of 0 and be good. If he has realtime needs, then not so much!
On a related note, I wonder if SQL Server's "roll forward" or "roll back" of transactions also has to be single threaded?
Googled it and I think it needs to be:
http://dba.stackexchange.com/questions/5233/is-rollback-a-fast-operation
@JackDouglas pointed to this article which describes one of the situations where rollback can take significantly longer than the original operation. The example being a 14 hour transaction, inevitably using parallelism, that takes 48+ hours to rollback because rollback is mostly single threaded. You would most likely also be churning the buffer pool repeatedly, so no longer are you reversing changes to in-memory pages.
The "this article" url:
Boy, if the OP wants to duplicate functionality like this, he sure does have his work cut out for him 🙂
Comes down to why would I want to re-invent the wheel? Especially by myself. Microsoft has a team of developers work in this stuff.
Yeah that's pretty much the downside of this sort of solution. As much as I like the tech geekiness of thinking through these processes, I personally would be depressed that I'm doing all of this just to eek out some extra tablespace in jet, when most people without the server budgets are out using mysql, postgresql, and if the budgets allow, people are using Microsoft 🙂
I like what the OP is doing, but sadly the economics of it are a downside for me, I would love to solve problems like Kristen has illustrated on a system like this and it would be a real nifty puzzle/project, but the economics and support logistics are an unavoidable downside.
September 24, 2015 at 12:11 pm
You keep talking about 'test runs' and logging that you 'could build'. Four people have asked you whether you had ever built the system you are describing and had a large number of actual users working with it (not simulated runs on a single machine). We're still waiting for that answer.
September 24, 2015 at 12:12 pm
erichansen1836 (9/24/2015)
but there are no shortage of instances in the SQL Server/MySQL/Oracle/other RDBMS world where people have had issues because they depended on application logic to maintain data integrity and bugs existed because the programmers did not implement integrity the same and consistently everywhere.
Steve, you can't see that you are implying that my solution won't work?
Are you also implying that there are not plenty of instances where MS SQL SERVER has failed to deliver?
Can you doument for us where Microsoft or it partners or major IT magazines, etc. have determined that SQL SERVER delivers 100% of the time?
I'm not implying that. I'm pointing out a flaw in your solution that may or may not exist. I didn't imply your implementation has these bugs, but I have no way of knowing, nor do you.
I also noted that this is an issue in SQL Server and other systems where the capabilities are not implemented. I didn't say this would work 100% of the time, but rather plenty of programmers make mistakes in their code, whereas the RDBMS capability has been widely tested and verified.
Please don't take this as an attack on you. I am trying to point out the positives or negatives of using this type of solution.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 24, 2015 at 12:15 pm
erichansen1836 (9/24/2015)
Steve, can you put your question in one sentence or two please.
There is no question, just observations on what you've described.
Lets use MDB files then for the RESTORE LOG as well as the database.
an MDB file (new one daily) with no indexes and no OBJECTS except for one TABLE object.
Include the inserts to the MDB restore log file within the ODBC transaction, so that those
writes are COMMITTED (from memory) to the database at the same time.
What do you think of that idea? any problems forseen?
yes. If I lose the mdb file because of a failure, I've lost the log and data. A fundamental issue for some businesses (maybe most, maybe not). This is what the separation of data and logs (and backups of those) allows for in RDBMS systems, and in many NoSQL implementations.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 24, 2015 at 12:18 pm
You keep talking about 'test runs' and logging that you 'could build'. Four people have asked you whether you had ever built the system you are describing and had a large number of actual users working with it (not simulated runs on a single machine). We're still waiting for that answer.
And then again, I might be wrong ...
David Webb
No you aren't. The answer was given many times.
Do we really have to entertain these same questions over and over which attempt to DILUTE this discussion.
Perahps some folks need to go back through and re-read or read for the first time earlier posts.
This is the last time I will respond to these questions so that I don't also contribute to the discussion being DILUTED.
September 24, 2015 at 12:21 pm
Lets use MDB files then for the RESTORE LOG as well as the database.
an MDB file (new one daily) with no indexes and no OBJECTS except for one TABLE object.
Include the inserts to the MDB restore log file within the ODBC transaction, so that those
writes are COMMITTED (from memory) to the database at the same time.
What do you think of that idea? any problems forseen?
yes. If I lose the mdb file because of a failure, I've lost the log and data. A fundamental issue for some businesses (maybe most, maybe not). This is what the separation of data and logs (and backups of those) allows for in RDBMS systems, and in many NoSQL implementations.
Not sure I undertand? I am separating LOG files and Database files as stated above.
I am not mixing restore logs with database data.
They are kept separate in different MDB files.
It has been stated that 99.99% (not documented as fact) of MDB corruption is due to the MS-Access front-end OBJECTs being stored with the backend database. I only use backend TABLE objects in my MDB files.
Another source of corruption is mixing Jet Engine Versions with a version of the Jet Database files.
ANother source of corruption is mixing ODBC access to the database with MS-Access access.
I limit access to ODBC user-intefaces only.
Use excellent SERVER hardware (have Sysadmin run scheduled integrity checks on the hardware to prevent incidents).
Use a LAN or Peer-to-Peer Network. Hard-wired, no wi-fi.
HAVE A BATTERY POWER SUPPLY BACKUP to prevent interruption of power.
Turn off Opportunistic locking on the Server.
Have lots of quality MEMORY on the Server.
Dedicate the Server to the MS-Jet database alone.
September 24, 2015 at 12:25 pm
There may be folks here evaluating SQL SERVER as a solution for their company.
Microsoft gives you this choice:
MS-Access Databases to 2 GIG (perhaps the latest version has increased to 4 GIG? research),
SQL SERVER Express Databases to 10GIG,
SQL SERVER databases to 1000s and 1000s of Terabytes (virtually unlimited).
There appears to be a NICHE for a solution in the 11 GIG to 100s of GIG to perhaps several Terabyte RANGE.
SQL SERVER "might" be overkill for many of these midrange databases, but MS-JET could be well suited.
Very well suited for single user READ/WRITE databases.
Very well suited for multi-user READONLY databases.
Well suited for READ/WRITE databases with Low to Moderate concurrent and controlled traffic.
Note: MS-Jet Engine was designed for this
Not suitable for READ/WRITE databases over WAN, wi-fi, or with uncontrolled and concurrent HEAVY traffic.
September 24, 2015 at 1:09 pm
erichansen1836 (9/24/2015)
I would not use Table CONTRAINTS (other than INDEXES) because that might interfere with my manual logic to enforce referential integrity in some way?
Fair enough, but I would not want to build an APP without server-enforced constraints. I don't rely on them, but they are there as a safety net.
We never use cascading deletes, we require the user to indicate if they specifically want to delete all the child records belonging to a parent, and if they do that our code will explicitly delete children-first-then-parent.. (In places we require the user to delete them, that makes it a very deliberate action, rather than an out-of-sight out-of-mind one!)
So lets assume that, in MY App, the user is viewing an Order and can see the Header (customer name/address, order date etc) and several line items on the order. Let assume they have to tick checkboxes against every line of the order in order to remove the lines and THEN they can delete the order header.
If they attempt to delete the order header, whilst it still has line items, MY App will give them a nice friendly message explaining what they have to do.
That is the way it is intended to work, and it will work just fine without any Constraints being set up in the Database and, I think :-D, it provides a nice friendly interface for the user, allowing them to delete records trivially-easily where that is appropriate, but to have to take extreme care in the places where such deletions are rare or have dire consequences.
But there are, sadly, bugs in my code. I absolutely and without question also want Database Constraints so that if I goof up and fail to catch that situation, and display a nice friendly warning, at worse the user's session will abort with a cryptic system error message ... but at least I won't have wrecked referential integrity and created a bunch of orphaned Order Items.
If we get a referential integrity error, raised by the server due to a constraint violation, in a production system, there would be a major post mortem as to how that code got into production with that flaw. But at least it would have failed safe.
But in both DEV and QA Testing it would be highly likely that we would hit constraint violations, caught by the server, because of bugs in our not-yet-fully-debugged code. That too is an excellent thing IMHO and enables us to discover that our code is not working as intended.
Thus I cannot conceive of a situation where I would rely solely on my APP programmed-in constraint checks without ALSO having server/database-level ones as a safety net.
Viewing 15 posts - 226 through 240 (of 245 total)
You must be logged in to reply to this topic. Login to reply