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.
23 Comments
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
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.
Thanks Boris! Let me know if you need any help doing that move 🙂
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
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.
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.
per your no equivalent, see our new Oracle Database 12c Multitenant pluggable databases feature.
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
Looking at this with 2012 eyes, I tend to agree with you Martin. Of course, I don’t do as much tinkering with SQL as I used to, if you know what I mean 😉
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
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.
Jens, thanks for sharing your thoughts and that link! I immediately bookmarked it, and I’m sure it will come in handy soon 🙂
It seems that I made a typing error in the link before (an extra quote). Although most people hopefully can guess the right url, I’m posting the link here again – hopefully without errors: http://troels.arvin.dk/db/rdbms/
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.
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.
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!
Ah, thanks for clearing that up.
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.
Would SQL Trace & tkprof be equivalent to SQL Server’s profiler ?
How about DBMS_PROFILER for PL/SQL code ?
(Note: I’ve never worked with SQL Server)
Pingback: Tweets that mention Oracle vs SQL Server, Take II | 140,000 Characters or Less -- Topsy.com
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.
Totally agree. Updated to reflect your advice. Thanks!
Pingback: Oracle Vs SQL Server | 140,000 Characters or Less