Transcript SSIS Changes in Denali

SQL Server 2014:
Overview
Phil Brammer
@PhilBrammer
ssistalk.com
Phil Brammer
• Over 13 years’ experience in database
technologies, data warehousing, ETL, on-call…
• Worked with Teradata at PayPal
• Operationally manages multi-terabyte instances
• Dabbles a bit in SSIS – ssistalk.com
• Microsoft MVP, SQL Server – 7th year
Breakthrough Performance & Scale
•
•
•
•
•
•
In-Memory OLTP (Hekaton)
Columnstore Improvements
Query Performance Improvements
Resource Governor for I/O
Buffer Pool Extension
Scale Optimizations for > 8 socket systems
Enhanced High Availability
• AlwaysOn Enhancements
• Online Database Operations
Hybrid Scenarios
•
•
•
•
Smart Backup
AlwaysOn Replica in Windows Azure VM
SQL Server with Azure Storage Integration
On-Prem to Azure Migration
Other
• Separation of Duties
• Extending PowerView
• Windows Server Enhancements
BP & S: In-Memory OLTP
•
•
•
•
•
•
Up to 20-30x throughput
Easy to migrate existing applications
Integrated experience
Full ACID support
All in-memory. No buffer pool, B-Trees
No lock manager, latches, or spinlocks
BP & S: Columnstore
• New clustered option
• Completely read/writeable
• New archive option for even more compression
BP & S: Query Performance Improvements
• New cardinality estimator
• Incremental statistics per partition
BP & S: Resource Governor
•
•
•
•
Limit
Limit
Limit
Limit
I/O to a Resource Pool
CPU usage; percent, affinity
DOP
Memory
BP & S: Buffer Pool Extension
• Ability to extend buffer cache to any volume
(SSD)
• Improve OLTP performance
• No risk of data loss (clean pages)
• Easy to implement
HA: AlwaysOn Enhancements
• Up to 8 readable secondaries
• Ability to use readable secondaries during
network failures
• Cluster Shared Volume support
• Easier troubleshooting
• Wizard to add replica to Azure
HA: Online Operations
• Single partition online index rebuild
• Manage lock priority for SWITCH and online index
rebuild operations
Hybrid: Smart Backup
•
•
•
•
•
•
•
Input storage URL (Azure), retention
Automatically backs up databases
Automatically cleans up
Context aware
Minimal storage costs
Zero hardware management
No media decay
Hybrid: Backup to Azure Storage
•
•
•
•
•
•
Near-bottomless storage
Offsite, geo-redundant
No provisioning
No device management
No media decay
Remote accessibility
Hybrid: AlwaysOn Replica in Azure
• Wizard to add a replica in Windows Azure VMs
• Eliminates DR site needs (hardware, rent, etc…)
• Offload read/backup workloads
Hybrid: SQL & Azure Storage
Integration
• Native support for SQL Server database files
stored as Azure blobs
• Can move a single database at a time
• Save storage costs
• Backed by Azure Storage SLAs
• Fast disaster recovery using database attach
• Control security with on-prem model
Hybrid: Migrations to Azure VMs
•
•
•
•
New Wizard to migrate databases to Azure VM
Perfect for DBAs new to Azure
No need of specific Azure knowledge
Time to transfer DB is limiting factor
Other: Separation Of Duties
•
•
•
•
CONNECT ANY DATABASE
IMPERSONATE ANY LOGIN
SELECT ALL USER SECURABLES
Allows for greater role separations – audit vs
admin
Other: Power View for Multidimensional Models
• Allows native support for DAX in Analysis
Services
• via BI Semantic Model
Other: Windows Server
Enhancements
•
•
•
•
•
•
ReFS Support
Online resize VHDX
Storage Tiering
SMB improvement
Hyper-V replica improvement
Faster live migrations
End
•
•
•
•
Thank you for attending!
Phil Brammer
@PhilBrammer
http://www.ssistalk.com