Building Mission Critical Systems with SQL Server 2005

1
(No Transcript)
2
Agenda

• Managing and Monitoring 2005
• Scalable, Flexible and Secure DB
• Delivering Business Insight
• Building Highly Available SQL DB
• Training and Certifications on SQL05

3
Demos

• Tour
• Surface Area Configuration
• DDL Triggers
• Profiler
• Maintenance Plans, SQLCMD
• XML Data Type and Schema
• T-SQL Enhancements
• Fuzzy Cleansing

4
Demos Contd

• Mining for Meaning
• Reporting Services
• Partial Database Availability
• Snapshot Isolation
• DB Mirroring
• Peer to Peer Replication

5
Managing and Monitoring SQL Server 2005
6
You Can Be More Productive

• Managing and Administering
• Maintaining Security/Operational Policies
• Monitoring and Troubleshooting
• Performance Tuning
• Automating The Work
• By Just Upgrading

7
Management Studio

• Integrated management and development environment
• Based on Visual Studio .NET
• Integrates Functionality of
• Enterprise Manager
• Query Analyzer
• Analysis Manager
• Used to manage
• Relational databases
• Analysis Services
• Reporting Services
• SQL Server Mobile databases
• Includes scripting tools for
• Transact-SQL/SQLCMD
• XMLA/MDX/DMX
• Provides graphical reports
• Much, much more!

8
A Tour of SQL Server Management Studio
9
Protect Your ServerSQL Server Surface Area
Configuration

• Configuration for Services and Connections
• Service Settings
• Remote Connections
• SQL Server Agent
• Reporting Services
• Integration Services
• SQL Server Browser
• Configuration for Features
• sp_configure settings
• Catalog Table queries
• Database Mail
• xp_cmdshell
• CLR Integration

10
SQL Server Surface Area Configuration
11
Auditing and Change ControlControlling and
Monitoring Change

• DDL Triggers targeting
• Specific Commands DROP_TABLE
• Groups DDL_PROCEDURE_EVENTS
• CREATE PROCEDURE ? ALTER PROCEDURE
• DROP PROCEDURE
• Event Notifications and WMI Events target all
  of the above, plus
• Specific Trace Events DATA_FILE_AUTO_GROW
• Trace Groups TRC_STORED_PROCEDURES
• SP_RECOMPILE ? SP_CACHEMISS
• SP_CACHEREMOVE ? SP_CACHEINSERT

12
DDL Triggers
13
MonitoringA Unified Approach

• Supports multiple logs
• SQL Server Database Engine Information
• SQL Server Agent Information
• Windows Event Viewer Application Log
• Database Mail Logs
• Across all logs, allows
• Searching
• Filtering
• Exporting

14
SQL Server Profiler

• Analyze the SQL Server Database Engine and
  Analysis Services
• Significantly easier to setup (Events, Data
  Columns and Filter dialogs combined)
• Special Events Service Broker, Notification
  Services, etc
• Special Event types Showplan XML and Deadlock
  Graph can be saved to files
• Supports pause and modify
• Can Profile SQL Server 2000 and 2005
• Permissions to profile are grantable

15
System Monitor Integration Performance Counter
Logs

• Create a Profiler Trace
• Create a Performance Monitor Log
• Open Trace (complete load), Use File Import
  Performance Data, Select Objects/Counters
• Works solely based on time make sure the two
  clients (if different) are time correlated
• Can select in either data set and indicators
  correlate
• Works with SQL Server 2000 and SQL Server 2005

16
A Tour of SQL Server Profiler
17
Database Engine Tuning Advisor

• Partitioning recommendations
• Time-bound tuning
• Indexes with Included columns
• XML Input/Output
• Drop ONLY mode
• Parameterized command line execution
• Import previously saved Session Definition (XML
  format)
• Workload options
• Can be a .trc, .sql or .xml format
• Can be a SQL Server Table

18
Database Maintenance Plans

• Based on SQL Server Integration Services
• Flexible Wizard based Interface
• Options to create cross database plans
• Backup all users databases will pick up newly
  added databases even after the plan is created

19
Database Mail

• Uses SMTP No Microsoft Outlook dependency!
• Mail calls made outside SQL Server process
• Cluster support
• 64-bit support
• SMTP account failover
• No SQLCLR dependency
• Asynchronous and queued architecture
• Leverages SQL Server Service Broker for queuing
• Multiple SMTP accounts
• Logging

20
SQLCMDCommand-line automation and scripting

• Replaces OSQL
• Supports Connections to multiple servers (master
  scripts)
• Allows Parameter substitution
• Variables defined in script
• Variables passed in
• Environment variables
• Better Control on Error
• Initialization Scripts
• Dedicated Admin Connection

21
Database Maintenance Plans, Database Mail and
SQLCMD
22
Upgrade AdvisorGet Ready!

• Checks schema for best practices and
  compatibility
• Upgrade Advisor on microsoft.com
• SQL Server 2005 Upgrade Handbook
• SQL Server 2005 Editions http//www.microsoft.com
  /sql/2005/productinfo/sql2005features.mspx

23
Why Upgrade?

• Database Engine
• Service Broker
• HTTP Access
• Database Tuning Advisor
• Enhanced Read ahead scan
• Indexes with Included Columns
• Multiple Active Result Sets
• Persisted Computed Columns
• Try/Catch in T-SQL statements
• Common Table Expressions
• Server Events
• Snapshot Isolation Level
• Partitioning
• Synonyms
• Dynamic Management Views
• .NET Framework
• Common Language Runtime Integration
• CLR-based Types, Functions, Triggers
• SQL Server .NET Data Provider

• Database Maintenance
• Backup and Restore Enhancements
• Checksum Integrity Checks
• Dedicated Administrator Connection
• Dynamic Configuration AWE
• Highly-available Upgrade
• Online Index Operations
• Online Restore
• Management Tools
• MDX XML/A Query Editor
• Maintenance Plan Designer
• Source Control Support
• Profiler access to non-sa
• SQLCMD Command Line Tool
• Database Mail
• Performance Tuning
• Profiling Analysis Services
• Exportable Showplan Deadlocks
• Profiler Enhancements

• Replication
• Seamless DDL replication
• Merge Web Sync
• Oracle Publication
• Peer to Peer Transactional replication
• Merge replication perf and scalability
• New monitor and improved UI
• Analysis Services and Data Mining
• Analysis Management Objects
• Windows Integrated Backup and Restore
• Web Service/XML for Analysis
• Integration Services and DM Integration
• Eight new Data Mining algorithms
• Auto Packaging and Deployment
• Migration Wizard
• Integration Services
• New high performance architecture
• Visual design and debugging environment
• Extensible with custom code and scripts

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

26
Off by DefaultSQL Server Surface Area
Configuration
27
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

28
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

29
Cryptography 101
Symmetric Key Encryption
Encryption
1234-5678-1234-5678
0x0088840517080E4FA2
Decryption
Asymmetric Key Encryption
Encryption with public key
0x0088840517080E4FA2
1234-5678-1234-5678
Private Key
Public Key
Decryption with private key
30
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
31
Defense in Depth

• Using a layered approach
• Increases an attackers risk of detection
• Reduces an attackers probability of success

Policies, Procedures, Awareness
Physical Security
Permissions, encryption, secure metadata
Data
Application
Execute As, signed procs, schemas
SQLOS/SQLCLR hardening
Host
SSL, session keys, cert security
Internal Network
Firewalls, packet filters
Perimeter
Guards, locks, tracking devices, HSM,
tamper-evident labels
Password policies, off by default
32
Summary Security
33
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

34
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
35
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

36
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
37
CacheSync
Query
Web Request
Results
Subscription
38
CacheSync
Web Request
Subscription
39
CacheSync
Subscription
UPDATE dbo.Products SET
40
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

41
IT Manager Dilemma
Computation Framework access
T-SQL
CLR
XML
Semi-structureddata access
Relational data access
42
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

43
Data Type Enhancements

• 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

44
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

45
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

46
XML Data Type Schema
47
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
• sqlvariable and sqlcolumn available in XQuery
• Can be combined with fulltext search

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

50
T-SQL Enhancements
51
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

52
SQLCLR (hidden)

• .NET CLR is integrated into
• SQL Server security model
• Classes loaded only from database
• Custom permission sets
• Normal GRANTs
• SQL Server reliability model
• Safe and External Access mode is reliable as
  T-SQL
• Unsafe is like safer extended stored procedure
• Resource management (through SQLOS)
• Storage
• Threads
• Buffers

53
SQLCLR and SQLOS
SQL Server 2005 Engine
Integrated Resource Management built-in, not
grafted on
Applications
SQLCLR
Assemblies stored in the database, not the file
system
CLR Hosting
Transact-SQL
SQLOS - System Services
Diverse Hardware /Windows Operating Systems
54
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

?
55
Bad Scenario for CLR Usage
?

• Heavy data access
• Transact-SQL set based access will be faster
• Dont 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 technologys sake

56
Summary Programmability
57
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

58
Delivering Business Insightwith SQL Server 2005
59
Why Is It So Difficult To Achieve A Higher Return
Of Information Today
Most companies today have a tangled web of IT and
human systems. This tangled web has a high
degree of inefficiency, complexity, and risks.
Many points of data integrationpoor data
integrity and reliability
High degree ofdata cleansing and
re-entrylabor-intensive
Data Marts Data Warehouses
High IT involvementlonger time-to-value
SourceSystems
Text Mining
ERP
Hand coding
CRM
Multiple solutions more costly, and frustrates
users
LOB
ETL
Data Analysis(OLAP, Data Mining)
Human input prone to error
Many disconnected systemsincomplete
datamultiple versions of truth
60
MicrosoftBusiness IntelligenceVision Strategy
Improving organizations by providing business
insights to all employees leading to better,
faster, more relevant decisions

• Complete and integrated BI offering
• Widespread delivery of intelligence through
  Microsoft Office
• Enterprise grade and affordable

61
SQL Server GenerationsA History of Business
Intelligence Innovation
1st Generation
2nd Generation
3rd Generation
SQL Server 2000 Reporting Services
SQL Server 2000
SQL Server 2005
SQL Server 7.0

• BI High availability
• Scalability
• Ad Hoc Reporting
• Enhanced Security
• Developer productivity
• First to include Enterprise ETL and Deep Data
  Mining

• First to include Enterprise Reporting solution
• Rich Report Design, Deployment Management

• Real-Time OLAP
• First RDBMS to include Data Mining
• Comprehensive Dimension Types
• 64Bit Support

• First RDBMS to integrate OLAP and ETL
• Excel Integration
• Multi-vendor support
• Clustering out of the box
• OLAP for Everyone

OLAP Services ? Analysis Services

• Lowest TCO
• Automatic Tuning

• Reliability and Security
• Integrated Business Intelligence

Cross-release objectives
62
OLAP Leadership

• http//www.olapreport.com

63
Decision ReadyBusiness Intelligence
SQL Server Reporting ServicesAvailable in All
Editions
Integrate
Analyze
Report

• Data acquisition from source systems and
  integration
• Data transformation and synthesis

• Data enrichment, with business logic,
  hierarchical views
• Data discovery via data mining

• Data presentation and distribution
• Data access for everyone

Delivering the Complete Business Intelligence
Toolset
64
Integration ServicesWhy ETL Matters
(Extract/Transform/Load)

• Merge data from heterogeneous data stores
• Text files ?Mainframes
• Spreadsheets ? Multiple RDBMS
• Refresh data in data warehouses and data marts
• Cleanse data before loading to remove errors
• High-speed load of data into online transaction
  processing (OLTP) and online analytical
  processing (OLAP) databases
• Send status notifications on success/failure
• Build BI into a data transformation process
  without the need for redundant staging
  environments
• Automate data-administrative functions

65
Integration ServicesBreakthrough ETL Capabilities

• Enterprise ETL platform
• High performance
• High scale
• More trustworthy and reliable
• Best in class usability
• Rich development environment
• Source control
• Visual debugging of control flow and data
• Great range of transforms out-of-the-box
• Highly extensible
• Custom tasks
• Custom enumerations
• Custom transformations
• Custom data sources

66
Data Integration Architecture Before Integration
Services

• Integration and warehousing require separate,
  staged, operations.
• Preparation of data requires different, often
  incompatible, tools.
• Reporting and escalation is a slow process,
  delaying smart responses.
• Heavy data volumes make this scenario
  increasingly unworkable.

67
Data Integration Architecture With Integration
Services
Data Integration Architecture With Integration
Services

• Integration and warehousing are a seamless,
  manageable, operation.
• Sourced, prepare and load data in a single,
  auditable process.
• Reporting and escalation can be parallelized with
  the warehouse load.
• Scales to handle heavy and complex data
  requirements.

68
How SQL Server Integration Services Works

• Data sources can be diverse, including custom or
  scripted adapters
• Transformation components shape and modify data
  in many ways.
• Data is routed by rules or error conditions for
  cleansing and conforming.
• Flows can be as complex as your business rules,
  but highly concurrent.
• And finally data can be loaded in parallel to
  many varied destinations.

69
SQL Server Integration ServicesNew Paradigm for
the ETL Platform

• Data Cleansing
• Provides data mining and AI expertise
• Domain-independent data cleansing
• Fuzzy lookup
• Lookup on approximate matches
• Tune for best match
• De-duplication
• Eliminate approximate duplicates
• Windows XP, WinXP, etc.
• Tune for confidence
• Managing Slowly Changing Dimensions
• E.g. Sales organization changes
• E.g. Customer movement
• E.g. Product category changes

70
Fuzzy Cleansing
71
Analysis ServicesWhy OLAP and Data Mining Matter

• One version of the truth
• Powerful business information modeling
• Cross platform data integration
• Integrated Relational OLAP views
• The best of MOLAP to ROLAP
• Data enrichment and advanced analytics
• Key Performance Indicators and Perspectives
• Real-time, high performance
• Real-time data in OLAP Cubes
• Very fast and flexible analytics
• XML standards for Data Access and Web Services
  integration
• Cost and time savings for customers integrating
  with other systems

72
Analysis ServicesEnhanced OLAP and Data Mining
Capabilities

• Unified Dimensional Model
• Pro-active caching
• Advanced Business Intelligence
• KPI/Perspectives
• Custom/Limited Aggregations and Semi-Additive
  Measures
• Web services
• Data Mining in the platform
• Integrated Developer Tools
• Failover Clustering

• plus
• Logistic Regression
• Linear Regression
• Text Mining

Decision Trees
Clustering
Naïve Bayes
Introduced in SQL Server 2000
73
New Capabilities

• Development Tools
• Business Intelligence Development Studio
• SQL Server Management Studio
• Analysis Services Wizards
• Analysis Services Templates
• Profiler Integration
• Cubes
• Key Performance Indicators
• Multiple Fact Tables
• Business Intelligence Smarts
• Perspectives
• Custom/Limited Aggregations and Semi-Additive
  Measures

• Infrastructure
• Multi-Instance Support
• Failover Clustering
• Web Service (XML/A) Support
• Scalable, Proactive Caching
• Advanced Language and Collation Support
• Parallel Processing Support
• Enhanced Referential Integrity Issue Handling
• Dimensions
• Enhanced Attribute Support
• True Multiple Hierarchies
• Many-to-Many Dimension Relationships
• Reference Dimension Relationships
• Fact (Degenerate) Dimension Relationships
• Role-Playing Dimension Relationships
• Simplified Dimension Types
• Linked Measure Groups and Dimensions
• Member Groups No Longer Required for large levels
• Dimension Size Virtually Unlimited

• Development Capabilities
• Microsoft .NET Framework Support
• Persisted Calculations
• Stored Procedures
• Multidimensional Expressions Enhancements
• XML for Analysis
• Analysis Services Scripting Language
• Analysis Management Objects (AMO)
• ADOMD.NET

Plus Data Mining Enhancements
74
What Is SQL Server 2005 Analysis Services?
Analysis Services
Spreadsheets
SQL Server
Datamart

• One version of the truth
• Data enrichment and advanced analytics
• Real-time and high performance
• Mission critical

BI Front Ends
DW
Teradata
Ad Hoc Reports
Rich Reports
Oracle DB2
LOB
Dashboards
75
Analysis ServicesHigh-level Architecture
Analysis Services
SQL Server
Datamart
DW
Teradata
Oracle DB2
LOB
Cache
76
SQL Server Analysis ServicesNew Paradigm for the
Analytics Platform

• Business Intelligence Enhancements
• Add data-aware smarts
• Autogenerated
• KPIs, MDX scripts, translations, currency
• Data Mining
• 10 Mining Algorithms
• Smart applications
• XML standards for Data Access Web services
  integration
• saving for customers integrating our solution
  with other systems

• Unified Dimensional Model
• Powerful business information modeling
• Cross platform data integration
• Integrated Relational OLAP views
• KPIs Perspectives
• Proactive caching
• Real-time data in OLAP Cubes
• Very fast and flexible analytics

77
Business IntelligenceGetting to What Matters

• Paralysis by Analysis
• The risk of providing raw data or data in large
  volumes
• Paradox it is important to have the detail to
  understand the causality of data

KPI
78
Business IntelligenceKey Performance Indicators

• Calculations that drive visual indicators
• Quick and easy way to manage by exception
• Set and Monitor Goals
• Monitor the trend, up or down

79
SQL Server 2005 Analysis Services Data Mining

• New Algorithms
• Two enhanced
• Eight new algorithms
• New Visualizations
• Enhanced tools
• custom visualizations
• Deep Integration
• OLAP, DTS, and Reporting Integration
• .NET programming model
• Completely extensible framework

80
Value of Data Mining
SQL Server 2005

• 8 new algorithms, 10 in total
• Graphical tools/wizards
• 12 embeddable viewers

• SQL Server 2005 makes it easier
• Tightly integrated with AS, DTS, Reporting
• Integration with Web/Office apps

81
Complete Set of Algorithms
Introduced in SQL Server 2000
Linear Regression
Text Mining
82
Putting Data Mining to Work
83
Mining for Meaning
84
Reporting ServicesWhy Reporting Matters

• Real-time insight into business trends
• Better decision making across entire organization
• Build a report with multiple data sources
• Enable interactive access to information
• Enable an end user to build (or modify) their own
  report
• Provide reports in a format that end users want
• Integrate reporting into applications easily

85
Reporting ServicesEnhanced Reporting Capabilities

• Scalable Server
• Rich, Enterprise Reporting Platform (static and
  interactive)
• Multiple data sources with multiple delivery
  options
• Scalable, manageable and embeddable Web Services
  architecture
• Scheduling, Snapshots, Caching, more
• Advanced Authoring Tools
• Visual Studio IDE
• XML specification (RDL)
• 3rd party extensibility
• End-user, Ad Hoc Reporting
• Strong Management Story
• SOAP Web Service APIs
• Report Manager portal
• Extensible security model
• Integration with AS, IS, management tools

86
Reporting ServicesSQL Server 2005 BI Development
Studio

• Integrated design time environment exposes
• Report Designer
• Multiple data sources
• Objects (tables, matrix, charts)
• SQL Generator
• MDX Query Generator
• New Reporting Controls
• Interactive Sort
• Floating Headers
• Date Picker
• Multi-value Select

87
Architecture
Browser
Office
Custom App
Builder
Web UI
Programmatic Interfaces
Security Services (NT, Passport, Custom)
Data Sources (SQL, OLE DB, XML/A, ODBC, Oracle,
Custom)
Report Server
Report Processing
Report Model
Security
Data Processing
Delivery
Rendering
Output Formats (HTML, Excel, TIFF, Custom)
Delivery Channels (E-mail, SharePoint, Custom)
SQL Server Catalog
88
Report Builder

• Extending the Reach of Reporting Services
• Ad Hoc Reporting forthe End-User
• 1-Click Install
• With Report Builder
• Report off a Business Model
• Modify a Report
• Build a New Report
• Report on Relational or OLAP data

89
Reporting Models

• Paradigm Build Once Query Many
• Empowers users to create their own reports
• End-user reports can be further edited extended
  in BI Development Studio by professional report
  developers
• Provide easy data navigation with drill-through
  relations
• Mask relational and OLAP schema complexity
• Present data in familiar business terms

90
Reporting Services
91
BI with Office 12

• Analyze data in Excel and Create Office
  documents based on business data

• Publish spreadsheets and documents to SharePoint

Best with SQL
Reuse spreadsheets, reports,and documents in
dashboards and business applications
Access and explore data in spreadsheets, reports,
and documents in the Portal
92
Mission Critical CapabilitiesRunning SQL Server
2005 Today

• 100 Microsoft IT applications live TODAY
• SAP R/3 Deployment, 1.7 TB
• Feedstore staging DW for all Microsoft data
• 2 TB of Data, 1800 Tables, 500 subscribing
  systems
• Sales Revenue Reporting and BI
• 9,000 users, 25,000 reports per week
• gt30 external customers in production TODAY!

93
Project REALBI in Practice
Reference Implementation, End-to-End, At Scale,
Lots of Users http//www.microsoft.com/SQL/BI/Proj
ectREAL
94
Building Highly Available Systems with SQL
Server 2005
95
AvailabilityWhat does it mean to you?
Can your customers get done, what they need to
get done, when they need to do it?

• Why not?
• Site is unavailable
• System is unavailable
• Database is unavailable
• Database is partially unavailable
• Table is unavailable
• Data is unavailable

24x7x365
96
Barriers to AvailabilityIsolated Failures

• Continuing to work with isolated failures
• Limiting the scope of failure
• Partial Database Availability
• Online Piecemeal Restore
• Supporting Technology
• Instant File Initialization
• How do they work?

97
What happens when

• Disks Fail
• In SQL Server 2000
• Database is marked suspect
• Users are unable to access the database
• In SQL Server 2005
• Filegroup is marked offline
• Users are able to access undamaged data

98
What happens when

• Recovery begins
• In SQL Server 2000
• Database is in a restoring state
• Users are unable to access the database
• File needs to be recreated and zero initialized
• File Restore can proceed offline
• In SQL Server 2005
• Filegroup is in a restoring state
• Users are able to access undamaged data
• File can be recreated with instant file
  Initialization
• Piecemeal Restore can proceed online

99
Functional PartitioningStrategies to separate
Objects/Data

• Related Object-groupings
• ? Separate tables strategically placed on
  different filegroups
• Time-based data placement/partitioning
• ? Structures designed for sliding window scenario
• List-based groupings/partitioning
• ? Range-based partitioning based on complete
  lists
• To fully leverage Partial Database Availability
  for partitioned objects use Partitioned Tables
• Partitioned Tables new feature in SQL Server
  2005 to further simplify the process of building
  large data warehouses

100
Benefits of Partitioning

• Speed in managing sliding window
• ? Partition manipulation outside of active table
• Piecemeal Backup
• ? Backup active components more frequently,
  inactive less frequently
• Partial Database Availability
• ? If a filegroup becomes unavailable the
  undamaged data remains available
• Online Piecemeal Restore
• ? During the restore, the undamaged data remains
  available

101
Partial Database AvailabilityImproving
Availability for Isolated Disaster

• Undamaged data remains available while damaged
  data is inaccessible
• File Status shown in sys.database_files catalog
  view
• Page Errors written to suspect_pages table in
  msdb
• Agent alerts
• Notification of the damaged file
• Can take the database offline, if desired
• Can automate the restore, for read-only data

102
Database Components

• Database consists of
• Filegroups consist of
• Files consist of
• Extents consist of
• Pages consist of data

TicketSalesDB
Primary
Readwritefilegroup
File1
File2
2004
File3
Readonlyfilegroups
0
4
8
12
16
20
24
28
File Header
2003
File4
1
5
9
13
17
21
25
29

File5
2002
2
6
10
14
22
18
30
26
2001
File6
3
7
11
15
19
23
27
31
extent0
extent1
extent2
extent3
Log
103
Improving Data AvailabilityPartial Database
Availability
104
Online Piecemeal RestoreImproving Availability
during Recovery

• Almost any component (page, file, filegroup) can
  be restored ONLINE
• If a page is damaged restore only that page
  from a file, filegroup or database backup
• If a file is damaged restore only that file
  from a file, filegroup or database backup
• If a filegroup is damaged restore only that
  filegroup from a filegroup or database backup
• Readonly filegroups can be restored without
  rolling forward log changes
• Users can access the database during the restore

105
Instant File InitializationImproving
Availability by Reducing Downtime

• SQL Server 2000
• All data and log files must be zero initialized
• Downtime during recovery negatively impacted by
  the file creation phase of restore
• SQL Server 2005
• Only log files must be zero initialized
• Downtime during recovery significantly reduced by
  skipping zero initialization during the file
  creation phase of restore
• Not only a benefit to Restore
• Database Creation
• All Restores File, Filegroup and Database
  Restores
• Database File Changes autogrow, manual resizing

106
Summary Isolated Failures
107
Barriers to AvailabilityConcurrency Requirements

• Database is available but the application/user
  cannot complete required operations
• What about operational impacts?
• Maintenance Operations which cause blocking
• ? New Online Index Rebuilds
• What about application impact?
• Poorly designed and/or long running transactions
• Varying data access patterns
• ? New Snapshot Isolation options

108
What happens when

• Indexes need to be rebuilt
• In SQL Server 2000
• Index rebuilds require an exclusive table-level
  lock, resulting in offline rebuilds
• Users are unable to access the table
• In SQL Server 2005
• Rebuilds of an index can be performed online if a
  few simple criteria are met
• Users are able to access the table

109
Online Index OperationsImproving Concurrency
during Index Maintenance

• SQL Server 2000
• Offline Index Rebuilds table data is unavailable
  during operation
• Rebuild options DBCC DBREINDEX and CREATE with
  DROP_EXISTING
• SQL Server 2005
• Includes all of the above offline operations,
  plus
• New ALTER INDEXREBUILD
• ONLINE allows concurrent user access (queries
  as well as modifications) to the index during
  rebuild
• OFFLINE works using locks (same as SQL Server
  2000)
• If online is not possible by default, consider
  design alternatives to fully leverage online
  index rebuilds

110
What happens when

• Readers and Writers desire the same data
• In SQL Server 2000
• Locking is used to guarantee the intended level
  of isolation
• Users must wait to access locked data
• Concurrency and performance compromised
• Correctness is compromised when lower isolation
  levels are used to avoid locking
• In SQL Server 2005
• Locking OR Versioning can be used to guarantee a
  variety of isolation levels
• With versioning, Readers wont block writers and
  writers wont block readers
• Performance improved if contention was primary
  bottleneck
• Correctness is not compromised due to use of
  lower isolation levels

111
Snapshot IsolationImproving Concurrency in Mixed
Workloads

• SQL Server 2000
• Isolation implemented solely through locking
• Mixed workloads may experience
• Concurrency problems due to blocking
• The Inconsistent Analysis problem
• SQL Server 2005
• Isolation implemented using locking and
  versioning
• Mixed workloads can improve read consistency and
  performance using
• Read committed with Statement-level snapshot to
  improve statement-level consistency
• Snapshot Isolation to improve transaction-level
  consistency

112
Improving ConcurrencyRead Committed
w/Statement-level Snapshot

• DB Option Enabled READ_COMMITTED_SNAPSHOT
• Uses locking for writes, versioning for reads
• Increases data availability while reducing
  deadlocks
• Non-blocking consistent reporting and ad hoc
  queries
• Readers wont block writers writers wont block
  readers
• Results in statement-level consistency through
  row versioning
• Impact to Applications
• No additional changes requires for read
  transactions
• Allows migration from environments that use
  versioning

113
Improving ConcurrencyTransaction-level Snapshot
Isolation

• DB Option Enabled ALLOW_SNAPSHOT_ISOLATION
• Uses locking by default
• Increases data availability while reducing
  deadlocks
• Non-blocking consistent reporting and ad hoc
  queries
• Readers wont block writers writers wont block
  readers
• Results in transaction-level consistency through
  row versioning when isolation level changedSET
  TRANSACTION ISOLATION LEVEL SNAPSHOT
• Impact to Applications
• No additional changes requires for read
  transactions
• Mandatory conflict detection for read-write
  transactions using versioning

114
Snapshot Isolation
115
Summary Concurrency Requirements
116
Barriers to AvailabilityCatastrophic Failures

• Database is completely unavailable
• Server is unavailable
• Site is unavailable
• Standby Technologies
• Failover Clustering
• Database Mirroring
• Replication
• Log Shipping
• How do they work?

117
Failover ClusteringServer-level Redundancy

• Established High Availability Technology
• Hot Standby Automatic Detection and Automatic
  Failover
• No work loss exposure and no direct impact to
  workload
• Protects against node failures
• Geographically DispersedFailover Clusters with
  approved hardware
• Recovery on failover improved by Fast Recovery

118
Failover ClusteringNew for SQL Server 2005

• Faster Failover through Fast Recovery
• Supports up to an 8-node Failover Cluster with
  Enterprise Edition
• Supports up to a 2-node Failover Cluster with
  Standard Edition
• Supports mounted volumes for better explicit disk
  usage helps in server consolidation
• Supports dynamic AWE for better memory
  utilization
• Unattended setup
• All SQL Server data services participate
• Database Engine, SQL Server Agent, Full-Text
  Search
• Analysis Services Now has multiple instances

119
Database MirroringDatabase-level Redundancy

• Upcoming High Availability Technology
• Released for testing and prototyping in SQL
  Server 2005 RTM
• Certified for Production Use in the first half of
  2006
• Supports three configurations
• High Availability
• High Protection
• High Performance

120
Database MirroringTechnology Overview

• Principal Database handles user activity
• Mirror Database receives changes via secure,
  dedicated TCP channel
• Server does NOT require a license if the server
  acts solely for redundancy
• Optional Witness Server
• Lightweight mechanism to help provide quorum
• Can run on any SQL Server Edition
• Supports three configurations
• High Availability
• High Protection
• High Performance

121
Database MirroringBasic Principal of Synchronous
Mirroring
Acknowledge
Commit
Acknowledge
Constantly Redoing on Mirror
Transmit to Mirror
Write to Local Log
Write to Remote Log
Committed in Log
Log
DB
DB
Log
122
Database Mirroring
123
Database Scale OutPeer to Peer Replication

• Identical databases continuously synchronize in
  near real time
• Scale query workloads beyond whats possible with
  a single database

Example Distributed Trading System
Chicago
London
Tokyo
124
Availability through Scalability Peer to Peer
Replication

• Enables load-balancing and improved availability
  through scalability
• Database failures shouldnt bring down the
  application system
• Database upgrades should be done without outages
• Individual databases can be taken online/offline
  and maintained without application downtime
• Warm Standby
• Small possibility of some data loss on failure

125
Peer-to-Peer Replication

• Based on Established Transaction Replication
  Technology
• Based on Bi-directional Transactional Replication
• All participants are peers
• Schema is identical on all sites
• Publish the updates made on their data
• Subscribe to others to pick up their changes
• No hierarchy as in normal transactional
  replication
• A given set of data can be updated at only one
  site at a time
• Data ownership is purely logical does not
  prevent conflicts
• SQL Server prevents a change from round-tripping

126
Peer to Peer Topology
London
Chicago
Peer to PeerTransactional Replication
Tokyo
127
Peer-to-Peer Replication
128
Summary Standby Technologies
129
(No Transcript)
130
(No Transcript)
131
(No Transcript)
132
(No Transcript)
133
(No Transcript)
134
(No Transcript)
135
(No Transcript)
136
(No Transcript)
137
(No Transcript)
138
(No Transcript)
139
(No Transcript)
140
(No Transcript)
141
(No Transcript)
142
(No Transcript)