sql server 2012 technical deep dive summary
DESCRIPTION
SQL Server 2012 Technical Deep Dive SummaryTRANSCRIPT
Jen Underwood
Data Platform Technology Specialist
Microsoft Corporation
2
High
Availability
SQL Server AlwaysOn
Security &
Manageability
User-Defined Server Roles
Web &
Breadth
PHP Driver
Scalability &
Performance
Database Replay Application Centric Failover
Contained Database Authentication
Local DB Runtime
Fast FILESTEAM UTF-16
Readable Secondaries
Fast Full-Text Search
Reliable, Integrated Failover Detection
Multiple Secondaries
Audit Enhancements
Paging for result sets
Column Store Index, “Apollo”
HA for StreamInsight
Management Pack for High Availability
Backup Secondaries
JDBC 4.0 driver
Support for ARM processors
Active Directory w/ SharePoint for SSRS
Default Schema for Windows Group
Windows Server Core Support
Beyond
Relational
FileTable
Microsoft Project “Juneau”
Online Operations
Win32 Access to Database files
Semantic Search Platform
Full Globe Spatial Support
DAC Enhancements
ODBC for Linux
Fast Spatial Performance
Hybrid Applications with SQL Azure
Up to 15K Partitions/Table
EIM
SSIS Server
Business
Intelligence
Unified Semantic Model
Crescent Data Quality
Alerting
Enhanced MDS In-memory BI for corporate
Sysprep for AS
PowerPivot Enhancements
Reporting as SharePoint Shared Service
DBC & OLTP Appliances/RAs
MDS add-in for Excel
http://www.microsoft.com/download/en/details.aspx?id=27069
•
•
•
•
•
•
•
•
•
•
•
Disaster Recovery
Shared Storage
A
Non-Shared Storage
A
A
A
A A A
Synchronous
Data Movement
Asynchcronous
Data
Movement
A
A
Direct Attached Storage local, regional and geo
secondaries
A A
Shared Storage, regional and geo secondaries
A
A
A
Multisite Clustering
Flexible Failover Policy
Improved Diagnostics
Multi-Database Failover
Multiple Secondaries
Active Secondaries
Integrated HA Management
AlwaysOn Availability Groups
for Database level protection
AlwaysOn Failover Cluster Instance
for Instance level protection
Availability Groups for High Availability and Disaster Recovery
• Multi-database failover
• Multiple secondaries
• Total of 4 secondaries
• 2 synchronous secondaries
• 1 automatic failover pair
• Synchronous and
asynchronous
• data movement
• Built in compression and
encryption
• Automatic and manual
failover
• Flexible failover policy
• Automatic Page Repair
• Application failover using
virtual name
• Configuration Wizard
• Dashboard
• System Center
Integration
• Rich diagnostic
infrastructure
• File-stream replication
• Replication publisher
failover
Flexible Integrated Efficient
• Active Secondary
• Readable Secondary
• Backup from
Secondary
• Automation using
power-shell
AG_HR
HR DB
HR
DB
Primary Secondary
HR_VNN
-server HR_VNN;-catalog HRDB
Application retry during failover
Connect to new primary once
failover is complete
and the virtual name is online
Primary Secondary Secondary
HR
DB
ServerA ServerB ServerC
Log Shipping
Database Mirroring
KB 976097
KB 2494036
http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx
KB 2494036
blog with details
A
A
A
Primary Data Center
Log
Shipping
Disaster Recovery Data Center
Principal Mirror
Witness
Synchronous
Database
Mirroring
Log Shipping Secondary
Note: More secondaries (total up to 4) can be added for additional resiliency or read scaleout
Replace Database Mirroring and Log Shipping with Availability Group
Primary Data Center
Synchronous / Asynchronous
Disaster Recovery Data
Center
Primary Secondary
Fileshare Witness
Synchronous
Secondary
Windows Server Failover Cluster
Availability Group
Failover Clusters for High Availability and Disaster Recovery
2) Storage
software detects
storage
replication state
3) Storage
software sets
storage to read
write enable
SRDF Replication
5) Replication reversed
Storage Replication
1) WSFC registers site failure
4) WSFC Mounts
Physical Disk Resources
Version Native
Support
Implementation
SQL Server 2008 R2 NO • Create stretch Virtual-LAN (VLAN) to act as a single subnet
SQL Server 2012 YES • IP address OR dependency support within SQL Server setup
• SQL Engine skips binding to IP’s not online on start-up
V-LAN
SAN Replication
IP: 10.10.10.10
subnet 1 subnet 2
Network Name: SqlClus
site 1 site 2
subnet 2 subnet 1
SAN Replication
IP1: 192.168.1.30 IP2: 192.168.2.30
Corpnet
OR
Network Name: SqlClus
site 1 site 2
The Microsoft Support Policy for Windows Server 2008 or Windows Server 2008 R2 Failover Clustershttp://support.microsoft.com/kb/943984
http://technet.microsoft.com/en-us/library/dd621586(WS.10).aspx
http://support.microsoft.com/kb/943984
Node and Disk Majority
• Recommended: Between 60 seconds (1 minute) and default value
• Check with DNS Admin.
http://msdn.microsoft.com/en-us/sqlserver/gg490638(en-us,MSDN.10)
Utilize Idle Hardware with Active Secondaries
DB2 DB1
SQLservr.exe SQLservr.exe
InstanceA
DB2 DB1
Primary Secondary
Database Log
Synchronization
InstanceB
Reports
Primary Replica Secondary Replica
Start Reporting workload on table T1
• Takes SCH-S lock on table T1
Execute DDL operation on table T1
REDO processes the DDL log record
• Requests SCH-M lock on table T1
Reporting Workload completes
REDO is unblocked Blocked
A
A
A
A
Reports
Backups
Synchronous
Data Movement
Asynchcronous
Data
Movement
Column Store Indexes Vector Mode Query Execution
•
•
•
•
•
•
•
•
•
•
•
•
•
Column Store Index 100x
43
Column Store stores data column-wise • Each page stores data from a single column
• Data not stored in sorted order
• Optimized for scans but can support all index
operations (in principle)
…
C1 C2 C3 C4
A B-tree index stores data
row-wise
select w_city, w_state, d_year,
SUM(cs_sales_price) as cs_sales_price
from warehouse, catalog_sales, date_dim
where w_warehouse_sk = cs_warehouse_sk
and cs_sold_date_sk = d_date_sk
and w_state = 'SD' and d_year = 2002
group by w_city, w_state, d_year
order by d_year, w_state, w_city;
Cold buffer pool Warm buffer pool
CPU Elapsed CPU Elapsed
Row store
only
259 20 206 3.1
Column store 19.8 0.8 16.3 0.3
Improvement 13X 25X 13X 10X
1TB TPC-DS database
Catalog_Sales 1.44B rows
Warehouse 20 rows
Date_dim
73,049 rows
Machine: 40/80 cores/threads,
256 GB, IO bandwidth
10GB/sec
45
Lis
t o
f q
ualify
ing
ro
ws
Column vectors
Batch object
46
Hash join
Row
mode
Vector mode
B1
B2
B3
Bn …
Hash table
(shared)
Thread1
Thread2
Build side Thread3
Thread2
Thread1
Probe
side
B1
B2
B4
Bm …
B3
Build
input
Hash join
Exchange Exchange
Probe input
Exchange Exchange
Expensive to repartition inputs
Data skew reduces parallelism
47
Beyond Relational
FileTable
• FileTable for backing up, restoring, searching, and updating Microsoft Windows® files
• Store unstructured files directly in the database
Full-text Semantic Search
• Provide deeper insight to unstructured data with semantic concept extraction and similarity search; extend full text search to enable search on the meaning of documents
Spatial data enhancements
• Leverage spatial data in new ways through advanced and even faster 2D spatial support
1001110101100101001
1100101001010010
1001110101100101001
Semantic Key Phrases
Key Title Document
D1 Annual Budget …
D2 Corporate
Earnings
…
D3 Marketing Reports …
… … …
-----------
-----------
-----------
-----------
-----------
-----------
----
-----------
-----------
-----------
-----------
-----------
-----------
----
-----------
-----------
-----------
-----------
-----------
-----------
----
Source Table
ID Keyword Colid … compDocid CompOc CompPid
K1 revenue 1 … 10,23,123 (1,4),(5,8),(1,34) 2,5,6,8,4,3
K2 growth 1 … 10,23,123 (1,5),(5,9),(1,34) 2,5,6,8,5,4
… … … … … …
Keyword Index (Full-Text)
Keyphrases KeyphraseDocuments
ID DocID
T1 (revenue) D1 (Annual Budget)
T2 (growth) D2 (Corporate Earnings)
T3 (Windows) D3 (Marketing Reports)
… …
T1 (revenue) D7 (Finance Report)
… …
T3 (Windows) D11 (Azure Strategy)
T4 (Azure) D11 (Azure Strategy)
ID Keyword
T1 revenue
T2 growth
T3 Windows
T4 Azure
… …
DocumentSimilarity
DocID MatchedDocID
D1 (Annual Budget) D2 (Corporate Earnings)
D1 (Annual Budget) D7 (Finance Report)
D3 (Marketing Reports) D11 (Azure Strategy)
… …
Full-Text and Semantic Processing
quarter, record, revenue…
2a
1
CREATE FULLTEXT INDEX ON
Production.Document (
Title LANGUAGE 1033,
Document
LANGUAGE 1033
TYPE COLUMN FileExtension
STATISTICAL_SEMANTICS
)
KEY INDEX PK_Document_DocumentID
ON documents_catalog
WITH CHANGE_TRACKING OFF, NO
POPULATION;
ALTER FULLTEXT INDEX ON Production.Document
ALTER COLUMN Document
ADD STATISTICAL_SEMANTICS
WITH NO POPULATION;
…
…
ALTER FULLTEXT INDEX ON Production.Document
START FULL POPULATION;
• New Hive ODBC driver – “Project Isotope”
to leverage Hadoop
• Bring Hadoop data into SSAS, SSRS,
PowerPivot using HiveQL
• Partnership with Hortonworks
• Future Windows and Azure Hadoop versions
• Develop simpler Hadoop APIs .NET and Javascript
• Contribute IP to the Open Source Community
HDFS
Map Reduce
Hive
AS Tabular AS Multidimensional
Crescent/Power View Excel/PowerPivot
PowerPivot
Analytical Apps
SQL Engine
PDW
SSRS
Miscellaneous
Security Enhancements
• User-defined server roles for easier separation of duties
• Improve compliance with audit enhancements (audit for all editions of SQL Server, improved audit resilience, user-defined audit event, and audit record filtering)
• Simplified security management with default schema for groups
• Database Authentication that uses self-contained access information without the need for server logins
• SharePoint and Active Directory security models for higher data security in end user reports
•
•
Distributed Replay
• Simplified application testing and upgrade
• Minimize errors in upgrades and proactively conduct capacity testing to simulate production environment and protect performance
•
•
•
•
•
•
•
Enterprise Information Management
Master Data Services
Data quality
Familiar tools
Performance
Data Quality Services
Profiling
Cleansing
Matching
Impact Analysis
Lineage tracking
with data sources
Integration Services
Easy data loading
MDS and DQS Integration
http://msdn.microsoft.com/en-us/sqlserver/hh323832.aspx
Map to DQS
Knowledge Base
Map Automated
Corrections
NEW SSIS DQS
Cleansing
Component
http://projectbarcelona.cloudapp.net
Visual and Drill-Down
Dependency Details
New Integration Services
Dashboard and Reports
Drill-Down reports for
troubleshooting issues New objects for troubleshooting:
• catalog.event_messages
• catalog.event_message_context
• catalog.executable_statistics
• catalog.execution_data_statistics
• dm_execution_performance_counters
New SSIS Management
System Views and Queries
Team BI PowerPivot for
SharePoint
Personal BI PowerPivot for
Excel
Corporate BI Analysis Services
BI Semantic Model
Data model
Business logic
and queries
Data access ROLAP MOLAP VertiPaq Direct
Query
MDX DAX
Multi-
dimensional Tabular
Third-party
applications
Reporting
Services Excel PowerPivot
Databases LOB Applications Files OData Feeds Cloud Services
SharePoint
Insights
New BISM project type
Import from Analysis
Services or PowerPivot
Visual Studio development
with integrated TFS
Source Control
New BISM Tabular Mode
Security Features
New BISM Tabular Mode
Partitioning Features
New Hierarchies
New Visual
Diagram View
New Date Type
Table for Time
Intelligence
New Measure Grid
New KPIs
Push User Defined Report
Data Level Alerts with
Defined Schedules
Define Alert
Rules
Manage User-
Defined Alerts
Sliders, Banding, Callout Template
visualizations
Export storyboards for story
telling with data
Charts, Grids, Scatter Plots and
Maps
Interactive charts highlight
selected data distributions in
charts
Easy Drag and Drop
Template Layouts
Animated charts play over
time periods
Basic and
Advanced Filtering
Visual Vertical and
Horizontal Multiples
Also Slicers and Tiles
for Visual Filtering
© 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista, and other product names are or may be registered trademarks and/or
trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation
as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part
of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation.
MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.