SQL Server 2005 Yukon The scalable secure enterpriseclass

SQL Server 2005 “Yukon” The scalable, secure, enterprise-class data management platform Dubi Lebel Database technologies Solution & Technology Group Microsoft Israel

Database Historical View

SQL Server Product Roadmap SQL Server 6. 5 • Data warehousing • Internet support • Differentiation from Sybase SQL Server Foundations of each release: SQL Server 7. 0 • Re-architecture of relational server • First to include OLAP in database • Auto tuning • Ease-of-use • Lowest TCO • Ease-of-Use SQL Server 2000 • Reliability and scalability advancements • Deep XML support • Data warehousing • SQL Server CE • 64 bit support SQL Server “Yukon” • Enterprise-class scalability • Programmability advancements • End-to-end business intelligence • Manageability • Support for multiple types of data • Performance and Scalability • Integrated Business Intelligence

SQL Server 2000 A Major Release n n n XML support n Distributed Partitioned Views n SELECT … FOR XMLn n Log Shipping n Open. XML n Parallel CREATE INDEX n n XML Views n Parallel scan n n XML Updategrams n Parallel DBCC n n XML View Mapper n Failover clustering n XML Bulk Load n Failover cluster managementn URL and HTTP db access n 32 CPU SMP system supportn HTTP access to cubes n 64 GB RAM support n Multi-instance support n VI SAN support n Integrated Data Mining n Indexed views n Full-Text Search in formatted n ROLAP dimension storage docs n Distributed Partitioned Cubes English Query for the Web Online index reorganization C 2 security rating (NSA)n n n Differential backup Installation disk imaging n User-defined functions Active Directory integration n n Server-less snapshot backup Self-management and tuning n SQL Query Analyzer debugger n New data types Column-level collations Virtual Cube Editor Linked cubes MDX Builder Dimensions Security in Analysis Services OLAP Actions Custom rollups Cascading referential integrity and actions INSTEAD OF triggers Indexes on computed columns Queued replication

SQL 2000 Benefits to the IT Pro 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. More scalable out of the box Highly secure out of the box More high availability options Easier to set up in enterprise environments Simplified management Supports rapid database development and deployment XML-enabled for all your data Integrated toolset for data warehousing and Business Intelligence Simplified licensing and pricing Consistent database platform from the largest server to the smallest Pocket. PC devices

SQL Server 2000 Product Roadmap Since RTM Till today SQL Server “Yukon” SQLXML 1. 0 Ø XML Updategrams Ø XML Bulk Load SQLXML 2. 0 Ø Managed classes ØXML schema support Business Intelligence Ø End to End data warehousing SQL Server XML View Mapper 1. 0 SQLXML 3. 0 Programmability Ø Deep XML support Ø Common Language Runtime integration ØWeb SQL Server 2000 Service Pack 1 Services Integration SQL Server CE 2. 0 SQL Server 2000 64 -bit SQL Server Notification Services SQL Server Reporting Services SQL Server 2000 Service Packs (2, 3) XML for Analysis Reliability Ø Self healing Availability Ø Online operations Scalability Ø Self-tuning

SQL Server 2005 “Yukon” The scalable, secure, enterprise-class data management platform

Business Agility Business Value q Increase Business Value n Connected n Productive n Connect with Customers (Revenue) Integrate with Partners (Profitability) Empower Employees (Productivity) q Economics Dependable Total Costs Total Cost of Ownership n Leverage existing assets n Hardware is getting LESS expensive n Downtime is MORE expensive Microsoftware enables business agility in today’s competitive climate

SQL Server 2005 “Yukon” . NET Framework Database Maintenance Replication § Common Language Runtime Integration § Backup and Restore Enhancements § Auto-tuning Replication Agents § User-defined Aggregates § Checksum Integrity Checks § Oracle Publication § User-defined Data Types § Dedicated Administrator Connection § Improved Blob Change Tracking § User-defined Functions § Dynamic AWE OLAP and Data Mining § SQL Server. NET Data Provider § Fast Recovery § Analysis Management Objects § Extended Triggers § Highly-available Upgrade § Windows Integrated Backup and Restore Data Types § Online Index Operations § Web Service/XML for Analysis § File. Stream Storage Attribute § Online Restore § DTS and DM Integration § Managed SQL Types § Parallel DBCC § Eight new DM algorithms § New XML Datatype § Parallel Index Operations § Auto Packaging and Deployment SQL Server Engine Management Tools Data Transformation Services § New Message Service Broker § MDX Query Editor § New Architecture (DTR + DTP) § HTTP Support (Native HTTP) § MDX Intellisense § Complex Control Flows § Database Tuning Advisor § T-SQL Intellisense § Control Flow Debugging § Enhanced Read ahead & scan § Version Control Support § For Each Enumerations § Extended Indexes § XML/A § Property Mappings § Multiple Active Result Sets § SQLCMD Command Line Tool § Full Data Flow Designer § Persisted Computed Columns Performance Tuning § Full DTS Control Flow Designer § Queuing Support § Profiler Enhancements § Graphical Presentation of Pkg Execution § Snapshot Isolation Level § Profiling Analysis Services § Immediate Mode and Project Mode § Scale Up Partitioning § Exportable Showplan § Package (Advanced) Deployment Tools § VIA support § Exportable Deadlock Traces § Custom Tasks and Transformations § NUMA support Full-text Search Reporting Services Database Failure and Redundancy § Indexing of XML Datatype § Multiple Output Formats § Fail-over Clustering (up to 8 node) MDAC § Parameters (Static, Dynamic, Hierarchical) § Enhanced Multi-instance Support § Side by Side installation § Bulk Delivery of Personalized Content § Database Mirroring § Microsoft Installer base setup § Support Multiple Data Sources § Database Viewpoints § Support for Active Directory Deployment § STS (Web Parts, Doc Libraries) XML SQL Client. NET Data Provider § Visual Design Tool § XQUERY Support (Server & Mid Tier) § Server Cursor Support § Charting, Sorting, Filtering, Drill-Through § XML Data Manipulation Language § Asynch § Scheduling, Caching § FOR XML Enhancements Security § Complete Scripting Engine § XML Schema (XSD) Support § All Permissions Grantable § Scale Out architecture § MSXML 6. 0 (Native) § Fine Grain Administration Rights § Open XML Report Definition §. Net XML Framework § Separation of Users and Schema Notification Services Microsoft Confidential

Programmability, Manageability, BI Programmability Data Management and Storage Business Intelligence q Enable developers to take advantage of existing skills q Extend and connect data across any platform q Provide end-to-end development tools q Storage of all data types, formats q Scalability, availability and security advancements q Continued leadership and focus on ease-of-use q Comprehensive business intelligence solution q Enable knowledge throughout the business q Extend the value of existing applications

Mission Critical Platform Key Customer Priorities Secure Productive Connected Economic

Security Framework ● Secure by Design • • • ● Architected & built for security today and tomorrow Secure and robust code Threat analysis and testing Secure by Default • • • ● Secure Reducing the attack surface Default configuration is a secure system Minimize attack surface Secure by Deployment • • Tools and training for secure environments Principle of least privilege Automate / Assist software maintenance Good tools for security assessment / admin

Secure Deployment: Security Tools: MBSA ● Microsoft Baseline Security Analyzer • Verify current configuration security • Local and remote scans § § § Windows, IIS, Exchange, SQL Server More in future • Graphical and scriptable

“Connected” SQL Server Connected Web Services Distributed Query SQL Server Driver for JDBC Reporting Services DTS Analysis Services DBLib for Unix Replication XML SQL Server Oracle IBM DB 2 Sybase Informix Access Excel Text Files d. Base Paradox VSAM Active Directory Web. DAV etc. . .

Developers Productive “Whidbey” Design Manage Build Deploy

Economic Scalability q Optimized for Windows Server 2003 and Itanium q Great performance n n Manageability n q Large memory addressability (up to 32 TB) Nearly unlimited virtual memory (up to 8 TB) I/O savings due to larger memory buffer pools T-SQL code-compatibility with SQL Server 2000 Cost Savings q 8 node clustering support q Same on-disk format as 32 -bit for easy migration q One setup for database & OLAP based on The highly scalable database platform for memory Windows Installer technology intensive, performance-critical business applications q Compelling alternative to expensive Unix

Reporting Services Analysis Services OLAP & Data Mining Data Transformation Services SQL Server Relational Engine Ma n a g e m e n t T o o l s D e v e l o p m e n t T o o l s SQL 2005 Business Intelligence

The Unified Dimensional Model – The Best of Relational and OLAP Relational Reporting OLAP Cubes * Multiple fact tables * Full richness the dimensions’ attributes * Transaction level access * Star, snowflake, 3 NF… * Complex relationships: Multigrains, many-to-many, role playing, indirect… * Recursive self joins * Slowly changing dimensions * Multidimensional navigation * Hierarchical presentation * Friendly entity names * Powerful MDX calculations * Central KPI framework * “Actions” * Language translations * Multiple perspectives * Partitions * Aggregations * Distributed sources m i D o i s en T U he d e i f i n M l na l e od

SQL Server 2005 Top Features ● ● ● For Database Administration For Development For Business Intelligence

For Database Administration 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. Database Mirroring Online operations Online Index Operations New Management Toolset - SQL Server Workbench, Agent and Command Line Utilities Data Partitioning Media Reliability Features Dedicated Administrator Connection Viewpoints Snapshot Isolation “No repro required” go to 50

. 1 Database Mirroring ● Database Failover – an Instant Standby • • ● Hardware • • ● ● Works with standard computers and storage No shared storage components Impact to transaction throughput • ● Very Fast … less than 3 seconds Automatic or manual failover Zero data loss Automatic re-sync after failover Zero to minimal Automatic, transparent client redirect Use View. Point to make mirror database a readable reporting database

Database Mirroring ● Fault Tolerant Virtual Database Witness Clients Principal Mirror

Database Mirroring How It Works ● Transaction Shipping Witness Mirror Principal Application 1 5 2 SQL Server 2 4 3

Transparent Client Redirect ● ● ● No changes to application code Client automatically redirected if session is dropped MDAC is aware of Principal and Mirror servers • Upon initial connect to Principal, MDAC caches Mirror name • When client attempts to reconnect § § If Principal is available, connects If not, MDAC automatically redirects connection to Mirror

. 2 Online Operations ● More Configuration is Dynamic • ● ● No longer requires a server restart CPU Affinity Address Windowing Extensions (AWE) • Changes to physical size don’t require downtime § § § • ● ● ● Dynamically configurable (Min / Max) Maximum limited by physical memory Dynamically adjusts to “hot-add” memory Requires Windows Server 2003 Index Creation and Maintenance Online Restore Availability During Rollback Recovery

3. Online Index Operations ● Online index maintenance § § § ● ● ● Create Rebuild Reorganize (including BLOBs) Drop Add or drop constraint (primary key, unique) Fully parallel Online / offline are both supported Updates run somewhat slower during an online index operation

. 4 New Management Tools ● New Integrated Tools Suite • Support for all SQL Server products • Support for SQL Server 7. 0, 2000, and SQL Server 2005 ● New Operations Capabilities • SQL Server Agent § Core Performance and Scalability enhancements

Performance Tools ● Profiler • Profiling of Analysis Server • Showplan Events, Deadlock Events ● Replay • Improved multi-process replay

SQL Best Practices Analyzer ● Scans SQL Server installations for “Best Practices” • • ● ● T-SQL (procs, functions, views, triggers) Configuration Deprecation SQL Server 2005 Readiness Consolidation of “Best Practice” lists Avoid common mistakes “Read Only” tool Works with SQL Server 2000

SQL Server Best Practices Analyzer

. 5 Data Partitioning ● Partitioning breaks a single object into multiple manageable pieces • • ● Partitions can be created or dropped with virtually no loss of availability to the table • • • ● Transparent to the application Allows easy management of very large tables and indexes The row is the unit of partitioning All partitions run on a single SQL Server database Table fully available while loading, indexing a new partition Create new and drop old partition quickly Fastest possible load rates If all indexes are “aligned”, moving partitions in or out of a table is possible • “Sliding window” scenarios

How Partitioning Works Two Steps ● ● Each row of an index/table is assigned to a partition (numbered 1, 2, 3, . . . ) using a “partition function” Each partition for a partition function is mapped to a physical storage location (Filegroup) through a “partition scheme”

The Paradigm Shift… Object 1 PARTITION FUNCTION Logical Partition 1 Partition 2 Logical Partition N PARTITION SCHEME Physical Partition 1 Partition 2 Physical Partition N

Managing Partitions Adding and removing partitions ● Two operations • SPLIT – adding a partition • MERGE – removing a partition ● No data in or out of the table; only rows of tables and indexes are moved from one partition to another

Managing Partitions Adding and removing partitions ● ● ● SPLIT and MERGE partition is achieved by modifying the Partition Function All tables and indexes using the same Partition Function are “repartitioned” in a single atomic operation Syntax • ALTER PARTITION FUNCTION pf() SPLIT [RANGE (new_boundary_value)] • ALTER PARTITION FUNCTION pf() MERGE [RANGE (old_boundary_value)]

SWITCH Partition Idea ● ● Exchange data between tables by SWITCHing the pointers to physical data locations Designed to bring a partition of new data in or take partition of old data out of a partitioned table

6. Media Reliability Features ● Enhanced Verification • ● Backup Media Mirroring • • ● Redundant backup media helps guarantee successful restore Extra copies for archival or disaster recovery Database Page Checksums • ● RESTORE VERIFYONLY now checks everything it can short of writing the data Detect disk I/O errors not reported by the hardware or operating system Backup Checksums • • Detect errors introduced by backup hardware but not reported by hardware or operating system Can continue past errors – repair later

7. Dedicated Administration Connection ● ● Provides DBA access to server regardless of load No server restart to kill runaway session

. 8 Database “View. Point” ● Read-only, consistent copy of a database • • Good for reporting Can rollback the actual database to View. Point § ● ● Unchanging as database is modified Inexpensive • • Very fast to create Cheap in terms of space § ● To correct user application error (or DBA error) “Copy-on-Write” technology Can be created for any database • • Production databases Instant standby databases with Database Mirroring

. 9 Snapshot Isolation ● ● New transaction isolation level Increased Data Availability for read applications • Allows Non-Blocking consistent reads in an OLTP environment • Writers don’t block readers • Readers don’t block writers ● Permits writes, which can cause conflicts • BUT… includes mandatory conflict detection

10. “No repro required” ● “No repro required”

SQL Server 2005 Top Features ● ● ● For Database Administration For Development For Business Intelligence

For Development 1. 2. 3. 4. 5. 6. 7. 8. 9. . NET Framework Integration XML Technologies XQuery Support API Enhancements: ADO. NET V 2 SQL Service Broker T-SQL Enhancements Full Text Search Enhancements Security Enhancements HTTP/SOAP Features go to 83

1. . NET Framework Integration ● ● Programming environment for Functions, Stored Procedures, Triggers User Defined Types, Aggregates In-Proc Data Access (ADO. NET V 2) Symmetric data access • Mid-tier/data tier deployment decision ● Security • Integration of SQL and CLR security • Three levels of code access security § ● Safe, External-Access (verifiable), Unsafe Tight integration with Visual Studio • Authoring, debugging, deployment, & profiling

The Developer Experience VB, C#, C++ VS. NET Build Project Runtime hosted by SQL (inproc) SQL Queries: select sum(tax(sal, state)) from Emp where county = ‘King’ Assembly: “Tax. Lib. dll” SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … SQL Server

Authoring/Debugging/Deploying ● ● ● New Visual Studio project type in “Whidbey” for “Yukon” managed code Server debug integration Set breakpoints anywhere Full debugger visibility Single step support: • • Between languages: T-SQL, C#, VB, & C++ Between deployment tiers: § E. g. Mid-tier, through SQL Server stored proc call & back to mid-tier

Developer Productivity

. 2 XML Technologies ● XML data type & XML index support • Unified XML & relational store § § Both SQL & XQuery supported by same industrial strength infrastructure Leverages existing SQL engine & optimizer • XQuery with data modification extensions ● XML views • Declarative Mapping Files ● ● XML schema enforcement Client access using ADO. NET & SOAP

Native XML Store XML Data Type ● XML data type • Native SQL type • Use for column, variable or parameter CREATE TABLE docs (id INT PRIMARY KEY, x. Doc XML NOT NULL) ● ● ● Store un-typed or typed XML instances Well-formed and validation checks Methods on XML data type • Query(), value(), exist() & modify()

Native XML Store XML Schema Support ● XML Schema • Rich mechanism for schema definitions • XML-based syntax (W 3 C standard) • Not required for Query or Storage ● Benefits of typed data • • ● Guarantees shape of data Allows storage and query optimizations XML type system • Store XML schemas in system meta-data

Native XML Store XML Index ● Create XML index on XML column CREATE XML INDEX idx_1 ON docs (x. Doc) ● ● Creates indexes on tags, values & paths Speeds up queries • Entire query is optimized § Same award winning cost based optimizer • Indexes are used as available

3. XQuery Support XML Query - XQuery ● Query XML document and data • Standards-based — W 3 C working draft http: //www. w 3. org/tr ● Methods on XML data type • • Query() Value() Exist() Modify() INT XML … Modify Update …

XQery Demo ● ● ● ● ● CREATE TABLE docs (id INT PRIMARY KEY, x. Doc XML) GO -- Insert a literal -- --------INSERT INTO docs VALUES (1, '<doc id="123"> <section num="1"> <heading>Introduction</heading> </section> <section num="3"> <heading>Sort</heading> </section> <section num="4"> <heading>Search</heading> </section> </doc>') GO

XQery Demo con ● ● ● ● INSERT INTO docs VALUES (1, '<doc id="123"> <section num="1"> <heading>Introduction</heading> </section> <section num="3"> <heading>Sort</heading> </section> <section num="4"> <heading>Search</heading> </section> </doc>') GO

XQery Demo insert ● ● ● ● ● INSERT INTO docs VALUES (3, NULL) GO -- Load from file UPDATE docs SET x. Doc = (SELECT * FROM OPENROWSET (BULK 'C: PWDdevTech. Ed 04 Eilatdemo 1. xml', SINGLE_CLOB) AS x. Doc) WHERE id=3 GO

XQery Demo select ● ● ● ● SELECT id, x. Doc. query( 'for $s in /doc[@id = 123]//section[@num >= 3] return <topic>{data($s/heading)}</topic>') FROM docs GO -- Eliminate NULL INSERT result SELECT id, x. Doc. query( 'for $s in /doc[@id = 123]//section[@num >= 3] return <topic>{data($s/heading)}</topic>') FROM docs WHERE x. Doc. exist ('/doc[@id = 123]//section[@num >= 3]') =1 GO

XQery Demo update ● ● ● UPDATE docs SET x. Doc. modify( 'update (/doc[@id = 123]/section[@num = 4]/heading/text())[1] to "Algorithms"') WHERE id = 1 GO UPDATE docs SET x. Doc. modify( 'delete /doc[@id = 123]/section[@num = 3]') WHERE id = 1 GO

4. API Enhancements: ADO. NET V 2 ● ● ● ● ● Multiple active result sets (MARS) Object Persistence FX (Object. Spaces) Query notifications – for cache invalidation Functional parity with ADO (ADOX, etc) Server cursors (Sql. Result. Set) Asynchronous client access Bulk update, paging, and batching XML Data type support User Defined Type (UDT) support

5. Service Broker Platform for building Asynchronous Queued applications ● Why Async Queuing? Why do messaging in the database Or SQL Service Broker positions versus MSMQ, Biz. Talk and Indigo? ●

Why Async Queuing? ● Almost all large – highly scalable applications use async queuing • • • ● Web Servers OS I/O systems Databases Service Broker brings async queuing to database applications

Queuing at the Grocery Store multi-queue

Queuing at the Airport single queue readers

What Service Broker Is ● ● ● Platform for building Asynchronous Queued applications Built into the engine, Queues as first class database objects Queue manipulation built into Microsoft® Transact-SQL (T-SQL) Combined with. Net integration makes the database a viable application platform. Reliable distributed queuing Transactional message processing

What’s New? ● ● ● Message Integrity Message Ordering Multi-Reader Queues Distribution Transactional Messaging Queue Reader Management

Business Scenarios ● ● ● Order Entry Parallel/Asynchronous Stored Procedures Event Notifications

6. T-SQL Enhancements - Infrastructural ● Performance • Statement level recompile • Statistics on views ● Manageability • Metadata changes • DDL triggers • Event notifications ● Web Development • Query Notifications ● Developer productivity • T-SQL debugging enhancements

T-SQL Enhancements – Language ● New data types • ● More expressive power • • ● ● Date/time types, varchar(max) Ranking functions Top enhancements Recursive query and CTE PIVOT/APPLY Transact abort handling (Try…Catch) Queuing primitives (WAIT FOR)

. 7 Fulltext Search ● ● ● Catalogs are included in Backup and Restore Attach and Detach includes catalogs Multi-Instance • One instance of Search per SQL Server

. 8 Security Enhancements ● Catalog and Metadata security • More detail later ● ● User / Schema separation Granular Permission control • Can grant role-type permissions § Example: Profiler access to non-SA • Fixed roles are still available ● Password Policy enforcement • Password strength, expiration ● Row-level security

. 9 HTTP/SOAP Features ● SQL & stored proc calls via HTTP/SOAP • Easy, standards based connectivity from Unix platforms to SQL Server ● Provides native HTTP listening • HTTP endpoint specifying URL, port, reqs • Publish WSDL for endpoints ● Standard-based • SOAP 1. 1 and 1. 2, WSDL 1. 1, inlined XSD ● ● Windows and SQL authentication (SSL only) Stored Proc can return result as Data. Set • Compatible with mid-tier prog. model

SQL Server 2005 Top Features ● ● ● For Database Administration For Development For Business Intelligence

For Business Intelligence 1. 2. 3. 4. 5. 6. 7. 8. 9. Tools One Click Cube UDM XML/A Based Protocol DTS – Data Transformation Services Availability data mining AMO Stored Procedures go to 118

1. The Tools - Evolution SQL 2000 SQL Servwer 2005 EM 2000 SQL Management Workbench QA 2000 AM 2000 Target Audience DBA Developer w Ne BI Development Workbench

Easy Development – The BI Workbench ● ● A complete, integrated tool for the development of BI applications One tool, multiple technologies: • ● Enterprise software development environment • • ● Integrated into Visual Studio. NET Team development, source control, versioning, developer isolation, resource independent coding Development cycle lifetime support • ● Relational, OLAP, DM, DTS, reporting, code, Web pages… Develop, test, deploy, modify, test… Breakthrough ease of use

Productivity Enhancements ● Creating the Sales and Warehouse cubes in Foodmart User Actions SQL 2000 SQL 2005 115 = 112 dim. wizards, 3 cube wizards 1 cube wizard # of objects created 115 7 # of mouse clicks 1321 7 # of wizards launched

. 2 One Click Cube ● ● New Cube Wizard for creating cube in data warehouse project An option to enable One-Click-Cube detection and Suggestions

The Enterprise BI Data Modeling Needs l l l Complex schemas Rich set of attributes Detail level reporting Easy navigation End user oriented Rich metadata Real time data Lightning speed Complex calculations Sophisticated security Simple management Single store

2. UDM Relational Versus OLAP Reports Feature Relational OLAP Flexible schema ü û Real time data access ü û Single data store ü û Simple management ü û Detail reporting ü û High performance û ü End-user oriented û ü Ease of navigation and exploration û ü Rich analytics û ü Rich semantics û ü

Enterprise BI – Today Data Source Data Model Tool MOLAP Browser (1) MOLAP Browser (2) Datamart Reporting Tool (3) Datamart DW Reporting Tool (2) Reporting Tool (1)

Enterprise BI – A Messy Reality Data Source OLAP versus Reporting Data Model Tool MOLAP Browser (1) MOLAP Browser (2) Datamart Reporting Tool (3) Datamart Reporting Tool (2) DW Duplicate Data Reporting Tool (1) Duplicate Models

The Unified Dimensional Model The best of Relational and OLAP Relational Reporting Ø Ø Ø Ø * Multiple fact tables * Full richness the dimensions’ attributes * Transaction level access * Star, snowflake, 3 NF… * Complex relationships: Multi-grains, many-to-many, role playing, indirect… * Recursive self joins * Slowly changing dimensions OLAP Cubes Ø Ø Ø * Multidimensional navigation * Hierarchical presentation * Friendly entity names * Powerful MDX calculations * Central KPI framework * “Actions” * Language translations * Multiple perspectives * Partitions * Aggregations * Distributed sources

The Unified Dimensional Model The best of Relational and OLAP Relational Reporting Ø Ø Ø Ø OLAP Cubes l e d o M l a n o i s n e m i D d e i f i n U e Th * Multiple fact tables * Full richness the dimensions’ attributes * Transaction level access * Star, snowflake, 3 NF… * Complex relationships: Multi-grains, many-tomany, role playing, indirect… * Recursive self joins * Slowly changing dimensions Ø Ø Ø * Multidimensional navigation * Hierarchical presentation * Friendly entity names * Powerful MDX calculations * Central KPI framework * “Actions” * Language translations * Multiple perspectives * Partitions * Aggregations * Distributed sources

Enterprise BI – With A UDM Data Source Data Model MOLAP Browser (1) MOLAP Datamart DW Tool OLAP Browser (2) UDM Reporting Tool (2) Reporting Tool (1) BI Applications

Enterprise BI – With A UDM Data Source Data Model MOLAP Browser (1) MOLAP Datamart DW Tool OLAP Browser (2) UDM Reporting Tool (2) Reporting Tool (1) BI Applications A single dimensional model for all OLAP analysis and Relational reporting needs

Analysis Services Streamlined BI Infrastructure Data Source Tool OLAP Browser (1) DW UDM Cache l l l XML/A or ODBO Analysis Services OLAP Browser (2) Reporting Tool (1) BI Applications Unified logical model for both relational and OLAP with superb performance and scalability One data store to manage ensure data consistency and low TCO Rich user experience with many, many of Microsoft and 3 rd party tools

4. XML/A Based Protocol – Native Web Services Functionality Internet / Intranet Analysis Server Any App, Any Device, Language, Platform XML for analysis Metadata discovery MDX query execution l l XML/A is native protocol Every UDM is a web service Zero client footprint HTTP & TCP/IP support XML/A l l l Standard based (HTTP, SOAP, WSDL, UDDI) Supported by all leading BI vendors WS evolution of OLE DB for OLAP

Light Footprint Win 32 Interface OLE DB for OLAP C++ App ADOMD VB 6 App Internet / Intranet Analysis Server Any App, Any Device, Language, Platform XML for analysis Metadata discovery MDX query execution l l l Preserve backward compatibility with OLE DB for OLAP and ADO MD Light memory and CPU footprint and no client caching Encryption, Compression, SSPI, TCP/IP enablement Win 32

Cross Platform Object Models Analysis Server XML for analysis l l l OLE DB for OLAP C++ App ADOMD VB 6 App ADOMD. Net App . Net ADOMD. J Java App J 2 EE Any App, Any Device, Language, Platform “Pure Managed Code”. Net Object model “Pure Java” object model for easy and optimized access from any J 2 EE platform Both object models compatible with ADO MD (the most popular BI object model) Win 32

Link Udms Across The Internet Analysis Server XML for analysis OLE DB for OLAP C++ App ADOMD VB 6 App ADOMD. Net App . Net ADOMD. J Java App J 2 EE Win 32 Any App, Any Device, Language, Platform Analysis Server l l UDMs can link to other UDMs Link within the same database, across databases, across servers and across the internet

5. DTS – Data Transformation Services What Is New In DTS ● Short version EVERYTHING !

SQL Server 2005 Vision For DTS ● Enterprise Ø Ø ● ETL Ø Ø ● Comprehensive and manageable Redefine performance and scale Readily handle complex scenarios Extensive BI features Platform Ø Ø Highly productive IDE Extensible, embeddable architecture The enterprise ETL platform for Windows

DTS For Dependable ETL ● Error flows • • ● Event handling • ● Readily design packages aware of external change Restart • ● Execute complex sequences that respond to package events WMI and MSMQ • ● Stream problem data without failures Fix and reprocess inline or save to disk for later Check point restart on failures Digital signing • Ensure integrity with certificates

DTS For Manageable ETL ● Configuration • • ● Deployment • • • ● Set properties at runtime from XML, registry, or command line Multiple and shared configurations Collect dependencies Build an MSI-based installation Distribute by CD or network Logging • • Log packages, tasks and sequences Log to multiple providers including § § ● SQL Profiler, NT Event Log, XML Or to custom providers Performance monitor • Rich selection of performance counters

DTS For Performance ● Transform engine • Pipeline architecture • Transforms, sources, destinations § As many as you’d like! • single pass transformations

. 6 Availability ● Failover and Active/Active Clustering • • ● Multi-Instances • • ● On par with SQL Server Very easy deployment – no registry entries needed Enhanced backup and restore • • ● Out of the box experience Support for n + 1 node cluster environment No 2 GB limit any more Parameterized: data folders, remote partitions, compression Cancel command • allows terminating a session that is draining server resources (ex. runaway query)

Availability ● Server Synching • Designed for dual machines configurations – number cruncher machine and end-user facing machine • Allow § § Processing the calculations isolated from user interactions Isolated verification of the results • Incremental and transactional synching of the query machine with the new results

7. Data Mining Key Messages ● ● Embedded data mining Integrated solution ● Complete set of analytics ● Alliance with ISVs l Embed DM to LOB applications l Complete SQL language based API l Native XML/A support

7. Data Mining Key Messages ● Embedded data mining ● Integrated solution ● Complete set of analytics ● Alliance with ISVs l Tight integration with Relational, OLAP, DTS, reporting technologies l SQL Server: The Platform for BI

7. Data Mining Key Messages ● Embedded data mining ● Integrated solution ● Complete set of analytics ● Alliance with ISVs l Most popular data mining algorithms l Enhanced viewer controls

7. Data Mining Key Messages ● Embedded data mining ● Integrated solution ● Complete set of analytics ● Alliance with ISVs l Focus on broadening the market rather than fight for share l Agreed on finalizing DM industry standard based on OLE DB for DM and XML/A

Complete Set Of Algorithms Decision Trees Sequence Clustering Time Series Association Naïve Bayes Neural Net

API: DMX CREATE MINING MODEL Credit. Risk INSERT INTO Credit. Risk (Cust. ID LONG KEY, Gender TEXT DISCRETE, (Cust. Id, Gender, Income, Profession, Risk) Income LONG CONTINUOUS, Profession TEXT DISCRETE, Risk TEXT DISCRETE PREDICT) Select Customer. ID, Gender, Income, Profession, Risk From Customers USING Microsoft_Decision_Trees Select New. Customers. Customer. ID, Credit. Risk, Predict. Probability(Credit. Risk) FROM Credit. Risk PREDICTION JOIN New. Customers ON Credit. Risk. Gender=New. Customer. Gender AND Credit. Risk. Income=New. Customer. Income AND Credit. Risk. Profession=New. Customer. Profession

Data Mining Flow ● ● Tight integration with DTS data flow and task flow Data mining transforms • • ● Model training Prediction Lift Nesting Data mining tasks • Prediction • Processing

. 8 AMO ● ● ● Analysis Management Objects Server administration OM Conceptually, it is the replacement for DSO Complete object model, on top of XMLA for designing cubes and programming the analysis services server Our VS administrative console is built using AMO

. 9 Stored Procedures ● Allow server side programmability • CLR has been integrated with AS server • Extend MDX capabilities § § Analytical computation Replace UDFs on client • May employ server version of ADOMD. Net

• • . NET framework hosting Native XML technology Integrated web services Distributed application framework • • Comprehensive ETL platform Real time analytics Accessible, easy data mining Rich, integrated reporting • Flexible, interoperable, scalable Improved predictability Self optimization and tuning Fast recovery and restore • • • 3 years in development Multiple security reviews 1, 000+ new and improved features Large private beta for early quality • • The comprehensive database platform for rapidly building, deploying and managing scalable, integrated solutions

Replication Service Broker Reporting Services SQL Server 2005 SQL Server Agent Relational Database Engine Analysis Services . NET CLR Notification Services Data Transformation Services

SQL Roadmap • • “Longhorn” Wave SQL 2005 • • “Yukon” • Wave • SQL Server 2000 Wave Web Services Everywhere Longhorn Storage Advancements Core XML Support: store, query Integration with. NET End-to-end business intelligence Manageability Web Services Toolkit for • Accelerator for Business Intelligence SQL Server 2000 • SQL Server SP 3 • SQL Server CE V 2 • SQL Server 64 -bit • Notification Services •

© 2003 -2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

© 2002 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

SQL Server Notification Services

Getting Information Today You Browse Request Information Sources Web Sites Newsgroups LOB Applications

Existing Systems (Problems) S P A M You Information Push Information Sources Web Sites Newsgroups LOB Applications

Notification Applications What are they? A new class of web application that delivers personalized and timely information to any device. Any device Timely Personal

The Notification Services Platform ● ● ● Notification Services is a platform for developing and deploying notification applications Notification Services applications are rich and highly scalable Notification Services offers • A simple, declarative programming model § • An efficient and scalable execution engine § ● ● Based on XML and T-SQL Based on SQL Server 2000 and. NET Framework You write Notification Application - Notification Services hosts and executes it Ships as a web download for SQL 2000

Notification Applications How Do Notification Applications Work? Subscriptions (millions) Events Activity (thousands) Notifications Match (millions)

Customer Adoption

Timely Delivery of Information Activity l l l Stock feeds New services Custom applications LOB applications Etc. Generation l SQL Server Notification Services Routing l l l . NET Alerts Greenwich Services SMS Aggregators Exchange (SMTP) MSMQ Web Service End Points l l l l Outlook Windows Messenger for Mac Pocket PC Smart Phone SMS capable phone Application Information flow A solution may use some or all of these parts Back to 7

SQL Server Reporting Services Back to 10

Reporting Lifecycle SQL Server Reporting Services supports the full reporting lifecycle, including Authoring, Management and Delivery. Authoring Management Delivery

Delivery Report Delivery ● Intermediate format for rendering reports to diverse output formats • • • ● Personalized and administrator defined subscription and delivery • • • ● Web Formats (HTML 4 and 3. 2) Print Formats (TIFF, PDF) Data Formats (Excel, XML, CSV) Ability to select output formats per subscription Can deliver links as well as rendered reports Data-driven subscriptions for personal delivery Extensible delivery provider architecture • E-mail, Share. Point

Web-based Rendering

Print Formatted Output

Personalized E-Mail Delivery

Report Authoring ● Powerful XML Report Definition Language • • • ● Extensible query execution architecture • • ● SQL Server / Analysis Services OLE DB / ODBC / Oracle Report Designer • • ● Freeform / Table / Matrix / Chart Parameters (Dynamic, Hierarchical) Sorting / Filtering / Grouping / Aggregates Interactivity (Drill-down, Drill-through) Full VB. NET Expression Language Integrated with SQL BI Workbench & VS. NET Query designers for SQL, Analysis Services Published definition enables 3 rd party tools Authoring

Graphical Report Layout

Integrated Query Design

Report Management ● ● ● Web Service APIs for management Web and Win 32 management user interfaces Report Management Functions • • ● Management Report Metadata (Name, Description, Connections, Credentials, Parameters) Scheduling integrated with SQL Server Agent Execution properties (live, cache or snapshot) History of prior executions Extensible, role-based security architecture

Web-based Management

Win 32 Management

Office Integration ● ● Import reports from Microsoft Access Office Document Formats • ● HTML rendering takes advantage of OWC • ● Interactive Charts, Pivot. Tables, Spreadsheets Share. Point Team Services • • • ● Excel, Word Report Web Parts Report delivery to document libraries, lists Report Library Template Share. Point Portal Server • • Protocol handler for search SSO, Profile integration

BIP 2003

BIP 2003

BIP 2003

BIP 2003

BIP 2003

BIP 2003

Back to 7

Back to 10