1 / 44

Oracle and SQL Server Under One Roof

Oracle and SQL Server Under One Roof. Joe Yong Chief Architect Scalability Experts Inc. jyong@scalabilityexperts.com. About This Session. Goals Architectural and functional overview of SQL Server vis-à-vis Oracle Explore design philosophies and implementation results Non-goals

Antony
Download Presentation

Oracle and SQL Server Under One Roof

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle and SQL Server Under One Roof Joe Yong Chief Architect Scalability Experts Inc. jyong@scalabilityexperts.com

  2. About This Session • Goals • Architectural and functional overview of SQL Server vis-à-vis Oracle • Explore design philosophies and implementation results • Non-goals • Deep dive into SQL Server • Better/worse comparisons • Make you a SQL Server expert • Pre-requisites • Experience as an Oracle DBA, Architect or Developer DBA • Open mind

  3. I manage an enterprise class database system I pretty much have to be rocket a scientist to manage my DBs I am paranoid about security and lock my database down real tight Developers sometimes driver me crazy with their un-optimized code Dev: Those DBA dudes need to chill out a little I’d rather deal with a corrupted DB on my Nasdaq system at 11am Monday than deal with pricing/licensing Product development leadership: Chuck Rozwat, Vice President Ex-DEC RDB I manage an enterprise class database system I built a rocket for a science project while managing my DBs After slammer, my DB makes Fort Knox look like 7-eleven I threw away the key and welded the basement doors on my 1st day Dev: We have more than one tunneling protocol I’d rather deal with a corrupted DB on my Dow Jones system at 11am Monday than deal with pricing/licensing Product development leadership: Peter Spiro, Distinguished Engineer Ex-DEC RDB Oracle DBA vs. SQL Server DBA

  4. Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary

  5. Why you should careThis used to be your world SUN, HP, Digital, etc… *NIX / Windows Server Mainframe, Mini, etc… Oracle Database Basic network KRON, SQL Loader, external tables, etc… Client PCs Flat-files

  6. Why you should careThis is your world now

  7. Why you should care • Mono-cultures don’t exist; heterogeneity is a fact • Right tool for the right job; you can’t build a data center with a Swiss army knife and duct tape • Data sharing is a necessity not a luxury • Knowledge puts you in control (as much as possible) • It pays to know both Oracle & $QL $erver

  8. Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary

  9. Database ArchitectureWhat is SQL Server the Platform?

  10. Database ArchitectureWhat is SQL Server the Database? • Multiple instances per server, multiple databases per instance, multiple schemas per database • Some shared components • Between instances: Tools, Books Online, Common Files • Between databases: System databases, Agents, Logs, resources

  11. Database ArchitectureSQL Server: Quick Peek Under The Hood RPC Messages SQL Messages HTTP messages Results SQL OS Memory Manager Scheduler Deadlock Monitor Synchronization Services Lock Manager Buffer Pool SQL CLR Hosting Layer CLR T-SQL Execution PARSER T-SQL Compiler Algebraizer Query Optimizer SQL Manager Query Execution Expression Service Execution Environment METADATA Interface Storage Engine

  12. Database ArchitectureSQL Server: Storage Overview • Database storage architecture covers physical and logical structures • Physical structures are data files, log files, and so on. • Logical structures are subdivisions of data files used to manage storage space

  13. Database ArchitectureSQL Server: Storage Blocks Variable Fixed

  14. Bind, Expand Views Found Compiled Plan Found Executable Plan Auto-Param Language Processing (Parse/Bind, Statement/Batch Execution, Plan Cache Management) Parse Lookup in Plan Cache Query Optimization (Plan Generation, View Matching, Statistics, Costing) Query Execution (Query Operators, Memory Grants, Parallelism, Showplan) Generate Executable Plan Query Optimization Fix Memory Grant & DoP Execute Return Plans to Cache Database ArchitectureSQL Server: Query Processing New Statement Not Found • Stored procedures • Parameterized queries • Query hints • Dynamic SQL

  15. Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary

  16. SecurityFeatures and Design Principles • Data encryption, granular permissions, surface area configuration, auditing, network packet encryption, default traces, alerts, etc… • Security tenets • Secure by design, out of the box • default settings are secure • difficult to choose less secure settings • Principle of least privileges • Minimum, granular permissions for specific tasks • Low privileged service accounts • Reduction of surface area • Install, run only necessary components • Tools to simplify lockdown

  17. Database Approle1 User1 Role1 Hasdefaultschema Owns Owns Owns Schema1 Schema2 Schema3 SP1 Fn1 Tab1 SecurityUsers, Schemas and Objects • A database can contain multiple schemas • Each schema has an owning principal – user or role • Each user has a default schema for name resolution • Object creation inside schema requires CREATE permission on the object and ALTER or CONTROL permission on the schema

  18. SecurityLogins and Users • A LOGIN give you connection rights • Is stored in the MASTER database • Applies to the instance • Has no permissions directly per se • Exception: Server Role membership • Mapped to a user for permissions • A database USER is the permissions container • Also the schema owner • Permissions are granted to database users, not logins • Specific to a single database

  19. Payloads TDS SOAP SSB Database Mirroring Transport Protocol Named pipes Shared Memory TCP HTTP VIA Endpoint Based Authentication • SQL Server 2005 Endpoint: • Point of entry into an instance • Binds transport protocol to payload • HTTP, Service Broker, Database Mirroring: • Endpoints need to be explicitly created • No permissions on endpoint by default • TCP, Named Pipes, Shared Memory • Default endpoint created at start up • CONNECT permissions granted to authenticated logins • Permissions can be denied on a per endpoint basis

  20. Network connection request/pre-login handshake Connect to the SQL Server computer Login authentication request to SQL Server Establish login credentials;Authorize against EP Switch to a database and authorize access Establish a database context Attempt to perform some action SQL Server Security Model Verify permissions for all actions

  21. Access and Authentication - Principals • Windows-level principals • Windows Domain Login • Windows Local Login • SQL Server-level principal • SQL Server Login • Database-level principals • Database User • Database Role • Application Role

  22. General Permissions Scheme • Grantee • Logins for Server level permissions • Database principals for database permissions • Securable • Entity to be secured • Example: Tables, assemblies, databases, server, etc… • Same permission can be at multiple scopes • Example CONTROL at schema or table level • DENY at any level always take precedence

  23. Permissions Hierarchy • Principal • Individuals, groups & processes • Requests resources • Can be hierarchical • Securable • Resources to which the authorization system regulates access • Can be nested (scope) and secured individually or collectively • Scope • Server • Database • Schema

  24. Covering Permissions • Permissions can be derived from grants at higher scope • Example EXECUTE granted at schema level • Permissions can be implied by other permissions • Example, CONTROL on a table implies SELECT • Sys.fn_my_permissions() • Ability to find out what permissions a user has

  25. Scope and Granularity

  26. Where are permissions recorded? • Sys.Server_permissions • server level permissions • Sys.database_permissions • Database level permissions • Sys.securable_classes • Lists all securables • Sys.fn_builtin_permissions • Shows all permissions grantable on a securable • Includes covering permissions

  27. Execution context • Token: • 1 principal as primary identity • N principals as secondary identities • Zero or more authenticators • Authenticator: • Vouches for authenticity of the token • Can be a principal or a certificate • Execution context: • 1 server token • 1 or more database tokens (one for each database that is accessed)

  28. EXECUTE AS (SQL Server2005 model) EXPLICIT impersonation. (stand alone) IMPLICIT impersonation. (module) Impersonation model • Login as one context, and at run time, ask the system to switch your context to some other context. • Server level: • Server-level triggers • Database level: • Stored procedures • Functions • Triggers • Queues

  29. Explicit Impersonation • EXECUTE AS login • Syntax: • EXECUTE AS login = ‘<server principal>’. • Must have Impersonate permission on login::<server principal> • Token is valid across the server • Server level authenticator = system. • Server level permissions and role memberships are honored • Revert to previous context by calling REVERT

  30. Implicit Impersonation • Permissions checked against module creator’s context at module creation time • Server level authenticator = system. • Database level authenticator = dbo. • Context is reverted back when the module execution finishes. To call a module marked with Execute as, the caller doesn’t need permission to impersonate anyone. Only permission to execute the module.

  31. Encryption • Network • Data/table • Let’s just see how it’s done

  32. Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary

  33. Management ToolsConfiguration Manager • Configuration manager • Management Studio • Business Intelligence Development Studio • Profiler • Database Tuning Advisor

  34. Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary

  35. Data Movement & InteroperabilityTiers and Options • Client-tier • Web browsers (duh?!) • Clients that wrap web services • Middle-tier • Web Services • Application/Web server, gateways, etc…. • Btw, J2EE is fully supported • Data-tier • Direct ODBC, OLEDB, .NET, JDBC • SQL Server wrapped OLEDB

  36. Data Movement & InteroperabilityData Import/Export • BCP • SELECT INTO • SSIS • Let’s see how they work

  37. SQL Server Distributor Data Movement & InteroperabilityReplication • Transactional and Snapshot Publications • Administered like SQL Server Publishers • No Oracle side software install necessary • v8+ Oracle on any OS supported • Improvements for Oracle and DB2 subscribers Subscribers

  38. Data Movement & InteroperabilityLinked Servers • <<wip>>

  39. Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary

  40. Case Study 1Joint Standards • <<WiP>>

  41. Case Study 2Maintain, Expand, Extend • <<WiP>>

  42. Agenda • Why you should care • Database architecture • Security • Management tools • Replication • Integration Services • Case study • Summary

  43. Summary • <<WiP>> • <<WiP>>

  44. Resources • www.microsoft.com/sql/ • msdn.microsoft.com/sqlserver/ • www.microsoft.com/technet/ • www.scalabilityexperts.com • www.sqldev.net • www.sqlservercentral.com/

More Related