SQL Updates Newsletter – December 2017
Recent Releases and Announcements
- Cumulative Update #9 for SQL Server 2014 SP2
- Announcing Power BI Premium support for larger datasets
- Power BI Premium now supports uploading Power BI Desktop (.PBIX) files that are up to 10 GB in size – a x10 increase compared to before.
- https://powerbi.microsoft.com/en-us/blog/announcing-power-bi-premium-support-for-larger-datasets/
- Power BI Desktop December Feature Summary
- This month we are very excited to integrate our Q&A experience into the reporting canvas, offering a whole new way to create your visuals. We are also updating our bookmarking preview with new features that provide you much more flexibility. A highly requested feature, the ability to drill to filter other visuals on the page, is also coming out this month. You can take advantage of several new connectors and major updates to existing ones.
- https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-december-feature-summary/
- Power BI service October and November feature summary
- Power BI now leverages Kerberos to support single sign-on (SSO) when connecting to certain data sources such as SQL Server, Teradata, and SAP HANA via DirectQuery.
- https://powerbi.microsoft.com/en-us/blog/power-bi-service-october-and-november-2017-feature-summary/
- R 3.4.3 released
- Azure Log Analytics (OMS) Agent now collects SQL Server audit logs
- The Azure Log Analytics (OMS) platform can now be used as a centralized data store for all your SQL Server audit logs
- https://blogs.msdn.microsoft.com/sqlsecurity/2017/12/28/azure-log-analytics-oms-agent-now-collects-sql-server-audit-logs/
- SQL Vulnerability Assessment now available for SQL Server 2012 and up
- SQL Vulnerability Assessment has been available for preview on Azure SQL Database for a couple of months, and has now been released on SSMS 17.4, supporting scanning of SQL Server 2012 and up. Whether on-premises or on a VM!
- https://blogs.msdn.microsoft.com/sqlsecurity/2017/12/11/sql-vulnerability-assessment-now-available-for-sql-server-2012-and-up/
- New in SSMS – Always On Availability Group Latency Reports
- With the new SSMS 17.4 release, we are introducing the Availability Group Latency data collection and reporting built into the Availability Group dashboard. This feature masks the capture and analysis of the Extended Events from the end user and provides an easy to understand report detailing the time spent during the various phases of the Log Transport process.
- https://blogs.msdn.microsoft.com/sql_server_team/new-in-ssms-always-on-availability-group-latency-reports/
- Azure Database Migration Service Public Preview brings the “lift and shift”
- The Azure Database Migration Service Public Preview is here to help you move on-premises SQL Servers to the cloud with near-zero downtime.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/11/30/azure-database-migration-service-preview-brings-the-lift-and-shift/
- The December release of SQL Operations Studio is now available
- SQL 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/2017/12/19/the-december-release-of-sql-operations-studio-is-now-available/
- 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.
- https://blogs.msdn.microsoft.com/analysisservices/2017/12/20/asynchronous-refresh-with-the-rest-api-for-azure-analysis-services/
- DSC Resource Naming Guidelines
- DSC Resources going forward are no longer required to use the “x” or “c” naming conventions.
- Instead, the guideline is that resources should use either the existing module name (if DSC Resources are combined with an existing module containing PowerShell functions) or the “Dsc” suffix. The best practices for finding a DSC Resource module is to search the PowerShell Gallery using the “DSC Resource” filter, or use the cmdlet: Find-DscResource
- The project name will not have any relationship with how or whether the project is supported.
- https://blogs.msdn.microsoft.com/powershell/2017/12/08/dsc-resource-naming-and-support-guidelines/
- Try mssql-cli, a new interactive command line tool for SQL Server
- We are excited to announce the Public Preview release of mssql-cli, a new and interactive command line query tool for SQL Server. This open source tool works cross-platform and is a proud member of the dbcli community.
- In order to install mssql-cli, you must have Python on your machine.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/12/12/try-mssql-cli-a-new-interactive-command-line-tool-for-sql-server/
- Announcing the General Availability of Azure Bot Service and Language Understanding, enabling developers to build better conversational bots
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)
- Analyze Network Latency Impact on Remote Availability Group Replica
- When network latency becomes an issue the most common symptom you will observe is sustained or growing log send queue. [To monitor...] (1) Add the Log Send Queue size (KB) column in AlwaysOn Dashboard and (2) Add the SQLServer:Database Replica:Log Send Queue Counter
- Measure Network Latency Impact Using Performance Monitor. On the secondary replica, launch Performance Monitor and add the following counters: (1) SQLServer:Database Replica:Log Bytes Received/sec for appropriate database instance and (2) SQLServer:Database Replica:Recovery Queue for appropriate database instance
- On the primary replica, launch Performance Monitor and add the following counters: (1) SQLServer:Databases:Log Bytes Flushed/sec for appropriate database instance and (2) Network Interface:Sent Bytes/sec for appropriate adapter instance
- In order to better understand how fast an application can push changes to the remote server, use a third-party network bandwidth performance tool, [such as] iPerf or NTttcp.
- https://blogs.msdn.microsoft.com/alwaysonpro/2017/12/21/analyze-network-latency-impact-on-remote-availability-group-replica/
- Availability Group Database Reports Not Synchronizing / Recovery Pending After Database Log File Inaccessible
- You may find that one or more availability group databases [..] reported ‘Not Synchronizing / Recovery Pending’ on the primary replica or ‘Not Synchronizing’ on one of the secondary replicas. Despite this, your availability group replicas report they are in the primary role or secondary role.
- This may occur if SQL Server is unable to access the database log files of your availability group database(s).
- https://blogs.msdn.microsoft.com/alwaysonpro/2017/11/29/availability-group-database-reports-not-synchronizing-recovery-pending/
- Centennial apps/desktop bridge, SQL Server and error "The data area passed to a system call is too small."
- Problem: Launching a Centennial application may fail with the following error: The data area passed to a system call is too small.
- Cause: This issue may be due to miscommunication between two filter drivers, namely WCNFS (the desktop bridge) and RsFxXXXX.sys driver (filestream system driver). RsFx system driver doesn't honor flags being passed by WCNFS driver appropriately, which causes startup failure of any Centennial application with the aforementioned error.
- Status: We will provide a fix for this issue in Cumulative updates for SQL Server versions which are still being serviced.
- Workaround: Disable Filestream feature or Move Filestream data to a different volume
- https://blogs.msdn.microsoft.com/sql_server_team/centennial-appsdesktop-bridge-sql-server-and-error-the-data-area-passed-to-a-system-call-is-too-small/
Recent Blog Posts and Articles
- Injecting JSON objects into existing JSON text with JSON_MODIFY function
- The only way to append a JSON object to the existing JSON array is to “tell” JSON_MODIFY function that the input string is properly formatted/valid JSON fragment. The following functions/operators in SQL Server return valid JSON: (1) FOR JSON clause if it is used without WITHOUT_ARRAY_WRAPPER option (2) JSON_QUERY function (3) JSON_MODIFY function
- If you use any of these operators in the third argument of JSON_MODIFY function, JSON_MODIFY will know that it should not treat the result of these operators as plain text and it will inject the JSON object as-is.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/12/22/injecting-json-objects-into-existing-json-text-with-json_modify-function/
- Storing JSON documents in SQL Database
- Bring clarity to complex data with graph databases on SQL Server 2017
- How cloud speed helps SQL Server DBAs
- R in the Windows Subsystem for Linux
Recent Training and Technical Guides
- [Video] The Trouble with Bias, by Kate Crawford
- [Video] Cloud Tech 10 - 27th November 2017 - Jenkins with ACI, SQL DW with Functions and more!
- Includes Scaling Azure SQL Data Warehouse using Azure Functions
- https://youtu.be/yjGNugqEHGQ
- AI School: Microsoft R and SQL Server ML Services
- Artificial Intelligence - Getting Started with Microsoft AI
- How We Share the Latest AI & ML Developments Within Microsoft
- 4 tips for keeping your resolution to learn Azure
- Free eBook – The Developer’s Guide to Microsoft Azure now available
- Azure Application Architecture Guide
Monthly Script and Tool Tips
- Install SQL Server 2017 Using PowerShell Desired State Configuration and SqlServerDsc
- What’s new in SSMS 17.4: SQL Vulnerability Assessment
- In addition to enhancements and bug fixes, SSMS 17.4 comes with an exciting new feature: SQL Vulnerability Assessment!
- SQL Vulnerability Assessment (VA) is your one-stop-shop to discover, track and remediate potential database vulnerabilities. It can be used as an excellent preventative security measure, providing visibility into your security state and offering actionable steps to investigate, manage and resolve security issues and enhance your database fortifications. It is designed to be usable even for non-security-experts – getting started and seeing an initial actionable report takes only a few seconds.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/12/11/whats-new-in-ssms-17-4-sql-vulnerability-assessment/
- Transitive closure clustering with CLR and JSON
- Transitive closure is a graph algorithm that tries to follow paths in graph edges and tries to find all elements that can be reached from some element, or groups of elements that are mutually reachable. Although SQL Server still ... [doesn't provide] ... native function for transitive closure, this algorithm can be implemented using CLR aggregates that can be placed in SQL database.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/12/20/transitive-closure-clustering-with-clr-and-json/
- What’s new for Python in Visual Studio 2017 15.6 Preview 1
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services