Oracle vs SQL Server, Take II

thatjeffsmith Expired 23 Comments

Tell Others About This Story:

Missed PT I?: Read Part I my favorite aspects of each RDBMS platform
Update: Read PT III where I talk about monitoring indexes

I had the pleasure of hanging out with @oraclenerd and @SQLChicken earlier this year in Tampa, FL. It was remarkable for a couple of reasons, but what I remember most is playing translator. When @SQLChicken would rave about one of the SQL experts on Twitter, I would relate to @oraclenerd how that person was a lot like one of our Oracle experts. It was a fun night even if I was on the sidelines for much of the spirited debate. I knew it was going to be OK when they went out for ice cream after I left.

I am currently suggesting that @SQLChicken abduct @oraclenerd next month for the SQLSaturday coming to town. I think it would do wonders for any Oracle professional to attend one of these events for reasons I’ve previously stated. But what is the equivalent of SQL Saturday for the Oracle crowd? Who is the Buck Woody (and for that matter Little Buck) of the Oracle domain? How do you do OBIEE in SQL Server?

For this blog post, I want to take a few minutes to help someone who is getting ready to step into ‘the other side’ by offering some reference points. It is generally not fun starting out from scratch in a new endeavor. I hope this ‘translation guide’ helps. I knew I could have used this several years ago!

Note: These are MY translations and may differ based on your personal experience.

Technical Jargon

Microsoft SQL Server Oracle
instance (server) database
database schema
schema No direct equivalent.
login user
user user
permission privilege (priv)
indexed view materialized view (snapshot)
T-SQL PLSQL (mostly)
Error Logs Alert Log
Log file Redo Logs
Execution Plan Explain Plan
Re-Compiles FLUSH SGA (wipes out ALL cached plans)
OR in 10/11g use baseline control (not nearly as common)

The underlying fundamentals of a RDBMS hold true for both SQL Server and Oracle. If you ‘know’ one, you will be able to transition into another. Some things are actually exactly the same but have different names. Others are similar but have their own twist in implementation or architecture. Clustering and HA are examples of where you would see major differences, and partitioning would be a place where you’d have only minor differences (other than available partitioning schemes in SQL Server).

Tools

Microsoft SQL Server Oracle
SSMS OEM
Query Analyzer SQL Developer
Server Side Trace DBMS_SYSTEM|Monitor|Support + tkProf
Profiler OEM can be used to start a trace.
Maintenance Plan No direct equivalent. (jobs?)
DMV SYS View
Default Traces No direct equivalent.
SSIS OBIEE
PowerShell SH (*NIX)
Clustering RAC (kind of, but not really)
Log Shipping ?
Mirroring Streams|Data Guard|GoldenGate

More GUIs from Microsoft – surprise, surprise! More packages and command-line tools from Oracle. However this is quickly evolving. Also, I doubt that this point rarely surfaces as the deal-breaker when it comes to choosing one platform over the other. It’s more of an issue for the people who get to support the database after-the-fact.

Community

Microsoft SQL Server Oracle
MSDN OTN
PASS ODTUG|IOUG|UKOUG|…
PASS Summit Oracle Open World
PASS Chapters Local Oracle User Groups (independent)
RMOUG, SOUG, …
SQLBits UKOUG Annual Conference
SQL Saturday No direct equivalent.
SQL Server Central OraFaq|OTN
SQLServerPedia OraDBPedia
Buck Woody|Paul Randall|Brent Ozar Tom Kyte|Cary Milsap|Alex Gorbachev
#SQLHELP #ORACLEHELP (barely)
SQL MVP Oracle ACE

Both communities are mature, strong, and active. The major difference I see is the major fragmentation on the Oracle side. I would say this is due a combination of factors, but the amount of time is probably the biggest one. I would say that PASS in 10 years will look drastically different today, and would not be surprised if it starts to splinter into different groups. Don’t assume that because there is no unifying ‘PASS’ that local groups of Oracle professionals do not congregate. There are many, and they are very dedicated.

What did I leave out? What did I get wrong?
I mentioned previously that this was a translation, so I admit I’ve probably lost a bit in that translation. There’s a comments section, feel free to blow it up!

Did you miss my first take on Oracle vs SQL Server? Read it now.

Tell Others About This Story:

Comments 23

  1. Hi Jeff,

    Thank you very much for putting the Terminology Translation. For years I have worked on SQL Server and now for a client I have to setup and work in a Oracle environment. From SQL Server prospective I was thinking about creating different database. But in that case I will end creating that many Services/SID here which will consume a lot of machine recourses. SQL Server Database -> Oracle Schema saved me here. Now I understood how to deal with this.

    Again Thanks a lot.

    Anupam

  2. Jeff, I am a big fan of yours! This article is right on spot as my company moving to Oracle from SQL Server.

    I would like to correct you though – SSIS is not the same as OBIEE. SSIS is an ETL tool so it would be ODI or OWB (obsolete now). OBIEE would be a combination of SSRS, SSAS and Sharepoint.

    1. thatjeffsmith Post
      Author
  3. Hi Jeff,
    I have exposure to Ms-SQL for a long time in my career and the worst problem I met with was issues with DeadLocks. I hope Oracle is more consistent in concurrent sessions.
    Thank You,
    VC Suresh Kumar

    1. thatjeffsmith Post
      Author

      In my experience it comes down to how well the code is written that determines if you get dead-locked (Oracle or SQL Server), however in Oracle doing reads generally won’t ‘deadlock’ or block someone else’s.

  4. You could argue both Log Shipping or Mirroring (Performance) as being equivalent to Data Guard ASYNC
    Mirroring (Safety) as being Data Guard SYNC
    filegroup = tablespace
    clustered index = IOT
    differential backup = differential cumulative
    incremental backup (rarely used) = differential incremental
    I tend not to agree with Database being the same as Schema because you can’t get transactional consistency between SQL Databases. You can get consistency over SQL schemas though so I tend to think Schema=Schema, Database=no equivalent.

    1. thatjeffsmith Post
      Author
  5. Jeff,
    thanks for the comparison, it’s always worth to know about the other side of the fence.
    I’d map SQL Servers “Log Shipping” to Oracles “Data Guard”?
    From my reading they are as close as they can be – given the different core implementations.
    Martin

    1. thatjeffsmith Post
      Author
  6. JeffS Post
    Author

    Schmoe – wow, I’m gobsmacked that my site made a corporate filter list! How cool is that?

    It’s very common for shops to have 2 database vendors in-house. One usually plays the dominant role while the other allows the company to keep the primary from going crazy on pricing.

    Or, you have developers that want to build something quickly or quietly and go about getting what they want. Then someone in IT wakes up and discovers they have several hundred instances of MySQL running and no ones backing it up.

    If you’re really curious about giving Oracle a spin, try out our Dev Days Virtual Box image. It’s a fully functional 11gR2 database running on Linux – all completely free. It comes with a series of lab exercises so you can get more comfortable with the technology.

    http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

  7. Very useful article. A couple of comments from my own experience (working as a SQL Server developer in an organization with a lot of Oracle people and Oracle systems).

    The database.schema.object hierarchy exists in both Oracle and SQL Server – and the basic meaning of a schema in SQL Server is the same as in Oracle: a logical collection of objects inside a single database to which permissions (privileges) can be assigned. In Oracle there is a one to one correspondence between a user and a schema – this is not the case in SQL Server.

    In practice however, a lot (in my experience almost all) of SQL Server databases are not using schemas to separate different parts of a database – instead multiple databases placed on the same instance are used to provide a similar separation (matching the term “translations” in the “Technical jargon” table).
    Regarding the Tools section. “Query Analyzer” is an obsolete term in regards to SQL Server – being relevant only to people working with SQL Server 2000 or earlier versions. Since 2005, SSMS has been the tool both for query editing (replacing Query Analyzer) and for management tasks (replacing Enterprice Manager).

    The SQL Server DMV’s are stored in the sys schema – apparently similar to Oracle if I read the table correctly. BTW, examining the sys schema in SQL Server can be highly recommended – most database objects have a corresponding sys view – i.e. sys.databases, sys.tables, sys.views, sys.columns etc.

    One thing that I think could supplement your translation tables is a T-SQLPL/SQL guide. “Funny” differences like Oracle’s use of the DUAL table (not used in MSSQL), CREATE OR REPLACE (Oracle) vs IF EXISTS (..) DROP/CREATE (MSSQL), IDENTITY columns (MSSQL) vs Sequences (Oracle) are various obstacles a developer or DBA has to overcome before being able to master both database systems.
    I’ve used Troels Arvin’s Comparison of different SQL implementations for several years – I’m not sure whether it is still being updated, but according to the legend it covers (among others) SQL Server 2008 (not R2) and Oracle 11g Rel 2.

    1. JeffS Post
      Author
    2. I’m really curious about this:

      “…a SQL Server developer in an organization with a lot of Oracle people and Oracle systems…”

      Like, how does that situation come about? I’m in a similar situation, and have some experience with SQLServer versus next to none with Oracle. I feel I should try to use SQL Developer just because the big mama tables here are all Oracle…but still on the fence.

      I can’t get your webpage here at work but I hope to remember to do so later.

  8. I’d put OTN as the equivalent to SQL Server Central. The forums are there [http://forums.oracle.com/forums/main.jspa?categoryID=84] plus articles, [http://www.oracle.com/technetwork/dbadev/index.html]

    Its hard to put that one-to-one mapping for some things (especially Community), and even harder as Oracle (the company) tries to incorporate the Java community (and MySQL/Solaris…).

    Maybe map the SQL Server MVP to the Oracle ACE program.

    1. JeffS Post
      Author

      Good points Gary. I was hesitant to even list SQL Server Central since it’s corporate sponsor is NOT MSFT, but I know many SQL folks use that a resource…and that’s kinda why I listed OraFAQ since it is also independent of Oracle.

      As for MySQL, Java, Sun hardware components of Oracle, I’m pretending they don’t exist and am worried just about the core RDBMS technology that Oracle started out with. I’d hate to even consider PeopleSoft, Siebel, Fusion, & JD Edwards!

  9. JeffS Post
    Author

    So the SQL Server profiler is a GUI that allows you to setup a trace, whereas in Oracle we just have our packages. It helps tune, replay, and do some other really cool stuff.

    DBMS_PROFILER is great for finding bottlenecks at the line level of your stored procedures in Oracle, but in SQL Server, most stored procedures are just a collection of sql statements, so a server side trace or profiler run will catch the info that you want.

  10. Pingback: Tweets that mention Oracle vs SQL Server, Take II | 140,000 Characters or Less -- Topsy.com

  11. This is an excellent effort so well done and thanks. The only detail I would pull up is that you say server -> database. I know you actually mean instance -> database , but there is no harm in me highlighting it since the term “server” is fairly ambiguous.

    Thanks again.

    1. JeffS Post
      Author
  12. Pingback: Oracle Vs SQL Server | 140,000 Characters or Less

Leave a Reply

Your email address will not be published. Required fields are marked *