Missing Agents, Undercover Hints and More SQL Mysteries in Database Weekly (11/14/2020)

Presenting the Facts about Data Breaches

During a long and diverse career in the data industry, I've seen the unpleasant after-effects on people and organizations when things go badly wrong with data. It is a horrible experience to be responsible for losing data, but even worse for being responsible for a data breach. The repercussions linger for years within the organization, and the people whose data is breached can suffer greatly too. Litigation is likely to continue, on and on, even for those who have subsequently left the organization, or the industry. Even when you are entirely innocent, there is nothing in IT so wearying and demoralizing as having to deal with this.

If you are in the data industry, you are at increasing risk of prosecution. Is this far-fetched? Not at all. If you are skeptical, just see how many healthcare organizations in the US have had to report data breaches in the past two years that have affected 500 or more people (it is 690). All these organizations are under investigation by the Office for Civil Rights. In the UK, in the past two years, 59 organizations have been fined, or suffered enforcement action, for the misuse of data.

Let's assume that you and your organization have put in place every conceivable device, procedure, and system to prevent data breaches, and you maintain it conscientiously. You've reduced the risk considerably, but it is still there. How can you minimize the possibility of prosecution if a breach happens? Basically, you must prove that you practiced good data governance at the time of the breach.

When you're faced by an investigation team, it is no use putting on your 'Mr. Sincerity' face and making vague statements whilst waving your hands. These people are hard-boiled souls and can't be hypnotized. They want documented facts. 'What facts?', I hear you ask. Take a look at the guidelines for the federal prosecutors of companies that have failed to keep data secure, issued by the US Department of Justice Criminal Division in the US. This outlines how they decide whether an organization should be prosecuted. Obviously, they look at the quality of the design of the compliance program, how well it is being applied, resourced, and supported by the organization, and how well it works in practice.

These three checks must be done by people who need proof that all this was in place at the time of the breach. They are looking for documented policies and procedures, for consultation processes, for evidence that the organization identified and monitored areas of risk and implemented security steps. Were they generally understood, and were members of the organization given training?

This sensible document should concentrate the minds of even the most care-free data managers. In fact, it might provide them with twenty pages of terror. In an editorial, I can't spell out in detail how this translates to good advice for all of us with responsibility for data, but I heartily recommend it for every data professional, but particularly anyone with responsibility for data governance.

Phil Factor

Join the debate, and respond to the editorial on the forums

A data catalog allows an organization to discover and record the facts about its data, where that data is held and how it used. William Brewer explains the details.

William Brewer explains how to make data governance a continuous organizational activity, based on well-established standards and practices, rather than a knee-jerk response, and which skills and tools will help you achieve compliance, including SQL Data Catalog for discovery and classification of data held in SQL Server.

Redgate is giving you the chance to win a three-month subscription to Pluralsight (the technical skills training platform) in this month’s forum competition. To enter, simply share ‘how SQL Compare has helped you’.

Aaron Bertrand on overcoming occasional failures when running CHECKDB on restored copies of very, very large databases.

Warwick Rudd explores the new SQL Database Projects extension in Azure Data Studio, for source controlling database code.

Azure SQL Database doesn't have an Agent. What are the alternatives?

How the new Accelerated Database Recovery feature works and why it allows the undo phase to complete (almost) instantaneously.

There are a handful of options when backing up SQL Server databases. A DBA must understand the differences and come up with a plan that protects the organisation’s data. In this article, Pamela Mooney explains service level agreements, recovery models, and some strategies to ensure that the data can be restored quickly.

How to avoid migrating your database to the cloud and having it just screech to a halt.

Database DevOps solutions can help your organization protect business-critical data and improve the efficiency and quality of your software delivery. Register for this Microsoft hosted webinar to learn how you can rapidly implement a database DevOps solution with Azure and Redgate tools.

Want to achieve the speed, flexibility and collaboration of DevOps but struggling against the rigid schemas, manual processes, and silos in the data management? Join Microsoft Data Platform MVP, Grant Fritchey to discover actionable strategies you can implement to bridge that divide between data management and DevOps in your organization.

This seems oddly specific, but someone or something had cleared out many files from the Windows\Installer folder, probably to save space and one of the side-effects of these missing files was the inability to patch SQL Server.

Steve Jones explores the use of PowerShell hash tables.

Garry Bargsley introduces the Export-DbaInstance function from dbatools, which will export key configuration items from your SQL Server to individual script files, and explains why it might get you out of a scrape.

The greatest challenge when integrating database development into a DevOps process is synchronizing application and database changes.

The database is often left behind as organisations embrace DevOps. In this article, Robert Sheldon explains how to successfully bring databases into DevOps, especially when dealing with legacy databases.

I want to trigger a Data Factory pipeline, but when I do I want the pipeline to know if it’s already running. If it is already running, stop the new run.

A script that implements a set of logic tests/checks using PowerShell to return details about the Data Factory ARM template.

Configuring policy-based management features for Availability Groups, and evaluating policies.

Phil Factor provides some helper functions to remove the pain of trying to fold a JSON document into one or more relational tables using a OpenJSON SELECT statement.

This article explains the roles of the formula engine and of the storage engine used to execute DAX queries.

In most cases, if SQL Server decides to parallelize your query, it’s goin’ straight to MAXDOP.

I’ve heard other engineers speak dismissively of hints, but I would encourage you to not discard a useful tool. Just realize you can cut yourself with it.

Erin Stellato explains why the multiple fixes and improvements to Query Store, since the initial SQL Server 2016 release, mean that it can now support all workloads, including those that are ad-hoc.

Matt Allington on changes to the ways in which we suck tabular data from the Power BI Service into Excel.

Calculating and visualizing semi- and non-additive measures like distinct count in Power BI is usually not a big deal. However, things can become challenging if your data volume grows and exceeds the limits of Power BI!

Chris Webb provides a detailed example of how to handle multi-select in the M code for your Power Query queries, and explains what happens behind the scenes when you use multi-select.

The Common Data Service connector wants the Server URL, but where is it?

Ed Hansberry shows how to import an Excel file into Power Query and rename the columns, even if the column names keep changing in each file.

Kendra Little, and others in the SQL Community, share thoughts on coping during the pandemic.

Quarantine restrictions are getting tighter and you might want to spent playing some useless game with your R engine, whilst programming, doing machine learning or other learning.

Can I use indicators in SQL Server Reporting Services, like those visuals available in Power BI?

Aaron Nelson's two new PowerShell commands to deploy SSRS projects have been merged into the ReportingServicesTools module. Both can handle deployment to multiple folders.

Microsoft have a fondness for providing three different ways to perform the same simple task, so it is no surprise to find that you can set up the encryption keys for the Microsoft repository on Ubuntu in at least three different ways.

Cross-server references keep cropping up as a problem for development and build. Phil Factor demonstrates how using linked server 'aliases' can get around these issues, even if the individual databases use four-part references within the code rather than synonyms.

One can’t help but wonder when Microsoft is going to cease work on SSMS to only work on ADS, and what that roadmap might look like.

Not exactly a dynamic linked server, but one that gets dropped and recreated in a loop.

Let’s say you need to troubleshoot someone’s query performance, and they’re using temp tables. You want to peek in at their temp table contents from another session while their query is running. SQL Server makes that a little bit challenging.

Brent Ozar recommends the three Paul White posts you should read, when your job eventually requires you to understand temp tables way better than you do today.

When your query uses CONTAINS, SQL Server has a nasty habit of doing a full text search across all of the rows in the table rather than using the rest of your WHERE clause to reduce the result set first.

One way to make your dynamic SQL a little bit safer is to keep user inputs as far away from the execution as you can.

Guy Glanster hits a problem when trying to assign a very long string to an NVARCHAR(MAX) variable.

Itzik Ben-Gan continues his coverage of optimization aspects of CTEs, specifically addressing how multiple CTE references are handled.

Erik Darling tries to rewrite a scalar function so that it runs in less than 23 seconds. It's not as easy as it sounds.