The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

One of the worst aspects of living with a technologist is the strange way that he or she will do anything, and everything, to overcome a technical problem. This thought occurred to me as I realised that it was well past midnight, and I still hadn't got a query running satisfactorily.

Eventually, I decided to abandon the quest, temporarily, and switch off the computer. When apologising for my behaviour the next morning over breakfast, I explained it like this.

"I know it seems silly, and the query isn't a job-stopper. The problem is that every fibre of my body itches to solve the problem. Taming technology is a battle of wills. It is you against the technology. You must stay eye-to-eye with it, unflinchingly."

Any surviving lion tamer will tell you the same. The direct gaze is the lion tamer's most valuable tool. To control a lion is to be brave enough to look at it eye to eye; if your nerve fails you at the critical point, it can prove fatal.

Unflinching eye contact also helps enormously in the battle of wills with a wild or flighty horse. Sometimes the database can seem to be as wilful as a colt. Your task is to persuade it to produce that report without rounding errors, nor anomalies due to NULL values. It stares at you fractiously. You must maintain eye contact. Never show fear. It flares its nostrils, snorting occasionally. If you lose confidence, you will be thrown. Either you master the recalcitrant database or retire, sadly, into the dingy half-life of IT management.

Of course, database taming is more complicated than this. Sometimes, direct confrontation and the battle of wills can end in an unsatisfactory truce, resulting in applying hints, using cursors or denormalising. Sometimes, it is better to back off and apply 'time-out'. I like to think that my databases pine for my constant attention, but in truth time away from them gives me time to think.

Suddenly, the thought hit me: I'd used entirely the wrong algorithm for achieving the result, and the true solution, much simpler and more robust, flowed into my conscious brain. Those few hours of sleep had been enough for reflection. Perhaps good database development and administration requires a mixture of eye contact and meditation. Perhaps there comes a time when every database tamer needs to leap quickly over the fence, and chill out for a while.

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

How the GDPR will impact your software delivery processes - With the GDPR enforcement date looming, organisations are focusing on how data is used internally. This webinar will explain the principals of data protection, translate the GDPR jargon, and cover the steps you'll need to consider to ensure compliance in your software delivery process....(more)

Vendors/3rd Party Products

Avoid T-SQL Technical Debt using SQL Prompt Code Analysis - SQL Prompt’s new Code Analysis feature helps you discover code issues and hidden pitfalls during code development, as you type. In this article, Catherine shows how the Code Analysis feature works, and illustrates how to use the feature to improve the quality of your team’s SQL code....(more)

T-SQL

Creating Insert Triggers to Silently Ignore Data You Don’t Want - Say you’ve got an application that insists on inserting data into the database, and…you don’t want the data. You want the application to THINK it inserted the data – you don’t want to roll it back or return an error to the end user. You just don’t want the data, and you don’t want the hassle of deleting it later....(more)

Using LIKE on Integers Gets You Implicit Conversion - Adding a computed field, then indexing that field, as a way to avoid implicit data type conversions, when you can't alter the code....(more)

A Gentle Introduction To the Stack Overflow Schema - Erik Darling explores usable relationships between the tables in the StackOverflow demo database, and then some queries to explore the data distributions in different columns....(more)

Query Like It’s 1986: Exploring mssql-cli - The Microsoft SQL Server Command Line Interface (mssql-cli) tool is a database client for the command line. If you’re stuck on a remote system that only allows terminal logins, this is a super handy tool to use....(more)

Calculating Tupper’s Self-Referential Formula With SQL - Tupper's self-referential formula visually represents itself when graphed at a specific location in the (x, y) plane, and it turns out to be rather easy to calculate using SQL....(more)

Does a Clustered Index Give a Default Ordering? - The short answer: SQL Server only guarantees that results are ordered per the columns you specify in an ORDER BY clause...(more)

Fun With Palindromes – Part 1 - What would be the most efficient way to test a string’s palindromicity using T-SQL?...(more)

Tech News : General Interest

Computer latency: 1977-2017 - Almost every computer and mobile device that people buy today is slower than common models of computers from the 70s and 80s. If we had to pick one root cause of latency bloat, we might say that it’s because of “complexity”. ...(more)

SQL Server Security and Auditing

Auditing Linked Servers - SQL Server does not track actions on linked servers, by default. You need to be auditing the linked servers for modifications before they happen....(more)

SQL Server Security

Storing and Applying SQL Server Database Permissions With PowerShell - As a DBA, at least part of your job revolves around data security. Your development and integration environments might be set so your developers can have free reign over almost everything in a database, but this changes as you move up the stack. Your QA environments may have elevated permissions for your testers only. Your production servers need to be locked down. How do you manage all this?...(more)

An alternative to data masking - Read on to see how you can bypass dynamic data masking, and for an alternative approach that uses SQL Server column-level security instead....(more)

SQL Server on Linux

Bash, an Introduction - You’re comfortable with PowerShell and you’ve honed your T-SQL skills over the years, but what about the Bash scripting language? With the release of SQL on Linux a lot of DBAs are wondering how they might manage SQL on this new and unfamiliar operating system....(more)

SQL Server News

SQL Updates Newsletter – January 2018 - A SQL monthly roundup of Recent Releases, Issue Alerts, Recent Articles, and more....(more)

Software Development

Logging is useless… if you don’t deal with uncertainty - You don’t need logging if your code is absolutely predictable. The truth is that is seldom the case....(more)

Security news and thoughts

Every NHS trust tested for cybersecurity has failed, officials admit - Assessments after WannaCry attack reveal vulnerabilities across whole of health system...(more)

PowerShell

Log Shipping with dbatools – Part 4: Recover a log shipped database - You have setup log shipping and have about 15 databases. Everything is working fine until one day the primary instance goes down and is not recoverable. For the production to continue you have to bring the log shipped databases online fast....(more)

PowerPivot/PowerQuery/PowerBI

Diagram your Database Tables using Power BI - Ever wanted to create a diagram of your MS SQL Database tables in Power BI? Here is what I did to make that happen....(more)

The definitive guide to ALLSELECTED - ALLSELECTED is a powerful function that can hide several traps. This article is an in-depth analysis of the behavior of ALLSELECTED, explaining shadow filter contexts, what they are and how they are used by ALLSELECTED....(more)

Creating Excel “Data Dump” Reports From Power BI - We’ve all met them: the users whose idea of a ‘report’ is a large table of data dumped into Excel. Internal politics means that you have to accommodate these requests, so Chris Webb shows how to get data from Power BI into an Excel table....(more)

Performance Tuning SQL Server

Filtered Indexes and Forced Parameterization: Can’t we all just get along? - How Forced Parameterization stopped the optimizer using a filtered index....(more)

How to Troubleshoot Waiting Tasks Without Requests in SQL Server? - Sometimes a waiting task can appear in sys.dm_os_waiting_tasks without a corresponding request. This happens when the system is out of worker threads, and then the next task that needs to run is waiting on the THREADPOOL wait type....(more)

Wait Statistics on a Query - Grant Fritchey explains how to use Query Store or Extended Events to capture query wait statistics on a specific query...(more)

Here is your index, now what? - The benefits of having an index are well known, you can get the same results by reading a smaller amount of data so the improvement in performance can be from several minutes to seconds or even less. However, things can go wrong and make all these indexes just a pile of useless burden....(more)

Hardware Testing

Rethink Server Sizing II - With proper design, most transaction workloads can be handled by the 16-28 cores available in a single processor with Hyper-Threading doubling the logical processors (threads). Scaling up to a multi-processor system provides only moderate throughput performance gain and may create new problems....(more)

HA/DR/Always On/Clustering

Analyze Synchronous Commit Impact on High Commit Rate Workloads - Workloads that rely on auto-commit and perform a large number of small transactions may take significantly longer to complete when run against availability group databases configured for synchronous commit. Aside from an issue with resource latency (IO, CPU or network) this performance impact may be expected....(more)

ETL/SSIS/ELT

Formatting a VTT Caption File into a Transcript with Sublime Text and Multiple Regular Expressions - Do you ever need to use a text editor to apply regular expressions to files? If so, this post is for you!...(more)

DevOps and Continuous Delivery (CI/CD)

The top 7 benefits of DevOps for CEOs - If you were asked what the benefits of DevOps are, you could probably name two or three straight away. Maybe four or five. But – and here’s the thing – what if the person down the corridor was asked the same question? Someone who works in the same place, but does a different job....(more)

Database Design, Theory and Development

Why 2018 Will Be The Year Of The Data Engineer - The shortage of data scientists – those triple-threat types who possess advanced statistics, business, and coding skills – has been well-documented over the years. But increasingly, businesses are facing a shortage of another key individual on the big data team who’s critical to achieving success – the data engineer....(more)

Data Warehousing

Data Virtualization vs. Data Movement - If you are building a data warehouse, should you move all the source data into the data warehouse, or should you create a virtualization layer on top of the source data and keep it where it is?...(more)

Data Privacy

gdpr - panic part 4 - In Part 4, Ed Elliott investigates how companies have mishandled the data they have, using it in ways they were not authorized to use it. ...(more)

Data Access / ORMs

AddWithValue is Evil - The SqlParameterCollection.AddWithValue method is a slightly more convenient way to add parameters and values to a SqlCommand, the implications are insidious because the high costs of SQL Server resource utilization is not apparent during development....(more)

Computing in the Cloud (Azure, Google , AWS)

Bridging the Azure gap : Managed Instances - Managed Instances provide much more of a feel of an on-premises SQL Server, yet are built on the same infrastructure as Azure SQL Database. What sets it apart from Azure SQL Database is that it presents an entire SQL Server instance to the customer...(more)

Backup and Recovery

SQLskills SQL101: How You Can Make Your Database Backups More Reliable - When DBCC CHECKDB fails with some specific series of errors, you may or may not be able to repair any damage, and you're definitely going to want a solid last line of defense being, which is the ability to restore from your last set of known, good database backups....(more)

The 2018 SQL Backup Survey - The majority of shops out there manage less than 25TB of total data, with most of these databases clocking in at 5TB or less, but 36% have 100+ servers. This means that most of the respondents manage “wide” environments, with more databases that are smaller in size....(more)

Backups of backups or How long is my backup really available? - I’ve said before that backups are at once one of the easiest things DBAs do, one of the most important, and one of the most complicated. One of those complications is the backup of the backup files. If you are using native backups, then that full backup is sitting on a drive somewhere, and hopefully, that drive gets backed up, right?...(more)

Analysis Services / BI on the MS Stack

Items NOT Selected in a Slicer? - My idea was that I would load school photos and also the reunion photos onto the one page. The user can then click on a slicer with someone’s name (or any other information about people) and “see” those people highlighted in the photo....(more)

AI/Machine Learning/Cognitive Services

SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python - Visualization is often the first step in analyzing data. Python makes visualization easy. In this article, Robert Sheldon demonstrates how to generate multiple charts from one dataset using Python with SQL Server Machine Learning Services....(more)

Administration of SQL Server

SQL Management Studio, Trusted Connections, and Remote User accounts - I often work for clients that require me to access their systems remotely, and usually through a VPN. Frequently, I use a Remote Desktop (RDP) session to access their resources after I have connected in via their VPN. I don't mind this, but often it is easier to use SQL Server Management Studio (SSMS) on my own desktop rather than remotely. ...(more)

Data Migration Assistant Custom Configuration - The Data Migration Assistant (DMA) will perform an assessment of your database against a target version. The DMA can also perform the migration of both schema and data....(more)

Updated shortcuts cheat sheet — Now with SQL Ops Studio - Andy Mallon updates his "Shortcuts from an impatient DBA" to include SQL Ops Studio....(more)

SQL Server Row Data Linking to Off Row Data - How to find a link from corrupt blog storage back to the table and row that contains that data....(more)

UNDERCOVER TOOLBOX: Get Details of All Open Transactions - For a little lunchtime quickie today, I thought I’d share with you all a little script that I wrote to give me details on all open transactions that I’ve got on an instance. ...(more)

How to Survive as a Lone DBA - Monica Rathbun tells us how she survived as the Lone DBA for 56 database servers for over a decade....(more)