I am posting this in GQ, although I fear that it may turn out to be better suited to IMHO or GD, but I am hoping that there will be a factual answer to my question…
At work we have a bespoke database system that was written (4 years ago) in Delphi, using Paradox for the tables (chosen mostly since the license for Paradox comes bundled with Delphi). The system has been adequate for our needs (we are a Membership organisation in the UK with about 35,000 members records on our database) so far. Some of the tables (mostly those involved with financial transactions) are getting rather large and ponderous - heading for 500,000 records - and we are finding that Paradox is becoming a little slow in dealing with those records. The contractors have said that it might be necessary for us to switch from Paradox to another database system, although we could continue to use the same Delphi front-end.
So, my question is this - the two systems that have been mentioned in talking about this so far are SQL Server and Oracle - which of these would serve us best?
Thanking you in advance, I remain
Yours sincerely
Gp
Well I administer 3 (and soon to be 5) MSSQL servers. It really depends how big your database will get. MSSQL is very easy to set up and administer. But it really doesnt scale up for huge databases like oracle does.
Oracle
Pros: You can stick on just about any OS. Handles really huge databases. (or small ones.) Preveloped apps.
Cons: A real pain in the ass to setup and maintain. It’s expensive.
MSSQL
Pros: So easy to setup a monkey can do it. 7.0 and beyond does some self tuning. DTS: This is the way cool thing about SQL. You can use a wizard to transform a file to a database file etc and back again. Good for small to medium projects. Cheaper then oracle.
Cons: Can only be used on WinNT platforms. Supporting the evil empire.
Neither of these are trivial to maintain and will eventually die without a reasonably good admin.
Either way if your original Delphi app was written with paradox in mind, using TTables as opposed to TQueries, then you will not gain as much leverage from the scale up to a client-server database.
I would start with MSSQL. The investment is minimal. You can administer it without hiring a fulltime DBA. Going to Oracle will require a large software (probably hardware) and personnel (DBA) investment. If MSSQL carries you for a couple of years, it will have been worth it.
I don’t know if the “Oracle is bigger than SQL” arguement is true anymore. We’re a SQL shop, and I did extensive research on SQL vs. Oracle before we went with SQL. There’s plenty o’ large banks, airlines, etc. using SQL as a back end. The Microsoft Web Site has lots of “case studies” for SQL being used for things like 24K simultaneous users, gazillions of transactions per second, etc. The bad part is that SQL 2K is also much more expensive than SQL 7. I don’t know the exacts, but I do know that we’ve been talking about going with something else because the licensing of SQL 2K is going to be a big chunk bigger than SQL 7.0.
Oracle does have the advantage of running on non-Windows OS’s.
I also seconds john_e_wagner’s comment about “Neither of these are trivial to maintain and will eventually die without a reasonably good admin.”
Going from something like Paradox to something like SQL Server or Oracle is a learning experience to say the least. I went from dBase/FoxPro/Access to SQL Server about 3 years ago, and it took a fair amount of work. It’s a lot of fun, though. I like databases.
Growth has been pretty consistant over the last ten years or so, averaging at around an extra 2,250 records p.a. In our largest table (currently at some 450,000 records), this means an extra 45,000 records p.a.
So - it is never going to be a huge database, but it seems a switch is going to be needed. From your resopnses, it looks like SQL Server is the way to go…
Yep. 3 DBMSs spring to mind, though none of them are MS SQL Server or Oracle.
Sybase SQL Anywhere runs on Win32 or Unix/Linux, is simple to administer, fully SQL92 compliant, has Java-In-The_Database which can be used for Stored Procs, and will easily scale to the sizes you’re talking about. Way cheaper than SQLServer or Oracle.
Sybase Adaptive Server Enterprise (formerly SQL Server). Sybase wrote SQL Server, shared the code with Microsoft, and split from Microsoft about 5 years ago (IIRC). Microsoft’s version of the code stayed as SQL Server and evolved one way, Sybase’ version was renamed to ASE and evolved in a different way. ASE runs on Win32, Unix, Linux, is definitely enterprise ready (used by a large percentage of Wall Street), is relatively easy to administer (not as easy as SQL Anywhere, but not too bad), and is not as expensive as Oracle. Over the last few years, ASE has held the TPC benchmark many times. It’s very fast. Running on Solaris, I’ve seen it stay up for 12 months (!) without a restart.
MySQL is an open source DBMS, released under the GPL. It’s free, and mighty fast. Used as the backend for a lot of Unixy stuff, including websites such as Slashdot. Not as feature rich as the two DBMSs above, it still does most things that you’ll ever need. It’s very very fast.
I’ve used all three extensively (I’m a programmer) and I’ve listed them in the order that I prefer to use them. I’ve also played around with PostgreSQL, but I can’t stand behind it yet, because I haven’t used it in production. It’s also under the GPL, it’s free, and more fully featured than MySQL.
Add another vote to MySql. Very fast, very easy to set up, integrates well with Apache so you can do all your front end work with HTML. It’s the backend database for this here website too, so you’ve seen proof that it’ll support large databases.
Having used SQL Server extensively and Oracle and mySQL to a lesser extent, I can’t come up with a good reason to pick any one over another. They all do the job. They each have pros and cons, but there is very little you can do with one that you can’t do with the others.
IMO, one of the predominant factors in your choice should be what kind of db admins you have available. If you have people who know one of them, choose that one because you’re going to need qualified admins to make the conversion and keep the thing running. john_e_wagner says any monkey can set up SQL Server, but IME there’s a huge difference between a db set up by a casual user and one done right. A qualified admin will get the field types set most optimally (no small task when porting data), set indices, stored procedures, internal permissions on various users, etc. All of these things can be done with any database, but they need to be done right, so get a good admin. Of course, if you don’t already have someone, you’re going to have to hire, but your selection may still be guided by the available pool.
Another thing to throw into the pot - we tend to do a lot of our in-house data analysis in MS Access, while all the data capture and various standard reports and functions are done by the bespoke Delphi front-end. Reasons for this - its part of the MS Office suite which we already have and it is easier to train people to use something that has a look and feel of other MS products.
The question then is can Access import data from all these data sources with ease? Since this is something we do on a daily basis, it will be something we need to look at as well.
Yes. I routinely use Access to pull data out of Pervasive and older Btrieve databases. If you can make an ODBC DSN to it, Access can link or import the data.
For what it’s worth, the higher-ups at my company wanted to go with a DB solution for all our products, and they picked MySql as the way to go. We did extensive testing here, and there is simply no comparision between SQL Server and MySQL. MySQL does not do the job. It’s much slower, and not nearly as full-featured as SQL Server. We’ll be sticking to SQL Server for now.
I’d be curious what your applications are if you’re willing to share. In certain applications, mySQL is certainly competitive. As a website back-end, it’s great, and I’ve seen benchmarks where it beat SQLServer in that application (which doesn’t apply to the OP). In the past, its use in some situations was limited by its lack of support for transactions, but the new table structures have remedied that.
I’m no huge fan of mySQL and I’m not defending it. I’m just curious about the details of your comparison so I can add that to my own list and quit making bad recommendations if that’s what I’ve done here.
I can say this from my experiences with both Oracle and SQLServer: don’t install Oracle on a MS Windows server, it really isn’t optimized for Windows systems and will be slow and unstable compared to running Oracle on a Unix system.
So if you plan to run Windows on the database server, install SQLServer, if you plan to run Unix, install Oracle. If you plan to run Oracle on a Linux system, you will probably want to install RedHat 6.2; Oracle still isn’t compatible enough with higher versions.
As for other reasons to choose between the two databases, it doesn’t matter much. You mention your database has about 500,000 records. Both databases should handle that easily (and a lot more records too). SQLServer is more userfriendly, but you can get GUI clients for Oracle also (like TOAD). I think SQLServer still is cheaper than Oracle, but not sure about that anymore.