The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

If you have been working with SQL Server long enough, you have probably been involved with a SQL Server 2000 to 2005 upgrade. It was painful for many shops due to the deprecation of some features like DTS and the removal of old-fashioned comma joins with the plus operator. It was worth the pain as SQL Server became a real enterprise worthy database system with that release. There have been six releases of SQL Server since then, with 2019 on the way.

Microsoft has provided tools to help with upgrades such as the Upgrade Advisors and a performance counter to figure out if you are using any deprecated features which might break your application when you upgrade. Theoretically, you figure this out early enough to rewrite any problematic code. As a last resort for some features, you can keep the database in an older compatibility mode to make the database work as if it is on an older server version until the issues are resolved.

Of course, making new versions of SQL Server is not about taking away features, but adding new functionality. In addition to these improvements, the teams have made changes to the optimizer in recent years to squeeze out even better performance. One group of optimization features is called Intelligent Query Processing. These features, such as Table Variable Deferred Compilation and Scalar UDF Inlining, can help solve issues that have been plaguing many of us for years.

In 2014, Microsoft began working on cardinality estimation. This is great news as most queries are positively impacted by the changes. Unfortunately, there are always a handful of queries that perform worse after the change. By changing the compat level, you turn this feature on or off for the entire database, so you can’t avoid running into a few problems.

To get over this issue, Microsoft has added a feature to SQL Server Management Studio 18 (still in preview at the time of this writing). This feature is called the Query Tuning Assistant (QTA). QTA works with Query Store and finds queries that perform worse after a database is switched to a higher compatibility mode. You can then decide to keep an older, better execution plan in place.

The tool has a wizard-like interface to get things set up. You specify which version of SQL Server to test for and how long to collect the baseline workload. Once set up, you’ll launch a dashboard to track progress, upgrade the database, and make decisions about queries. Having this tool available will make upgrading that much easier. This is important because you lose a lot of functionality by keeping databases in lower compat modes.

I’ve been amazed at the advances made to SQL Server over the past few versions and the announcements for this year. I’m glad that Microsoft is providing tools to address some of the pain that customers have experienced when upgrading.

Kathi Kellenberger from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Webinars

Women in Tech Talk Tech - March is Women’s History Month, to celebrate the PASS Women in Technology Virtual Group (@PASS_WIT) held a webcast featuring prominent woman in the Data Platform community. I had the pleasure of being one of five panelists alongside these amazing women ......(more)

How to Overcome the Top Blockers for Implementing Database DevOps - Join Microsoft Data Platform MVP Kendra Little to dive into the key findings of Redgate’s 2019 State of Database DevOps Report and investigate the growing importance of the database in successful DevOps and IT performance. ...(more)

SQL in the City Streamed: Standardizing Database Change Management across your Organization - Wednesday April 3, 09:00-14:00 Eastern / 14:00 - 19:00 BST - Join online for sessions designed to broaden your skillset, support your ongoing learning, and keep you up-to-date with the industry, presented by Steve Jones, Kendra Little, Grant Fritchey and Kathi Kellenberger....(more)

How DevOps keeps DBAs safe from being automated out of a job - Thursday March 28, 16.00-17.00 GMT / 11.00-12.00 CDT - Join our expert panel of Microsoft MVPs Kendra Little, William Durkin and Hamish Watson as they discuss how Database DevOps offers DBAs the opportunity to develop expertise in areas for which demand is increasing and how it can advance your career as a DBA....(more)

Virtualization and Containers

More things for using K8s for real - In my current job, the team are working on deploying an application onto Azure using Kubernetes, so it was time to do some reading around the technologies that are being used. Docker is used by the company as the universal packaging mechanism. The engine ......(more)

Using docker named volumes to persist databases in SQL Server - I’ve previously talked about using named volumes to persist SQL Server databases but in that post I only used one named volume and I had to manually reattach the databases after the container spun up. This isn’t really ideal, what we’d want is for the ......(more)

SSIS, Docker, and Windows Containers, Part 1 – Why? - In my first post in this series, SSIS, Docker, and Windows Containers, Part 0 – Getting Started, I covered: Installing dockerChoosing a Container OSPulling a test imagePulling a Windows Container with SQL Server installedinteracting with with your instantiated ......(more)

T-SQL

The Curiously Large CASE Statement - A powerful little expression in SQL Server is the CASE statement. The CASE statement has the ability to “transform” data per business rules to help better understand the data in a given query. In the simplest form, the CASE statement is a statement that ......(more)

What A Difference A CTP Makes - FROID Rage To test FROID, which is the codename for Microsoft’s initiative to inline those awful scalar valued function things that people have been griping about for like 20 years, I like to take functions I’ve seen used in real life and adapt them ......(more)

Tools

The March release of Azure Data Studio is now available - Were excited to announce the March release of Azure Data Studio (formerly known as SQL Operations Studio) is now available. Download Azure Data Studioand review the Release notes to get started. Please note: If youre currently using the preview version ......(more)

Importing Data With Azure Data Studio - By now, you’ve probably heard of Azure Data Studio, the lightweight, cross-platform tool for data professionals to connect to, query, and otherwise explore data sources for all sorts of databases, and not just Microsoft SQL Server... ...(more)

Extended Events in Azure Data Studio - I was prompted to dig into using Extended Events in Azure Data Studio (ADS) after the latest release came out from the Tools Team this past Monday. After you download and install ADS, and connect to an instance, you’ll want to navigate to the Extension ......(more)

The free SQL to Excel Export tool - Earlier this month I released a new, free tool to export SQL Server query data to Excel files without needing Excel. It also installs stored procedures and runs them, if that’s what you desire. Go ahead and play with it. I’ll wait. There was some positive ......(more)

Tech News

How The Fastest-Growing Data Company Uses Data For Everything - Four leaders from Periscope Data's lines of business share stories about how our internal data team empowers them to do their job better. ...(more)

SQL Server Security and Auditing

Lesson Learned #80: Monitoring Login-Logout events in Azure SQL Database using Extended Events - Today, I created an extended event for monitoring the Login and Logout events for a Azure SQL Database. I did it, because I missed the logout in SQL Auditing Log. ...(more)

SQL Server on Linux

SQL Server Data Pages on Linux - One of the new phrases coming out of Microsoft is that “SQL is just SQL” regardless of what operating system it resides on. This was echoed during the keynote at SQL Bits 2019 by the Microsoft team, which you can watch here. Later that weekend, I ......(more)

SQL Server News

PASS turns 20 - Its really hard to believe that its been 20 years since the start of the PASS organization – the volunteer run database education group that I am proud to be associated with. My association runs to 18 of those 20 years, and 16 of those have been with ......(more)

Mixed feelings over the new truncation error - As of SQL 2019 CTP2.0 or SQL 2017 CU12 Microsoft has given us a long-awaited addition to the truncation error. Yay! But … Yes, the new error is awesome! It gives us information about exactly what column isn’t big enough and what data is going to be truncated. ...(more)

Security news and thoughts

Cyber Attack Puts a Spotlight on Fragile Global Supply Chain - The ransomware attack crippling one of the world’s top aluminum makers is exposing how crucial sophisticated digital systems have become in the centuries-old industry of turning mined rock into metal products. ...(more)

Edge Computing Is a New Frontier for Cybersecurity - Distributed, ubiquitous computing infrastructure can come at the price of a much bigger attack surface. ...(more)

RSA 2019: Emerging Cyber Threats - IT Pro Today's Maggie Miller spoke with security experts at the RSA 2019 conference to find out the top emerging security threats to businesses. ...(more)

Reporting Services

Backup your Reporting Services Encryption Key - If you run SQL Server Reporting Services, part of your DR plan needs to include a backup of the encryption key for SSRS. This sadly is an all to often overlooked part of the solution, even though it is incredibly easy to do. If you don’t have a backup ......(more)

Product Upgrades and Releases

Invoke-Sqlcmd is Now Available Supporting Cross-Platform - The official SqlServer module now includes a version of the Invoke-Sqlcmd cmdlet that runs in PSCore 6.2 and above. The version of the SqlServer module which contains this cmdlet is 21.1.18095-preview and is available in the PowerShell Gallery. ...(more)

PowerShell

The PowerShell Extension is now in the Azure Data Studio Marketplace - We are excited to announce the PowerShell Extension is available in the Azure Data Studio (ADS) marketplace! Now you can write PowerShell scripts with the full benefits of PowerShell Editor Services using the excellent IDE-like interface that Azure Data ......(more)

PowerPivot/PowerQuery/PowerBI

Dynamic Row Level Security in Power BI with Organizational Hierarchy and Multiple Positions in Many-to-Many Relationship – Part 1 - I have written previously about Dynamic Row Level Security, and some of the patterns of it. Two of the most common patterns that I explained were; Organizational Hierarchy, and Many to Many situations of users and profiles. Recently I came across a couple ......(more)

Power BI reports for consumers (end users) - Adam walks through how to navigate and use Power BI reports for consumers, or end users. This is geared towards someone not really familiar with the Power BI interface or how to use Power BI reports to their fullest. The post Power BI reports for consumers ......(more)

Conditional Formatting with a Text Field in Power BI - One of the things I like about my live, online training courses is that I get to hear great questions from the trenches from people learning DAX and Power BI. Last week, John asked me how to apply conditional formatting with a Text field (not a numeric ......(more)

Performance Tuning SQL Server

Do Queries Always Respect Cost Threshold of Parallelism? – Interview Question of the Week #216 - Question: Do Queries Always Respect Cost Threshold of Parallelism? Answer: No. Not Always. Let us do a quick test about it. First, let us set the Cost Threshold of Parallelism for my query to 10 and Max Degree of Parallelism to 0 for my server. I have ......(more)

Internet of Things

IoT World 2019: IT’s Role in an IoT Environment - Zach Butler, portfolio manager for the IoT World event series, discusses the challenges that IT pros face in an IoT environment and previews the content that’s in store for IT at IoT World in May. ...(more)

DevOps and Continuous Delivery (CI/CD)

How to Do Continuous Delivery without Confusing Users - The ability to update applications quickly and constantly is useful for a variety of reasons, but rapid application changes can backfire. ...(more)

Approaching Zero: A Case Study - This is part eight in a series on near-zero downtime deployments. As we wind down this series, I want to talk through a case study, something of which I’m particularly proud. Here’s the scenario: I joined a product team responsible for approximately ......(more)

Conferences, Classes, and Events

SQL in the City Summits – UK, US & Down Under - Redgate are inviting senior data professionals to attend one of the upcoming SQL in the City Summit events taking place in April, May and June. If you’re interested in learning how your business can benefit from implementing Compliant Database DevOps this event is for you. Find out who’s presenting and register for a Summit near you today. ...(more)

Computing in the Cloud (Azure, Google , AWS)

Azure Everyday Roundup - Short (2-4 minute) video posts to help you learn about the Microsoft Azure analytics and data platform. In November of last year, I joined the Pragmatic Works consulting team as Principal Consultant where I work alongside several great professionals, ......(more)

Career Growth

Writing a presentation 4: Practice and improvement - My presentation so far is really rough. I need to improve a lot before SQL Saturday Chicago! For now, the goals are to practice the presentation and write the demos. Practice, practice, practice I’ve started to talk through what I have done with the ......(more)

Technical Debt Can Be Personal--Here’s How to Handle It - Technical debt is any kind of shortcoming within an IT system that forces the system’s users, or the system itself, to perform extra work. ...(more)

Recruiter Training – SQL Server - Hello Recruiters! Just to start off I want to say that the whole purpose of this post and video is to help you find the right candidates and ask your clients the right questions. If you don’t know what they want, you can’t effectively ask me or my counterparts ......(more)

Data Architect vs. Data Engineer - The data architect and data engineer work in tandem – conceptualizing, visualizing, and then building an Enterprise Data Management Framework. The data architect visualizes the complete framework and creates the blueprint, which the data engineer uses ......(more)

Big Data

Azure Databricks & Spark ETL: Unifying Data Engineering at Cloud Scale - Since the introduction of Azure Databricks in 2018, there has been a lot of excitement around the potential of this Unified Analytics Platform and how it can simplify the complexities of deploying cloud-based analytics solutions. ...(more)

Administration of SQL Server

Scripting out several databases on a server - A while ago, I wrote an article Automated Script-generation with Powershell and SMO about using SMO to script out a SQL Server database. It has remained surprisingly but agreeably popular. SMO is still there, but now part of the sqlserver module that ......(more)

Removing an In-Memory OLTP File Group - Yesterday evening I came across a very interesting behavior in SQL Server: a customer has told me that they have some problems by removing a file group that was used for In-Memory OLTP. Unfortunately that is NOT possible! You can’t remove an In-Memory OLTP file group!...(more)

The Curious Case of… unstoppable DBCC CHECKDB - Last week I was asked to explain an unstoppable DBCC CHECKDB process. The client regularly runs DBCC CHECKDB at night during off-peak load times and has no problems with it but last week they ran it during the day because of a corruption indication, then decided to stop it and couldn’t....(more)

Monitoring Your Databases and Servers and… - You absolutely need to know about the servers you have under management in your estate. You need to know about the databases. Uptime, performance, behaviors, errors, corruption and a much longer list of items that will frankly fill this blog post, are ......(more)

Take the 2019 State of SQL Server Monitoring Survey: Here’s What’s in It For You - Calling all Database Administrators, Developers, Analysts, Consultants, and Managers: Redgate has a survey open asking how you monitor your SQL Servers. Take the survey before April 5, 2019. Your time is valuable. The survey will take 5 – 10 minutes ......(more)

Microsoft Unveils Azure Backup for SQL Server - Azure Backup for SQL Server allows organizations to centralize backup and restore across a fleet of SQL Server instances. ...(more)