1 / 28

What's New in Denali: Engine and Tools Overview by Aaron Bertrand - SQL Sentry Inc.

Explore the latest updates in SQL Server "Denali" covering engine, tools, setup changes, manageability, security, availability, and programmability. Discover key enhancements such as AlwaysOn, FileTable, and multi-subnet clustering. Get insights from Aaron Bertrand, a seasoned expert and Senior Consultant at SQL Sentry. Learn about new features like custom server roles, metadata discovery, and windowing enhancements. Stay informed about the advancements in SQL Server "Denali" to enhance your database management strategies.

farren
Download Presentation

What's New in Denali: Engine and Tools Overview by Aaron Bertrand - SQL Sentry Inc.

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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”

More Related