1 / 33

What’s New in SQL Server 2012

What’s New in SQL Server 2012. 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: @AaronBertrand. Overview.

azriel
Download Presentation

What’s New in SQL Server 2012

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 SQL Server 2012 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, cluster on WoW 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 error conditions, severity, responsiveness DBA-203 | What’s New in “Denali”

  7. Availability • Multi-Subnet Clustering • Geographically dispersed failover • Requires common domain + Win2008 R2 • Local Drive Flexibility • Use more accessible SSD drives for TempDB • Mount points can be on local disk 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 new DMV/XE 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 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 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 • More restore options DBA-203 | What’s New in “Denali”

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

  13. Juneau – Table Designer DBA-203 | What’s New in “Denali”

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

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

  16. Programmability • OFFSET / FETCH • Works like MySQL’s LIMIT, but ANSI standard • Performance difference negligible • SEQUENCE • Central IDENTITY mechanism (like Oracle) • Performs better than IDENTITY • Same transaction limitations DBA-203 | What’s New in “Denali”

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

  18. Programmability • Windowing Enhancements • Moving average, median, other aggs based on surrounding rows (“framing”) • LAG/LEAD, FIRST_VALUE/LAST_VALUE • CUME_DIST(), PERCENT_RANK(), PERCENTILE_CONT() • Can also use WITHIN GROUP / DATEDIFF • Much faster running totals, moving sums, etc. DBA-203 | What’s New in “Denali”

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

  20. Programmability • Date/Time Constructors • DATEFROMPARTS • TIMEFROMPARTS • DATETIMEFROMPARTS • SMALLDATETIMEFROMPARTS • DATETIME2FROMPARTS • DATETIMEOFFSETFROMPARTS DBA-203 | What’s New in “Denali”

  21. Programmability TRY_CONVERT() FORMAT() PARSE() TRY_PARSE() DBA-203 | What’s New in “Denali”

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

  23. Programmability • Full-Text Search • Property searching for Office 2007+ docs • Customizable proximity term (NEAR / ~) • Semantic Search • Log() • Now takes optional base argument, e.g. • SELECTLOG(32, 2); DBA-203 | What’s New in “Denali”

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

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

  26. Performance • Resource Governor • More resource pools (64 vs. 20) • Scheduler / NUMA node affinity • Real CPU capping • More memory control DBA-203 | What’s New in “Denali”

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

  28. Testing / Troubleshooting • Distributed Replay Utility • Easily replay traces from multiple servers • Up to 16 servers x 255 threads • With or without real-time synchronization • More realistic simulation of workloads DBA-203 | What’s New in “Denali”

  29. What’s Being deprecated? • 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”

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

  31. Complete the Evaluation Form to Win! Sponsored by Dell • Win a Dell Mini Netbook – every day – just for submitting your completed form. Each session evaluation form represents a chance to win. • Pick up your evaluation form: • In each presentation room • Online on the PASS Summit website • Drop off your completed form: • Near the exit of each presentation room • At the Registration desk • Online on the PASS Summit website DBA-203 | What’s New in “Denali”

  32. Thank you for attending this session and the 2011 PASS Summit in Seattle

  33. Hands-on Labs Get experienced through self-paced & instructor-led labs on our cloud based lab platform - bring your laptop or use HP provided hardware Microsoft SQL Server Clinic Work through your technical issues with SQL Server CSS & get architectural guidance from SQLCAT Ask The Experts @ Dev Pods Meet Microsoft SQL Server Engineering team members & SQL MVPs Microsoft Product Pavilion Talk with Microsoft SQL Server & BI experts to learn about the next version of SQL Server and check out the new Database Consolidation Appliance Room 611 Expo Hall 6th Floor Lobby Room 618-620 DBA-203 | What’s New in “Denali”

More Related