Transcript 10 Software Architecture Failures

Oracle and SQL Server
Under One Roof
Joe Yong
Chief Architect
Scalability Experts Inc.
[email protected]
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
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 unoptimized code
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: Those DBA dudes need
to chill out a little
Dev: We have more than one
tunneling protocol
I’d rather deal with a corrupted
DB on my Nasdaq system at
11am Monday than deal with
pricing/licensing
Product development
leadership:
I’d rather deal with a corrupted
DB on my Dow Jones system
at 11am Monday than deal
with pricing/licensing
Product development
leadership:



Chuck Rozwat, Vice President
Ex-DEC RDB

Peter Spiro, Distinguished
Engineer
Ex-DEC RDB
Agenda
Why you should care
Database architecture
Security
Management tools
Data movement & interoperability
Case study
Summary
Why you should care
This used to be your world
SUN, HP, Digital, etc…
*NIX / Windows Server
Mainframe,
Mini, etc…
Oracle Database
Basic network
Client PCs
KRON, SQL Loader,
external tables, etc…
Flat-files
Why you should care
This is your world now
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 Architecture
What is SQL Server the Platform?
Database Architecture
What is SQL Server the Database?
Memory
Process
Data
Process
System
Data
Data
Process
Process
PC
Log
Temp
CPU
Control
CPU
Storage
Server
CLIENT
INSTANCE
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 Architecture
SQL Server: Quick Peek Under The Hood
SQL Messages
HTTP messages
RPC Messages
Results
SQL OS
Deadlock Monitor
Memory Manager
Scheduler
Buffer Pool
Synchronization
Services
Lock Manager
SQL CLR Hosting Layer
T-SQL
Compiler
CLR
Query
Optimizer
SQL
Manager
Algebraizer
Execution Environment
METADATA Interface
Storage Engine
T-SQL
Execution
Query
Execution
Expression Service
P
A
R
S
E
R
Database Architecture
Physical
SQL Server: Storage Overview
Data File
Data File
Data File
Data File
Logical
Temporary Tablespace Groups
Tablespace
Tablespace
Segment
File Group
Segment
Heap/Index
Heap/Index
Extent
Extent
Extent
Extent
Extent
Extent
Extent
Extent
Blocks
Blocks
Blocks
Blocks
Pages
Pages
Pages
Pages
ORACLE
SQL SERVER
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 Architecture
SQL Server: Storage Blocks
2K
block
2K
block
2K
2K
block 6 x 2K
block
= 12K
8K
block
8K
block
8K
block
2K
block
2K
block
2K
block
EXTENT
2K
block
8K
block
8K
block
8K 8 x 8K2K
= 64K
block EXTENT
block
2K
block
2K
block
2K
block
2K
block
8K
block
8K
block
8K
block
2K
block
2K
block
2K
block
2K
block
2K
block
8K
block
8K
block
8K
block
2K
block
2K
block
2K
block
2K
block
2K
block
8K
block
8K
block
8K
block
2K
block
2K
block
2K 8 x 2K
2K= 16K 2K
block EXTENT
block
block
8K
block
8K
block
= 64K
8K 8 x 8K2K
block EXTENT
block
2K
block
2K
block
2K
block
2K
block
8K
block
8K
block
8K
block
2K
block
2K
block
2K
block
2K
block
2K
block
8K
block
8K
block
8K
block
2K
block
12K + 16K = 28K
SEGMENT
(Table/Index)
2K
block
Oracle
SQL Server
Variable
Fixed
64K + 64K =
128K
HEAP/INDEX
Database Architecture
SQL Server: Query Processing
New Statement
Language Processing
Found Executable
Plan
Lookup in
Plan Cache
Found Compiled
Plan
Not Found
Parse
Auto-Param
Bind, Expand Views
Query Optimization
Generate Executable Plan
Fix Memory Grant & DoP
Execute
Return Plans to Cache
(Parse/Bind, Statement/Batch Execution, Plan Cache
Management)
Query Optimization (Plan
Generation, View
Matching, Statistics,
Costing)
Query Execution (Query
Operators, Memory
Grants, Parallelism,
Showplan)
 Stored procedures
 Parameterized queries
 Query hints
 Dynamic SQL
Agenda
Why you should care
Database architecture
Security
Management tools
Data movement & interoperability
Case study
Summary
Security
Features 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
Security
Users, 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
Database
Approle1
User1
Role1
Has
default
schema
Owns
Schema1
Owns
Schema2
Schema3
SP1
Fn1
Tab1
Owns
Security
Logins 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
Endpoint Based Authentication
SQL Server 2005 Endpoint:


Point of entry into an instance
Binds transport protocol to payload
Transport Protocol
Named pipes
Shared Memory
TCP HTTP
VIA
Payloads
TDS
SOAP
SSB Database Mirroring
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
SQL Server Security Model
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
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
Scope and Granularity
Database
Table1
Schema 1
Schema 2
View1
View 2
Schema 3
Stored Proc
Function
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)
Impersonation model
Login as one context, and at run time, ask the
system to switch your context to some other
context.
EXECUTE AS
(SQL Server2005
model)
Server level:
Server-level triggers
Database level:
Stored procedures
Functions
Triggers
Queues
EXPLICIT
impersonation.
IMPLICIT
impersonation.
(stand alone)
(module)
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 Tools
Configuration 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 & Interoperability
Tiers 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 & Interoperability
Data Import/Export
BCP
SELECT INTO
SSIS
Let’s see how they work
Data Movement & Interoperability
Replication
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
SQL Server
Distributor
Subscribers
Data Movement & Interoperability
Linked Servers
<<wip>>
Agenda
Why you should care
Database architecture
Security
Management tools
Data movement & interoperability
Case study
Summary
Case Study 1
Joint Standards
<<WiP>>
Case Study 2
Maintain, 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/