logobossally

Microsoft has announced the availability of SQL Server 2016 Service Pack 2 (SP2) recently. Currently, SP2 is only available for download separately and it will be offered in Visual Studio subscriptions, MBS/Partner Source and VLSC soon.

1. Sql Server Service Pack Vs Cumulative Update
2. Current Version Of Sql Server

SQL Server and Updates Builds Numbers Article History SQL Server and Updates Builds Numbers. Table of Contents. SQL Server Release Dates; SQL Server 2000. SQL Server 2000 Service Pack 1. SQL Server 2008 Service Pack 1: 10.0.2531.0:: KB968369: Cumulative update package 1 for SQL Server 2008 Service Pack 1: 10.0.2710:. Because service packs include fixes that often affect performance (among other things) you should upgrade your SQL Servers with the most recent service pack when it becomes available. In some cases, service packs affect how the Query Optimizer performs. Because of this, some queries that were once slow may now be faster.

Service Pack 2 includes all the patches since SQL Server 2016 SP1 plus performance improvements, diagnostic additions. SP1 was released back in 2016 so you will see a lot of improvement in this release.

Quick Summary

• 2 How to get SQL Server 2016 SP2?
• 3 FAQs

Changes made in SQL Server 2016 SP2

Generally speaking, Microsoft has worked a lot over server and database performance. Some of the enhancement include the following:

• 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.
• Support 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.

If you want to go through the complete change log, you may check out SQL Server SP2 release notes here.

How to get SQL Server 2016 SP2?

You can download the Service Pack from below mentioned links. Installation is not an issue. Just run the downloaded file SQLServer2016SP2-KB4052908-x64-ENU.exe (For English) on the system where SQL Server 2016 SP1 is installed. Follow the setup instructions and you’ll be good to go.

It’s always recommended to backup configuration and databases of the server before applying the new changes.

Download SQL Server 2016 Service Pack 2 Standard/Enterprise

Download SQL Server 2016 SP2 English [774 MB]

Download SQL Server 2016 SP2 French [783 MB]

Download SQL Server 2016 SP2 German [783 MB]

Download SQL Server 2016 SP2 Italian [783 MB]

Download SQL Server 2016 SP2 Japanese [782 MB]

Download SQL Server 2016 SP2 Korean [782 MB]

Download SQL Server 2016 SP2 Portuguese (Brazil) [783 MB]

Download SQL Server 2016 SP2 Russian [783 MB]

Download SQL Server 2016 SP2 Spanish [783 MB]

FAQs

Some frequently ask questions that I have been looking at in different forums. I thought I should compile them into an FAQ section for your ease.

Q: When was SQL Server 2016 Service Pack 2 released?

Answer: SQL Server 2016 SP2 was released on 1st May 2018.

Q: Is SQL Server 2016 a cumulative update?

Answer: Yes SP2 is a cumulative update but you may be required to install at least SP1 before installing SP2. There is no need to install any other update.

Q: What about SQL Server 2016 Service Pack 3?

Answer: There is no news about SQL Server 2016 SP3. It may be released after one year or so. From SQL Server 2017, Microsoft has discontinued the Service Pack model.

Q:What is the difference between Hotfix, Cumulative Update and Service Pack?

Answer:Hotfix is released to fix a specific issue. A single hotfix can be used to resolve multiple issues related to a single scenario.

A Cumulative Update (CU) is a pack of all the previous hotfixes since the last cumulative update. This means if you have not installed all the hotfixes, a cumulative update installation will install those hotfixes for you.

A Service pack is a tested cumulative update of all the hotfixes and cumulative updates since the last service pack. Normally, service pack also contains new features and enhancements in addition to security fixes.

Q: Where can I download the feature pack for SQL Server 2016 SP2 in my preferred language?

Answer:Download SQL Server 2016 SP2 feature pack

If there is any other question related to the service pack, please let us know through comments below.

Must Read Articles:

SQL Server Product Team is excited to bring you the final release of SQL Server 2012 Service Packs, Service Pack 4 (SP4). This release of SQL 2012 Service Pack has 20+ improvements centered around performance, scalability and diagnostics based on feedback from customers and the SQL Server community. These improvements enable SQL Server 2012 to perform faster and scale out of the box on modern hardware design.

In addition to improvements listed below, SQL Server 2012 SP4 includes all the fixes up to and including SQL Server 2012 SP3 CU10.

Following is the detailed list of improvements introduced in SQL Server 2012 SP4.

Performance and Scalability Improvements

• Dynamic Memory Object Scaling – Dynamically partition memory object 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 if it becomes a bottleneck. Unpartitioned 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).
• Enable >8TB for Buffer Pool – Enabled 128TB Virtual address space for buffer pool usage. This improvement enables SQL Server Buffer Pool to scale beyond 8TB on modern hardware.
• Automatic Soft NUMA partitioning – When Trace Flag 8079 is enabled during startup, SQL Server 2012 SP4 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 of workload with Auto-Soft NUMA before it is turned ON in production.
• Improved Distribution agent cleanup procedure – An oversized distribution database tables caused blocking and deadlock situation. An improved cleanup procedure aims to eliminate some of these blocking or deadlock scenarios.
• Change Tracking Cleanup – A new stored procedure sp_flush_CT_internal_table_on_demand is introduced to cleanup change tracking internal tables on demand.

Supportability and Diagnostics Improvements

Sql Server Service Pack Vs Cumulative Update

• Full Dumps support for Replication Agents – Today if replication agents encounter an unhandled exception, the default is to create a mini dump of the exception symptoms. This makes troubleshooting unhandled exception issues very difficult. Through this change we are introducing a new Registry key, which would allow to create a full dump for Replication Agents. You need to add a new DWORD key – ReplAgentFullDump with data as 1 in HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerXXX registry hive (XXX refers to SQLGLOBAL_VERSION for example, 110 for SQL 2012). After this change, when the replication agent encounters an unhandled exception it will generate a full dump instead of minidump.
• Extended diagnostics in showplan XML – Showplan XML has been extended to expose information about enabled trace flags, memory fractions for optimized nested loop join, CPU time and elapsed time.
• Better correlation between diagnostics Extended Event and DMVs – This improvement addresses connect feedback (1934583). Query_hash and query_plan_hash fields are used for identifying a query uniquely. DMV defines them as varbinary(8), while Extended Event defines them as UINT64. Since SQL server does not have “unsigned bigint”, casting does not always work. This improvement introduces new Extended Event action/filter columns equivalent to query_hash and query_plan_hash except they are defined as INT64 which can help correlating queries between Extended Event and DMVs.
• Better memory grant/usage diagnostics – New query_memory_grant_usage Extended Event.
• Add protocol tracing to SSL negotiation steps – Add bid trace information for successful/failed negotiation, including the protocol etc. This can be useful when troubleshooting connectivity scenarios while, for example, deploying TLS 1.2.
• Setting correct compatibility level for distribution database – After Service Pack Installation the Distribution database compatibility level changes to 90. This was because of a code path in sp_vupgrade_replication stored procedure. The SP has now been changed to set the correct compatibility level for the distribution database.
• Database Cloning – Clone database is a new DBCC command added that allows Microsoft CSS to troubleshoot 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’). Cloned databases should not be used in production environments. To see if a database has been generated from a clonedatabase you can use the following command, 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 indicating the number of tempdb files and notifying different size/autogrowth of tempdb data files at server startup.
• Database Instant File Initialization Logging – A new errorlog message indicating that Database Instant File Initialization is enabled/disabled at server startup.
• 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.
• Extended Events enhancement for read routing failure for an Availability Group – Currently the read_only_route_fail Extended Event only gets fired if there is a routing list present, but none of the servers in the routing list are available for connections. In this improvement, we are including additional information to assist with troubleshooting and also expand on the code points where this Extended Event gets fired.
• Proper handling of Service Broker with Availability group failover – In the current implementation when Service Broker is enabled on an Availability Group Databases, during an AG failover all Service broker connections which originated on the Primary Replica are left open. This improvement targets to close all such open connections during an AG failover.
• Programmatically identify LPIM to SQL service account – New sql_memory_model, sql_memory_model_desc columns in DMV sys.dm_os_sys_info to allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time.
• Programatically identify IFI privilege to SQL service account – New column instant_file_initialization_enabled in DMV sys.dm_server_services to allow DBAs to programmatically identify if Instant File initialization (IFI) is in effect at the SQL Server service startup.

We will be updating and adding follow-up posts on the Tiger blog in the coming weeks to describe some of the above improvements in detail.

We are releasing this service pack after mainstream support ended in July, 2017. To learn more about Microsoft Support Lifecycle Policy, please read here. Please refer to the FAQ below for further details on SP3 and SP4 support.

Current Version Of Sql Server

The SQL Server 2012 SP4 installation may require reboot post installation. As a best practice, we recommend to plan and perform a reboot following the installation of SQL Server 2012 SP4.

The Service Pack is available for download on the Microsoft Download Center and will also be made available on Azure Images Gallery, MSDN, MBS/Partner Source, and VLSC in the coming weeks. As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2012 deployments.
To obtain SQL Server 2012 SP4, please visit the links below:
SQL Server 2012 SP4
SQL Server 2012 SP4 Feature Pack
SQL Server 2012 Service Pack 4 Release Information

Thank you,

Microsoft SQL Server Engineering Team