Which database should I use?

  • Thread starter Thread starter HKSHK
  • Start date Start date
H

HKSHK

Hi guys,

I want to write a database application, which I later want to sell. Now
I am looking for a nice, little database which would have to fullfil
these requirements:

* Either free or low cost
* Support encryption
* run via network or locally
* low CPU usage (I don't need a big web server database)
* run on Windows 98 and later
* Allow several users to access the db at the same time
* reliable if 2 or more users want to access the same record at the
same time

I don't want to use MS Access, because it is not very reliable if
multiple user access the same record.

I was thinking about using Firebird, but it doesn't support encryption,
so the data are not really safe.

Do you have any ideas?

Thank you in advance,

HKSHK
 
What reliability issues have you faced in MS Access? I have used MS Access
for many of my commercial applications. Like you I too market my products
widely and I have found that NOTHING beats Access for the particular
requirements that you have mentioned.

Do you really want to use an alternative database technology and not get all
the benefits of wide-usage and tools that Jet offers.

Most reliability issues that people report can be solved through
workarounds, so do report the issues, maybe there's a solution.

Regards
Cyril Gupta
 
I am with Cyril ,,


if you need the described functionality Access stands all alone out there ,
i have not yet found a good alternative ( although i would prefer a RDBMS
as i use databases of gigabytes in size ) however they all miss the security
thingy


about the multi user problem ,,,,, well you can tackle this with your
program design


regards

Michel Posseth [MCP]
 
I agree, the Jet engine is a great little engine, and highly reliable. It's
only weak point is on security. Everyone with MS-Access can basically enter
and alter the database. That password protection thingy that they offer is a
bit on the weak side.

However, if you like free and reliable and with more security options, I'd
go for MSDE (SQL Server Desktop Edition, or I believe these days it's called
SQL Server 2005 Express). It is free and downloadable from www.microsoft.com

hth,
Martin


m.posseth said:
I am with Cyril ,,


if you need the described functionality Access stands all alone out there
, i have not yet found a good alternative ( although i would prefer a
RDBMS as i use databases of gigabytes in size ) however they all miss the
security thingy


about the multi user problem ,,,,, well you can tackle this with your
program design


regards

Michel Posseth [MCP]


Cyril Gupta said:
What reliability issues have you faced in MS Access? I have used MS
Access for many of my commercial applications. Like you I too market my
products widely and I have found that NOTHING beats Access for the
particular requirements that you have mentioned.

Do you really want to use an alternative database technology and not get
all the benefits of wide-usage and tools that Jet offers.

Most reliability issues that people report can be solved through
workarounds, so do report the issues, maybe there's a solution.

Regards
Cyril Gupta
Click to expand...
Click to expand...
 
Ahum sorry but you are wrong :-)

I wish MSDE or SQL Express could even come close to the security that
Access has with the combination Workgroup information file
Everyone with MS-Access can basically enter
and alter the database. That password protection thingy that they offer is
a bit on the weak side.
Click to expand...

That is exactly what is possible with SQL server and for a fact with all
other RDBMS systems but not with Access in combination with a Workgroup
information file ( this is not the same as the easy to break password
protected database )

if you ship a sql mdf file everyone with a Enterprise Manager or SMS
installed and administrator rights on his system can access the database


if you want to take up the challenge i can send you a workgroup protected
database of mine and you can see for yourself :-)

regards

Michel Posseth [MCP]





Martin said:
I agree, the Jet engine is a great little engine, and highly reliable. It's
only weak point is on security. Everyone with MS-Access can basically enter
and alter the database. That password protection thingy that they offer is
a bit on the weak side.

However, if you like free and reliable and with more security options, I'd
go for MSDE (SQL Server Desktop Edition, or I believe these days it's
called SQL Server 2005 Express). It is free and downloadable from
www.microsoft.com

hth,
Martin


m.posseth said:
I am with Cyril ,,


if you need the described functionality Access stands all alone out there
, i have not yet found a good alternative ( although i would prefer a
RDBMS as i use databases of gigabytes in size ) however they all miss the
security thingy


about the multi user problem ,,,,, well you can tackle this with your
program design


regards

Michel Posseth [MCP]


Cyril Gupta said:
What reliability issues have you faced in MS Access? I have used MS
Access for many of my commercial applications. Like you I too market my
products widely and I have found that NOTHING beats Access for the
particular requirements that you have mentioned.

Do you really want to use an alternative database technology and not get
all the benefits of wide-usage and tools that Jet offers.

Most reliability issues that people report can be solved through
workarounds, so do report the issues, maybe there's a solution.

Regards
Cyril Gupta
Click to expand...
Click to expand...
Click to expand...
 
This isn't an issue if you encypt the data a you should.

m.posseth said:
Ahum sorry but you are wrong :-)

I wish MSDE or SQL Express could even come close to the security that
Access has with the combination Workgroup information file
Everyone with MS-Access can basically enter
and alter the database. That password protection thingy that they offer
is a bit on the weak side.
Click to expand...

That is exactly what is possible with SQL server and for a fact with all
other RDBMS systems but not with Access in combination with a Workgroup
information file ( this is not the same as the easy to break password
protected database )

if you ship a sql mdf file everyone with a Enterprise Manager or SMS
installed and administrator rights on his system can access the database


if you want to take up the challenge i can send you a workgroup protected
database of mine and you can see for yourself :-)

regards

Michel Posseth [MCP]





Martin said:
I agree, the Jet engine is a great little engine, and highly reliable.
It's only weak point is on security. Everyone with MS-Access can basically
enter and alter the database. That password protection thingy that they
offer is a bit on the weak side.

However, if you like free and reliable and with more security options,
I'd go for MSDE (SQL Server Desktop Edition, or I believe these days it's
called SQL Server 2005 Express). It is free and downloadable from
www.microsoft.com

hth,
Martin


m.posseth said:
I am with Cyril ,,


if you need the described functionality Access stands all alone out
there , i have not yet found a good alternative ( although i would
prefer a RDBMS as i use databases of gigabytes in size ) however they
all miss the security thingy


about the multi user problem ,,,,, well you can tackle this with your
program design


regards

Michel Posseth [MCP]


What reliability issues have you faced in MS Access? I have used MS
Access for many of my commercial applications. Like you I too market my
products widely and I have found that NOTHING beats Access for the
particular requirements that you have mentioned.

Do you really want to use an alternative database technology and not
get all the benefits of wide-usage and tools that Jet offers.

Most reliability issues that people report can be solved through
workarounds, so do report the issues, maybe there's a solution.

Regards
Cyril Gupta
Click to expand...
Click to expand...
Click to expand...
 
Huh ???

As you should ??? you know what should they should have taken care of
programmers like me who have valuable data
The aproach of all RDMS systems is that the user that owns the system is
also the owner of the data wich is not always the case

If i should implement encryption in my database ( as it is the standard
acording to your comment ) why isn`t this feature there ??? my companny
pays a lot for SQL server ( 2000 enterprise and 2005 ) and even on this
versions it isn`t even there , if i SHOULD do that the tools SHOULD be
there and they are not , in ACCESS they are there and i can do it even
better without anny noticable performance lost ( my proggy`s run on win 98
on a PII 300 MHZ without problems )


The only way i can protect my sql data is by implementing custom encryption
,,,, and even this is more suitable for storing for instance credit card
info etc etc what if you have the situation where you query on hughe
datasets ? of protected data do you have a solution for this to ??

please tell me i am curious as i hear a lot of bla from people that can`t
inmagine the problem

to give you an idea :

Yes i know SQL server verry well ( i use it daily in a corporate
environment )
Yes i wish i could use sql server in my deployable apps
look at this website http://www.bildelskatalogen.se/

this is one of the products i make , now inmagine that the customers pay for
the data relations and NOT for the program we have people in our company
that mine this data from OE manufacturers
the data is the value of the product in the web application everything is
fine as it is running on our companny`s SQL server

We also ship catalogue programs to end users ( currently written in VB6 icw
ACCESS with a workgroup file ) we are now bussy writing a BLS in VS.net
2005 we investigated the possibility`s and untill so far nothing beats
Access in terms of performance in combination with security ( and then i
did not even mention it`s small footprint and the ease of deployability
wich i would trade for more performance )


regards

Michel Posseth [MCP]





Jim said:
This isn't an issue if you encypt the data a you should.

m.posseth said:
Ahum sorry but you are wrong :-)

I wish MSDE or SQL Express could even come close to the security that
Access has with the combination Workgroup information file
Everyone with MS-Access can basically enter
and alter the database. That password protection thingy that they offer
is a bit on the weak side.
Click to expand...

That is exactly what is possible with SQL server and for a fact with all
other RDBMS systems but not with Access in combination with a Workgroup
information file ( this is not the same as the easy to break password
protected database )

if you ship a sql mdf file everyone with a Enterprise Manager or SMS
installed and administrator rights on his system can access the database


if you want to take up the challenge i can send you a workgroup protected
database of mine and you can see for yourself :-)

regards

Michel Posseth [MCP]





Martin said:
I agree, the Jet engine is a great little engine, and highly reliable.
It's only weak point is on security. Everyone with MS-Access can
basically enter and alter the database. That password protection thingy
that they offer is a bit on the weak side.

However, if you like free and reliable and with more security options,
I'd go for MSDE (SQL Server Desktop Edition, or I believe these days
it's called SQL Server 2005 Express). It is free and downloadable from
www.microsoft.com

hth,
Martin


I am with Cyril ,,


if you need the described functionality Access stands all alone out
there , i have not yet found a good alternative ( although i would
prefer a RDBMS as i use databases of gigabytes in size ) however they
all miss the security thingy


about the multi user problem ,,,,, well you can tackle this with your
program design


regards

Michel Posseth [MCP]


What reliability issues have you faced in MS Access? I have used MS
Access for many of my commercial applications. Like you I too market
my products widely and I have found that NOTHING beats Access for the
particular requirements that you have mentioned.

Do you really want to use an alternative database technology and not
get all the benefits of wide-usage and tools that Jet offers.

Most reliability issues that people report can be solved through
workarounds, so do report the issues, maybe there's a solution.

Regards
Cyril Gupta
Click to expand...
Click to expand...
Click to expand...
 
Hello

here are some links to get you started

http://office.microsoft.com/en-us/assistance/HA010546941033.aspx

this one is verry good step by step with pictures ( also note the links that
show how to implement user level security )
http://www.databasedev.co.uk/access_security.html

the big trick is to create a new Administrator user and throw out the
default accounts

now nobody can access the database unless they know what is the workgroup
file ( i rename it ) and the username and password
so the security is in multiple layers ( knowing that the DB is secured by
another workgroup , discovering what is the workgroup file , and finding the
username and password )

oh by the way

if you google on the subject you will find several people claiming they can
extract the password form the workgroup file well we have also implemented
database encryption in the workgroup file ( with a key you must assign
yourself )

we bought several of these tools and they all failed ( we did not get our
monney back ;-)

if you need more help ,,, for instance how to connect to it from code etc
etc feel free to ask


regards

Michel Posseth [MCP]
 
I was interested in your comment about "workarounds" for Access Reliability.
I assume you are referring to simultaneous access to a record. Do you have
any suggestions for what "workarounds" are needed. I was assuming that users
had simultaneious read access and that when writing to the database that the
write would wait for anyother writes to finish. Looks like I may have been
wrong.
 
Hmm... No, you aren't wrong Dennis. You can actually lock the database as
per your requirement if you want. If you explain your problem specifically I
might be in a position to give you possible leads to the solution.

Regards
Cyril Gupta
 
Dennis


Split the database when you come near the 2 GB border ( i do this around 600
MB ) to call one
Looks like I may have been
wrong.
Click to expand...


You are not wrong these are all things that you can set with the connection
and recordset object ( VB6 )

i prefer to use firehose cursors to read and optimistic locking for writing
the data

you can simulate this behavior in .Net


regards

Michel in Rotterdam :-)
 
did some comparisation tests and guess ,,, although they claim they are
faster as Access turned out in my tests that they were actually much slower

i have tried them all SQLlite , vistadb etc etc etc all claim to be better
but can`t make the claim for reall

regards

Michel Posseth [MCP]
 
Thanks for answers. I"m not sure what the "optimistic" locking is nor how to
split a database. I'll do some "googling" and see if I can find out what
it's all about. What I really wanted to do was put an access database on the
internet so more than one user can read from it simultaneously and also write
without errors due to someone else simultaneously read or writing.
 
Thanks for answers. I"m not sure what the "optimistic" locking is nor how
to
split a database. I'll do some "googling" and see if I can find out what
it's all about. What I really wanted to do was put an access database on
the
internet so more than one user can read from it simultaneously and also
write
without errors due to someone else simultaneously read or writing.
Click to expand...

Optimistically locking implies locking as little of the DB as possible
during updates.

Ask yourself what you want user A to see when user B has the record(s)
locked for update.
 
did some comparisation tests and guess ,,, although they claim they are
faster as Access turned out in my tests that they were actually much
slower

i have tried them all SQLlite , vistadb etc etc etc all claim to be
better but can`t make the claim for reall
Click to expand...

IME it's rarely the DB engine. Speed issues are usually the fault of the
application designers, some of who do loony things which screw things up.

Oracle, well designed, should be as fast as can be with the server 10 feet
away. I don't regard 5+ hours to retrieve one record as indicative of a well
designed system (no joke).
 
about that ACCESS Thingy


well in this situation we wrote a program that retrieved data from a 8
million records 3 column database ( simulated license plate retrival,
verry comon in our app )
, 30000 updates ( to simulate a price update ) and and some random
selects on 2 joined tables with 30.0000 records both


turned out that with this same dataset Access was the overall winner (
ofcourse a RDMS is faster , however these company`s claimed to have a faster
product )

regards

Michel Posseth [MCP]
 
Hello Dennis

You don't have to detect. When you try to update the DB you get a big fat
error. You catch it, and process it :)

Cyril
 
Back
Top