September 24, 2015 at 1:22 pm
erichansen1836 (9/24/2015)
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.
How does this work? Are you saying that you have an mdb file that has a table for the data, say the 2015 Texas census, and then a separate mdb file that has a table that logs the inserts?
Is there transactional capability across both these mdbs? I'm asking as I don't know. If there is, then this may cover the logging. If there isn't, then you still potentially have a write occurring in one mdb/table and not getting written to the second, which causes a resolution error for disaster restores.
We can stop talking about Access. You've made it clear that you don't use Access, which is fine.
We can stop talking about corruption here in terms of the files, as both SQL Server and Jet can have corruption. I don't know what precautions JET contains. SQL has some, but they're not extensive. The backup and restore with the data and log files can help, but it's not necessarily foolproof.
Everyone will use server grade hardware. That's a given. However hardware fails.
With regard to the network, limiting someone to not using wi-fi is a deal breaker for some situations, but not for others. Certainly in your Zales client, that might not matter. In some companies I've worked, it's reasonable restriction. In others, it's not. However, let's cease to discuss that. If you require wi-fi, then we might agree this isn't a good architecture.
I think this is a good summary, though there seem to be other restrictions worth debating.
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.
Certainly the lack of constraints is an issue for me in most systems. Application developers make mistakes, especially as they enhance systems and cut/paste code. Server side constraints help protect here.
September 24, 2015 at 1:29 pm
erichansen1836 (9/24/2015)
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.
Really??? I would never state something like that without any proof. You really have no idea about who I am and I take serious offense that you would even say that in a public forum. It could even be considered liable or slander. Oh, and by the way, I am a he, not a she.
September 24, 2015 at 2:03 pm
EricHansen,
What you're proposing is edgy, and perhaps you're earnestly advocating this use of MS Jet database, because that's the database and set of API you're most familiar with. However, it makes me think that perhaps another DBMS, something specifically designed for peer-to-peer distribution of shards across nodes (in your case user workstations), where each node assumes role of both data and compute node. That would perhaps work better than a collection of Jet database files sitting on a file share. I'm thinking maybe Cassandra. Did you consider any other DBMS besides SQL Server and Jet ?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 25, 2015 at 10:40 am
How does this work? Are you saying that you have an mdb file that has a table for the data, say the 2015 Texas census, and then a separate mdb file that has a table that logs the inserts?
Is there transactional capability across both these mdbs? I'm asking as I don't know. If there is, then this may cover the logging. If there isn't, then you still potentially have a write occurring in one mdb/table and not getting written to the second, which causes a resolution error for disaster restores.
Steve, yes, I am keeping the database data in MDB files separate from the restore logs.
US_CENSUS_2010_TX_A.mdb DATA
US_CENSUS_2010_TX_A_log.mdb Restore log (new: daily, weekly, etc, depending on traffic)
Transactional capability should extend to multiple MDB files because I can have unlimited (Only by memory) ODBC connections open concurrently to any Windows Servers and their MDB files, containing both common and uncommon Tables. EXAMPLE:
use Win32;
use Win32::ODBC;
$PWD=Win32::GetCwd(); #-- get current working directory location
#-- Or you could reference a Network Share Folder
#-- Create/Open Two(2) Database Connection Objects
$db = new Win32::ODBC("FILEDSN=$PWD\\CensusDB.dsn; DBQ=$PWD\\Census_TX_A.mdb");
if (! $db) {
print "Census Database Not Opened";
$error=Win32::ODBC::Error();
print $error; die;
}
$db2 = new Win32::ODBC("FILEDSN=$PWD\\CensusDB.dsn; DBQ=$PWD\\Census_TX_A_log.mdb");
if (! $db2) {
print "Census Database Restore Log Not Opened";
$error=Win32::ODBC::Error();
print $error; die;
}
#-- turn off automatic SQL write commits to the database. We will do them manually.
$db->SetConnectOption($db->SQL_AUTOCOMMIT,$db->SQL_AUTOCOMMIT_OFF);
$db2->SetConnectOption($db2->SQL_AUTOCOMMIT,$db2->SQL_AUTOCOMMIT_OFF);
$sqltxt= #-- set this to an SQL maintenance statement
$sqltxt2= #-- set this to an SQL maintenance statement
$ret = $db->Sql($sqltxt);
$ret2 = $db2->Sql($sqltxt2);
if ($ret || $ret2) {
$db->Transact($db->SQL_ROLLBACK); $db2->Transact($db2->SQL_ROLLBACK);
} else {
$db->Transact($db->SQL_COMMIT); $db2->Transact($db2->SQL_COMMIT);
}
$db->Close(); undef $db;
$db2->Close(); undef $db2;
exit;
#-- this subroutine is always performed regardless of normal or abnormal exit
END {
if ($db) {
$db->Close(); #-- ensures database connection is closed upon exit or forced exit
undef $db; #-- free up memory
}
if ($db2) {
$db2->Close(); #-- ensures database connection is closed upon exit or forced exit
undef $db2; #-- free up memory
}
}
I See perhaps your point now, that I am actually performing 2 separate COMMITS, 1 to 1 MDB file, and
another to the other MDB file. DO you still see this as a significant problem the way I show it coded?
This is not for: Network Servers with poor hardware, WAN's, wi-fi's, Servers without Backup power, etc.
Hard Wired LANs only. Regular hardware integrity checks by SysAdmin, etc.
September 25, 2015 at 11:07 am
You all have asked for JetComp.exe utility execution times.
Here they are. My own personal testing today.
For a 10.1 Million row MDB file, Jet 4.x format (unicode support), which has both text Compression,
and Encryption turned on (the least efficient combination for speed because of the translation that has to take place), the time to compact/repair/reorganize/re-optimize (which the utility does) takes 5+ minutes.
I then copied this MDB file to 5 other MDB files, changing the names.
Now I have 5 files containing 50.5 million rows.
I then ran 2 tests.
(1) I ran 5 simultaneous instances of the JetComp.exe utility to perform the operations concurrently on
all 5 MDB files. It was inefficient this way taking 1 Hour.
(2) I ran 5 sequential instances of the JetComp.exe utility to perform the operations 1 after the other on
all 5 MDB files. It took 30 minutes.
Option 2 is the way to go for batch operations on multiple MDB files making up your database.
JetComp.exe uses much memory, but little CPU resources. This likely explains the poor performance with concurrent processing.
These tests were performed on a modest Laptop, single Intel Celeron processor, 2.2GHz, 3 GIG RAM,
Windows 7 Home Premium O/S.
Extrapolating this out to a 1 BILLION ROW, 200 GIG, 100 MDB file database, it would take
30 minutes X 20 = 10 HOURS. This is on my modest LapTop.
Any guess at the process time on a robust Server with lots of Memory?
I use Perl for my job control language, as well as for COM automation, ODBC, Native Windows GUI development, etc.
MS-DOS batch file *.bat could be another way to run this.
Below is the code I ran:
trial (1) without $POBJ->Wait(INFINITE); i.e. concurrent processing - inefficient in 60 Min.
trial (2) with $POBJ->Wait(INFINITE); i.e. sequential processing - efficient in 30 Min.
use Win32;
use Win32::Process;
use Time::localtime;
$now = ctime();
print "Started at " . $now . "";
$PWD=Win32::GetCwd();
$from="$PWD\\bible4x_1.mdb";
$to="$PWD\\bible4X_1a.mdb";
Win32::Process::Create($POBJ,"$PWD\\jetcomp40.exe","jetcomp40 -src:\"$from\" -dest:\"$to\"",0,DETACHED_PROCESS,".");
$POBJ->Wait(INFINITE);
$from="$PWD\\bible4x_2.mdb";
$to="$PWD\\bible4X_2a.mdb";
Win32::Process::Create($POBJ,"$PWD\\jetcomp40.exe","jetcomp40 -src:\"$from\" -dest:\"$to\"",0,DETACHED_PROCESS,".");
$POBJ->Wait(INFINITE);
$from="$PWD\\bible4x_3.mdb";
$to="$PWD\\bible4X_3a.mdb";
Win32::Process::Create($POBJ,"$PWD\\jetcomp40.exe","jetcomp40 -src:\"$from\" -dest:\"$to\"",0,DETACHED_PROCESS,".");
$POBJ->Wait(INFINITE);
$from="$PWD\\bible4x_4.mdb";
$to="$PWD\\bible4X_4a.mdb";
Win32::Process::Create($POBJ,"$PWD\\jetcomp40.exe","jetcomp40 -src:\"$from\" -dest:\"$to\"",0,DETACHED_PROCESS,".");
$POBJ->Wait(INFINITE);
$from="$PWD\\bible4x_5.mdb";
$to="$PWD\\bible4X_5a.mdb";
Win32::Process::Create($POBJ,"$PWD\\jetcomp40.exe","jetcomp40 -src:\"$from\" -dest:\"$to\"",0,DETACHED_PROCESS,".");
$POBJ->Wait(INFINITE);
$now = ctime();
print "Ended at " . $now . "";
exit;
September 25, 2015 at 12:13 pm
erichansen1836 (9/25/2015)
I See perhaps your point now, that I am actually performing 2 separate COMMITS, 1 to 1 MDB file, andanother to the other MDB file. DO you still see this as a significant problem the way I show it coded?
I can't tell. Honestly I don't have enough knowledge of ODBC transaction implementation. In SQL, I'd need the DTC to ensure transactions worked across servers. The .NET transactions also work across systems, and I'd assume that OdBC has a good implementation here and it is possible.
As to your code. Someone with more knowledge would have to examine the code and verify it works.
Viewing 6 posts - 241 through 245 (of 245 total)
You must be logged in to reply to this topic. Login to reply