Transcript What’s New in Microsoft SQL Server 2012 for Administrators

What’s New in
Microsoft SQL Server 2012
for Administrators
Bryan Smith
[email protected]
& Developers
Online Operation Enhancements
Multi-site Clustering
SQL Server Express LocalDB
AlwaysOn
Contained Database Authentication
Reliable Secondaries
Windows Server Core Support
PHP & Java Connectivity
ColumnStore Index
PowerShell 2.0 Support
FileTable
Multiple Secondaries
Database Recovery Advisor
Full Globe Spatial
Flexible Failover Policy
BI Semantic Model
User-defined Audit
Data Quality Services
PowerPivot Enhancements
Resource Governor Enhancements
Power View
CDC Support for SSIS
Distributed Replay
SSMS to Windows Azure Platform
FTS Support for Czech and Greek
Master Data Management Excel Add-in
Full-Text Search Performance
SSIS Troubleshooting
Ad Hoc Reporting
Reporting Alerts
Unstructured Data Performance
Audit Resilience
New SSIS Design Surface
Unstructured Data Performance
Extended Events Enhancements
AlwaysOn Connection Director
SQL Server Data
Tools
T-SQL Debugger Enhancements
Audit Filtering
Statistical Semantic Search
15k Partitions
HA for StreamInsight
Availability Groups
Default Scheme for Windows Groups
Spatial 2D Support
ODBC Driver for Linux
SSIS Package Management
T-SQL Enhancements
SharePoint Active Directory Support
SQL Audit for All Editions
MISSION CRITICAL
CONFIDENCE
BREAKTHROUGH
INSIGHT
CLOUD ON
YOUR TERMS
Required 9s & protection
Rapid data discovery
Scale on demand
Blazing-fast performance
Managed self-service BI
Fast time to market
Organizational compliance
Credible, consistent data
Extend any data, anywhere
Peace of mind
Complete DW solutions
Optimized productivity
Database Engine
Analysis Services
Reporting Services
Integration Services
Master Data Services
Data Quality Services
StreamInsight
Survey
Developers
Administrators
AlwaysOn: Objectives
• Increased availability
• Improved hardware utilization
• Simplified administration
AlwaysOn: Availability
Instance Availability
Database Availability
AlwaysOn: Instance Availability
• Enhances Windows Failover Clustering with:
– Enhanced diagnostics
– Flexible failover policies
– Easier multi-site clustering
AlwaysOn: Health & Diagnostics
Five Resource Types:
•
•
•
•
•
System
Resources
Query Processing
IO Subsystem
Events
AlwaysOn: Failover Policies
Condition
Windows Service Down
Cannot Obtain Health
System Reports Error
Resource Reports Error
Query Processing Reports Error
Policy Number
0
1
2
3
4
5
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
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
AlwaysOn: Database Availability
• Enhances database mirroring with:
–
–
–
–
Multiple Secondaries
Availability Groups
Virtual Network Name
Readable Secondaries
AlwaysOn: Availability Groups
• Multiple user databases in replicated set
• Failover together within topology
AlwaysOn: Secondaries
• 1 primary + up to 4 secondaries
– 2 max synchronous secondaries
• Provide failover & automatic page correction
• 1 secondary in automatic failover pair
AlwaysOn: Secondaries
AlwaysOn: Automated Failover
Policy Number
Condition
1
2
3
4
5
Windows Service Down OR
AG Lease Expired
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Replica in Failed State
System Reports Error
Resource Reports Error
Query Processing Report Error
•
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 Virtual Network Name
• Applications connect to VNN or
individual server names
AlwaysOn: Readability
• All secondaries support backups
• 2 secondaries can be designated readable
• Availability Group Listener can route read-only
workloads to readable secondaries Virtual Network Name
– Routing list prioritizes secondaries
– Application must specify
ReadOnly intent
AlwaysOn: Administration
Windows Core Server Support
• Supports:
– Database Engine
– Analysis Services
– Integration Services
Security: Contained Databases
Instance
Instance
Login
User
User/Login
Useful in scenarios when need to move a database:
• AlwaysOn
• Application Development
• Azure
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
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
Security
• BUILTIN\Admins & LocalSystem
• Managed service accounts & virtual accounts
• Custom server roles
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_write proc for custom audit
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
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
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!
Columnar Storage
1
2
Header
1
2
23
1 1 2
61
72
23
1 1 2
61
72
23
1 1 2
61
72
1
4
4
5 4
4
5
5
5
5
6
6
6
6
7
7
7
7
8
8
8
8
43
38
43
38
43
38
5 4
4
5 4
4
5
5
5
5
6
6
6
6
7
7
7
7
8
8
8
8
5 4
4
5
5
6
6
7
7
8
8
3
4
5
6
7
8
1 Offset2 Array 3
2
3
1
4
4
5
5
6
6
7
7
8
8
1 2
61
1
23
72
2
3
3
43
38
Columnstore Index Execution Plan
With Columnstore Index
Without Columnstore Index
Columnstore Index Scan
• Scans required index columns
• Optimized for batch retrieval
– Retrieves ~1,000 rows per batch
– Batches proceed to subsequent filtering
&
Batch Hash Table Build
& Hash Match
• Multiple filtering values organized as hash table
• Hash table applied to index scan batches
Memory Consumption
With Columnstore Index
Without Columnstore Index
New T-SQL Functions
•
•
•
•
String Conversion
Date & Time
Logical
Analytical
String Conversion Functions
• PARSE
• FORMAT
• CONCAT
• TRY_CONVERT
• TRY_PARSE
Date & Time Functions
•
•
•
•
DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
• SMALLDATETIMEFROMPARTS
• TIMEFROMPARTS
• EOMONTH
Logical Functions
• CHOOSE
• IIF
Analytical Functions
•
•
•
•
CUME_DIST
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
•
•
•
•
FIRST_VALUE
LAST_VALUE
LEAD
LAG
Programmability Enhancements
• Pagination in ORDER BY clause
Programmability Enhancements
• Windowed operations in OVER clause
– Enable moving averages, running totals, etc.
Programmability Enhancements
• THROW statement
• FORCESEEK & FORCESCAN table hints
– Back ported to SQL Server 2008 R2
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,999 to
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
FileTable Objects
Semantic Search
SQL Server Data Tools
SQL Server Data Tools
Data Providers
.NET
(ADO.NET)
Native
(ODBC)
PHP
(SQLPHP)
Windows/Windows Azure
Java
(JDBC)
Native
(ODBC)
Java
(JDBC)
Non-Windows
Distributed Replay Utility
• Employs standard replay trace from 2005+
• Up to 16 replay clients + 1 controller
• Executes in synchronization or stress mode
Upgrade Advisor
System Center Advisor
SQL Server Appliances
• Workload optimized solutions
– MPP & SMP Data Warehouse
– All-in-One Business Intelligence
– Database Consolidation/Private Cloud
– High-Performance OLTP
• Fastest time to deployment
• End-to-end support
Database Engine
Analysis Services
Reporting Services
Integration Services
Master Data Services
Data Quality Services
StreamInsight
StreamInsight
Event Sources
Event Targets
Application
MSFT CEP
Engine
Event stores
& Databases
Stock tickers &
News feeds
Output Adapters
Input Adapters
Devices, Sensors
Pagers &
Monitoring devices
KPI Dashboards,
SharePoint UI
Microsoft SQL Server 2012 for
embedded systems
Reference data
Trading stations
Database Engine
Analysis Services
Reporting Services
Integration Services
Master Data Services
Data Quality Services
StreamInsight
Enterprise Information Management
DECISIONS
& ACTION
Workflow
Embedded
Collaboration
Real-time
PREDICTIVE
MODELING
EXPLORATION
SHARING
INFORMATION MANAGEMENT
DISCOVER
TRANSFORM
SHARE
RECOMMEND
CLEAN
GOVERN
DATA MANAGEMENT SUBSTRATE
FEDERATED STORAGE & QUERY
1
011
01
RELATIONAL
NON RELATIONAL
MULTIDIMENSIONAL
STREAMING
External Data and
Services
AD-HOC
INSIGHT
Mobile
MARKETPLACE
OPERATIONAL
Social
Data Quality Services
Master Data Services
Integration Services
Project Codename Barcelona
Database Engine
Analysis Services
Reporting Services
Integration Services
Master Data Services
Data Quality Services
StreamInsight
Online Operation Enhancements
Multi-site Clustering
SQL Server Express LocalDB
AlwaysOn
Contained Database Authentication
Reliable Secondaries
Windows Server Core Support
PHP & Java Connectivity
ColumnStore Index
PowerShell 2.0 Support
FileTable
Multiple Secondaries
Database Recovery Advisor
Full Globe Spatial
Flexible Failover Policy
BI Semantic Model
User-defined Audit
Data Quality Services
PowerPivot Enhancements
Resource Governor Enhancements
Power View
CDC Support for SSIS
Distributed Replay
SSMS to Windows Azure Platform
FTS Support for Czech and Greek
Master Data Management Excel Add-in
Full-Text Search Performance
SSIS Troubleshooting
Ad Hoc Reporting
Reporting Alerts
Unstructured Data Performance
Audit Resilience
New SSIS Design Surface
Unstructured Data Performance
Extended Events Enhancements
AlwaysOn Connection Director
SQL Server Data
Tools
T-SQL Debugger Enhancements
Audit Filtering
Statistical Semantic Search
15k Partitions
HA for StreamInsight
Availability Groups
Default Scheme for Windows Groups
Spatial 2D Support
ODBC Driver for Linux
SSIS Package Management
T-SQL Enhancements
SharePoint Active Directory Support
SQL Audit for All Editions
Upgrade Resources
• What’s New?
• What’s Deprecated, Discontinued,
Broken, or Changed?
• Upgrade Advisor
Certifications
• Microsoft Certified Technical Specialist (MCTS)
• Microsoft Certified IT Professional (MCITP)
– Data Platform
•
•
•
•
•
70-465: Designing Database Solutions with Microsoft SQL Server 2012
70-464: Developing Microsoft SQL Server 2012 Databases
70-463: Implementing Data Warehouses with Microsoft SQL Server 2012
70-462: Administering a Microsoft SQL Server 2012 Database
70-461: Querying Microsoft SQL Server
•
•
•
•
•
70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012
70-466: Implementing Data Models & Reports with Microsoft SQL Server 2012
70-463: Implementing Data Warehouses with Microsoft SQL Server 2012
40-462: Administering a Microsoft SQL Server 2012 Database
70-461: Querying Microsoft SQL Server
– Business Intelligence
• Microsoft Certified Master
– Data Platform
– Business Intelligence
Community Events
•
•
•
•
•
SQL Saturday #107 – Houston, TX
PASS SQL Rally – Dallas, TX
Microsoft TechEd – Orlando, FL
SQL Saturday #125 – Oklahoma City, OK
PASS Summit – Seattle, WA
SQL Rally