sql server denali features

SQL Server vNext Denali Overview:

SQL Server vNext Denali Overview Ing. Eduardo Castro, PhD ecastro@mswindowscr.org

Slide 2:

This presentation is based on the Presentaion What's new in SQL11 by Roger Noble

Agenda:

Agenda Overview of SQL Server vNext Denali Major Enhancements from SQL Server 2008 R2 to SQL Server vNext Denali

Overview:

Overview What is Denali? T-SQL Enhancements HADR Atlanta Data Quality Services SSIS Enhancements Column-store index BISM, PowerPivot and SSAS Crescent

SQL Denali Introduction:

SQL Denali Introduction SQL Server 11 CTP 1 currently available to all RTM Prerequisites PowerShell 2.0 .NET Framework 4.0

Main highlights:

Main highlights Download SQL Denali http :// tinyurl.com/39ubk7r SQL Server 2008 R2 Parallel Data Warehouse Microsoft Critical Advantage Program SQL Azure Reporting SQL Azure

Areas unchanged in CTP1 :

Areas unchanged in CTP1 Analysis Services Data Quality Services Reporting Services Replication Service Broker

SQL Server Engine :

SQL Server Engine SMSS uses VS2010 shell Transact-SQL debugging and IntelliSense Transact-SQL Snippets Contained databases SEQUENCE objects Circular arc segment (spatial) User-defined server roles

T-SQL Enhancements:

T-SQL Enhancements Containment Databases FileTable Storage Sequence Generators Paging UTF-16 collation (SC) Error Handling (THROW) Circular arc segment (spatial)

Databases are not always easy to move:

Databases are not always easy to move Master MSDB Temp Master MSDB User DB TempDB Instance Collation Logins Credentials Linked Server Defs. CLR … Agent Replication DB Mail … … TempDB Collation Other Apps Other DBs User DB User DB User DB Tobias Ternstrom @Pass Summit 2010 – http ://sqlpass.eventpoint.com/topic/details/DBA346M

T-SQL Enhancements:

T-SQL Enhancements New database option – CONTAINMENT Only option supported in Denali is PARTIAL meaning, non-enforaced containment Partially contained databases solve problems related to: Logins: Database Users with passwords or mapped directly to Windows principles System Collation: Temp tables use the database’s collation sys.dm_db_uncontained_entities will display all potential containment breaches Tobias Ternstrom @Pass Summit 2010 – http ://sqlpass.eventpoint.com/topic/details/DBA346M

T-SQL Enhancements:

T-SQL Enhancements FileTable A merging of the FILESTREAM and HierarchyID Can store files and folders FileTables are accessed via windows share Created table has predefined schema CREATE TABLE DocumentStore AS FileTable WITH FileTable_Directory Document FILESTREAM_ON FILESTREAMGroup1;

T-SQL Enhancements:

T-SQL Enhancements SEQUENCE Generates a predicatble number of values Global Can be ascending, descending, minimum, maximum and cycle Supports getting a range via sp_sequence_get_range

T-SQL Enhancements:

T-SQL Enhancements Demo

High Availability and Disaster Recovery:

High Availability and Disaster Recovery Several improvements over existing HA/DR Multi-database failover Multiple secondaries Mirrors can be queried Group databases to allow them to failover together Needs Server 2008 R2 with Failover Clustering

Availability Group Concepts Overview:

Availability Group Concepts Overview Availability Group Defines the high availability requirements Databases, Replicas, Availability Mode, Failover Mode etc. Availability Replica SQL Server Instances that are part of the availability group which hosts the physical copy of the database Role: Primary, Secondary, Resolving Availability Database SQL Server database that is part of an availability group This can be a regular database or contained database Gopal Askok @Pass Summit 2010 – http://sqlpass.eventpoint.com/topic/details/DBA351M

'Atlanta' :

'Atlanta' Runs on your SQL Server system Uploading information to cloud service Configuration Performance, etc Browse using a dashboard Give access to Microsoft Support

Atlanta:

Atlanta Cloud-based SQL Server monitoring tool Can monitor SQL Server 2008 and up This secure cloud service will help DBAs to Proactively avoid problems ongoing monitoring and alerting of configuration from the cloud Reduce downtime and improve performance proactive scanning for known configuration issues and comparison with best practices Resolve issues faster ability to get a current view of your configuration, changes that have been made, and use the most up to date solutions to solve them

Slide 19:

http://onlinehelp.microsoft.com/en-us/atlanta/ff962512.aspx

Microsoft Code Name Atlanta:

Microsoft Code Name Atlanta Alerts View alerts to see any issues that have been detected. Servers View and manage the gateways and agents deployed in your environment Configuration: Current Snapshot View current configuration information for the computers being analyzed. Configuration: Change History View historical configuration information for the computers being analyzed.

https://beta.microsoftatlanta.com :

Demo https://beta.microsoftatlanta.com

Data Quality Services:

Data Quality Services Web based tool that is used to cleanse data Rules based on a user defined knowledge base SSIS component Zoomix's Accelerator software combines semantic and linguistic analysis with machine learning to classify, match and standardize complex corporate data

Data Quality Services Overview:

Data Quality Services Overview Denise Draper @Pass Summit 2010 - http://sqlpass.eventpoint.com/topic/details/BIA283M

Slide 24:

A DQKB contains Data domains – containing all information related to a specific data type (name, city, state, postcode, id number…). Each domain stores all identified values, rules and 3 rd party reference data that are relevant to this data domain. Matching policy – a set of rules that defines the matching computation Domains can originate from one of two sources Pre-Packaged Knowledge – a set of out-of-the-box data domains that will enable organizations an easy to use friction free starting point for their DQ work User-Generated Knowledge - organizations will be able to build and manage the DQKB knowledge from samples of their business data, or by the data steward using the DQS client. Denise Draper @Pass Summit 2010 http://sqlpass.eventpoint.com/topic/details/BIA283M

Slide 25:

Denise Draper @Pass Summit 2010 http ://sqlpass.eventpoint.com/topic/details/BIA283M

Data Quality Services:

Data Quality Services The cleansing process classifies data into 4 categories Correct Corrected Not Corrected Auto Suggested Completeness Accuracy Conformity Consistency

SQL Server Integration Services :

SQL Server Integration Services “Biggest change since 2005” New deployment model Object Impact and Data Lineage Analysis Dependency Designer Merge and Merge Join memory improvements Designer enhancements Edit disconnected components Data correction component

SQL Server “Denali” CTP1 - Integration Services :

SQL Server “Denali” CTP1 - Integration Services Integration Services catalog is the central storage and administration point for Integration Services projects, packages, parameters, and environments There is only one catalog, a user database named SSISDB, on an instance of SQL Server

Integration Services Catalog:

Integration Services Catalog Enabling CLR Integration Create Catalog dialog box you will be asked to supply a password. This password is used to create a database master key. The database master key is used to encrypt sensitive data in the catalog . Sensitive Parameter Value Encryption Operations Cleanup Project Version Cleanup

Column Store Indexes:

Column Store Indexes PowerPivot in SQL Server An index is created across columns instead of rows Can give a massive query performance increase Read only Reduced IO Compressed Not a NoSQL implementation / alternative

Denali Columnstore Indexes:

Denali Columnstore Indexes Denali introduces a new type of index called the columnstore . This new type of index is built up on the values across columns instead of traditional row based indexes. As data tends to be less unique across a column it allows the columnstore to efficiently compress and store data.

Denali Columnstore Indexes:

Denali Columnstore Indexes The columnstore is currently read-only, however it can be updated via dropping and recreating the index, or switching in a partition . Due to the ability to compress and keep the index in memory the Columnstore can give massive (10x, 100x, 1000x…) increase in speed to warehouse queries Not all queries can benefit and some can regress. In general typical star join queries found in a Data Warehouse when only a portion of the columns are selected will get the biggest benefit.

Denali Columnstore Indexes:

Denali Columnstore Indexes http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Server%2011.pdf

Slide 34:

http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9 / Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Server%2011.pdf

Business Intelligence Semantic Model:

Business Intelligence Semantic Model Analysis Services is perceived to be “too complicated” Can be in either one of two modes VertiPaq engine is used to store data locally in Relational tables Real-time Uses Column Store for huge performance gains DAX expression language Does not replace UDM Can author in Excel or BIDS (VS 2010) Can be hosted by Analysis Services

Slide 36:

Third-Party BI Applications Reporting Services Reports Excel Workbooks PowerPivot Applications SharePoint Dashboards & Scorecards Files OData Feeds Data Model Business Logic Data Access BI Applications Data Sources Relational Multidimensional DAX MDX VertiPaq Real-time BI Semantic Model T.K. Anand @Pass Summit 2010 - http://sqlpass.eventpoint.com/topic/details/BIA381M

'Crescent' :

'Crescent' Web-based reporting experience that offers rich visualization to let you project the story about your data to end users and other business users

Microsoft Project "Crescent:

Microsoft Project "Crescent New data visualization experience coming in the SQL "Denali". Project Crescent allows business users to manage data and show information visualize, interact and report on data using highly interactive visualizations, animations and smart querying Most exciting features is its integration with PowerPoint

Crescent:

Crescent Self service BI / ad-hoc reporting Based on BISM Hosted in SharePoint 2010 with SSRS and SSAS integration Easily export to PowerPoint

Microsoft Project "Crescent:

Microsoft Project "Crescent Through a feature called Storyboarding, users can embed reports in PowerPoint and manipulate the live data during a presentation The first public CTP for Project “Crescent” is currently planned for the first half of calendar year 2011

Project Crescent:

Project Crescent Demo

Slide 43:

PowerPivot + Analysis Services Architecture Excel Services PowerPivot for SharePoint (Analysis Services) Excel Internet Explorer Analysis Services BISM SharePoint Reporting Services Third Party Apps PowerPivot for Excel xlsx Analysis Services UDM xlsx BI Development Studio / Project Juneau Personal BI Team BI Organizational BI T.K. Anand @Pass Summit 2010 - http://sqlpass.eventpoint.com/topic/details/BIA381M

PowerPivot vs. Analysis Services:

PowerPivot vs. Analysis Services When to use what? Depends on the complexity of the app Use UDM for multidimensional modeling, complex calculations, scoped assignments, calc members, custom rollups, etc. For everything else, BISM offers a simpler and high performance alternative Existing BI solutions Stick with UDM… Consider BISM if you’re planning a major solution upgrade Migration from UDM to BISM will require some redesign BISM and UDM are available side-by-side Instance level choice Chris Webb @Pass Summit 2010 - http://sqlpass.eventpoint.com/topic/details/BIA412

References:

References What’s new BOL - http ://msdn.microsoft.com/en-us/library/bb500435(v=SQL.110).aspx HADR - http://msdn.microsoft.com/en-us/library/ff877884(SQL.110).aspx & http ://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks / Atlanta - https://www.microsoftatlanta.com/ T-SQL - Tobias Ternström Sequence Generators - http://msdn.microsoft.com/en-us/library/ff878058(SQL.110). aspx & http:// www.sergeyv.com/blog/archive/2010/11/09/sql-server-sequence-generators.aspx Contained Databases - http:// sqlblog.com/blogs/aaron_bertrand/archive/2010/11/16/sql-server-v-next-denali-contained-databases.aspx Data Quality Services - Denise Draper Column Store - http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9/Columnstore Indexes for Fast DW QP SQL Server 11.pdf UDM & BISM - http:// blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx & http:// prologika.com/CS/blogs/blog/archive/2010/11/13/business-intelligence-semantic-model-the-good-the-bad-and-the-ugly.aspx SSIS - Matt Masson SSIS Resolve References - http:// www.sqlservercentral.com/blogs/dknight/archive/2010/11/15/ssis-denali-resolve-references.aspx BISM vs SSAS - http://cwebbbi.wordpress.com/2010/11/14/pass-summit-day-2-the-aftermath/ Crescent - http://www.jenstirrup.com/2010/11/project-crescent-when-is-it-best_1327.html

SQL Server vNext Denali Overview:

SQL Server vNext Denali Overview Ing. Eduardo Castro, PhD ecastro@mswindowscr.org