h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

Okay Guys, here's a crux for you...

Which is better? Microsoft SQL or MySQL ? Microsoft bashing aside, I need to figure this one out as to which to have running this stupidly huge project I've got running. Help?

I've not used Microsoft SQL so I can't say anything about it. But I have used MySQL, its free, loads of people use it and all its "features" are well documented. There are also lots of online places to go if you get stuck.

And it scales quite well, with a little server tweaking. Go look at http://www.ensembl.org if you don't believe me, the whole human genome plus features in a MySQL database.

cheers Menz, that's my favourite, but I'm trying not to be biased

SQL Server can do stuff MySQL can't. That's when you reach for PostgreSQL, which is also open source, although the documentation's not as good and it's harder to set up, which is why so many people use MySQL. It does support subqueries, stored procedures and transactions, which MySQL doesn't (yet, version 4 and 4.1 look set to add most of those). So if I want ease with a fair bit of power, I reach for MySQL, if I need mega power, I reach for Postgres. Simple really.

Cheers

It's looking though like I'm going to have to use Microsoft SQL Server, something to do with transaction logs and data recovery or something

Unfortunately there are some things you can do with commercial servers which MySQL and PostgreSQL just aren't capable of

Although you don't necessarily have to use Microsoft... I assume your company already has the licence for SQL Server, but Oracle is probably even better... and it runs on UNIX.

We tend to use Oracle at work, but that might you cost a fortune. We only use MySQL for those things that have to be distibuted, and generally handed out to other people as there are no licencing costs.

I've had a quick browse round the MySQL website and there are some extentions to MySQL that are available under the same GPL license. Some of them say they do things like transactions, but I've not read into too much detail yet.

To be honest anything that we require transaction logging for is done in Oracle, but then I do get kinda spoilt at work.

Thankfully PHP seems to communicate just as well with MSSQL as it does with MySQl, yeah the open source. Unfortunatley, no matter how I try I can't seem to get a mssql query to set an auto_increment in a table field.

Can you use a sequence and insert it into the table yourself?

Trying to get some php scripts to create the table on the fly. I've found a way to do it, you use "IDENTITY(0,1) NOT FOR REPLICATION" instead of "DEFAULT '0' AUTO_INCREMENT" Seems a strange way of doing things, but I guess it's just what you get used to.

Now to try and get some respect back as I've done nothing the last couple of days then run to the techs trying to get them to sort out my problems. Why they couldn't have got me my computer up and running before hand I've no idea. Expecting someone to build a Win2000 box when they've never used NT before, and then install IIS, PHP and Microsoft SQL Server?? They're asking for trouble.

Now to convince someone who's been coding database packages for a couple of years that we don't really want all the information in the same database...

Splitting data across different databases is not a good idea, I must spend half my time trying to sort out data inconsistancies caused by just such an arrangement. Its totally evil, please don't do it.

What you can do (in oracle anyway) is to set up different schemas with different logins all running on the same server. That way the data is seperated but you can let the schemas share some tables, so you can connect them back together again. This does save some serious data curation headaches but it does need a large server to run it all on. We tend to use monster alpha clusters here, I have no idea what the NT equivelent would be.

We're looking to seperate the customers databases from our own. Currently all the information about the resellers clients is held in with our own, which is causing a headache, so we're going to split that off. It also means that we can adjust and modify the customers databases seperatley from ours, and save on downtime when patching and all that.