1 / 61

Architecting Scalable, Flexible and Secure Database Systems with SQL Server 2005

Architecting Scalable, Flexible and Secure Database Systems with SQL Server 2005. 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"

kelsey-hurley
Download Presentation

Architecting Scalable, Flexible and Secure Database Systems with SQL Server 2005

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Architecting Scalable, Flexible and Secure Database Systems with SQL Server 2005

  2. 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?

  3. Take Advantage When?How much work to leverage the technology? Design and Architect Upgrade Immediate Minimal Work to Leverage • Security by Policy • Secure Metadata • Granular Permissions • Support for Advanced OS/Hardware features • Relational Engine Speed-ups • Notification Services Integration • User/Schema Separation • Cache Sync • SQLCLR Procedures • LOB Data Types • T-SQL TRY/CATCH • New T-SQL Statements • Data Paging • XML Processing • 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

  4. Secure By Default, now... Scale Up? Scale Out? Caching? More Data Formats? More Robust? Start Small... Web Server in DMZ Secure Connections Database Server Secure Data And Metadata Your Internal Network

  5. Secure By Default Scale Up Scale Out – Messaging Performance – Caching More Data Formats More Robust Or Start Big... Web Server New, Bigger Database Server Database Server SQL Server 2005 supports advanced hardware and OS features

  6. Secure By Default Scale Up Scale Out – Messaging Performance – Caching More Data Formats More Robust As You Grow Order Reliable transactional messaging with SQL Server Service Broker Inv Bill

  7. Secure By Default Scale Up Scale Out – Messaging Performance – Caching More Data Formats More Robust As You Grow Order Inv Master data management and better performance with Query Notifications Bill

  8. Secure By Default Scale Up Performance – Caching Scale Out – Messaging More Data Formats More Robust As You Grow Order Inv Native XML storage Improved LOB types Custom data types and aggregates Bill <invoice/>

  9. Secure By Default Scale Up Performance – Caching Scale Out – Messaging More Data Formats More Robust As You Grow Order BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH Inv Bill Exception Handling in Transact-SQL <invoice/>

  10. Improved Security From the Ground Up

  11. 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

  12. Off by DefaultSQL Server Surface Area Configuration

  13. 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

  14. 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

  15. Cryptography 101 Symmetric Key Encryption Encryption 0x0088840517080E4FA2… 1234-5678-1234-5678 Decryption Asymmetric Key Encryption Encryption with public key 0x0088840517080E4FA2… 1234-5678-1234-5678 Private Key Public Key Decryption with private key

  16. 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) SQL Server Encryption  

  17. low-priv Encapsulating Encryption Credit Card # Credit View Has access to view Low-priv needs access to both keys

  18. low-priv low-priv Execute As and Encryption Credit Card # Credit View Has access to 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

  19. Defense in Depth • Using a layered approach: • Increases an attacker’s risk of detection • Reduces an attacker’s probability of success Policies, Procedures, & Awareness Physical Security Permissions, encryption, secure metadata Data Application Execute As, signed procs, schemas SQLOS/SQLCLR hardening Host Internal Network SSL, session keys, cert security Firewalls, packet filters Perimeter Guards, locks, tracking devices, HSM, tamper-evident labels Password policies, off by default

  20. Summary: Security

  21. SQL Server 2005 Scales • With Hardware and Operating System • With Database Features • With Application Design

  22. 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

  23. Scaling - Hardware Options • SQL Server optimized for hardware & OS • Known as the "SQLOS" abstraction • This enables better support on • 64 bit architectures • NUMA systems • Threads managed as tasks • Enables SQL Server use of new OS features • Windows Server 2003 Dual core support at no extra cost

  24. 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

  25. 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

  26. 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

  27. Dialog Customer Service Travel Service Dialogs • Dialogs provide two-way messaging between two services • Dialogs offer: • Guaranteed delivery • Exactly-once delivery • In-order delivery • Secure communications • Dialogs: • May be long-lived (years) or short-lived (seconds) • Are light-weight • Are persistent sessions Database A Database B

  28. X System continues to run if service program or queue is unavailable! 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 Service Program (decrement_inventory) Message Message

  29. 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 • built into ASP.NET • automatic cache invalidation • Known as Cache Sync • two lines of code multiple granular replicas master data

  30. CacheSync Query Web Request Results Subscription

  31. CacheSync Web Request Subscription

  32. CacheSync Subscription UPDATE dbo.Products SET …

  33. 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

  34. Summary: Scalability

  35. Flexibility • Storage Options • Programming Options • Deployment Options

  36. IT Manager Dilemma Computation & Framework access T-SQL CLR XML Semi-structureddata access Relational data access

  37. Flexibility • Storage Options • Programming Options • Deployment Options

  38. 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

  39. 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

  40. 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

  41. 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

  42. 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

  43. 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) Scenario for XML Development  

  44. 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

  45. Improved Support for...

  46. Summary: Data Types

  47. Flexibility • Data Type Options • Programming Options • Deployment Options

  48. 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

  49. 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

More Related