Transcript Architecting Scalable, Flexible and Secure Database Systems

Architecting Scalable, Flexible
and Secure Database Systems
with SQL Server 2005
Adam Calderon
Principal Engineer
Interknowlogy LLC
[email protected]
http://blogs.interknowlogy.com/adamcalderon
Architectural Benefits
 SQL Server 2005 provides
 all the "big company" benefits
 all the latest technology
 while minimizing total cost of ownership
 When you invent "the next big thing"
 your database systems can grow with your
business without a total system rewrite
 don't re-architect when you outgrow
hardware
 Scaling is incremental
 pay as you go
What features make this happen?
When can I use them?
Take Advantage When?
How much work to leverage the technology?
Upgrade
Immediate






Security by Policy
Secure Metadata
Granular Permissions
Support for
Advanced
OS/Hardware
features
Relational Engine
Speed-ups
Notification Services
Integration


Minimal Work to
Leverage (~ 2 days)



User/Schema
Separation
Cache Sync
SQLCLR Procedures

LOB Data Types
T-SQL TRY/CATCH
New T-SQL Statements
Data Paging

XML Processing



Design and
Architect (+ days)






Service Broker
Web Services
Data Encryption and
Key Management
Execute Context for
Procedures
XML Type
UDTs/UDAggregates
Improving Security and Scalability from Installation to Design
Availability in Layers to allow re-architecting in stages
Start Small...
Web Server
in DMZ
Secure By Default, now...
Scale Up?
Scale Out?
Caching?
More Data Formats?
More Robust?
Secure Connections
Database Server
Your Internal
Network
Secure Data And
Metadata
Or Start Big...
Web Server
Secure By Default
Scale Up
Scale Out – Messaging
Performance – Caching
More Data Formats
More Robust
New, Bigger
Database Server
Database Server
SQL Server 2005 supports advanced hardware and OS features
As You Grow
Secure By Default
Scale Up
Scale Out – Messaging
Performance – Caching
More Data Formats
More Robust
Order
Inv
Bill
Reliable transactional
messaging with
SQL Server Service Broker
As You Grow
Secure By Default
Scale Up
Scale Out – Messaging
Performance – Caching
More Data Formats
More Robust
Order
Inv
Bill
Master data management
and better performance
with Query Notifications
As You Grow
Secure By Default
Scale Up
Performance – Caching
Scale Out – Messaging
More Data Formats
More Robust
Order
Inv
Bill
<invoice/>
Native XML storage
Improved LOB types
Custom data types
and aggregates
As You Grow
Secure By Default
Scale Up
Performance – Caching
Scale Out – Messaging
More Data Formats
More Robust
Order
Inv
BEGIN TRY ...
END TRY
BEGIN CATCH ...
END CATCH
Bill
Exception Handling
in Transact-SQL
<invoice/>
Improved Security
From the Ground Up
Security
 SQL Server is part of the Trustworthy
Computing initiative
 Whether your company is small, medium, or
large, security is not optional
 Data is your company's view of "reality"
 You must be secure for accurate picture
 Database security consists of
 Security by design - integrated with policy
 Security by default
 Secure deployment and maintenance
 Secure communications and storage
Off by Default
SQL Server Surface Area Configuration
Secure Data & Metadata
 User-schema separation - database
objects need not be tied to users
 Fixes "user leaves company" problem
 Allows DBA to allow installation of packages with
owners other than DBO
 Allows separation of database object owners even
within a single database
 Secure Metadata
 You can only see what you can access
 Consolidation without seeing others' data
 All Permissions Grantable
 Granular permissions
Encryption and Privilege
 Some industries require encryption
 Encryption keys securely stored in database
 Instance key protected by DPAPI
 Logins are always encrypted
 Procedures can be signed or run as certain
accounts
 Principle of least privilege
 Original login always available for auditing
 Proxy accounts for SQL Agent jobs
Cryptography 101
Symmetric Key Encryption
Encryption
0x0088840517080E4FA2…
1234-5678-1234-5678
Decryption
Asymmetric Key Encryption
Encryption with public key
1234-5678-1234-5678
Public Key
0x0088840517080E4FA2…
Decryption with private key
Private Key
SQL Server Encryption

Good Scenario:
 Encrypting secrets during





login
Using asymmetric keys to
generate session keys
Using symmetric keys for
data encryption
Using SQL Server
certificates from trusted
sources
Encrypting data as required
by law
Bad Scenario:
 Encrypting all network




traffic inside an organization
Using asymmetric keys for
data encryption (slow)
Using symmetric keys for
main key distribution
mechanism
Using SQL Server as a
certificate server
Encrypting all data (SLOW,
and data can't be used for
indexes and joins)
Encapsulating Encryption
low-priv
Credit Card #
Credit View
Has access to view
Low-priv
needs access
to both keys
Execute As and Encryption
low-priv
Has access to view
low-priv
Credit Card #
Credit View
Low-priv
needs access
to both keys
Credit View
Has access to view
Credit Card #
Decrypt Helper
EXECUTE AS DBO
Low-priv has
no access to keys
Data Security
Name
Title
Company
Defense in Depth
 Using a layered approach:
 Increases an attacker’s risk of detection
 Reduces an attacker’s probability of success
Data
Application
Host
Internal Network
Perimeter
Physical Security
Policies, Procedures, &
Awareness
Permissions, encryption, secure
metadata
Execute As, signed procs, schemas
SQLOS/SQLCLR hardening
SSL, session keys, cert security
Firewalls, packet filters
Guards, locks, tracking devices,
HSM, tamper-evident labels
Password policies, off by default
Summary: Security
Technology
Off by Default &
Password Policies
Metadata security
All permissions
grantable
User/Schema
Separation
Keys and Encryption
Execution Context
Signed Procs
Improves
When
 Greater security at install time
 Integrated Windows/SQL policies
Upgrade
Immediate
 Less exposed surface area
 Permissions easier to manage
Upgrade
Immediate
 No recoding when staff change
 Separate DBO and developers
Upgrade
Immediate
 Compliance with privacy
requirements
 Secure communications
Minimal Work to
Leverage
Principle of least privilege
Auditability
Design And
Architect
SQL Server 2005 Scales
 With Hardware and Operating System
 With Database Features
 With Application Design
Scaling
 Now that we're secure, we may need to
scale the application when we're a success
 You can scale with hardware
 Or you can build scalability into your
application architecture
 SQL Server is designed to scale
 Lots of service oriented scale-by-architecture
features added
 It's used by a wider variety of app sizes
Scaling - Hardware Options
 SQL Server optimized for hardware & OS
 Known as the "SQLOS" abstraction
 This enables better support on
 64 bit architectures
 NUMA systems
Dual core support
at no extra cost
 Threads managed as tasks
 Enables SQL Server use of new OS features
 Windows Server 2003
Windows Server 2003 Enabled
 Password policy check for SQL passwords
 Hot add memory
 Dynamic AWE
 Native 64 bit support
 SOAP support
 Instant file initialization
 8 node SQL Server failover cluster
Scaling Data with Services
 Functionality built-in to SQL Server
 Asynchronous Operations - Service Broker
 To achieve scalable, resilient large scale systems
 System continues to work with partial outage,
things just queue up
 Cache coherency - Cache Sync
 Master data management
 Request-response - Web Services
 Industry standard protocol
 Service Programs can be T-SQL or SQLCLR
 Better performance and flexible deployment
SQL Service Broker
 Platform for building reliable, asynchronous,
loosely coupled database applications
 Queues are database objects
 Input in one transaction/context, execute in a different one
 Queue locking reduces conflicts and deadlocks
 Locks are based on dialogs (point-to-point conversation)
 Dialogs give unprecedented message ordering
 Reliable, durable, sequenced communications
session between services
 Ordering even across transactions
 New DDL and DML for messaging
 Use the same API’s and tools as vanilla SQL
 Activation - the right number of readers running
 To service the queues
Dialogs
 Dialogs provide two-way messaging between two services
 Dialogs offer:
 Guaranteed delivery (even across transactions)
 Exactly-once delivery
 In-order delivery
 Secure communications
 Dialogs:
 May be long-lived (years) or short-lived (seconds)
 Are light-weight
 Are persistent sessions
Customer
Service
Travel
Service
Dialog
Database A
Database B
Messaging with Service Broker
 Inbound messages arrive on protocol pipe
 Message is:
 Authenticated
 Dispatched to appropriate queue
 Service Programs:
 Pick up work from queue
 Run in different context than
incoming message
 May run inside or outside server
 May send additional messages
Message
X
Service Program
(decrement_inventory)
System continues to run
if service program
or queue is unavailable!
Message
Service Broker
Name
Title
Company
Query Notifications
 Notify Caches When
Master Data Changes
 built into SQL Server 2005
 based on indexed view
notifications
 built into ADO.NET
 cache listeners can be scaled
to multiple machines using
SQL Express
 delivery via Service Broker
multiple
granular
replicas
 built into ASP.NET
 automatic cache invalidation
 Known as Cache Sync
 two lines of code
master data
CacheSync
Web Request
Query
Results
Subscription
CacheSync
Web Request
Subscription
CacheSync
Subscription
UPDATE dbo.Products SET …
Web Services and SQL Server
 SQL Server 2005 can
 Be used for HTTP-based web services on any
OS that supports HTTP in the kernel
 Execute any stored procedure and return
results using SOAP packets
 Allow custom WSDL to support
heterogeneous clients
 Use to wrap internal legacy systems
 asynchronous access
 Use SQL Express and Web Services as a
network input to a Service Broker application
Summary: Scalability
Technology
SQLOS
Service Broker
Messaging
Cache Sync
Web Services
SQL Server
Notification
Services
Improves
When
Advanced hardware support
Upgrade
Immediate
 Advanced OS feature support
 Reliable system, even with partial outage
 Scalability - service based
 Large scale transaction messaging
 Load balance over machines and time
 Granular synchronization
Master data management
Two lines of ASP.NET code
Heterogeneous integration
Complement to Service Broker
Pre-built architecture component
Scales to larger number of events
Useable over multiple machines
Design and
Architecture
Minimal Work
To Leverage
Design and
Architecture
Upgrade
Immediate
Flexibility
 Storage Options
 Programming Options
 Deployment Options
IT Manager Dilemma
T-SQL
Computation &
Framework access
CLR
XML
Relational
data access
Semi-structured
data access
Flexibility
 Storage Options
 Programming Options
 Deployment Options
Data Type Options
 The relational data types serve enterprise
applications well but...
 There's always been a tension with large data
 In database or file systems?
 XML becoming common for all industries
 In B2B, B2C, data exchange
 XML is a standard for data on the web
 To evolve and integrate your business(es) you may
need to support XML
 Domain-specific types used by some
industries
Data Type Enhancements
 Relational is native for SQL Server
 Relational "open-schema" helped by PIVOT
 Assists sparse population & name-value pairs
 Hierarchical queries with common table expressions
 Large value type support is better
 MAX data types subsume TEXT and IMAGE
 XML is new built-in alternative
 Through XML data type and query
 Custom types and aggregates available
 Through SQLCLR UDT for custom scalars
 Through SQLCLR custom aggregates
Large Object Storage
 New LOB support
 VARCHAR(MAX)/NVARCHAR(MAX),
VARBINARY(MAX)
 work like (N)VARCHAR, VARBINARY
 support most T-SQL manipulation functions
 extended support for large data through
extension methods (WRITE method)
 up to 2gb in size, extendable in future
XML Support
 XML is a first class data type in SQL Server 2005
 Native XML storage
 no need to store XML as TEXT
 no hassles integrating with XML on file system
 document-centric or data-centric XML
 XML Schema support
 validation on input and update
 schema collections support schema versioning
 Native XQuery
 query in place - no need to retrieve over network
 XML Indexes
 XML processing uses same query processor as SQL
XML Data Type & Schema
Name
Title
Company
XML Query
 XQuery is the standard language for XML
and databases
 Implemented with XML data type methods
 exist(), value(), query() operate on XML
 nodes() produces rowsets from XML
 modify() changes XML in place
 Uses XPath for data selection
 Can be used with T-SQL
 sql:variable and sql:column available in XQuery
 Can be combined with fulltext search
Scenario for XML Development

Good Scenario:
 Data is semi-structured,

small core of fixed data with
many, sparsely populated
extended attributes
 Multi-value Property bags
 Complex Property bags
 “WordXML”
 Fixed data can be stored as
relational columns
 Documents are large but
rarely updated
 Indexing will pay off
 Data is hierarchical
 path expressions are
well suited for finding data
Bad Scenario:
 “Database in a Cell”
 Documents are large and
updated frequently
 Document update
contention is likely
 Data is fully structured &
populated  candidate for
conversion to relational
schema
 Data contains large binary
objects (2GB limitation)
Data Type Specialization
 Custom data types
 User-defined types for custom scalars
 assists with heterogeneous data domains
 adjunct to SQL Server built-in types
 User-defined aggregates for custom formulas
 alternative to mailto:sqlwish for domain-specific
Improved Support for...
Model
Schema
Query
Extension
Strict
Relational
Tables and
Relations
Relational
Schema
SQL
T-SQL
SQLCLR
Hierarchical
Tables or
XML
Relational /
XML Schema
SQL
Recursive
CTE
Sparse
Attribute
Tables or
XML
Name/Value
XML Schema
SQL or
XQuery
PIVOT
T-SQL
SQLCLR
Semistructured
Or Markup
XML
XML Schema
XQuery
XPath
FullText
Unstructured
MAX
Datatypes
IFilter
FullText
Custom
Scalars
UDT
Custom
SQL
Custom
Methods
Summary: Data Types
Technology
Improves
When
 Semi-structured data mgmt
XML Support
Markup language document mgmt
Validation/integration of XML and SQL
Design and
Architect
 XML Indexes can improve performance
SQL
Enhancements
Support for hierarchical data
Open schema processing
Sparse attribute data models
Upgrade
Immediate
In-database aggregation
New LOBs
Data just over the VARCHAR limit
Programming with large data
Buffer management for large rows
User-Defined
Types and
Aggregates
Domain-specific data management
Domain-specific formulas
Inter-database interoperability
Minimal Work
to Leverage
Design and
Architect
Flexibility
 Data Type Options
 Programming Options
 Deployment Options
T-SQL and SQLCLR
 SQL is the language of relational database
 Procedural code can be
 T-SQL
 Native usage of logic with SQL statements
 Built into SQL Server since its inception
 Continuing enhancements with each release
 SQLCLR
 .NET framework code running in SQL Server
 Enhances and compliments T-SQL
 Not a replacement for T-SQL
 or set based operations
T-SQL Enhancements
 T-SQL is the language of 99% of pre-SQL
Server 2005 procedural code
 Procedural enhancements
 Robust structured error handling comes to T-SQL
 Output clause in SQL
 SQL enhancements
 Standard hierarchical recursive queries
 Better support for sparse attributes (PIVOT)
 Ranking, Row Numbering functions
 INTERSECT and EXCEPT
 Others
T-SQL Enhancements
Name
Title
Company
Programmability
.NET Integration Key Differences
 CLR Runs in SQL Server Process Space:
 SQL Server manages memory access etc
 Calls to SQL never Cross the Process Boundary
 Assemblies Stored in SQL Server, not the file system
 All CLR Objects get included in:




Backups
Replication
Mirroring
Clustering
 Security
 Integration of SQL and CLR security
 Three levels of code access security
 Safe, External-Access (verifiable), Unsafe
SQLCLR and SQLOS
SQL Server 2005
Engine
Applications
Integrated Resource
Management
built-in, not grafted on
SQLCLR
CLR Hosting
Transact-SQL
SQLOS - System Services
Diverse Hardware /Windows Operating Systems
Assemblies stored in
the database,
not the file system
Good Scenario for CLR Usage
 Data validation & network traffic reduction
 Writing general purpose functions:
 Data passed as arguments
 Little/no additional data access
 Complex computation applied on a row by row basis
 Scalar types & custom aggregations
 Leveraging the power of the .NET Framework
 Access to a rich set of pre-built functionality
 Replacing Extended Stored Procedures (XP)
 The CLR is safer:





No access violations making SQL Server crash
No leaks making SQL Server slow down & crash
Better performance & scalability (managed memory model)
No security issues…
Bad Scenario for CLR Usage
 Heavy data access

 Transact-SQL set based access will be faster
 Don’t write SELECT statements as CLR procedures!
 Replacement for T-SQL built in functions
 Use to enhance, not replace T-SQL
 Your application must support previous versions
of SQL Server
 Large complex types
 Don't replace tables with user-defined types
 Locks are at row level
 Technology for technology’s sake…
Summary: Programmability
Technology
T-SQL
Enhancements
SQLCLR
Procedures
Improves
 More robust error handling
 Row numbering and ranking in DB
 Using large rowsets without cursors
 Logic intensive procedures
 Complex mathematics
 Functions that are .NET built-ins
When
Minimal Work
to Leverage
Design and
Architect
Flexibility
 Data Type Options
 Programming Options
 Deployment Options
Deployment Options
 There's always been a choice between in-
database and middle tier/client logic
 Sometimes the topology changes over time
 Machine power vs machine numbers
 Network bandwidth
 Smart client
 Programming toolkits may facilitate moving
processing around (more agile system)
 T-SQL usually best in DB
 .NET code can move from DB <-> middle tier
 XML can be processed in either tier too
Flexible Deployment
with SQLCLR code
Prod_Sched
run in middle tier
to ease pressure
on database
Prod_Sched
...or run in database
for locality of data and logic
SQLCLR: Whiten Wagner
Name
Title
Company
Summary
 More secure by default
 Better security integration with policies
 Secure code, data, metadata
 More scalable
 Scale up with SQLOS
 Scale out with Service Orientation in design
 More data models
 Relational, XML, Large Data, Custom Types
 More robust query models
 Procedural alternatives
Architectural Enhancements
Technology
Improves
 Integrated, built-in security policy
Security
Secure data and metadata
Enables principle of least privilege
 The way to build scalable, resilient
Service Broker
XML Support
SQLCLR
Procedures
T-SQL
Enhancements
When
Upgrade
Immediate
large-scale systems
Design and
Architect
Queues and dialogs with transactional
consistency
 Storage, schema, query, indexing
Design and
Business data and documents
Architect
Native Web Service support
 Logic intensive service programs
Design and
Architect
 Adjunct to Transact-SQL
Data access language of SQL Server Minimum Work
to Leverage
Robust exception handling
Take Advantage When?
How much work to leverage the technology?
Minimal Work to
Leverage
Upgrade
Immediate





Security by Policy
Secure Metadata
Granular Permissions
Support for
Advanced
OS/Hardware
features
Relational Engine
Speed-ups





User/Schema
Separation
Query Notifications
SQLCLR Procedures

LOB Data Types
T-SQL TRY/CATCH
New T-SQL Statements
Data Paging

XML Processing



Design and
Architect






Service Broker
Web Services
Data Encryption and
Key Management
Execute Context for
Procedures
XML Type
UDTs/UDAggregates
Improving Security and Scalability from Installation to Design
Availability in Layers to allow re-architecting in stages