Oracle Easier to Administer Than Microsoft SQL Server?
by Stephen Wynkoop and the SSWUG Community
Note: this original "editorial" portion ran in the newsletter on May 11, 2004.
I'm biased. I admit it. But I found an article on Intelligent Enterprise, and the associated Progressive Strategies report to be, well, contrary to everything I have experienced and everything I've seen and heard when comparing "out of the box" SQL Server and Oracle installations. I don't know that I've ever heard an Oracle administrator cry out "SQL Server is so much harder to administer!" In fact, usually you hear the opposite, that Oracle is more complex (which an Oracle admin will quickly contend means it's better) and that SQL Server is too simplified.
This report amazingly contends that Oracle is "20% less complex" than SQL Server.
I have to ask - do you agree? Disagree? Please, drop me a note (swynk@sswug.org) - let me know what you think. I'll pull the responses together and if there is interest, we'll publish a quick SSWUG rebuttal and discussion (both sides welcome by the way) of the report and article. Their article seems written to stir controversy, and I suppose I'm biting on the bait, but please let me know your thoughts ASAP.
What follows is the feedback I received on this report, including people's observations about the methodology and their experiences with SQL Server and Oracle. One item of note is that the report is indeed comparing SQL Server 2000 and Oracle 10g.
------------------------------
Scott T
Coming from an Oracle background, this article has certainly piqued my interest more in 10G. It's probably a more pertinent question to ask: How many people are using 10G now, and how many of those are using the new features?
Be interesting to see if there are any "real life" stories out there comparing the 2 products.
---------------------------------
Sean M
The problem is not so much the conclusions and justification as sited in the article but rather the methodology and rigor of the "study" that the article is based upon.
A portion of the "study" that can be interpreted with validity is Appendix V - Detailed Task Descriptions. From perusing those descriptions, it does indeed appear that (qualitatively) the Enterprise Manager interface use is more comparable in the two products (in the areas covered in the appendix) than in years and versions past. A very defensible conclusion from the Appendix V - Detailed Task Descriptions might therefore be that Oracle EM has caught up and surpassed Sql Server EM, in the categories as described.
Unfortunately, the study methodology as documented is really very poor** and as a result very likely may actually largely represent extraneous differences the authors did not consider, (or are oblivious to), rather than the unsupportable statements made in the "study" about the likely effects of observable differences ascribable to DBMS Enterprise Manager interfaces.
**The study methodology as documented is really very poor for example consider:
* Significant DBA salary differentials based on specific vendor environment experience do exist, an issue which was not addressed in cost calculations.
* No indication of the level of error of measurement / estimate is included for any quoted measure.
* Oracle's DBMS supports flashback queries while MS SS does not. The benefits the capability conveys in many environments are absent in MS Sql Server; however, in that instance the authors considered and estimated metrics for a work around for SS for comparison purposes.
* The shrink database task may actually be accomplished in at most three or four steps from Sql Server EM rather than the many steps mentioned. The resulting measures thus likely may reflect the quality MS documentation, rather than EM interface usability differences.
* It is a simple matter to setup an automatic "all user database" database backup maintenance plan in MS Sql Server; while the authors considered a work around for SS in the case of flashback queries, none was considered in this case (inconsistent comparisons).
However, the unsupportable statements made in the "study" could form the basis of statistically testable hypotheses. In other words, the assertions made in the study could be examined in a more carefully designed follow up study (wherein the hypothesis could be tested at a reasonable level of confidence). For example:
Briefly, 20 randomly selected MS Sql Server and Oracle EM users of similar work experience with the respective products could be asked to perform each Appendix V task in a controlled standardized setting. Actual salary and time data could in fact also be collected and the hypotheses (the unsupportable statements) put forth in the flawed study, could then be statistically tested.
---------------------------------
Bill N
I've had plenty of experience with SQL Server, which I remember I found easy from the word go. Enterprise Manager, while it has its faults, is at least easy to start using.
I've had very limited experience with Oracle. The short summary is that it was a nightmare! There were just too many user names and passwords, and there was no clarity about the purpose each username served. Maybe that makes Oracle a better product, but I can't see how!
---------------------------------
Ken M
1. SQL Server installation is much easier than Oracle installation. However, once you have done a couple of Oracle installations on Windows, it is about the same. Oracle installation takes longer because there is more to it but it is not difficult to install to Windows. Installing to Unix is somewhat more complicated if you don't have a good X environment configured. Also you have to make kernel settings, but that is really not difficult after you do it a couple of times.
2. If you do not use third-party backup software, SQL Server backups to disk are much easier than Oracle. If you do use third-party software, it is the same.
3. Administering the internals of SQL Server and Oracle is about the same. By that I mean setting up users, setting up schemas and schema objects, adding new physical storage, and all of the things a DBA does to get an app up and running.
4. Understanding the physical structer of Oracle, it seems to me, is more difficult than understanding the physical structure of SQL Server. But that may be because I learned Sybase (which is identical, structurally and architecturally, to SQL Server) before I learned Oracle.
SQL Server is much harder to administer in a high-availability, high-demand situation:
1. SQL Server backups take a tremendous toll on the running application. You may advertize 24X7, but some of those hours are not going to perform very well because of the backups. Oracle hot backups cause a small hit on running applications. This means you have to look hard for the sweet spot in the application schedule to run backups, and your backup schedule is totally inflexible.
2. SQL Server transaction logs are extremely inefficient. Specifically, transaction logs use tons of disk space in a very short time. This can be very troublesome if one has not planned for the amount of disk these things need. For instance, when I do index defrags on just the indexes in my app that are over 40% fragmented, the transaction logs have generated as much as 90GB of backups in 2 hours, and they regularly generate 60GB. That is ridiculous. Of course if I don't back up a transaction log, the transaction log itself grows dramatically. Then when I back up the huge transaction log, it causes extreme degradation in the performance of the application until the transaction log backup is finished. I think the reason transaction logs in SQL Server are so voluminous and take so much time and resource to back up is tied in with the architecture. SQL Server keeps rollback information in the transaction logs. That is why SQL Server transaction logs are so huge. I am guessing that the reason transaction log backups with SQL Server have such a dramatic negative impact on the application is that it has to sort thru the rollback information to figure out what is still needed and what is not. And it needs to keep putting new rollback and transaction information onto the log at the same time it is backing it up. Oracle does not put rollback on its transaction logs, so they are much smaller than SQL Servers. Also, transaction log backups in Oracle are simple OS file copies, so there is no impact on DB performance.
3. The marketers of Oracle do not make enough noise about "writers don't block readers and readers don't block writers". That is a tremendous architectural advantage for Oracle. SQL Server is plagued with locking problems because of this. Our SQL Server developers are very, very good, and very, very aware of this and they have coded to avoid blocking cause by locking. But it is just a fact of life that locks must be taken for read consistency. And SQL Server rapidly escalates locks from row-level to table-level. This causes significant problems for the app. You may say that this item is not an administration problem. But who gets pinged when the DB does not perform? Every place I have worked, the DBA is at the top of the list. You have to explain and show the cause of performance problems. This takes time.
4. When an Oracle explain plan says it is going to use a particular access path, that is the path it takes, and while this can change over time, it does not change easily. In contrast to that, in our SQL Server app we have one table that we have to run stats on every 5 minutes. If we don't, there is one query (which runs quite often) that can take out all 8 of the processors dedicated to the DB and keep them occupied until we run stats on that table. That table is the most popular table in the DB. What I am saying is that the way SQL Server uses access plans is very, very brittle. I have seen much other evidence of this. You may say this is not an administration problem. If that is what you say, see item 3, above.
I have gone on long enough. I could probably write 10 more paragraphs. (For instance, I have not even mentioned the consequences of Microsoft's famous quote:"We scale out, not up". Have you considered the effect on an administrator of applying application database upgrades to a "scaled-out" database, not to mention the impact of applying Microsoft patches to a "scaled-out" environment...not to mention the additional complexity of administering a bunch of servers to scale-out on, with the attendant administration of network configuration, firewall configuration, backup configuration, etc. To do a large, industrial-grade application on SQL Server is much tougher than Oracle. Well, you could say Oracle RAC is not easy. That may be true, but you don't even have to think about RAC until the demand on your database is 10 or 20 times what SQL Server can handle without "scaling out". Oracle has a much better architecture and runs on boxes with much more capacity than SQL Server, so it will do a lot more without adding boxes. That is fact. If you believe MS's statements to the contrary BUT you have not actually experienced a high-demand application in both Oracle and SQL Server, I say to you "Good luck.")
What I am saying is that out of the box, SQL Server looks more like a toaster than Oracle does...you know, plug it in and put bread in it. But when the going gets tough, Oracle is much more forgiving and offers many more tuning options, and actually needs less tuning, than SQL Server. It is also, in my experience, much more predictable than SQL Server.
From one who has been there (and is still there).
-*-*-*-*-* Part II:
I made my previous response without reading the articles. It is interesting to me that the Progressive Studies article exactly mirrors my experiences. Equally interesting is that Rizzo does not respond on the basis of these areas where Oracle is superior, but raises points in other areas. For instance, he talks about SQL Server's ability to dynamically tune itself. I am running SQL Server in a high-demand Web environment, and if it is "dynamically tuning itself based on workload requirements and available system resources" as Rizzo says it is, it is doing an extremely poor job of it. Furthermore Rizzo's assertion that "the quality of SQL Server's auto-tuning dictates its settings rarely need to be overridden" is probably true, but not for good reasons. It's true because with SQL Server, once it's auto-tuning is done, there is no place else to go. The basic architecture of the product (not to mention the limitations of the OS it runs on) is so poor that there isn't enough magic to overcome it. I know there are a lot of variables which could cause performance issues other than the database engine. But I, an experienced DBA, have reviewed the areas of worst performance exhaustively with some very, very good application developers, and I believe our performance issues belong to the engine, not the lack of indexes or poor programming practices.
In my previous note I was comparing SQL Server 2000 to Oracle 9i, not 10g. I have no experience with 10g, and actually all of my experience with Oracle in a high-demand environment is with Oracle 8i. 8i performs circles around SQL Server, even when Oracle is running on Wintel boxes, for all the reasons I explained in my previous note.
---------------------------------
Eamonn C
I have set up a data dump to run every night, and a log dump every 15 minutes.. that is the total of my database administration work. Maybe I am missing something, but this database has been in production for 3 years now and there have been no problems. I sometimes consider looking into administration activities, but so far I am unable to discover anything that needs to be done.
My experience is that SQL Server is a zero administration database.
---------------------------------
Hao Z
I disagree.
To Maintenance Oracle database, you need understand more inside database than SQL Server. I have been working on Oracle for five years, after I changed to work on SQL Server, from set up database to maintenance, only spent a half year to make it. But I still need learn Oracle more to handle Oracle database. SQL Server have very convenience tools for new DBA to learn, it make me feel easier.
---------------------------------
Anonymous
Oracle is very flexible. Matter of fact it is so flexible that it is hard to make it stand up.
On the other hand most my contracts are for Oracle, and they pay better because Oracle is more complex and harder to make work.
---------------------------------
Scott D
A couple thoughts since I work on both.
When it comes to patches I had to apply only 1 patch for security to an Oracle server in the last 3 years. But when it comes to MS, I quickly look for more fingers. We find Oracle is more robust and enterprise-ready and therefore use SQL Server for little applications. With the improvements of the last couple versions of Oracle, day-to-day DB work is pretty much the same between the two (creating & modifying tables, etc.). I don't think at this point either vendor has much room to claim a huge victory though.
---------------------------------
Domenico
I have to say that it is beyond my grasp when I heard those who claim Oracle is better than SQL Server. I had a lotta experience in Oracle installations and all the times I have to hit my head against some obstacles and difficulties.
I tell you... SQL Server is much better than Oracle whatever point you look from at it. A better interface with the user, a better and concise managing of the tools. Last but not least an amazing Book On Line who help you very much. Oracle is a good product but it is not at all easier to administer than SQl Server and definetely I think that it is at least 20% less comprehensible than it and that does mean "Oracle is 20% harder to understand and administer!".
---------------------------------
Colin B
I once heard a Yukon architect talking about the evolution of database servers. In his talk he likened the development of the database to that of the TV. The first generation had many dials and knobs to adjust the frequency, the hold, colour, etc. The second generation of TVs had these same, but smaller, dials and knobs with an ‘automatic’ setting which could be overridden so you could use the old knobs. In the third generation you can’t override the automatic settings. Instead the interface is more about your experience than it is about the technical capabilities of the machine (eg: widescreen mode, zoom, etc)
I say that to respond to your question this morning. Even with the release of 10g, I find SQL Server much easier to administer and develop with. I will agree that 10g finally has a much nicer and faster interface than the previous 9 versions but it is still behind where SQL Server 2k has been for a long time. The problem I believe is that Oracle still expects, even requires, the user to override the defaults and adjust all the fine details. SQL Server 7 was this way, but by 2k most things were done automatically for you and you never really wanted to override these settings. In Yukon, they don’t even expect you to want to override these settings and are instead moving you to do real work. (A good example is the deprecation of the n/text data type by n/varchar(max) which removes all the headache of space allocation and special string manipulation for clob fields)
---------------------------------
Michelle V
Actually, I do agree with you. In the sense the Oracle just seems to require less "tweaking" than SQL, and has less weird "things" happen to it. But Oracle is harder to install initially than SQL, any day of the week. I have only worked with 8 and 9, but have had LOTS of trouble initially installing it. SQL 2000 goes on without a hitch, but requires more work later on.
---------------------------------
Andy C
I received 5 weeks of formal training from Oracle School in Bethesda MD and no training at all for SQL Server. I managed Oracle database for 5 years. I have managed SQL Server databases for 2 years. SQL Server is so much easier.
---------------------------------
Bob H
(Subject line: Oracle not easier)
Marketing spin fabrication
---------------------------------
Gary M
I manage both Oracle 9i and SQL Server 2000 and there is no way an intelligent, educated individual can state that Oracle is easier to manage than SQL Server. I've been a DBA for 12 years and that has never the case, and I seriously doubt will ever be.
Granted I haven't worked with 10g yet but I can't imagine Oracle matching the auto-tuning and resource management capabilities of SQL Server.
---------------------------------
David F
I have to make my changes over several database servers at the same time, so I have to use T-SQL or PL-SQL to do that. Whether it is adding an index or a column to a table, I have to do that with scripting. You can use Enterprise Manager to all of that, but if you have to do it multiple servers at the same time, it is much faster to use scripting.
Having said that, it is nice having gui tools. I think it takes too long to create a database in Oracle.
---------------------------------
Walt B
Having administered both -- I'd say SQL Server is much easier to administer. Both out of the box will run, but of course need tuning to be useful.
There's more to do with Oracle. Oracle is like flying a plane with flight cables exposed and all you get is vise-grips to fly.
There's less to do with SQL Server. You're still flying the plane and need to get to a cable, but can't. It's just as well, the other cables are hidden too.
---------------------------------
Brian N
Having administered both Oracle and Sql Server, Oracle is much, much harder - assuming you use the tools out of the box. First Oracle's install is over 2 hours long to begin with. Second a DBA's best friend the backup is terrible compared to SQL Server's. I usually don't even both with Oracles and move to a 3rd party product, then its not so bad.
Oracle can be easy, if you buy TOAD, SQL Navigator and SQL Back Track.
But by the time you've spent 5 grand on these additional tools, you end up with SQL server's out of the box functionality, but now you have 3 other products to support.
---------------------------------
Mark B
Mate, I'm with you - although I'm comparing SQL2K with Oracle 8i (not 9i or 10g).
The most obvious complaint is backup and recovery - on Oracle it can be like pulling teeth. In fact, most places I have come in to have deliberately left Archive Logging disabled and run either Cold Dump or Exports than have to get their head around rman. There may be some smarter, lower level things you can do in Oracle - and this can be critical at times - but for the day-to-day DB's I tend to work on - Oracle is overkill and a burden to manage.
---------------------------------
Joe V
I have had to administer both databases and the constant care and feeding of Oracle is way much more than SQL Server. You can have Oracle automatically extend table spaces but you still have to monitor it or it will get you in trouble. Recovery is also more complicated.
---------------------------------
Deji A
I'm not an Oracle guy, but an analyst/programmer with about 20 years experience in languages and databes ranging from assembler to java to various databases. Over the past 5 years, I've been using SQL server (v7 & 2000). This is an easy skill to pick up for anyone with relational Db experience, especially with all the wizards and GUI-oriented tools inclused with sql server. A few months ago, I had to learn DB2 for my latest project, and I could not believe the amount of extra learning of concepts and approaches that was involved. This included everything from administration tools (can you add/drop columns using control center?, etc) to SP programming techniques (eg can you have dynamically generated SQL in a UDF?, in a SP?).
As somebody who has spent a lot of time complaining about the flakiness of microsoft products, I discovered that this is a small price to pay for the availability of technical information about m/s products. To find a technical answer to a DB2 problem was like trying to squeeze blood from a stone. This is where Oracle comes in: I often found when typing my DB2 question into google that the answer comes from an Oracle user group or discussion group, and I find the concepts of DB2 and Oracle to be almost identical, unlike SQL server.
Once you've managed to find the info about how to do something in DB2 (& oracle?), the resulting DB os SP feels a lot more solid than the sql server equivalent. It just takes 5 times a long to find the answer. I guess this is why an experienced oracle/DB2 DBA is far more valuable than an experienced sql server dba. This is because even a relative novice in sql server can quickly get up to speed with sql server, with the amount of wizards, gui tools, and information available. A DB2/oracle dba learns from bitter experience, with no microsoft TechNet equivalent, which is what they get paid for.
I think sql server compares to oracle/DB2 in the same way that VB compares to C++. The former is very easy to master and become 'productive' with, which is why millions of people use it to write largely garbage programs; the latter imposes discipline on the developer, which often results in more robust coding practices (but not always).
