Topic: What Database ?

[Adam]

Hi all,

we currently use M$ SQL Server 7 for our 5 or so Database applications. It works well and am happy with the ability to utilize maintenance plans and automate backups etc.

What I am not happy with is the cost. We are at a crossroad, do we upgrade to the latest SQL Server version at a considerable cost or do we look at an alternative.

We are using our e-smith server for fairly basic stuff at the moment - mainly for our Postnuke intranet.

My questions are:

1) How stable are the linux based sql databases, i.e. mySQL and Postgres.
2) Are their automatic scripts etc that can automatically run to optimize and backup the databases ?
3) Do they run well under Windows environment using Microsoft Access as the front-end database (and odbc)

Any advice here would be greatly appreciated.

cheers,

Adam

[Nathan Fowler]

1) They are very stable, as stable or more so than the Microsoft counter-parts.
2) You can create your own backup scripts and cron them to ensure they run automatically.  The scope of your backups can range from database structure to actual database record values.
3) You will need to install the ODBC drivers on your clients, and reference them accordingly.  These are available from mysql.org

Note:
MySQL and SQL 7's date formats are different.  Additionally, MySQL does not support the INNER JOIN, LEFT JOIN, and RIGHT JOIN statements, so you may need to create some recordset recursion.  There are other language differences such as GetDate() versus Now(), SubString(), the "Select TOP" versus "Limit", etc.

These changes may require recoding of the applications you use.  It is recommended that you research the language differences in these products before you start to implement such a drastic change.

Hope this helped,
Nathan

[Maggard]

Adam wrote:
 
> My questions are:
>
> 1) How stable are the linux based sql databases, i.e. mySQL
> and Postgres.

According to http://jeremy.zawodny.com/blog/archives/000841.html SAP is moving to MySQL. This is in line with reports from lots of other companies. These aren't "toy" DBs, they're running big important high volume apps in high visibility locations.

> 2) Are their automatic scripts etc that can automatically run
> to optimize and backup the databases ?

Yes.

> 3) Do they run well under Windows environment using Microsoft
> Access as the front-end database (and odbc)

Yes, as well as any 3rd party product runs under MS. I know that sounds unfair at first but the truth is MS has tuned their OS's optimally for their DB product, 3rd parties must make do. As to playing nice with Access, yes they do.
 
> Any advice here would be greatly appreciated.

As your question is really MySQL/Postgres vs SQL Server and not SME-specific I suggest you'd find more/better information in places specializing in DBs. You can assume that anything you learn there will directly apply to SME, just keep in mind the new database templates coming in SME 6.

[Adam]

Thanks for both your input.

The only thing here that frightens me off a little bit is the talk of recursive queries, recoding etc.

I am not a programmer by trade but have created some nifty databases that we have used for years. My SQL skills aren't great and I do rely on M$ Access query builder quite a lot.

Do you feel that this would make the task difficult ?

cheers,

Adam