SQL Updates Newsletter – September 2017
Recent Releases and Announcements
- At Microsoft Ignite, we showcased our work in quantum computing.
- Watch the Keynote here (quantum computing is discussed ~1hr mark): https://myignite.microsoft.com/sessions/59125
- In just hours or days, a quantum computer can solve complex problems that would otherwise take billions of years for classical computing to solve.
- Microsoft will release a new quantum computing programming language, with full Visual Studio integration, along with a quantum computing simulator.
- https://www.microsoft.com/quantum/
- https://arstechnica.com/gadgets/2017/09/microsoft-quantum-toolkit/
- SQL Server 2017 GA Announced
- In-database Machine Learning in SQL Server 2017
- With support for both R and Python, we have rebranded 'R Services' to 'Machine Learning Services'.
- Machine Learning Server is the transformation of Microsoft R Server into an even more flexible platform that offers a choice of R and Python languages and brings the best of algorithmic innovations from the open source world and Microsoft.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/09/26/in-database-machine-learning-in-sql-server-2017/
- Announcing the Modern Servicing Model for SQL Server
- Starting with SQL Server 2017, we are adopting a simplified, predictable mainstream servicing lifecycle.
- SPs will no longer be made available. Only CUs, and GDRs when needed.
- https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-the-modern-servicing-model-for-sql-server/
- Cumulative Update #5 for SQL Server 2016 SP1
- Cumulative Update #8 for SQL Server 2016 RTM
- Introducing the Timeline Storyteller custom visual for Microsoft Power BI
- Timeline Storyteller, a new custom visual for Power BI which was created by a team of researchers at Microsoft, is now available in the Office Store for anyone to use.
- https://powerbi.microsoft.com/en-us/blog/what-story-does-your-timeline-tell-introducing-the-timeline-storyteller-custom-visual-for-microsoft-power-bi/
- MSBuild support for Reporting Services projects now available
- By invoking msbuild.exe on your project or solution file, you can orchestrate and build products in environments where Visual Studio isn't installed. This is key for developers looking to automate deployment of Reporting Services projects outside of Visual Studio, which can streamline the process of moving between development and production environments.
- https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/09/25/msbuild-support-for-reporting-services-projects-now-available/
- Announcing general availability of Native Scoring using PREDICT function in Azure SQL Database
- The PREDICT function support was added in SQL Server 2017. It is a table-valued function that takes a RevoScaleR or revoscalepy model & data (in the form of a table or view or query) as inputs and generates predictions based on the machine learning model.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/09/25/announcing-general-availability-of-native-scoring-using-predict-function-in-azure-sql-database/
- New Power BI Connector for Azure Enterprise users
- We are pleased to announce the release of Azure Consumption and Insights Connector in Power BI Desktop. Enterprise customers can use this to pull Azure Charge and Usage data for both Azure and Marketplace resources.
- https://azure.microsoft.com/en-us/blog/new-power-bi-connector-for-azure-enterprise-users/
- Introducing Azure confidential computing
- Confidential computing ensures that when data is "in the clear", which is required for efficient processing, the data is protected inside a Trusted Execution Environment (TEE)
- TEEs ensure there is no way to view data or the operations inside from the outside, even with a debugger.
- Microsoft already uses enclaves to protect everything from blockchain financial operations, to data stored in SQL Server, and our own infrastructure within Azure. While we've previously spoken about our confidential computing blockchain efforts, known as the Coco Framework, [...] we are announcing the use of the same technology to implement encryption-in-use for Azure SQL Database and SQL Server.
- https://azure.microsoft.com/en-us/blog/introducing-azure-confidential-computing/
- Azure Analysis Services now available in Azure Government
- Announcing tools for the AI-driven digital transformation
- AML Workbench is a cross-platform client for AI-powered data wrangling and experiment management
- AML Experimentation service helps data scientists increase their rate of experimentation with big data and GPUs.
- AML Model Management service hosts, versions, manages and monitors machine learning models.
- https://azure.microsoft.com/en-us/blog/tools-for-the-ai-driven-digital-transformation/
- Introducing SQL Vulnerability Assessment for Azure SQL Database and on-premises SQL Server
- We are delighted to announce the public preview of our latest security development, the new SQL Vulnerability Assessment (VA). SQL Vulnerability Assessment is your one-stop-shop to discover, track, and remediate potential database vulnerabilities. The VA preview is now available for Azure SQL Database and for on-premises SQL Server, offering you a virtual database security expert at your fingertips.
- The scan is lightweight and safe. It takes a few seconds to run, and is entirely read-only. It does not make any changes to your database.
- https://azure.microsoft.com/en-us/blog/introducing-sql-vulnerability-assessment-for-azure-sql-database-and-on-premises-sql-server/
- New managed SQL server Integration Service hosting capability in Azure Data Factory
- In public preview: SQL Server Integration Services (SSIS) customers will benefit from easily lifting their SSIS packages into the cloud using new managed SSIS hosting capabilities in Azure Data Factory.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/09/25/new-managed-sql-server-integration-service-hosting-capability-in-data-factory/
- Announcing the public preview for Azure File Sync
- Azure File Sync keeps your Azure File share in-sync with your on-premises Window Servers. The real magic of Azure File Sync is the ability to tier files between your on-premises file server and Azure Files. This enables you to keep only the newest and most recently accessed files locally without sacrificing the ability to see and access the entire namespace through seamless cloud recall. With Azure File Sync, you can effectively transform your Windows File Server into an on-premises tier of Azure Files.
- https://azure.microsoft.com/en-us/blog/announcing-the-public-preview-for-azure-file-sync/
- Announcing Virtual Network integration for Azure Storage and Azure SQL
- We are glad to announce the public preview of Virtual Network (VNet) Service Endpoints for Azure Storage and Azure SQL
- Service endpoints extend your VNet private address space and identity to the Azure services, over a direct connection. This allows you to secure your critical service resources to only your virtual networks, providing private connectivity to these resources and fully removing Internet access.
- https://azure.microsoft.com/en-us/blog/announcing-virtual-network-integration-for-azure-storage-and-azure-sql/
- General availability of HDInsight Interactive Query - blazing fast queries on hyper-scale data
- We announced the general availability of the Interactive Query cluster type in Azure HDInsight (formerly known as Interactive Hive).
- Interactive Query enables data analysts to query data interactively in the same storage where data is prepared, eliminating the need for moving data from storage to another analytical engine for data warehousing needs.
- We've also announced the preview of Interactive Query tools for Visual Studio code.
- Customers can attach an Interactive Query cluster to existing metastore and data storage, and start querying the data right away.
- https://azure.microsoft.com/blog/general-availability-of-hdinsight-interactive-query-blazing-fast-data-warehouse-style-queries-on-hyper-scale-data-2/
- Introducing HDInsight integration with Azure Log Analytics Preview
- HDInsight customers can now monitor and debug their Hadoop, Spark, HBase, Kafka, Interactive Query, and Storm clusters in Azure Log Analytics.
- https://azure.microsoft.com/en-us/blog/introducing-hdinsight-integration-with-azure-log-analytics/
- Azure Analysis Services adds firewall support
- A preconfigured rule called "Allow access from Power BI" is enabled by default so that customers can continue to use their Power BI dashboards and reports without friction.
- https://azure.microsoft.com/en-us/blog/azure-analysis-services-adds-firewall-support/
- Announcing preview of Machine Learning Services with R support in Azure SQL Database
- You can now train and score machine learning models in Azure SQL Database and the predictions can be exposed to any application using your database, easily and seamlessly.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/09/25/announcing-preview-of-machine-learning-services-with-r-support-in-azure-sql-database/
- Meet the new Microsoft R Server: Microsoft ML Server 9.2
- Microsoft R Server has received a new name and a major update: Microsoft ML Server 9.2 is now available. ML Server provides a scalable production platform for R - and now Python - programs.
- https://blog.revolutionanalytics.com/2017/09/microsoft-ml-server-92.html
- R 3.4.2 is released
- This release fixes minor bugs and includes a performance improvement to the commonly-used function c when applied to vectors with a names attribute.
- https://blog.revolutionanalytics.com/2017/09/r-342-is-released.html
- Microsoft R Open 3.4.1 now available
- Microsoft R Open (MRO), Microsoft's enhanced distribution of open source R, has been upgraded to version 3.4.1. This update upgrades the R language engine to R 3.4.1 and updates the bundled packages.
- MRO 3.4.1 is based on R 3.4.1
- https://blog.revolutionanalytics.com/2017/09/mro-341-now-available.html
- PowerShell in Azure Cloud Shell (Preview) is now publicly available in Azure Portal
- Seamless cost reporting and analysis for Enterprise customers: now in preview
- We're excited to announce the preview release of Enterprise Cost Management within the Azure portal.
- This gives you a quick idea of the top resources contributing to your costs. The burn rate chart tells you your month-to-date costs and provides a forecast, enabling you to take corrective action early.
- https://azure.microsoft.com/en-us/blog/cost-reporting-for-enterprise-preview/
- General availability of App Service on Linux and Web App for Containers
- Announcing Default Encryption for Azure Blobs, Files, Table and Queue Storage
- We will be enabling this capability region by region, expanding to all Azure regions and Azure clouds in the coming weeks.
- https://azure.microsoft.com/en-us/blog/announcing-default-encryption-for-azure-blobs-files-table-and-queue-storage/
- Filters coming for Power BI phone reports on iOS
- With filters in reports, you can quickly remove everything except data on which you want to focus. Even better, filters on phone reports require no extra work! If a filter is defined on the original report, it automatically works on phone reports.
- https://powerbi.microsoft.com/en-us/blog/filters-coming-for-phone-reports-on-ios/
- Power BI service and mobile August 2017 feature summary
- Per-user usage metrics provide Power BI dashboard usage metrics
- Dynamic RLS is now supported for embedded reports using the "app owns credentials" flow. This highly requested option allows report authors to control what rows of data are accessible on a per-user granularity. As a reminder, dynamic RLS is already available in the Power BI service and for the "user owns credentials" embedding flows.
- And more...
- https://powerbi.microsoft.com/en-us/blog/power-bi-service-and-mobile-august-2017-feature-summary/
- Power BI Desktop September Feature Summary
- Drillthrough to another report page
- Explain the increase/decrease insights (preview). This feature lets you right click on a bar or a data point in a line chart and ask us to explain why the data point increased or decreased compared to the data point before it. We will run our insights machine learning algorithms over the data and populate a flyout with charts showing what categories most influenced this increase or decrease.
- The Ribbon chart, a new visualization, is geared towards showing rank change. The visual is like a stacked column chart, but each column's inner categories are sorted according to their rank for that column. Additionally, the inner categories are connected by ribbons across the columns to help you visually see how the rank changes across the columns.
- Our theme file format now includes full control over your chart styling. To use this extension to theming, you can add a visualStyles section to your JSON file.
- Accessibility improvements. You can access the accessible data behind the chart using the keyboard shortcut Alt+Shift+F11.
- And more...
- https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-september-2017-feature-summary/
- Early technical preview of JDBC 6.3.2 for SQL Server released!
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)
- Backup Compression for TDE-enabled Databases: Important fixes in SQL 2016 SP1 CU4 and SQL 2016 RTM CU7
- In past months, we discovered some edge scenarios related to backup compression for TDE databases causing backups or restores to fail
- Starting SQL 2016 RTM CU7, SQL 2016 SP1 CU4 and above, we have made improvements and updates to the SQL Server engine which would avoid the edge cases discussed earlier. If you plan to leverage native backup compression for TDE databases or are already using it, we strongly recommend applying the latest CUs on SQL 2016 to ensure you are not hitting any of the known issues we have discovered earlier.
- https://blogs.msdn.microsoft.com/sql_server_team/backup-compression-for-tde-enabled-databases-important-fixes-in-sql-2016-sp1-cu4-and-sql-2016-rtm-cu7/
Recent Blog Posts and Articles
- Instant Log Initialization for SQL Server in Azure
- The documentation says quite explicitly that "Log files cannot be initialized instantaneously."
- There is a case where log can be initialized instantaneously and yet maintain crash recovery semantics. Specifically, this happens when database files are created directly in Azure Blob Storage.
- The specific Azure Blob Storage feature that SQL Server is using here is the ability to clear a range of bytes in a page blob, provided by the Put Page API.
- This means that if the log file is created directly in Azure Blob Storage (as opposed to on a disk attached to an Azure VM), SQL Server does not have to initialize the log by writing zeroes to the log blob. It can instead make a call to the storage API to clear a byte range within the blob. This call completes very fast, effectively resulting in instant log file initialization.
- The log initialization operation, among other operations on database files in Azure Blob Storage, can be monitored using the xio_send_complete extended event.
- The request_type field will be set to XIOTypeZeroFile
- https://blogs.msdn.microsoft.com/sqlcat/2017/09/11/instant-log-initialization-for-sql-server-in-azure/
- Diving deep into what's new with Azure Machine Learning
- Using Legacy Data Sources in Tabular 1400
- Tabular 1400 fully supports provider data sources and native query partitions. The modern Get Data experience is optional.
- Analysis Services always invokes the Mashup engine when using structured data sources to get the data. It might or might not for provider data sources.
- https://blogs.msdn.microsoft.com/analysisservices/2017/09/12/using-legacy-data-sources-in-tabular-1400/
- Using Azure Analysis Services on Top of Azure Data Lake Storage
- The latest release of SSDT Tabular adds support for Azure Data Lake Store (ADLS) to the modern Get Data experience
- The Azure Data Lake Store connector only supports interactive logons. When you define the Azure Data Lake Store data source, SSDT prompts you to log on to Azure Data Lake.
- https://blogs.msdn.microsoft.com/analysisservices/2017/09/05/using-azure-analysis-services-on-top-of-azure-data-lake-storage/
- Featurizing images: the shallow end of deep learning
- We explore an example of using a pre-trained deep learning image classifier to generate features for use with traditional machine learning approaches to address a problem the original model was never trained on.
- https://blog.revolutionanalytics.com/2017/09/wood-knots.html
- Pirating Pirate Data for Pirate Day
- Discusses a post from Bob Rudis, who used R to create a map of worldwide piracy incidents from 2013 to 2017. It provides a useful and practical example of extracting data from a website without an API, otherwise known as "scraping" data.
- https://blog.revolutionanalytics.com/2017/09/pirating-pirate-data-for-pirate-day.html
- Preview: ALTREP promises to bring major performance improvements to R
- ALTREP is an alternative way of representing R objects internally
- One example: Today, a vector of numbers in R is represented as a contiguous block of memory in RAM. In other words, if you create the sequence of a million integers 1:1000000, R creates a block of memory 4Mb in size. With ALTREP, a sequence like this is instead represented by just its start and end values, which takes up almost no memory at all.
- https://blog.revolutionanalytics.com/2017/09/altrep-preview.html
- Monitoring Azure SQL Data Sync using OMS Log Analytics
- Previously, users had to manually look at SQL Azure Data Sync in the Azure portal or use PowerShell/RestAPI's to pull the log and detect errors and warnings. By following the steps in this blog post, Data Sync users can configure a custom solution which will greatly improve the Data Sync monitoring experience.
- https://azure.microsoft.com/en-us/blog/monitoring-azure-sql-data-sync-using-oms-log-analytics/
- Automation of Azure Analysis Services with Service Principals and PowerShell
- Enhancing query performance with Adaptive Query Processing in SQL Server 2017
- For this first version of this adaptive query processing feature family, we have three new improvements: batch mode adaptive joins, batch mode memory grant feedback, and interleaved execution for multi-statement table valued functions.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/09/28/enhancing-query-performance-with-adaptive-query-processing-in-sql-server-2017/
- Embed paginated reports into SharePoint using the Report Viewer web part (Preview)
- Azure SQL Data Warehouse Workload Patterns and Anti-Patterns
- Workload patterns on a data warehouse can be characterized as: (1) Batch loading of data (2) Transformed into fact and dimension tables (3) Complex queries involving multiple table joins (4) Aggregates over a certain dimension key (example: date or customer)
- Azure SQL DW is not a good match for the following scenarios: (1) OLTP workload (2) High volume of small reads and writes (3) Multi-Tenancy Database (4) Frequent changing of schema (5) Row by row processing (6) JSON, XML data and Spatial, Struct, Array and Map data types (7) Power BI direct query requiring dashboard performance (8) High concurrency of queries (eg. hundreds of thousands of concurrent queries) (9) Small datasets (less than 250GB) (10) Disaster recovery with stringent RPO and RTO
- https://blogs.msdn.microsoft.com/sqlcat/2017/09/05/azure-sql-data-warehouse-workload-patterns-and-anti-patterns/
Recent Training and Technical Guides
- View Microsoft Ignite Event Sessions
- Microsoft Data Platform - SQL Server 2017 and Azure Data Services
- This session covers all the latest news - from upcoming SQL Server 2017 general availability, to the new Azure Hybrid Benefit for SQL Server, to serverless computing with Azure Cosmos DB and Azure Functions. Hear how to take your SQL Server Integration Services to the cloud with Azure Data Factory's new hybrid functionality, plus learn about the latest performance and scale enhancements for Azure Data Services.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/09/29/view-on-demand-microsoft-data-platform-sql-server-2017-and-azure-data-services/
- Online textbook on data visualization with the ggplot2 package
- Free edX Course - Introduction to Artificial Intelligence (AI)
- Discover how machine learning can be used to build predictive models for AI. Learn how software can be used to process, analyze, and extract meaning from natural language and to process images and video to understand the world the way we do. Find out how to build intelligent bots that enable conversational communication between humans and AI systems.
- https://blogs.technet.microsoft.com/machinelearning/2017/09/12/free-edx-course-introduction-to-artificial-intelligence-ai/
- Tutorial: Launch a Spark and R cluster with HDInsight
- Invoking Azure ML Web Services from Excel, Using Power Query
- Walkthrough of using Power Query to invoke an Azure ML web service that forecasts various financial metrics (e.g., revenue, EPS, etc.) for the 30 Dow Jones companies
- https://blogs.technet.microsoft.com/machinelearning/2017/09/20/invoking-azure-machine-learning-web-services-from-excel-spreadsheets/
- Azure HDInsight training resources - Learn about big data using open source technologies
- Microsoft Tech Summit is back - register for a free event near you!
- Announcing the 2017-2018 Microsoft Tech Summit global tour.
- Build your skills with the latest in cloud technologies at a free, technical learning event for IT professionals and developers, coming to a city near you. We're hitting the road with our top engineers to bring you two days of in-depth sessions, networking opportunities, industry insights, and hands-on skill-building with the experts behind Microsoft's cloud services.
- https://azure.microsoft.com/en-us/blog/microsoft-tech-summit-is-back-register-for-a-free-event-near-you/
Monthly Script and Tool Tips
- Get the most out of your Azure portal experience
- The list of resources provides the ability to either act on one or multiple resources. For example, you can start multiple VM's at once.
- Organize resources using tags
- Customizable dashboards
- And more...
- https://azure.microsoft.com/en-us/blog/azure-portal-experience/
- Five more reasons why you should download the Azure mobile app
- The Azure mobile app allows you to quickly check your resources status at a glance
- Use Bash and now even PowerShell on Cloud Shell to take full control of your Azure resources.
- "Favorite" your most important resources across subscriptions and keep them in your Favorites tab for easy access.
- Share a direct link to the Azure resource via email, text message or other apps with the click of a button.
- The Azure mobile app can even help you track Azure Health incidents. Just scan the QR code from the portal and track the incident from your phone
- https://azure.microsoft.com/en-us/blog/five-more-reasons-why-you-should-download-the-azure-mobile-app/
- DSC Future Direction Update
- As a Windows product, Windows PowerShell Desired State Configuration will continue to be supported and security fixes will be released for it but all new features and functionality will be driven in and released in DSC Core.
- Windows PowerShell DSC: Requires WMI; Requires Windows PowerShell 4.0, 5.0, 5.1; Cmdlets use WinRM or CIM for remote connections
- DSC on Linux: Requires OMI; Is separate open source code base; Is not at feature parity with Windows DSC
- DSC Core: A soon to be released version of DSC that aligns with PowerShell Core; No dependency on WMI; Xcopy-able package; Runs on Windows and Linux; Requires (includes in package) PowerShell Core and .NET Core
- Azure Automation DSC is the recommended pull server solution for enterprise and cloud environments. It supports both Windows PowerShell Desired State Configuration and will support DSC Core.
- https://blogs.msdn.microsoft.com/powershell/2017/09/12/dsc-future-direction-update/
- Run your Hive LLAP & PySpark Job in Visual Studio Code
- Use HDInsight Tools for Visual Studio Code (VSCode) for (1) querying log files and gaining insights through Hive LLAP (2) data warehouse query experiences for big data (3) data science interactive tools and BI applications for big data (4) python HDInsight Spark developers
- https://azure.microsoft.com/en-us/blog/run-you-hive-llap-pyspark-job-in-visual-studio-code-vscode/
- SQL Server Database Objects and Dependencies Report
- Walk through the steps to create an SSRS report visualizing database objects and dependencies
- https://blogs.technet.microsoft.com/johannh/2017/09/09/sql-server-database-objects-and-dependencies-report/
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services