SQL Server 2014. Benefits of upgrading from SQL Server 2005, 2008, 2008R2 and 2012.


imageAre you running a previous version of SQL Server and want to know what new SQL Server capabilities you will be able to use by upgrading to the latest version?

Please find below the list of new features introduced since SQL Server 2005, 2008, 2008R2 and SQL Server 2012 (it will be a very long blog post!). 

 

If you're interested in attending an event to learn more about SQL Server 2014, we've created a consolidated view of upcoming events, including the Microsoft vision for IT Infrastructure.

.

 
Whats New in SQL Server 2014 since SQL Server 2012
PERFORMANCE & SCALE
o        In-Memory OLTP
o        Enhanced In-Memory ColumnStore for DW
o        Buffer Pool Extension to SSDs
o        Enhanced Query Processing
o        Resource Governor adds IO governance
o        SysPrep at cluster level
o        Predictable performance with tiering of compute, network, and storage with Windows Server 2012 R2
HIGH AVAILABILITY
o        Enhanced AlwaysOn, with 8 secondaries and Replica Wizard
o        Delayed Durability
o        Clustered Shared Volume support, VHDX support (Windows Server 2012 R2)
o        Manage on-premises and cloud apps (System Center 2012 R2)
SECURITY
o        Enhanced separation of duty
o        CC certification at High Assurance Level for 2014
o        Backup encryption support
PROGRAMMABILITY
o        Query optimization enhancements
EASY ACCESS TO DATA, BIG & SMALL
o        Power Query
o        Windows Azure HDInsight Service
o        Analytics Platform System (PDW V2)
POWERFUL INSIGHTS WITH FAMILIAR TOOLS
o        Power BI in Office 365
o        Power Map for Excel
o        Mobile interfaces for Power BI
 
HYBRID CLOUD SOLUTIONS
o        Simplified backup to Windows Azure
o        Support for backup of previous versions of SQL Server to Windows Azure
o        Cloud back-up encryption support
o        Simplified cloud Disaster Recovery with AlwaysOn replicas in Windows Azure VMs
EASY ON-RAMP TO THE CLOUD
o        New Windows Azure Deployment UI
for SQL Server
o        Larger SQL Server VMs and memory sizes now available in Windows Azure
 
Whats New in SQL Server 2014 since SQL Server 2008 R2
 

PERFORMANCE & SCALE

o        In-Memory OLTP

o        Enhanced In-Memory ColumnStore for DW

o        Support for 640 logical proc. & 4 TB memory

o        Support to 15,000 partitions

o        Resource Governor IO governance

o        Buffer Pool Extension to SSDs

o        Query optimization enhancements

o        SysPrep at cluster level

o        Predictable performance with tiering of compute, network, and storage with Windows Server 2012 R2
HIGH AVAILABILITY

o        SQL Server AlwaysOn

o        Delayed Durability

o        Recovery Advisor

o        Windows Server Core

o        Live Migration

o        Online Operations enhancements

o        Clustered Shared Volume support, VHDX support (Windows Server 2012 R2)

o        Manage on-premises and cloud apps (System Center 2012 R2)

SECURITY

o        User-Defined Server Roles

o        Default Schema for Groups

o        SQL Server Audit

o        SQL Server Fine-grained Auditing

o        Enhanced separation of duty

o        CC certification at High Assurance Level

o        Backup encryption support
PROGRAMMABILITY
o        SQL Server Data Tools
o        Local DB runtime (Express)
o        Data-tier Application Component project template
o        Data-Tier Application Framework (DAC Fx)
o        Query optimization enhancements
o        Interoperability support (ADO.NET, ODBC, JDBC, PDO, ADO APIs and .NET C/C++, Java, Linux, and PHP platforms)
T-SQL ENHANCEMENTS
o        Enhanced support for ANSI SQL standards
o        Transact-SQL Static Code Analysis tools
o        Transact-SQL code snippets
o        Intellisense
o        Unstructured & Complex Data Support
o        FileTable built on FILESTREAM
o        Remote Blob Storage with SharePoint 2010
o        Statistical Semantic Search
o        Spatial features, including Full Globe & arcs
o        Large user-defined data types
MANAGEABILITY
o        Distributed Replay
o        Contained Database Authentication
o        System Center Management Pack for SQL Server 2012
o        Windows PowerShell 2.0 support
o        Multi-server Management with SQL Server Utility Control Point
o        Data-Tier Application Component
 
ACCESS ANY DATA
o        Power Query
o        Windows Azure HDInsight Service
o        Analytics Platform System (PDW V2)
o        Mash up data from different sources, such as Oracle & Hadoop
INSIGHTS WITH FAMILIAR TOOLS
o        Power BI in Office 365
o        Power Map for Excel
o        Mobile interfaces for Power BI
COMPLETE BI SOLUTION
o        SQL Server BI Edition
o        HA for StreamInsight, complex event processing
o        BI Semantic Model
o        SQL Server Data Tools support for BI
o        Change Data Capture for Oracle
ANALYSIS SERVICES
o        Import PowerPivot models into Analysis Services
o        Enhancements on productivity, performance
REPORTING SERVICES
o        Power View
o        Configurable reporting alerts
o        Reporting as SharePoint Shared Service
o        Report Builder 3.0
DATA QUALITY SERVICES
o        Build organizational knowledge base
o        Connect to 3rd party data cleansing providers
MASTER DATA SERVICES
o        Master Data Hub
o        Master Data Services Add-in for Microsoft Excel
INTEGRATION SERVICES
o        Graphical tools in SSIS
o        Extensible object model
o        SSIS as a Server
o        Broader data integration with more sources; DB vendors, cloud, Hadoop
o        Pipeline improvements
HYBRID CLOUD SOLUTIONS
o        Simplified backup to Windows Azure
o        Support for backup of previous versions of SQL Server to Windows Azure
o        Cloud back-up encryption support
o        Simplified cloud Disaster Recovery with AlwaysOn replicas in Windows Azure VMs
EASY ON-RAMP TO THE CLOUD
o        New Windows Azure Deployment UI for SQL Server
o        Larger SQL Server VMs and memory sizes now available in Windows Azure
o        DAC enhancements: Import/export with Windows Azure SQL Database
COMPLETE AND CONSISTENT
FROM ON-PREM TO CLOUD
o        SQL Server Data Tools
o        License Mobility (with SA)
o        Resource Governor enhancements
o        Snapshot backups to Windows Azure via SQL Server Management Studio
 
 
Whats New in SQL Server 2014 since SQL Server 2008
 
PERFORMANCE & SCALE
o        In-Memory OLTP
o        Enhanced In-Memory ColumnStore for DW
o        Support for 640 logical proc. & 4 TB memory
o        Support to 15,000 partitions
o        Resource Governor IO governance
o        Buffer Pool Extension to SSDs
o        Query optimization enhancements
o        SysPrep at cluster level
o        Predictable performance with tiering of compute, network, and storage with Windows Server 2012 R2
o        Data Compression with USC-2 Unicode support
o        Backup Compression
HIGH AVAILABILITY
o        SQL Server AlwaysOn
o        Delayed Durability
o        Recovery Advisor
o        Windows Server Core
o        Live Migration
o        Online Operations enhancements
o        Clustered Shared Volume support, VHDX support (Windows Server 2012 R2)
o        Manage on-premises and cloud apps (System Center 2012 R2)
SECURITY
o        User-Defined Server Roles
o        Default Schema for Groups
o        SQL Server Audit
o        SQL Server Fine-grained Auditing
o        Enhanced separation of duty
o        CC certification at High Assurance Level
o        Backup encryption support
PROGRAMMABILITY
o        SQL Server Data Tools
o        Local DB runtime (Express)
o        Data-tier Application Component project template
o        Data-Tier Application Framework (DAC Fx)
o        Query optimization enhancements
o        Interoperability support (ADO.NET, ODBC, JDBC, PDO, ADO APIs and .NET C/C++, Java, Linux, and PHP platforms)
T-SQL ENHANCEMENTS
o        Enhanced support for ANSI SQL standards
o        Transact-SQL Static Code Analysis tools
o        Transact-SQL code snippets
o        Intellisense
UNSTRUCTURED & COMPLEX DATA SUPPORT
o        FileTable built on FILESTREAM
o        Remote Blob Storage with SharePoint 2010
o        Statistical Semantic Search
o        Spatial features, including Full Globe & arcs
o        Large user-defined data types
MANAGEABILITY
o        Distributed Replay
o        Contained Database Authentication
o        System Center Management Pack for SQL Server 2012
o        Windows PowerShell 2.0 support
o        Multi-server Management with SQL Server Utility Control Point
o        Data-Tier Application Component
ACCESS ANY DATA
o        Power Query
o        Windows Azure HDInsight Service
o        Analytics Platform System (PDW V2)
o        Mash up data from different sources, such as Oracle & Hadoop
INSIGHTS WITH FAMILIAR TOOLS
o        Power BI in Office 365
o        Power Map for Excel
o        Mobile interfaces for Power BI
COMPLETE BI SOLUTION
o        SQL Server BI Edition
o        HA for StreamInsight, complex event processing
o        BI Semantic Model
o        SQL Server Data Tools support for BI
o        Change Data Capture for Oracle
 
ANALYSIS SERVICES
o        Import PowerPivot models into Analysis Services
o        Enhancements on productivity, performance
REPORTING SERVICES
o        Power View
o        Configurable reporting alerts
o        Reporting as SharePoint Shared Service
o        Report Builder 3.0
DATA QUALITY SERVICES
o        Build organizational knowledge base
o        Connect to 3rd party data cleansing providers
MASTER DATA SERVICES
o        Master Data Hub
o        Master Data Services Add-in for Microsoft Excel
INTEGRATION SERVICES
o        Graphical tools in SSIS
o        Extensible object model
o        SSIS as a Server
o        Broader data integration with more sources; DB vendors, cloud, Hadoop
o        Pipeline improvements
HYBRID CLOUD SOLUTIONS
o        Simplified backup to Windows Azure
o        Support for backup of previous versions of SQL Server to Windows Azure
o        Cloud back-up encryption support
o        Simplified cloud Disaster Recovery with AlwaysOn replicas in Windows Azure VMs
EASY ON-RAMP TO THE CLOUD
o        New Windows Azure Deployment UI for SQL Server
o        Larger SQL Server VMs and memory sizes now available in Windows Azure
o        DAC enhancements: Import/export with Windows Azure SQL Database
COMPLETE AND CONSISTENT
FROM ON-PREM TO CLOUD
o        SQL Server Data Tools
o        License Mobility (with SA)
o        Resource Governor enhancements
o        Snapshot backups to Windows Azure via SQL Server Management Studio
 
Whats New in SQL Server 2014 since SQL Server 2005
 
PERFORMANCE & SCALE
o        In-Memory OLTP
o        Enhanced In-Memory ColumnStore for DW
o        Support for 640 logical proc. & 4 TB memory
o        Support to 15,000 partitions
o        Resource Governor IO governance
o        Buffer Pool Extension to SSDs
o        Query optimization enhancements
o        SysPrep at cluster level
o        Predictable performance with tiering of compute, network, and storage with Windows Server 2012 R2
o        Data Compression with USC-2 Unicode support
o        Backup Compression
HIGH AVAILABILITY
o        SQL Server AlwaysOn
o        Database Mirroring
o        Failover Clustering
o        Database Snapshots
o        Delayed Durability
o        Recovery Advisor
o        Windows Server Core
o        Live Migration
o        Online Operations
o        Clustered Shared Volume support, VHDX support (Windows Server 2012 R2)
o        Manage on-premises and cloud apps (System Center 2012 R2)
SECURITY
o        User-Defined Server Roles
o        Default Schema for Groups
o        SQL Server Audit
o        Transparent Data Encryption
o        Extensible Key Management
o        Standards-based Encryption
o        SQL Server Fine-grained Auditing
o        Enhanced separation of duty
o        CC certification at High Assurance Level
o        Backup encryption support
PROGRAMMABILITY
o        SQL Server Data Tools
o        Local DB runtime (Express)
o        Data-tier Application Component project template
o        Data-Tier Application Framework (DAC Fx)
o        Query optimization enhancements
o        Interoperability support (ADO.NET, ODBC, JDBC, PDO, ADO APIs and .NET C/C++, Java, Linux, and PHP platforms)
T-SQL ENHANCEMENTS
o        Enhanced support for ANSI SQL standards
o        Transact-SQL Static Code Analysis tools
o        Transact-SQL code snippets
o        Intellisense
DATA SUPPORT
o        FILESTREAM data type
o        FileTable built on FILESTREAM
o        Remote Blob Storage with SharePoint 2010
o        Spatial data support
o        Full Text Search for unstructured files
o        Statistical Semantic Search
o        Large user-defined data types
PROGRAMMABILITY SUPPORT
o        Support for LINQ and ADO.NET Entity Framework
o        CLR Integration and ADO.NET Object Services
MANAGEABILITY
o        Distributed Replay
o        Contained Database Authentication
o        System Center Management Pack for SQL Server 2012
o        Windows PowerShell 2.0 support
o        Multi-server Management with SQL Server Utility Control Point
o        Data-Tier Application Component
o        Multi-server Management with SQL Server Utility Control Point
o        Data-Tier Application Component
o        Policy-Based Management
o        SQL Server Performance Data Collector
o        Query enhancements
o        SMTP mail for secure DB email w/o Outlook
ACCESS ANY DATA
o        Power Query
o        Windows Azure HDInsight Service
o        Analytics Platform System (PDW V2)
o        Mash up data from different sources, such as Oracle & Hadoop
INSIGHTS WITH FAMILIAR TOOLS
o        Power BI in Office 365
o        Power Map for Excel
o        Mobile interfaces for Power BI
COMPLETE BI SOLUTION
o        SQL Server BI Edition
o        StreamInsight
o        BI Semantic Model
o        SQL Server Data Tools
o        BI Development Studio
o        Microsoft Visual Studio-based report dev tools
o        Change Data Capture for Oracle
ANALYSIS SERVICES
o        Import PowerPivot models into Analysis Services
o        Enhancements on productivity, performance
o        Cube design tools, block computations, and write-back to MOLAP
REPORTING SERVICES
o        Power View
o        Configurable reporting alerts
o        Reporting as SharePoint Shared Service
o        Report Builder 3.0
o        Report Designer
o        Report Manager
DATA QUALITY SERVICES
o        Build organizational knowledge base
o        Connect to 3rd party data cleansing providers
MASTER DATA SERVICES
o        Master Data Hub
o        Master Data Services Add-in for Microsoft Excel
INTEGRATION SERVICES
o        Graphical tools in SSIS
o        Extensible object model
o        SSIS as a Server
o        Broader data integration with more sources; DB vendors, cloud, Hadoop
o        Pipeline improvements
o        Persistent lookups
o        High-performance connectors
o        Data profiling tool
HYBRID CLOUD SOLUTIONS
o        Simplified backup to Windows Azure
o        Support for backup of previous versions of SQL Server to Windows Azure
o        Cloud back-up encryption support
o        Simplified cloud Disaster Recovery with AlwaysOn replicas in Windows Azure VMs
EASY ON-RAMP TO THE CLOUD
o        New Windows Azure Deployment UI for SQL Server
o        Larger SQL Server VMs and memory sizes now available in Windows Azure
o        DAC enhancements: Import/export with Windows Azure SQL Database
COMPLETE AND CONSISTENT
FROM ON-PREM TO CLOUD
o        SQL Server Data Tools
o        License Mobility (with SA)
o        Resource Governor enhancements
o        Snapshot backups to Windows Azure via SQL Server Management Studio
 

This list also includes some technologies that work together with SQL Server such as Azure, System Center, Excel 2013, Power BI for Office 365 and others.

Please find information how to upgrade to SQL Server 2014 here.

 
Comments (9)

  1. Ed says:

    Amit, please take a look at new SQL2014 feature – Memory Optimized Tables. This is an entirely new storage and query subsystem with designed around tables that must remain in RAM at all times. This allows for some interesting optimizations.

    One of the optimizations offered by this new table type is entirely lock-free writes.

    “When accessing memory-optimized tables, SQL Server uses completely optimistic multi-version concurrency control. Although SQL Server has previously been described as supporting optimistic concurrency control with the snapshot-based isolation levels introduced
    in SQL Server 2005, these so-called optimistic methods do acquire locks during data modification operations. For memory-optimized tables, there are no locks acquired, and thus no waiting because of blocking.”
    Kalen Delaney – Microsoft,
    For details on Memory Optimized Tables, see:
    https://technet.microsoft.com/en-us/library/dn133186(v=sql.120).aspx

  2. Amit Gupta says:

    Any major enhancement in SQL 2014 to reduce locking issues?

  3. Abhishek Dadarya says:

    Currently I am using SQL Server 2005 and want to upgrade to SQL Server 2014.

    I have migrated the same just by taking full backup of SQL Server 2005 and restore the same in SQL Server 2014.This is the correct approach or not.

  4. Kodal says:

    Can you also compare between versions (Standard/Enterprise)?

  5. Dat Diep says:

    Does any know whether you can use Microsoft Report Builder 3.0 for SQL 2014 on Microsoft SQL Server 2008 R2 ?

    Thanks.

  6. Anonymous says:

    Our friends at Microsoft Virtual Academy have a number of no-cost online training courses available,

  7. Sasidhar says:

    Thanks for the blog, Its good and valuable information. I using the Ref URL in my blog…

  8. Eduard_D says:

    Taking a full backup of SQL Server database 2005 and restore the same in SQL Server 2014 should be fine. The recommendation is always to use the upgrade wizard that will check for things like deprecated features.

    As for your question: I want to know about the mirroring feature in SQL Server 2014 in the machine at different location?

    Mirroring is one of two ways in providing synchronous availability to SQL Server in a disaster recovery scenario. The other is known as AlwaysOn Availability Groups. See the following for details:

    SQL Server Mirroring:
    https://msdn.microsoft.com/en-us/library/ms189852.aspx
    SQL Server AlwaysOn Availability Groups:
    https://msdn.microsoft.com/en-us/library/hh510230.aspx