The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Relational databases are the good guys of the whole privacy debate. I ought to qualify that statement: a properly-normalised relational database, with its full quota of CHECK constraints, is the Goody-Two-Shoes of the privacy debate. OK, maybe just one shoe, but if you are trying to maintain the viewpoint that compliance is mainly a relational database problem, then you are deluding yourself.

A privacy researcher in the USA once made the point about the dangers of open data and denormalization, by un-masking a pseudonymized database. It was one of the many databases sent by healthcare organisations for epidemiological research. Every piece of data in the medical histories that could identify an individual had been conscientiously masked out. Unfortunately, nobody had spotted an insignificant-looking XML column in an innocent-looking table. The XML fragments in this column recorded contacts between the individual patient and the healthcare staff. It was a miniature, schema-less database full of identifying nuggets that allowed the researcher, and presumably any villain who might come across it, to identify many of the patients.

However, this sort of problem is nothing compared to an entire document-based database that prides itself on being 'schema-less'. We of the old-school always scratched our heads over how this could allow the management of any organisation to handle personal or transactional data responsibly. For example, now that you are obliged to be able to remove personal data when appropriate, such as when a customer leaves, or a member of a society quits, how do you do so?

Schema-less document databases have many uses; even I use them enthusiastically for particular purposes, but never when the data could be required for legal reasons. Why not? Because when a schema isn't enforced, people will, and do, slip data into inappropriate places, and the organisation will be unable to demonstrate the authenticity and provenance of the data. It may not even know that the data is there and will have no idea how to comply with a legal request to remove it.

However, even those document databases that are entirely innocent of the requirement for the enforcement of schemas are relatively virtuous. Personal or financial data held in files, especially password-protected files, backups or in old email accounts, are where the scariest leaks can happen, and where it becomes impossible to redact false data.

You need to give provenance to the data in your document databases, and to do that the documents must be enforced with a schema. JSON Schema is improving greatly and is beginning to be used effectively in databases that store JSON data. If you use JSON in SQL Server, then you should be planning how to enforce its schema, as you do (gulp) with XML documents. Hopefully, one day JSON schemas will be built-in to SQL Server like XML Schema, but I can't see it happening soon.

Phil Factor.

Phil Factor 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

SQL in the City Streamed December 2018 - The theme for December 2018's SQL in the City Streamed is Your Journey to Compliant Database DevOps. The schedule includes sessions designed to broaden your skillset, support your ongoing learning, and keep you up-to-date with what’s new in the industry and at Redgate....(more)

Year in review with Kendra, Steve, Grant and Kathi: The best of 2018 and our predictions for 2019 - Join Microsoft Data Platform MVPs Kendra Little, Steve Jones, Kathi Kellenberger and Grant Fritchey live to discuss the highlights they’ve seen in 2018 and what cool things they hope to be surprised with in 2019. Along the way they'll share their own personal moments of glory, and favorite goofy memories as well....(more)

What we’ve learned about adopting Database DevOps, and what to avoid - Panellists Kendra Little, William Durkin and Hamish Watson are joined by guest, Tony Maddonna-Microsoft Platform Lead & SQL Server Enterprise Architect at BMW Manufacturing, to discuss examples of things they’ve learned the hard way....(more)

Virtualization and Containers

Running a custom SQL Server Docker Image in Amazon AWS - Klaus Aschenbrenner describes one way to run a custom Docker Image in the cloud. ...(more)

Kubernetes For the Microsoft Data Platform Professional: Can I Run Windows Containers On Kubernetes ? - From a ‘Vanilla’ Kubernetes perspective, where all nodes in the cluster run on Linux, only containers based on Linux images can run. Kubernetes supports running windows containers but, as is often the case in life, a more detailed answer comes with “It depends” style caveats....(more)

Vendors/3rd Party Products

Best practices for SQL Provision - In order to get the best out of SQL Provision, it’s a good idea to think about how it would best fit into your workflows and network architecture. We’ve put together a set of guidelines which can help you to optimize efficiency, performance, security, and reliability, and recommend that you consider these when planning a proof-of concept or rollout of SQL Provision in your organization. Read the How To Guide. ...(more)

T-SQL

SQL Server Guide To NULL Handling - One of the largest causes of bugs in software is unexpected null values that have not been properly handled. You can it will reduce risk later if you just don’t allow NULLS to get into your tables or come out of your queries but this will not always be possible....(more)

Testing Scalar UDF Performance on SQL Server 2019 - What is the impact on query performance of inlining a scalar UDF?...(more)

Introducing SQL-to-Excel - Dave Mason presents SQL to Excel, his modest command line utility that iterates though a folder of SQL script files, executing each one, capturing the query output, and writing the results to a Microsoft Excel Worksheet file, one sheet per script file....(more)

TSQL: OUTPUT Columns Not In Target Table - Using MERGE along with the OUTPUT clause to insert new values into a table while also providing a way to use columns that don’t exist in the target table...(more)

Fixing some of the problems caused when you introduce rowstore filtered indexes - Kevin Chant tackles some of the common pain points around introducing rowstore filtered indexes....(more)

5 ways to track Database Schema changes – Part 2 – Transaction Log - If your database operates in the Full recovery model, then the transaction log file (and backups) consists of every transaction executed against your database....(more)

Git Your SQL Together (with a Query Library) - Queries are living artifacts that change over time, and you will always need that query again. Caitlin Hudon explains why you need to track SQL queries in git. ...(more)

A curious case of case when and Null values - Adrian Buckman discovers an aesthetically-pleasing alternative to his usual CASE statement, but also that the two behave rather differently....(more)

How to learn and implement Change Tracking in SQL Server - John Shaulis discusses use of Change Tracking in SQL Server for cases where you want to transfer incremental data changes between databases with as light of a footprint as possible....(more)

SQL Server on Linux

SQL Server 2017 on Linux: Support for third-party AD providers - In the past, SQL Server on Linux has required SSSD to be configured for AD Authentication to work.However, some third-party AD providers (such as Centrify, PBIS, and VAS) do not configure SSSD when joining the AD domain....(more)

PowerShell

Gathering all the Logs and Running the Availability Group Failover Detection Utility with PowerShell - Using PowerShell to gather the information required by the Failover Detection Utility....(more)

Treating SQL Server as an Object with PowerShell - It’s easy to use invoke-sqlcmd to send T-SQL statements to SQL Server from PowerShell. There is, however, a rich PowerShell library, sqlps, that can be used instead. In this article, Greg Moore shows how to use sqlps to treat a SQL Server instance as an object....(more)

PowerPivot/PowerQuery/PowerBI

Power BI Introduction: Visualizing SQL Server Audit Data — Part 9 - SQL Server Audit is a powerful feature that can help you comply with regulations such as HIPAA and SOX, but it’s not easy to view the data collected. In this article, Robert Sheldon demonstrates how Power BI can be used to view and filter SQL Server Audit results....(more)

Complex Filter Conditions In Power BI Reports Using Visual-Level Filters And Measures - Chris Webb shows how you can use visual-level filters and measures to control how filtering works....(more)

Determine columns you don’t need using DMV’s in Power BI - Armed with a couple of DMVs, Kasper de Jonge manages to reduce the model size by a factor of 10....(more)

Hijacking the R Visual - Patrick Mahoney demonstrates a few ways you can hijack the R visual to do lots of other things too, such as export the data as a .csv file, or produce paginated reports....(more)

Power BI Aggregation: Step 1 Create the Aggregated Table - Aggregation tables are the fast-performing solution for huge DirectQuery tables in Power BI. Reza Rad leads us through the first step, which is to create an aggregated table. ...(more)

The Importance of Structure in Analytics – Part 2: the Dimensional Model and Power BI - Josh Roll explores why adherence to what can often seem like something of an esoteric concept such as the star schema can pay dividends when working with Microsoft analytics tools, and especially Power BI....(more)

Performance Tuning SQL Server

Tales From Overindexing: Too Many One Column Indexes - Sometimes you see a query, and it’s hitting one table, and then the query plan looks like a vase full of spaghetti. Usually, there’s a view involved. Sometimes, there’s just really weird indexing....(more)

How to Troubleshoot Blocking and Deadlocking with Scripts and Tools - Brent Ozar on the tools you can use to troubleshoot blocking, depending on whether it's t happening now, recently, or coming up soon....(more)

Forcing a Plan that has a Plan Guide - Grant Fritchey explains how you can create a plan using a plan guide, force that plan then chuck the plan guide. The plan forcing will still work. Further, the plan you originally forced still shows the plan guide. That plan didn’t change. One more use of the word "plan" and that paragraph would have exploded....(more)

DevOps and Continuous Delivery (CI/CD)

Database DevOps without compromising the data - Database DevOps has arrived and is here to stay. However this has also brought friction with the increasing importance of data privacy and protection. Redgate’s Compliant Database DevOps whitepaper outlines how you can introduce DevOps processes and their benefits while also keeping your information and data safe....(more)

Adopting Compliant Database DevOps at PASS - To help meet the requirements of the GDPR, PASS teamed up with Redgate to implement Compliant Database DevOps. This benchmark process has streamlined PASS’ database development pipeline, and strengthened their ability to uphold data privacy regulations such as the GDPR. ...(more)

DBA Tools

Tracking Database Changes Using Working Folder Source Control - How to version control a database using a working folder so that historical changes made to the database can be traced back....(more)

Database Design, Theory and Development

Data and Meaning Part 1: The RDM Is Applied Theory - Just like physics is not the mathematics used to describe it (a central issue in quantum mechanics), conceptual modeling is not data modeling, the latter is the representation of the former in the database -- they are distinct....(more)

Data Warehousing

Should I load structured data into my data lake? - James Serra covers a few reasons why you might want to copy structured data into a data lake, before loading it into the data warehouse....(more)

Data Transfer (XML, JSON)

Transferring Data with JSON in SQL Server - JSON is a viable option for transferring data between systems. It has the ability to include schema information along with the data which is an advantage over CSV files. In this article, Phil Factor demonstrates how he takes advantage of JSON when exporting or importing tables....(more)

Data Science

Common Sense Data Science - Collaboration is beneficial among data scientists, too. Many of the data scientists I have talked to are afraid to ask other data scientists to exchange experience, ideas and even code with the primary concern being the competitive advantage. But they shouldn’t be afraid....(more)

Columnstore Indexes

Fixing some of the problems caused when you introduce Columnstore indexes - Kevin Chant covers a few of the common, and not-so-common, errors you might encounter on first introducing Columnstore indexes....(more)

Case Studies

Stack Overflow: How We Do Monitoring - 2018 Edition - Nicke Craver describes how they do monitoring at Stack Overflow....(more)

Career Growth

A Speaker’s Workshop in 35 Minutes - Buck Woody shares 35 minutes of his considerable wisdom on how to be a more effective speaker....(more)

Create dynamic agenda slides with PowerPoint Zoom - Kendra Little talks about using ‘Zoom’ for dynamic presentations (rather than ‘zooming in’)....(more)

Backup and Recovery

Monitoring Backups Across Instances - An intricate way to monitor backups across instances, using Linked Servers, catalog views, SQL Agent Jobs and Database Mail....(more)

Azure SQL Database

R now supported in Azure SQL Database - Azure SQL Database, the database-as-a-service based on Microsoft SQL Server, now offers R integration. ...(more)

Administration of SQL Server

Things I Learned at Summit v20 - Trace Flag 4199 - Trace Flag 4199 enables query optimizer fixes that are included in CUs, after RTM. What does this mean? If you aren't running with TF 4199, then your Query Optimizer (QO) is running the same as RTM, regardless of what CU level you have applied to your engine....(more)

Auditing when Database Files Change - As a Database Administrator, something that should be part of your database audit is monitoring the growth of files. ...(more)

Where to Store Index DDL - How and where do you store the index DDL for a single database that had multiple clients with varied usage patterns?...(more)

New Resumable Online Index Create SQL Server 2019 - Have you ever started to build a new index on very large table only to have users call and complain their process is hung, not completing, or system is slow? That’s when you realize you’re the cause because you tried to sneak in a new index....(more)

.NET Related Articles

How to Program with MongoDB Using the .NET Driver - As more shops move to NoSQL databases, developers must learn new techniques for querying and updating data. In this article, Darko Martinovic demonstrates how to work with MongoDB in .NET....(more)