The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I don’t doubt for a minute that on a certain level you’re sick to death of people talking about the Wanna Cry ransomware. However, bear with me, we need to go through it just a little more because it actually has some bearing on us as data professionals. OK, more than some bearing, it’s a fundamental aspect of what ought to be our jobs.

One of the more frustrating aspects of Wanna Cry is that a lot of it could have been avoided if people were just patching their OS on a regular basis. No, it wouldn’t have fixed all the issues for everyone, but it sure would have radically mitigated this from a literal global event to yet another entry into one of those lists of viruses and ransonwares discovered daily. Why aren’t people patching their systems?

The first, obvious, easy, answer to that question is because Microsoft is so intrusive and mean and rude and horrible about automatic updates that everyone turns them off. Right. That’s the answer. It’s Microsoft. They’re monsters. If we all just go to Linux (not Apple, they’re evil too), everything will be fine… Except the core problem is still right there. See, it’s not Microsoft or Apple, it’s you. You’re not ensuring that your systems get updated. I hear you “No! It’s Microsoft! See they make me have to reboot my machine when I don’t want to and….” Yes, yes. Microsoft forced you to turn of automatic updates. Then, somehow, Microsoft also forced you to not do manual updates. Wait. They didn’t do that did they. See. It’s on you.

The real issue here though goes beyond your laptop. The real issue is all your servers. A very dear friend and I were talking about Wanna Cry when he mentioned the effect it had on his company. The CEO immediately, and rightly, asked for an assessment of the patch levels on all systems. Guess what? They found that they had a lot of SQL Server instances as well as OSes all running on the RM version. In short, maybe the company dodged the Wanna Cry bullet, but they still had their head sticking way up out of the trench waiting for the next shot. They immediately changed policy and my poor friend had to spend a really tough two days doing nothing but patching machines.

It gets worse. Another thing we can all get mad at Microsoft about is that they’re not supporting Windows XP any more with patches and fixes (although they did issue an unprecedented patch for this one). Same thing goes for all those SQL Server 2000 instances. Once again, I’m saying that the problem here is on you. If you’re using twenty year old technology, but expecting Microsoft to maintain it for you, you’re probably getting set up for pain because they’re not going to do it and (my opinion) nor should they. If they publish a reasonable support cycle that covers at least 10 years, I feel like they’ve done a good job (which is what they do). Since you know that this is the life-cycle, it’s on you to make your choices and live with the consequences. I get it. You don’t want to update the SQL Server 2000 instance because it’s working and buying a 2016 license is cost you don’t want to pay. Fine. However, you’re assuming the risk that nothing will ever go wrong with that old software. If it does, if an exploit is found, you’re the one that made that choice.

Yeah, we can argue what Microsoft, or any other software company, should be doing around this type of thing. But, until they are doing what we want, we are making the choice to turn off our updates and not upgrade our servers and our operating systems. We are making that choice with the full knowledge of the consequences. So, sure, lobby Microsoft to make changes or switch to a different OS & database system, fine. However, you’ll still be dealing with the fact that it’s you that is choosing to not update your systems regularly. As Wanna Cry just demonstrated, that’s a choice that may have rather severe consequences.

Grant Fritchey 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.

Virtualization and Containers

Docker Blog Series Part 2 – Build & Deploy ASP.NET Core based Docker Container on Service Fabric - Azure Service Fabric in addition to offering a Service Fabric programming model is also able to orchestrate container based services across a cluster of machines. Service Fabric can deploy services in container images. In this blog post, we will see how to use Service Fabric as an orchestrator for Windows based Docker images....(more)

Vendors/3rd Party Products

VMware monitoring in SQL Monitor - The Redgate SQL Monitor team has been looking into how you can more effectively monitor SQL Server running on a virtual machine, and are pleased to announce that they've begun this by introducing support for VMware in SQL Monitor version 7.0.11. Find out more about the beta feature in this blog post....(more)

How to style your SQL using SQL Prompt’s new formatting options - Prefer tabs but your teammates use spaces? That's not a problem with the brand new formatting options in SQL Prompt. It's now possible to customize, save, and share multiple styles that you can then quickly switch between when working. Here's how it works, and how to get set up....(more)

T-SQL

Statistics in SQL: The Kruskal–Wallis Test - Before you report your conclusions about your data, have you checked whether your 'actionable' figures occurred by chance? The Kruskal-Wallis test is a safe way of determining whether samples come from the same population, because it is simple and doesn't rely on a normal distribution in the population. This allows you a measure of confidence that your results are 'significant'. Phil Factor explains how to do it....(more)

SQLskills SQL101: How can a SELECT cause a database to change? - Surely a SELECT operation can’t cause a database to change, because it’s just reading data, not altering it in any way, right? Well, no. There are actually quite a few side effects of queries that only read data and never perform data changes. Paul Randal lists the four most common....(more)

How to Execute a SQL Query Only if Another SQL Query has no Results - A user wants to query a table for a given predicate. If that predicate returns no rows, they wanted to run another query using a different predicate. Preferably in a single query. Challenge accepted!...(more)

Simultaneous Auto-growth in Multiple Files - SQL Server 2016 has a new configuration to control the auto-growth of multiple files in the same filegroup. However, the amount of data written in each file may not be always the same. If the auto-growth happens, one file will be bigger than the other, therefore the data distribution across the files will be unbalanced....(more)

The Lighter Side

SQL Crossword - Kenneth Fisher is back with another SQL-themed crossword - test your knowledge!...(more)

Tech News : Disaster Recovery

British Airways Says Cause of Massive IT Outage Remains a Mystery - At some point, the system-wide computer outage that took all of British Airways out of action starting on May 27 will provide a valuable lesson in maintaining critical systems. But for now, British Airways' IT staff is investigating why the systems failed so it can decide how to prevent it happening again....(more)

SQL Server Security and Auditing

Simplifying the user access audit with SQL Census - SQL Census is the latest piece of technology to be developed within Redgate Foundry. Still early in its development, it’s a product that’s designed to help you explain to an auditor which of your colleagues are able to access business-critical SQL Server databases. Here's how it works, and how you can get involved with the early access program....(more)

SQL Server on Linux

Do I Need to Learn Linux and R and Python? - The big changes coming in SQL Server 2017 are the Linux version and the addition of Python as a language used to analyze data. In SQL Server 2016, we had the R language added, and quite a few people have been on crash courses learning R, or worrying about the fact they don't understand how to write an R script. Now many of these same DBAs and data professionals worry that they also need to find time to pick up some Python and Linux....(more)

Security news and thoughts

Microsoft GDPR Cloud Dashboard to Help Customers Comply With Rules - Microsoft is the latest global-scale cloud provider to recently tout the steps it is taking to comply with the European Union's (EU) General Data Protection Regulation (GDPR)....(more)

PowerShell

Powershell Module Improvements for SQL Server in 2017 - Sheldon Hull gives a quick rundown of the improvements that SQL Server 2017 has made to the PowerShell Module, including an easier setup and new cmdlets....(more)

Where-Object vs. the Where method: Array filtering in PowerShell - The Where-Object cmdlet and the Where method both allow you to filter arrays in PowerShell. In this post, you will learn the difference between the two filters....(more)

PowerPivot/PowerQuery/PowerBI

Data Privacy Settings In Power BI/Power Query, Part 2: Preventing Query Execution - In part 1 of this series Chriss Webb showed how the data privacy settings in Excel Power Query/Get & Transform and Power BI could impact the performance of your queries, in this post he shows how they can stop a query from running at all....(more)

Performance Tuning SQL Server

Use WITH clause in OPENJSON to improve parsing performance - OPENJSON function has a WITH clause where you can specify what fields should be extracted from input JSON. This might improve performance of your queries compared to the case where you use OPENJSON without schema and later extract information from the parsed JSON using JSON_VALUE function....(more)

Use WITH clause in OPENJSON to improve parsing performance - OPENJSON function has a WITH clause where you can specify what fields should be extracted from input JSON. This might improve performance of your queries compared to the case where you use OPENJSON without schema and later extract information from the parsed JSON using JSON_VALUE function....(more)

ETL/SSIS/ELT

Deleting A Package From The SSIS Catalog - You still can’t delete a single package from the SSIS catalog. Even though the latest version allows the deployment of a package at a time, removing a deployed package must still be done at the project level - Tim Mitchell shows how....(more)

Data Privacy

Register for “GDPR: What you need to know” on Modern Workplace - Microsoft Chief Privacy Officer Brendon Lynch is hosting a webinar on June 13 to take a closer look at the global impact of the all-encompassing GDPR privacy law that's becoming effective in May 2018....(more)

Defensive computing - Mike Loukides talks about the tools of defensive computing, whether they involve mascara and face paint or random autonomous web browsing....(more)

Data Mining/Data Analysis

A Brief History of Data Modeling - Data Modeling is “the act” of creating a data model, and includes defining and determining the data needs of an organization, and its goals. The act of Data Modeling defines not just data elements, but also the structures they form and the relationships between them....(more)

Conferences and Events

Database DevOps Training - This is aimed at anyone responsible for SQL Server schema changes. The course teaches how to manage database changes throughout their lifecycle, helping to deliver more frequently and more reliably. London, September 25 - 27 2017....(more)

Columnstore Indexes

Columnstore Indexes: Clustered or Nonclustered? - In this article Hugo Kornelis will help you choose whether or not to use columnstore indexes, what tables to use them on, and what type to choose, by presenting a short summary overview of the two types of columnstore indexes, focusing on benefits, limitations, and workarounds....(more)

Big Data

Managing Data as a Corporate Asset: Data Virtualization - To be effective in managing data as a corporate asset, you must follow the same type of procedures as you would with other assets. ...(more)

Azure SQL Database

SQL Azure Blob Auditing Basic Power BI Dashboard - A simple Power BI template for viewing Blob Auditing data, along with how to set it up....(more)

Azure SQL Data Warehouse and Data Lake

Comparing and Synchronizing Two Folders with Azure - When we have an Azure SQL Data Warehouse (ASDW), it is possible to query NoSql files using PolyBase. It is very common to work with csv files and query them using ASDW. However, these csv files are constantly changing and they need to be uploaded to an Azure Storage account. How can we compare the content of two folders in order to upload only the new ones?...(more)

Azure Analysis Services

Building an Azure Analysis Services Model on Top of Azure Blob Storage—Part 2 - The first part in this series covering Azure Analysis Services models on top of Azure Blob Storage discussed techniques to implement a small Tabular 1400 model based on synthetic TPC-DS source data. This second part continues the journey to take Tabular 1400 in Azure Analysis Services to larger scale factors—up to the maximum capacity that Azure Analysis Services currently provides....(more)

AI/Machine Learning/Cognitive Services

How AI Can Keep Accelerating After Moore’s Law - New ideas in chip design look likely to keep software getting smarter....(more)

Mitigating offensive search suggestions with deep learning - The humble search bar is the window through which most Internet users experience the web. Deep learning is set to enhance the capabilities of this simple tool such that search engines can now anticipate what the user is looking for whilst moderating offensive suggestions before the query is complete....(more)

Administration of SQL Server

News for Differential Backup - Dennes Torres tells us what's new with differential backups in SQL Server 2017....(more)

SQL Server 2016 Distributed Replay Errors - Unless you already know quite a bit about how Distributed Relay should be properly configured, it can be pretty difficult to pinpoint what the error messages are actually hinting at. Jonathan Kehayias runs through a few of them in SQL Server 2016 to help....(more)

Database Delete vs. Detach vs. Offline - If you haven’t been asked to delete a database in your DBA career…it's just a matter of time. But stop before you do anything! Kevin Hill covers what your options are....(more)

A Quick Look at scalability [Part II]: SQL Server Checktable without physical_only option - Lonny Niederstadt takes a look at scaling DBCC CHECKTABLE based on a degree of parallelism, this time looking at full CHECKTABLE runs, and not just physical_only....(more)

How To Roll-Back Data in a Temporal Table - Bert Wagner shows how to use a temporal table to clean up incorrectly entered data, if you already have a temporal table in place....(more)

Columnstore Indexes – part 105 (“Performance Counters”) - Niko Neugebauer talks about the many perfmon counters available for understanding what’s going on with columnstore indexes....(more)

How to Insert Results of Stored Procedure into a Temporary Table? - Here is a simple script which demonstrates how we can insert the result of the stored procedure into a temporary table without pre-creating the temporary table before the stored procedure is executed....(more)

.NET Related Articles

Hello, React! – A Beginner’s Setup Tutorial - React has been around for a few years now and there are quite a few tutorials available. Unfortunately, many are outdated, overly complex, or gloss over configuration for getting started. Tutorials which side-step configuration by using jsfiddle or code generator options are great when you’re wanting to just focus on the framework features itself, but many tutorials leave beginners struggling to piece things together when you’re ready to create a simple react application from scratch. This tutorial is intended to help beginners get up and going with React by manually walking through a minimal setup process....(more)