Whats New in Denali Engine and Tools Aaron

What’s New in “Denali” Engine and Tools Aaron Bertrand SQL Sentry, Inc.

Who is Aaron Bertrand? • Senior Consultant at SQL Sentry www. sqlsentry. net abertrand@sqlsentry. net • Microsoft MVP since 1997 -98 • Blog: www. sqlblog. com • Twitter: @Aaron. Bertrand DBA-203 | What’s New in “Denali” 2

Overview • Changes to setup, engine and tools • Will not be covering BI features • Some features NDA or subject to change DBA-203 | What’s New in “Denali” 3

Setup • New prerequisites: • • • Power. Shell 2. 0, “no-reboot” package Simpler slipstream process Server Core supported 32 -bit still supported (for now) No longer supported: • AWE, Itanium DBA-203 | What’s New in “Denali” 4

Books Online • Revamped content organization • Several usability enhancements: • • Utility to switch local/online preferences Better content updating experience DBA-203 | What’s New in “Denali” 5

Availability Always. On • Availability groups: databases as a unit • Read-only secondaries • Compression, encryption, FILESTREAM • Requires WSFC Flexible Failover Policy • Based on Failure condition, severity, responsiveness DBA-203 | What’s New in “Denali” 6

Availability Multi-Subnet Clustering • Geographically dispersed failover • Requires common domain + Win 2008 R 2 Local Temp. DB • Use cheaper SSD drives in server DBA-203 | What’s New in “Denali” 7

Manageability Contained Databases • Isolate parts of a database that are server- or tempdb-dependent • Provide alternatives to support containment • Identify potential issues via a new DMV UTF-16 Collations (_SC) DBA-203 | What’s New in “Denali” 8

Manageability File. Table • File. Table managed by SQL using Win 32 API • Can run set-based DML against files/folders • SQL detects external changes Startup options now easier to configure Expanded Policy-Based Management Facets DBA-203 | What’s New in “Denali” 9

Security Custom Server Roles • Separation of duties at server level • New DDL; sp_*role* procedures deprecated HASHBYTES() • Supports SHA 2_256/512; still limited to 8 K New Permissions to Support New Features DBA-203 | What’s New in “Denali” 10

Programmability Management Studio • Port to VS shell: • • • Multi-monitor support Zoom Powerful region editing Snippets, Surround With Intelli. Sense improvements Clipboard cycle • Debugging enhancements DBA-203 | What’s New in “Denali” 11

Programmability SQL Server Developer Tools (“Juneau”) • More integrated Visual Studio experience • “Reveal Codes”-style table designer • Sandbox development and debugging • Build/deploy can target 2005+ and Azure • Smart refactor/delete, easier CLR • Not a full replacement for “Data Dude” or Management Studio DBA-203 | What’s New in “Denali” 12

Programmability Metadata Discovery • New procedures/DMVs to inspect resultsets • Replaces SET FMTONLY ON • Automatically used by ODBC / OLEDB DBA-203 | What’s New in “Denali” 13

Programmability EXECUTE … WITH RESULT SETS • Define a “contract” for shape of result • Rename redundant columns, force types • Allow apps to adjust to schema changes at different rates DBA-203 | What’s New in “Denali” 14

Programmability OFFSET / FETCH • Works like My. SQL’s LIMIT, but ANSI standard • No performance gain, just syntactic sugar SEQUENCE • Central IDENTITY mechanism (like Oracle) • Performs better than IDENTITY • Same transaction limitations DBA-203 | What’s New in “Denali” 15

Programmability THROW • Raise custom errors without sys. messages • Can use in CATCH or outside • Outside, severity is always 16 • Some RAISERROR functionality is missing DBA-203 | What’s New in “Denali” 16

Programmability Windowing Enhancements • Moving average and other aggregates based on preceding/following rows • LAG/LEAD, DATEDIFF, FIRST_VALUE/LAST_VALUE • Distribution / Median • • CUME_DIST(), PERCENT_RANK(), PERCENTILE_CONT() Can also use WITHIN GROUP DBA-203 | What’s New in “Denali” 17

Programmability IIF() • This is just syntactic sugar around CASE CHOOSE() CONCAT() • Not a group concat function! EOMONTH() DBA-203 | What’s New in “Denali” 18

Programmability Date/Time Constructors • Construct date/time values without messy string manipulation • • • DATEFROMPARTS, TIMEFROMPARTS DATETIMEFROMPARTS, SMALLDATETIMEFROMPARTS DATETIME 2 FROMPARTS, DATETIMEOFFSETFROMPARTS DBA-203 | What’s New in “Denali” 19

Programmability FORMAT() • . NET parity, with exceptions PARSE() TRY_CONVERT() TRY_PARSE() DBA-203 | What’s New in “Denali” 20

Programmability FORCESEEK enhancements • Can now specify index/columns New FORCESCAN hint • For obscure scenarios where you want a scan DBA-203 | What’s New in “Denali” 21

Programmability Full-Text Search • Property searching for Office 2007+ docs • Customizable proximity term (NEAR / ~) Spatial Improvements DBA-203 | What’s New in “Denali” 22

Performance Column-based storage (“Apollo”) • Stores columns together on pages • Best for star joins, aggregates • Not so good for unions, outer joins • Many limitations in Denali DBA-203 | What’s New in “Denali” 23

Performance New Online Operations • Index rebuild with LOB • Add column with default Underlying performance enhancements • FILESTREAM, Full-Text Search • 2008/R 2 fixes ported forward DBA-203 | What’s New in “Denali” 24

Troubleshooting Extended Events Investments • New profiler-like session viewer • New handling for service broker, checkpoint, memory, disk New DMOs / system procedures • dm_os_volume_stats, dm_os_windows_info • dm_server_registry, dm_server_services • sys. sp_server_diagnostics DBA-203 | What’s New in “Denali” 25

Testing / Troubleshooting Distributed Replay Utility • Easily replay traces from multiple servers • More realistic simulation of workloads DBA-203 | What’s New in “Denali” 26

What’s Being deprecated? • • • SET FMTONLY DATABASEPROPERTY() osql. exe, sqlmaint. exe, SQL Mail, SQL-DMO SQL Server 2000 (80) compatibility OLEDB provider for SQL Server DBA-203 | What’s New in “Denali” 27

Resources http: //bit. ly/AB-Denali-Links DBA-203 | What’s New in “Denali” 28