Oracle and SQL Server Under One Roof

1
Oracle and SQL Server Under One Roof

• Joe Yong
• Chief Architect
• Scalability Experts Inc.
• jyong_at_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
Oracle DBA vs. SQL Server DBA

• 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
• Id 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
• Id 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

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
Mainframe, Mini, etc
NIX / Windows Server
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 dont exist heterogeneity is a
  fact
• Right tool for the right job you cant 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
T-SQL Execution
CLR
T-SQL Compiler
PARSER
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
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
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
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
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
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 ltserver principalgt.
• Must have Impersonate permission on
  loginltserver principalgt
• 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 creators
  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 doesnt need permission to impersonate
anyone. Only permission to execute the module.
31
Encryption

• Network
• Data/table
• Lets just see how its 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
• Lets see how they work

37
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

• ltltwipgtgt

39
Agenda

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

40
Case Study 1Joint Standards

• ltltWiPgtgt

41
Case Study 2Maintain, Expand, Extend

• ltltWiPgtgt

42
Agenda

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

43
Summary

• ltltWiPgtgt
• ltltWiPgtgt

44
Resources

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