SQL Updates Newsletter – December 2016

Recent Releases and Announcements

• GDR update package for SQL Server 2016 SP1
  • https://support.microsoft.com/en-us/kb/3210089
• GDR update package for SQL Server 2016 RTM
  • https://support.microsoft.com/en-us/kb/3210111
• On-demand hotfix update package for SQL Server 2016 CU3
  • https://support.microsoft.com/en-us/kb/3210110
• Cumulative Update #10 for SQL Server 2014 SP1
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/cumulative-update-10-for-sql-server-2014-sp1/
• Cumulative Update #3 for SQL Server 2014 SP2
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/cumulative-update-3-for-sql-server-2014-sp2/
• SQL Server v.Next CTP 1.1 is here!
  • Microsoft is excited to announce that the next version of SQL Server (SQL Server v.Next) Community Technology Preview (CTP) 1.1 is now available for download on both Windows and Linux.
  • https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/16/sql-server-next-version-community-technology-preview-1-1-now-available/
  • Download: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-vnext-ctp
• Introducing SQL Server Premium Assurance
  • https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/08/introducing-sql-server-premium-assurance/
  • Microsoft is announcing two new offerings to help you run applications longer without disruption: Windows Server Premium Assurance and SQL Server Premium Assurance. These offerings add six more years of product support for Windows Server and SQL Server versions 2008 and newer–for up to 16 years of total product support.
• Released: Public Preview for Microsoft Azure SQL Database Management Pack (6.7.11.0)
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/released-public-preview-for-microsoft-azure-sql-database-management-pack-6-7-11-0/
• Using SQL Server 2016 with R Services for Campaign Optimization
  • https://blogs.technet.microsoft.com/machinelearning/2016/12/07/using-sql-server-2016-with-r-services-for-campaign-optimization/
  • We are happy to announce a new Campaign Optimization solution based on R Services in SQL Server 2016, designed to help customers apply machine learning to increase response rates from their leads.
• We are making Microsoft R Server 9.0 immediately available for download from MSDN and Visual Studio Dev Essentials.
  • https://blogs.technet.microsoft.com/machinelearning/2016/12/07/introducing-microsoft-r-server-9-0/
  • MRS 9.0 now supports Spark 2.0, in addition to Spark 1.6, and also adds support for Ubuntu, complementing our support for SUSE and RedHat Linux.
  • We are also introducing a more powerful version of the R Client
• Announcing SQL Server Management Studio – 16.5.1 Release
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-sql-server-management-studio-16-5-1-release/

Recent Whitepapers/E-books/Training/Tutorials

• Getting started with PHP, Java, and SQL Server on Linux
  • https://channel9.msdn.com/Shows/Visual-Studio-Toolbox/Getting-started-with-PHP-Java-and-SQL-Server-on-Linux
• SQL Server on Linux: The HOW
  • https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-on-Linux-The-HOW
• Introducing the Database Experimentation Assistant
  • https://channel9.msdn.com/Shows/Data-Exposed/Introducing-the-Database-Experimentation-Assistant

Monthly Script Tips

• [Script Of Dec. 29] How to determine Microsoft products versions through scripts
  • https://blogs.technet.microsoft.com/onescript/2016/12/29/script-of-dec-29-how-to-determine-microsoft-products-versions-through-scripts/
• [Script Of Dec. 23] How to create a shortcut to run an application as a different user by PowerShell
  • https://blogs.technet.microsoft.com/onescript/2016/12/23/script-of-dec-23-how-to-create-a-shortcut-to-run-an-application-as-a-different-user-by-powershell/
• [Script Of Dec. 7] How to use SQL Database In-Memory technologies to boost your store procedure
  • https://blogs.technet.microsoft.com/onescript/2016/12/07/script-of-dec-7-how-to-use-sql-database-in-memory-technologies-to-boost-your-store-procedure/
  • The sample code demonstrates how to use SQL Database In-Memory technologies to boost your store procedure.
• [Sample Of Dec. 8] How to connect to SQL Server database in Node.js using tedious
  • https://blogs.msdn.microsoft.com/onecode/2016/12/08/sample-of-dec-8-how-to-connect-to-sql-server-database-in-node-js-using-tedious/

Issue Alert

• Critical: Do NOT delete files from the Windows Installer folder. C:\windows\Installer is not a temporary folder and files in it should not be deleted. If you do it on machines on which you have SQL Server installed you may have to rebuild the operating system and reinstall SQL Server.
  • https://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/30/do-not-delete-files-from-the-windows-installer-folder.aspx
• Critical: Please be aware of a critical Microsoft Visual C++ 2013 runtime pre-requisite update that may be required on machines where SQL Server 2016 will be, or has been, installed.
  • https://blogs.msdn.microsoft.com/sqlcat/2016/07/28/installing-sql-server-2016-rtm-you-must-do-this/
  • If KB3164398 or KB3138367 are installed, then no further action is necessary. To check, run the following from a command prompt:
    • powershell get-hotfix KB3164398
    • powershell get-hotfix KB3138367
  • If the version of %SystemRoot%\system32\msvcr120.dll is 12.0.40649.5 or later, then no further action is necessary. To check, run the following from a command prompt:
    • powershell "get-item %systemroot%\system32\msvcr120.dll | select versioninfo | fl"
• Important: Description of the Update Cache folder in SQL Server
  • If the Update Cache folder or some patches are removed from this folder, you can no longer uninstall an update to your SQL Server instance and then revert to an earlier update build. In that situation, Add/Remove Programs entries point to non-existing binaries, and therefore the uninstall process does not work. Therefore, Microsoft strongly encourages you to keep the folder and its contents intact.
  • https://support.microsoft.com/en-us/kb/3196535
• Important: You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
  • https://support.microsoft.com/kb/239530
• Important: Default auto statistics update threshold change for SQL Server 2016
  • https://blogs.msdn.microsoft.com/psssql/2016/10/04/default-auto-statistics-update-threshold-change-for-sql-server-2016/
  • SQL Server 2016:  Default is new threshold if database compatibility level is 130.  If database compatibility  is below 130, old threshold is used (unless you use trace flag 2371)
• Unable to connect to SQL Server AlwaysOn Listener on Azure due to misconfigured subnet mask
  • https://blogs.msdn.microsoft.com/psssql/2016/12/04/unable-to-connect-to-sql-server-alwayson-listener-on-azure-due-to-misconfigured-subnet-mask/
• Msg 13575 OR Msg 13597 error message while adding period columns
  • https://blogs.msdn.microsoft.com/vikas_rana/2016/11/29/msg-13575-or-msg-13597-error-message-while-adding-period-columns/
• Error when you execute SSIS package on FIPS-enabled Windows
  • https://support.microsoft.com/en-us/kb/2925865
• Interoperability of Columnstore indexes with large page memory model in SQL Server
  • https://support.microsoft.com/en-us/kb/3210239
• “A digitally signed driver is required” warning when you install SQL Server packages in Windows Server 2016 and Windows 10
  • https://support.microsoft.com/en-us/kb/3203693

Recent Blog Posts and Articles

• What’s new for SQL Server vNext on Windows CTP 1.1 for Analysis Services
  • https://blogs.msdn.microsoft.com/analysisservices/2016/12/16/whats-new-for-sql-server-vnext-on-windows-ctp-1-1-for-analysis-services/
• SQL Server on Linux: How? Introduction
  • https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/16/sql-server-on-linux-how-introduction/
  • Making SQL Server run on Linux involves introducing what is known as a Platform Abstraction Layer (“PAL”) into SQL Server.
  • …it was decided to marry parts of the Microsoft Research (MSR) project Drawbridge with SQL Server’s existing platform layer SQL Server Operating System (SOS) to create what we call the SQLPAL.
  • Drawbridge was an Microsoft Research project … that focused on drastically reducing the virtualization resource overhead incurred when hosting many Virtual Machines on the same hardware.
• Using SQL Tools with SQL Server on Linux
  • https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/01/using-sql-tools-with-sql-server-on-linux/
  • We’ve created Linux-native versions of your favorite SQL command line tools such as sqlcmd and bcp and sqlpackage and also added the new mssql-conf tool that lets you configure various properties for the SQL Server instance on Linux.
  • We have released updated versions (v17.0 RC1) of our flagship SQL Server tools including SQL Server Management Studio (SSMS), Visual Studio SQL Server Data Tools (SSDT) and SQL PowerShell with support for the SQL Server v.Next on Windows and Linux.
• Developer Updates:
  • SQL Server + C#: What’s new https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/15/sql-server-c-whats-new/
  • SQL Server + PHP – What’s new https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/13/sql-server-php-whats-new/
  • SQL Server + Python — What’s new https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/09/sql-server-python-whats-new/
  • SQL Server + Node.js – What’s new? https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/08/sql-server-node-js-whats-new/
  • SQL Server + Java: What’s new https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/06/sql-server-java-whats-new/
• Extended per-operator level performance stats for Query Processing
  • https://blogs.msdn.microsoft.com/sql_server_team/extended-per-operator-level-performance-stats-for-query-processing/
  • As requested by the community to complete the per-operator information, starting with SQL Server 2016 SP1 we are now exposing memory grant per grant iterator (such as Sorts and Hash Matches). These give you added insight into memory grants, and how overall memory usage is driven throughout execution.
  • In the October release of SSMS we exposed per-operator stats directly in the Properties window for each operator, but the memory grant properties are not there yet. These will be added to the properties window in an upcoming release of SSMS.
• Developers Choice: Query progress – anytime, anywhere
  • https://blogs.msdn.microsoft.com/sql_server_team/query-progress-anytime-anywhere/
  • With SQL Server 2016 SP1, we are introducing a real lightweight query execution statistics profiling infrastructure, to dramatically reduce performance overhead of collecting per-operator query execution statistics, such as actual number of rows. This feature can be enabled either using global startup trace flag 7412, or is automatically turned on when query_thread_profile xEvent is enabled.
  • …for any server that is not already resource bound, you can run the lightweight profiling infrastructure continuously, and tap into any running execution at any time using for example Activity Monitor or directly querying sys.dm_exec_query_profiles, and get the query plan with execution statistics.
  • There’s also the new DMF sys.dm_exec_query_statistics_xml, which returns the query execution plan for in-flight requests.
• SQL Server 2016 SP1: Know your limits
  • https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-sp1-know-your-limits/
  • With the recent announcement of SQL Server 2016 SP1, we announced the consistent programmability experience for developers and ISVs, who can now maintain a single code base and build intelligent database applications which scale across all the editions of SQL Server. The processor, memory and database size limits does not change and remain as–in all editions as documented in the SQL Server editions page. We have made … changes in our documentation to accurately reflect the memory limits on lower editions of SQL Server.
• Parameterization for Always Encrypted
  • https://blogs.msdn.microsoft.com/sqlsecurity/2016/12/13/parameterization-for-always-encrypted-using-ssms-to-insert-into-update-and-filter-by-encrypted-columns/
  • SQL Server  Management Studio 17.0 introduces two important capabilities for Always Encrypted:
    • Ability to insert into, update and filter by values stored in encrypted columns from a Query Editor window.
    • The new online encryption algorithm, exposed in the Set-SqlColumnEncryption PowerShell cmdlet, which makes tables available for both reads and writes during the initial encryption and column encryption key rotation.
• Transaction Commit latency acceleration using Storage Class Memory in Windows Server 2016/SQL Server 2016 SP1
  • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/12/02/transaction-commit-latency-acceleration-using-storage-class-memory-in-windows-server-2016sql-server-2016-sp1/
  • We compared the results when the transaction log was located on NVMe SSD (the fastest class of non-memory storage), against the same configuration with the SCM persistent log buffer configured.
  • The persistent log buffer configuration was approximately 2X faster than putting the log on the fastest [NVMe SSD] available storage.
• Power BI reports in SQL Server Reporting Services: Feedback on the Technical Preview
  • https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/12/16/power-bi-reports-in-sql-server-reporting-services-feedback-on-the-technical-preview/
  • What’s Microsoft’s approach to offering Power BI capabilities in an on-premises solution?
    • Power BI was designed to be Software-as-a-Service running in Microsoft’s Azure datacenters, while SQL Server Reporting Services (SSRS) was designed to be an on-premises solution that customers can deploy and manage on their own servers. For customers who need an on-premises solution, as described in our reporting roadmap, we’re investing in the SSRS product and adding support for Power BI reports to SSRS. With this support, you can create a report in Power BI Desktop, publish it to your SSRS report server, and view and interact with it in your web browser.
  • Beyond the current Technical Preview, we plan to add support for
    • Custom visuals
    • Additional data connectors (besides Analysis Services), cached data, and scheduled data refresh
    • Power BI mobile apps (viewing Power BI reports stored in SSRS)
  • When will we have a production-ready version?
    • We’re targeting availability in mid-2017.

Recommended KB’s To Review Regularly

• Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters
  • https://support.microsoft.com/en-us/kb/2920151
  • Note: November 2016 (KB3197874) security monthly quality rollup for Windows 8.1 and Windows Server 2012 R2
• Recommended updates and configuration options for SQL Server 2012 and later versions with high-performance workloads
  • https://support.microsoft.com/en-gb/kb/2964518
  • Note for SQL Server 2016: You no longer have to enable these trace flags in SQL Server 2016 because the auto-detection of the associated logic for trace flags is already incorporated into the product.
• Troubleshooting SQL Server backup and restore operations
  • https://support.microsoft.com/en-us/kb/224071

Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services