Hide Notes 

Slide 1: SQL Server 2005 “Yukon” The scalable, secure, enterprise-class data management platform Dubi Lebel Database technologies Solution & Technology Group Microsoft Israel
Slide 2: Database Historical View
Slide 3: SQL Server Product Roadmap SQL Server 6.5 SQL Server 7.0 SQL Server 2000 SQL Server “Yukon” • Data warehousing • Internet support • Differentiation from Sybase SQL Server • Re-architecture of relational server • First to include OLAP in database • Auto tuning • Ease-of-use • Reliability and scalability advancements • Deep XML support • Data warehousing • SQL Server CE • 64 bit support • Enterprise-class scalability • Programmability advancements • End-to-end business intelligence • Manageability • Support for multiple types of data Foundations of each release: • Lowest TCO • Ease-of-Use • Performance and Scalability • Integrated Business Intelligence
Slide 4: SQL Server 2000 A Major Release            XML support  SELECT … FOR XML  OpenXML  XML Views  XML Updategrams  XML View Mapper  XML Bulk Load URL and HTTP db access HTTP access to cubes Multi-instance support Integrated Data Mining Full-Text Search in formatted docs English Query for the Web C2 security rating (NSA) Installation disk imaging Active Directory integration Self-management and tuning                   Distributed Partitioned Views Log Shipping Parallel CREATE INDEX Parallel scan Parallel DBCC Failover clustering Failover cluster management 32 CPU SMP system support 64 GB RAM support VI SAN support Indexed views ROLAP dimension storage Distributed Partitioned Cubes Online index reorganization Differential backup User-defined functions Server-less snapshot backup  SQL Query Analyzer debugger                 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 DTS enhancements Online reorg Copy Database Wizard
Slide 5: 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 PocketPC devices
Slide 6: SQL Server 2000 Product Roadmap Since RTM SQLXML 1.0  XML Updategrams  XML Bulk Load SQL Server XML View Mapper 1.0 SQL Server 2000 Service Pack 1 Till today SQLXML 2.0  Managed classes XML schema support SQLXML 3.0 Web SQL Server “Yukon” Business Intelligence  End to End data warehousing Programmability  Deep XML support  Common Language Runtime integration Reliability  Self healing Availability  Online operations Scalability  Self-tuning 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
Slide 7: SQL Server 2005 “Yukon” The scalable, secure, enterprise-class data management platform
Slide 8: Business Agility Business Value Connected Productive  Increase Business Value    Connect with Customers (Revenue) Integrate with Partners (Profitability) Empower Employees (Productivity)  Total Cost of Ownership  Leverage existing assets  Hardware is getting LESS expensive  Downtime is MORE expensive Microsoft software enables business agility in today’s competitive climate Economics Dependable Total Costs
Slide 9: 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 Online Index Operations Web Service/XML for Analysis Data Types FileStream 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 Full DTS Control Flow Designer Performance Tuning 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 Indexing of XML Datatype Multiple Output Formats Database Failure and Redundancy Fail-over Clustering (up to 8 node) Parameters (Static, Dynamic, MDAC 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 ReportMicrosoft Confidential Definition .Net XML Framework Separation of Users and Schema
Slide 10: Programmability, Manageability, BI  Enable developers to take advantage of existing skills Extend and connect data across any platform Provide end-to-end development tools Programmability    Storage of all data types, formats Scalability, availability and security advancements Continued leadership and focus on ease-of-use Data Management and Storage    Comprehensive business intelligence solution Enable knowledge throughout the business Extend the value of existing applications Business Intelligence  
Slide 11: Programmability Programmability Enable developers to take advantage of existing skills    Use existing tools/processes (VS, SourceSafe, etc) Multiple languages: T-SQL, VB.NET, C#… One development framework for relational, XML, OLAP Data Management and Storage Extend and connect data across any platform    Native support for XML, Web Services Interoperability with any platform, application Open standards: HTTP, Soap, WSDL, XQuery, XSD Provide end-to-end development tools  Integrated SQL Server Workbench Simplified and consistent source control environment Work in Project and Server modes for fast deployment Business Intelligence  
Slide 12: Manageability Programmability Storage of all data types, formats    XML data type User defined data types Versioning, multiple active result set (MARS) Data Management and Storage Scalability, availability and security enhancements    Scale up partitioning, row level versioning… Snapshots, on-line operations, real time log shipping Secure by default, by design, by deployment Continued leadership & focus on ease-of-use  Self diagnosis Integrated scriptable/schedulable management UI Integrated relational and OLAP management Business Intelligence  
Slide 13: Business Intelligence Programmability Comprehensive business intelligence solution    Unified dimensional model (UDM) New data mining algorithms for advanced analytics BI development workbench for rapid BI development Data Management and Storage Enable knowledge throughout the business    Support for familiar end-user tools (Office) Scalable and reliable for 24*7 support Integration with operational systems Extend the value of existing applications  Supports heterogeneous data sources Enterprise-class ETL (DTS) Native XML Web Services Business Intelligence  
Slide 14: Mission Critical Platform Key Customer Priorities Secure Connected Productive Economic
Slide 15: Security Framework ● Secure Secure by Design • • • Architected & built for security today and tomorrow Secure and robust code Threat analysis and testing Reducing the attack surface Default configuration is a secure system Minimize attack surface Tools and training for secure environments Principle of least privilege Automate / Assist software maintenance Good tools for security assessment / admin ● Secure by Default • • • ● Secure by Deployment • • • •
Slide 16: 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
Slide 17: “Connected” SQL Server Web Services Distributed Query DTS Reporting Services Connected SQL Server Driver for JDBC Analysis Services DBLib for Unix Replication XML SQL Server Oracle IBM DB2 Sybase Informix Access Excel Text Files dBase Paradox VSAM Active Directory WebDAV etc...
Slide 18: Developers “Whidbey” Design Productive Manage Build Deploy
Slide 19: Economic Scalability   Optimized for Windows Server 2003 and Itanium Great performance    Large memory addressability (up to 32 TB) Nearly unlimited virtual memory (up to 8 TB) I/O savings due to larger memory buffer pools Manageability     T-SQL code-compatibility with SQL Server 2000 8 node clustering support Same on-disk format as 32-bit for easy migration One setup for database & OLAP based on Windows Installer technology Compelling alternative to expensive Unix solutions Cost Savings  The highly scalable database platform for memory intensive, performance-critical business applications
Slide 20: SQL 2005 Business Intelligence Reporting Services Management Tools Management Tools Analysis Services OLAP & Data Mining Development Tools Data Transformation Services SQL Server Relational Engine
Slide 21: 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, 3NF… * Complex relationships: Multigrains, many-to-many, role playing, indirect… * Recursive self joins * Slowly changing dimensions OLAP Cubes eU Th ied nif im D s io en * Multidimensional navigation * Hierarchical presentation * Friendly entity names * Powerful MDX calculations * Central KPI framework * “Actions” * Language translations * Multiple perspectives * Partitions * Aggregations * Distributed sources lM na el od
Slide 22: SQL Server 2005 Top Features ● ● ● For Database Administration For Development For Business Intelligence
Slide 23: 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
Slide 24: 1. Database Mirroring ● Database Failover – an Instant Standby • • • • Very Fast … less than 3 seconds Automatic or manual failover Zero data loss Automatic re-sync after failover Works with standard computers and storage No shared storage components Zero to minimal ● Hardware • • ● ● ● Impact to transaction throughput • Automatic, transparent client redirect Use ViewPoint to make mirror database a readable reporting database
Slide 25: Database Mirroring ● Fault Tolerant Virtual Database Witness Clients Principal Mirror
Slide 26: Database Mirroring How It Works ● Transaction Shipping Witness Principal Mirror Application 1 5 2 SQL Server SQL Server 2 4 3
Slide 27: 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
Slide 28: 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
Slide 29: 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
Slide 30: 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
Slide 31: Performance Tools ● Profiler • Profiling of Analysis Server • Showplan Events, Deadlock Events ● Replay • Improved multi-process replay
Slide 34: 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
Slide 35: SQL Server Best Practices Analyzer
Slide 36: 5. Data Partitioning ● Partitioning breaks a single object into multiple manageable pieces • • • • 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 ● Partitions can be created or dropped with virtually no loss of availability to the table • • • 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
Slide 37: 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”
Slide 38: The Paradigm Shift… Object 1 PARTITION FUNCTION Logical Logical Partition 1 Partition 2 Logical Partition N PARTITION SCHEME Physical Physical Partition 1 Partition 2 Physical Partition N
Slide 39: 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
Slide 40: 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)] ●
Slide 41: 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
Slide 42: 6. Media Reliability Features ● Enhanced Verification • RESTORE VERIFYONLY now checks everything it can short of writing the data Redundant backup media helps guarantee successful restore Extra copies for archival or disaster recovery Detect disk I/O errors not reported by the hardware or operating system Detect errors introduced by backup hardware but not reported by hardware or operating system Can continue past errors – repair later ● Backup Media Mirroring • • ● Database Page Checksums • ● Backup Checksums • •
Slide 43: 7. Dedicated Administration Connection ● ● Provides DBA access to server regardless of load No server restart to kill runaway session
Slide 44: 8. Database “ViewPoint” ● Read-only, consistent copy of a database • • Good for reporting Can rollback the actual database to ViewPoint  To correct user application error (or DBA error) ● ● Unchanging as database is modified Inexpensive • • Very fast to create Cheap in terms of space  “Copy-on-Write” technology ● Can be created for any database • • Production databases Instant standby databases with Database Mirroring
Slide 45: 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
Slide 46: 10. “No repro required” ● “No repro required”
Slide 47: SQL Server 2005 Top Features ● ● ● For Database Administration For Development For Business Intelligence
Slide 48: For Development 1. 2. 3. 4. 5. 6. 7. 8. 9. .NET Framework Integration XML Technologies XQuery Support API Enhancements: ADO .NET V2 SQL Service Broker T-SQL Enhancements Full Text Search Enhancements Security Enhancements HTTP/SOAP Features go to go to 83
Slide 49: 1. .NET Framework Integration ● ● ● ● ● Programming environment for Functions, Stored Procedures, Triggers User Defined Types, Aggregates In-Proc Data Access (ADO.NET V2) 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
Slide 50: The Developer Experience VB,C#,C++ VS .NET Project Runtime hosted by SQL (in-proc) Build Assembly: “TaxLib.dll” SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … SQL Queries: select sum(tax(sal,state)) from Emp where county = ‘King’ SQL Server
Slide 51: 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
Slide 52: Developer Productivity
Slide 53: 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
Slide 54: 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, xDoc XML NOT NULL) ● ● ● Store un-typed or typed XML instances Well-formed and validation checks Methods on XML data type • Query(), value(), exist() & modify()
Slide 55: Native XML Store XML Schema Support ● XML Schema • Rich mechanism for schema definitions • XML-based syntax (W3C 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
Slide 56: Native XML Store XML Index ● ● ● Create XML index on XML column CREATE XML INDEX idx_1 ON docs (xDoc) Creates indexes on tags, values & paths Speeds up queries • Entire query is optimized  Same award winning cost based optimizer • Indexes are used as available
Slide 57: 3. XQuery Support XML Query - XQuery ● Query XML document and data • Standards-based — W3C working draft http://www.w3.org/tr ● Methods on XML data type • • • • Query() Value() Exist() Modify() INT XML … Modify Update …
Slide 58: XQery Demo ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● CREATE TABLE docs (id INT PRIMARY KEY, xDoc 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
Slide 59: 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
Slide 60: XQery Demo insert ● ● ● ● ● ● ● ● ● ● INSERT INTO docs VALUES (3, NULL) GO -- Load from file UPDATE docs SET xDoc = (SELECT * FROM OPENROWSET (BULK 'C:\PWD\dev\TechEd04Eilat\demo1.xml', SINGLE_CLOB) AS xDoc) WHERE id=3 GO
Slide 61: XQery Demo select ● ● ● ● ● ● ● ● ● ● ● ● ● SELECT id, xDoc.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, xDoc.query( 'for $s in /doc[@id = 123]//section[@num >= 3] return <topic>{data($s/heading)}</topic>') FROM docs WHERE xDoc.exist ('/doc[@id = 123]//section[@num >= 3]') =1 GO
Slide 62: XQery Demo update ● ● ● ● ● ● ● ● ● ● ● UPDATE docs SET xDoc.modify( 'update (/doc[@id = 123]/section[@num = 4]/heading/text())[1] to "Algorithms"') WHERE id = 1 GO UPDATE docs SET xDoc.modify( 'delete /doc[@id = 123]/section[@num = 3]') WHERE id = 1 GO
Slide 63: 4. API Enhancements: ADO .NET V2 ● ● ● ● ● ● ● ● ● Multiple active result sets (MARS) Object Persistence FX (ObjectSpaces) Query notifications – for cache invalidation Functional parity with ADO (ADOX, etc) Server cursors (SqlResultSet) Asynchronous client access Bulk update, paging, and batching XML Data type support User Defined Type (UDT) support
Slide 64: 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, BizTalk and Indigo? ●
Slide 65: 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
Slide 66: Queuing at the Grocery Store multi-queue
Slide 67: Queuing at the Airport single queue queue readers
Slide 68: 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
Slide 69: What’s New? ● ● ● ● ● ● Message Integrity Message Ordering Multi-Reader Queues Distribution Transactional Messaging Queue Reader Management
Slide 70: Business Scenarios ● ● ● Order Entry Parallel/Asynchronous Stored Procedures Event Notifications
Slide 74: Service Programs A Service Program receives and sends Service Broker messages • Internal    Stored Procedure Activated by messages on queue TSQL or CLR Any program with a database connection Can do its own activation through WMI events • External  
Slide 75: 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
Slide 76: T-SQL Enhancements – Language ● ● New data types • • • • • Date/time types, varchar(max) Ranking functions Top enhancements Recursive query and CTE PIVOT/APPLY More expressive power ● ● Transact abort handling (Try…Catch) Queuing primitives (WAIT FOR)
Slide 77: 7. Fulltext Search ● ● ● Catalogs are included in Backup and Restore Attach and Detach includes catalogs Multi-Instance • One instance of Search per SQL Server
Slide 78: 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
Slide 79: 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 DataSet • Compatible with mid-tier prog. model
Slide 80: SQL Server 2005 Top Features ● ● ● For Database Administration For Development For Business Intelligence
Slide 81: For Business Intelligence 1. 2. 3. 4. 5. Tools One Click Cube UDM XML/A Based Protocol DTS – Data Transformation Services 6. 7. 8. 9. Availability data mining AMO Stored Procedures go to 118
Slide 82: 1. The Tools - Evolution SQL 2000 EM 2000 QA 2000 AM 2000 SQL Servwer2005 SQL Management Workbench w Ne BI Development Workbench Target Audience DBA Developer
Slide 83: Easy Development – The BI Workbench ● ● A complete, integrated tool for the development of BI applications One tool, multiple technologies: • Relational, OLAP, DM, DTS, reporting, code, Web pages… ● Enterprise software development environment • • Integrated into Visual Studio .NET Team development, source control, versioning, developer isolation, resource independent coding Develop, test, deploy, modify, test… ● Development cycle lifetime support • ● Breakthrough ease of use
Slide 84: Productivity Enhancements ● Creating the Sales and Warehouse cubes in Foodmart User Actions SQL 2000 115 = 112 dim. wizards, 3 cube wizards 115 1321 SQL 2005 1 cube wizard 7 7 # of wizards launched # of objects created # of mouse clicks
Slide 85: 2. One Click Cube ● ● New Cube Wizard for creating cube in data warehouse project An option to enable One-Click-Cube detection and Suggestions
Slide 86: The Enterprise BI Data Modeling Needs             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
Slide 87: 2. UDM Relational Versus OLAP Reports Feature 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 Relational OLAP                    
Slide 88: Enterprise BI – Today Data Source MOLAP Data Model Tool OLAP Browser (1) OLAP Browser (2) Reporting Tool (3) Reporting Tool (2) Reporting Tool (1) MOLAP Datamart Datamart DW
Slide 89: Enterprise BI – A Messy Reality Data Source MOLAP Data Model Tool OLAP Browser (1) OLAP Browser (2) Reporting Tool (3) Reporting Tool (2) Reporting Tool (1) OLAP versus Reporting MOLAP Datamart Datamart DW Duplicate Data Duplicate Models
Slide 90: 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, 3NF… * Complex relationships: Multi-grains, 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
Slide 91: The Unified Dimensional Model The best of Relational and OLAP Relational Reporting        he T * Multiple fact tables * Full richness the dimensions’ attributes * Transaction level access * Star, snowflake, 3NF… * Complex relationships: Multi-grains, many-to-many, role playing, indirect… * Recursive self joins * Slowly changing dimensions ied nif U el od lM na s io en im D            OLAP Cubes * Multidimensional navigation * Hierarchical presentation * Friendly entity names * Powerful MDX calculations * Central KPI framework * “Actions” * Language translations * Multiple perspectives * Partitions * Aggregations * Distributed sources
Slide 92: Enterprise BI – With A UDM Data Source MOLAP Data Model Tool OLAP Browser (1) OLAP Browser (2) MOLAP Datamart Datamart UDM Reporting Tool (2) Reporting Tool (1) BI Applications DW
Slide 93: Enterprise BI – With A UDM Data Source MOLAP Data Model Tool OLAP Browser (1) OLAP Browser (2) MOLAP Datamart Datamart UDM Reporting Tool (2) Reporting Tool (1) BI Applications DW A single dimensional model for all OLAP analysis and Relational reporting needs
Slide 94: Analysis Services Data Source Analysis Services Streamlined BI Infrastructure Tool OLAP Browser (1) DW XML/A or ODBO OLAP Browser (2) Reporting Tool (2) Reporting Tool (1) BI Applications UDM Cache    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 3rd party tools
Slide 95: 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     XML/A is native protocol Every UDM is a web service Zero client footprint HTTP & TCP/IP support  XML/A   Standard based (HTTP, SOAP, WSDL, UDDI) Supported by all leading BI vendors WS evolution of OLE DB for OLAP
Slide 96: Light Footprint Win32 Interface OLE DB for OLAP ADOMD Internet / Intranet Analysis Server Any App, Any Device, Language, Platform C++ App VB6 App Win32 XML for analysis Metadata discovery MDX query execution    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
Slide 97: Cross Platform Object Models OLE DB for OLAP ADOMD ADOMD.Net Analysis Server ADOMD.J C++ App VB6 App .Net App Java App Win32 .Net J2EE XML for analysis Any App, Any Device, Language, Platform    “Pure Managed Code” .Net Object model “Pure Java” object model for easy and optimized access from any J2EE platform Both object models compatible with ADO MD (the most popular BI object model)
Slide 98: Link Udms Across The Internet OLE DB for OLAP ADOMD ADOMD.Net Analysis Server ADOMD.J C++ App VB6 App .Net App Java App Win32 .Net J2EE XML for analysis Any App, Any Device, Language, Platform Analysis Server   UDMs can link to other UDMs Link within the same database, across databases, across servers and across the internet
Slide 99: 5. DTS – Data Transformation Services What Is New In DTS ● Short version EVERYTHING !
Slide 100: SQL Server 2005 Vision For DTS ● Enterprise   Comprehensive and manageable Redefine performance and scale ● ETL   Readily handle complex scenarios Extensive BI features Highly productive IDE Extensible, embeddable architecture ● Platform   The enterprise ETL platform for Windows
Slide 101: DTS For Dependable ETL ● Error flows • • Stream problem data without failures Fix and reprocess inline or save to disk for later Execute complex sequences that respond to package events Readily design packages aware of external change Check point restart on failures Ensure integrity with certificates ● Event handling • ● WMI and MSMQ • ● Restart • ● Digital signing •
Slide 102: DTS For Manageable ETL ● Configuration • • 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 Log packages, tasks and sequences Log to multiple providers including   ● Deployment • • • ● Logging • • SQL Profiler, NT Event Log, XML Or to custom providers ● Performance monitor • Rich selection of performance counters
Slide 103: DTS For Performance ● Transform engine • Pipeline architecture • Transforms, sources, destinations  As many as you’d like! • single pass transformations
Slide 104: 6. Availability ● Failover and Active/Active Clustering • • Out of the box experience Support for n + 1 node cluster environment On par with SQL Server Very easy deployment – no registry entries needed No 2GB limit any more Parameterized: data folders, remote partitions, compression allows terminating a session that is draining server resources (ex. runaway query) ● Multi-Instances • • ● Enhanced backup and restore • • ● Cancel command •
Slide 105: 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
Slide 106: 7. Data Mining Key Messages ● Embedded data mining  Embed DM to LOB applications Complete SQL language based API Native XML/A support ● Integrated solution Complete set of analytics Alliance with ISVs   ● ●
Slide 107: 7. Data Mining Key Messages ● Embedded data mining Integrated solution Complete set of analytics Alliance with ISVs ●  Tight integration with Relational, OLAP, DTS, reporting technologies SQL Server: The Platform for BI ●  ●
Slide 108: 7. Data Mining Key Messages ● Embedded data mining Integrated solution Complete set of analytics Alliance with ISVs ●  Most popular data mining algorithms Enhanced viewer controls ●  ●
Slide 109: 7. Data Mining Key Messages ● Embedded data mining Integrated solution Complete set of analytics Alliance with ISVs Focus on broadening the market rather than fight for share Agreed on finalizing DM industry standard based on OLE DB for DM and XML/A ●   ● ●
Slide 110: Complete Set Of Algorithms Decision Trees Clustering Time Series Sequence Clustering Association Naïve Bayes Neural Net
Slide 111: API: DMX CREATE MINING MODEL CreditRisk (CustID Gender Income Risk LONG KEY, TEXT DISCRETE, LONG CONTINUOUS, TEXT DISCRETE PREDICT) INSERT INTO CreditRisk (CustId, Gender, Income, Profession, Risk) Select CustomerID, Gender, Income, Profession,Risk From Customers Profession TEXT DISCRETE, USING Microsoft_Decision_Trees Select NewCustomers.CustomerID, CreditRisk.Risk, PredictProbability(CreditRisk) FROM CreditRisk PREDICTION JOIN NewCustomers ON CreditRisk.Gender=NewCustomer.Gender AND CreditRisk.Income=NewCustomer.Income AND CreditRisk.Profession=NewCustomer.Profession
Slide 112: Data Mining Flow ● ● Tight integration with DTS data flow and task flow Data mining transforms • • • • Model training Prediction Lift Nesting Prediction Processing ● Data mining tasks • •
Slide 113: 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
Slide 114: 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
Slide 115: • • • • .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
Slide 116: Replication Service Broker Reporting Services SQL Server 2005 SQL Server Agent Relational Database Engine .NET CLR Analysis Services Notification Services Data Transformation Services
Slide 117: SQL Roadmap Web Services Everywhere “Longhorn” • Longhorn Storage Advancements Wave • SQL 2005• • “Yukon” • Wave • SQL Server 2000 Wave • 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 SP3 • SQL Server CE V2 • SQL Server 64-bit • Notification Services
Slide 118: © 2003-2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
Slide 119: ‫שאלות / אוטוגרפים‬  Dubil@microsoft.com
Slide 120: © 2002 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Slide 121: SQL Server Notification Services
Slide 122: Getting Information Today You Browse Request Information Sources Web Sites Newsgroups LOB Applications
Slide 123: Existing Systems (Problems) You SPAM Information Push Information Sources Web Sites Newsgroups LOB Applications
Slide 124: Notification Applications What are they? A new class of web application that delivers personalized and timely information to any device. Any device Timely Personal
Slide 125: 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  Based on XML and T-SQL Based on SQL Server 2000 and .NET Framework An efficient and scalable execution engine  ● ● You write Notification Application - Notification Services hosts and executes it Ships as a web download for SQL 2000
Slide 126: Notification Applications How Do Notification Applications Work? Subscriptions (millions) Events Activity (thousands) Notifications Match (millions)
Slide 127: Customer Adoption
Slide 128: Timely Delivery of Information Activity    Generation  Routing   End Points     Stock feeds New services Custom applications LOB applications Etc. SQL Server Notification Services     .NET Alerts Greenwich Services SMS Aggregators Exchange (SMTP) MSMQ Web Service      Outlook Windows Messenger 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
Slide 129: SQL Server Reporting Services Back to 10
Slide 130: Reporting Lifecycle SQL Server Reporting Services supports the full reporting lifecycle, including Authoring, Management and Delivery. Authoring Management Delivery
Slide 131: Browser Office Custom Application Data Sources (SQL, OLE DB, XML/A, ODBC, Oracle, Custom) XML Web Service Interface Report Server Report Processing Data Processing Rendering Security Scheduling & Delivery Security Services (NT, Passport, Custom) Output Formats (HTML, Excel, PDF, Custom) SQL Server Catalog Delivery Targets (E-mail, SharePoint, Custom)
Slide 132: Delivery Report Delivery ● Intermediate format for rendering reports to diverse output formats • • • Web Formats (HTML 4 and 3.2) Print Formats (TIFF, PDF) Data Formats (Excel, XML, CSV) ● Personalized and administrator defined subscription and delivery • • • Ability to select output formats per subscription Can deliver links as well as rendered reports Data-driven subscriptions for personal delivery E-mail, SharePoint ● Extensible delivery provider architecture •
Slide 133: Web-based Rendering
Slide 134: Print Formatted Output
Slide 135: Personalized E-Mail Delivery
Slide 136: Report Authoring ● Authoring Powerful XML Report Definition Language • • • • • Freeform / Table / Matrix / Chart Parameters (Dynamic, Hierarchical) Sorting / Filtering / Grouping / Aggregates Interactivity (Drill-down, Drill-through) Full VB.NET Expression Language SQL Server / Analysis Services OLE DB / ODBC / Oracle Integrated with SQL BI Workbench & VS.NET Query designers for SQL, Analysis Services ● Extensible query execution architecture • • ● Report Designer • • ● Published definition enables 3rd party tools
Slide 137: Graphical Report Layout
Slide 138: Integrated Query Design
Slide 139: Report Management ● ● ● Management Web Service APIs for management Web and Win32 management user interfaces Report Management Functions • • • • 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
Slide 140: Web-based Management
Slide 141: Win32 Management
Slide 142: Office Integration ● ● ● ● Import reports from Microsoft Access Office Document Formats • • • • • Excel, Word Interactive Charts, PivotTables, Spreadsheets Report Web Parts Report delivery to document libraries, lists Report Library Template Protocol handler for search SSO, Profile integration HTML rendering takes advantage of OWC SharePoint Team Services ● SharePoint Portal Server • •
Slide 143: BIP 2003
Slide 144: BIP 2003
Slide 145: BIP 2003
Slide 146: BIP 2003
Slide 147: BIP 2003
Slide 148: BIP 2003
Slide 150: Back to 7
Slide 151: Back to 10