Architecting Scalable Flexible and Secure Database Systems with

Architecting Scalable, Flexible and Secure Database Systems with SQL Server 2005 Adam Calderon Principal Engineer Interknowlogy LLC adamc@interknowlogy. com 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 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 0 x 0088840517080 E 4 FA 2… 1234 -5678 -1234 -5678 Decryption Asymmetric Key Encryption with public key 1234 -5678 -1234 -5678 Public Key 0 x 0088840517080 E 4 FA 2… 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 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 Keys and Encryption Compliance with privacy requirements Secure communications Minimal Work to Leverage Execution Context Signed Procs Principle of least privilege Auditability Design And Architect Off by Default & Password Policies Metadata security All permissions grantable User/Schema Separation

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 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 multiple SQL Express granular delivery via Service Broker replicas built into ASP. NET automatic cache invalidation Known as Cache Sync two lines of code master data

Cache. Sync Web Request Query Results Subscription

Cache. Sync Web Request Subscription

Cache. Sync Subscription UPDATE dbo. Products SET …

Web Services and 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 Improves When SQLOS Advanced hardware support Advanced OS feature support Upgrade Immediate Service Broker Messaging Cache Sync 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 Design and Architecture Minimal Work To Leverage Web Services Heterogeneous integration Complement to Service Broker Design and Architecture SQL Server Notification Services Pre-built architecture component Scales to larger number of events Useable over multiple machines 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 B 2 B, B 2 C, 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 2 gb 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 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 “Word. XML” 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 (2 GB 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 Schema XQuery XPath Full. Text Unstructured MAX Datatypes IFilter Full. Text Custom Scalars UDT Custom SQL Custom Methods

Summary: Data Types Technology Improves When XML Support Semi-structured data mgmt Markup language document mgmt Validation/integration of XML and SQL XML Indexes can improve performance Design and Architect Support for hierarchical data Open schema processing Sparse attribute data models In-database aggregation Upgrade Immediate SQL Enhancements New LOBs User-Defined Types and Aggregates Data just over the VARCHAR limit Programming with large data Buffer management for large rows 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 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 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 indatabase 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 Security Service Broker XML Support SQLCLR Procedures T-SQL Enhancements Improves When Integrated, built-in security policy Upgrade Secure data and metadata Immediate Enables principle of least privilege The way to build scalable, resilient 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