Oracle Vs SQL

Personally I'd stick to Oracle, but as always, it depends on various things ;-)
- what's the server OS?!
- what about the costs?!
- large tables should be no problem for both options when set up correctly ;-)
- .....

Windows Environment
Cost here is Not an Issue

If cost is not an issue, then you should defintely aim for Enterprise Edition (if not already running): all the nice features, like partitioning won't be available if you're using "lower" versions...

Guy Hengel [angelIII / a3]

Actually we are dropping the old application, new database structure going to be built.
it is a multi tier ERP, no use to upgrade the existing database.

Alexander Eßer [Alex140181]

You mean Oracle ?

Sorry, I missed that. Yes, I mean Oracle ;-)

I really like the table partitioning and data compression options that Oracle offers.  These make handling large tables much easier and more efficient.  (Maybe SQL Server offers similar things?  I'm not an expert on SQL Server.)

I ran Oracle8 and Oracle9 databases on Windows for years and I liked that combination.  We were able to support 500 concurrent users on a 32-bit Windows system, so supporting 90 users now on 64-bit Windows should not be a problem.

But, we switched to Linux when we upgraded to Oracle10 some years ago.  Linux is much more stable than Windows is, but I do miss the Windows Performance Monitor.  That tool is very handy to be able to see: CPU, memory and disk I/O all in a real-time GUI display.

I can't tell you whether Windows or Linux is best for you.  That is a decision you have to make.  But, if you choose SQL Server for the database, you can't have the stability of Linux in the O/S.

ms sql server also has partitioning options.

Just remember the Windoze way of "fixing" things: Control-Alt-Delete...

Just remember the Windoze way of "fixing" things: Control-Alt-Delete...

lol ;-) good point
But even Linux might have troubles... Recently we had an issue with one of our Linux servers: Kernel Panic ;-)

You could still go with Oracle standard edition and use some Zero Cost Partitioning method.

SQL Server should cost you vastly less in this case, esp. since you need only Standard Edition.  Most of the people I work with helping convert to SQL see a big performance improvement from SQL Server.  Bonus: if you install them on the same licensed CPUs, there is no additional charge for many SQL Server components, including OLAP, unlike with Oracle (at least the last I looked at SQL licensing, but on which I am certainly NOT an expert).

You should also look at converting any SEQUENCE columns that you assign upon row INSERT to "Identity" columns in SQL; vastly easier, no trigger needed.  And look at snapshot isolation level; you may need/want to enable that to have SQL do the same type of locking as Oracle, at least initially.

25M rows is nothing for SQL Server, assuming the table has the proper clustered index.  That's true for RDBMS's in general, but particularly for SQL Server.

[SQL 7.0 and SQL 2000 did have issues with larger tables.  SQL 2005 far less so.  SQL 2008+ does not.]

SQL does not have some of the more esoteric indexing options available in Oracle, but most people don't use those anyway :-) .

Call or no call to Oracle, no way they can match SQL's cost of the db engine, OLAP system (SSAS) and Reporting System (SSRS) all on the same licensed CPUs for only the cost of the db engine.

Yes, retraining is a very big issue when switching from any DBMS to another.  But it's vastly easier to go from Oracle to SQL than from SQL to Oracle.

Finally, I suspect you will get vastly better performance from SQL than from Oracle 8.  The Oracle 8 "optimizer" was still the simplistic "rules-based optimizer", IIRC, rather than cost-based.  Don't know about later versions of Oracle's optimizer because I quit being an Oracle DBA after Oracle 8.

This is hardly a fair comparison: "I suspect you will get vastly better performance from SQL [Server, current version] than from Oracle 8."  Oracle8 is from 15 years ago.  The newer versions of Oracle will also give "vastly better performance than Oracle8".  In my opinion, Oracle's performance beats SQL Server's performance.  But, both are likely very good in most cases.  Oracle does give you more tuning options: for different server hardware, storage system charcteristics, numbers of records, numbers of users, etc.  The flip side is: SQL Server can be easier to manage (because there aren't as many configurable options).

And yes, SQL Server integrates easier with Microsoft tools.  I agree with slightwv's recommendation; "go with what your staff is comfortable with".

I agree with slightwv's recommendation; "go with what your staff is comfortable with".

Me too. This should be the "best" way to go either case ;-)

I also know from my time at a $25B+ company that with Oracle, applying the smallest update could cause massive issues with queries, performance, etc..  Even full SQL Server Service Packs didn't cause us anything remotely close to that level of issues; indeed, we very rarely had any issues from a SQL upgrade.

In my 20+ years supporting Oracle-based systems, I have not seen small updates cause massive issues.  Yes, when Oracle switched from the rule-based optimizer to the cost-based optimizer about 15 years ago, that caused some issues but only for some queries/programs.  And this was certainly not a *SMALL* update!  That came with a significant database version upgrade and it was widely publicized at the time, and it was possible then to use optional parameters to continue with the old optimizer functionality until developers had a chance to change the programs that needed to be changed for the new optimizer.

I know when the Int'l Paper Oracle team went from even 10/11.number.number to 10/11.number.newnumber they had massive issues, esp. often performance issues.  I consider a sub-sub-version number to be a relatively minor change.  That happened over and over again.

With SQL, we would go from SvcPack1 to SvcPack3 without a ripple.

Let's be more fair with one other thing, too.

Often Oracle runs on $500,000+ hardware, whereas SQL Server is on $50,000 hardware.  Yeah, in that case, I'd certainly expect Oracle to run better too.  Give me half a mil and I'll give you great SQL response time as well.

>>The Oracle 8 "optimizer" was still the simplistic "rules-based optimizer", IIRC, rather than cost-based.

The Cost-Based Optimizer (CBO) was around in 8i.  Granted, it wasn't that smart back them but it was still around.

8.1.5 docs:
http://docs.oracle.com/cd/F49540_01/DOC/server.815/a67775/ch7_opti.htm#3734


>> I consider a sub-sub-version number to be a relatively minor change.  

Oracle is known for sneaking MAJOR changes and at times complete rewrites of code in minor version numbers...

>>Let's be more fair with one other thing, too.

Let's be fair:  Not a fair comparison there.  Apples to apples, Oracle will likely perform just as well as SQL Server on the same hardware.

I also stand by my cost suggestion.  Database engine to database engine, I think they will be about the same.

Now if SQL Server bundles some additional products, I don't know but I'm betting it will still be in the same ball park.

Each vendor will do whatever it takes to take a customer from the other...

No chance.  IP had HUGE purchasing power, and Oracle still cost us orders of multitude more than SQL Server, esp. on the hardware side (with Grid).

The questioner has said his server environment is Windows.  If this isn't changeable then SQL Server on Windows > Oracle on Windows.  On the other hand, SS/Win = Ora/Linux on the same x86 hardware as far as I've been able to tell.  The SS programming team has access to low level Windows tweaks that the Oracle people don't even know about.

>>No chance.

I have this debate from time to time with SQL Server folks.  I stand by what I stated.

I don't doubt that you may have spent a lot more on Oracle than you did SQL Server.  My argument would be did you really need all the Oracle that you bought?  Oracle does have a really aggressive Sales force and they are good at their jobs!

Many people buy Oracle Enterprise Edition when they really don't need it.  Check out Oracle Standard One MSRP then SQL Server MSRP.  Since you mentioned Grid, did you buy RAC?  Oracle loves to sell that...  Many times it isn't necessary.

A quick checklist of options and features between the versions:
http://www.oracle.com/us/products/database/enterprise-edition/comparisons/index.html

Last time I took the time to look it up, the MSRP for Standard One and SQL Server were about the same price per core.

>>The SS programming team has access to low level Windows tweaks that the Oracle people don't even know about.

Then you need better Oracle DBAs!  The DBAs need to work with the Sys Admin staff.  Part of the job of a DBA is to understand the OS they are running on.

IP has Oracle experts.  They have a huge legal dept and aggressive push back themselves.

Oracle simply is much more expensive, no matter what spin Oracle puts on it.

Again, show me the Oracle equivalents of SSAS (OLAP engine) and SSRS (Reporting system) that Oracle does not charge for -- no such thing!

"Then you need better Oracle DBAs!  The DBAs need to work with the Sys Admin staff.  Part of the job of a DBA is to understand the OS they are running on."

I was partly joking here; I didn't mean your local DBA. I mean the people at Oracle who write Oracle DBMS versus the people at Microsoft writing SS DBMS. One of those two teams has a home field advantage when writing to run on Windows OS.

This is going to be a fundamental discussion...
Could we all (Oracle AND SS) probably agree to the following (regarding the initial question):

1. If you're using Windows based (DB) server AND your team feels comfortable with SS (no/little learning required), you should definetely stick to Win + SS.

2. If the OS (DB server) doesn't matter (Windows, Linux, whatever) AND your team feels comfortable with Oracle (no/little learning required), you should definetely stick to Oracle.

3. Whatever DB software you're going to use, you should always check (beforehand) all the neccessary options/features of all the different versions/editions. E.g.: why do you want to buy Oracle EE if you're not going to use any EE relevant feature??!! This IS a cost factor!

the good old database war between SQL and Oracle will probably last a lot longer than the cold war ...
and it's probably gonna be a battle forever... ugh do i see HANA peeping at the horizon ?

the best advice is "stick with what the people are comfortable with and are using today"
someone once said:
  "don't break what isn't broken"
or
  "don't change a winning team"

unless you have time and money and want to waste some of both

upgrading from 8i to 11g doesn't require a change of the ERP at all
providing you don't touch the 8i oracle client yet, some apps are client sensitive
you could change the ERP later on
but some legacy systems are only good for the trashcan compared to modern needs

if you want to start from scratch
also check what exists and at what price you can get it
(usually cheaper than inhouse development)
some of those items (like  SAP) are customizable

"stick with what the people are comfortable with and are using today"
"don't break what isn't broken"
=
don't improve anything as long as it's minimally functional now...:
GPS?  Who needs GPS, people already know how to use a compass, right?
who needs more than 640K anyway?
smaller transistors -- why? they're already only a few mms thick

Sorry for being so blunt, but that type of complacency has killed many companies.  Kodak for one.


Move forward rather than stand still when you can.  Learn more not less.

That's not what Geert and others here were saying and or meaning. Do not twist words in order to somehow improve your opinion!  We're saying: if you're delivering mails with your bicycle,  keep on doing this, BUT use a car ;-)

And I'm saying if a plane is available, consider that instead of a car!

They're saying stick with Oracle simply because you're familiar with it.

I'm saying, if SQL would be better going forward, including based on cost, go ahead and switch now.  Don't complacently continuing using a less-applicable product, and vastly more expensive product, just because you're used to it.

Sure, there's some learning curve, but not nearly as much as going from SQL to Oracle.  The learning curve has to be carefully weighed vs the advantages gained, not just avoid change for the sake of keeping something more familiar.

"some ... items (like SAP) are customizable".

I disagree with that statement.  I don't know if you are also looking at buying a complete system instead of building one, but if you are, here are a couple comments about the two big ones: SAP and Oracle's EBS.

SAP advertises that it provides "industry best practises".  That may be true for at least some parts of the SAP system.  But, the flip side is: customizing SAP is not encouraged.  If you buy SAP, you will run your business the SAP way (you won't have a choice).

Oracle advertises that their EBS system is: "configurable", meaning that it is flexibile and that even without custom programming, it can work different ways for different organizations.  And, that is true.  (Plus, you can also add custom code to it if you want to.)  The flip side of this is: Oracle's EBS system is not optimized to perform efficiently for any particular business or industry.

SAP was designed and built by one organization to one set of standards using one set of tools.  That has advantages.  But, much of the design was done before the days of relational databases and it certainly does not take advantage of the advanced features of the modern relational databases that it runs on.  SAP is advertised as being "database independent".  Actually, it abuses any database, but Oracle is more tolerant of its abuse than SQL Server or DB2 are, so more SAP installations run on top of Oracle databases than any others.

Oracle's EBS system is a conglomeration of a number of separate systems (GL, Purchasing, Order Entry, Inventory, etc.) that were designed and built at different times by different organizations using different tools and standards, then purchased by Oracle and modified (at least a bit) so they can sell it as an integrated package.  The latest version though still uses two fundamentally different screen programs and may still include two very-different reporting programs.

And I'm saying if a plane is available, consider that instead of a car!

If a rocket is available, .... bla bla bla: this kind of "discussion" is somewhat like kindergarten talk.
I got no problem at all with you being a pro-SS and I really don't want to bother, but your "discussion style" will lead us nowhere...

They're saying stick with Oracle simply because you're familiar with it.

Who the hell said that?! This is simply not true!

...not just avoid change for the sake of keeping something more familiar...

Again: you're not listening/reading carefully ;-)

...Don't complacently continuing using a less-applicable product...

OK, I'm outa here... lol..... If you want to "end" up using just Windows based OS/machines in order to stick to your SQL Server monster, go ahead. Meanwhile, I (and many others too) will be so fu**ng comfortable with selling my poor Oracle based application on virtually every kind of OS/machine...

>SAP was designed and built by one organization to one set of standards using one set of tools.
i'm not a complete fan of SAP and neither of Oracle EBS.
sap bought a few companies with good products too, so don't give sap the credit for all the good things, solman isn't one of their best products
> sell it as an integrated package
that integrated is not completely finished i heard :)

Scott, is SS really better than Oracle ?
i bet every sample you give on SS can be equally matched on oracle
and vice versa
i guess flashback query will be a little harder in SS

cost ?
i've heard of deals of 11K per oracle enterprise cpu license

i bet every sample you give on SS can be equally matched on oracle
and vice versa

On a much larger system (Terabytes, not gigabytes like the case here), I'm inclined to say oracle may have a slight edge.  There are many more "knobs and dials" in Oracle but that of course also translates into additional administration costs.  Of course I might be splitting hairs.

However, there's no question in my mind that SQL Server is the easier database to develop against, especially in the case of a .net application.

i've heard of deals of 11K per oracle enterprise cpu license

General rule according to the DBAs around here is that most businesses only pay half of list for Oracle.  So it is hard to compare costs and on a smaller system.  But that does leave a couple of things in my mind:
1)Sql Server is easier to administer, especially by a non-dba, and that is perfectly feasible in the case of a small 11GB database.  I'm certainly not advocating such an idea as that's often the way SQL Server "issues" get propagated as a problem with the software rather than a problem with the setup.  Oracle is, in my limited experience harder to administer, but I think some of that is on purpose.  Oracle is more likely to get dedicated DBAs and so it's rare for it to suffer from the same appearance problem.  Of course that increased likely hood also comes with a professional salary that you have to pay.

2)It's really tough to compare editions since many features in an "edition" will be located in an add on.  Need TDE or AD integration?  You need the advanced security pack.  
http://docs.oracle.com/cd/B28359_01/license.111/b28287/options.htm#CIHJJBGA

Interesting discussion...

Just a couple of points I am trying to pick up from @m_jundi

>>  Actually we are dropping the old application, new database structure going to be built.
1) Who writes the code for the application ?
2) Going from 8i to current Oracle is not entirely trivial ?
3) If all "in-house" skills, then do the skill sets adequately serve the tasks ahead, or, augmented with specialists as required ?
4) if changing code base and changing data base, then is there a training regime in place and/or the timeframe to ensure an efficiency gain in that transition ?
5) What else ? Reporting, imports / exports, 3rd party interfaces, centralised or hugely distributed, HA / DR initiatives... etc

In terms of performance : Afraid SQL has issues with Large Tables and Performance.
1) both will perform, but will likely require ongoing housekeeping and tuning in the early days of transition.
2) performance is (arguably) more a function of the physical environment as opposed to "which database" especially when the choice is either Oracle or SQL Server.
3) To maximise performance (despite the above comment) then the physical environment should be built with the target operating environment having been decided.

In some regards the statement reflecting "what your people are most experienced with" whilst largely true, can also be a noose.

In some environments, the fact that there is change and learning can further engage and invigorate the workforce in such a way that every conceivable efficiency is sought out. Keeping the same environment (albeit current versions and significant difference in that), doesn't always bring about the same level of dedication and commitment to learning and growing the expertise in a new platform.

In that regard, I would not restrict your thought process to "current skills" as much as assessing the work place environment and the potential gains by introducing something new as a contrasting perspective for efficiency (being effectiveness on so many levels).

So, if you wouldn't mind answering some of the above questions, we might be able to provide a more impartial guide and less bipartisan according to our own DB loyalties.

As it stands SQL v Oracle as DB both work well, and will serve your corporate requirement well.

>>  In terms of performance : Afraid SQL has issues with Large Tables and Performance. <<

Not on equal hardware.

Oracle gets put on a $500K+ box, and then people compare performance of that to a $30K SQL box.  Makes it seem that "Oracle runs faster".  Do the same due diligence on SQL hardware -- including separate RAID sets for logs and tempdb(rollback), more spindles, etc. as always gets done for Oracle -- and SQL will do just as well.


>> i've heard of deals of 11K per oracle enterprise cpu license <<

A rumor started by Oracle probably :-) .

>> On a much larger system (Terabytes, not gigabytes like the case here), I'm inclined to say oracle may have a slight edge. <<

That definitely used to be true.  I'm not sure with DataCenter editions and where MS themselves do the setup.  You throw enough RAM and spindles at it, and you can get performance from any size db.

One of the clients I helped convert switched to SQL because of the huge cost in Oracle of having to upgrade to be able to encrypt even a few columns -- which he absolutely had to do, since it was credit card data.

That definitely used to be true.  I'm not sure with DataCenter editions and where MS themselves do the setup.  You throw enough RAM and spindles at it, and you can get performance from any size db.

Yeah but RAC does provide a scale out solution that sql server does not have at this time.  I think you have to have Terabytes to justify the cost over just going with "big iron", but I do look at it as an "edge".  There's other little tweeks like sequence caching and bitmap indexes that are only going to matter in the absolute largest systems, but they do exist.

One of the clients I helped convert switched to SQL because of the huge cost in Oracle of having to upgrade to be able to encrypt even a few columns -- which he absolutely had to do, since it was credit card data.

Column level encryption can be handled at the code level.  You only need advanced security if you want it to be transparent (TDE).  That difference also exists between sql server standard and enterprise.

Anybody care to wager what the point distribution is going to look like on this one :)?  The OP has his work cut out for him :P

>> Column level encryption can be handled at the code level. <<

I'm not sure that's allowed for certain credit card info.  There are very specific legal minimum requirements for how that encryption must be handled.

>>I'm not sure that's allowed for certain credit card info.  There are very specificlegal minimum requirements for how that encryption must be handled.<<
As long as you don't save that CC data permanently and mask it after processing,  everything is fine. Otherwise you'll have to be certified the PCI way ;-)

I'm not sure that's allowed for certain credit card info.  There are very specific legal minimum requirements for how that encryption must be handled.

Actually no, PCI-DSS, 3.4 just specifies "Strong cryptography, with associated key-management processes and procedures."  It doesn't go so far as describing the mechanism responsible for doing such encryption.
https://www.pcisecuritystandards.org/documents/PCI_DSS_v3.pdf

I'm not an expert on this, I trusted the client's statements.  Have you actually tried to implement "Strong cryptography, with associated key-management processes and procedures" and then have it passed by an independent auditor?  It's not as easy as you make it sound.

As I said before: as long as you do not save the CC data permanently (cc number and cvc are most critical here), you're fine. But if you want to perform payment processing or something else where storing this data would be neccessary, then you'd have to stick to te PCI conventions & have to be certified -> VERY expensive, trust me, we/I did some investigations on that subject. We ended up in using 2 PSP (Payment Service Provider) for storing CC data & handling payments...

Like most vendors, they were saving the number so the customer could use it again with them without reentering all the info.  They had to use full TDE (SQL), but Oracle wanted huge money for their version.  They still had other steps they had to take to pass the audit (or cert it might have been, I'm not sure).

Dealing with CC and/or payment data (properly) is always a pain in the neck ;-)

I'm not an expert on this, I trusted the client's statements.  Have you actually tried to implement "Strong cryptography, with associated key-management processes and procedures" and then have it passed by an independent auditor?  It's not as easy as you make it sound.

Regardless in the difference of amount of work there might be between different methods, that does not mean you must pay for advanced security or enterprise edition in order to have PCI Compliance.  That is the point I'm trying to make relative to the SS v. O question.

That said, an auditor would not care one way or another how you AES encrypted (for example) a column of data.  It's the same algorithm whether you used DBMS_CRYPTO or used TDE.  Key/Certificate management processes must be developed in either case.   Ironically, from a system side, I believe the oracle wallet is used in both cases.  That said, even volume level encryption is permissible according to the PCI documentation (of course then you'd have to make sure your backups are also encrypted and there are additional requirements on access control).

Like most vendors, they were saving the number so the customer could use it again with them without reentering all the info.

Tokenization, if offered by your payment processor, is often the best route.  Let it be somebody else's problem ;).

>>  that does not mean you must pay for advanced security or enterprise edition in order to have PCI Compliance.  That is the point I'm trying to make relative to the SS v. O question. <<

The client told me that Oracle charged (a lot, apparently, since there was a per-user fee involved as well) to have Oracle encrypt the data column such that it remained encrypted at all times (as required for cc data).  I'm not an expert on Oracle licensing, but it rang true to me.  This particular client was not a large shop, so they wouldn't been given any extreme discounts.

That's the point I'm trying to make as well:

It seems that everything in Oracle beyond standard db functioning is an extra charge.  That really adds up, esp. when it includes per-user charges.

The client told me that Oracle charged (a lot, apparently, since there was a per-user fee involved as well) to have Oracle encrypt the data column such that it remained encrypted at all times (as required for cc data).  I'm not an expert on Oracle licensing, but it rang true to me.  This particular client was not a large shop, so they wouldn't been given any extreme discounts.

Well your persistence has paid off - I kept reading and I'll have to tip my hat on this one :).

Its not the encryption that's the issue.  PCI-DSS 3.6.6 Describes split management of the keys (two people), which you're not going to get with DBMS_Crypto.   I hate being wrong but I love learning new things ;).

>> It seems that everything in Oracle beyond standard db functioning is an extra charge.

While normally a concern, costs are NOT an issue in this case as per post : a39949854

Windows Environment
Cost here is Not an Issue

And Scott, you copied my quote in your post a39959151 Except, that wasn't my quote it was from the Question Header and was pointed out that the concern for performance should not be an issue. IE no need to be "afraid". The price point defence and the subsequent comment about Oracle license is all costs which is NOT an Issue.

The reason why I raise this, is all the extra talk about costs adds a rather large volume of potential "noise" and I am waiting for m_jundi to chime in... At this rate, our Asker might be excused for missing my request.

>> Cost here is Not an Issue
if that's the case, adding an extra swimming pool for my backyard shouldn't be a problem :)

>>  Cost here is Not an Issue <<

"Healthcare.gov" contractor? :-)

Do we need to move this thread to Politics?

Any Issues for Both Products in Vmware Environment ?

as long as the database files stay on real/SAN disks, and not on virtualized disks, any db software shall work OK in VMware.

Part of that is discussed in a bit more detail from VMware (as if one-eyed perspectives weren't enough)...

http://www.vmware.com/files/pdf/techpaper/vmw-understanding-oracle-certification-supportlicensing-environments.pdf

Otherwise you need your Oracle support login to see the real thing...

Really interesting discussion. You might also find this direct comparison between Oracle Database and SQL Server from the IT Central Station user community to be helpful: https://goo.gl/D3aW7e