SQL Updates Newsletter – January 2018
Recent Releases and Announcements
- Cumulative Update #3 for SQL Server 2017 RTM
- Cumulative Update #7 for SQL Server 2016 SP1
- Cumulative Update #10 for SQL Server 2014 SP2
- Power BI Desktop January Feature Summary
- Microsoft R Open 3.4.3 now available
- The Future Computed: Artificial Intelligence and its role in society
- Microsoft released a new book, The Future Computed: Artificial Intelligence and its role in society.
- As the title suggests, the book provides our perspective on where AI technology is going and the new societal issues it has raised.
- https://blogs.microsoft.com/blog/2018/01/17/future-computed-artificial-intelligence-role-society/
- Microsoft and Adaptive Biotechnologies announce partnership using AI to decode immune system; diagnose, treat disease
- We’re excited to announce a new partnership with Seattle-based Adaptive Biotechnologies, coupling the latest advances in AI and machine learning with recent breakthroughs in biotechnology to build a practical technology for mapping and decoding the human immune system.
- https://blogs.microsoft.com/blog/2018/01/04/microsoft-adaptive-biotechnologies-announce-partnership-using-ai-decode-immune-system-diagnose-treat-disease/
- The January release of SQL Operations Studio is now available
- Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB, and SQL DW from Windows, macOS, and Linux.
- https://blogs.technet.microsoft.com/dataplatforminsider/2018/01/17/the-january-release-of-sql-operations-studio-is-now-available/
- Database scoped optimizing for ad hoc workloads
- SQL Server provides the “optimize for ad hoc workloads” server-scoped option that is used to reduce the memory footprint of single use ad hoc batches and associated plans.
- We are now introducing a new database scoped configuration called OPTIMIZE_FOR_AD_HOC_WORKLOADS which enables this behavior at the database scope in Azure SQL Database.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/01/04/database-scoped-optimizing-for-ad-hoc-workloads/
- Compatibility Level 140 is now the default for Azure SQL Database
- Why move to database Compatibility Level 140? The biggest change is the enabling of the adaptive query processing feature family, but there are also query processing related fixes and batch mode improvements as well.
- We do not update database compatibility level for existing databases. This is up to customers to do at their own discretion. With that said, we highly recommend customers plan on moving to the latest compatibility level in order to leverage the latest improvements.
- https://azure.microsoft.com/en-us/blog/compatibility-level-140-is-now-the-default-for-azure-sql-database/
- Asynchronous refresh with the REST API for Azure Analysis Services
- We are pleased to introduce the REST API for Azure Analysis Services. Using any programming language that supports REST calls, you can now perform asynchronous data-refresh operations. This includes synchronization of read-only replicas for query scale out.
- The REST API for Azure Analysis Services enables data-refresh operations to be carried out asynchronously. It therefore does not require long-running HTTP connections from client applications. Additionally, there are other built-in features for reliability such as auto retries and batched commits.
- https://azure.microsoft.com/en-us/blog/asynchronous-refresh-with-the-rest-api-for-azure-analysis-services/
- On-premises data gateway January update is now available
- Partnering for a path to digital identity
- Last summer [..] Microsoft took a first step, collaborating with Accenture and Avanade on a blockchain-based identity prototype on Microsoft Azure. Together, we pursued this work in support of the ID2020 Alliance – a global public-private partnership dedicated to aiding the 1.1 billion people around the world who lack any legal form of identity.
- Today, we are excited to share that we are deepening our commitment to this issue by formally joining ID2020 as a founding member. In addition to a donation of $1 million, we will commit resources and expertise to further develop a secure, portable form of digital identity and help implement it across governments and agencies.
- https://blogs.microsoft.com/blog/2018/01/22/partnering-for-a-path-to-digital-identity/
- PowerShell Core 6.0: Generally Available (GA) and Supported!
- What’s the difference between Windows PowerShell and PowerShell Core? Windows PowerShell is the edition of PowerShell built on top of .NET Framework. 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/2018/01/10/powershell-core-6-0-generally-available-ga-and-supported/
Troubleshooting and Issue Alerts
- 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)
- How Azure Security Center helps analyze attacks using Investigation and Log Search
- Azure Security Center (ASC) uses advanced analytics and global threat intelligence to detect malicious threats, and the new capabilities empower you to respond quickly. This blog post showcases how an analyst can leverage the Investigation and Log Search capabilities in Azure Security Center to determine whether an alert represents a security breach, and to understand the scope of that breach.
- https://azure.microsoft.com/en-us/blog/how-azure-security-center-helps-analyze-attacks-using-investigation-and-log-search/
- Securing Azure customers from CPU vulnerability
- An industry-wide, hardware-based security vulnerability was disclosed in early January.
- The majority of Azure infrastructure has already been updated to address this vulnerability.
- This blog post provides more details about VM reboot schedules and impact.
- https://azure.microsoft.com/en-us/blog/securing-azure-customers-from-cpu-vulnerability/
Recent Blog Posts and Articles
- Implicit Conversions – Ouch!
- This post discusses the pitfalls of comparing different data types in a where clause and how it can hurt performance.
- https://blogs.technet.microsoft.com/dataplatform/2018/01/29/implicit-conversions-ouch/
- JSON in SQL Server – Use cases
- Simplification of complex data models
- REST API for single-page applications
- Retail/E-commerce
- Log and telemetry data analysis
- Storing semi-structured IoT data
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/01/31/json-in-sql-server-use-cases/
- Simplify data access using de-normalized models
- SQL Server and Azure SQL Database enable you to combine both relational and non-relational models in the same database. If you identify parts of the database schema that are suitable for NoSQL-like design models, you don’t need to export these parts into separate NoSQL database. You can leverage the same design concepts without going out of the database.
- You can store information within a table column as collections formatted as JSON arrays.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/01/24/simplify-data-access-using-de-normalized-models/
- Care and Feeding of Predictive Maintenance Solutions
- This post is written specifically to prepare users interested in using their own data to deploy customized predictive maintenance scenarios. We will discuss the prerequisites to building and requirements for maintaining an intelligent, ML predictive maintenance solution. We will define what predictive maintenance means and discuss data requirements for creating a solution. We also discuss the end result of the product and how to maintain your operationalized ML solution.
- https://blogs.technet.microsoft.com/machinelearning/2018/01/23/care-and-feeding-of-predictive-maintenance-solutions/
- Custom Vision Service: Code-Free Automated Machine Learning for Image Classification
- Now, you can easily add real time image classification to your mobile apps.
- https://blogs.technet.microsoft.com/machinelearning/2018/01/22/custom-vision-service-code-free-automated-machine-learning-for-image-classification/
- SQL Server VLDB in Azure: DBA Tasks Made Simple
- Starting with SQL Server 2014, in addition to using files stored on a local disk or on a UNC path, SQL Server also supports placing database files as page blobs in Azure Blob Storage, and accessing them over an http(s) endpoint. This lets SQL Server use certain features of Azure Blob Storage that are not necessarily available in the more traditional storage subsystems.
- These examples show that for a VLDB with files stored directly in Azure Blob Storage, backup/restore scenarios can be greatly simplified by using file-snapshot backups, in much the same way as SAN storage snapshots, if used correctly, simplify these scenarios for large on-premises databases.
- https://blogs.msdn.microsoft.com/sqlcat/2018/01/22/sql-server-vldb-in-azure-dba-tasks-made-simple/
Recent Training and Technical Guides
- SQL Server 2017 on Linux webcast series
- Launching the Azure Storage Solution showcase
- [We are] pleased to announce a new webcast series showcasing innovative technology partners who have built solutions on top of the Azure Storage infrastructure.
- Learn to use solutions you already have, from the vendors you trust, while extending your data center to Azure and building Cloud native solutions with your data.
- https://azure.microsoft.com/en-us/blog/announcing-the-azure-storage-solution-showcase/
- Azure Analysis Services features on Azure Friday
- Watch this Azure Friday recording to hear about some new features in Azure Analysis Services. Query scale out and diagnostic logging were announced at the SQL PASS Summit 2017 and both lend themselves particularly well to the cloud.
- https://azure.microsoft.com/en-us/blog/azure-analysis-services-features-on-azure-friday/
- Migration checklist when moving to Azure App Service
Monthly Script and Tool Tips
- Desired State Configuration (DSC) Planning Update – January 2018
- LCM is the engine that runs DSC
- The team’s plan is to make LCM an open source project in GitHub within the next 12 months.
- To make sure we are not creating unnecessary work for community maintainers, the first implementation of the provider model for LCM will be Windows PowerShell. This means that in the future a new open source LCM would be able to manage configurations that include the existing community-maintained DSC Resources authored using Windows PowerShell. Our intention is that no changes to the resources will be required.
- https://blogs.msdn.microsoft.com/powershell/2018/01/26/dsc-planning-update-january-2018/
- Azure Data Lake tools integrates with VSCode Data Lake Explorer and Azure Account
- The VSCode Data Lake Explorer enhances your Azure login experiences, empowers you to manage your ADLA metadata in a tree like hierarchical way and enables easier file exploration for ADLS resources under your Azure subscriptions. You can also preview, delete, download, and upload files through contextual menu.
- https://azure.microsoft.com/en-us/blog/azure-data-lake-tools-integrates-with-vscode-data-lake-explorer-and-azure-account/
- SQL Setup ToolSuite Introduction (3) – SQL Registry Viewer
- You may want to know what registry keys will be added to system for a SQL server installation.
- This tool pre-reads all SQL server meta data from setup source (the setup media containing those MSI/MSP files). The meta data includes product code, patch code, package code, files, registry keys will be added to system etc. It will then use this prepared meta data to scan registry and display only SQL server related in the UI.
- https://blogs.msdn.microsoft.com/psssql/2018/01/24/sql-setup-toolsuite-introduction-3-sql-registry-viewer/
- https://blogs.msdn.microsoft.com/psssql/2018/01/24/sql-setup-toolsuite-introduction-2-product-browser/
- https://blogs.msdn.microsoft.com/psssql/2018/01/24/sql-setup-toolsuite-introduction-1-fixmissingmsi/
- Managing Azure Secrets on GitHub Repositories
- CredScan monitors all incoming commits on GitHub and checks for specific Azure tenant secrets such as Azure subscription management certificates and Azure SQL connection strings.
- https://azure.microsoft.com/en-us/blog/managing-azure-secrets-on-github-repositories/
- Network Watcher Connection Troubleshoot now generally available
- Azure Network Watcher Connection Troubleshoot...enable[s] you to troubleshoot network performance and connectivity issues in Azure.
- https://azure.microsoft.com/en-us/blog/network-watcher-connection-troubleshoot-now-generally-available/
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services