473,326 Members | 2,114 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Which database?

I'm looking for some advice on choosing a suitable database for a new
application. It's going to be written in VB. I have a lot of experience with
VB6, but I now have a copy of VB2005 Express, and intend to use this for the
new project. I am also quite familiar with SQL and have worked with Oracle,
SQL Server and Access in the past on various projects, but I'm not sure
which will be most appropriate for this application. The database design is
in very early stages at the moment, but it's looking like the schema isn't
going to be too complicated (about 12 tables identified so far) but it will
need to store quite a lot of data, hundreds, possibly thousands of records
could potentially be generated per day (although most of these records will
be quite small) and a lot of the data will need to be kept live for long
periods of time (months probably). There will be one main system generating
the data, but it will be necessary for a small number of other users to
query the database, mainly to generate reports.
I'm currently thinking about Access or SQL Server, but I'm a little
concerned about the costs. We will (hopefully) be putting this system in at
a large number of different sites, so would prefer not to have to install
the full version of Office or SQL Server.
I quite like the idea of using Access, as it does allow multiple users, and
I believe it is possible to freely distribute a runtime version, so it would
not be necessary to install Office to run it. I am worried though whether it
could cope with the amount of data involved.
I believe I also have SQL Server Express available as part of the new VB
system, but I don't know much about this. Would this be the best thing to go
with, or might I be better looking at a full version of SQL Server? (the
cost of this may be prohibitive). Are there any other options?
Thanks in advance.
Phil.
Apr 26 '07 #1
18 1402
How many sites will have the database server? One?

How many users will need to access the db at one time?

JerryM
Apr 26 '07 #2
"Phil" <N/Awrote in
news:46**********************@ptn-nntp-reader02.plus.net:
I quite like the idea of using Access, as it does allow multiple
users, and I believe it is possible to freely distribute a runtime
version, so it would not be necessary to install Office to run it.
If the system is multi-user, use SQL Server Express or SQL Server.
I
am worried though whether it could cope with the amount of data
involved.
Well, some people say it can, but I wouldn't use it for more than a
couple hundred MBs. I would only use it for simple single user
applications. I'm probably going to get flamed for this comment :-)
I believe I also have SQL Server Express available as part
of the new VB system, but I don't know much about this. Would this be
the best thing to go with, or might I be better looking at a full
version of SQL Server? (the cost of this may be prohibitive). Are
there any other options? Thanks in advance.
SQL Server Express is a slimmed down version of SQL Server. Here is a
feature comparison of all the SQL Server database:

http://www.microsoft.com/sql/prodinf...-features.mspx

As you see, Express has most of the features of the paid versions of SQL
Server. Although it has a 4GB catalog size limit - for most databases
that's huge. If your customers need a larger database or have an in-
house SQL Server, they can merely change your applications's connection
string to point to the new database.

Otherwise you could look at MySQL. Great database :)
Apr 26 '07 #3

"JerryWEC" <Je******@newsgroups.nospamwrote in message
news:up*************@TK2MSFTNGP05.phx.gbl...
How many sites will have the database server? One?
Multiple independant sites. No communications required between multiple
sites. I was thinking of a single shared mdb file on a LAN.
>
How many users will need to access the db at one time?
Most of the time probably just 1, but others need to be able to query from
time to time, so would be useful to allow for a few concurrent connections.
Typical usage would be one user generating the data, and then a small number
of other users querying the database from time to time. The application will
also be doing some complex calculations from the data, the results of which
will be fed back to the user generating the data, and stored in the database
for the other occasional users to query.
Apr 26 '07 #4
WINNER : "SQL Server Express"
ok, it is free, and you can upgrade the db to sql server without ANY
PROBLEM.

Access : only you want to have the application come with the DB.

--
cheers,
RL
"Spam Catcher" <sp**********@rogers.comwrote in message
news:Xn**********************************@127.0.0. 1...
"Phil" <N/Awrote in
news:46**********************@ptn-nntp-reader02.plus.net:
>I quite like the idea of using Access, as it does allow multiple
users, and I believe it is possible to freely distribute a runtime
version, so it would not be necessary to install Office to run it.

If the system is multi-user, use SQL Server Express or SQL Server.
>I
am worried though whether it could cope with the amount of data
involved.

Well, some people say it can, but I wouldn't use it for more than a
couple hundred MBs. I would only use it for simple single user
applications. I'm probably going to get flamed for this comment :-)
>I believe I also have SQL Server Express available as part
of the new VB system, but I don't know much about this. Would this be
the best thing to go with, or might I be better looking at a full
version of SQL Server? (the cost of this may be prohibitive). Are
there any other options? Thanks in advance.

SQL Server Express is a slimmed down version of SQL Server. Here is a
feature comparison of all the SQL Server database:

http://www.microsoft.com/sql/prodinf...-features.mspx

As you see, Express has most of the features of the paid versions of SQL
Server. Although it has a 4GB catalog size limit - for most databases
that's huge. If your customers need a larger database or have an in-
house SQL Server, they can merely change your applications's connection
string to point to the new database.

Otherwise you could look at MySQL. Great database :)

Apr 26 '07 #5
If the system is multi-user, use SQL Server Express or SQL Server.

I realise that is probably better, but as the number of users is small can I
not get away with a shared access mdb file?
At some installations, there may be only one user.
>
>I
am worried though whether it could cope with the amount of data
involved.

Well, some people say it can, but I wouldn't use it for more than a
couple hundred MBs. I would only use it for simple single user
applications. I'm probably going to get flamed for this comment :-)
:-)
>
>I believe I also have SQL Server Express available as part
of the new VB system, but I don't know much about this. Would this be
the best thing to go with, or might I be better looking at a full
version of SQL Server? (the cost of this may be prohibitive). Are
there any other options? Thanks in advance.

SQL Server Express is a slimmed down version of SQL Server. Here is a
feature comparison of all the SQL Server database:

http://www.microsoft.com/sql/prodinf...-features.mspx
Thanks for that. I've had a quick and I don't think I need any of those
features. All I need is a relational database that I can store data in and
query using SQL.
>
As you see, Express has most of the features of the paid versions of SQL
Server. Although it has a 4GB catalog size limit - for most databases
that's huge. If your customers need a larger database or have an in-
house SQL Server, they can merely change your applications's connection
string to point to the new database.
It's highly unlikely our target customers for this application will have a
SQL server already. Some may not even have a LAN. I think 4Gb will be plenty
for this application. I suspect I'll be running into performance problems
before the database gets near that size.
>
Otherwise you could look at MySQL. Great database :)
I'd thought of that. I've used it for a couple of noddy web applications
using php on my personal website. How well does this integrate with VB and
dotnet? Are there any issues with distribution and licensing?

Cheers,
Phil.
Apr 26 '07 #6
WINNER : "SQL Server Express"
ok, it is free, and you can upgrade the db to sql server without ANY
PROBLEM.
Is it totally free to distribute, as part of our application?
Access : only you want to have the application come with the DB.
We would definitely want the DB to be installed as part of our application
if possible.
I think it's possible to bundle SQL Server Express as part of the
application installation package.
Presumably it would also be possible to bundle the Access Runtime in a
similar manner?
Apr 26 '07 #7
"Phil" <N/Awrote in
news:46**********************@ptn-nntp-reader02.plus.net:
>If the system is multi-user, use SQL Server Express or SQL Server.

I realise that is probably better, but as the number of users is small
can I not get away with a shared access mdb file?
At some installations, there may be only one user.
You could - but when there is something better why not use it?

If you want an embeddable database, take a look at Embedded FireBird. It's
only 200K and can be embedded DIRECTLY into your application as a 250KB
DLL! Open Source.

http://www.firebirdsql.org/manual/fb...-embedded.html

FYI, Firebird has a .NET driver.

>>
>>I
am worried though whether it could cope with the amount of data
involved.

Well, some people say it can, but I wouldn't use it for more than a
couple hundred MBs. I would only use it for simple single user
applications. I'm probably going to get flamed for this comment :-)

:-)
>>
>>I believe I also have SQL Server Express available as part
of the new VB system, but I don't know much about this. Would this
be the best thing to go with, or might I be better looking at a full
version of SQL Server? (the cost of this may be prohibitive). Are
there any other options? Thanks in advance.

SQL Server Express is a slimmed down version of SQL Server. Here is a
feature comparison of all the SQL Server database:

http://www.microsoft.com/sql/prodinf...-features.mspx

Thanks for that. I've had a quick and I don't think I need any of
those features. All I need is a relational database that I can store
data in and query using SQL.
>>
As you see, Express has most of the features of the paid versions of
SQL Server. Although it has a 4GB catalog size limit - for most
databases that's huge. If your customers need a larger database or
have an in- house SQL Server, they can merely change your
applications's connection string to point to the new database.

It's highly unlikely our target customers for this application will
have a SQL server already. Some may not even have a LAN. I think 4Gb
will be plenty for this application. I suspect I'll be running into
performance problems before the database gets near that size.
>>
Otherwise you could look at MySQL. Great database :)
I'd thought of that. I've used it for a couple of noddy web
applications using php on my personal website. How well does this
integrate with VB and dotnet? Are there any issues with distribution
and licensing?

Cheers,
Phil.
Apr 26 '07 #8
"Phil" <N/Awrote in news:4630d3ec$0$8734$ed2619ec@ptn-nntp-
reader02.plus.net:
Presumably it would also be possible to bundle the Access Runtime in a
similar manner?

Access runtime is a bit different, the runtime is used to launch Access
applications without the need for Office.

Do you have an access application or do you only need the ADO.NET drivers?
Apr 26 '07 #9
Yes it is free. However, you must register yourself on the sql server
express web site.

here :
http://msdn2.microsoft.com/en-us/lib...custapp_topic5

It is not Access runtime, it is the jet engine :), and it should come with
the OS anyway,

--
cheers,
RL
"Phil" <N/Awrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>WINNER : "SQL Server Express"
ok, it is free, and you can upgrade the db to sql server without ANY
PROBLEM.

Is it totally free to distribute, as part of our application?
>Access : only you want to have the application come with the DB.

We would definitely want the DB to be installed as part of our application
if possible.
I think it's possible to bundle SQL Server Express as part of the
application installation package.
Presumably it would also be possible to bundle the Access Runtime in a
similar manner?

Apr 26 '07 #10
>can I not get away with a shared access mdb file?
>At some installations, there may be only one user.

You could - but when there is something better why not use it?
I'm just more familiar with Access at the moment. If SQL Server Express
really is so much better, that's what I'll probably go with. It is
definitely looking like I need to have a play with this, and try and get
up-to-speed with how it works.
>
If you want an embeddable database, take a look at Embedded FireBird. It's
only 200K and can be embedded DIRECTLY into your application as a 250KB
DLL! Open Source.

http://www.firebirdsql.org/manual/fb...-embedded.html

FYI, Firebird has a .NET driver.
OK, thanks, I'll take a look.
>
Cheers,
Phil.
Apr 26 '07 #11
>
>Presumably it would also be possible to bundle the Access Runtime in a
similar manner?


Access runtime is a bit different, the runtime is used to launch Access
applications without the need for Office.

Do you have an access application or do you only need the ADO.NET drivers?
No it won't be an Access application. So you're right I'll just need ADO.
I've not looked at ADO in .NET yet, is this included as standard, or is it
something else that we'd need to distribute with the application?

Thanks for your help.
Cheers,
Phil.
Apr 26 '07 #12

"Egghead" <robertlo@NO_SHAW.CAwrote in message
news:ut**************@TK2MSFTNGP02.phx.gbl...
Yes it is free. However, you must register yourself on the sql server
express web site.

here :
http://msdn2.microsoft.com/en-us/lib...custapp_topic5
That one's gone on the favourites list for later. Looks like a lot to read
:-)

Thanks for your help.
Cheers,
Phil.
Apr 26 '07 #13
We have moved all of our MS-Access databases to MySQL and we are
slowly moving our MS-SQL databases to MySQL as well. Our databases
hold a huge amount of data as well, i would say thousands of records
are generated everyday. We have been using MySQL for almost an year
now and we haven't faced any issues.

Apr 26 '07 #14

"Hetal" <he*************@gmail.comwrote in message
news:11**********************@r3g2000prh.googlegro ups.com...
We have moved all of our MS-Access databases to MySQL and we are
slowly moving our MS-SQL databases to MySQL as well. Our databases
hold a huge amount of data as well, i would say thousands of records
are generated everyday. We have been using MySQL for almost an year
now and we haven't faced any issues.
I've only ever used the MySQL database provided by my ISP. I believe this is
an Open Source database. Does this mean that it is free to download and
distribute?, or is it one of these things that's only free for
non-commerical use? Is it something that we could easily bundle in with our
application? I would like to avoid anything that needs any technical
knowledge on the part of the end-user to install, set-up and configure.

Thanks
Phil.
Apr 27 '07 #15
Hi Phil,

One of the great advantages of the MS solution is the ease of use of the
tools. For example, I had a go with PostGRE and didn't get on with it very
well - i.e. the learning curve was too steep for the time I had available to
make progress on the project. Like a lot of Open Source software, the user
experience takes second place to the functionality, although the
functionality itself is often very high quality. With SQL Server Management
Studio (for example) and tight Visual Studio integration, SQL Server is
actually a joy to use for a programmer and this for me makes it competitive
with other free solutions.

The only downside with Express 2005 is the memory limit (no more than 1gb
used by SQL Server), CPU limit (no more than 1 cpu used by SQL Server) and
the database size limit (4Gb). We got around the latter by implementing a
"roll-over" facility with some nifty footwork to ensure the roll-over case
was robust.

Robin
Apr 27 '07 #16
On Fri, 27 Apr 2007 10:01:54 +0100, "Phil" <N/Awrote:

¤
¤ "Hetal" <he*************@gmail.comwrote in message
¤ news:11**********************@r3g2000prh.googlegro ups.com...
¤ We have moved all of our MS-Access databases to MySQL and we are
¤ slowly moving our MS-SQL databases to MySQL as well. Our databases
¤ hold a huge amount of data as well, i would say thousands of records
¤ are generated everyday. We have been using MySQL for almost an year
¤ now and we haven't faced any issues.
¤ >
¤
¤ I've only ever used the MySQL database provided by my ISP. I believe this is
¤ an Open Source database. Does this mean that it is free to download and
¤ distribute?, or is it one of these things that's only free for
¤ non-commerical use? Is it something that we could easily bundle in with our
¤ application? I would like to avoid anything that needs any technical
¤ knowledge on the part of the end-user to install, set-up and configure.

MySQL Embedded is the product you would use if you are distributing the database components with
your application. However, unless your app is distributed as open source under the GPL, you would
need to purchase a commercial license for the product.

http://www.mysql.com/oem/
Paul
~~~~
Microsoft MVP (Visual Basic)
Apr 27 '07 #17
On Thu, 26 Apr 2007 15:44:01 +0100, "Phil" <N/Awrote:

¤ I'm looking for some advice on choosing a suitable database for a new
¤ application. It's going to be written in VB. I have a lot of experience with
¤ VB6, but I now have a copy of VB2005 Express, and intend to use this for the
¤ new project. I am also quite familiar with SQL and have worked with Oracle,
¤ SQL Server and Access in the past on various projects, but I'm not sure
¤ which will be most appropriate for this application. The database design is
¤ in very early stages at the moment, but it's looking like the schema isn't
¤ going to be too complicated (about 12 tables identified so far) but it will
¤ need to store quite a lot of data, hundreds, possibly thousands of records
¤ could potentially be generated per day (although most of these records will
¤ be quite small) and a lot of the data will need to be kept live for long
¤ periods of time (months probably). There will be one main system generating
¤ the data, but it will be necessary for a small number of other users to
¤ query the database, mainly to generate reports.
¤ I'm currently thinking about Access or SQL Server, but I'm a little
¤ concerned about the costs. We will (hopefully) be putting this system in at
¤ a large number of different sites, so would prefer not to have to install
¤ the full version of Office or SQL Server.
¤ I quite like the idea of using Access, as it does allow multiple users, and
¤ I believe it is possible to freely distribute a runtime version, so it would
¤ not be necessary to install Office to run it. I am worried though whether it
¤ could cope with the amount of data involved.
¤ I believe I also have SQL Server Express available as part of the new VB
¤ system, but I don't know much about this. Would this be the best thing to go
¤ with, or might I be better looking at a full version of SQL Server? (the
¤ cost of this may be prohibitive). Are there any other options?
¤ Thanks in advance.
¤ Phil.
¤

I would hesitate to recommend Access or SQL Server (or Express) given your requirements. With
respect to Access I think you're going to bump up against the size limitation (which is 2G). SQL
Server and Express are both large footprint distributions and there is generally some setup and
administration involved. SQL Server CE would be a good choice but it was designed to support only
single-user client installations.

You may want to take a look at VistaDB (http://www.vistadb.net/). There is a per developer license
but there is no distribution license.
Paul
~~~~
Microsoft MVP (Visual Basic)
Apr 27 '07 #18
"Phil" <N/Awrote in
news:46**********************@ptn-nntp-reader02.plus.net:
>Otherwise you could look at MySQL. Great database :)
I'd thought of that. I've used it for a couple of noddy web
applications using php on my personal website. How well does this
integrate with VB and dotnet? Are there any issues with distribution
and licensing?
I don't think there are any distribution issues - in fact I believe MySQL
can be deployed using xCopy (no installer required).

MySQL has a couple .NET drivers (open source + commercial) which are very
good quality. The tools are great too - right up along side SQL Server
Enterprise Manager.

If you end up going with MySQL, EMS makes several good MySQL tools.
Apr 27 '07 #19

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: John | last post by:
I have over 5000 thumbnail pictures of size 5kb each. I would like to able to load all 5000 pictures and view 50 per page using mysql_data_seek(). I would like to know what are the advantages and...
11
by: Jason | last post by:
Let's say I have an html form with 20 or 30 fields in it. The form submits the fields via POST to a php page which updates a table in a database with the $_POST vars. Which makes more sense? ...
7
by: Murtix Van Basten | last post by:
Hi all, I will deploy a database project to an Oracle server, but I could not figure out which version of Oracle should I get. Here is my configuration: Hardware: Dell 1750 Dual Xeon 3.2Ghz,...
1
by: gregs | last post by:
Hi folks, I need to know which EXE's of SP3 to install with SQL Server 2000 Personal Edition. From the Microsoft website: ...
4
by: Knokmans | last post by:
Hi, All of this is still a test envinonment, but has to go on production.... I restore a database a database the follwing way db2 restore db <database> from <directory. taken at <timestamp>...
5
by: NG | last post by:
Hi, We are having DB2-V7.2 DB on AIX 5.2 machine. Recently we upgraded our system to fixpack 13 and activated activate AIX asynchronous IO function. Our DB is going to crash recovery with...
122
by: seberino | last post by:
I'm interested in knowing which Python web framework is most like Ruby on Rails. I've heard of Subway and Django. Are there other Rails clones in Python land I don't know about? Which one...
3
by: ferg | last post by:
I have a Customer table. The table has two different CHECK constraints. Then there is the Customer details dialog, which provides the user with an UI for changing users. I have some UPDATE sql,...
4
by: ImOk | last post by:
I need to program generically. I am familiar with the ODBC layer but never used PDO or DB. Does anyone have any experience with these database layers? Thanks
5
by: rAinDeEr | last post by:
Hi, I have a web application with a table to store terms and conditions of a Company. This may some times run into many pages and some times it may be just a few sentences. It is a character...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.