September 8, 2015 at 12:51 pm
MS-Jet Engine 4.x ODBC enabled, NO SQL, database file systems to 1 Terabyte/5 billion rows
(or) SQL SERVER?
What reasons can SQLServerCentral give for downsizing to a MS-SQL Server database
from a 1 Terabyte/5 Billion row, MS-Jet Engine 4.x format, ODBC enabled, NO SQL, database file system (*.MDB), which uses ActiveState Win32 Perl for Native Windows GUI & ODBC for user-interfacing?
Perl Win32::ODBC module by David Roth is an extremely stable ODBC implementation.
No MS-Access software or licensing is required. Database files are created with Windows 7 Home Premium ODBC Administrator, and the tables, views, indexes, constrants created with ODBC/SQL commands from Perl. File reorganization is accomplished by the JetComp.exe standalone batch utiilty for index recovery. I have never had to use it since Perl ODBC never corrupts the *.MDB files that I have ever experienced on a Network.
Thanks,
Eric in Dallas, Texas
September 8, 2015 at 4:31 pm
Are you asking a question here? Do you want to know why move from the Jet database to SQL Server?
Your post isn't phrased well.
September 9, 2015 at 1:42 pm
I stated to provide reasons for downsizing to SQL Server from Jet Engine 4.x.
Since Jet Engine 4.x is the more powerful of the two from what I can gather,
what advantages can be gained to downsize to SQL Server over continued use of MS-Jet Engine 4.x?
Perhaps you don't understand because you believe it is an upsize to go from
Jet Engine 4.x to SQL Server. That is a fallicy perpetuated by Microsoft Marketing to promote their flagship software SQL Server, which Microsoft wishes to sell to companies.
I have a FREE/NO COST, 1 Terabyte 5 Billion row Jet Engine 4.x Database (Compression and Encryption turned on) that has no practical limit on multi-user concurrency. And I never have had to use the JetComp.exe file recovery tool because I have not experienced crashes occurring on a Network, unlike MS-Access software which requires frequent file recovery, and limits the database size to 2 GIG treating an *.MDB file as a database in and of itself instead of as a partial table in a 500 *.MDB file system. Jet Engine is absolutely FREE and requires no end-user licenses and you don't need MS-Access software for anything. ODBC Administrator on Windows 7 offers ability to create empty database files, and SQL/ODBC commands can be used with Win32 PERL (or other language) to create tables, view, indexes, constrants, etc.
Granted, yes, you should make your MS-Jet Engine 4.x 5 Billion row database a NO SQL database accessed only through an ODBC-enabled user front-end that controls open database connections and directs user SQL requests to the appropriate *.MDB indexed file, building the dynamic SQL statements (from user input) on the end-user's behalf. But that programming is easy to implement and inforce on a Network directory setup with the appropriate group permissions to prevent COPY, DELETE, MOVE, etc. User Permissions for Insert, Update, Delete, Read can be inforced by providing each user an ACCESS LEVEL stored in a *.MDB files used for that purpose. Yes there is a bit of manual programming involved, but once it is done, it can be cloned to any database implementation you want. It is not worth paying Microsoft to give you that functionality when you can do it yourself.
September 9, 2015 at 2:04 pm
erichansen1836 (9/9/2015)
... Since Jet Engine 4.x is the more powerful of the two ...
Huh? I hope you're thinking about a different Jet than I'm thinking about.
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 9, 2015 at 2:12 pm
The Jet I am referring to is the Jet Engine used by Microsoft Access 2002-2003. Jet 4.x
You don't need MS-ACCESS.
I refer to this database technology as
"REDUCTION DATABASE TECHNOLOGY" because you take MS-ACCESS software out of the picture.
Jet Engine databases can be 5 Billion Rows/1 Terabyte with instantaneous random access to any row.
September 9, 2015 at 2:16 pm
erichansen1836 (9/9/2015)
The Jet I am referring to is the Jet Engine used by Microsoft Access 2002-2003. Jet 4.xYou don't need MS-ACCESS.
I refer to this database technology as
"REDUCTION DATABASE TECHNOLOGY" because you take MS-ACCESS software out of the picture.
Jet Engine databases can be 5 Billion Rows/1 Terabyte with instantaneous random access to any row.
Fine, but where do you get the idea that going from JET to SQL Server is "downsizing"?
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 9, 2015 at 2:19 pm
I consider it downsizing to go to SQL Server from Jet Engine because my understanding is that SQL Server cannot match Jet Engine for size and speed. Can SQL Server provide instantaneous random access lookup to any one of 5 Billion database rows? Can SQL Server contain 5 Billion rows (MEMO field included) within 1 Terabyte of disk space?
September 9, 2015 at 2:22 pm
erichansen1836 (9/9/2015)
I consider it downsizing to go to SQL Server from Jet Engine because my understanding is that SQL Server cannot match Jet Engine for size and speed. Can SQL Server provide instantaneous random access lookup to any one of 5 Billion database rows? Can SQL Server contain 5 Billion rows (MEMO field included) within 1 Terabyte of disk space?
SQL Server (not SQL Server Express) can actually support peta size databases.
September 9, 2015 at 2:43 pm
Yes I stand corrected. It does appear that SQL Server has grown in recent years from 1 Terabyte to 1/2 Petabyte capacity.
SQL Server Express has a 10 GIG limit still I believe.
I will then venture to say that for databases under 1 Terabyte (data only, no pictures or videos),
it is strictly user preference to decide to use a Jet Engine database file system or whether to use an SQL SERVER database. And Jet Engine databases are likely to be much more cost effective to implement since the software is free, no end user licenses are required, and DB Administration can be done by modestly trained staff.
I read that most corporate databases (data only, no pics or videos) frequently run in the 10s of Gigabytes.
That is a size easily handled by Jet Engine Database System architecture.
Not everyone is Server style oriented. Somefolks are File System oriented.
I come from a Microsoft PC COBOL and INFORMIX Standard Engine/SCO UNIX background, so database files systems make the most sense to me, plus I can administer them myself, thus I chose Jet Engine file system databases.
Jet Engine is a viable option not to ignore for mid-range database architecture.
September 9, 2015 at 3:16 pm
erichansen1836 (9/9/2015)
Yes I stand corrected. It does appear that SQL Server has grown in recent years from 1 Terabyte to 1/2 Petabyte capacity.SQL Server Express has a 10 GIG limit still I believe.
I will then venture to say that for databases under 1 Terabyte (data only, no pictures or videos),
it is strictly user preference to decide to use a Jet Engine database file system or whether to use an SQL SERVER database. And Jet Engine databases are likely to be much more cost effective to implement since the software is free, no end user licenses are required, and DB Administration can be done by modestly trained staff.
I read that most corporate databases (data only, no pics or videos) frequently run in the 10s of Gigabytes.
That is a size easily handled by Jet Engine Database System architecture.
Not everyone is Server style oriented. Somefolks are File System oriented.
I come from a Microsoft PC COBOL and INFORMIX Standard Engine/SCO UNIX background, so database files systems make the most sense to me, plus I can administer them myself, thus I chose Jet Engine file system databases.
Jet Engine is a viable option not to ignore for mid-range database architecture.
I'm going to say this, "It depends."
September 9, 2015 at 3:20 pm
Lynn Pettis (9/9/2015)
erichansen1836 (9/9/2015)
Yes I stand corrected. It does appear that SQL Server has grown in recent years from 1 Terabyte to 1/2 Petabyte capacity.SQL Server Express has a 10 GIG limit still I believe.
I will then venture to say that for databases under 1 Terabyte (data only, no pictures or videos),
it is strictly user preference to decide to use a Jet Engine database file system or whether to use an SQL SERVER database. And Jet Engine databases are likely to be much more cost effective to implement since the software is free, no end user licenses are required, and DB Administration can be done by modestly trained staff.
I read that most corporate databases (data only, no pics or videos) frequently run in the 10s of Gigabytes.
That is a size easily handled by Jet Engine Database System architecture.
Not everyone is Server style oriented. Somefolks are File System oriented.
I come from a Microsoft PC COBOL and INFORMIX Standard Engine/SCO UNIX background, so database files systems make the most sense to me, plus I can administer them myself, thus I chose Jet Engine file system databases.
Jet Engine is a viable option not to ignore for mid-range database architecture.
I'm going to say this, "It depends."
Agree.
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 10, 2015 at 1:15 pm
For discussion purposes, so that both myself and others can gain an understanding of the potential limitations of Jet Engine file system databases vs. SQL Server databases for midrange, Live databases up to 1 Terabyte (5 billion rows), please continue with more detail on what you view as the stronger database for the situations you see i.e. "it depends".
September 10, 2015 at 1:33 pm
for other readers....seems a similar discussion is also here
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 10, 2015 at 2:23 pm
I'll add my $0.02, probably worth about that.
File based systems work fine. Plenty of people have used them for a long time. However, there are caveats and potential issues. The value certainly depends in some case on the work put into the application. SQL Server is a "file based" system that proxies the access to files for the users.
The work you do to maintain integrity and quality in the application doesn't have to be handled by the database engine. However, you do the work. If you want a large scale (hundreds of users) , relational system, I do not think the Jet Engine provides enough by itself. Can you make it work? Sure. Just takes dev time.
Jet is a powerful database. It powered Exchange for years, and still may. It certainly could be used as a database. SQL Server is a powerful database, built to act as a server, with tables that are related and tied together within it.
If you don't need SQL Server, don't use it. It's expensive.
September 10, 2015 at 2:59 pm
Thanks for the link to my post at Microsoft Developers Forum regarding Jet (Red) Engine Databases.
Also, FYI, I have issued 510 concurrent independant SQL processes on Windows 7 Home Premium on a modest Laptop with 3 GIG Ram and 1 Processor, launching these as "Detached" Windows Background Processes from within a Win32 Perl script. I used Task Manager to monitor the processing, RAM Memory, and CPU usage.
All SQL report output went to individual TEXT files. All SQL report output consistently worked beautifully as long as I increased the THREADS property within my ODBC FILE DSN from default of 3 to 512. I was also able to run 66 concurrent database update processes to the same *.MDB file, each of the 66 updating a separate logical collection of records in the database file so as not to STEP ON each others udpates. In a user-interface, I would write the record locking strategy myself. If a record is wished to be edited by a user, the user-interface only allows it if the record is not currently locked for edit by another user. I would manually program the locking/unlocking instead of repling on the Jet Engine. This is how I have done it for a couple companies.
BATCH UPDATES can be done Server Side during off hours and by Operations staff whereby record locking would not be an issue.
Now that is one Jet Engine working on 1 MDB database file system file.
If you implement a Network database file system of 500 MDB files, each acting as a partial table within the 5 billion row database, each MDB file containing 10 million rows...
And if you implement dozens or 100s of Jet Engines, i.e. 1 Jet Engine from each PC on the Network, accessing the Network Database file system...
And if you only allow SQL access (i.e. NO SQL) from within a user-interface front-end which controls the ODBC connections, opening them only long enough to retrieve a row(s) or uddate/delete/insert row(s), and which dynamically builds and executes the SQL statements on the end-user's behalf (from keyboard input or selection criteria chosen from the database)...
Then it may be possible for 1000s of end-users to randomly access the 500 *.MDB database system files on the Network without any concurrency issues arising.
Viewing 15 posts - 1 through 15 (of 245 total)
You must be logged in to reply to this topic. Login to reply