SQL Updates Newsletter - October/November 2015

Recent Releases and Announcements

• SQL Server 2016 CTP 3 Preview
  • https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016
  • https://blogs.technet.com/b/dataplatforminsider/archive/2015/10/28/sql-server-2016-community-technology-preview-3-0-is-available.aspx
  • Revolution R integration included in SQL Server 2016 CTP 3.0: https://blogs.technet.com/b/stbnewsbytes/archive/2015/10/28/cloud-platform-release-announcements-for-october-28-2015.aspx
• SQL Server 2014 SP1 CU3 released
  • https://blogs.msdn.com/b/sqlreleaseservices/archive/2015/10/22/cumulative-update-3-for-sql-server-2014-sp1.aspx
  • https://support.microsoft.com/en-us/kb/3094221
• Gartner positions Microsoft as a leader in the Magic Quadrant for Operational Database Management Systems
  • https://blogs.technet.com/b/dataplatforminsider/archive/2015/10/15/gartner-positions-microsoft-as-a-leader-in-the-magic-quadrant-for-operational-database-management-systems.aspx
• Azure Data Platform Announcements
  • Azure Data Lake: Analytic processing power and an exabyte-scale big data store as a fully managed service. Part of the Cortana Analytics Suite, Azure Data Lake includes Azure HDInsight and new previews available now for the Store and the Analytics service and also includes.
  • Azure Data Lake Store (preview): A single repository where you can easily capture data of any size, type and speed.
  • Azure Data Lake Analytics (preview): A new service built on Apache YARN that dynamically provisions resources and lets you do analytics on exabytes of data. With Data Lake Analytics, use U-SQL, a query language that blends the declarative nature of SQL with the expressive power of C#.
  • In-Memory OLTP in Azure SQL Database is in public preview
  • https://blogs.technet.com/b/dataplatforminsider/archive/2015/10/28/sql-server-2016-everything-built-in.aspx
  • https://blogs.technet.com/b/dataplatforminsider/archive/2015/09/28/microsoft-expands-azure-data-lake-to-unleash-big-data-productivity.aspx
• Datazen Publisher for Windows 7 available now
  • https://blogs.technet.com/b/dataplatforminsider/archive/2015/10/19/datazen-publisher-for-windows-7-available-now.aspx
• Announcing SQL Server Management Studio - October 2015 Release
  • https://blogs.msdn.com/b/sqlreleaseservices/archive/2015/10/28/announcing-sql-server-management-studio-october-2015-release.aspx
• SQL Server Data Tools Previewupdate for October 2015
  • https://blogs.msdn.com/b/ssdt/archive/2015/10/27/sql-server-data-tools-preview-update-for-october-2015.aspx
• Power BI Desktop October update
  • Custom visualizations support, KPI Consumption (preview), improved defaulkt sorting, tooltips and data labels on funnel charts, insert visual from the ribbon, data point label improvements, and many more improvements.
  • https://blogs.msdn.com/b/powerbi/archive/2015/10/20/custom-visualizations-support-and-22-other-features-in-the-power-bi-desktop-october-update.aspx
• Released: Samples for SQL Server 2016 CTP3
  • https://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/10/29/samples-for-sql-server-2016-ctp3.aspx
• Released: System Center Management Pack for SQL Server and Replication (6.6.2.0)
  • https://blogs.msdn.com/b/sqlreleaseservices/archive/2015/10/16/released-system-center-management-pack-for-sql-server-and-replication-6-6-2-0.aspx
• Released: Localized System Center Management Packs for SQL Server Product Family (6.6.0.0)
  • https://blogs.msdn.com/b/sqlreleaseservices/archive/2015/10/13/released-localized-system-center-management-packs-for-sql-server-product-family-6-6-0-0.aspx
• The I/O Reliability Requirements document has been updated for SQL 2014 and SQL 2016
  • Outlines the required and recommended behaviors an I/O subsystem must provide for Microsoft SQL Server.
  • https://download.microsoft.com/download/F/1/E/F1ECC20C-85EE-4D73-BABA-F87200E8DBC2/SQL_Server_IO_Reliability_Program_Review_Requirements.pdf
• SQL Server 2016 – Added information on TempDB Spill events in Showplan
  • Additional details in the warnings section of how many threads spilled, how much I/O was spilled, and memory statistics for the operator
  • https://blogs.msdn.com/b/sql_server_team/archive/2015/10/28/sql-server-2016-added-information-on-tempdb-spill-events-showplan.aspx
• Announcing a SQL Server scalability enhancement
• New update will dynamically adjust the number of concurrent compiles allowed in the Big Gateway
  • To take advantage of this new policy change, install KB3024815 for SQL Server 2014 and turn on trace flag 6498
  • In SQL Server 2016, you will not require the trace flag as the dynamic policy for Big Gateway is on by default.
  • In addition, we have introduced a new DMV sys.dm_exec_query_optimizer_memory_gateways that will give both configuration and run time information of all the compile gateways for that instance.
  • https://blogs.msdn.com/b/sql_server_team/archive/2015/10/09/query-compile-big-gateway-policy-changes-in-sql-server.aspx
• SQL Server 2016 Analysis Services CTP 3 update can be downloaded within the next few days
  • Improvements include: Xevents support through SMS, Tabular model now supports parallel partition processing, Over 50 new DAX functions, improved performance to DirectQuery mode, improved DAX performance through Super DAX.
  • https://blogs.msdn.com/b/analysisservices/archive/2015/10/28/what-s-new-for-sql-server-2016-analysis-services-in-ctp3.aspx
• Released: System Center Management Pack for SQL Server and Replication (6.6.2.0)
  • https://blogs.msdn.com/b/sqlreleaseservices/archive/2015/10/16/released-system-center-management-pack-for-sql-server-and-replication-6-6-2-0.aspx
• Comparison Tool released with latest SSMS
  • https://blogs.msdn.com/b/sql_server_team/archive/2015/10/12/comparison-tool-released-with-latest-ssms.aspx
  • Plan Comparison option provides side-by-side comparison of two different query plans, for easier identification of similarities and changes.
• Microsoft Azure is hiring people around the world with open source skills
  • https://blogs.microsoft.com/firehose/2015/10/29/microsoft-azure-is-hiring-people-around-the-world-with-open-source-skills/
  • https://openness.microsoft.com/blog/2015/10/28/have-open-source-skills-microsoft-azure-is-hiring/

Recent Whitepapers/E-books/Training/Tutorials

• Introducing U-SQL - A new language for Massive Data processing
  • U-SQL is designed to be closer in syntax to T-SQL and uses C# to extend its footprint. If you know SQL, you can use U-SQL from Day 1 and process Big Data right away.
  • https://channel9.msdn.com/Series/AzureDataLake/USQL
• Internet of Things for Heatlhcare with Azure and Power BI
  • https://channel9.msdn.com/Blogs/mwilmot/Internet-of-Things-for-Heatlhcare-with-Azure-and-Power-BI
• Azure SQL Data Warehouse: Massively Parallel Processing
  • https://channel9.msdn.com/events/TechTalks-2015-Finland/SQL-Server-2016--Big-Data/Azure-SQL-Data-Warehouse-Massively-Parallel-Processing-palvelu-tuntihinnalla
• Azure SQL Database Threat Detection
  • https://channel9.msdn.com/Shows/Data-Exposed/Azure-SQL-Database-Threat-Detection
• Azure SQL Database Layers of  Protection
  • https://channel9.msdn.com/Shows/Data-Exposed/Azure-SQL-Database-Layers-of-Protection
• Azure Active Directory Authentication for SQL Database V12
  • https://channel9.msdn.com/Shows/Data-Exposed/Azure-Active-Directory-Authentication-for-SQL-Database-V12
• SQL Server 2016 Master Data Services Entity Sync and Hierarchy Management
  • https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-and-Master-Data-Services
• Loading data with PolyBase in Azure SQL Data Warehouse
  • https://channel9.msdn.com/Blogs/Windows-Azure/Loading-data-with-PolyBase-in-Azure-SQL-Data-Warehouse
• Loading Azure SQL Data Warehouse with Azure Data Factory
  • https://channel9.msdn.com/Blogs/Windows-Azure/Loading-Azure-SQL-Data-Warehouse-with-Azure-Data-Factory
• Loading data into Azure SQL Data Warehouse with BCP
  • https://channel9.msdn.com/Blogs/Windows-Azure/Loading-data-into-Azure-SQL-Data-Warehouse-with-BCP
• Azure SQL Data Warehouse Sample Data and PowerBI
  • https://channel9.msdn.com/Blogs/Windows-Azure/Azure-SQL-Data-Warehouse-Sample-Data-and-PowerBI
• Dynamic Data Masking Updates
  • https://channel9.msdn.com/Shows/Data-Exposed/Dynamic-Data-Masking-Updates
• Getting Started with Always Encrypted with SSMS
  • https://channel9.msdn.com/Shows/Data-Exposed/Getting-Started-with-Always-Encrypted-with-SSMS
• Predicting Home Values with Azure Machine Learning
  • https://channel9.msdn.com/Blogs/raw-tech/Predicting-Home-Values-with-Azure-Machine-Learning

Monthly Script Tips

• Use SQL Server PowerShell Provider for Fun and Profit
  • https://blogs.technet.com/b/heyscriptingguy/archive/2015/10/30/use-sql-server-powershell-provider-for-fun-and-profit.aspx
• PowerTip: Use PowerShell to Find Versions of SQL Server
  • https://blogs.technet.com/b/heyscriptingguy/archive/2015/10/29/powertip-use-powershell-to-find-versions-of-sql-server.aspx
• PowerTip: Use PowerShell Get-Command to Show Syntax
  • https://blogs.technet.com/b/heyscriptingguy/archive/2015/10/26/powertip-use-powershell-get-command-to-show-syntax.aspx
• PowerTip: Use PowerShell to Back Up System Databases
  • https://blogs.technet.com/b/heyscriptingguy/archive/2015/10/30/powertip-use-powershell-to-back-up-system-databases.aspx
• Getting Information from the SQL Server Provider with PowerShell
  • https://blogs.technet.com/b/heyscriptingguy/archive/2015/10/29/getting-information-from-the-sql-server-provider-with-powershell.aspx
• Storing PowerShell Credentials in the local user registry
  • https://blogs.technet.com/b/undocumentedfeatures/archive/2015/10/01/storing-credentials-in-the-local-user-registry.aspx

Lifecycle Updates

• Support for .NET Framework 4, 4.5 and 4.5.1 ending on January 12, 2016
  • Please complete the in-place update to .NET Framework 4.5.2 by January 12, 2016
  • The support change does not affect the support lifecycle policy for older versions prior to .NET Framework 4. Support for .NET Framework 4.5.2, as well as all other .NET versions such as 3.5 SP1, will continue to be supported for the duration of the operating system support lifecycle.
  • https://support.microsoft.com/en-us/gp/framework_faq

• Support for SQL Server 2014 RTM ends on 7/12/2016. Please upgrade to the latest service pack as soon as possible.
• Support for SQL Server 2012 SP1 ended on 7/14/2014. Please upgrade to the latest service pack as soon as possible.
• Support for SQL Server 2012 RTM ended on 1/14/2014. Please upgrade to the latest service pack as soon as possible.
• Extended Support for SQL Server 2005 ends on April 12, 2016.
  • https://www.microsoft.com/en-us/server-cloud/products/sql-server-2005/?WT.mc_id=Blog_SQL_General_DI
  • https://blogs.microsoft.com/firehose/2015/10/13/extended-support-for-sql-server-2005-ends-in-six-months-are-you-ready/

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
• 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
• FIX: Error occurs when you configure SQL Server replication or set up a linked server in SQL Server 2012 or SQL Server 2014
  • https://support.microsoft.com/en-us/kb/3091643
• FIX: It takes longer than expected when you run a query with non-admin user role in SSAS 2012 or SSAS 2014 in Tabular model
  • https://support.microsoft.com/en-us/kb/3087094
• FIX: Rare incorrect result occurs when you run parallel query in SQL Server 2012 or SQL Server 2014
  • https://support.microsoft.com/en-us/kb/3077273
• FIX: Access violations when you use the FileTable feature in SQL Server 2012 or SQL Server 2014
  • https://support.microsoft.com/en-us/kb/3087872
• FIX: Error 602 when you run a stored procedure repeatedly in SQL Server 2012
  • https://support.microsoft.com/en-us/kb/3080209
• SQL Server 2012 NUMA Node Imbalance – Cont’d
  • https://blogs.msdn.com/b/sqlcan/archive/2015/10/01/sql-server-2012-numa-node-imbalance-cont-d.aspx
• Don’t do this when configuring Azure SQL Data Warehouse
  • https://blogs.technet.com/b/andrew/archive/2015/10/26/azure-sql-data-warehouse-don-t-do-this.aspx
• Database restore failure when restoring from URL
  • https://blogs.msdn.com/b/dfurman/archive/2015/10/23/database-restore-failure-when-restoring-from-url.aspx

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