What’s New in “Denali”

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

2. 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: @AaronBertrand DBA-203 | What’s New in “Denali”

3. 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”

4. Setup • New prerequisites: • PowerShell 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”

5. 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”

6. Availability • AlwaysOn • 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”

7. Availability • Multi-Subnet Clustering • Geographically dispersed failover • Requires common domain + Win2008 R2 • Local TempDB • Use cheaper SSD drives in server DBA-203 | What’s New in “Denali”

8. 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”

9. Manageability • FileTable • FileTable managed by SQL using Win32 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”

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

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

12. 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”

13. 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”

14. 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”

15. Programmability • OFFSET / FETCH • Works like MySQL’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”

16. 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”

17. 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”

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

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

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

21. 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”

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

23. 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”

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

25. 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”

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

27. 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”

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