SQL SERVER and Oracle Differences and similarities for

SQL SERVER and Oracle Differences and similarities for the DBA Marcelo Goncalves Adade @marcelo. adade

Sp_help. DBA(main) Marcelo is a Database Consultant at The Pythian Group. Currently holds the MCSE, MCT and MCSA Microsoft Certifications and also the Oracle OCP. Sp_help. DBA(sparetime) Marcelo Gonçalves Adade Microsoft Data Platform Consultant - Pythian Marcelo. adade @marceloadade • PASS Chapter SQLMANIACS co-leader (http: //bit. ly/2 A 1 Hr. Wv) • Blogger at DBBITS (www. dbbits. com. br) • (Very Occasional) Gamer at PSN (geekmarcelo) /marcelo. adade GROUP LEADER

SELECT * FROM Agenda – Quick comparisson Why? Basic architecture Administration Features we’d like each other had Where to learn Investment Take Away

Why? Reason 1

Why? Reason 2

Why? Reason 3 6 |

RDBMS – Generic Architecture Memory Process Data System Control Temp Log PC CPU Client 8 | CPU Server Storage Instance Database

Supported Operating Systems ORACLE -> Linux (Oracle and Red Hat), Supported on almost every Enterprise grade OS; SQL SERVER -> Windows. . . And Linux! https: //www. microsoft. com/en/server-cloud/sqlserver-on-linux. aspx 9 |

Instance Oracle X SQL SERVER Oracle 12 c SQL Server 2014 Host machine Instance Database Schema Object Object Schema Object Instance 10 | Object Database Object Schema Object Object Schema Object Instance Objec t Object

Oracle Architecture • SGA • PGA • Background processes • Pfile, Spfile • Control Files • Redo log files • Data files • Listener 11 |

Multitenant Oracle 12 |

Oracle Arch– simplyfied. . . Instance Process monitor SGA User process Client Request queue Dispatcher User process Redo log buffer Response queue Shared server System monitor Database buffer cache Job queue Queue monitor Event monitor Client User process Dedicated server Client Recoverer Database writer Log writer Checkpoint Archiver Trace writer Users Database Data files Archive log files Control files Automatic storage management 13 | Redo log files

SQL SERVER ARCH - Simplyfied SQLOS Memory pool Database cleanup User process Database buffer cache Worker thread Client Open data services User process Client Log cache Log writer Lazy writer Log files Worker thread User process Database shrinking Client Relational engine Users 14 | Data files ODBC Storage engine

Memory structures Oracle System Global Area (SGA) Shared pool Buffer cache In Memory area Redo buffers Fixed SGA Large pool Java pool Streams pool SQL Server memory pool Columnstore cache Hekaton – In Mem Buffer cache Procedure cache Log cache System structures 15 | Connection context CLR hosting layer

Storage structures Physical Data file Logical Data file Data file Temporary tablespace groups Tablespace Filegroup Segment Heap/Index (hobt) Heap/Index Extent Extent Blocks Pages 16 | Oracle SQL Server Tables

A bit more detail. . . System tablespace Data file Master DB Sys. Aux tablespace Data file Resource DB Data file Temp DB Data file Model DB Data file MSDB Tablespace group Temporary tablespace Data file Big. File tablespace Data file User data tablespace Data file User index tablespace Data file Undo tablespace Redo log files User DB User database Data file Data FG Data file Index FG Data file Redo log Oracle database instance Log files SQL Server instance Log file(s)

Metadata ORACLE: SQL SERVER: • Schema SYS • V$ • DM_ • DBA_, ALL_ • Sys. tables, sys. columns 19 |

LOG mechanisms (ACID) ORACLE: SQL SERVER: • REDO LOGs (Changes on DB) • Transaction Log • Undo tablespace (Change data – Begin Tran) • LOG Buffer • Redo Log Buffer Virtual log 1 Virtual log 2 Virtual log 3 Virtual log 4 Virtual log 5 Unused Truncated Min. LSN End of logical log Last checkpoint Start of logical log Virtual Log 1 Virtual Log 2 Virtual Log 3 Virtual Log 4 Truncated End of logical log Last checkpoint Min. LSN Start of logical log Next to last checkpoint 20 |

Recovery Models SQL SERVER: • FULL • BULK-LOGGED • SIMPLE ORACLE: • ARCHIVELOG • NOARCHIVELOG Either way, the most important piece is to have a working and well validated Backup policy 21 |

Tools SQL SERVER ORACLE • SSMS • SQLDEVELOPER and SQLPLUS • All cool graphic MS tools • SDSQL (Command line) • Command prompt tools • RMAN • ENTERPRISE MANAGER (paid!) • Powershell 22 |

What we would love to see in SQL SERVER • ASM – Automatic Storage Management; • RAC – Real Application Clusters; • Adaptive plan • Table Restore • Flashback Database • Compatibility with Other OS

What we would love to see in Oracle • • • Management Studio; Dynamic Memory adjustment (on the fly); Recovery mode switch (online); Dettach/Attach; Easy Backup/Restore An Iron Man Suit : -P

ASM e RAC - curiosity 25 |

Where to learn? SQL SERVER: ORACLE: • Technet wiki • Oracle wiki • MVA • Oracle Books Online • SQL Server Books Online • Community events • Community Events • Oficial courses: WDP and OAEC • Oficial courses • Extra-oficial courses 26 | • Extra-oficial courses

And how much will this cost?

Want to migrate? • Microsoft gives away license for clients willing to migrate (Software Assurance) https: //www. microsoft. com/en/server-cloud/sqllicense-migration. aspx

Take Away • Two best of (TOP) Market Tecnologies • Check overall TCO over time – Implementation and Maintenance • Ease of Administration, Development and Deploy; • Which one do you use in your Development environment? • Which OS do you prefer? 29 |

References • http: //www. microsoftvirtualacademy. com/training-courses/sql-server-para-dbas-oracle (ptbr) • http: //www. microsoftvirtualacademy. com/training-courses/sql-server-2014 -essentials-fororacle-dbas • Curso oficial Microsoft: 40074 A - Microsoft SQL Server 2014 for Oracle DBAs • Blog do Ricardo Portilho: http: //nervinformatica. com. br/blog/ • SQL SERVER Features I’d like to see: http: //www. brentozar. com/archive/2015/10/sql-serverfeatures-id-like-to-see-oracle-edition/ • Oracle X SQL SERVER: Indexes: https: //www. youtube. com/watch? v=OBUed. C 08 lf 8&list=WL&index=8 • Documentação Oracle: http: //docs. oracle. com/en/ • Documentação SQL SERVER: https: //msdn. microsoft. com/en-us/library/bb 545450. aspx 30 |

”Ever read ORACLE backwards? ” only works in PT • Anonymous genius 2

Keep in touch! http: //www. sqlmaniacs. com. br http: //www. dbbits. com. br Marcelo. Adade @marceloadade https: //br. linkedin. com/in/marcelo. adade marcelo@dbbits. com. br http: //www. youtube. com/user/dbbits 1

SELECT * FROM Questions;

Thank you!