mssql-mysql-postgresql-oracle

SQL 2005 just came out, and since I am at a point where I really need to upgrade, I decided to look for options. Options include MySQL 5.0 (right), PostgreSQL, Ms-SQL 2005, and Oracle 10g.

I found several documents and interesting articles comparing this DBRMS’s…

Informational:
http://www.mssqlcity.com/Articles/Compare/sql_server_vs_mysql.htm
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp
http://www.mysql.com/news-and-events/news/article_976.html

Critiques:
http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/26/428527.aspx

Comparisons (excellent):
http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html

And last but not least, a PDF document called Features, strengths and weaknesses comparison between MS SQL 2005 (Yukon) and Oracle 10g databases (attached).

Well, out of the batch, mysql is out… I’m looking for a reliable and scalable DBRMS that can handle hundreds of transactions per second in an active/active cluster environment. The introduction of views and stored-procs was interesting, it’s still missing essential locks, rollbacks, constrains, data validation, and I’m pretty sure that if I start playing with cursors and t-sql it will break.

PostgreSQL still doesn’t fully support the clustering I’m looking for, but I’ll keep looking, I might be wrong. Ms-SQL 2005 seems very promising, and I read some articles claiming that clustering is easier. I had a horrible time accomplishing this with SQL 2000 because another requirement is no disk-arrays… the servers are not even in the same continent.

I know Oracle will fulfill, but I have to study the price impact and code migration time. I’ll be installing ms-sql 2005 this week or weekend, and hopefully I’ll elaborate more early next week.

Database Mirroring (Extend log shipping, automatic failover to a standby server)

SQL Server 2005 significantly enhances the capabilities of log shipping by providing a database mirroring option. Database mirroring allows continuous streaming of the transaction log from a source server to a single destination server. In the event of failure to the primary system, applications can reconnect to the database on the secondary server almost immediately, without waiting for recovery to finish. The secondary database instance detects the failure of the primary server within several seconds and accepts database connections almost immediately after detecting the failure. Unlike failover clustering, the mirrored server is fully cached and ready to accept workloads because of its synchronized state. Well, what this really means is that you need three MSSQL server instances. However only a single instance is actually active and then up-to-date standby database should be available (which can not be accessed by users until failover)

Oracle has a very similar mirroring as compared to MSSQL by having Oracle physical standby with dataguard. The only difference is that with Oracle just two instances are required: active and passive.