Sunday, February 5, 2017

What’s New in SQL Server 2019 since SQL Server 2005, 2008, 2008R2 , 2012, 2014, 2016, 2017

What’s New in SQL Server 2019 since SQL Server 2017
Database Engine Enhancements on Windows
Enhancements in the SQL Server 2019 database engine are not limited to PolyBase and Big Data Clusters; many components of the database engine have new features and capabilities with this release. The following features are added or enhanced for SQL Server 2019 CTP 2.0.

Database Engine
  • UTF-8 support 
  • Resumable online index create allows index create to resume after interruption 
  • Clustered columnstore online index build and rebuild 
  • Always Encrypted with secure enclaves 
  • Intelligent query processing 
  • Java language programmability extension 
  • SQL Graph features 
  • Database scoped configuration setting for online and resumable DDL operations 
  • Enhancement in Always On Availability Groups 
  • Data discovery and classification - natively built into SQL Server 
  • Expanded support for persistent memory devices 
  • Support for columnstore statistics in DBCC CLONEDATABASE 
  • New options added to sp_estimate_data_compression_savings 
  • SQL Server Machine Learning Services failover clusters 
  • Lightweight query profiling infrastructure enabled by default 
  • New PolyBase connectors 
  • New sys.dm_db_page_info system function returns page information 

Let's look at some of the added features that I believe will be the most useful to users of the database management system (DBMS).

AlwaysOn Availability Groups
SQL Server 2019 adds support for even more high availability scenarios and platforms, including:
Enable high availability configurations for SQL Server running in containers - SQL Server 2019 enables customers to configure highly-available systems with AlwaysOn Availability Groups using Kubernetes as an orchestration layer.
Up to five synchronous replica pairs – SQL Server 2019 increases the limit for synchronous replica pairs from three (in SQL Server 2017) to five. Users can now configure up to five synchronous replicas (1 Primary and up to 4 secondary replicas) with automatic failover between these replicas.
Better scale-out with automatic redirection of connections based on read/write intent - Configuring an AlwaysOn Availability can be challenging for a number of reasons, including:
  • In SQL Server 2017, an administrator must configure the Availability Group listener (and the corresponding cluster resource) to direct SQL Server traffic to the primary replica to ensure that clients are transparently reconnected to the active primary node upon failover; however, there are cluster technologies that support SQL Server Availability Groups that do not offer a listener-like capability.
  • In a multi-subnet configuration such as Azure or multi-subnet floating IP address in an availability group using Pacemaker, configurations become complex, prone to errors and difficult to troubleshoot due to multiple components involved.
  • When the availability group is configured for read scale-out or DR and cluster type is NONE, there is no straightforward mechanism to ensure transparent reconnection upon manual failover.

To address these challenges, SQL Server 2019 adds a new feature for Availability Groups: secondary to primary replica connection redirection. With this feature, client applications can connect to any of the replicas of the Availability Group and the connection will be redirected to the primary replica, according to the Availability Group configuration and the connection intent (read only or read/write) specified in the connection string

Data discovery and classification
SQL Data Discovery and Classification allows to classify columns in the database that contain sensitive information. Columns can be classified by the type of information it contains. For example names, addresses, social security numbers etc and by the level of sensitive data in the column including levels such as public, general and confidential. We can easily generate reports from the classification that are applied to meet statutory and regulatory requirements, such as EU GDPR. SSMS also includes the SQL Data Discovery and Classification wizard, which will try to identify columns in the database that contain sensitive information. SQL Data Discovery and Classification uses the underlying mechanism of SQL Server Extended Properties, and so is backwards-compatible with SQL Server 2008 and later

Always Encrypted with Secure Enclaves
Introduced in SQL Server 2016, Always Encrypted is an encryption technology that protects the confidentiality of sensitive data from malware and high-privileged, but unauthorized users of SQL Server, including DBAs, machine admins and cloud admins. Sensitive data is never visible in plain-text to those users. 
The secure enclave technology, introduced in SQL Server 2019, addresses the limitations of Always Encrypted by allowing computations on plain-text data within a secure enclave inside the SQL Server process. A secure enclave is a protected region of memory within the SQL Server process, and it acts as a trusted execution environment for processing sensitive data inside the SQL Server Engine. A secure enclave extends client applications’ trust boundary to the server side. While it is contained by the SQL Server environment, the secure enclave is not accessible to SQL Server, the operating system, or to the database or system administrators.

Clustered columnstore online index build and rebuild
Creating clustered columnstore indexes (CCI) was an offline process in the previous versions of SQL Server - requiring all changes stop while the CCI is created. 
With SQL Server 2019 preview and Azure SQL Database we can create or re-create clustered columnstore index online. Workload will not be blocked and all changes made on the underlying data are transparently added into the target columnstore table.

Expanded support for persistent memory devices
Microsoft is improving persistent memory support for this release. It’s doing so with a newly optimized I/O path that’s meant to interact with persistent memory storage. When an SQL Server file is placed on a persistent memory device, it allows SQL Server to access the device directly, bypassing the operating system’s storage stack entirely. Basically, this improves performance and low latency I/O without actually changing your database design.

Intelligent query processing
The new Intelligent Query Processing suite is meant to rectify some of the common query performance problems by taking some automatic corrective approaches during run-time. It leverages a feedback loop based on statistics collected from past executions. Microsoft has already started leveraging some of these features in Azure SQL Database and plans to continue building on this area for SQL Server 2019.

UTF-8 Support
SQL Server 2019 includes full support for the widely used UTF-8 character encoding as an import or export encoding, or as database-level or column-level collation for text data. 
UTF-8 is allowed in the CHAR and VARCHAR datatypes, and is enabled when creating or changing an object’s collation,to a collation with the "UTF8" suffix,such as LATIN1_GENERAL_100_CI_AS_SC to LATIN1_GENERAL_100_CI_AS_SC_UTF8. UTF-8 is only available to windows collations that support supplementary characters, as introduced in SQL Server 2012. Note that NCHAR and NVARCHAR allow UTF-16 encoding only, and remain unchanged.

Significant storage savings can also be achieved, depending on the character set in use. For example, changing an existing column data type from NCHAR(10) using UTF-16 to CHAR(10) using an UTF-8 enabled collation, translates into nearly 50% reduction in storage requirements. This is because NCHAR(10) requires 22 bytes for storage, whereas CHAR(10) requires 12 bytes for the same Unicode string.
Database Engine Enhancements on Linux
The following features are added or enhanced for SQL Server 2019 preview CTP 2.

SQL Server on Linux
  • Replication support
  • Support for the Microsoft Distributed Transaction Coordinator (MSDTC)
  • Always On Availability Group on Docker containers with Kubernetes
  • OpenLDAP support for third-party AD providers
  • Machine Learning on Linux
  • New container registry
  • New RHEL-based container images
  • Memory pressure notification

Replication support
SQL Server 2019 preview supports SQL Server Replication on Linux. A Linux virtual machine with SQL Agent can be a publisher, distributor, or subscriber. Create the following types of publications
  • Transactional
  • Snapshot
  • Merge
Configure replication SQL Server Management Studio or use replication stored procedures

Support for the Microsoft Distributed Transaction Coordinator (MSDTC)
SQL Server 2019 on Linux supports the Microsoft Distributed Transaction Coordinator (MSDTC). SQL Server on Linux instances can now initiate and participate in distributed transactions. This is achieved with a Linux version of the Microsoft Distributed Transaction Coordinator (MSDTC) that runs within the SQL Server process to help SQL Server participate in distributed transactions. With access to MSDTC, SQL Server on Linux can participate in distributed transactions with other third-party transaction coordinators, or if you have specific processing needs there is support for you to develop your own.

Active Directory Integration
Integration between SQL Server on Linux and Active Directory is much improved. SQL Server on Linux instances can be configured use Active Directory for authentication of users, for replication, and for distributed queries. SQL Server on Linux instances can now participate in Availability Groups that are authenticated by Active Directory, in addition to the support for certificate-based authentication included in SQL Server 2017 on Linux. As an alternative to Active Directory, SQL Server on Linux instances can now use OpenLDAP as a directory provider, making it easier to manage domain-joined Linux servers.

Always On Availability Group on Docker containers with Kubernetes
Kubernetes can orchestrate containers running SQL Server instances to provide a highly available set of databases with SQL Server AlwaysOn Availability Groups. A Kubernetes operator deploys a StatefulSet including a container with mssql-server container and a health monitor.

OpenLDAP support for third-party AD providers
SQL Server 2019 preview on Linux supports OpenLDAP, which allows third-party providers to join Active Directory.

Machine Learning on Linux
There are many advantages to bringing the machine learning compute to the data instead of moving data out to compute. These advantages include the elimination of data movement, ease of deployment, improved security and better scale and performance. These advantages also make SQL Server a powerful end to end machine learning platform. Enhancements to Machine Learning in SQL Server 2019 CTP 2.0 include:
  • Machine Learning on Linux: SQL Server 2019 Machine Learning Services (In-Database) is now supported on Linux. 
  • Input data partitioning: without changing your R or Python scripts, you can process data at table partition level. This allows you to train a model for each table partition and parallelize model training per partition. 
  • Failover cluster support: You can install SQL Server 2019 Machine Learning Services (In-Database) on a Windows failover cluster to meet your requirements for redundancy and uptime in the event your primary server fails over. 
  • Java language extension: In addition to R and Python runtimes, SQL Server 2019 adds a Java language extension. This will allow you to call a pre-compiled Java program and securely execute Java code on SQL Server. This reduces the need to move data and improves application performance by bringing your workloads closer to your data. You specify the Java runtime you want to use, by installing the JDK distribution and Java version of your choice.
Container
All container images for SQL Server 2019 preview as well as SQL Server 2017 (14.x) are now located in the Microsoft Container Registry. Microsoft Container Registry is the official container registry for the distribution of Microsoft product containers. In addition, certified RHEL-based images are now published.

  • Microsoft Container Registry: mcr.microsoft.com/mssql/server:vNext-CTP2.0 
  • Certified RHEL-based container images: mcr.microsoft.com/mssql/rhel/server:vNext-CTP2.0

 What’s New in SQL Server 2017 since SQL Server 2016

The changes introduced in SQL Server 2017 discussed here include the following features:

SQL Server on Linux

SQL Server is no longer just a windows-based relational database management system (RDBMS). You can run it on different flavors of the Linux operating systems. You can also develop applications with SQL Server on Linux, Windows, Ubuntu operating systems, or Docker and deploy them on these platforms.

Resumable online index rebuild

This feature resumes an online index rebuild operation from where it stopped after events such as database failovers, running out of disk space, or pauses.

GUIDELINES FOR INDEXING

When you perform online index operations, the following guidelines apply:
·         Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains image, ntext, and text large object (LOB) data types.
·         Non-unique and non-clustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or as non-key (included) columns.
·         Indexes on local temporary tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temporary tables.
·         You can perform concurrent online index data definition language (DDL) operations on the same table or view only when you are creating multiple new non-clustered indices, or reorganizing non-clustered indices. All other online index operations performed at the same time fail. For example, you cannot create a new index online while rebuilding an existing index online on the same table.

 

SQL Server machine learning services

SQL Server 2016 integrated the R programming language, which can be run within the database server and can be embedded into a Transact-SQL (T-SQL) script, too. In SQL Server 2017, you can execute the Python script within the database server itself. Both R and Python are popular programming languages that provide extensive support for data analytics along with natural language processing capability.

 

Query processing improvements

SQL Server 2017 adapts optimization strategies to your application workload’s runtime conditions. It includes adaptive query processing features that you can use to improve query performance in SQL Server and SQL Database.
·         Batch mode memory grant feedback: This feedback technique recalculates required memory for the execution plan and grants it from cache.
·         Batch mode adaptive joins: To execute the plan faster, this technique can use a hash join or a nested loop join. After scanning the first input of the execution plan, it decides which join to use to produce output at the fastest speed.
·         Interleaved execution: Interleaved execution pauses optimization of an execution plan when it encounters multi-statement table-valued functions. Then, it calculates perfect cardinality and resumes optimization.

 

Automatic database tuning

This feature notifies you whenever a potential performance issue is detected and enables you to apply corrective actions, or it enables the database engine to automatically fix performance issues caused by the SQL plan choice regressions. Thus, the database can dynamically adapt to your workload by finding what indexes and plans might improve performance of your workloads and what indexes affect your workloads. Based on these findings, the automatic tuning process applies actions that improve the workload performance. In addition, the database continuously monitors performance after any change made by automatic tuning to ensure that it improves the workload performance. Any action that doesn’t improve performance is automatically reverted.

 

SQL PLAN CHOICE REGRESSION

The SQL Server database engine may use different SQL plans to execute the T-SQL queries. Query plans depend on the statistics, indexes, and other factors. In some cases, the new plan might not be better than the previous one, and the new plan might cause a performance regression. Whenever you notice a poor plan choice regression, you should find a previously used good plan and force it to be used instead of the current one by using the sp_query_store_force_plan procedure. The database engine in SQL Server 2017 (v. 14.x) provides information about regression plans and recommended corrective actions. Additionally, the database engine enables you to fully automate this process and let the database engine fix any problems related to the plan changes that are found.

 

AUTOMATIC PLAN CORRECTION

·         Automatic plan correction (available in SQL Server 2017 v14.x and Azure SQL Database): It identifies problematic query execution plans and fixes the SQL plan performance problems

·         Automatic index management (available only in Azure SQL Database): It identifies indexes that should be added in your database and indexes that should be removed.

 

TempDB file size improvements

SQL Server 2017 setup now enables you to specify the initial TempDB file size up to 256 GB (262,144 MB) per file, with a warning if the file size is set greater than 1GB without instant file initialization (IFI) enabled. It is important to understand that, depending on the initial size of TempDB data file specified, not enabling IFI can cause setup time to increase exponentially.

 

Smart differential backup

A new column modified_extent_page_count is introduced in sys.dm_db_file_space_usage to track differential changes in each database file in the database. The new column modified_extent_page_count allows DBAs, the SQL community, and backup independent software vendors (ISVs) to build smart backup solutions, which perform differential backups if the percentage of changed pages in the database is below a threshold (approximately 70-80%). Otherwise, they perform a full database backup. With a large number of changes in the database, the cost and time to complete differential backups is similar to taking a full database backup, so there is no real benefit of taking differential backup in this case. However, it can certainly increase the restore time of database. By adding this intelligence to the backup solutions, you can now save on restore and recovery time by using differential backups.

 

Smart transaction log backup

A new Dynamic Management Function (DMF), sys.dm_db_log_stats (database_id), was released. This function exposes a new column, log_since_last_log_backup_mb, which empowers DBAs, the SQL community, and backup ISVs to build intelligent T-log backup solutions to take backups based on the transactional activity on the database. This T-log backup solution intelligence ensures that, if the T-log backup frequency is too low, the transaction log size doesn't grow due to a high burst of transactional activity in a short time. It also helps to avoid a situation where the scheduled transaction log backup creates too many T-log backup files even when there is no transactional activity on the server. If that happened, it would add unnecessarily to the storage, file management, and restore overheads.

 

Improved SELECT INTO statement

In SQL Server 2017, you can provide the filegroup name on which to create a new table by using the ON keyword with the SELECT INTO statement. The table is created on the default filegroup of the user by default. This functionality was not available in previous versions.

 

Distributed transaction support

SQL Server 2017 supports distributed transactions for databases in availability groups. This support includes databases on the same instance of SQL Server and databases on different instances of SQL Server. Distributed transactions are not supported for databases configured for database mirroring.

 

New availability groups functionality

This functionality includes clusterless support, the Minimum Replica Commit Availability Groups setting, and Windows-Linux cross-OS migrations and testing.
This functionality includes the following features:
·         Availability groups can now be set up without an underlying cluster (Windows Server Failover Cluster or WSFC) and across mixed environments (instances on Windows and Linux or Docker).
·         The new Minimum Replica Commit setting enables you to dictate a certain number of secondary replicas. You must commit a transaction before committing on the primary.

 

New dynamic management views

The dynamic management views (DMVs) include the following elements:
·         sys.dm_db_log_stats exposes summary level attributes and information on transaction log files and is helpful for monitoring transaction log health.
·         sys.dm_tran_version_store_space_usage enables you to see the impact on version store usage, grouped by each database. As a result, you can use this to profile your workload in a test environment (before and after the change) and to monitor the impact over time–even if other databases are also using version store.
·         sys.dm_db_log_info exposes virtual log file (VLF) information to monitor, alert, and avert potential transaction log issues.
·         sys.dm_d_stats_histogram is a new dynamic management view for examining statistics.
·         sys.dm_os_host_info exposes things like platform, distribution, service pack level, and language.
·         sys.dm_os_sys_info was expanded, revealing CPU information (such as socket count, core count, and cores per socket).

 

In-memory enhancements

The in-memory changes in SQL Server 2017 include the following enhancements:
·         Computed column, and indexes on those columns, are now supported.
·         CASE expressions, CROSS APPLY, and TOP (N) WITH TIES are now supported in natively-compiled modules.
·         JSON commands are now fully supported in both check constraints and in natively-compiled modules.
·         The system procedure sp_spaceused now properly reports space for memory-optimized tables.
·         The system procedure sp_rename now works on in-memory tables and natively-compiled modules.
·         The limitation of eight indexes on memory-optimized tables has been eliminated.
·         Memory-optimized filegroup files can now be stored on Azure storage.

 

Security enhancement

You can now grant, deny, or revoke permissions on database-scoped credentials such as CONTROL, ALTER, REFERENCES, TAKE OWNERSHIP, and VIEW DEFINITION permissions. Also, ADMINISTER DATABASE BULK OPERATIONS is now visible in sys.fn_builtin_permissions.

 

High availability and disaster recovery

Gain mission-critical uptime, fast failover, easy setup, and load balancing of readable secondaries with enhanced Always On functionality in SQL Server 2017. This is a unified solution for high availability and disaster recovery on Linux and Windows. You can also put an asynchronous replica in an Azure virtual machine for hybrid high availability.

 

Performance improvements

SQL Server 2017 introduces the following changes to the way queries and statistics are collected and displayed:
·         A new DMV sys.dm_exec_query_statistics_xml allows you to correlate sessions to plans, as long as query profiling is enabled.
·         Showplan XML now includes information about the statistics used for a plan and, for actual plans, runtime metrics and the top 10 wait statistics experienced by that plan. These wait statistics are also now being tracked in the query store.
·         A new dynamic management function sys.dm_db_stats_histogram enables you to access histogram information programmatically, without databases console commands (DBCC).


What’s New in SQL Server 2016 since SQL Server 2014

Always Encrypted

With the Always Encrypted feature enabled your SQL Server data will always be encrypted within SQL Server. Access to encrypted data will only be available to the applications calling SQL Server.  Always Encrypted enables client application owners to control who gets access to see their applications confidential data.  It does this by allowing the client application to be the one that has the encryption key.  That encryption key is never passed to SQL Server. By doing this you can keep those nosey Database or Windows Administrators from poking around sensitive client application data In-Flight or At-Rest. This feature will now allow you to sleep at night knowing your confidential data stored in a cloud managed database is always encrypted and out of the eyes of your cloud provider.

Dynamic Data Masking

If you are interested in securing your confidential data so some people can see it, while other people get an obscured version of confidential data then you might be interested in dynamic data masking.  With dynamic data masking you can obscure confidential columns of data in a table to SQL Server for users that are not authorized to see the all the data.  With dynamic data masking you can identify how the data will be obscured.  For instance say you accept credit card numbers and store them in a table, but you want to make sure your help desk staff is only able to see the last four digits of the credit card number. By setting up dynamic data masking you can define a masking rules so unauthorized logins can only read the last four digits of a credit card number, whereas authorized logins can see all of the credit card information.

JSON Support

JSON stands for Java Script Object Notation.  With SQL Server 2016 you can now interchange JSON data between applications and the SQL Server database engine.  By adding this support Microsoft has provided SQL Server the ability to parse JSON formatted data so it can be stored in a relation format.  Additionally, with JSON support you can take relational data, and turn it into JSON formatted data.  Microsoft has also added some new functions to provided support for querying JSON data stored in SQL Server. Having these additional JSON features built into SQL Server should make it easier for applications to exchange JSON data with SQL Server.   

Multiple TempDB Database Files

It has been a best practice for a while to have more than one tempdb data file if you are running on a multi-core machine.  In the past, up through SQL Server 2014, you always had to manually add the additional tempdb data files after you installed SQL Server. With SQL Server 2016 you can now configure the number of tempdb files you need while you are installing SQL Server.  Having this new feature means you will no longer need to manually add additional tempdb files after installing SQL Server.  

PolyBase

PolyBase allows you to query distributed data sets.  With the introduction of PolyBase you will be able to use Transact SQL statements to query Hadoop, or SQL Azure blob storage.  By using PolyBase you can now write adhoc queries to join relational data from SQL Server with semi-structured data stored in Hadoop, or SQL Azure blob storage.  This allows you to get data from Hadoop without knowing the internals of Hadoop.  Additionally you can leverage SQL Server’s on the fly column store indexing to optimize your queries against semi-structured data.    As organizations spread data across many distributed locations, PolyBase will be a solution for them to leverage SQL Server technology to access their distributed semi-structured data. 

Query Store

If you are into examining execution plans than you will like the new Query Store feature.  Currently in versions of SQL Server prior to 2016 you can see existing execution plans by using dynamic management views (DMVs).  But, the DMVs only allow you to see the plans that are actively in the plan cache.  You can’t see any history for plans once they are rolled out of the plan cache.  With the Query Store feature, SQL Server now saves historical execution plans.  Not only that but it also saves the query statistics that go along with those historical plans.   This is a great addition and will allow you to now track execution plans performance for your queries over time.    

Row Level Security

With Row Level Security the SQL database engine will be able to restrict access to row data, based on a SQL Server login.   Restricting rows will be done by filter predicates defined in inline table value function.  Security policies will ensure the filter predicates get executed for every SELECT or DELETE operation.   Implementing row level security at the database layer means application developers will no longer need to maintain code to restrict data from some logins, while allowing other logins to access all the data.  With this new feature, when someone queries a tables that contains row level security they will not even know whether or not any rows of data were filtered out.

R Comes to SQL Server

With Microsoft’s purchase of Revolution Analytics they are now able to incorporate R to support advance analytics against big data right inside of SQL Server. By incorporating R processing into SQL Server, data scientists will be able to take their existing R code and run it right inside the SQL Server database engine.  This will eliminate the need to export your SQL server data in order to perform R processing against it.  This new feature brings R processing closer to the data.

Stretch Database

The Stretch Database feature provides you a method to stretch the storage of your On-Premise database to Azure SQL Database.  But having the stretch database feature allows you to have your most frequently accessed data stored On-Premise, while your less accessed data is off-site in an Azure SQL databases.  When you enable a database to “stretch” the older data starts moving over to the Azure SQL database behind the scenes.  When you need to run a query that might access active and historical information in a “stretched” database the database engine seamlessly queries both the On-Premise database as well as Azure SQL database and returns the results to you as if they had come from a single source.  This feature will make it easy for DBA’s to archive information to a cheaper storage media without having to change any actual application code.  By doing this you should be able to maximize performance on those active On-Premise queries.

Temporal Table

A temporal table is table that holds old versions of rows within a base table.  By having temporal tables SQL Server can automatically manage moving old row versions to the temporal table every time a row in the base table is updated.  The temporal table is physically a different table then the base table, but is linked to the base table.  If you’ve been building or plan to build your own method to managing row versioning then you might want to check out the new temporal tables support in SQL server 2016 before you go forth and build your own row versioning solution.

What’s 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
What’s 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
What’s 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
What’s 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.

Collected information from Microsoft blog:
https://blogs.technet.microsoft.com/cansql/2014/04/13/sql-server-2014-benefits-of-upgrading-from-sql-server-2005-2008-2008r2-and-2012/
By at

1 comment:

Subscribe to: Post Comments (Atom)

Popular Posts