Sql server 2014 features william emmert microsoft
This presentation is the property of its rightful owner.
Sponsored Links
1 / 28

SQL Server 2014 Features William Emmert Microsoft PowerPoint PPT Presentation


  • 58 Views
  • Uploaded on
  • Presentation posted in: General

SQL Server 2014 Features William Emmert Microsoft . SQL Server 2014 and the Data Platform. Faster Insights from Any Data. Mission Critical Performance. Platform for Hybrid Cloud. SQL Server 2014 and the Data Platform. Faster Insights from Any Data. Platform for Hybrid Cloud.

Download Presentation

SQL Server 2014 Features William Emmert Microsoft

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql server 2014 features william emmert microsoft

SQL Server 2014 FeaturesWilliam EmmertMicrosoft


Sql server 2014 and the data platform

SQL Server 2014 and the Data Platform

Faster Insights from Any Data

Mission Critical Performance

Platform for Hybrid Cloud


Sql server 2014 and the data platform1

SQL Server 2014 and the Data Platform

Faster Insights from Any Data

Platform for Hybrid Cloud

Mission Critical Performance


Mission critical performance

Mission Critical Performance

  • In-Memory OLTP

  • ColumnStore Enhancements

  • Security Enhancements

  • Query Processing Enhancements

  • Resource Governor Enhancements

  • SSD Buffer Pool Extension & Scale-Up

  • High Availability (AlwaysOn) Enhancements

  • On-Line Operations Enhancements


In memory oltp

In-Memory OLTP

  • Optimized for in-memory data up to 20–30 times throughput

    • Indexes (hash and range) exist only in memory; no buffer pool, B-trees

    • T-SQL compiled to machine code via C code generator and Visual C compiler

    • Core engine uses lock-free algorithms; no lock manager, latches, or spinlocks

  • Multiversion optimistic concurrency control with full ACID support

  • On-ramp existing applications

  • Integrated experience with same manageability, administration, and development experience


Columnstore enhancements

Columnstore Enhancements

  • Real-time super-fast data warehouse engine

    • Ability to continue queries while updating without the need to drop and re-create index or partition switching

  • Huge disk space saving due to compression

    • Ability to compress data 5X – 15X using archival per-partition compression

  • Better performance and more efficient (less memory) batch query processing using batch mode rather than row mode


Separation of duties enhancement

Separation of Duties Enhancement

  • Greater role separation

    • Ability to create new roles for database administrators who are not sysadmin (super-user)

    • Ability to create new roles for users or apps with specific purposes

  • Examples:

  • Database administrators but cannot see user data

  • Database administrators but cannot modify logins

  • Database administrators but cannot impersonate any logins

  • New roles (e.g. auditors) to read all data but not database administrators

  • New roles to read all metadata for monitoring purposes (e.g. SCOM) but cannot see user data

  • New roles for middle tier app to impersonate any login except sa, sysadmin, or other high privilege logins


Query processing enhancements

Query Processing Enhancements

  • Better query performance:

    • Better choice of query plans

    • Faster and more frequent statistics refresh on partition level

  • Consistent query performance

  • Better supportability using two steps (decision making & execution) to enable better query plan troubleshooting

  • Loading speed into table improved significantly using parallel operation


Io resource governance

IO Resource Governance

  • Better isolation (CPU, memory, and IO) for multi-tenant workloads

  • Guarantee performance in private cloud and hosters scenario


Ssd buffer pool extension and scale up

SSD Buffer Pool Extension and Scale up

  • BP Extension for SSDs

    • Improve OLTP query performance with no application changes

    • No risk of data loss (using clean pages only)

    • Easy configuration optimized for OLTP workloads on commodity servers (32GB RAM)

  • Scalability improvements for systems with >8 sockets

Example:

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON

(FILENAME = 'F:\SSDCACHE\EXAMPLE.BPE‘, SIZE = 50 GB)


High availability alwayson

High Availability (AlwaysOn)

  • Further scale-out read workloads across, possibly geo-distributed, replicas

  • Use readable secondaries despite network failures (important in geo-distributed environments)

  • Improve SAN storage utilization

    • Avoid drive letter limitation (max 24 drives) via CSV paths

    • Increase resiliency of storage failover

  • Ease troubleshooting


Online ops enhancements

Online Ops Enhancements

  • New partition-level granularity for online index rebuild allows continuous access to the table resource during this operation to better meet the needs of customers who need 24/7 access

  • Provide customers greater control over impacting running transactions if using switch partition or online index rebuild commands

  • Combined together, these features allow customers to better schedule resource intensive or blocking operations and better achieve 24/7 availability for their applications


Sql server 2014 and the data platform2

SQL Server 2014 and the Data Platform

Mission Critical Performance

Faster Insights from Any Data

Platform for Hybrid Cloud


Power view for multidimensional models

Ship in CU4 of SQL Server 2012 SP1

Power View for Multidimensional Models

  • Power View on Analysis Services via BI Semantic Model

  • Native support for DAX in Analysis Services

  • Better flexibility: Choice of DAX on Tabular or Multidimensional (cubes)


Sql server 2014 and the data platform3

SQL Server 2014 and the Data Platform

Mission Critical Performance

Faster Insights from Any Data

Platform for Hybrid Cloud


Platform for hybrid cloud

Platform For Hybrid Cloud

  • Backup to Azure

  • Smart Backup

  • AlwaysOn Replica in Azure

  • SQL Server with Azure Storage Integration

  • SQL Server with Azure IaaS


Sql server journey to the cloud

SQL Server Journey to the Cloud

AlwaysOn Replica in Azure

SQL Server with Azure Storage integration

Smart Backup

SQL Server with Azure IaaS

Backup to Azure


Backup to azure manual

Backup to Azure - Manual

  • Leverage Windows Azure blob storage

    • Flexible, reliable, and limitless off-site storage

    • Backup Archive

    • No overhead of hardware management

    • Cost Benefits


Backup to azure automatic

Backup to Azure - Automatic

  • Large scale management and no need to manage backup policy

    • Minimal knobs – control retention period

    • Context-aware – e.g. workload/throttling

    • Manage whole instance, or particular DBs

  • Leverage Backup to Azure

    • Inherently off-site

    • Geo-redundant

    • Minimal storage costs

    • Zero hardware management

Example:

EXEC smart_admin.sp_set_db_backup

@database_name='TestDB',

@storage_url=<storage url>,

@retention_days=30,

@credential_name='MyCredential',

@enable_backup=1


Alwayson replica in azure

AlwaysOn Replica in Azure

  • Easily deploy one or more replicas of your databases to Windows Azure

    • No need for a DR site (hardware, rent, ops)

  • Use these replicas for

    • Disaster Recovery

    • Workloads (reads/backups)


Sql server with azure storage integration

SQL Server with Azure Storage Integration

  • Delivers an incremental path to database in the cloud. Can move one database at a time, without application changes

  • Save storage cost: On-premises computing, with bottomless off-site storage in Azure

  • Increase HA and DR:

    • A set of database files stored in Azure storage – backed by Azure Storage SLA

    • Fast disaster recovery using database attach operation without the need to restore data

  • Maintain on-prem control of security: Allow TDE key on-prem while encrypted data in Azure Storage


Deploy db to windows azure vm

Deploy DB to Windows Azure VM

  • Easy to use

    • Perfect for DBAs new to Azure and for ad hoc scenarios

  • Complexity hidden

    • Detailed Azure knowledge is not needed

    • Almost no overhead: the defining factor for time to transfer is dB size


Sysprep enhancements

Shipped in SQL Server 2012 SP1 CU2

Sysprep enhancements

  • Sysprep support for

  • Analysis Services

  • Integration Services

  • Management Tools (SSMS)

  • Other Shared Features

  • Performance Improvements


Sysprep enhancements in sql server 2014

SysprepEnhancements in SQL Server 2014

  • What’s being delivered

    • Extensions to SQL SysPrep functionality in order to support image based deployment of clustered SQL Server instances

  • Main benefit

    • Supports full automation of SQL Failover Cluster deployment scenarios

    • Reduces deployment times for SQL Failover Clusters

    • Combined together, these features allow customers to automate the provisioning of SQL Failover Clusters both on-premises and through Infrastructure as a Service (IaaS)

    • Built on top of SQL Server 2012 SP1 CU2 Sysprep enhancements


Refs support

ReFS support

  • What’s being delivered

    • SQL Server 2014 support for Windows Server Resilient File System (ReFS)

  • Main benefits

    • Greater protection on data corruption

      • Efficient checksum on all user data (including Hekaton and FILESTREAM)

      • Checksum runs proactively in the background

      • Protects against disk failure

      • Backward compatible with NTFS

      • Works with commodity, direct-attached disks


Better together features

Better Together Features

  • Works very well with Windows Server 2012 R2, System Center 2012 R2, and Windows Azure refresh

    • ReFS support

    • Online resize VHDX

    • Storage Tiering

    • SMB improvement

    • Hyper-V replica improvement

    • Faster live migration


Complete consistent data platform

Complete & Consistent Data Platform

SQL Server 2014

Mission Critical Performance

Faster Insights from Any Data

Platform for Hybrid Cloud

CUSTOMER

SERVICE PROVIDER

WINDOWS AZURE

1

CONSISTENTPLATFORM

DEVELOPMENT

MANAGEMENT

DATA

IDENTITY

VIRTUALIZATION


  • Login