Which database?

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

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.
 
How many sites will have the database server? One?

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

JerryM
 
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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/prodinfo/features/compare-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 :)
 
JerryWEC said:
How many sites will have the database server? One?
Click to expand...

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?
Click to expand...

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.
 
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.
 
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.
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 :-)
:-)


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/prodinfo/features/compare-features.mspx
Click to expand...

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.
Click to expand...

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 :)
Click to expand...
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.
 
WINNER : "SQL Server Express"
ok, it is free, and you can upgrade the db to sql server without ANY
PROBLEM.
Click to expand...

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

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?
 
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.
Click to expand...

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/fbmetasecur-embedded.html

FYI, Firebird has a .NET driver.
 
Presumably it would also be possible to bundle the Access Runtime in a
similar manner?
Click to expand...

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?
 
can I not get away with a shared access mdb file?
You could - but when there is something better why not use it?
Click to expand...

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/fbmetasecur-embedded.html

FYI, Firebird has a .NET driver.
Click to expand...

OK, thanks, I'll take a look.

Cheers,
Phil.
 
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?
Click to expand...

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.
 
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.
 
Hetal said:
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.
Click to expand...

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.
 
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
 
On Fri, 27 Apr 2007 10:01:54 +0100, "Phil" <N/A> wrote:

¤
¤ ¤ > 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)
 
On Thu, 26 Apr 2007 15:44:01 +0100, "Phil" <N/A> wrote:

¤ 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)
 
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?
Click to expand...

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.
 
Back
Top