SQL Updates Newsletter – July 2017
Recent Releases and Announcements
- First release candidate of SQL Server 2017 now available
- Linux support for tier-1, mission-critical workloads; Note that with RC1, SQL Server on Linux supports Active Directory Authentication
- 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
- Graph data processing in SQL Server
- Adaptive query processing
- Python integration for advanced analytics
- SQL Server 2017 has several new benchmarks demonstrating faster performance than competitive databases, and against older versions of SQL Server
- https://blogs.technet.microsoft.com/dataplatforminsider/201 7/07/17/first-release-candidate-of-sql-server-2017-now-available/
- SQL Server 2017 Reporting Services Release Candidate now available
- As we described with the release of CTP 2.1, we moved Reporting Services installation from the SQL Server installer to a separate installer. This is a packaging change, not a product change; access to SQL Server Reporting Services is still included with your SQL Server license.
- https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/07/19/sql-server-2017-reporting-services-release-candidate-now-available/
- Updated Report Viewer control now generally available
- It’s an ASP.NET Web Forms control
- It enables you to embed paginated (RDL) reports into your app
- It supersedes the Report Viewer 2015 version and includes several enhancements we made for SSRS 2016: modern browser support, cross-browser printing, report parameter positioning, and a modern look-and-feel.
- It works with SSRS 2008-2017
- Includes Microsoft.SqlServer.Types and SqlServerSpatial140 assemblies, which you may need to render reports that contain maps.
- https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/07/27/updated-report-viewer-control-now-generally-available/
- Public Preview of Compatibility Level 140 for Azure SQL Database
- To enable: ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 140;
- After SQL Server 2017 launches (RTM version), the default Azure SQL Database compatibility level will change from 130 to 140 for newly created databases. Databases created before that time will not be affected and will maintain their current compatibility level.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/07/07/public-preview-of-compatibility-level-140-for-azure-sql-database/
- Power BI On-premises data gateway – July Update is now available!
- Nested Virtualization in Azure
- We are excited to announce that you can now enable nested virtualization using the Dv3 and Ev3 VM sizes. We will continue to expand support to more VM sizes in the coming months.
- Now not only you can create a Hyper-V container with Docker (see instructions here), but also by running nested virtualization, you can create a VM inside a VM. Such nested environment provides great flexibility in supporting your needs in various areas such as development, testing, customer training, demo, etc.
- The Dv3 and Ev3 sizes are also some of the first VM’s to be running on Windows Server 2016 hosts. Windows 2016 hosts enable Nested Virtualization and Hyper-V Containers for these new VM sizes.
- https://azure.microsoft.com/en-us/blog/nested-virtualization-in-azure/
- https://azure.microsoft.com/en-us/blog/introducing-the-new-dv3-and-ev3-vm-sizes/
- Microsoft announces Project Olympus support for new Intel Xeon Scalable Processors
- Project Olympus is Microsoft’s blueprint for future hardware development and collaboration. It is our next generation hyperscale cloud hardware design.
- Microsoft is now proud to announce support for the newest generation of Intel Xeon Scalable Processors within the Project Olympus ecosystem.
- https://azure.microsoft.com/en-us/blog/microsoft-announces-project-olympus-support-for-new-intel-xeon-scalable-processors/
- Announcing Microsoft 365
- A new set of commercial offerings that brings together Office 365, Windows 10, and Enterprise Mobility + Security.
- Microsoft 365 Enterprise is the evolution of our highly successful Secure Productive Enterprise offering, and includes Office 365 Enterprise, Windows 10 Enterprise, and Enterprise Mobility + Security. It’s designed for large organizations and empowers employees to be creative and work together, securely.
- Microsoft 365 Business, available in public preview starting August 2, is designed for small- to medium-sized businesses with up to 300 users and integrates Office 365 Business Premium with tailored security and management features from Windows 10 and Enterprise Mobility + Security. It also includes a centralized console for deploying and securing devices and users in one location.
- https://blogs.microsoft.com/blog/2017/07/10/microsoft-puts-partners-center-4-5-trillion-transformation-opportunity/
- Microsoft Azure leads the industry in ISO certifications
- We are happy to announce that Microsoft Azure recently completed a new set of independent third-party ISO and Cloud Security Alliance (CSA) audits to expand our certification portfolio.
- The CSA STAR Certification for Microsoft Azure can be downloaded from the CSA Registry. Our ISO reports and certificates can be downloaded from the Service Trust Portal.
- https://azure.microsoft.com/en-us/blog/microsoft-azure-leads-the-industry-in-iso-certifications/
- Microsoft Azure Stack is ready to order now
- Together with Dell EMC, HPE and Lenovo, we are announcing Microsoft Azure Stack integrated systems are now available to order
- https://azure.microsoft.com/en-us/blog/microsoft-azure-stack-is-ready-to-order-now/
- https://azure.microsoft.com/en-us/blog/inspire-2017-new-cloud-services-for-partners-to-enable-digital-transformation/
- Instant File Recovery from Azure VM backups is now generally available
- With this file recovery feature, you can now securely 1) Recover files instantly – recover files from the cloud backups of Azure VMs without any additional infrastructure. Whether it’s accidental file deletion or simply validating the backup, instant restore drastically reduces the time to recover your data. 2)Open application files without restoring them
- https://azure.microsoft.com/en-us/blog/instant-file-recovery-from-azure-vm-backups-is-now-generally-available/
- Azure SQL Data Warehouse previews 3x compute scale with unlimited columnar storage
- Azure SQL Data Warehouse is increasing its compute scale threefold, raising the compute scale from 6,000 to 18,000 Data Warehouse Units (DWU) so that customers can run their most compute intensive data warehouse workloads in the cloud.
- https://azure.microsoft.com/en-us/blog/azure-sql-data-warehouse-previews-3x-compute-scale-with-unlimited-columnar-storage/
- PowerApps and Power BI, together at last
- Now with the new Power BI tile control, you can show your Power BI tiles inside your app.
- https://powerbi.microsoft.com/en-us/blog/power-bi-tile-in-powerapps/
- Azure SQL Data Warehouse: New monitoring functionality for Columnstore
- In order to enable easier monitoring and tuning of row group quality we are now exposing a new DMV.
- Azure SQL DW now has a new DMV (dm_pdw_nodes_db_column_store_row_group_physical_stats) for exposing information about physical statistics of row groups for a Columnstore table
- The key piece of information is the trim_reason_desc that tells whether a row group was prematurely trimmed or not. [If] it was trimmed, then the trim_reason_desc is set to the trim reason such as MEMORY_LIMITATION or DICTIONARY_SIZE.
- https://azure.microsoft.com/en-us/blog/sql-data-warehouse-columstore-monitor/
- Microsoft joins Cloud Native Computing Foundation
- I’m excited to share that we have just joined the Cloud Native Computing Foundation (CNCF) as a Platinum member. CNCF is a part of the Linux Foundation, which helps govern for a wide range of cloud-oriented open source projects.
- https://azure.microsoft.com/en-us/blog/announcing-cncf/
- Microsoft Drivers v4.3.0 for PHP for SQL Server released
- The driver enables access to SQL Server, Azure SQL Database, and Azure SQL DW from any PHP application on Linux, Windows, and macOS.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/07/18/microsoft-drivers-v4-3-0-for-php-for-sql-server-released/
- Microsoft Cognitive Services updates - Bing Entity Search API and Project Prague
- We are launching Bing Entity Search API, a new service available in Free Preview which makes it easy for developers to build experiences that leverage the power of Bing knowledge graph with more engaging contextual experiences.
- Microsoft Cognitive Services Lab’s Project Prague is now available. Project Prague lets you control and interact with devices using gestures to have a more intuitive and natural experience.
- Presentation Translator, a Microsoft Garage project, is now available for download. It provides presenters the ability to add subtitles to their presentations in real time, in the same language for accessibility scenarios or in another language for multi-language situations.
- https://azure.microsoft.com/en-us/blog/microsoft-cognitive-services-updates-bing-entity-search-api-and-project-prague/
- New government datacenter regions available in Arizona and Texas
- Announcing StorSimple 8000 series in the new Azure portal
- Power BI Service and Mobile June Feature Summary
- You can now use the API to automate scheduled refresh, clone and rebind reports, update gateway bindings, and much more.
- We shipped a new, enhanced version of the On-premises data gateway (personal mode).
- Note: Refreshes through your existing personal gateway (Power BI Gateway − Personal) will stop working after July 31st, 2017; please ensure that you upgrade to the new gateway by July 31st, 2017 to keep your datasets up to date.
- Significant improvements to the report loading and caching in [Power BI] mobile.
- And more…
- https://powerbi.microsoft.com/en-us/blog/power-bi-service-and-mobile-june-feature-summary/
- Introducing wait stats support in Query Store
- Azure SQL Database and SQL Server 2017 CTP 2.0+ now have wait stats per query available in Query Store.
- Now you can exactly identify why and how much every plan waited for some resource.
- For simplicity in evaluating the collected data for waits, we are introducing wait categories in query_store_wait_stats
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/07/03/what-are-you-waiting-for-introducing-wait-stats-support-in-query-store/
- Loan Chargeoff Prediction Solution Templates are Live in Cortana Intelligence Solutions Gallery
- A charged off loan is a loan that is declared by a creditor (usually a lending institution) that an amount of debt is unlikely to be collected, usually when the loan repayment is severely delinquent by the debtor
- We have published two solution templates
- Loan Chargeoff Prediction using SQL Server 2016 R Services: Using DSVM with SQL Server 2016 and Microsoft ML, this solution template walks through how to create and clean up a set of simulated data, use 5 different models to train, select the best performant model, perform scoring using the model and save the prediction results back to SQL Server. A PowerBI report connects to the prediction table and show interactive reports with the user on the chargeoff prediction.
- Loan Chargeoff Prediction using HDInsight Spark Clusters: This solution demonstrates how to develop machine learning models for predicting loan chargeoff (including data processing, feature engineering, training and evaluating models), deploy the models as a web service (on the edge node) and consume the web service remotely with Microsoft R Server on Azure HDInsight Spark clusters. The final predictions is saved to a Hive table which could be visualized in Power BI.
- https://blogs.msdn.microsoft.com/sql_server_team/loan-chargeoff-prediction-solution-templates-are-live-in-cortana-intelligence-solutions-gallery/
- Responsive visualizations coming to Power BI
- Power BI responsive visualizations change dynamically to display the maximum amount of data and insight, no matter the screen size.
- As a visualization changes its size, Power BI prioritizes the data view, for example removing padding and legend tweaks such as moving the legend to the top of the visualization - automatically, so the visualization remains informative and beautiful even as it gets smaller.
- https://powerbi.microsoft.com/en-us/blog/responsive-visualizations-coming-to-power-bi/
- Microsoft JDBC Driver 6.2 for SQL Server released
- Early technical preview of JDBC 6.3.0 for SQL Server released
- Power BI Desktop July Feature Summary
- New table & matrix visuals are now generally available; We’ve also updated the drill experience to make it easier to drill on either the rows or columns; We now honor new lines in content, either when it’s in the data you load from your source or if you specify it in a DAX function.
- This month we are bringing the custom visuals store into the Power BI Desktop. Right from the Home ribbon, you can open the store, browse visuals and add them to your Desktop.
- Responsive layout for visuals (preview)
- New waterfall chart option - breakdown
- Bidirectional cross filtering for DirectQuery is now generally available
- Quick measures from the community: Star rating, Concatenated list of values
- Add Column from Examples enhancements
- And more…
- https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-july-feature-summary-2/
- DSC Resource Kit Release July 2017
- The modules updated in this release are: PsDscResources, SecurityPolicyDsc, SharePointDsc, xCertificate, xComputerManagement, xFailOverCluster, xHyper-V, xNetworking, xSqlServer, xStorage, xWebAdministration, xWindowsUpdate
- https://blogs.msdn.microsoft.com/powershell/2017/07/12/dsc-resource-kit-release-july-2017/
- Azure Data Lake Tools for Visual Studio Code (VSCode) July updates
- Azure Data Lake Tools for VSCode is an extension for developing U-SQL projects against Microsoft Azure Data Lake
- https://azure.microsoft.com/en-us/blog/azure-data-lake-tools-for-visual-studio-code-vscode-july-updates/
- Database Scoped Global Temporary Tables in public preview for Azure SQL DB
- Similar to global temporary tables for SQL Server, tables prefixed with ##table_name, global temporary tables for Azure SQL DB are stored in tempdb and follow the same semantics. However, rather than being shared across all databases on the server, they are scoped to a specific database and are shared among all users’ sessions within that same database. User sessions from other Azure SQL databases cannot access global temporary tables created as part of running sessions connected to a given database. Any user can create global temporary objects.
- https://azure.microsoft.com/en-us/blog/database-scoped-global-temporary-tables-in-public-preview-for-azure-sql-db/
- Resumable Online Index Rebuild is in public preview for Azure SQL DB
- We are delighted to announce that Resumable Online Index Rebuild (ROIR) is now available for public preview in Azure SQL DB. With this feature, you can resume a paused index rebuild operation from where the rebuild operation was paused rather than having to restart the operation at the beginning. Additionally, this feature rebuilds indexes using only a small amount of log space.
- https://azure.microsoft.com/en-us/blog/resumable-online-index-rebuild-is-in-public-preview-for-azure-sql-db/
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.
- 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: 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
- 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)
- Monitoring automatic tuning actions using XEvents
- Automatic tuning detects regressed plans, but it will not apply last know good plan if the difference between performance of the regressed plan and last known good plan is not high enough.
- Whenever automatic tuning detects that a plan has potentially regressed and compares performance of the current plan with the last known good plan, XEvent automatic_tuning_plan_regression_detection_check_completed is fired. Field is_regression_detected means that SQL Server has identified possible plan change regression; is_regression_corrected means that regression is corrected.
- Handling data encoding issues while loading data to SQL Data Warehouse
- This blog is intended to provide insight on some of the data encoding issues that you may encounter while using Polybase to load data to SQL Data Warehouse.
- If the data is formatted using either the UTF-8 or UTF-16 encoding standard, you can use Polybase to load the data. However, the format of your data is dependent on the encoding options supported by the source system. Some systems do not provide support for UTF-8 or UTF-16 encoding. If the data you are working with is formatted in an alternate format, such as ISO-8859-1, then being able to convert the data to UTF-8/UTF-16 format can save valuable time and effort.
- The sample bash script on Ubuntu VM that was used to convert the encoding on the data files automatically uses the iconv command where the source file encoding is specified as ISO-8859-1 and the target file encoding is specified as UTF-8
- https://azure.microsoft.com/en-us/blog/handling-data-encoding-issues-while-loading-data-to-sql-data-warehouse/
- Demo: Identify and fix plan change regression in SQL Server 2017 RC1
- As an alternative for manual monitoring and correction using sp_query_store_force_plan procedure, you can let SQL Server to automatically apply recommendations whenever big performance regression happens after the plan change using the following code: ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); SQL Server will [then] take recommendations from the sys.dm_db_tuning_recommendations view, apply them, and automatically verify that the forced plan is better than the previous one.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/07/20/demo-identify-and-fix-plan-change-regression-in-sql-server-2017-rc1/
Recent Blog Posts and Articles
- Tableau and Azure SQL DB, a match made in the cloud
- What’s new in SQL Server 2017 RC1 for Analysis Services
- More Companies using R
- Quick roundup of some case studies published recently on the Microsoft Customer Stories portal, with examples of companies running R in production environments using the Microsoft stack
- https://blog.revolutionanalytics.com/2017/07/more-companies-using-r.html
- Deep Learning Part 5: Running Pre-trained Deep Neural Networks through Microsoft Cognitive Services APIs on Raspberry Pi 3 & Parrot Drones
- Current trends in the research have demonstrated that Deep Convolutional Networks (DCNNs) are very effective in automatically analyzing large collections of images and identifying features that can categorize images with minimum error. DCNNs are rarely trained from scratch, as it is relatively uncommon to have a domain-specific dataset of sufficient size.
- Since modern DCNNs take 2-3 weeks to train across GPUs, it is a costly and time consuming process. We at Microsoft have identified this as a blocker for AI enthusiasts, and we have pretrained some common DCNNs and released them as Microsoft Cognitive Services APIs to help get started easily.
- https://blog.revolutionanalytics.com/2017/07/deep-learning-and-drones.html
- SSMS: Your one-stop-shop for SQL Server Performance Troubleshooting
- Setting up SQL Server High-Availability between Windows and Linux with SQL Server 2017
- How the SQLCAT Customer Lab is Monitoring SQL on Linux
- The following solutions were tested: Graphing with Grafana and Graphite; Collection with collectd and Telegraf; Storage with Graphite/Whisper and InfluxDB
- https://blogs.msdn.microsoft.com/sqlcat/2017/07/03/how-the-sqlcat-customer-lab-is-monitoring-sql-on-linux/
Recent Training and Technical Guides
- SQL Unplugged - July 2017 Edition
- [Tutorial] Go serverless with R Scripts on Azure Function
- Online Analysis Services Course: Developing a Multidimensional Model
- UseR! 2017 Conference live-stream
- [Video] Super Charge Power BI with Azure Analysis Services
- Missed Azure OpenDev? Watch the videos on-demand now!
- [Video] Azure Container Registry adds individual identity, webhooks, and delete capabilities
Monthly Script and Tool Tips
- Introducing the Azure Analysis Services web designer (Preview)
- We’ve released a preview of the Azure Analysis Services web designer. This new browser-based experience will allow developers to start creating and managing Azure Analysis Services (AAS) semantic models quickly and easily. While SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS) are still the primary tools for development, this new experience is intended to make simple changes fast and easy.
- This initial release includes three major capabilities: model creation, model editing, and query execution.
- https://azure.microsoft.com/en-us/blog/introducing-the-azure-analysis-services-web-designer/
- Announcing Cortana Intelligence Solution Evaluation Tool
- If you have a solution leveraging Cortana Intelligence platform 1) Use the Cortana Intelligence solution evaluation tool to see how it fares against best practices 2) Get started with Cortana Intelligence AppSource publishing guide to publish it to AppSource
- https://blogs.technet.microsoft.com/machinelearning/2017/07/10/announcing-cortana-intelligence-solution-evaluation-tool/
- Converting your Office Theme Colors to a Power BI Report Theme
- How to filter multivalued column in Power BI?
- Solution: Create temporary index table extracting values via the DAX FILTER and SUMMARIZE functions; Apply CROSSJOIN operation and convert source table from wide to long format; Define relationships.
- https://community.powerbi.com/t5/Community-Blog/How-to-filter-multivalued-column-in-Power-BI/ba-p/217914
- Model Comparison and Merging for Analysis Services
- Relational-database schema comparison and merging is a well-established market. [Comparison and merging of BI models] is [now] possible with BISM Normalizer, which we are pleased to announce now resides on the Analysis Services Git repo.
- https://blogs.msdn.microsoft.com/analysisservices/2017/07/19/model-comparison-and-merging-for-analysis-services/
- PowerShell 6.0 Roadmap: CoreCLR, Backwards Compatibility, and More!
- There are two editions of PowerShell
- Windows PowerShell is the edition of PowerShell built on top of .NET Framework. Windows PowerShell 5.1, much like .NET Framework 4.x, will continue to be a built-in, supported component of Windows 10 and Windows Server 2016. However, it will likely not receive major feature updates or lower-priority bug fixes.
- PowerShell Core is the edition of PowerShell built on top of .NET Core. PowerShell Core is cross-platform, available on Windows, macOS, and Linux, thanks to the cross-platform nature of .NET Core.
- https://blogs.msdn.microsoft.com/powershell/2017/07/14/powershell-6-0-roadmap-coreclr-backwards-compatibility-and-more/
- SQL Nexus 6.0 is released to github
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services