SQL Updates Newsletter – November 2017
Recent Releases and Announcements
- Cumulative Update #2 for SQL Server 2017 RTM
- Cumulative Update #9 for SQL Server 2016 RTM
- Cumulative Update #6 for SQL Server 2016 SP1
- Hour of Code 2017: Unlock an exciting new world by taking a ‘Hero’s Journey’
- Microsoft has released a new Minecraft tutorial for Hour of Code, called Hero’s Journey, that will be used in classrooms, at after-school programs, community centers and homes everywhere.
- All Microsoft Store locations will host a free Computer Science for Everyone workshop series for educators and parents at all coding levels who want to learn new coding skills, understand why computer science is critical for students and how to lead their own Hour of Code.
- https://blogs.microsoft.com/blog/2017/11/14/hour-code-2017-unlock-exciting-new-world-taking-heros-journey/
- Power BI Desktop November Feature Summary
- New version of Power BI Report Server now available
- Includes ability to upload Power BI reports that do not require an external connection to SQL Server Analysis Services and ability to set data refresh schedules for those reports.
- https://powerbi.microsoft.com/en-us/blog/new-version-of-power-bi-report-server-now-available/
- Announcing Single Sign-On Support when connecting to data sources from the Power BI Service
- Announcing SQL Operations Studio preview
- SQL Operations Studio is a free, light-weight tool for modern database development and operations for SQL Server on Windows, Linux and Docker, Azure SQL Database and Azure SQL Data Warehouse on Windows, Mac or Linux machines.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/11/15/announcing-sql-operations-studio-for-preview/
- Announcing Project Honolulu, our new Windows Server management experience - preview
- Project “Honolulu” is a flexible, lightweight browser-based customer-deployed platform and solution for Windows Server management scenarios for troubleshooting, configuration, and maintenance.
- https://blogs.technet.microsoft.com/windowsserver/2017/09/22/project-honolulu-technical-preview-is-now-available-for-download/
- Windows server release cadence changes and FAQ on Semi-Annual Channel
- Windows Server now ships along two release channels: Long Term-Servicing Channel and Semi-Annual Channel.
- The Semi-Annual Channel releases are supported for 18 months and a new release will be out twice a year. If you want to put servers in this channel, you should install Windows Server, version 1709, which can be installed in Server Core mode or as Nano Server run in a container.
- Windows Server, version 1709 is not an update to Windows Server 2016 -it is the first Windows Server release in this new Semi-Annual Channel.
- [webinar] https://infopedia.eventbuilder.com/event?eventid=m9o6s9
- https://blogs.technet.microsoft.com/windowsserver/2017/10/26/faq-on-windows-server-version-1709-and-semi-annual-channel/
- /en-us/windows-server/get-started/semi-annual-channel-overview#semi-annual-channel
- Released: Microsoft Kerberos Configuration Manager for SQL Server 4.1
- New GitHub location for AdventureWorks
- AdventureWorks has long been one of the most used database samples to run demos. Its downloads and scripts are now available in the SQL Server Samples repository in GitHub.
- The AdventureWorks and AdventureWorksDW install scripts work on any version of SQL Server. Each script auto-generates the right database compatibility to match the current SQL Server instance. This means you can quickly install either database on any release of SQL Server including CTPs, SPs, and any interim release.
- https://blogs.msdn.microsoft.com/sqlreleaseservices/new-github-location-for-adventureworks/
- https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
- Power BI expands access to intelligence for external guest users
- Power BI users can seamlessly distribute Power BI apps and dashboards to guest users outside of their organization – recipients are able to securely sign into the service using their own organization’s security credentials or personal email address, while the content owner is able to maintain control over the internal data.
- This new feature is the result of Power BI integration with Azure Active Directory (AD) business-to-business (B2B) collaboration.
- https://powerbi.microsoft.com/en-us/blog/power-bi-expands-access-to-intelligence-for-external-guest-users/
- We recently published the Release Candidate for PowerShell Core 6!
- We are currently targeting having the GA release on January 10th, 2018.
- PowerShell Core 6.0.0 will adopt the Microsoft Modern Lifecycle for support. Essentially, this means that barring any critical security fixes, customer are expected to install the latest released version of PowerShell Core.
- https://blogs.msdn.microsoft.com/powershell/2017/11/17/powershell-core-6-release-candidate/
- DSC Resource Kit Release November 2017
- We recommend that you use PowerShellGet to install DSC resource modules: Install-Module -Name < module name >
- To update all previously installed modules at once, open an elevated PowerShell prompt and use this command: Update-Module
- After installing modules, you can discover all DSC resources available to your local system with this command: Get-DscResource
- https://blogs.msdn.microsoft.com/powershell/2017/11/15/dsc-resource-kit-release-november-2017/
- Released: SQL Server 2017+ and Replication Management Packs (7.0.0.0)
- You can now monitor SQL Server 2017 on Windows and on Linux!
- We are moving to version agnostic MPs to address this issue. This will be valid going forward. The new MP is named SQL Server 2017+. The ‘+’ in the name indicates that it will be used to monitor SQL Server 2017 and the releases that come after that. Current in-market MPs (2008 through 2016) will not be changed and the 2017+ MP cannot be used to monitor older releases.
- SQL Server 2017+ Management Pack introduces Agentless Monitoring mode support. This monitoring mode is designed to support SQL Server on Linux but it also works for Windows deployments.
- https://blogs.msdn.microsoft.com/sqlreleaseservices/released-sql-server-2017-and-replication-management-packs-7-0-0-0/
- Exciting AI Platform & Tools Announcements from Microsoft
- Azure Databricks, VS Tools for AI, and more
- https://blogs.technet.microsoft.com/machinelearning/2017/11/15/exciting-ai-platform-tools-announcements-from-microsoft/
- Announcing General Availability of Azure Reserved VM Instances (RIs)
- Azure RIs enable you to reserve Virtual Machines on a one- or three-year term, and provide up to 72% cost savings versus pay-as-you-go prices.
- Azure RIs give you price predictability and help improve your budgeting and forecasting.
- https://azure.microsoft.com/en-us/blog/announcing-general-availability-of-azure-reserved-vm-instances-ris/
- Automatic tuning will be a new default
- Automatic tuning, technology that continuously monitors and automatically improves database performance in Azure SQL Database, will be enabled by default in the upcoming period.
- All servers that do not have automatic tuning explicitly configured will inherit Azure defaults, making automatic tuning enabled. Similarly, all databases that do not have automatic tuning explicitly configured will inherit the configuration from the parent server. All newly created databases by default will inherit the configuration from the parent server.
- https://azure.microsoft.com/en-us/blog/automatic-tuning-will-be-a-new-default/
- Introducing query replica scale-out for Azure Analysis Services
- With scale-out, client queries can be distributed among multiple query replicas in a query pool, reducing response times during high query workloads.
- https://azure.microsoft.com/en-us/blog/introducing-query-replica-scale-out-for-azure-analysis-services/
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)
- In-Memory OLTP Indexes – Part 1: Recommendations + Part 2: Performance Troubleshooting Guide
- Azure Analysis Services integration with Azure Diagnostic Logs
- Perfect statistics histogram in just few steps
- Why does updating with fullscan result in fewer histogram steps than when doing a sampled scan?
- With sampling [...] SQL Server pulls random pages worth of data, then extrapolates distribution.
- Having more steps in a statistic object is not always synonym of better key value coverage, and better estimations.
- https://blogs.msdn.microsoft.com/sql_server_team/perfect-statistics-histogram-in-just-few-steps/
Recent Blog Posts and Articles
- Making parallelism waits actionable
- We announced changes to how users will be able to make parallelism waits more actionable – specifically the “infamous” CXPACKET. This change effectively splits CXPACKET waits into an actionable wait (CXPACKET) and a negligible wait (CXCONSUMER). This change will be effective starting with upcoming SQL Server 2017 CU3 and SQL Server 2016 SP2 (this wait type is already present in Azure SQL Database).
- Parallelism is implemented as if it were two operators. There’s the producers that push data to consumers, and the consumers that may have to wait for data from producers.
- Producer waits are the ones that may require attention, while consumer waits are inevitable as a passive consequence of longer running producers.
- https://blogs.msdn.microsoft.com/sql_server_team/making-parallelism-waits-actionable/
- Sentiment analysis with Python in SQL Server Machine Learning Services
- Azure SQL Databases Disaster Recovery 101
- New Showplan enhancements
- We have introduced overall query CPU and elapsed time tracking for statistics showplan xml (both in ms). These can be found in the root node of an actual plan.
- One other information you can now find in showplan is trace flags. This is relevant to understand what trace flags are active during compilation, and which one (if any) actually influence compilation.
- https://blogs.msdn.microsoft.com/sql_server_team/new-showplan-enhancements/
- Automatic tuning introduces Automatic plan correction and T-SQL management
- Automatic plan correction, feature introduced in SQL Server 2017, is now making its way to Azure SQL Database as a tuning option Force Last Good Plan.
- To view the current state of automatic tuning options on Azure SQL Database: SELECT * FROM sys.database_automatic_tuning_options
- To enable, use ALTER DATABASE current SET AUTOMATIC_TUNING...
- To view the history of recent automatic tuning recommendations: SELECT * FROM sys.dm_db_tuning_recommendations
- https://azure.microsoft.com/en-us/blog/automatic-tuning-introduces-automatic-plan-correction-and-t-sql-management/
- How we made backups faster with SQL Server 2017
- There are couple of places in the backup where we need to iterate and scan through the buffer pool to drain out pending modifiers or IO writes to buffers before we move on to data copy operation for backup.
- The first iteration is when we clear the differential bitmap. The second iteration is when we create a backup sync object.
- In SQL Server 2017, we eliminated the buffer pool iteration at both places by alternatives that make backups faster. To eliminate first iteration, we made use of indirect checkpoint prepare to dirty list to identify all the modifiers for that database which started before the clearing of differential bitmap. To eliminate second iteration, we made use of the IO dispensers maintained by SQL Server at a lower level which tracks all pending page IO operations.
- https://blogs.msdn.microsoft.com/sql_server_team/how-we-made-backups-faster-with-sql-server-2017/
- SQL Server 2017 and Azure Data Services – The ultimate hybrid data platform
- Changes to hashing algorithm for self-signed certificate in SQL Server 2017
- Starting with SQL Server 2005, a self-signed certificate is created automatically during the startup to be used for channel encryption.
- Beginning with SQL Server 2017, the self-signed certificate now uses SHA256 algorithm which is more secure compared to SHA1 algorithm. Having said that, we still recommend using a certificate obtained from trusted certification authority to be used for channel encryption.
- https://blogs.msdn.microsoft.com/psssql/2017/11/08/changes-to-hashing-algorithm-for-self-signed-certificate-in-sql-server-2017/
- JSON vs CLR UDT – performance comparison
- This post [...]will show you the case where JSON is 5x faster than the equivalent CLR type.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/11/15/json-vs-clr-udt-performance-comparison/
- Configure TDE encrypted database in SQL Server AlwaysOn Availability Group
- A technical overview of Azure Databricks
- Accelerating the adoption of enterprise blockchain
Recent Training and Technical Guides
- DevOps at Microsoft
- [Webinar Nov 30] Power BI Reporting Webinar: Transforming "Good" to "Great!"
- On-Demand Webinar – AI Development Using Data Science VMs (DSVM), Deep Learning VMs (DLVM) & Azure Batch AI
- In-Memory OLTP Updated Overview and Case Studies
- Developing AI applications on Azure: learning plans
- [E-Book] Azure Virtual Datacenter Guidance
- Azure Virtual Datacenter (VDC) is an approach to making the most of the Azure cloud platform's capabilities while respecting your existing security and networking policies.
- https://azure.microsoft.com/en-us/blog/azure-virtual-datacenter/
Monthly Script and Tool Tips
- Introducing Visual Studio Live Share
- Live Share enables your team to quickly collaborate on the same codebase without the need to synchronize code or to configure the same development tools, settings, or environment
- When you share a collaborative session, your teammate sees the context of the workspace in their editor. This means your teammate can read the code you shared without having to clone a repo or install any dependencies your code relies on. They can use rich language features to navigate within the code; not only just opening other files as text but using semantic analysis-based navigation like Go to Definition or Peek.
- https://code.visualstudio.com/blogs/2017/11/15/live-share
- Tool which aides in the creation of your JSON file template for Power BI themes
- You can customize the look and feel of the Power BI reports using the formatting pane for each specific visual, or you can speed up the process by using a JSON file.
- https://community.powerbi.com/t5/Community-Blog/Help-creating-your-JSON-file-template-for-themes-using-a-Power/ba-p/303052
- Azure DevOps Project – public preview
- Creating a DevOps Project provisions Azure resources and comes with a Git code repository, Application Insights integration and a continuous delivery pipeline setup to deploy to Azure. The DevOps Project dashboard lets you monitor code commits, builds and, deployments, from a single view in the Azure portal.
- https://azure.microsoft.com/en-us/blog/azure-devops-project-public-preview/
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services