Time to migrate from Access DB to???? ---- Need recommendations

  • Thread starter Thread starter Algonquin J. Calhoun
  • Start date Start date
A

Algonquin J. Calhoun

I've developed an application that exceeds the Access 2GB limit. This application was developed as a prototype and the users have found it very
useful. Approximately 45,000 records are added to the database each day. Every 3 months I have to archive some of the older detail rows to allow
continued running of the application against the most current 90 day data.

The users have decided to incorporate ALL of the data for their analysis. Initially it is going to be about 16GB.

I'm looking for a database that will house the 16GB and be able to handle the transaction volume. Limited multi-user capability - up to 5 users doing
inquiry only. The most current 90 data will be used by the current system on a daily basis. The older data for research, used in a standalone
process. Since the application is written in VB.net 2005, the DB must interface transparently, just as Access or SQL.

Cost will be one of the key factors in the decision. I've read about these DB's with some of my comments.

SQL Express - can't handle 16+ GB
MS SQL Server - high cost
SQL Lite - don't know if it slips right in to VS development environment.

If anyone has any recommendations of other DB's that fit the bill please respond.

Al
 
If anyone has any recommendations of other DB's that fit the bill please
respond.
Click to expand...

Have you looked at MySQL?

ADO.NET being used in you application should work with MySQL. There should
be a MySQL ADO.NET data provider.
 
I'm looking for a database that will house the 16GB and be able to
handle the transaction volume. Limited multi-user capability - up to
5 users doing inquiry only. The most current 90 data will be used by
the current system on a daily basis. The older data for research,
used in a standalone process. Since the application is written in
VB.net 2005, the DB must interface transparently, just as Access or
SQL.

Cost will be one of the key factors in the decision. I've read about
these DB's with some of my comments.

SQL Express - can't handle 16+ GB
MS SQL Server - high cost
SQL Lite - don't know if it slips right in to VS development
environment.
Click to expand...

Does each site have their own SQL Server? A lot of companies already have
their own SQL servers so perhaps you can piggyback off it?

A a SQL server 5-pack license isn't all that expensive considering the
tools (and support) you receive with it.

Otherwise, take a look at MySQL. But the tools that come with it aren't
very good, and I suggest you buy a good management/query tool for it.
 
Well ....

for the best performance , best integration with VS i would say take SQL
2005 and just pay the fee

however if it rally needs to be a free RDBMS i would recomend Firebird
http://www.firebirdsql.org/ in contradiction to MYSQL it has verry good
free support and a active comunity , it has ADO.Net providers ( for free )
there are very nice graphical administration modules out there . there is
also a embedded version ( although i wouildn`t recomend this with a 16 GB
DB ) it is also very fast etc etc etc

it has no restrictions what so ever , so it can scale to the linits of the
system

HTH

Michel
 
Hi there,

Just a thought, but if you are handling such a large amount of data
for frequent querying, perhaps moving the querying method away from
standard RDBMS is worth considering. Have you though about perhaps
using an OLAP or ROLAP data model?

Of course the requirements of this type of system are a little
different from using a standard RDBMS, but if you head down the SQL
server path you can see if it will work for you as you will get these
capabilities with it (if I remember correctly). You can also do some
very nice bespoke development with Analysis Services (SQL server OLAP)
with such work environments as ProClarity, and if your client has an
version of MS Office from Office 2k onwards then there is direct
integration of Analysis Services to the MS Office applications.

There is a website you can get some more details on the topic here:
http://www.microsoft.com/sql/solutions/bi/default.mspx

A business intelligence (BI) approach may be a good way of scaling up
your application and providing a rich user experience, and maybe even
integrating in some workflow applications to boot.

Just a passing though

Cheers

The Frog
 
Does each site have their own SQL Server? A lot of companies already have
their own SQL servers so perhaps you can piggyback off it?

A a SQL server 5-pack license isn't all that expensive considering the
tools (and support) you receive with it.

Otherwise, take a look at MySQL. But the tools that come with it aren't
very good, and I suggest you buy a good management/query tool for it.
Click to expand...


There's only one site. The "90 days of data" db will be used for running daily analysis and reports. The 16GB will be used for research. MySQL
seems to be poping up everywhere as a recommendattion.

Thanks,

Al
 
There's only one site. The "90 days of data" db will be used for
running daily analysis and reports. The 16GB will be used for
research. MySQL seems to be poping up everywhere as a
recommendattion.
Click to expand...

MySQL is a great DB for the price, but SQL Server does come with tools
such as analytical services, notification services, service broker, .NET
CLR, SSIS, etc which make it a great platform for application
development.

Since you mention you have a 16GB research database, you should take a
look at SQL Server Analysis Services prior to committing to MySQL.
Analysis Services allow you to create datamining OLAP cubes - great for
mining/analysing large amount of data (sure beats Stored Procedures).

Your application seems to have heavy reporting requirements which excel
on SQL Server. So it might be worth biting the bulleting and paying the
SQL Server licensing fee than reinventing the wheel and rebuilding all
this stuff.
 
I've developed an application that exceeds the Access 2GB limit. This application was developed as a prototype and the users have found it very
useful. Approximately 45,000 records are added to the database each day. Every 3 months I have to archive some of the older detail rows to allow
continued running of the application against the most current 90 day data.

The users have decided to incorporate ALL of the data for their analysis. Initially it is going to be about 16GB.

I'm looking for a database that will house the 16GB and be able to handle the transaction volume. Limited multi-user capability - up to 5 users doing
inquiry only. The most current 90 data will be used by the current system on a daily basis. The older data for research, used in a standalone
process. Since the application is written in VB.net 2005, the DB must interface transparently, just as Access or SQL.

Cost will be one of the key factors in the decision. I've read about these DB's with some of my comments.

SQL Express - can't handle 16+ GB
MS SQL Server - high cost
SQL Lite - don't know if it slips right in to VS development environment.

If anyone has any recommendations of other DB's that fit the bill please respond.

Al
Click to expand...

Does your company have a DBA? If so his/her familiarity with a certain
database technology should be considered when you decide what database
to go with. After all, a tool is only useful when someone knows how to
use it.

I'm also curious if you fall under the licensing terms of Sql Server
2005 Express edition? I'm not very familiar with the rules, but if
you're eligible you could get a great database engine (with a good
amount of tools) for free.

Thanks,

Seth Rowe
 
Never heard of Firebird. Will have to look into it.

ever heard of interbase ? http://en.wikipedia.org/wiki/Interbase

Firebird is the conitinuance of the source from the once greatest
multiplatform database availlable at that time and even today a lot of
companys work with the commercial versions .

Borland made the source public , and so you have a few spinoffs , the best
spinoff is Firebird wich is actually , really a great datbase if costs oare
your concern
i just visited there website for the latset news and read there that it even
has now full VS.Net integration .

I have onced tested the DB engine for my previous employer , and we were
really impressed how good it performed and easy to use it was in
contradiction to other vendors ( who charged for all extras , while Firebird
is 100% free )

However as i said before my main database is SQL 2005 , if you can aford it
i would say go for SQL 2005 as it is in my opinion the dream of every VS.net
developer as a DB (Superior fast , easy in use , broad comunity with lots of
help and howtos availlable etc etc ) however it costs a few bucks :-(

michel
 
Back
Top