SQL Updates Newsletter – April 2018
Recent Releases and Announcements
- Cumulative Update #6 for SQL Server 2017 RTM
- Starting with SQL Server 2017, we are adopting a new modern servicing model. Please refer to our blog for more details on Modern Servicing Model for SQL Server: https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-the-modern-servicing-model-for-sql-server/
- Summary of new servicing model for SQL Server: (1) SPs will no longer be made available. Only CUs, and GDRs when needed (2) CUs will be delivered more often at first and then less frequently. Every month for the first 12 months, and every quarter for the remainder 4 years of the full 5-year mainstream lifecycle.
- https://blogs.msdn.microsoft.com/sqlreleaseservices/cumulative-update-6-for-sql-server-2017-rtm/
- SQL Server 2016 Service Pack 2 is now available
- Includes performance and scale improvements for SQL Server, such as improved database backup performance on large memory machines and added backup compression support, which helps performance of almost all databases.
- A new DMV sys.dm_db_log_info is introduced in SQL Server 2016 SP2 to expose the VLF information similar to DBCC LOGINFO to monitor, alert and avert potential T-Log issues experienced by customers.
- New columns added to the sys.dm_os_sys_info DMV to expose the processor related information, such as socket_count, and cores_per_numa.
- New column added to sys.dm_db_file_space_usage to track the number of modified extents since the last full backup.
- Users can now query DATABASEPROPERTYEX([database], ‘lastgoodcheckdbtime') to obtain a single value representing the date/time of the last successful DBCC CHECKDB run on the specified database.
- Support for cancelling request through setting CPU time out in Resource Governor - Improves the handling of query requests by actually cancelling the request, if CPU thresholds for a request is reached. This behavior is enabled under trace flag 2422.
- Introduces the CXCONSUMER wait type which makes parallelism waits actionable: https://blogs.msdn.microsoft.com/sql_server_team/making-parallelism-waits-actionable/
- And more: https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-2-sp2-released/
- https://support.microsoft.com/en-us/help/4052908/sql-server-2016-service-pack-2-release-information
- Virtual Machine Serial Console access
- Serial Console for Virtual Machines is now available in all global regions. You can access it by going to the Azure portal and visiting the Support + Troubleshooting section
- https://azure.microsoft.com/en-us/blog/virtual-machine-serial-console-access/
- Support for tags in cost management APIs is now available
- The Cloud Cost Management (CCM) APIs provides a rich set of APIs to get detailed reporting on your Azure usage and charges.
- We're announcing the support for tags in both the Usage Details API and the Budgets API.
- This release only supports tags for subscriptions in Enterprise Agreements (EA), in future releases we plan to support other subscription types as well.
- https://azure.microsoft.com/en-us/blog/announcing-the-support-for-tags-in-cost-management-apis/
- A new IP strategy for a new era of shared innovation
- We are announcing Microsoft's Shared Innovation Initiative. It is based on a set of principles designed to address co-created technology and intellectual property (IP) issues that give customers clarity and confidence regarding their work with Microsoft.
- https://blogs.microsoft.com/blog/2018/04/04/a-new-ip-strategy-for-a-new-era-of-shared-innovation/
- Gartner recognizes Microsoft as a leader in enterprise integration
- Gartner's Magic Quadrant for Enterprise Integration Platform as a Service (eiPaaS), 2018 positions Microsoft as a leader and it reflects Microsoft's ability to execute and [its] completeness of vision.
- https://azure.microsoft.com/en-us/blog/gartner-recognizes-microsoft-as-a-leader-in-enterprise-integration/
- Released: Microsoft OLE DB Driver for SQL Server
- To use the new driver in existing applications, you should plan to convert your connection strings from sqlncli<x> or sqloledb, to msoledbsql. For example, for a trusted connection using SQL Native Client (SNAC11), plan to convert from:
- Provider=SQLNCLI11; Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=yes; -to- Provider=MSOLEDBSQL; Server=myServerName\theInstanceName; Database=myDataBase;Trusted_Connection=yes;
- https://blogs.msdn.microsoft.com/sqlreleaseservices/released-microsoft-ole-db-driver-for-sql-server/
- Please note that in October 2017, we announced our plans to un- deprecate OLE DB data access technology: https://blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/announcing-the-new-release-of-ole-db-driver-for-sql-server/
- The April release of SQL Operations Studio is now available
- Replicated Tables now generally available in Azure SQL Data Warehouse
- We are excited to announce that Replicated Tables, a new type of table distribution, are now generally available in Azure SQL Data Warehouse (SQL DW).
- With Replicated Tables, the data is available on all compute nodes, hence data movement is eliminated, and queries run faster. In some cases, such as small dimension tables, choosing a Replicated Table versus a Round Robin table, can increase performance because data movement is reduced.
- https://azure.microsoft.com/en-us/blog/replicated-tables-now-generally-available-in-azure-sql-data-warehouse/
- Blazing fast data warehousing with Azure SQL Data Warehouse
- We announced general availability of Azure SQL Data Warehouse (SQL DW) Compute Optimized Gen2 tier
- Azure SQL DW Compute Optimized Gen2 tier increases the number of concurrent queries that can be executed. With support for 128 concurrent queries, Azure SQL DW delivers four times more concurrency compared to the previous generation.
- To enable new concurrency levels, workload management functionality has been extended. Each SQL Data Warehouse SLO comes with predefined query capacity quotas. As the query concurrency grows, available capacity is consumed until the quota is reached.
- One of the recent hardware innovations becoming widely available are NVM Express (NVMe) solid-state drive (SSD) devices. NVMe SSDs offer significantly more I/O bandwidth than SATA SSDs or hard drives. A typical single NVMe device used in Azure, generally offers up to 2GB/sec of local I/O bandwidth, with multiple devices available per physical host, resulting in bandwidth previously reserved only to very high-end storage systems. Azure SQL DW Compute Optimized Gen2 tier fully takes advantage of NVMe devices through adaptive caching of recently used data on NVMe.
- https://azure.microsoft.com/en-us/blog/adaptive-caching-powers-azure-sql-data-warehouse-performance-gains/
- https://azure.microsoft.com/en-us/blog/blazing-fast-data-warehousing-with-sql-data-warehouse-gen2/
- Streamlining GDPR requests with the Azure portal
- The Azure portal will provide tenant admins a simple, powerful tool to quickly fulfill the Data Subject Requests (DSR) that are central to compliance with the European Union General Data Protection Regulation (GDPR).
- Using it, you can identify information associated with a data subject and will be able to execute DSRs against system-generated logs (data Microsoft generates to provide a given service).
- In addition, Azure enables the fulfillment of DSRs against customer data (data you and your users upload or create) through pre-existing application programming interfaces (APIs) and user interfaces (UIs) across the breadth of services provided.
- https://azure.microsoft.com/en-us/blog/streamlining-gdpr-requests-with-the-azure-portal/
- Replication Enhancement - Distribution Database in Availability Group
- SQL Server engineering team is excited to announce the new enhancement around the configuration of Replication Distribution Database in an Availability Group. This feature enhancement will be available with SQL Server 2017 CU6 and will be ported to SQL Server 2016 in a subsequent CU for SP2.
- https://blogs.msdn.microsoft.com/sql_server_team/replication-enhancement-distribution-database-in-availability-group/
- Transparent Data Encryption with customer managed keys in Azure SQL Database generally available
- We are excited to announce the general availability of Transparent Data Encryption (TDE) with Bring Your Own Key (BYOK) support for Azure SQL Database and Azure SQL Data Warehouse.
- TDE with BYOK support is offered in addition to TDE with service managed keys, which is enabled by default on all new Azure SQL Databases.
- TDE with BYOK support uses Azure Key Vault, which provides highly available and scalable secure storage for RSA cryptographic keys backed by FIPS 140-2 Level 2 validated Hardware Security Modules (HSMs).
- https://azure.microsoft.com/en-us/blog/transparent-data-encryption-with-customer-managed-keys-in-azure-sql-database-generally-available/
- Windows Pull Server Planning Update - April 2018
- A new feature will be released to provide support for SQL Server as the database platform to support Windows Pull Server for DSC. If you are a member of Windows Server Insiders you can test this now by downloading a preview build.
- The focus for new features in DSC pull service will be delivered in Azure Automation DSC. The implementation of pull service in Windows Server 2012 R2 and 2016 will continue to be supported
- https://blogs.msdn.microsoft.com/powershell/2018/04/19/windows-pull-server-planning-update-april-2018/
- Released: Microsoft Azure SQL Database Management Pack (7.0.4.0)
- Released: System Center Management Packs for SQL Server 2008-2016 (7.0.4.0)
- Improvements to SQL Elastic Pool configuration experience
- SQL Database: Long-term backup retention preview includes major updates
- With this update we have eliminated the need for you to deploy and manage a separate Backup Service Vault. Instead, SQL Database will utilize Azure Blob Storage under the covers to store and manage your long-term backups.
- https://azure.microsoft.com/en-us/blog/sql-database-long-term-backup-retention-preview-includes-major-updates/
- A flexible new way to purchase Azure SQL Database
- The new vCore-based model introduces two service tiers, General Purpose and Business Critical. These tiers let you independently define and control compute and storage configurations, and optimize them to exactly what your application requires.
- We're committed to supporting the DTU-based model alongside the new vCore-based option.
- https://azure.microsoft.com/en-us/blog/a-flexible-new-way-to-purchase-azure-sql-database/
- Announcing the general availability of Azure Files share snapshot
- Share snapshots provide a way to make incremental backups of Server Message Block (SMB) shares in Azure Files. Storage administrators can use snapshots directly and backup providers can now leverage this capability to integrate Azure Files backup and restore capabilities into their products.
- https://azure.microsoft.com/en-us/blog/announcing-the-general-availability-of-azure-files-share-snapshot/
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)
- Lesson learned from an Availability Group performance case
- Problem: During stress testing, about every 5~10 minutes the SQL Server 2016 XTP Transactions:Transactions Created/sec counter could drop to zero suddenly and quickly resume to normal within a second or tens of micro-seconds.
- An extended events trace was put in place. We found the latency was happening on the primary replica after the primary receives the LSN harden message from remote node.
- Whenever the transaction sharp drop occurs there were always HADR_LOGPROGRESS_SYNC waits happening which in turn caused lots of HADR_SYNC_COMMIT waits.
- The thread which owned the HADR_LOGPROGRESS_SYNC was in a runnable state, meaning it was waiting for a chance to run on the scheduler.
- Root cause: Another query on the same scheduler was accessing a big in-memory table and not yielding within a second. This caused the HADR_LOGPROGRESS_SYNC thread to wait in runnable queue.
- Solution: We involved product group to add yielding code when scanning the in-memory table and the problem is fixed in SQL2016 SP1 CU7.
- https://blogs.msdn.microsoft.com/psssql/2018/04/05/lesson-learned-from-an-availability-group-performance-case/
- Troubleshooting SQL Server Scheduling and Yielding
- Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups
- Monitoring performance of natively compiled stored procedures - database-scoped configuration options
- Installation of SQL Server 2017 failing with ‘VS Shell installation has failed with exit code 1638
Recent Blog Posts and Articles
- Fill Factor and Data Compression
- If you're using fill factor then enable data compression, is the fill factor still enforced? If you aren't using fill factor and enable data compression does it create free space on the original pages?
- https://blogs.technet.microsoft.com/dataplatform/2018/04/30/fill-factor-and-data-compression/
- Training Many Anomaly Detection Models using Azure Batch AI
- Generate image captions with the Computer Vision API
- Secure your backups, not just your data!
- BotRNot: An R app to detect Twitter bots
- USAFacts breaks down government financial data with Power BI
- Very Large Database Migration to Azure - Recommendations & Guidance to Partners
- The steps performed here are not intended for Homogenous System Copies (a copy where the DBMS and Processor Architecture (Endian Order) stays the same). In general, Homogeneous System Copies should have very low downtime regardless of DBMS size because log shipping can be used to synchronize a copy of the database in Azure.
- Very Large Databases (VLDB) are now commonly moved to Azure. Database sizes over 20TB require some additional techniques and procedures to achieve a migration from on-premises to Azure within an acceptable downtime and a low risk.
- A fully optimized VLDB migration should achieve around 2TB per hour migration throughput per hour or possibly more. This means the data transfer component of a 20TB migration can be done in approximately 10 hours. Various postprocessing and validation steps would then need to be performed.
- https://blogs.msdn.microsoft.com/saponsqlserver/2018/04/10/very-large-database-migration-to-azure-recommendations-guidance-to-partners/
- Continuous integration and deployment using Data Factory
- Not Hotdog: An R image classification application, using the Custom Vision API
- Intelligent Edge: Building a Skin Cancer Prediction App with Azure Machine Learning, CoreML & Xamarin
- Lesson Learned #35: How to transfer the login and user from OnPremise to Azure SQL Database
- Implementation patterns for big data and data warehouse on Azure
Recent Training and Technical Guides
- Learn from experts and play with emerging tech at Microsoft Build
- Power BI Desktop April Feature Summary
- Power BI GDPR Whitepaper is now available
- Power BI Service and Mobile March Feature Summary
- Preview: SQL Database Transparent Data Encryption with Azure Key Vault configuration checklist
Script and Tool Tips
- Unit Testing your Database Changes
- Learn about two tools that can help you write database tests, in TSQL, and run them as part of your CI/CD pipeline.
- https://channel9.msdn.com/Shows/DevOps-Lab/Unit-Testing-your-Database-Changes
- Celebrating 1M installations milestone for mssql extension for VS Code with another update
- A new update for the mssql extension for VS Code is now available on the VS Code Marketplace.
- https://blogs.technet.microsoft.com/dataplatforminsider/2018/04/10/celebrating-1m-installations-milestone-for-mssql-extension-for-vs-code-with-another-update/
- Drill Down & Up in Power BI Mobile apps
- Now in Power BI Mobile app you can continue to analyze your data and gain even more insights by using drill down and drill up in your report's visuals.
- https://powerbi.microsoft.com/en-us/blog/drill-down-up-in-power-bi-mobile-apps/
- Calling Http endpoints in T-SQL using CURL extension
- SQL Server Database Engine don't have built-in functions that would enable you to send information to some API using http protocol. If you would need to call some REST endpoint or a web hook from the T-SQL code, you would need to use WebClient or WebRequest classes from .Net framework and expose them as T-SQL function or procedure. In this post you will see how to create that kind of extension.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/04/17/calling-http-endpoints-in-t-sql-using-curl-extension/
- Python in Visual Studio 15.7 Preview 4
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services