450 likes | 703 Views
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
E N D
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 • 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
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
Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary
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
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
Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary
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
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
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
Database ArchitectureSQL Server: Storage Blocks Variable Fixed
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
Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary
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
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
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
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
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
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
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
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
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
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
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)
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
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
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.
Encryption • Network • Data/table • Let’s just see how it’s done
Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary
Management ToolsConfiguration Manager • Configuration manager • Management Studio • Business Intelligence Development Studio • Profiler • Database Tuning Advisor
Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary
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
Data Movement & InteroperabilityData Import/Export • BCP • SELECT INTO • SSIS • Let’s see how they work
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
Agenda • Why you should care • Database architecture • Security • Management tools • Data movement & interoperability • Case study • Summary
Case Study 1Joint Standards • <<WiP>>
Case Study 2Maintain, Expand, Extend • <<WiP>>
Agenda • Why you should care • Database architecture • Security • Management tools • Replication • Integration Services • Case study • Summary
Summary • <<WiP>> • <<WiP>>
Resources • www.microsoft.com/sql/ • msdn.microsoft.com/sqlserver/ • www.microsoft.com/technet/ • www.scalabilityexperts.com • www.sqldev.net • www.sqlservercentral.com/