Oracle and SQL Server Under One Roof

Oracle and SQL Server Under One Roof

Joe YongJoe YongChief ArchitectChief ArchitectScalability Experts Inc.Scalability Experts Inc.jyong@scalabilityexperts.comjyong@scalabilityexperts.com

About This SessionAbout This Session

GoalsGoals Architectural and functional overview of SQL Architectural and functional overview of SQL

Server vis-à-vis OracleServer vis-à-vis Oracle Explore design philosophies and Explore design philosophies and

implementation resultsimplementation results

Non-goalsNon-goals Deep dive into SQL ServerDeep dive into SQL Server Better/worse comparisonsBetter/worse comparisons Make you a SQL Server expertMake you a SQL Server expert

Pre-requisitesPre-requisites Experience as an Oracle DBA, Architect or Experience as an Oracle DBA, Architect or

Developer DBADeveloper DBA Open mindOpen mind

Oracle DBA vs. SQL Server DBAOracle DBA vs. SQL Server DBA

I manage an enterprise I manage an enterprise class database systemclass database systemI pretty much have to be I pretty much have to be rocket a scientist to rocket a scientist to manage my DBsmanage my DBsI am paranoid about I am paranoid about security and lock my security and lock my database down real tightdatabase down real tightDevelopers sometimes Developers sometimes driver me crazy with their driver me crazy with their un-optimized codeun-optimized code Dev: Those DBA dudes Dev: Those DBA dudes

need to chill out a littleneed to chill out a littleI’d rather deal with a I’d rather deal with a corrupted DB on my corrupted DB on my Nasdaq system at 11am Nasdaq system at 11am Monday than deal with Monday than deal with pricing/licensingpricing/licensingProduct development Product development leadership:leadership: Chuck Rozwat, Vice Chuck Rozwat, Vice

PresidentPresident Ex-DEC RDBEx-DEC RDB

I manage an enterprise I manage an enterprise class database systemclass database systemI built a rocket for a I built a rocket for a science project while science project while managing my DBsmanaging my DBsAfter slammer, my DB After slammer, my DB makes Fort Knox look like makes Fort Knox look like 7-eleven7-elevenI threw away the key and I threw away the key and welded the basement welded the basement doors on my 1doors on my 1stst day day Dev: We have more than Dev: We have more than

one tunneling protocolone tunneling protocolI’d rather deal with a I’d rather deal with a corrupted DB on my Dow corrupted DB on my Dow Jones system at 11am Jones system at 11am Monday than deal with Monday than deal with pricing/licensingpricing/licensingProduct development Product development leadership:leadership: Peter Spiro, Distinguished Peter Spiro, Distinguished

EngineerEngineer Ex-DEC RDBEx-DEC RDB

AgendaAgenda

Why you should careWhy you should care

Database architectureDatabase architecture

SecuritySecurity

Management toolsManagement tools

Data movement & interoperabilityData movement & interoperability

Case studyCase study

SummarySummary

Mainframe, Mainframe, Mini, etc…Mini, etc…

Mainframe, Mainframe, Mini, etc…Mini, etc…

SUN, HP, Digital, etc…SUN, HP, Digital, etc…SUN, HP, Digital, etc…SUN, HP, Digital, etc…

*NIX / Windows Server*NIX / Windows Server*NIX / Windows Server*NIX / Windows Server

Oracle DatabaseOracle DatabaseOracle DatabaseOracle Database

Flat-filesFlat-filesClient PCsClient PCs

Why you should careWhy you should careThis used to be your worldThis used to be your world

KRON, SQL Loader, KRON, SQL Loader, external tables, etc…external tables, etc…

Basic networkBasic network

Why you should careWhy you should careThis is your world nowThis is your world now

Why you should careWhy you should care

Mono-cultures don’t exist; heterogeneity is Mono-cultures don’t exist; heterogeneity is a facta fact

Right tool for the right job; you can’t build Right tool for the right job; you can’t build a data center with a Swiss army knife and a data center with a Swiss army knife and duct tapeduct tape

Data sharing is a necessity not a luxuryData sharing is a necessity not a luxury

Knowledge puts you in control (as much as Knowledge puts you in control (as much as possible)possible)

It pays to know both Oracle & $QL $erverIt pays to know both Oracle & $QL $erver

AgendaAgenda

Why you should careWhy you should care

Database architectureDatabase architectureSecuritySecurity

Management toolsManagement tools

Data movement & interoperabilityData movement & interoperability

Case studyCase study

SummarySummary

Database ArchitectureDatabase ArchitectureWhat is SQL Server the Platform?What is SQL Server the Platform?

Database ArchitectureDatabase ArchitectureWhat is SQL Server the Database?What is SQL Server the Database?

Storage

PC

Data

Server

CPU CPU

Process

Process

Process

Process

Memory

DataData

System

Log

CLIENT DATABASEINSTANCE

Control

Temp

Multiple instances per server, multiple databases Multiple instances per server, multiple databases per instance, multiple schemas per databaseper instance, multiple schemas per databaseSome shared componentsSome shared components Between instances: Tools, Books Online, Common FilesBetween instances: Tools, Books Online, Common Files Between databases: System databases, Agents, Logs, Between databases: System databases, Agents, Logs,

resourcesresources

Database ArchitectureDatabase ArchitectureSQL Server: Quick Peek Under The SQL Server: Quick Peek Under The HoodHood

SchedulerMemory ManagerDeadlock Monitor

Buffer PoolLock Manager

SQL CLR Hosting Layer

Synchronization Services

SQL Messages RPC MessagesHTTP messages

SQL OS

Algebraizer

T-SQL

Compiler

PARSER

Query Optimizer

SQLManager

T-SQL Execution

CLR

Query Execution

Expression S

erviceExecution Environment

Storage Engine

Results

METADATA Interface

Database ArchitectureDatabase ArchitectureSQL Server: Storage OverviewSQL Server: Storage Overview

Data File Data File

Tablespace

Segment Segment

Extent Extent Extent Extent

Blocks Blocks Blocks Blocks

Data File Data File

File Group

Extent Extent Extent Extent

Pages Pages Pages Pages

Ph

ysic

alL

og

ical

ORACLE SQL SERVER

Heap/Index Heap/Index

Temporary Tablespace Groups

Tablespace

Database storage architecture covers physical and logical Database storage architecture covers physical and logical structuresstructures

Physical structures are data files, log files, and so on.Physical structures are data files, log files, and so on.

Logical structures are subdivisions of data files used to Logical structures are subdivisions of data files used to manage storage spacemanage storage space

Database ArchitectureDatabase ArchitectureSQL Server: Storage BlocksSQL Server: Storage Blocks

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

2Kblock

6 x 2K = 12KEXTENT

8 x 2K = 16KEXTENT

12K + 16K = 28KSEGMENT

(Table/Index)

8Kblock

8Kblock

8Kblock

2Kblock

8Kblock

8Kblock

8Kblock

2Kblock

8Kblock

8Kblock

8Kblock

2Kblock

8Kblock

8Kblock

8Kblock

2Kblock

8Kblock

8Kblock

8Kblock

2Kblock

8Kblock

8Kblock

8Kblock

2Kblock

8Kblock

8Kblock

8Kblock

2Kblock

8Kblock

8Kblock

8Kblock

2Kblock

8 x 8K = 64KEXTENT

8 x 8K = 64KEXTENT

64K + 64K = 128K

HEAP/INDEX

Oracle SQL Server

VariableVariable FixedFixed

Database ArchitectureDatabase ArchitectureSQL Server: Query ProcessingSQL Server: Query Processing

Lookup inPlan Cache

Generate Executable Plan

Fix Memory Grant & DoP

Execute

Found Executable Plan

Found Compiled Plan

Not Found

Auto-Param

Bind, Expand Views

Parse

Query Optimization

Return Plans to Cache

New Statement

Query Optimization (Plan Generation, View Matching,

Statistics, Costing)

Query Execution (Query Operators, Memory Grants,

Parallelism, Showplan)

Language Processing

(Parse/Bind, Statement/Batch Execution, Plan Cache Management)

Stored proceduresStored procedures

Parameterized queriesParameterized queries

Query hintsQuery hints

Dynamic SQLDynamic SQL

AgendaAgenda

Why you should careWhy you should care

Database architectureDatabase architecture

SecuritySecurityManagement toolsManagement tools

Data movement & interoperabilityData movement & interoperability

Case studyCase study

SummarySummary

SecuritySecurityFeatures and Design PrinciplesFeatures and Design Principles

Data encryption, granular permissions, Data encryption, granular permissions, surface area configuration, auditing, surface area configuration, auditing, network packet encryption, default traces, network packet encryption, default traces, alerts, etc…alerts, etc…

Security tenetsSecurity tenets Secure by design, out of the boxSecure by design, out of the box

default settings are securedefault settings are secure difficult to choose less secure settingsdifficult to choose less secure settings

Principle of least privilegesPrinciple of least privileges Minimum, granular permissions for specific tasksMinimum, granular permissions for specific tasks Low privileged service accountsLow privileged service accounts

Reduction of surface areaReduction of surface area Install, run only necessary componentsInstall, run only necessary components Tools to simplify lockdownTools to simplify lockdown

Role1 User1

Owns

Has default schema

Owns

Approle1

Owns

Schema1 Schema2

Schema3

SP1Fn1

Tab1

Database

A database can A database can contain contain multiple schemasmultiple schemas

Each schema has an Each schema has an owning principal – owning principal – user or roleuser or role

Each user has a Each user has a default schema for default schema for name resolutionname resolution

Object creation inside Object creation inside schema requires schema requires CREATE permission on CREATE permission on the object the object andand ALTER ALTER or CONTROL or CONTROL permission permission on the schemaon the schema

SecuritySecurityUsers, Schemas and ObjectsUsers, Schemas and Objects

SecuritySecurityLogins and UsersLogins and Users

A LOGIN give you connection rights A LOGIN give you connection rights Is stored in the MASTER databaseIs stored in the MASTER database Applies to the instanceApplies to the instance Has no permissions directly per seHas no permissions directly per se

Exception: Server Role membershipException: Server Role membership

Mapped to a user for permissionsMapped to a user for permissions

A database USER is the permissions A database USER is the permissions containercontainer Also the schema ownerAlso the schema owner Permissions are granted to database users, not Permissions are granted to database users, not

loginslogins Specific to a single databaseSpecific to a single database

Endpoint Based AuthenticationEndpoint Based Authentication

SQL Server 2005 Endpoint:SQL Server 2005 Endpoint: Point of entry into an instancePoint of entry into an instance Binds transport protocol to payloadBinds transport protocol to payload

HTTP, Service Broker, Database Mirroring:HTTP, Service Broker, Database Mirroring: Endpoints need to be explicitly createdEndpoints need to be explicitly created No permissions on endpoint by defaultNo permissions on endpoint by default

TCP, Named Pipes, Shared MemoryTCP, Named Pipes, Shared Memory Default endpoint created at start upDefault endpoint created at start up CONNECT permissions granted to authenticated loginsCONNECT permissions granted to authenticated logins Permissions can be denied on a per endpoint basisPermissions can be denied on a per endpoint basis

Transport ProtocolTransport Protocol

Named pipes Shared MemoryNamed pipes Shared Memory

TCPTCP HTTPHTTP VIA VIA

PayloadsPayloads

TDSTDS SOAPSOAP

SSBSSB Database MirroringDatabase Mirroring

SQL Server Security ModelSQL Server Security Model

Connect to the SQL Server computerConnect to the SQL Server computer

Network connection request/pre-login handshake

Establish login credentials;Establish login credentials; Authorize against EPAuthorize against EP

Verify permissions for all actionsVerify permissions for all actions

Attempt to perform some action

Establish a database contextEstablish a database context

Switch to a database and authorize access

Login authentication request to SQL Server

Access and Authentication - Access and Authentication - PrincipalsPrincipals

Windows-level principalsWindows-level principals Windows Domain LoginWindows Domain Login Windows Local LoginWindows Local Login

SQL Server-level principalSQL Server-level principal SQL Server LoginSQL Server Login

Database-level principalsDatabase-level principals Database UserDatabase User Database RoleDatabase Role Application RoleApplication Role

General Permissions SchemeGeneral Permissions Scheme

GranteeGrantee Logins for Server level permissionsLogins for Server level permissions Database principals for database permissionsDatabase principals for database permissions

SecurableSecurable Entity to be securedEntity to be secured Example: Tables, assemblies, databases, Example: Tables, assemblies, databases,

server, etc…server, etc…

Same permission can be at multiple scopesSame permission can be at multiple scopes Example CONTROL at schema or table levelExample CONTROL at schema or table level

DENY at any level always take precedenceDENY at any level always take precedence

Permissions HierarchyPermissions Hierarchy

PrincipalPrincipal Individuals, groups & Individuals, groups &

processesprocesses Requests resourcesRequests resources Can be hierarchicalCan be hierarchical

SecurableSecurable Resources to which the Resources to which the

authorization system authorization system regulates accessregulates access

Can be nested (scope) Can be nested (scope) and secured individually and secured individually or collectivelyor collectively

ScopeScope ServerServer DatabaseDatabase SchemaSchema

Permissions can be derived from grants at Permissions can be derived from grants at higher scopehigher scope Example EXECUTE granted at schema levelExample EXECUTE granted at schema level

Permissions can be implied by other Permissions can be implied by other permissionspermissions Example, CONTROL on a table implies SELECTExample, CONTROL on a table implies SELECT

Sys.fn_my_permissions()Sys.fn_my_permissions() Ability to find out what permissions a user hasAbility to find out what permissions a user has

Covering PermissionsCovering Permissions

Scope and GranularityScope and Granularity

DatabaseDatabase

SchemaSchema 11 Schema 2Schema 2 Schema 3Schema 3

Table1Table1 View1View1 View 2View 2 Stored ProcStored Proc FunctionFunction

Where are permissions Where are permissions recorded?recorded?

Sys.Server_permissionsSys.Server_permissions server level permissionsserver level permissions

Sys.database_permissionsSys.database_permissions Database level permissionsDatabase level permissions

Sys.securable_classesSys.securable_classes Lists all securablesLists all securables

Sys.fn_builtin_permissionsSys.fn_builtin_permissions Shows all permissions grantable on a securableShows all permissions grantable on a securable Includes covering permissionsIncludes covering permissions

Execution contextExecution context

Token:Token: 1 principal as primary identity1 principal as primary identity NN principals as secondary identities principals as secondary identities Zero or more authenticatorsZero or more authenticators

Authenticator:Authenticator: Vouches for authenticity of the tokenVouches for authenticity of the token Can be a principal or a certificateCan be a principal or a certificate

Execution context:Execution context: 1 server token1 server token 1 or more database tokens (one for each 1 or more database tokens (one for each

database that is accessed)database that is accessed)

Impersonation modelImpersonation model

EXECUTE AS

(SQL Server2005 model)

EXPLICIT impersonation.

(stand alone)

IMPLICIT impersonation.

(module)

Server level:Server level:Server-level triggersServer-level triggers

Database level:Database level:Stored proceduresStored proceduresFunctionsFunctionsTriggersTriggersQueuesQueues

Login as one context, and at run time, ask Login as one context, and at run time, ask the system to switch your context to some the system to switch your context to some other context.other context.

Explicit ImpersonationExplicit Impersonation

EXECUTE AS EXECUTE AS loginlogin Syntax: Syntax:

EXECUTE AS EXECUTE AS loginlogin = ‘<server principal>’. = ‘<server principal>’. Must have Impersonate permission on Must have Impersonate permission on

login::<server principal>login::<server principal> Token is valid across the serverToken is valid across the server Server level authenticator = system. Server level authenticator = system. Server level permissions and role memberships Server level permissions and role memberships

are honoredare honored Revert to previous context by calling REVERTRevert to previous context by calling REVERT

Implicit ImpersonationImplicit Impersonation

PermissionsPermissions checked against module checked against module creator’s context at module creation time creator’s context at module creation time

Server level authenticator = system.Server level authenticator = system.

Database level authenticator = dbo.Database level authenticator = dbo.

Context is reverted back when the module Context is reverted back when the module execution finishes.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.

EncryptionEncryption

NetworkNetwork

Data/tableData/table

Let’s just see how it’s doneLet’s just see how it’s done

AgendaAgenda

Why you should careWhy you should care

Database architectureDatabase architecture

SecuritySecurity

Management toolsManagement toolsData movement & interoperabilityData movement & interoperability

Case studyCase study

SummarySummary

Management ToolsManagement ToolsConfiguration ManagerConfiguration Manager

Configuration managerConfiguration manager

Management StudioManagement Studio

Business Intelligence Development StudioBusiness Intelligence Development Studio

ProfilerProfiler

Database Tuning AdvisorDatabase Tuning Advisor

AgendaAgenda

Why you should careWhy you should care

Database architectureDatabase architecture

SecuritySecurity

Management toolsManagement tools

Data movement & Data movement & interoperabilityinteroperabilityCase studyCase study

SummarySummary

Data Movement & Data Movement & InteroperabilityInteroperabilityTiers and OptionsTiers and OptionsClient-tierClient-tier

Web browsers (duh?!)Web browsers (duh?!) Clients that wrap web servicesClients that wrap web services

Middle-tierMiddle-tier Web ServicesWeb Services Application/Web server, gateways, etc…. Application/Web server, gateways, etc…. Btw, J2EE is fully supportedBtw, J2EE is fully supported

Data-tierData-tier Direct ODBC, OLEDB, .NET, JDBCDirect ODBC, OLEDB, .NET, JDBC SQL Server wrapped OLEDBSQL Server wrapped OLEDB

Data Movement & Data Movement & InteroperabilityInteroperabilityData Import/ExportData Import/ExportBCPBCP

SELECT INTOSELECT INTO

SSISSSIS

Let’s see how they workLet’s see how they work

Data Movement & Data Movement & InteroperabilityInteroperabilityReplicationReplicationTransactional and Snapshot Transactional and Snapshot

PublicationsPublications

Administered like SQL Server Administered like SQL Server PublishersPublishers

No Oracle side software install No Oracle side software install necessarynecessary

v8+ Oracle on any OS supported v8+ Oracle on any OS supported

Improvements for Oracle and DB2 Improvements for Oracle and DB2 subscriberssubscribers SQL ServerSQL Server

DistributorDistributor SubscribersSubscribers

Data Movement & Data Movement & InteroperabilityInteroperabilityLinked ServersLinked Servers<<wip>><<wip>>

AgendaAgenda

Why you should careWhy you should care

Database architectureDatabase architecture

SecuritySecurity

Management toolsManagement tools

Data movement & interoperabilityData movement & interoperability

Case studyCase studySummarySummary

Case Study 1Case Study 1Joint StandardsJoint Standards

<<WiP>><<WiP>>

Case Study 2Case Study 2Maintain, Expand, ExtendMaintain, Expand, Extend

<<WiP>><<WiP>>

AgendaAgenda

Why you should careWhy you should care

Database architectureDatabase architecture

SecuritySecurity

Management toolsManagement tools

ReplicationReplication

Integration ServicesIntegration Services

Case studyCase study

SummarySummary

SummarySummary

<<WiP>><<WiP>>

<<WiP>><<WiP>>

ResourcesResources

www.microsoft.com/sql/www.microsoft.com/sql/

msdn.microsoft.com/sqlservermsdn.microsoft.com/sqlserver//

www.microsoft.com/technet/www.microsoft.com/technet/

www.scalabilityexperts.comwww.scalabilityexperts.com

www.sqldev.netwww.sqldev.net

www.sqlservercentral.com/www.sqlservercentral.com/