Softpedia >Windows >Internet >Servers >Database Utils >Microsoft SQL Server >  Changelog

Microsoft SQL Server Changelog

What's new in Microsoft SQL Server 2019

Nov 3, 2021
  • SQL Server 2019 (15.x) introduces Big Data Clusters for SQL Server. It also provides additional capability and improvements for the SQL Server database engine, SQL Server Analysis Services, SQL Server Machine Learning Services, SQL Server on Linux, and SQL Server Master Data Services.
  • Data virtualization and SQL Server 2019 Big Data Clusters:
  • Scalable big data solution
  • Data virtualization with PolyBase
  • Intelligent Database:
  • SQL Server 2019 (15.x) builds on innovations in previous versions to provide industry-leading performance out of the box. From Intelligent Query Processing to support for persistent memory devices, the SQL Server Intelligent Database features improve performance and scalability of all your database workloads without any changes to your application or database design.
  • Intelligent Query Processing:
  • Row mode memory grant feedback
  • Batch mode on rowstore
  • Scalar UDF Inlining
  • Table variable deferred compilation
  • Approximate query processing with APPROX_COUNT_DISTINCT
  • In-Memory Database:
  • Hybrid buffer pool
  • Memory-optimized TempDB metadata
  • In-Memory OLTP support for Database Snapshots
  • Intelligent performance:
  • OPTIMIZE_FOR_SEQUENTIAL_KEY
  • Forcing fast forward and static cursors
  • Resource governance
  • Reduced recompilations for workloads
  • Indirect checkpoint scalability
  • Concurrent PFS updates
  • Scheduler worker migration
  • Monitoring:
  • WAIT_ON_SYNC_STATISTICS_REFRESH
  • Custom capture policy for Query Store
  • LIGHTWEIGHT_QUERY_PROFILING
  • sys.dm_exec_requests column command
  • sys.dm_exec_query_plan_stats
  • LAST_QUERY_PLAN_STATS
  • query_post_execution_plan_profile
  • sys.dm_db_page_info(database_id, file_id, page_id, mode)
  • Developer experience:
  • Edge constraint cascade delete actions
  • New graph function - SHORTEST_PATH
  • Partition tables and indexes
  • Use derived table or view aliases in graph match query
  • Unicode support:
  • Support for UTF-8 character encoding
  • Language extensions:
  • New Java language SDK
  • Java language SDK is open source
  • Support for Java data types
  • New default Java Runtime
  • SQL Server Language Extensions
  • Register external languages
  • Spatial:
  • New spatial reference identifiers (SRIDs)
  • Error messages:
  • Verbose truncation warnings
  • Mission-critical security:
  • Always Encrypted with secure enclaves
  • Certificate management in SQL Server Configuration Manager
  • Data Discovery & Classification
  • SQL Server Audit
  • High availability:
  • Up to five synchronous replicas
  • Secondary-to-primary replica connection redirection
  • HADR Benefits
  • Recovery:
  • Accelerated database recovery
  • Resumable operations:
  • Online clustered columnstore index build and rebuild
  • Resumable online rowstore index build
  • Suspend and resume initial scan for Transparent Data Encryption (TDE)
  • Containers:
  • Microsoft Container Registry
  • Non-root containers
  • Red Hat certified container images
  • PolyBase and Machine Learning support
  • Setup options:
  • New memory setup options
  • New parallelism setup options
  • Setup warning on Server/CAL license Product Key
  • SQL Server Machine Learning Services:
  • Partition-based modeling
  • Windows Server Failover Cluster
  • SQL Server Analysis Services:
  • Calculation groups in tabular models
  • Query interleaving
  • Many-to-many relationships in tabular models
  • Property settings for resource governance
  • Governance setting for Power BI cache refreshes
  • Online attach
  • SQL Server Integration Services:
  • Flexible file task
  • Flexible file source and destination
  • SQL Server Master Data Services:
  • Support for Azure SQL Managed Instance databases
  • New HTML controls
  • SQL Server Reporting Services:
  • This release of SQL Server Reporting Services features support for Azure SQL Managed Instances, Power BI Premium datasets, enhanced accessibility, Azure Active Directory Application Proxy, and Transparent Database Encryption. It also brings an update to Microsoft Report Builder.

New in Microsoft SQL Server 2016 SP2 (Apr 24, 2018)

  • SQL Server 2016 Service Pack 2 includes:
  • Performance and scale improvements for SQL Server, such as improved database backup performance on large memory machines and added backup compression support, which helps performance of almost all databases.
  • Supportability and diagnostics enhancements, such as improved troubleshooting and additional information about statistics used during query plan optimization.
  • New improvements based on Connect feedback items filed by the SQL Server Community.
  • Some improvements originally introduced in SQL Server 2014 SP2 and SQL Server 2012 SP4.

New in Microsoft SQL Server 2017 (Nov 27, 2017)

  • Database Engine:
  • Issue and customer impact: After upgrade, the existing FILESTREAM network share may be no longer available.
  • Workaround: First, reboot the computer and check if the FILESTREAM network share is available. If the share is still not available, complete the following steps:
  • In SQL Server Configuration Manager, right-click the SQL Server instance, and click Properties.
  • In the FILESTREAM tab clear Enable FILESTREAM for file I/O streaming access, then click Apply.
  • Check Enable FILESTREAM for file I/O streaming access again with the original share name and click Apply.
  • Master Data Services (MDS):
  • Issue and customer impact: On the user permissions page, when granting permission to the root level in the entity tree view, you see the following error: "The model permission cannot be saved. The object guid is not valid"
  • Workarounds:
  • Grant permission on the sub nodes in the tree view instead of the root level.
  • Run the script described in this MDS team blog error applying permission on entity level
  • Analysis Services:
  • Issue and customer impact: Data connectors for the following sources are not yet avaialble for tabular models at the 1400 compatibility level.
  • Amazon Redshift
  • IBM Netezza
  • Impala
  • Workaround: None.
  • Issue and customer impact: Direct Query models at the 1400 compatibility level with perspectives can fail on querying or discovering metadata.
  • Workaround: Remove perspectives and redeploy.
  • Tools:
  • Issue and customer impact: Running DReplay fails with the following message: "Error DReplay Unexpected error occurred!".
  • Workaround: None.

New in Microsoft SQL Server 2012 SP4 (Oct 6, 2017)

  • SQL Server 2012 Service Pack 4 will include:
  • All fixes and Cumulative Updates (CUs) for SQL Server 2012 up to and including SQL Server 2012 SP3 CU10.
  • Scalability and performance improvements for SQL Server.
  • Additional monitoring capabilities through enhancements in DMV, Extended Events and Query Plans and the ability to clone the database including statistics with DBCC CLONEDATABASE.
  • New improvements based on Connect feedback items filed by the SQL Server Community.
  • Some of the improvements originally introduced in SQL Server 2014 SP2 and SQL Server 2016 SP1.

New in Microsoft SQL Server 2017 RC 2 (Aug 3, 2017)

  • This release contains bug fixes and performance improvements.
  • Master Data Services (MDS):
  • The upgrade experience and performance has been improved, when upgrading to SQL Server 2017 Master Data Services from the following previous releases of SQL Server.
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016
  • SQL Server Database Engine:
  • CLR assemblies can now be added to a whitelist, as a workaround for the clr strict security feature described in CTP 2.0. sp_add_trusted_assembly, sp_drop_trusted_assembly, and sys.trusted_asssemblies are added to support the white list of trusted assemblies (RC1).
  • Resumable online index rebuild resumes an online index rebuild operation from where it stopped after a failure (such as a failover to a replica or insufficient disk space), or pauses and later resumes an online index rebuild operation. See ALTER INDEX and Guidelines for online index operations. (CTP 2.0)
  • The IDENTITY_CACHE option for ALTER DATABASE SCOPED CONFIGURATION allows you to avoid gaps in the values of identity columns if a server restarts unexpectedly or fails over to a secondary server. See ALTER DATABASE SCOPED CONFIGURATION. (CTP 2.0)
  • Automatic database tuning provides insight into potential query performance problems, recommends solutions, and can automatically fix identified problems. See Automatic tuning. (CTP 2.0)
  • New graph database capabilities for modeling many-to-many relationships include new CREATE TABLE syntax for creating node and edge tables, and the keyword MATCH for queries. See Graph Processing with SQL Server 2017. (CTP 2.0)
  • An sp_configure option called clr strict security is enabled by default to enhance the security of CLR assemblies. See CLR strict security. (CTP 2.0)
  • Setup now allows specifying 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 with IFI not enabled. (CTP 2.0)
  • The modified_extent_page_count column in sys.dm_db_file_space_usage tracks differential changes in each database file, enabling smart backup solutions that perform differential backup or full backup based on percentage of changed pages in the database. (CTP 2.0)
  • SELECT INTO T-SQL syntax now supports loading a table into a FileGroup other than the user's default by using the ON keyword. (CTP 2.0)
  • Cross database transactions are now supported among all databases that are part of an Always On Availability Group, including databases that are part of same instance. See Transactions - Always On Availability Groups and Database Mirroring (CTP 2.0)
  • New Availability Groups functionality includes clusterless support, Minimum Replica Commit Availability Groups setting, and Windows-Linux cross-OS migrations and testing. (CTP 1.3)
  • New dynamic management views:
  • sys.dm_db_log_stats exposes summary level attributes and information on transaction log files, helpful for monitoring transaction log health. (CTP 2.1)
  • sys.dm_tran_version_store_space_usage tracks version store usage per database, useful for proactively planning tempdb sizing based on the version store usage per database. (CTP 2.0)
  • sys.dm_db_log_info exposes VLF information to monitor, alert, and avert potential transaction log issues. (CTP 2.0)
  • sys.dm_db_stats_histogram is a new dynamic management view for examining statistics. (CTP 1.3)
  • sys.dm_os_host_info provides operating system information for both Windows and Linux. (CTP 1.0)
  • The Database Tuning Advisor (DTA) has additional options and improved performance. (CTP 1.2)
  • In-memory enhancements include support for computed columns in memory-optimized tables, full support for JSON functions in natively compiled modules, and the CROSS APPLY operator in natively compiled modules. (CTP 1.1)
  • New string functions are CONCAT_WS, TRANSLATE, and TRIM, and WITHIN GROUP is now supported for the STRING_AGG function. (CTP 1.1)
  • There are new bulk access options (BULK INSERT and OPENROWSET(BULK...) ) for CSV and Azure Blob files. (CTP 1.1)
  • Memory-optimized object enhancements include sp_spaceused and elimination of the 8 index limitation for memory-optimized tables, sp_rename for memory-optimized tables and natively compiled T-SQL modules, and CASE and TOP (N) WITH TIES for natively compiled T-SQL modules. Memory-optimized filegroup files can now be stored, backed up and restored on Azure Storage. (CTP 1.0)
  • DATABASE SCOPED CREDENTIAL is a new class of securable, supporting CONTROL, ALTER, REFERENCES, TAKE OWNERSHIP, and VIEW DEFINITION permissions. ADMINISTER DATABASE BULK OPERATIONS is now visible in sys.fn_builtin_permissions. (CTP 1.0)
  • Database COMPATIBILITY_LEVEL 140 is added. (CTP 1.0).
  • SQL Server Integration Services (SSIS):
  • The new Scale Out feature in SSIS has the following new and changed features. For more info, see What's New in Integration Services in SQL Server 2017. (RC1)
  • Scale Out Master now supports high availability.
  • The failover handling of the execution logs from Scale Out Workers is improved.
  • The parameter runincluster of the stored procedure [catalog].[create_execution] is renamed to runinscaleout for consistency and readability.
  • The SSIS Catalog has a new global property to specify the default mode for executing SSIS packages.
  • In the new Scale Out for SSIS feature, you can now use the Use32BitRuntime parameter when you trigger execution. (CTP 2.1)
  • SQL Server 2017 Integration Services (SSIS) now supports SQL Server on Linux, and a new package lets you run SSIS packages on Linux from the command line. For more information, see the blog post announcing SSIS support for Linux. (CTP 2.1)
  • The new Scale Out for SSIS feature makes it much easier to run SSIS on multiple machines. See Integration Services Scale Out. (CTP 1.0)
  • OData Source and OData Connection Manager now support connecting to the OData feeds of Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online. (CTP 1.0)
  • Master Data Services (MDS):
  • In addition to improving the upgrade performance and experience for upgrading to SQL Server 2017 MDS, the following additional enhancements have been made to Master Data Services.
  • You can now view the sorted lists of entities, collections and hierarchies in the Explorer page of the Web application.
  • Performance has been improved for staging millions of records using the staging stored procedure.
  • Performance has been improved when expanding the Entities folder on the Manage Groups page to assign model permissions. The Manage Groups page is located in the Security section of the Web application. For more information about the performance improvement, see https://support.microsoft.com/help/4023865?preview. For more information about assigning permissions, see Assign Model Object Permissions (Master Data Services).
  • SQL Server Analysis Services (SSAS)
  • SQL Server Analysis Services 2017 introduces many enhancements for tabular models. These include:
  • Tabular mode as the default installation option for Analysis Services. (CTP 2.0)
  • Object-level security to secure the metadata of tabular models. (CTP 2.0)
  • Date relationships to easily create relationships based on date fields. (CTP 2.0)
  • New Get Data (Power Query) data sources, and existing DirectQuery data sources support for M queries. (CTP 2.0)
  • DAX Editor for SSDT. (CTP 2.0)
  • Encoding hints, an advanced feature for optimizing data refresh of large in-memory tabular models. (CTP 1.3)
  • Support for the 1400 Compatibility level for tabular models. To create new or upgrade existing tabular model projects to the 1400 compatibility level, download and install SQL Server Data Tools (SSDT) 17.0 RC2. (CTP 1.1)
  • A modern Get Data experience for tabular models at the 1400 compatibility level. See the Analysis Services Team Blog. (CTP 1.1)
  • Hide Members property to hide blank members in ragged hierarchies. (CTP 1.1)
  • New Detail Rows end-user action to Show Details for aggregated information. SELECTCOLUMNS and DETAILROWS functions for creating Detail Rows expressions. (CTP 1.1)
  • DAX IN operator for specifying multiple values. (CTP 1.1)
  • SQL Server Reporting Services (SSRS):
  • As of CTP 2.1, SSRS is no longer available to install through SQL Server setup. Go to the Microsoft Download Center to download the Microsoft SQL Server 2017 Reporting Services Release Candidate.
  • Comments are now available for reports, to add perspective and collaborate with others. You can also include attachments with comments. (CTP 2.1)
  • In the latest releases of Report Builder and SQL Server Data Tools, you can create native DAX queries against supported SQL Server Analysis Services tabular data models by dragging and dropping desired fields in the query designers
  • SQL Server Machine Learning Services:
  • SQL Server R Services has been renamed SQL Server Machine Learning Services, to reflect support for Python in addition to the R language. You can use Machine Learning Services (In-Database) to run R or Python scripts in SQL Server. Or install Microsoft Machine Learning Server (Standalone) to deploy and consume R and Python models that don't require SQL Server.
  • SQL Server developers now have access to the extensive Python ML and AI libraries available in the open source ecosystem along with the latest innovations from Microsoft:
  • revoscalepy - This Pythonic version of RevoScaleR includes parallel algorithms for linear and logistic regressions, decision tree, boosted trees and random forests, as well as a rich set of APIs for data transformation and data movement, remote compute contexts, and data sources.
  • microsoftml - This state-of-the-art package of machine learning algorithms and transforms with Python bindings includes deep neural networks, fast decision trees and decision forests, highlyand optimized algorithms for linear and logistic regressions. You also get pre-trained models based on ResNet models that you can use for image extraction or sentiment analysis.
  • Python operationalization with T-SQL - Deploy Python code easily by using the stored procedure sp_execute_external_script. Get great performance by streaming data from SQL to Python processes and using MPI ring parallelization.
  • Python in SQL Server compute contexts - Data scientists and developers can execute Python code remotely from their development environments to explore data and develop models without moving data around.

New in Microsoft SQL Server 2017 RC 1 (Jul 18, 2017)

  • In our seven community technology previews (CTPs) to date, SQL Server 2017 has delivered:
  • Graph data processing in SQL Server – With the graph data features available in SQL Server 2017 and Azure SQL Database, customers can create nodes and edges, and discover complex and many-to-many relationships.
  • Adaptive query processing – Adaptive query processing is a family of features in SQL Server 2017 that automatically keeps database queries running as efficiently as possible without requiring additional tuning from database administrators. In addition to the capability to adjust batch mode memory grants, the feature set includes batch mode adaptive joins and interleaved execution capabilities.
  • Python integration for advanced analytics – Microsoft Machine Learning Services now brings you the ability to run in-database analytics using Python or R in a parallelized and scalable way. The ability to run advanced analytics in your operational store without ETL means faster time to insights for customers while easy deployment and rich extensibility make it fast to get up and running on the right model.
  • Key enhancements in Release Candidate 1
  • In SQL Server 2017 RC1, there were several feature enhancements of note:
  • SQL Server on Linux Active Directory integration – With RC1, SQL Server on Linux supports Active Directory Authentication, which enables domain-joined clients on either Windows or Linux to authenticate to SQL Server using their domain credentials and the Kerberos protocol. Check out the getting started instructions.
  • Transport Layer Security (TLS) to encrypt data – SQL Server on Linux can use TLS to encrypt data that is transmitted across a network between a client application and an instance of SQL Server. SQL Server on Linux supports the following TLS protocols: TLS 1.2, 1.1, and 1.0. Check out the getting started instructions.
  • Machine Learning Services enhancements – In RC1, we add more model management capabilities for R Services on Windows Server, including External Library Management. The new release also supports Native Scoring.
  • SQL Server Analysis Services (SSAS) – In addition to the enhancements to SSAS from previous CTPs of SQL Server 2017, RC1 adds additional Dynamic Management Views, enabling dependency analysis and reporting. See the Analysis Services blog for more information.
  • SQL Server Integration Services (SSIS) on Windows Server – RC1 adds support for SSIS scale out in highly available environments. Customers can now enable Always On for SSIS, setting up Windows Server failover clustering for the scale out master.
  • SQL Server 2017 for faster performance:
  • SQL Server 2017 has several new benchmarks demonstrating faster performance than competitive databases, and against older versions of SQL Server:
  • The world record TPC-H 1TB nonclustered data warehousing benchmark, achieved in April 2017 using SQL Server 2017 on Red Hat Enterprise Linux and HPE Proliant DL380 Gen9 hardware.
  • A new, nonclustered TPC-H 10TB data warehousing workload benchmark achieved using SQL Server 2017 on Windows Server 2016 and Lenovo ThinkSystem SR950 hardware. At 1,336,109 QppH, this newly published benchmark is a world record.
  • A new TPC-E benchmark achieved with SQL Server 2017 running on Windows Server 2016 on a Lenovo ThinkSystem SR650, a world record for 2 socket TPC-E results.
  • Streamline your DevOps using SQL Server 2017:
  • In SQL Server 2017, we have introduced support for SQL Server on Linux-based containers, a benefit for customers using containers in development or production. We’re also working to help developers get started developing an app for SQL Server as fast as possible with installation instructions, code snippets, and other handy information.
  • On our new microsite DevOps using SQL Server, which launched today, developers and development managers can learn how to integrate SQL Server in their DevOps tasks. Find demos, documentation, and blogs, as well as videos and conference presentations. Or, join the DevOps conversation at our Gitter channels.

New in Microsoft SQL Server v.Next CTP 1.4 (Mar 17, 2017)

  • Key CTP 1.4 enhancements:
  • The primary enhancement to SQL Server v.Next on Linux in this release is the ability to schedule jobs using SQL Server Agent. This functionality helps administrators automate maintenance jobs and other tasks, or run them in response to an event.
  • In addition, SQL Server Analysis Services and SQL Server Reporting Services developer tools now support Visual Studio 2017

New in Microsoft SQL Server v.Next CTP 1.3 (Feb 20, 2017)

  • SQL Server v.Next CTP 1.3 also includes these additional feature enhancements:
  • Resumable online index rebuilds enables users to recover more easily from interruption of index builds, or split an index build across maintenance windows.
  • Temporal Tables Retention Policy support enables customers to more easily manage the amount of historical data retained by temporal tables.
  • Indirect checkpoint performance improvements. Indirect checkpoint is the recommended configuration for large databases and for SQL Server 2016, and now it will be even more performant in SQL Server v.Next.
  • Minimum Replica Commit Availability Groups setting enables users to set the minimum number of replicas that are required to commit a transaction before committing on the primary.
  • For SQL Server v.Next technical preview running on Windows Server, encoding hints in SQL Server Analysis Services is an advanced feature to help optimize refresh times with no impact on query performance.

New in Microsoft SQL Server v.Next CTP 1.2 (Jan 21, 2017)

  • Supported installation scenarios (CTP 1.2):
  • SQL Server vNext is intended as a test version only. Production deployments are not supported. It is recommneded you install and test SQL Server vNext on a virtual machine.
  • SQL Server Database Engine (CTP 1.2):
  • Issue and customer impact: In some cases, the MSSQLSERVER service will get stuck in the "Starting" state.
  • Workaround: To work around this issue:
  • Create a dependency between the mssqlserver service and the keyiso service. One way to do this is to run the following from an elevated Command Prompt: sc config mssqlserver depend= keyiso
  • Reboot the computer.
  • Documentation (CTP 1.2):
  • Issue and customer impact: Documentaion for SQL Server vNext is limited and content is inlcuded with the SQL Server 2016 documentation set. Content in artilces that is specific to SQL Server vNext will be noted with Applies To:.
  • Issue and customer impact: No offline content is avaiable for SQL Server vNext.
  • SQL Server Integration Services (SSIS) (CTP 1.2)
  • Deleting the SSIS Catalog may fail when SSIS Scale Out is installed
  • Issue and customer impact: When the SSIS Scale Out feature is installed on a computer, deleting the SSISDB catalog database may fail with following error: “Could not drop login 'login' as the user is currently logged in”.
  • Workaround:
  • On a Scale Out Master computer, run the command “services.msc” to open the Services window. Stop the SQL Server Integration Services Cluster Master service.
  • On Scale Out Worker computers that connect to the master, run the command "services.msc" to open the Services window. Stop the SQL Server Integration Services Cluster Worker service.
  • You can now delete the SSISDB catalog database.
  • SQL Server Master Data Services (CTP 1.2):
  • Transaction may not work when the entity transaction log type is set to attribute
  • Issue and customer impact: When the entity transaction log type is set to Attribute in Master Data Services (default value is Member), the following scenarios fail:
  • Transactions for entity changes are not shown in the website.
  • Unable to open the Transactions page on the website and reverse a transaction.
  • Unable to update an entity with a transaction annotation, in the website.
  • Workaround: There is no workaround.
  • Copy version may not work when Copy only committed version is set to false
  • Issue and customer impact: When the Copy only committed version setting is set to No (default value is Yes), the copy version operation may fail. There is no error message.
  • Workaround: There is no workaround.

New in Microsoft SQL Server 2014 SP2 (Jul 13, 2016)

  • Performance and Scalability Improvements:
  • Automatic Soft NUMA partitioning: With SQL Server 2014 SP2, Automatic Soft NUMA is enabled when Trace Flag 8079 is turned on during instance startup. When Trace Flag 8079 is enabled during startup, SQL Server 2014 SP2 will interrogate the hardware layout and automatically configure Soft NUMA on systems reporting 8 or more CPUs per NUMA node. The automatic, soft NUMA behavior is Hyperthread (HT/logical processor) aware. The partitioning and creation of additional nodes scales background processing by increasing the number of listeners, scaling, and network and encryption capabilities. It is recommended to first test the performance workload with Auto-Soft NUMA before turning it in production.
  • Dynamic Memory Object Scaling: SQL Server 2014 SP2 dynamically partitions memory objects based on number of nodes and cores to scale on modern hardware. The goal of dynamic promotion is to automatically partition a thread safe memory object (CMEMTHREAD) if it becomes a bottleneck. Un-partitioned memory objects can be dynamically promoted to be partitioned by node (number of partitions equals number of NUMA nodes), and memory objects partitioned by node can by further promoted to be partitioned by CPU (number of partitions equals number of CPUs).
  • MAXDOP hint for DBCC CHECK* commands: This improvement addresses connect feedback (468694). You can now run DBCC CHECKDB with the a MAXDOP setting other than the sp_configure value. If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see DBCC CHECKDB (Transact-SQL).
  • Enable >8TB for Buffer Pool: SQL Server 2014 SP2 enables 128TB of virtual address space for buffer pool usage. This improvement enables SQL Server Buffer Pool to scale beyond 8TB on modern hardware.
  • SOS_RWLock spinlock Improvement: The SOS_RWLock is a synchronization primitive used in various places throughout the SQL Server code base. As the name implies the code can have multiple shared (readers) or single (writer) ownership. SQL Server 2014 SP2 removes the need for spinlock for SOS_RWLock in no conflict situation thereby enable read intensive workloads to scale further.
  • Spatial Native Implementation: Significant improvement in spatial query performance is introduced in SQL Server 2014 SP2 through native implementation.
  • Supportability and Diagnostics Improvements:
  • Database Cloning: Clone database is a new DBCC command that enhances troubleshooting existing production databases by cloning the schema and metadata without the data. The clone is created with the command DBCC clonedatabase(‘source_database_name’, ‘clone_database_name’). Note: Cloned databases should not be used in production environments. Use the following command determine if a database has been generated from a cloned database: select DATABASEPROPERTYEX('clonedb', 'isClone'). The return value of 1 indicates the database is created from clonedatabase while 0 indicates it is not a clone.
  • Tempdb supportability: A new errorlog message that indicates the number of tempdb files and the size/autogrowth of tempdb data files present at server startup.
  • Database Instant File Initialization Logging: A new errorlog message that indicates on server statup, the status of Database Instant File Initialization (enabled/disabled).
  • Module names in callstack: The Xevent callstack now includes modules names + offset instead of absolute addresses.
  • New DMF for incremental statistics: This improvement addresses connect feedback (797156) to enable tracking the incremental statistics at the partition level. A new DMF sys.dm_db_incremental_stats_properties is introduced to expose information per-partition for incremental stats.
  • Index Usage DMV behavior updated: This improvement addresses connect feedback (739566) from customers where rebuilding an index will not clear any existing row entry from sys.dm_db_index_usage_stats for that index. The behavior will now be the same as in SQL 2008 and SQL Server 2016. See the blog for more information.
  • Improved correlation between diagnostics XE and DMVs: This improvement addresses connect feedback (1934583). Query_hash and query_plan_hash are used for identifying a query uniquely. DMV defines them as varbinary(8), while XEvent defines them as UINT64. Since SQL server does not have "unisigned bigint", casting does not always work. This improvement introduces new XEvent action/filter columns equivalent to query_hash and query_plan_hash except they are defined as INT64 which can help correlating queries between XE and DMVs.
  • Support for UTF-8 in BULK INSERT and BCP: This improvement addresses connect feedback (370419) where support for export and import of data encoded in UTF-8 character set is now enabled in BULK INSERT and BCP.
  • Lightweight per-operator query execution profiling: While troubleshooting query performance, although showplan provides lot of information on the query execution plan and cost of operator in the plan but it has limited information on actual runtime statistics like (CPU, I/O Reads, elapsed time per-thread). SQL 2014 SP2 introduces these additional runtime statistics per operator in the Showplan as well as an XEvent (query_thread_profile) to assist troubleshooting query performance.
  • Change Tracking Cleanup: A new stored procedure sp_flush_CT_internal_table_on_demand is introduced to cleanup change tracking internal tables on demand.
  • AlwaysON Lease Timeout Logging Added new logging capability for Lease Timeout messages so that the current time and the expected renewal times are logged. Also a new message was introduced in the SQL Errorlog regarding the timeouts.
  • New DMF for retrieving input buffer in SQL Server: A new DMF for retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is now available. This is functionally equivalent to DBCC INPUTBUFFER.
  • Mitigation for underestimated and overestimated memory grant: Added new query hints for Resource Governor through MIN_GRANT_PERCENT and MAX_GRANT_PERCENT. This allows you to leverage these hints while running queries by capping their memory grants to prevent memory contention.
  • Better memory grant/usage diagnostics: A new extended event was added to the list of tracing capabilities in SQL Server (query_memory_grant_usage) to track memory grants requested and granted. This provides better tracing and analysis capabilities for troubleshooting query execution issues related to memory grants.
  • Query execution diagnostics for tempdb spill:- Hash Warning and Sort Warnings now have additional columns to track physical I/O statistics, memory used and rows affected. We also introduced a new hash_spill_details extended event. Now you can track more granular information for your hash and sort warnings (KB3107172). This improvement is also now exposed through the XML Query Plans in the form of a new attribute to the SpillToTempDbType complex type (KB3107400). Set statistics on now shows sort worktable statistics.
  • Improved diagnostics for query execution plans that involve residual predicate pushdown: The actual rows read will now be reported in the query execution plans to help improve query performance troubleshooting. This should negate the need to capture SET STATISTICS IO separately. This now allows you to see information related to a residual predicate pushdown in a query plan.

New in Microsoft SQL Server 2016 GA (Jun 2, 2016)

  • Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk based relational databases and real-time operational analytics
  • New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes
  • Built-in advanced analytics– provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database
  • Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android
  • Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology
  • Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes
  • Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure

New in Microsoft SQL Server 2016 RC 3 (Apr 15, 2016)

  • The improvements in SQL Server 2016 Release Candidate 3 (RC3) primarily consist of bug fixes and other work to increase the stability and reliability of SQL Server 2016.

New in Microsoft SQL Server 2016 RC 2 (Apr 1, 2016)

  • R Services setup – the setup process for R Services is much more integrated into SQL Server setup. There is no longer a need to manually download and install Microsoft R open and R Server if the SQL Server is connected to the Internet; it becomes part of the SQL Server install sequence.
  • SQL Server Management Studio (SSMS) – This release of SSMS features an update to the Visual Studio 2015 shell bringing enhancements such as the quick launch toolbar and improved theming support.
  • Mobile reports – Brand Packages will now be downloaded to the mobile report publisher from a server running RC2 and available for use in report creation. Basic mobile report content migration between servers is now supported.

New in Microsoft SQL Server 2016 RC 1 (Mar 19, 2016)

  • In SQL Server 2016 RC 1, we made enhancements to SQL Server Reporting Services, including
  • Updated preview of the new web portal: The new web portal by default, and the classic Report Manager now removed. Additionally, open the Mobile Report Publisher and Report Builder from the new web portal using any modern browser
  • Custom branding: Customize the web portal with your organization’s logo and colors
  • KPIs and mobile reports: Click a KPI and see a view with more details, and connect KPIs and mobile reports to parameterized datasets
  • Modern paginated reports: Design beautifully modern paginated reports with new, modern styles for charts, gauges, maps and other data visualizations

New in Microsoft SQL Server 2016 CTP 3.3 (Feb 3, 2016)

  • Continued enhancement of Stretch Database: Stretch Database allows you to stretch operational tables in a secure manner into Azure for cost-effective historic data availability. CTP 3.3 includes multiple improvements to Stretch Database, including Azure Stretch database edition preview with support for up to 60TB, Point-in-time restore and geo-failover support.
  • Enhancements to In-Memory OLTP: In-Memory OLTP, which dramatically improves transaction processing performance, has added support in CTP 3.3.
  • Enhancements to Analysis Services DirectQuery models: Analysis Services Tabular Models running in DirectQuery mode now also allows us of DAX filters when defining roles and creation of calculated columns.
  • Enhancements to the new Reporting Services web portal: An updated preview of the new web portal now enables you to add the KPIs and reports you use to your Favorites, to create and edit shared data sources for your KPIs and reports, and to perform other management tasks.

New in Microsoft SQL Server 2016 CTP 3.2 (Dec 19, 2015)

  • SQL Server Reporting Services – End-to-end mobile BI on any device:
  • The SQL Server 2016 CTP 3.2 release marks a significant milestone for SQL Server Reporting Services (SSRS) as we continue to deliver on our Microsoft BI reporting roadmap and the promise to enable users to get business insights, anyway, anywhere and from any device.
  • In this preview release, we are adding support for mobile reports to SQL Server Reporting Services for on-premises implementations. This means that Reporting Services will be able to support two report types, paginated reports, which are the existing Reporting Services reports and mobile reports, which are based on Datazen technology that was acquired in April of 2015.
  • Mobile reports are optimized for mobile devices and form factors and provide an optimal experience for users accessing BI reports on mobile devices. With SQL Server 2016 CTP 3.2, you can author and manage mobile reports for easy consumption by users across your organization online and on mobile devices.
  • Author interactive mobile reports:
  • Microsoft SQL Server Mobile Report Publisher is the single point for creation of mobile reports. You can simply connect to SQL Server Reporting Services to access data sources and easily create stunning reports, then publish them to SQL Server Reporting Services for access by others in the organization via a unified web experience or on mobile devices.
  • Consume mobile reports using a unified Mobile BI experience:
  • Whether you are using SQL Server Reporting Services on-premises, Power BI in the cloud, or both as your report delivery solution you will only need one mobile app (for each of Windows, iOS and Android) to access dashboards and mobile reports on mobile devices.
  • Starting with the Power BI app for iOS all of your BI content will be available at your fingertips from within one single, unified mobile app. The Power BI app for iOS that includes a preview of the SSRS mobile reporting capabilities is planned to be available from the App Store by the end of December 2015.
  • New Web portal experience:
  • The addition of the mobile report type in SQL Server Reporting Services is accompanied with an entirely new web portal experience allowing users to access KPIs, paginated and mobile reports in one centralized location.
  • Ready to learn more about these exciting new capabilities and explore the opportunities and scenarios it can enable for you and your organization? Download the CTP 3.2 release today and check out this SSRS blog post to find out how your organization can get business insights, any way, anywhere and from any device.
  • Additional enhancements:
  • SQL Server Management Studio (SSMS) features improvements to the XEvents wizard to allow the use of templates when connected to an Azure v12 server, user interface improvements to AlwaysEncrypted wizards and dialogs, an improvement to the Results pane to enable switching to the results tab after query execution, and updates to the Showplan comparison feature to enable comparison of the current execution plan with one saved in a file. Please visit the SSMS blog post to learn more.
  • SQL Server Analysis Services (SSAS) updates allow scripting in SSMS, creation of calculated tables, and DirectQuery for models with the 1200 compatibility level. Please visit the SSAS team blog post to learn more.
  • SQL Server Data Tools (SSDT) now includes enhancements for the new connection experience for Microsoft SQL Server and Azure SQL Database which was introduced in the CTP 3.1, as well as programmability support for SQL Server 2016 CTP3.2 features and enhancements in SQL Server Analysis Services. Please visit the SSDT team blog post to learn more.
  • SQL Server Integration Services (SSIS) enhancements include HDFS-to-HDFS copy support, as well as Hadoop connectivity improvements, including ARVO file format support and Kerberos authentication support.

New in Microsoft SQL Server 2016 CTP 3.1 (Dec 1, 2015)

  • SETUP CTP 3.1:
  • Analysis Services upgrade:
  • CTP 3.1 includes a partial fix for the upgrade bug in the previous CTP. Upgrading from 3.0 to 3.1 is no longer blocked by older Analysis Services client libraries, but upgrading from 2.x is still a problem. See Setup CTP 3.0 for recommendations on how to proceed.
  • DATABASE ENGINE CTP 3.1:
  • Reconnect databases configured for Stretch Database after upgrade from CTP 2.x to CTP 3.x:
  • After you upgrade from CTP 2.x to CTP 3.x, you have to reconnect databases that you previously configured for Stretch Database by running the stored procedure sys.sp_rda_reauthorize_db for each database. For more info, see sys.sp_rda_reauthorize_db (Transact-SQL)
  • Inconsistent nonclustered index on a table with clustered columnstore index:
  • Issue: This issue is resolved in CTP 3.1 and might still exist if you upgraded from CTP 3.0. In CTP 3.0, if you had a table with a clustered columnstore index (CCI) and a multi-column key nonclustered index (NCI), and you ran multiple updates to the same row, you might get inconsistent results in CTP 3.1 when you access the table through the NCI. You can get the correct results by accessing the table through the CCI.
  • Detection: To detect this index inconsistency run DBCC CHECKTABLE () with extended_logical_checks.
  • Workaround: Drop and re-create the NCI if it is corrupted.
  • Applies to: SQL Server 2016 CTP2.4 or CTP3.0
  • Data corruption with clustered columnstore index on readable secondary in AlwaysOn configuration:
  • Issue: This issue is resolved in CTP 3.1 and might still exist if you upgraded from CTP 3.0. In CTP 3.0, if your database had a columnstore index in the AlwaysON configuration with a readable secondary and you have offloaded the reporting workload using the columnstore index on the secondary replica, you could have data corruption on the secondary replica. This corruption could have occurred if you queried any of the columnstore index-based dynamic management views (DMVs) or catalog views on the secondary replica
  • Detection: You can detect the corruption by running DBCC CHECKTABLE ()
  • Workaround: If you detect the corruption and your database is not corrupt, first disable the readable secondary, and then restart the readable secondary. Perform a manual failover and then restart the new secondary replica. These steps are will remove a corrupt internal cached counter
  • Applies to: SQL Server 2016 CTP3.0 and earlier
  • REPORTING SERVICES CTP 3.1:
  • ANALYSIS SERVICES (SSAS) CTP 3.1:
  • This release adds several features and provides fixes for known issues in previous the previous CTP.
  • Roles supported in SQL Server Data Tools (SSDT):
  • When creating a tabular model at compatibility level 1200, you can now create roles within the model itself in SSDT. This release removes a restriction previously called out in CTP 3.0.
  • Partition processing and merging of Tabular 1100 or 1033 model databases in SSMS:
  • You can now process or merge partitions for tabular databases at any compatibility level in SSMS
  • Upgrade 1100 or 1103 Tabular models to 1200 compatibility level:
  • You can now upgrade an 1100 or 1103 model to 1200 compatibility level, except when it contains pasted data. To upgrade, switch the compatibility level property to 1200 in either SSMS or SSDT.
  • Tabular 1100/1103 limitations in CTP 3.1 (MDX limitations):
  • Tabular models at the 1100 or 1103 compatibility level are fully functional with the exception of a few MDX limitations which are described below. Several limitations called out in CTP 3.0 are now gone. In CTP 3.1, the list of known limitations is reduced to the following:
  • No session-scope MDX statements (named sets, calculated members, calculated cells, visual totals, default members, and so forth), but you can use query-scope constructs, such as the 'WITH' clause.
  • No drillthrough support.
  • The CTP 3.0 section explicitly calls out several unsupported MDX features for Tabular 110x models, implying that they might be supported in the future. In fact, the following features that were not supported in the previous release will remain unavailable in SQL Server 2016:
  • Unqualified MDX member names are not supported in DirectQuery Tabular 110x models. When in DirectQuery mode, you need to specify the full object name in MDX to avoid name resolution errors. By full object name, an unqualified name like [Canada] would need to be expressed as [Customer].[Country].[Canada].
  • User-defined hierarchies are not supported in DirectQuery Tabular 110x models.
  • Tabular 1200 limitations in CTP 3.1:
  • The following limitations still exist in CTP 3.1 for Tabular 1200 models but are expected to be lifted in a future CTP:
  • DirectQuery implementation for Tabular 1200 models. This will be a new set of behaviors that specifically target the 1200 compatibility level. This feature is not yet available in CTP 3.1.
  • AMO is still not supported for actions on objects below Database.
  • XMLA is still not supported for actions on objects below Database .
  • The Deployment wizard is still not supported.
  • PowerShell scripts are still not supported.
  • SSIS tasks and transforms against Tabular 1200 models are still not supported.
  • Setting reporting properties is still not supported .
  • Pasted tables are still not supported.

New in Microsoft SQL Server 2016 CTP 3.0 (Oct 29, 2015)

  • Includes new innovations for mission-critical performance with In-Memory OLTP and real-time Operational Analytics, first in-market Always Encrypted, built-in SQL Server R Services, JSON support, and federated query from relational to Hadoop with PolyBase, and active archive of cold data to Azure with Stretch Database. This preview also includes new Business Intelligence (BI) capabilities for SQL Server Analysis Services and SQL Server Reporting Services, and we plan to include mobile BI capabilities in the coming months to deliver end-to-end BI solutions for on-premises implementations.
  • SQL Server, an industry leader, now packs an even bigger punch:
  • In the recent Gartner Magic Quadrant for Operational Database Management Systems Microsoft is positioned as a leader, highest in execution and furthest in vision. SQL Server 2016 builds on this leadership, and will come packed with powerful built-in features. As the least vulnerable database for six years in a row, SQL Server 2016 offers security that no other database can match. It also has the data warehouse with the highest price-performance, and offers end-to-end mobile BI solutions on any device at a fraction of the cost of other vendors. It provides tools to go beyond BI with in-database Advanced Analytics, integrating the R language and scalable analytics functions from our recent acquisition of Revolution Analytics.
  • Our cloud-first product development model means that new features get hardened at scale in the cloud, delivering proven on-premises experience. In addition, we offer a consistent experience across on-premises and cloud with common development and management tools and common T-SQL.
  • Security with Always Encrypted:
  • The Always Encrypted feature in SQL Server 2016 CTP 3.0, an industry-first, is based on technology from Microsoft Research and helps protects data at rest and in motion. Using Always Encrypted, SQL Server can perform operations on encrypted data and – best of all – the encryption key resides with the application in the customers’ trusted environment. It offers unparalleled security.
  • Mission Critical Performance:
  • With an expanded surface area, you can use the high performance In-Memory OLTP technology in SQL Server with a significantly greater number of applications. We are excited to introduce the unique capabilities of combine in-memory analytics (columnstore) with in-memory OLTP and traditional relational store in the same database to achieve real-time operational analytics. We have also made significant performance and scale improvements across all components in the SQL Server core engine.
  • Insights on All Your Data:
  • You’ll find significant improvements in both SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS) that help deliver business insights faster and improve productivity for BI developers and analysts. The enhanced DirectQuery enables high-performing access to external data sources like SQL Server Columnstore. This capability enhances the use of SSAS as a semantic model over your data for consistency across reporting and analysis without storing the data in Analysis Services.
  • SQL Server Reporting Services 2016 offers a modernized experience for paginated reports and updated tools as well as new capabilities to more easily design stunning documents. To get more from your investments in SSRS and to provide easy access to on-premises reports to everyone in your organization, you can now pin paginated reports items to the Power BI dashboard. In coming months, we will add new Mobile BI capabilities to Reporting Services, allowing you to create responsive, interactive BI reports optimized for mobile devices.
  • PolyBase, available today with the Analytic Platform System, is now built into SQL Server, expanding the power to extract value from unstructured and structured data using your existing T-SQL skills. PolyBase CTP 3.0 improvements including better performance and scale out PolyBase nodes to use other SQL Server instances.
  • Advanced Analytics:
  • CTP 3.0 introduces a new workload for Advanced Analytics with built-in SQL Server R Services. SQL Server R Services bridges the gap between data scientists and DBAs by enabling you to embrace the highly popular open source R language in SQL Server to build intelligent applications and discover new insights about your business. The SQL Server database is the best place to run Advanced Analytics because you can leverage industry leading technologies such as the In-Memory Columnstore and Parallelized R Services for fast predictive in-database analytics.
  • SQL Developers can learn R skills and build intelligent applications, while Data Scientists can leverage powerful SQL Database tools to create value through predictive and prescriptive enhancements to their applications. SQL Server 2016 enables intelligent applications to be built by hosting analytical models in the database, while reducing complexity and overall costs by moving expensive analytic computations close to the data.

New in Microsoft SQL Server 2016 CTP 2.4 (Oct 1, 2015)

  • Operational Analytics and In-Memory Data Warehouse improvements in this release include:
  • Frictionless operational analytics. Existing nonclustered columnstore indices (NCCI) are updateable without requiring index rebuild.
  • Parallel Index build of nonclustered columnstore index (NCCI). Starting with CTP 2.4, NCCI can now be built in parallel, leveraging available cores/logical processors available on the server for significant performance improvement.
  • INSERT operations performance in delta Rowgroup. Prior to CTP2.4, the delta rowgroup data was PAGE compressed, which added overhead for Insert operations. With CTP2.4, the data in delta rowgroup is no longer PAGE compressed.
  • Better query performance with nonclustered columnstore index (NCCI).
  • a. String predicate pushdown.
  • b. Improved query path when processing deleted rows by bypassing delete buffer when possible.
  • x86 server deprecation:
  • Starting with the CTP 2.4 release, we have removed x86 server from SQL Server 2016 starting with CTP2.4 release, although all the x86 client tools and components are still available. Extensive customer research has shown that the x86 server instance has no adoption in new server deployments, primarily because server hardware have evolved to be x64 only. At setup, if you select the x86 option, you will only be able to install client components
  • SQL Server Smart Maintenance Plans for effective database maintenance: Multi-TB SQL Server databases have become commonplace; at the same time, the global business economy demands increased database availability, consequently narrowing allowed maintenance windows. Frequently, the traditional plans of maintaining database no longer not fit inside customers’ narrow maintenance windows. Running past the maintenance window can cause poor application performance, which in turn can lead to ever-increasing infrastructure and operational costs for the customer and can negatively affect business efficiency. Smart Maintenance Plan integrates best practices, and can identify optimal strategies on the fly. Changes can be made to existing maintenance plan allow it to scale with the database.
  • Consistency-checking strategy: based on database and table sizes to distribute the task across different maintenance windows. More options are added to the user controlled consistency-checking tasks UI.
  • Backup strategy: Space an expected differential backup will consume to decide whether or not to run a differential or a full backup. Most options are surfaced in “Back up database task” UI for better usability.
  • Index and statistics maintenance strategy: based on index size/page count, degree of fragmentation, and usage frequency to decide what operations to perform. More options are exposed in REBUILD indexes task UI for better usability
  • Tempdb scalability improvement:
  • Tempdb scalability improvement creates multiple files during setup based on number of cores, this will reduce allocation contention on system pages in tempdb. The improvement also includes optimistic latching of system pages when dropping temp tables. SQL Server 2016 CTP 2.4 setup provides a separate tab for tempdb configuration. You can choose number of tempdb files, and customize initial and auto-growth size for tempdb files.
  • SQL Server Integration Services (SSIS) improvements in this release include:
  • Complex data feed including Data Streaming destination.
  • Balanced data distributor (BDD).
  • Excel 2013 source and destination (requires Access runtime 2013).
  • AlwaysOn support.
  • oData v4 source component in addition to oData v3.
  • SQL Server Reporting Services (SSRS) improvements in this release include:
  • Design-time control over layout of report parameters, including the ability to have more than two columns of parameters and to control the position of each parameter within the Parameters pane.
  • Export to PowerPoint.
  • A new printing feature that works across modern browsers and doesn’t require installing an ActiveX control.

New in Microsoft SQL Server 2016 CTP 2.3 (Sep 3, 2015)

  • Row Level Security support for in-memory OLTP tables
  • A core engine scalability improvement, dynamically partitioning thread safe memory objects by NUMA or CPU, which enables higher scalability
  • In-memory data warehouse (column tore) performance optimizations
  • Many-to-many derived hierarchies in Master Data Services
  • Several performance improvements to SQL Server Analysis Services, including DAX query performance
  • Enhancements to Reporting Services, including an updated Report Builder with a modern theme and report rendering for modern browsers

New in Microsoft SQL Server 2016 CTP 2.2 (Jul 23, 2015)

  • Users are able to add security policies including row-level security to a table that has been stretched to Azure using Stretch Database
  • Master Data Services now allows compound keys, slowly changing dimension (SCD) Type 2, and syncing an entity between models
  • SQL Server Reporting Services (SSRS) will now support two additional chart types, treemap and sunburst
  • The Stretch Database enables you dynamically stretch the cold transactional data to Azure SQL so your operational data is always at hand, no matter the size, and you can benefit from the low cost of using Azure. Improvements in this release include:
  • Row Level Security (RLS) enabled
  • Stretch Database Advisor now available to analyze existing database tables, discovering and evaluating candidates for stretch by adjustable table size thresholds
  • Bundled with SQL Server 2016 Upgrade Advisor Preview 1, Stretch Database Advisor is available for download here or through the Web Platform Installer
  • First released in SQL 2008 R2, the Master Data Services (MDS) is the SQL Server solution for master data management. We are making significant investments in SQL 2016 to advance strategic capabilities in the data management space. Improvements in this release include:
  • Sync entity between models allow you to setup sync relationship to sync entity from another model. Steps: go to Admin, Sync Entity page, click Add, choose target model, version and entity, source model, version and entity, choose sync type to be on-demand or auto sync, click Save.
  • SCD Type-2 support enables creation of SCD type-2 subscription view for Member transaction log type entities. Steps: go to Admin, Entity page, choose transaction log type; if transaction log type is Member, go to Integration, Create Views page, create a view of SCD Type-2 then History View can be chosen from format type.
  • Compound Keys index support includes custom attributes to improve the performance or enforce constraints. NOTE: In this release Entity Based Staging batches need to start by calling stored procedures directly instead of using the web UI. Steps: go to Admin, Entity page, click Add on Customer Indexes, choose the columns and click Save to create the index.

New in Microsoft SQL Server 2016 CTP 2.1 (Jun 25, 2015)

  • The release includes improvements for three new innovations releasing in SQL Server 2016 - Stretch Database, Query Store, Temporal - and Columnstore Index, introduced in SQL Server 2012:
  • Stretch Database, which enables transparent stretching of warm and cold OLTP data to Microsoft Azure in a secure manner without requiring any application changes, includes the following fixes:
  • Data migration does not trigger lock escalation in stretched tables, so no timeouts for INSERT or SELECT operations
  • Automatic encryption and validation requirement of remote server certification, preventing “man-in-the-middle” security attacks
  • Ability to run INSERT statement against updatable views created on top of stretch tables
  • Query Store, the “flight recorder” which stores historical query plans and their performance characteristics, allowing DBAs to monitor and analyze plans and force a specific query plan on regression, includes:
  • Parse statistics avg_parse_duration, last_parse_duration, avg_parse_cpu_time, last_parse_cpu_time removed from sys.query_store_query view
  • The minimally allowed for flush_interval_seconds parameter, 60 seconds, is now verified in ALTER DATABASE statement
  • Naming in sys.database_query_store_options and actual parameters in ALTER statements are in alignment with flush_interval_seconds and operation_mode
  • Query Store on master and tempdb disabled and error message thrown ("Cannot perform action because Query Store cannot be enabled on system database master (tempdb)")
  • Force_failure_count parameter is now cleared after plan is enforced
  • Temporal, which enables handling and analyzing database records that changes over time, includes:
  • Support for computed columns
  • Support for marking one or both period columns with HIDDEN flag, allowing for frictionless migration for existing applications. The following applies to HIDDEN period columns:
  • Column is not returned in SELECT * statements
  • INSERT statement without column list do not expect inputs for HIDDEN columns
  • Hidden column must be explicitly included in all queries that directly reference temporal table or other objects that reference temporal table (views, for example)
  • BULK INSERT scripts that worked with non-temporal table (prior to adding system-versioning and hidden period columns) will continue to work and hidden columns will be auto-populated
  • is_hidden flag is set to 1 in sys.columns view
  • Columnstore Index includes:
  • Improved seek performance
  • Improved scan performance with partitioned tables

New in Microsoft SQL Server 2016 CTP 2 (May 30, 2015)

  • SQL Server 2016 provides breakthrough performance for mission critical applications and deeper insights on your data across on-premises and cloud. Top capabilities for the release include: Always Encrypted - a new capability that protects data at rest and in motion, Stretch Database - new technology that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure, enhancements to our industry-leading in-memory technologies for real-time analytics on top of breakthrough transactional performance and new in-database analytics with R integration.
  • Unique in this release of SQL Server, we are bringing capabilities to the cloud first in Microsoft Azure SQL Database such as Row-level security and Dynamic Data Masking and then bringing the capabilities, as well as the learnings from running these at hyper-scale in Microsoft Azure, back to SQL Server to deliver proven features at scale to our on-premises offering. This means all our customers benefit from our investments and learnings in Azure.
  • Always Encrypted:
  • Always Encrypted, based on technology from Microsoft Research, protects data at rest and in motion. With Always Encrypted, SQL Server can perform operations on encrypted data and best of all, the encryption key resides with the application in the customers trusted environment. Encryption and decryption of data happens transparently inside the application which minimizes the changes that have to be made to existing applications.
  • Stretch Database:
  • This new technology allows you to dynamically stretch your warm and cold transactional data to Microsoft Azure, so your operational data is always at hand, no matter the size, and you benefit from the low cost of Azure. You can use Always Encrypted with Stretch Database to extend data in a more secure manner for greater peace of mind
  • Real-time Operational Analytics & In-Memory OLTP:
  • For In-Memory OLTP, which customers today are using for up to 30x faster transactions, you will now be able to apply this tuned transaction performance technology to a significantly greater number of applications and benefit from increased concurrency. With these enhancements, we introduce the unique capability to use our in-memory columnstore delivering 100X faster queries on top of in-memory OLTP to provide real-time operational analytics while accelerating transaction performance
  • Additional capabilities in SQL Server 2016 CTP2 include:
  • PolyBase – More easily manage relational and non-relational data with the simplicity of T-SQL
  • AlwaysOn Enhancements – Achieve even higher availability and performance of your secondaries, with up to 3 synchronous replicas, DTC support and round-robin load balancing of the secondaries
  • Row Level Security– Enables customers to control access to data based on the characteristics of the user. Security is implemented inside the database, requiring no modifications to the application
  • Dynamic Data Masking – Supports real-time obfuscation of data so data requesters do not get access to unauthorized data. Helps protect sensitive data even when it is not encrypted
  • Native JSON support – Allows easy parsing and storing of JSON and exporting relational data to JSON
  • Temporal Database support – Tracks historical data changes with temporal database support
  • Query Data Store – Acts as a flight data recorder for a database, giving full history of query execution so DBAs can pinpoint expensive/regressed queries and tune query performance
  • MDS enhancements – Offer enhanced server management capabilities for Master Data Services
  • Enhanced hybrid backup to Azure – Enables faster backups to Microsoft Azure and faster restores to SQL Server in Azure Virtual Machines. Also, you can stage backups on-premises prior to uploading to Azure

New in Microsoft SQL Server 2012 11.0.3000.00 SP1 (Nov 8, 2012)

  • Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade:
  • SQL Server 2012 SP1 introduces support for cross-cluster migration of AlwaysOn Availability Groups deployments to a new Windows Server Failover Clustering (WSFC) cluster. A cross-cluster migration moves one AlwaysOn availability group or a batch of availability groups to the new, destination WSFC cluster with minimal downtime. The cross-cluster migration process enables you to maintain your service level agreements (SLAs) when upgrading to a Windows Server 2012 cluster. SQL Server 2012 SP1 must be installed and enabled for AlwaysOn on the destination WSFC cluster. The success of cross-cluster migration depends on thorough planning and preparation of the destination WSFC cluster.
  • Selective XML Index:
  • SQL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads, and improve scalability by reducing storage costs of the index itself.
  • DBCC SHOW_STATISTICS works with SELECT permission:
  • In earlier releases of SQL Server, customers need administrative or ownership permissions to run DBCC SHOW_STATISTICS. This restriction impacted the Distributed Query functionality in SQL Server because, in many cases, customers running distributed queries did not have administrative or ownership permissions against remote tables to be able to gather statistics as part of the compilation of the distributed query. While such scenarios still execute, it often results in sub-optimal query plan choices that negatively impact performance. SQL Server 2012 SP1 modifies the permission restrictions and allows users with SELECT permission to use this command.
  • Note that the following requirements exist for SELECT permissions to be sufficient to run the command: Users must have permissions on all columns in the statistics object and Users must have permission on all columns in a filter condition (if one exists).
  • Customers using Distributed Query should notice that statistics can now be used when compiling queries from remote SQL Server data sources where they have only SELECT permissions. Trace flag 9485 exists to revert the new permission check to SQL Server 2012 RTM behavior in case of regression in customer scenarios.
  • New function returns statistics properties:
  • The dynamic management function sys.dm_db_stats_properties returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database. You can use this function to return information such as the last time the statistics object was updated for the table or indexed view, or the number of rows that were sampled for statistical calculations.
  • SSMS Complete in Express:
  • Customers who use the SQL Server Express With Tools, SQL Server Express COMP, SQL Server Express with Advanced Services and SQL Server Management Studio Express now have SQL Server Management Studio complete. These Express Editions now ship the SSMS feature with functionalities that exist with the SSMS from full editions of SQL Server.
  • SlipStream Full Installation:
  • Provides customers with a pre-built "Slipstream image" that consists of a compressed, self-extracting .exe and a '.box' payload file that contains a SQL Server 2012 RTM image (Setup.exe, MSI’s, etc.) along with the most recent Service Pack. When the package is executed, the two images are merged in real-time providing the user with a single Setup workflow experience, landing the operation (Install, Upgrade, AddNode, etc) at the encompassed SP1 level, executing any updates made to Setup components themselves in the Service Pack
  • Customers can perform new instance installations (or SQL Server 2008/2008 R2 upgrades) at the Service Pack functional level using a single click install workflow experience. This can be performed directly by utilizing the slipstream .exe from the command line just as they would use Setup.exe by supplying all appropriate setup command line parameters. This is in contrast to having to install RTM media, find and download the desired Service Pack (for the correct language and CPU), apply the Service Pack, then load Setup.exe to perform the desired setup action.
  • Business Intelligence highlights (with SQL Server 2012 SP1, Office and SharePoint Server 2013):
  • Enable self-service BI as a natural part of users day-to-day activities in Excel 2013.
  • Access and mash-up data from any source (PowerPivot).
  • Stunning visualizations and data discovery (Power View).
  • Work with hundreds of millions of rows of data (powered by xVelocity in-memory technologies).
  • Discover, assess and audit user created spreadsheets via SharePoint Server 2013 Preview.
  • A new version of the Reporting Services add-in for SharePoint and an updated SharePoint mode report server that supports SharePoint 2013.
  • A new architecture for SQL Server 2012 SP1 CTP4 PowerPivot that supports a PowerPivot server outside a SharePoint 2013 farm. A Windows Installer package (spPowerpivot.msi) that enhances the PowerPivot for SharePoint experience. Additional features include PowerPivot Gallery, schedule data refresh, and management dashboard.
  • Management Object Support Added for Resource Governor DDL:
  • In SQL Server 2012 SP1, SQL Server Management Objects are updated to support the syntax of CREATE RESOURCE POOL (Transact-SQL):
  • CAP_CPU_PERCENT
  • AFFINITY SCHEDULER
  • AFFINITY NUMANODE
  • The following management objects limit CPU usage and affinitize resource pools to schedulers and NUMA nodes:
  • CapCpuPercentage
  • AffinityInfoBase
  • ResourcePoolAffinityInfo
  • Scheduler
  • SchedulerCollection

New in Microsoft SQL Server 2008 SP2 10.50.3720.0 CTP (May 15, 2012)

  • FIX: Lots of messages that have message ID 19030 and message ID 19031 are logged in SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2 Errorlog file when you use SQL Server Profile in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2
  • SQL Server 2008 Service Pack 2 and SQL Server 2008 R2 Service Pack 2 enhancements to the "Operation has been cancelled" error message text in Analysis Services
  • FIX: Deadlock might occur in sys.dm_database_encryption_keys DMV in SQL Server 2008 R2 when you run log shipping from many databases to one secondary server
  • FIX: You may receive incorrect results when you run a complex query that contains aggregates functions, join functions, and distinct functions in subqueries in a SQL Server 2008 or SQL Server 2008 R2 environment
  • FIX: Slipstream installation of a SQL Server 2008 or SQL Server 2008 R2 service pack fails when you add new features to the SQL instance if database engine is installed
  • FIX: You cannot connect to SQL Server by using JDBC Driver for SQL Server after you upgrade to JRE 6 update 29 or a later version
  • The fix is related to usage of BindParameter method and the reference of long type parameters that are used by this method.
  • The fix is to update UpgradeIncompleteFeatureConfigEventHandler.cs to include RebuildDatabase scenario when you reset ConfigurationState.
  • For document libraries with the “Opening Documents in the Browser” setting set to “Open in the client application” GetSnapshot.exe ignore the configuration and always displays the workbook by using Excel Services as if the setting was “Open in the browser”.
  • FIX: On a Russian Localized build of the Reporting Services Configuration Manager, Execution Account information that is contained clipped strings.
  • The ExpectedCount and ExpectedElapsedMs usage counts are updated to reflect the actual counts respectively.
  • Fixes an issue in which SQL Server 2008 R2 Analysis Services crashes during the database synchronization.
  • Trace flag 1264 is removed and so that the process name is collected by default in non-yielding scenario dumps. The number of thread records in non-yielding scenario dump files is increased from 1K to 16K.
  • SQL Server 2008 R2 changes the FireAgain parameter to True if an event should be logged. However, after some codes are removed by mistake, some logs are missing because of the FireAgain parameter is only determined by the external event listener. After you apply this hotfix, the issue is resolved.
  • Consider the following scenario. You have indexed view projects columns from base and non-base tables in SQL Server 2008 R2. A column from the non-base table has the same order id and type as a column of the base table. In this scenario, SQL Server 2008 R2 switches the partitions. After you apply the hotfix, SQL Server 2008 R2 experiences an error instead of switches the partitions.
  • Fixes an issue in which an object that is allocated on a SOS_Task PMO could be also used by other TASK which has a different life span. This service pack uses the correct PMO for allocating the object.
  • After you apply the hotfix, the optimizer understands that CRYPT_GEN_RANDOM can return different outputs for the same input.
  • Fixes an issue in which an incorrect value is returned when you run the SCOPE_IDENTITY().
  • Fixes an issue in which the SQL Server 2008 R2 setup crashes when you install SxS with Shiloh OLAP SP3a. This issue occurs because the Shiloh OLAP SP3a incorrectly writes the version registry key as "Service Pack 3.0."
  • Fixes an issue in which an incorrect CPU time is reported by sys.dm_exec_query_stats when parallel query plans are involved.
  • Fixes an issue that occurs when internal asynchronous file reading buffers are splitting the multi-character column delimiter in two parts. The fix is to correctly reset the current parsing pointer after buffers are switched.
  • The fix will only use the synchronization if the SQLWixCompilerExtension operation runs in candle.
  • This fix changes the HierarchyGet operation to avoid a certain code path for the explicit cap that only applies to the derived part of the hierarchy. The update is a one-line update that is low risk.
  • Fixes an issue in which you receive errors in a statement when purging from snapshots.notable_query_text.
  • Bad GUID values parsed from flat files could cause errors. The fix is to fall back into a truncation mode if the source buffer is bigger than max size of a GUID string.

New in Microsoft SQL Server 2012 11.0.2100.60 (Apr 7, 2012)

  • SQL Server AlwaysOn, a new high availability solution delivering increased application availability, lower TCO and ease of use.
  • xVelocity, in-memory technologies significantly boost data warehouse and analytics performance by up to 100x.
  • Power View, a web-based, highly interactive, data visualization and presentation solution designed to enable business users and decision makers to quickly discover meaningful insights from their data.
  • Data integration and management, new and enhanced tools to deliver credible, consistent data to the right users at the right time, including new SQL Server Data Quality Services and enhanced Master Data Services.
  • Enhanced PowerPivot functionalities enable customers to leverage new advanced analytic capabilities and further ease of use while still working within the familiar tools provided by Excel.
  • The new Business Intelligence (BI) Semantic Model provides a single, scalable model for BI applications, from reporting and analysis to dashboards and scorecards.
  • SQL Server Data Tools, a new tool that unifies SQL Server and cloud SQL Azure development for both professional database and application developers.

New in Microsoft SQL Server 11.0.1750.32 RC 0 (Nov 18, 2011)

  • Added core capabilities for increased mission critical confidence, including multiple secondaries in SQL Server AlwaysOn and multi-tenancy enhancements with Resource Governor.
  • New functionality in the Power View (”Crescent”) data-exploration and visualization tool, including the inclusion of Change Data Capture support for Oracle enables broader support for heterogeneous data with Data Warehouses.
  • Snapshot backups in Windows Azure from inside Management Studio.

New in Microsoft SQL Server Service Pack 2 (Sep 30, 2010)

  • SQL Server Utility. After you apply SP2, an instance of the SQL Server 2008 Database Engine can be enrolled with a utility control point as a managed instance of SQL Server. For more information, see Overview of SQL Server Utility in SQL Server 2008 R2 Books Online.
  • Data-tier Application (DAC). Instances of the SQL Server 2008 Database Engine support all DAC operations after SP2 has been applied. You can deploy, upgrade, register, extract, and delete DACs. SP2 does not upgrade the SQL Server 2008 client tools to support DACs. You must use the SQL Server 2008 R2 client tools, such as SQL Server Management Studio, to perform DAC operations. A data-tier application is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.
  • Reporting Services in SharePoint Integrated Mode. SQL Server 2008 SP2 provides updates for Reporting Services SharePoint integration. SQL Server 2008 SP2 report servers can integrate with SharePoint 2010 products. SQL Server 2008 SP2 also provides a new add-in for SharePoint 2007 products. The new add-in supports the integration of SharePoint 2007 products with SQL Server 2008 R2 report servers. For more information see the “What’s New in SharePoint Integration and SQL Server 2008 Service Pack 2 (SP2)” section in What's New (Reporting Services).

New in Microsoft SQL Server R2 RTM (Apr 21, 2010)

  • PowerPivot for SharePoint:
  • PowerPivot for SharePoint adds shared services and infrastructure for loading, querying, and managing PowerPivot workbooks that you publish to a SharePoint 2010 server or farm. To create PowerPivot workbooks, you use PowerPivot for Excel.
  • PowerPivot for Excel:
  • PowerPivot for Excel is an add-in to Excel 2010 that can be downloaded from the web and installed on client workstations. You use PowerPivot for Excel to assemble and create relationships in large amounts of data from different sources, and then use that data as the basis for PivotTables and other data visualization objects that support data analysis in Excel.
  • Multi-Server Administration and Data-Tier Application:
  • The SQL Server Utility forms a central repository for performance data and management policies that tailor the operation of instances of the Database Engine that have been enrolled in the utility. It also includes a Utility Explorer for centralized management, and dashboards that report the state of the managed instances. A data-tier application (DAC) forms a single unit for developing, deploying, and managing the database objects used by an application.
  • Master Data Services:
  • Master Data Services is comprised of a database, configuration tool, Web application, and Web service that you use to manage your organization's master data and maintain an auditable record of that data as it changes over time. You use models and hierarchies to group and organize data to prepare it for further use in business intelligence and reporting tools, data warehouses, and other operational systems. Master Data Services integrates with source systems and incorporates business rules to become the single source of master data across your organization.
  • Features Supported by the Editions of SQL Server 2008 R2:
  • The largest database supported by SQL Server Express has been increased from 4 GB to 10 GB.
  • Connecting to the Database Engine Using Extended Protection:
  • SQL Server now supports Extended Protection, using service binding and channel binding to help prevent an authentication relay attack. Also, see Extended Protection for Authentication with Reporting Services.

New in Microsoft SQL Server R2 November CTP (Nov 12, 2009)

  • Building on the trusted, productive, and intelligent data platform delivered with SQL Server 2008, The Microsoft SQL Server 2008 R2 November Community Technology Preview (CTP) provides a wealth of new improvements that will help your organization scale with confidence, improve IT efficiency, and enable managed self-service BI. This CTP release includes new and enhanced capabilities for application and multi-server management, complex event processing, master data services, and end user reporting. Also introduced is PowerPivot for Microsoft Excel (previously known as project “Gemini’) to empower users to easily access, integrate, analyze and share information using Microsoft Office tools they are already familiar with.
  • Key features in this CTP release include:
  • A new Control Point Explorer for central multi-instance and application utilization management.
  • Built-in wizards to help you quickly set up and enroll instances and Data-Tier Application components into central management.
  • Dashboard viewpoints for quick insight into application and instance utilization.
  • Report Builder 3.0
  • PowerPivot for Microsoft® Excel
  • Master Data Services
  • StreamInsight core technology engine.