The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

This past week was a dichotomy for Coloradans. On Tuesday, it was 60F (15C) for most of the day, with my daughter and I going to volleyball practice in t shirts and shorts. Our phones chimed during practice, noting that schools had been cancelled for Wednesday, in anticipation of a winter storm. Plenty of people laughed about that at the time, and no shortage of parents were annoyed by the premature decision, especially with little wind and rain falling at 7am Wednesday.

That changed with high winds and some snow all over the area. Cars were stuck on roads, first responders overwhelmed, the airport closed, and people stuck in places between home and work. We hunkered down here at the ranch, and while horse care was hard, we survived. Power flickered for me, but I was able to work Wed and Thur without worry. I also have a generator, but we never lots power longer than the 30s it takes to kick in.

Glenn Berry wasn't quite as lucky, though he came through the storm fine. He did lose power, and wrote about his experiences. Glenn is about 12 miles S of me, and in a rural area. I thought his thoughts on the experience were interesting, and not a lot different from the ones I had recently with another power outage. I don't mind storing gasoline, and we have multiple generators here because of the horses, but I appreciate Glenn's thoughts on finding a different way of storing power.

We both realize that disaster can strike and it's important to think through the issues ahead of time. While I depend on my phone in the event of losing our Internet connection, I could easily lose that as well. The local towers probably run off batteries and in an extended outage, they'd lose power before I would. I have a tractor and can plow out our driveway, but in this case, the county roads were closed Wednesday and Thursday due to the poor conditions and number of cars stranded. Apparently Wed night visibility was inches and cars were being abandoned in the middle of roads. I'd prepared for a few days outage, but if this were still ongoing today, I'd start feeling the pinch from a lack of supplies.

It's not often we encounter a major disaster at work, but it's entirely possible that the secondary support plans we have in place won't work. You might have a supplier for more diesel fuel, but if everyone else needs more, will you still get some? What about issues with a complex process at work? If it fails and you end up rebuilding some ETL flow, do you have the staff or are you expecting a "Brent" to help explain things and do some of the work? I think plenty of us have that dependency, one which we don't think about until that person is on holiday and we need their expertise.

Disasters come in all sizes, but often they seem to come in the size that is at the limit (or just beyond) our preparations. I suggest that during some down times, take a minute and think about your preparations. Think what could go wrong. Do a little brainstorming and gaming of situations. If your plan has an issue, do you have another idea of what to do? It might be good to at least have thought about the potential issues.

Steve Jones 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

Don’t just think DevOps. Think Compliant Database DevOps! - How can the promise of releasing changes to the database faster and easier be balanced with the need to keep data safe and remain compliant with legislation? Join this webinar to discover how the answer lies in in going one step further than database DevOps and thinking about Compliant Database DevOps....(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)

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)

Virtualization and Containers

SQL Server Availability Group Failovers in Kubernetes - (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) In my previous blog posting I have talked about how ......(more)

Using Kubernetes Deployments for Updating SQL Server - In Kubernetes we can leverage Controllers to help manage our application state, keeping them in the desired state. In this blog post, we’re going to look at how to use a Deployment Controller to manage the application state of SQL Server in Kubernetes. ...(more)

Vendors/3rd Party Products

Have your say on the state of SQL Server monitoring - In this blog, we review the key insights from last year’s State of SQL Server Monitoring report. One year on, how has this changed? Read the blog to find out how you can help us define the state of 2019 plus a chance to win a $250 Amazon voucher. ...(more)

T-SQL

Advanced SQL: CROSS APPLY and OUTER APPLY - In this article, we’ll look into the “APPLY” operator and its variations – CROSS APPLY and OUTER APPLY along with examples of how they can be used. In particular, we will learn: the difference between CROSS APPLY and the JOIN clause how to join the ......(more)

Converting a History Table into a System-Versioned Temporal Table - SQL Server has had many different methods to track changes to data. There has been old-fashioned trigger-based logging, Change Data Capture, and Change Tracking. All of these features allow you to see how data has changed with varying degrees of how ......(more)

Build a CREATE TABLE statement from the output of a Stored Procedure with dm_exec_describe_first_result_set - Day-to-day database administration and development on SQL Server will often require creating a temporary table to hold the output of some stored procedure. The code below uses the dm_exec_describe_first_result_set 1 system dynamic management function, ......(more)

T-SQL bugs, pitfalls, and best practices – determinism - Like any programming language, T-SQL has its share of common bugs and pitfalls, some of which cause incorrect results and others cause performance problems. In many of those cases, there are best practices that can help you avoid getting into trouble. ...(more)

How to use SQL Cursors for Special Purposes - This article describes SQL cursors and how to use them for some special purposes. It highlights the importance of SQL cursors along with their downsides. It is not always the case that you use SQL cursor in database programming, but their conceptual ......(more)

Why You Shouldn’t Hardcode the Current Database Name in Your Views, Functions, and Stored Procedures - “There are only two hard things in Computer Science: cache invalidation and naming things”Phil Karlton I’m terrible at naming things. I recently wrote some quick code to reproduce a design problem and demonstrate several options for solutions, and later ......(more)

Scalar UDF Inlining in SQL Server 2019 – Simpler functions - I recently published a post detailing the new Scalar UDF Inlining feature in SQL 2019 here. That post introduced the new feature in a way that I used to compare performance to the other function types, continuing the performance evaluation of functions ......(more)

Gaps and Islands Across Date Ranges - Watch this week’s episode on YouTube. In a traditional gaps and islands problem, the goal is to identify groups of continuous data sequences (islands) and groups of data where the sequence is missing (gaps). While many people encounter gaps and islands ......(more)

Tools

Quick tip: Zoom in Azure Data Studio - If you finally have given a try to Azure Data Studio, and if you use it on a regular basis, maybe you want to customize it to suit your needs.Among the huge customization options it has, you can control the text size in form of zoom. To do change it, ......(more)

Whats a SQL Notebook in Azure Data Studio? - Azure Data Studio is a cross-platform database tool for data professionals using the Microsoft family of on-premises and cloud data platforms on Windows, MacOS, and Linux. Recently Vicky Harp tweeted By the way, you can watch a recording from SQLBits ......(more)

The Lighter Side

How to Handle Power Grid Failure - The Blizzard On March 13-14, 2019, most of eastern Colorado experienced a pretty severe blizzard. A blizzard is when you have snow combined with sustained winds of 35 mph or more that last for three hours or more. This storm was unusual because it was ......(more)

At quick Los Angeles event, Tesla announces the 300-mile-range Model Y - Tonight in Los Angeles, Tesla CEO Elon Musk showed off a prototype version of the Model Y, the fourth mass-produced vehicle that the electric car maker will bring to market. As expected, the vehicle will be ......(more)

Pop quiz: what do these things cost? - It’s been a few years since we’ve stopped in for a sanity check. Quick: without searching the web for prices, put these items in order. Do it on a scratch paper, guessing how much you think each one costs, then arrange ’em. SQL Server 2017 Standard ......(more)

SQL Server on Linux

Does SQL Server 2019 run on Ubuntu 18.04 LTS? - Yes. Here’s the proof from an output of SELECT @@VERSION: [crayon-5c8b1ac251cb0696553406/] Here’s a screenshot of me running mssql-cli on the Ubuntu Server virtual machine in question. SQL Server 2019 (CTP 2.3) is running on 18.04.2 LTS. Is it supported ......(more)

Security news and thoughts

Critical Flaw in Swiss Internet Voting System - Researchers have found a critical flaw in the Swiss Internet voting system. I was going to write an essay about how this demonstrates that Internet voting is a stupid idea and should never be attempted -- and that this system in particular should never ......(more)

A new rash of highly covert card-skimming malware infects ecommerce sites - The rash of e-commerce sites infected with card-skimming malware is showing no signs of abating. Researchers on Thursday revealed that seven sites—with more than 500,000 collective visitors per month—have been ......(more)

A world of hurt after GoDaddy, Apple, and Google misissue >1 million certificates - Enlarge (credit: Internet1.jpg by Rock1997 modified) A major operational error by GoDaddy, Apple, and Google has resulted in the issuance of at least 1 million browser-trusted digital certificates that don’t comply with binding industry mandates. The ......(more)

Prevent Getting Hacked With Ten Counterstrategies - There may be ten surefire ways to invite a malicious actor into your IT assets -- but each of these avenues can be blocked, security expert Paula Januszkiewicz explained during an RSA session. ...(more)

Reporting Services

SQL Server 2017 Reporting Services: The Basics Part 5 - We’re now on Part 5 of our SQL Server Reporting Services Basics Series. In this installment, we’ll learn how to use a range parameter. If you want to review the series: Part 1, Part 2, Part 3, Part 4. You may remember in Part 4 we created a simple parameterized ......(more)

R Language

Bayesian Statistics: Analysis of Health Data - CategoriesRegression ModelsTagsBayesian AnalysisLinear RegressionPlotR ProgrammingThe premise of Bayesian statistics is that distributions are based on a personal belief about the shape of such a distribution, rather than the classical assumption which ......(more)

Product Upgrades and Releases

Security Update: Microsoft PHP Drivers 4.3.1 for SQL Server - We have made available updated packages for the Microsoft PHP Drivers 4.3.1 for SQL Server. The updated drivers were built using an updated compiler with flags to incorporate mitigations which help prevent attacks which take advantage of a class of vulnerabilities that ......(more)

Product Reviews and Articles

Checking on Failed Server Logins, Server Errors and Warnings using SQL Monitor - Whenever I hear of yet another SQL Server exploit that was initiated by a brute-force password attack on the Windows Server host, my standard reaction is to wave my hands in the air, in agitation, whilst explaining the need to continuously and automatically ......(more)

5 reasons why backup and restore doesn’t cut it in dev and test - If developers and testers work with a database that has similar data characteristics and is of a similar size to production, it will lead to fewer surprises when deploying database changes and help the team troubleshoot urgent production bugs or performance ......(more)

Simplify and improve your security model with SQL Census - In the R&D division of Redgate, Foundry, we’re working on a new tool, SQL Census, in an effort to make your SQL Server permissions more manageable by seeing who has access to your servers and restructuring existing access rights into a simpler and more ......(more)

Book Review: “Collect, Combine And Transform Data Using Power Query In Excel And Power BI” by Gil Raviv - I generally try to avoid writing book reviews here, but the fact that there are so few books available on Power Query and M means that I’m making an exception for “Collect, Combine and Transform Data using Power Query in Excel and Power BI” by Gil Raviv. ...(more)

Monitoring SQL Server Performance: What’s Required? - There are three good reasons for monitoring and optimizing the performance of a database: Chronic performance problems: If a database is responding slowly, consistently, it is easy to assume that it is because you need more resources, or a different ......(more)

PowerPivot/PowerQuery/PowerBI

Combining Dimension Tables in Power BI using Power Query: Basics and Foundations of Modeling - The article that I wrote earlier this week about the shared dimension had a lot of interest, and I’m glad it helped many of you. So I thought better to write about the basics of modeling even more. In this article, I will be focusing on a scenario that ......(more)

Power BI Apps for Consumers - Adam looks at how you can use Power BI Apps as a consumer of dashboards and reports. Power BI Apps are the main way that dashboards and reports are distributed widely in your organization. The post Power BI Apps for Consumers appeared first on Guy in ......(more)

Attaching Manually-Created Common Data Model Folders In Power BI Dataflows - Most of the focus on Power BI dataflows so far has been on the use of Power Query Online to load data from external data sources. However, when you create a dataflow you also have the option to attach an existing Common Data Model (CDM) folder stored ......(more)

Using SSMS with Power BI - I’m curious by nature and many have shown interest when I connect Power BI to the SQL Profiler to collect performance information, but if you can do that, what happens when you connect it to the SQL Server Management Studio, (SSMS)? That can seem quite ......(more)

Showing the Total Value in Stacked Column Chart in Power BI - Although very simple, still I get this question very often; How can I show the total value as a data label in a stacked column chart? In this quick blog post, I’m going to show you a quick and easy method to use a combo chart and show the total as a ......(more)

Power BI Paginated reports: using String_Split to improve multi-value parameters - Patrick looks at how you can use the STRING_SPLIT SQL Function to make life easier when using multi-valued parameters within Power BI Paginated reports. The post Power BI Paginated reports: using String_Split to improve multi-value parameters appeared ......(more)

Find and Delete Duplicate Photos with Power Query - I was trying to clean up my hard disk drive on my PC the other day.  I use a program called WinDirStat to show me graphically what is taking space (as an aside, this is great software and you can download it here).  After running the software I noticed ......(more)

Polybase/HDInsight

PolyBase Revealed: Hive Shim Errors - I just recently worked through an error in which predicate pushdown would work for flat files but would fail with a weird error on ORC files. tl;dr If you’re hitting Hive 3, make sure you’re using SQL Server 2019 CTP 2.3 (or later). The Equipment HDP ......(more)

Performance Tuning SQL Server

Scalar UDF inlining in SQL Server 2019 – FROID’s complex - This page contains the description for my conference session “Scalar UDF inlining in SQL Server 2019 – FROID’s complex”, and links to the slide deck and demo code used in this presentation. Description Target audience Experienced database developers ......(more)

Lock Promotion In Parallel Plans - I Don’t Always Talk About Locks But when I do, it’s usually to tell people they should use RCSI, and then disappear in a cloud of smoke. Recently I was thinking about lock promotion, because that’s what happens when I get lonely. While digging around, ......(more)

SQL SERVER – Default Worker Threads Per Number of CPUs - During my recent SQL Server Performance Tuning Practical Workshop, someone asked, how do they know what is the automatically configured max worker threads for their machine. This is a good question. Microsoft has already answered this question so we ......(more)

How to Know When Memory Constraints are Negatively Impacting CPU in SQL Server? – Interview Question of the Week #216 - Question: How to Know When Memory Constraints are Negatively Impacting CPU in SQL Server? Answer: the other day, I was asked this question when someone wanted to sign up for my Comprehensive Database Performance Health Check. I indeed thought it was ......(more)

Microsoft News

Microsoft to start nagging users in April about the January 2020 Windows 7 end-of-support deadline - Starting next month, Microsoft will start displaying periodic pop-up notifications reminding Windows 7 users that support for that OS is ending early next year....(more)

MDX/DAX

Counting working days in DAX - This article shows a DAX technique to compute the number of working days between two dates. The example includes a Sales table containing order and delivery dates. DAX can compute the difference between two dates by subtracting one from the other. This ......(more)

Using DAX to format a list of values in Power BI Desktop - Alberto Ferrari joins Patrick to walk through how you can use DAX to format a list of values within Power BI Desktop. This takes the concatenate values quick measure to the next level. Original Quick Measure video Alberto's article The post Using DAX ......(more)

Hardware News

Microsoft adds new Surface Pro 6, Surface Laptop 2 configurations for business users - New Intel Core i5, 16 GB of RAM models of Surface Pro 6 and Surface Laptop 2 are coming, starting in the U.S. and Canada, for Surface for Business customers. ...(more)

ETL/SSIS/Azure Data Factory

How and Why to Add a Source Code Repository to Azure Data Factory - For developers, it’s very beneficial to have a source code repository. A source code repository helps to keep all your changes, to manage tasks, branches, share the code with a team and simply put, to keep it in safe place. In this post, I’ll tell you ......(more)

Database Design, Theory and Development

Fourth Order Properties Part 2: Association Relations in Database Design - An Example - Part 1 outlined fundamentals of fourth order properties (4OP) of a multigroup arising from relationships among its group members due to 1:1, M:1, M1:M2, and, generally, M1:M2:M3:...:Mn relationships among the groups' entity members. Fundamentals are ......(more)

Data Privacy, Compliance, and GDPR

Complimentary access to Gartner Guide - Redgate has been acknowledged as a representative vendor in the Gartner 2018 Market Guide for Data Masking. For Gartner’s insights, analysis and recommendations on data masking you can get your complimentary copy of the report from Redgate....(more)

Conferences, Classes, and Events

The Recording for Troubleshooting SSIS is Available - A fantastic crowd attended today’s (13 Mar 2019) free webinar titled Troubleshooting SSIS. I recorded the presentation but told attendees I had no plans to share the recording. I changed my mind. My reason? I’m adding this focused content to SSIS Self-Help, ......(more)

Free Webinar – Faster SSIS - SQL Server Integration Services (SSIS) is a powerful enterprise data integration tool that ships free with Microsoft SQL Server. Join me as I demonstrates practical SSIS performance design patterns. Register today to learn: Load BLObs fasterAn Incremental ......(more)

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. Register now...(more)

Computing in the Cloud (Azure, Google , AWS)

What is Azure Active Directory B2C? - How important is secure identity management to you? If you’re like most, it is a top priority. In today’s post I’ll talk about Azure Active Directory B2C which is an identity management service that enables you to customize and control how users securely ......(more)

Checking Open Ports in Azure - Let’s be honest here, data security is really important to me.  Some people probably think that I go to extremes to ensure that my data as well as my customers data is secured.  With that, sometime ago, I wrote a blog post on utilizing a VPN server that ......(more)

Career Growth

PASS Summit 2019 Call for Speakers is Open – How I Sketch Out My Submission Ideas - Are you interested in speaking at the Professional Association for SQL Server’s annual Summit conference? The call for speakers is now open, and you may submit up to three sessions between now and March 31, 2019. I’m currently in the process of sketching ......(more)

The Most In-Demand Big Data Skills and for IT and Data Professionals - Click to learn more about author Gilad David Maayan. The World Wide Web has grown to include more than 1.8 billion websites. Of these, about 200 million or less are active. With each passing day, thousands of new sites are added to the mix to intensify ......(more)

Technical Accomplishments and Happy Memories (T-SQL Tuesday #112) - The March 2019 edition of T-SQL Tuesday is hosted by Shane O’Neill (@SOZDBA). This month, Shane wants us to share our cookies. Wait… what? Yes! Cookies :) In this analogy, cookies are accomplishments or memories you can look back on when things get tough. ...(more)

Big Data

Is it Time to Drain the Data Lake? - Click to learn more about author Karthik Ramasamy. It sounds appealing – easily store all of your data in a single location, where all of your users and applications can access it and put it to use. It’s no wonder that interest in data lakes rose rapidly ......(more)

How to Get Your Board Members to Love Big Data - Click to learn more about author Oksana Sokolovsky and Rohit Mahajan. As folks in the technology business, we tend to see the best possibilities for emerging technologies; we can easily visualize how the latest and greatest can be used to make us more ......(more)

Azure SQL Managed Instance

Monitor local storage usage on General Purpose Azure SQL Managed Instance - Azure SQL Managed Instance has predefined storage space that depends on the values of reserved storage and vCores that you choose when you provision the instance. In this post you will see how to monitor storage space on the Managed Instance. In Managed ......(more)

Azure SQL Database

Migrating DB2 Databases to Azure - Yep, still doing a lot of database migrations. Just too many people wanting to migrate their other database platforms over to Azure… I have two customers that have DB2 databases and I know how overwhelming it can be to take on a project like this, ......(more)

Azure SQL Data Warehouse and Data Lake

Improve Predictive Healthcare Models with Azure Databricks - Microsoft Azure Resources Advance Digital Transformation Within Healthcare Healthcare organizations struggle with several daunting issues; many affect healthcare quality. Low unemployment, high demand for healthcare providers, and ever-changing healthcare ......(more)

Planning for Accounts, Containers, and File Systems for Your Data Lake in Azure Storage - Now that Azure Data Lake Storage Gen2 is now based on Azure Storage as its foundation, we have a new level to incorporate into our planning process the file system itself. The file system contains the files and folders, and is equivalent to a container ......(more)

Azure CosmosDB

Azure Data Explorer - Azure Data Explorer (ADX) was announced as generally available on Feb 7th.  In short, ADX is a fully managed data analytics service for near real-time analysis on large volumes of data streaming (i.e. log and telemetry data) from such sources as applications, ......(more)

Running Azure CosmosDB queries from SQL Server using ODBC driver - Azure CosmosDB provides ODBC driver that enables you to query CosmosDB collections like classic databases. In this post you will see how to query CosmosDB collections from Sql Server using Transact-Sql. Why querying CosmosDB from SQL Server? Cosmos ......(more)

Administration of SQL Server

SQL SERVER – Difference Between Status in SP_Who2 – Running, Pending, Runnable, Suspended, Sleeping - One of the most popular questions I receive during my Comprehensive Database Performance Health Check is a difference between status in sp_who2 which discusses status as Running, Pending, Runnable, Suspended. Let us today understand them with regards ......(more)

Extra Documentation for KB Article 4462481 - SQL Server KB 4462481 is a bit light on details: Assume that you use Microsoft SQL Server data warehousing. When you run parallel insert queries to insert data into a clustered columnstore index, the query operation freezes. To quote one of the Top Men ......(more)

Comparing plans in Query Store - One feature that was added in the 2016 version of SSMS that hasn’t received a lot of attention, is the ability to compare execution plans. There’s two ways of doing this, from Query Store and from saved files. Let’s start with Query Store, and I’m going ......(more)

Lonely Tables in SQL Server - Takeaway: I provide a script that looks at the procedure cache and reports tables that are never joined to other tables. Recently, I’ve been working hard to reduce our use of SQL Server as much as possible. In other words, I’ve been doing some spring ......(more)

Why You Need SQL Server Upgrade Knowledge - Introduction Both SQL Server 2008 and SQL Server 2008 R2 are falling out of extended support from Microsoft on July 9, 2019. SQL Server 2012 already fell out of mainstream support on July 11, 2017. Finally, SQL Server 2014 is also falling out of mainstream ......(more)

How to Implement In-Memory OLTP Quickly and Easily - When you first hear about it, SQL Server’s In-Memory OLTP sounds like a new feature that’s hard to implement, requires a lot of schema changes, and takes planning and downtime. I’m here to tell you it’s simply not true, and you can do it in just 3 steps. ...(more)

Missing Indexes in the Query Store - I’ve shown before how to use the DMVs that read the plan cache as a way to connect the missing indexes suggestions with specific queries, but the other place to find missing index suggestions is the Query Store. Pulling from the Query Store The plans ......(more)

SQL Server Deadlocks - A quick video clip showing how to create a deadlock in SQL Server and find information about it. ...(more)

Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2) - Binary collations are, in many ways, simpler to understand than non-binary collations because they don’t have any complex, culture-based linguistic rules. They just work with the character ......(more)

Piecemeal Restore: Backup Set Holds a Backup of a Database Other than the Existing Database (Error) - When attempting to do a piecemeal restore of a database, using the ‘REPLACE’ option to replace the existing database, you may see the following error message: Msg 3154, Level 16, State 4, Line 63 The backup set holds a backup of a database other than ......(more)