1 / 71

What’s New in Microsoft SQL Server 2012 for Administrators & Developers

What’s New in Microsoft SQL Server 2012 for Administrators & Developers. Bryan Smith brysmi@microsoft.com. Online Operation Enhancements. Contained Database Authentication. AlwaysOn. Reliable Secondaries. Multi-site Clustering. SQL Server Express LocalDB. Windows Server Core Support.

abra
Download Presentation

What’s New in Microsoft SQL Server 2012 for Administrators & Developers

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 inMicrosoft SQL Server 2012for Administrators & Developers Bryan Smithbrysmi@microsoft.com

  2. Online Operation Enhancements Contained Database Authentication AlwaysOn Reliable Secondaries Multi-site Clustering SQL Server Express LocalDB Windows Server Core Support Statistical Semantic Search PHP & Java Connectivity FileTable Unstructured Data Performance ColumnStore Index Extended Events Enhancements AlwaysOn Connection Director Multiple Secondaries PowerShell 2.0 Support Reporting Alerts Database Recovery Advisor SQL Server Data Tools PowerPivot Enhancements Resource Governor Enhancements T-SQL Debugger Enhancements Audit Filtering Power View Full Globe Spatial Flexible Failover Policy Unstructured Data Performance CDC Support for SSIS BI Semantic Model Audit Resilience Distributed Replay New SSIS Design Surface User-defined Audit SSMS to Windows Azure Platform Data Quality Services FTS Support for Czech and Greek 15k Partitions Master Data Management Excel Add-in Full-Text Search Performance HA for StreamInsight Availability Groups SSIS Troubleshooting Ad Hoc Reporting Default Scheme for Windows Groups Spatial 2D Support ODBC Driver for Linux SharePoint Active Directory Support SSIS Package Management SQL Audit for All Editions T-SQL Enhancements

  3. Mission Critical Confidence Breakthrough Insight CLOUD ON YOUR TERMS Enable mission critical performance & availability at an appropriate TCO Unlock new insights with pervasive data discovery across the organization Create business solutions fast, on your terms across server & private or public cloud Required 9s & protection Blazing-fast performance Organizational compliance Peace of mind Rapid data discovery Managed self-service BI Credible, consistent data Complete DW solutions Scale on demand Fast time to market Extend any data, anywhere Optimized productivity

  4. Database Engine Analysis Services Reporting Services Integration Services Master Data Services Data Quality Services StreamInsight

  5. Survey Developers Administrators

  6. AlwaysOn: Objectives • Increased availability • Improved hardware utilization • Simplified administration

  7. AlwaysOn: Availability Instance Availability Database Availability

  8. AlwaysOn: Instance Availability • Enhances Windows Failover Clustering with: • Enhanced diagnostics • Flexible failover policies • Easier multi-site clustering

  9. AlwaysOn: Health & Diagnostics • Five Resource Types: • System • Resources • Query Processing • IO Subsystem • Events

  10. AlwaysOn: Failover Policies

  11. AlwaysOn: Multi-Site Clustering • Multi-site clustering supported since 2005 • VLAN required between sites • Storage-level replication separate from SQL Server • Multi-subnet support with 2012 • Storage-level replication still separate from SQL Server DNS Site A Site B

  12. AlwaysOn: Database Availability • Enhances database mirroring with: • Multiple Secondaries • Availability Groups • Virtual Network Name • Readable Secondaries

  13. AlwaysOn: Availability Groups • Multiple user databases in replicated set • Failover together within topology

  14. AlwaysOn: Secondaries • 1 primary + up to 4 secondaries • 2 max synchronous secondaries • Provide failover & automatic page correction • 1 secondary in automatic failover pair

  15. AlwaysOn: Secondaries

  16. AlwaysOn: Automated Failover

  17. AlwaysOn: Virtual Network Name • Virtual Network Name (VNN) established with Windows Failover Cluster Services (WFCS) • Availability Group Listener interacts with WFCS to identify primary Availability Group • Applications connect to VNN or individual server names Virtual Network Name

  18. AlwaysOn: Readability • All secondaries support backups • 2 secondaries can be designated readable • Availability Group Listener can route read-only workloads to readable secondaries • Routing list prioritizes secondaries • Application must specify ReadOnly intent Virtual Network Name

  19. AlwaysOn: Administration

  20. Windows Core Server Support • Supports: • Database Engine • Analysis Services • Integration Services

  21. Security: Contained Databases Instance Instance Login User User/Login • Useful in scenarios when need to move a database: • AlwaysOn • Application Development • Azure

  22. Security: Contained Databases • Supports Windows & SQL authentication • Windows-only recommended for authentication security & DoS concerns • Must be enabled at instance-level and implemented for specific databases • Access to instance-level resources permitted • Database becomes partially contained • Partial containment monitored through sys.dm_db_uncontained_entities& cdb_uncontained_usage event

  23. Security: Contained Databases • Increased potential attack surface • db_owner, db_securityadmin, or users with ALTER ANY USER permissions can grant access without knowledge of instance admins • Drives security administration to multiple levels • DB-level logins can access other databases through guest or corresponding logins or user-accounts

  24. Security • BUILTIN\Admins & LocalSystem • Managed service accounts & virtual accounts • Custom server roles

  25. Security: Audit Improvements • Server-level audit available in every edition • Database-level audit in Enterprise & Developer only • Fail operation options for log failures • More information in audit logs • Audits can be defined with filters • sp_audit_writeproc for custom audit

  26. Resource Governor Enhancements • Support for 64 resource pools • Hard caps for CPU usage • CPU does not wait for contention • Resource pools can be assigned affinity for specific schedulers or NUMA nodes

  27. Performance Improvements • Full-text search • Fully integrated with database engine • Improved query optimization & parallelism • Versioning to improve concurrent reads & writes • Spatial indexes • Numerous improvements to indexes • Helper procs for easier index tuning • Support for row and page-level compression • Query plan optimizations

  28. Performance Improvements • Expanded online operations • Add a non-nullable column with a default • Index rebuilds support BLOBs • Support for 15k partitions • Back ported to SQL Server 2008 & 2008 R2 • Columnstore indexes!

  29. Columnar Storage 1 2 3 4 5 6 7 8 Header 1 2 3 4 5 6 7 8 1 2 3 4 5 6 7 8 1 2 3 4 5 1 2 3 4 5 6 7 8 6 7 8 1 2 3 4 5 6 7 8 1 2 3 4 5 1 2 3 4 5 6 7 8 6 7 8 1 2 3 4 5 6 7 8 1 2 3 4 5 1 2 3 4 5 6 7 8 6 7 8 1 2 3 4 5 6 7 8 1 2 3 4 5 1 2 3 4 5 6 7 8 6 7 8 1 2 3 4 5 6 7 8 1 2 3 4 5 6 7 8 Offset Array 2 3 4 5 6 7 8 1

  30. Columnstore Index Execution Plan With Columnstore Index Without Columnstore Index

  31. Columnstore Index Scan • Scans required index columns • Optimized for batch retrieval • Retrieves ~1,000 rows per batch • Batches proceed to subsequent filtering

  32. Batch Hash Table Build& Hash Match & • Multiple filtering values organized as hash table • Hash table applied to index scan batches

  33. Memory Consumption With Columnstore Index Without Columnstore Index

  34. New T-SQL Functions • String Conversion • Date & Time • Logical • Analytical

  35. String Conversion Functions • PARSE • FORMAT • CONCAT • TRY_CONVERT • TRY_PARSE

  36. Date & Time Functions • DATEFROMPARTS • DATETIME2FROMPARTS • DATETIMEFROMPARTS • DATETIMEOFFSETFROMPARTS • SMALLDATETIMEFROMPARTS • TIMEFROMPARTS • EOMONTH

  37. Logical Functions • CHOOSE • IIF

  38. Analytical Functions • CUME_DIST • PERCENT_RANK • PERCENTILE_CONT • PERCENTILE_DISC • FIRST_VALUE • LAST_VALUE • LEAD • LAG

  39. Programmability Enhancements • Pagination in ORDER BY clause

  40. Programmability Enhancements • Windowed operations in OVER clause • Enable moving averages, running totals, etc.

  41. Programmability Enhancements • THROW statement • FORCESEEK & FORCESCAN table hints • Back ported to SQL Server 2008 R2

  42. Sequence Objects • Defined using: • TinyInt: 0 to 255 • SmallInt: -32,768 to 32,767 • Int: -2,147,483,648 to 2,147,483,647 • BigInt: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 • Decimal: - 99,999,999,999,999,999,999,999,999,999,999,999,999to 99,999,999,999,999,999,999,999,999,999,999,999,999 using decimal(38,0) • Bound by start, increment, min & max values • Cycle options for when range is exceeded • Created outside scope of current transaction • Values consumed upon request • Caching available for performance • Cache lost with system failure • Default cache of 50 • Retrieved using NEXT VALUE FOR or sp_sequence_get_range

  43. FileTable Objects

  44. Semantic Search

  45. SQL Server Data Tools

  46. SQL Server Data Tools

  47. Data Providers .NET(ADO.NET) Native(ODBC) PHP(SQLPHP) Java(JDBC) Native(ODBC) Java(JDBC) Windows/Windows Azure Non-Windows SQL Server/SQL Azure

  48. Distributed Replay Utility • Employs standard replay trace from 2005+ • Up to 16 replay clients + 1 controller • Executes in synchronization or stress mode

  49. Upgrade Advisor

  50. System Center Advisor

More Related