|
Inexpensive Relational Database
I work at a small enough shop that $15k for SQL Server or Oracle is a real bummer. We're using SQL Server almost exclusively, but it's cost frequently prohibits us from effectively scaling out and better serving our clients. (Yes, they're getting what they pay for -- that's a separate issue.)
Has anybody ever successfully migrated a fairly serious SQL Server application to something like PostgreSQL? By "fairly serious" I'm talking about 10-20 GB of storage, and 2500-3000 users, usually no more than 200 using it simultaneously. Row-level locking and stored procedures are a must, triggers highly desirable.
I've looked at a few different Linux-based options, and PostgreSQL seems to have the most comparable feature set, but if I'm missing a better one, I'm all ears. (I was amazed to learn MySQL, which you always hear about, doesn't even have stored procedures?)
Anyway, any success stories? Or horror stories, for that matter.
Ian Olsen
Tuesday, November 18, 2003
FWIW, MySQL is planning on stored procedures and triggers.
From their web site:
"Stored procedures are being implemented in our version 5.0 development tree"
"Triggers are scheduled for implementation in MySQL version 5.1."
I have no idea on the dates for those releases, but it's worth looking into if your timeline allows.
Mark Hoffman
Tuesday, November 18, 2003
For what its worth, our company, which can very much afford $15K for a db license, has done some analysis on the open source alternatives for an application very much like yours. Our application had about 500 stored procs. After several weeks of research and proof of concepts, we learned three things:
1. PostgreSQL probably would have been a good fit for our needs.
2. The time it would take to port the stored procedures would have exceeded two years of licensing costs, which was about the lifespan of the application.
3. The analysis we had to go through exceeded on year of licensing costs.
I think where you will really see payback is when you are porting from an expensive Unix HW to cheaper Intel hardware with Linux/DB or Windows/DB as opposed to saving big money on the software alone.
Good luck!
m
Tuesday, November 18, 2003
I once looked into migrating an Oracle DB to PostgreSQL.
However, it didnt work out, since Postgres at the time
did not support Oracle packages (which allows you to store stored procedures and functions within a single package, much like a Pascal unit).
Here is the old thread, which may be helpful, some posters over in that thread did some pretty cool things with PostgreSQL.
http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=32137&ixReplies=13
HTH
Patrik
Tuesday, November 18, 2003
One other thing I just remembered. Check out Sybase iAnywhere product (or SQL Anywhere). It used to be their mobile db solution, but has grown up nicely. Word is they will be spinning it off to its own product. It is extremely cheap, supposed to be robust and is an easy port for SQL Server SP's.
m
Tuesday, November 18, 2003
http://www.mysql.com/
http://www.postgresql.com/
http://firebird.sourceforge.net/
http://www.hwaci.com/sw/sqlite/
check.these
Tuesday, November 18, 2003
More free RDBMS worth checking out perhaps,
http://www.sapdb.org/
Patrik
Tuesday, November 18, 2003
Where did you get $15k?
A SQL Server processor license is $3500. Four-CPU machine?
Philo
Philo.
Tuesday, November 18, 2003
Incidentally, don't forget to add in TCO. You know and manage SQL Server now - you probably only spend an hour a week managing the server when it's in stable production.
Moving to another RDBMS means learning it, learning the limitations, porting your apps, teaching your developers, teaching your DBA or replacing him, vastly reduced resources for development...
If your developers/DBA are worth $50/hr, $15k = 300 man-hours of work. That's seven people for one week. As someone else mentioned - subtract the time you've already spent doing research...
Philo
Philo.
Tuesday, November 18, 2003
MySQL advocates have been promising that prerequisites of a modern RDBMS are "just around the corner" for _years_ now: Like many things, it's much easier said than done.
Of course a lot of the advantages of MySQL (dbase like speed that it attains because it really isn't much more complex than the same) will evaporate as it adds the features of the big boys.
Even for the fervent open sourcer, I've never understood the cultish following MySQL has when the lost-without-a-cause folks could just as easily have chosen the much more credible postgresql to define themselves by.
Dennis Forbes
Tuesday, November 18, 2003
We're using either 2 or 4 processor servers, depending on many factors, cash flow of course being one. None of these servers are running just one application.
I usually figure 5k per processor, I know it varies a bit. This page says $4781.
http://www.microsoft.com/sql/howtobuy/default.asp
Then add about $1,000 for the OS, and we've got $10,562 to $20,124 per server. We're not buying enough volume to get any major discounts, so this is pretty close to where we end up.
I fully believe we're getting what we pay for: SQL Server's expensive because it's a great product. I just thought we'd be foolish to not at least investigate alternatives.
We should also, no doubt, investigate whether we're really getting our money's worth with these multi-processor servers. I'd bet our bottlenecks are more I/O than CPU related.
Ian Olsen
Tuesday, November 18, 2003
Dennis,
I guess this is how MySQL will implement triggers and stored procedures in its database :-)
"SAP DB and MySQL AB signed a technology and cross licensing partnership agreement. With this agreement MySQL acquired full commercial rights to develop and market future releases of SAP DB. SAP will continue its investment in SAP DB development, maintenance and support.
From Q4 2003, SAP DB will be rebranded as "MaxDB" and offered as a MySQL AB product. "
http://www.sapdb.org/7.4/sapdb_mysql.htm
Patrik
Tuesday, November 18, 2003
> I've never understood the cultish following MySQL has
Me neither. Postgresql is very solid, very fast, and has a boatload of features -- the same ones that MySQL has listed on their "upcoming releases"!
Philo, cost to switch, though a real factor (hey, that's why we have legacy!), is ultimately a losing argument. I'd say the database folks should definitely be worrying about Open Source databases, which are already in the low to middle end.
Portabella
Tuesday, November 18, 2003
I'm with you on TCO -- and generally speaking this is a longshot for just that reason. We have very little "institutional knowledge" of Linux. I'm probably the resident expert, which barely gets the needle off zero.
But I still figure I'd be a fool to not at least do a little investigating.
Ian Olsen
Tuesday, November 18, 2003
Portabella, I don't agree that the cost to switch is ultimately a losing argument, at least not for us.
If these applications were going to be around for 20 years, that's one thing, but we're not in that boat. Probably 3-5 is a reasonable target, before an application either totally goes away or is totally reinvented. If it takes us, as a company, 2 years to develop reasonable competence with an open source solution, was it worth it? And 2 years is pretty fast, taking into account turnover and the fact that everybody and their brother knows SQL Server or Oracle.
But dear god, I didn't intend to start a discussion about MS vs open source TCO. I was more looking for some other small company folks to regale me with tales of success or disaster in similar situations, e.g. the only thing discussion forums are really good for: anecdotal evidence. ;)
Ian Olsen
Tuesday, November 18, 2003
> I don't agree that the cost to switch is ultimately a losing argument, at least not for us.
I didn't mean for you. I agree that (of course!!!) it does makes sense to look at TCO.
I meant that if the argument you rely on for a technology is cost to switch, then you're in trouble. Just look at Novell.
Portabella
Tuesday, November 18, 2003
Allow me to throw in a vote for Firebird. It's a open-source DB that's based on Borland's Interbase. Interbase has been around for what seems like forever and is rock solid and fast. Here's the link:
http://firebird.sourceforge.net/
Peter M. Jagielski
Tuesday, November 18, 2003
Portabella, cost to switch is only part (and often a small part) of TCO.
I've been a production DBA for SQL Server and Oracle. They simply do not compare. I have run six instances of SQL Server with less than an hour/day of DBA time. In my experience (through 8i), having a production Oracle box means having a full-time Oracle DBA. (do Oracle DBA's do Linux sysadmin'ing yet?)
I will probably get PostgreSQL in the near future so I can speak more intelligently about it, but I cannot imagine it being easier to admin than SQL Server.
Mind you, I take advantage of all of SQL Server's features - intricate SP's with temporary tables, Triggers, UDF's, Full text searching, DTS, scheduled jobs, alerts, log shipping, etc, etc. If you just use your database as a place for tables and queries, then perhaps you don't need a full enterprise RDBMS.
Philo
Philo.
Tuesday, November 18, 2003
Just because you have a 4 CPU server doesn't mean you have to purchase a 4 CPU license for SQL Server. You can run a single CPU license, and it will run on a single CPU.
Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, November 18, 2003
Why do you need stored procedures? I know MySQL has query caching, so i'd imagine that would be almost as the speed increase from stored procedures.
Vince
Tuesday, November 18, 2003
The reason people like MySQL is because it claims that databases are really simple, and that appeals to simple-minded people. ;)
(Honestly, I'd sooner trust Prevayler than MySQL. Hype aside, the Prevayler folks at least understand databases!)
I myself am rather curious about whether PostgreSQL would work well for the scenario the OP mentioned; I have a Sybase-backed app that might be nice to port, except for the stored-procedure hitch.
Phillip J. Eby
Tuesday, November 18, 2003
"Why do you need stored procedures?"
Yeah! And what's up with this "object oriented" and "recursion" nonsense? And don't get me started on lists and sorting - all completely overrated.
If I can't code it with input, print, and goto, then it's not worth doing!!!!
[g,d,r]
Philo
Philo.
Tuesday, November 18, 2003
> all completely overrated.
Great sarcasm, but poor argument.
Many people eschew stored procedures for perfectly sensible reasons.
I myself have done it both ways, and prefer the non-stored-procedures approach. I am not religious about it, however, and am perfectly willing to work with them as well.
IMO, it really comes down to the language and environment: I simply prefer OO languages to PL/SQL or T-SQL, and I prefer to host code outside the database.
Portabella
Tuesday, November 18, 2003
The reason that MySQL is popular is that for low end DB requirements, it works well, allowing people who've not done any previous DB work to get up & running easily.
In addition, it works well with Windows, running as a service in the background, etc, whereas Postgres has always seemed to give the impression that if you try & run it on Windows, you're on your own...
Gwyn Evans
Tuesday, November 18, 2003
> whereas Postgres has always seemed to give the impression that if you try & run it on Windows, you're on your own
I'm not sure if it's progressed since last I used it (2001), but at that time your description was spot on. You could get it to run through some wacky Cygwin magic, but that was it.
Portabella
Tuesday, November 18, 2003
My company has an MS SQLServer application that we love, but we have many customers that can't afford their own SQLServer. We were looking for a good port to a free relational DB, so I researched MySQL, PostgreSQL and Firebird.
MySQL doesn't have several of the capabilities we need. PostgreSQL has the capabilities, but you need a Linux server/admin to pull it off properly. Firebird has the capabilities and Windows compatibility that we wanted, but...it doesn't have a boolean field data type. (I've heard that MySQL has the same problem.)
I managed to export our current SQLServer database to Firebird in an afternoon, and I was merrily using our app with only a few minor syntax changes for the SQL statements. However, the idea of removing/converting all boolean references throughout our app is horrendous and might be a deal-breaker for us.
1/4 Ain't Bad
Tuesday, November 18, 2003
You don't get something for nothing.
There is always MSDE if you don't plan to go over 2 gigs.
m
Tuesday, November 18, 2003
"Yeah! And what's up with this "object oriented" and "recursion" nonsense? And don't get me started on lists and sorting - all completely overrated."
With all due respect to Philo The High Priest of Stored Procedures, let's not turn this into some stupid, pedantic argument over stored procedures.
Some people use them.
Some people don't.
Friggin' deal with it without making this a religious debate and insinuating that people who don't use them are thick-headed morons.
Geesh. I hate fanatics.
Whatever
Tuesday, November 18, 2003
Check out Sybase adaptive server anywhere.
T-sql like stored procedure language. Simple admin. Very scalable.
Karel
Wednesday, November 19, 2003
 Recent Topics
Fog Creek Home
| 
