The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

For many of the data professionals out there, our job is to ensure that data is protected. We work to protect the information in our databases in a variety of ways that allow our organization to continue to work in adverse situations. We often schedule backup schemes to ensure we can recover data from losses. We perform maintenance to ensure efficient access to information, and we move data from one system to another so that a wide variety of applications and clients can use it. We spend most of our workday trying to ensure that data is where it needs to be.

It's worth considering that there may be times when we are asked to destroy data as well. If hardware is being upgraded, then it might be a part of our job description to ensure that no traces of data exist in old systems. Even if it's not our job, we might want to ensure that hardware is destroyed or permanently wiped. It's unlikely to be an issue, but there are numerous stories of someone buying used equipment with old data on it. This is such a low bar of something to check that there isn't an excuse to avoid removing old data.

If you aren't sure what to do, don't have time, or want independent proof, maybe you want to use a service. There are numerous ones, which makes sense with the growth in legislation requiring stronger data protections as well as the embarrassment from management that wants to outsource their risk to another entity. This might be especially handy for larger organizations that could have multiple types of devices, some of which aren't as accessible as a hard drive in a server chassis. There are services to ensure wiping of phones, tablets, and more.

It's a little funny to me to think of destroying data when most of my career has revolved around protecting data. What I mostly think about here is that too often many organizations have thought about data as an asset of the company to be used as needed, but not necessarily protected throughout its life cycle, and certainly not classified according to it's sensitivity.

I hope that with the growing value of data, and regular misuse of information, and the security issues that appear in the news that most companies would start to actively manage their data in the future, from capture to destruction.

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

Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report - Microsoft MVP Steve Jones is joined by acclaimed author and researcher Gene Kim to discuss the latest in all things DevOps. They offer a closer look at the key findings in the 2018 Accelerate State of DevOps Report, and investigates the growing importance of the database in successful DevOps and IT performance....(more)

SQL in the City Streamed - The theme for September 2018's SQL in the City Streamed is Adapt and thrive as a data professional. Microsoft MVPs, Kathi Kellenberger, Grant Fritchey, Steve Jones and Rob Richardson will head up the speaker line-up, and will be joined by members of the team at Redgate and other technical experts....(more)

How to extend DevOps practices to the database - In this webinar Arneh Eskandari will walk you through Redgate’s Database DevOps solution, showing reliable, scalable and repeatable processes for automating your database development and deployment....(more)

Database DevOPs September Training Schedule - Do you need to do more with less as a Database professional? Why not start your journey to Database DevOps nirvana today with our September Training schedule. You will learn Source Control, Continuous Integration & Continuous Delivery for the Database....(more)

Virtualization and Containers

Learn Linux With Docker and Microsoft Container Image - I just finished teaching an 8 hour pre-con at SQL Saturday Indianapolis on Essential Linux for the DBA. The focus of the class was on what the Microsoft DBA would need to know as more SQL Server environments begin to run on Linux. The obstacles and ......(more)

Vendors/3rd Party Products

Practical PowerShell Processes with SQL Change Automation - This article uses the PowerShell cmdlets of SQL Change Automation (SCA), formerly DLM-Automation, to take the source code of a database from a directory, validate it, document it and then create a NuGet package of it....(more)

The conflict between data protection and DevOps - Data breaches are the new normal – according to the Identity Theft Resource Center there were nearly 1,600 of them in 2017 in the US alone. No wonder companies want to keep data confidential and protected. At the same time, however, the increasing pace of business means developers need to create and release code in much shorter DevOps timescales. Steve Jones discussed ways to solve this conflict...(more)

T-SQL

SQL SERVER – How to Generate Random Password? – Enhanced Version – Part 2 - I have previously written about Random Password before in the blog posts here, here and here. This blog is sent by James Curran, who has created the following generate random password stored procedure keeping the base of the original solution of Tim ......(more)

6 Techniques For Troubleshooting Your Code - This post is a response to this month’s T-SQL Tuesday #105 prompt by Wayne Sheffield. T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month. This month’s topic asks to share a ......(more)

A data dimension for SQL Server - A data dimension for SQL ServerThe most common table you will find in a data warehouse will be the date dimension. There is no "right" implementation beyond what the customer needs to solve their business problem. I'm posting a date dimension for SQL ......(more)

Spoofing Data Convincingly: Doing the Shuffle and Zip - Having spent a lot of my working life trying to preserve the integrity of data, there was a certain intriguing novelty in the idea of pseudonymizing data. One of the standard techniques of pseudonymization is that of shuffling data columns as though ......(more)

A Common Query Error - So Many Choices When you only need stuff from a table where there’s matching (or not!) data in another table, the humble query writer has many choices. Joins In/Not In Exists/Not Exists No, this isn’t about how NOT IN breaks down in the presence of ......(more)

The Lighter Side

I’m Joining Redgate Software! - I’m excited to announce that I’m joining the evangelist team at Redgate. Starting next Monday, I’ll be working with Steve Jones, Grant Fritchey, and Kathi Kellenberger — plus the whole Redgate team. Why take on a “real job”? The last time I had a manager ......(more)

SpaceX reveals the controls of its Dragon spacecraft for the first time - Enlarge / NASA astronaut Mike Hopkins meets with employees at SpaceX on Monday. (credit: SpaceX) HAWTHORNE, Calif.—Across the cavernous rocket factory, the buzz, whirr, and whine of various machinery never ebbed. Even when the president of SpaceX and ......(more)

My Home Office Setup, 2018 - We moved to San Diego and I refreshed my computer hardware, so it’s time for another update in my home office blog post series. Here’s what I’m using: Home office 2018 Standing Desk: Xdesk Terra Pro – Expensive, but works wonderfully and is built to ......(more)

Testing Software

Go Unit Tests: Tips from the Trenches - In this article, Michael Sorens describes unit testing for applications written in Go. While focused on Go, many of his recommendations and techniques can be applied to other languages as well....(more)

Writing ssisUnit test using API - In the post about using MSTest framework to execute ssisUnit tests, I used parts of the ssisUnit API model. If you want, you can write all your tests using this model, and this post will guide you through the first steps. I will show you how to write ......(more)

SQL Server Security

What are my permissions? - Permissions are a common concern. One of the most frequent requests I get is I need X, Y and Z permissions. And all too often the conversation goes like this: Dev: I need write permission to this database. Me: Ok. Me: Checks their current permissions. Me: ......(more)

SQL Server News

What’s new in SQL Server 2017 - SQL Server 2017 is considered a major release in the history of the SQL Server life cycle for various reasons. From my personal point of view, SQL Server 2017 is indeed an interesting release. After writing a lot about it … Continue reading ? ...(more)

Security news and thoughts

8 practices for business data security - These days, everything from buying clothes to banking is done online, and this is all thanks to technology. The shift of transactional and record-keeping systems from physical to virtual has led to an unprecedented amount of problems, the most troublesome ......(more)

What SOX means to the DBA - The responsibilities of a database administrator can seem endless, so why should that already heavy workload be burdened with legislation compliance? Surely, legal stuff can be dealt with by lawyers? Unfortunately, this is no longer the case. With legislation ......(more)

Intel’s SGX blown wide open by, you guessed it, a speculative execution attack - Foreshadow explained in a video. Another day, another speculative execution-based attack. Data protected by Intel's SGX—data that's meant to be protected even from a malicious or hacked kernel—can be read by an attacker thanks to leaks enabled by speculative ......(more)

Lloyds, Barclays Reveal Security Lapses, Disruptions in Payments - Regulators are signaling they may ask banks to standardize the way they disclose cyberattacks to consumers, said James Chappell, a co-founder and chief intelligence and innovation officer at Digital Shadows, a London-based cyber-defense firm. ...(more)

Encryption Broken by Laziness - Click to learn more about author David Schlesinger. Many readers of my previous blogs have failed to ask the question “Is there really an unbreakable cipher?” Even though they have been silent, I know some of them stayed awake nights with this provocative ......(more)

R Language

Lesson 6 - Matrices - We've spent the last few lessons exploring the vector. In a sense, a vector is a one dimensional array or collection. In R, a matrix is much like a vector, but with two dimensions: one for "rows" and the other for "columns". The matrix() function (among others) can be used to create a matrix: ...(more)

Product Upgrades and Releases

Azure Database Migration Service – August 2018 Updates - We are pleased to announce the August 2018 release of the Azure Database Migration Service (DMS), which includes the following key updates. Virtual network (VNET) guidance for DMS service creation In this release, we updated the Virtual Network (VNET) ......(more)

Connecting SQL and NoSQL into One Platform - Merging the comfortable familiarity and efficiency of SQL with the flexibility and scalability of a non-relational systems is no easy task. CrateDB, offered by Crate.io, is a “distributed SQL database that is built on top of a NoSQL foundation.” It allows ......(more)

Google Updates Cloud Firestore NoSQL Database Beta for GCP - Google’s new Cloud Firestore NoSQL database technology now supports two modes, a native mode and a Datastore mode, the company says. ...(more)

First Responder Kit Release: A Year From Now These Will All Stop Working On 2008 And 2008R2 - You think I’m kidding. Time bomb. Boom. Get your upgrade underwear on. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1664 – We’re officially smart enough to not warn people that we’re recompiling our own stored procedures. #1669 – ......(more)

Release: SQL Server Migration Assistant (SSMA) v7.9 - Overview SQL Server Migration Assistant (SSMA) for Oracle, MySQL, SAP ASE (formerly SAP Sybase ASE), DB2, and Access allows users to convert a database schema to a Microsoft SQL Server schema, upload the schema, and then migrate data to the target SQL ......(more)

Python in Visual Studio 2017 version 15.8 - We have released the 15.8 update to Visual Studio 2017. You will see a notification in Visual Studio within the next few days, or you can download the new installer from visualstudio.com. In this post, we're going to look at some of the new features ......(more)

Microsoft R Open 3.5.1 now available - Microsoft R Open 3.5.1 has been released, combining the latest R language engine with multi-processor performance and tools for managing R packages reproducibly. You can download Microsoft R Open 3.5.1 for Windows, Mac and Linux from MRAN now. Microsoft ......(more)

Product Reviews and Articles

Documenting your Database with SQL Change Automation - It would be wrong to portray SQL Change Automation (SCA) as being suitable only for epic project deployments, of the sort described in my previous article. It can do smaller tasks as well. To demonstrate, I’ll show how you can use SCA to check that the ......(more)

The New SQL Provision Dashboard - As much as I liked the ability to quickly and easily build development and test databases with SQL Provision, I thought the dashboard of cloned databases was hideous. It left a lot to be desired, and frankly, the dark theme is annoying to me. Here’s ......(more)

SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020) - The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Relational tables are not ordered, so the ORDER BY is meaningless. Use a Row_Number() window clause instead, ......(more)

PowerShell

default parameter values - When connecting to a SQL Server instance with alternative credentials, it can be tedious to repeat the SQL credential over and over. The great news is that this repetition is not required, as it can be handled instead by $PSDefaultParameterValues Intro ......(more)

PowerPivot/PowerQuery/PowerBI

Simple Linear Regression in Power BI - Combining Power BI with statistics yields some very powerful results. In this post we’ll show how easy it is to do Linear Regression with the Power BI tool. Linear Regression is a very useful statistical tool that helps us understand the relationship ......(more)

Creating Sparklines And Small Multiples In Power BI Using The Google Image Charts API - The excellent work that David Eldersveld has been doing recently on using SVG images in Power BI has generated a lot of interest; now that the August 2018 release of Power BI Desktop allows you to set data categories on measures – and specifically the ......(more)

Power BI Custom Visuals Class (Module 115 – Circle KPI Gauge) - In this module you will learn how to use the Circle KPI Gauge. The Circle KPI Gauge displays a single measure value in a highly customizable circular gauge visual. Module 115 – Circle KPI Gauge Downloads Power BI Custom Visual – Circle KPI Gauge Dataset ......(more)

Power BI Workspaces, August 2018 Power BI Desktop and more… (August 13, 2018) - Understanding Power BI Dual Storage (@tlachev) Convert from Seconds to Minutes with a DAX Measure (@GilbertQue) Choosing font color based on background color in Power BI (@Dmaslyuk Enable your team with new workspace experiences (preview) Power BI Desktop ......(more)

Performance Tuning SQL Server

Mining Plans : Not just for the plan cache - When looking at query performance there are a lot of great sources of information within SQL Server, and one of my favorites is the query plan itself. In the last several releases, notably starting with SQL Server 2012, each new version has included ......(more)

Async Network IO SQL Wait - Over the last few days we have been tracking down the cause of some really high async network IO waits. In order to fix this problem we have to understand the wait and what it is telling us, so that is where we begin. Async Network IO in my experience ......(more)

Measuring Query Execution Time: What Is Most Accurate - Probably the single most important factor when deciding which query to tune, or actively tuning a query, is how you go about measuring query execution time. SQL Server provides a number of different mechanisms (really, maybe too many) to get this done. ...(more)

Wait Stats capture script - Performance troubleshooting should begin with capturing wait stats so we can understand where SQL Server is busy. The script below captures wait stats into a table in tempdb; the script should be ran via a... The post Wait Stats capture script appeared ......(more)

NoSQL

Rant: There Is No NoSQL Data Storage Engine - Do I need relational or NoSQL? How does NoSQL help me scale? Will I get paid better with NoSQL or relational? Does NoSQL make my butt look big? OK, maybe I made up that last one. However, the others are all variations of questions I’ve been seeing a ......(more)

MDX/DAX

Dates differences between Excel and DAX - DAX supports dates starting in 1900 and while teaching DAX I always explain that DAX manages dates like Excel, which is the reason why these limitations exist. Actually, this is true – but answering to a comment today I realized I could explain this ......(more)

Side effects of the Sort By Column setting in DAX - The Sort By Column feature in Power BI causes side effects that are important to know when writing a DAX formula. This article explains these side effects and how to write correct DAX code to avoid getting incorrect results. The Sort By Column feature ......(more)

Fun with DAX – Tic Tac Toe - To follow in the spirit of some of my recent blogs, I thought I would have a look to see how feasible it might be to build a Tic Tac Toe game using just DAX and Power BI. I get pretty close and will detail my approach here in this article. For starters, ......(more)

Hardware News

Building the Ultimate Developer PC 3.0 - The Parts List for my new computer, IronHeart - It's been 7 years since the last time I built "The Ultimate Developer PC 2.0," and over 11 since the original Ultimate Developer PC that Jeff Atwood built with for me. That last PC was $3000 and well, frankly, that's a heck of a lot of money. Now, I ......(more)

Lenovo’s Unveils ThinkPad P72 Workstations: Thinner DTR with Intel Hex-Core CPUs - Lenovo today is unveiling a new version of its 17.3-inch ThinkPad P7-series highi-end portable workstations. The flagship of Lenovo's desktop replacement-class workstation laptops, the ThinkPad P72 has a new design that's a little thinner than predecessors, ......(more)

Lenovo Launches Ultra-Thin ThinkPad P1: X1 Carbon Meets Workstation - Lenovo on Monday introduced its new ThinkPad P1 workstation, which brings together a 4K 15.6-inch display, high performance, and portability. The system can integrate Intel’s latest Core or Xeon processors with up to six cores, NVIDIA’s Quadro discrete ......(more)

HA/DR/Always On/Clustering

AlwaysOn Data Synchronization in Synchronous and Asynchronous Mode - When you are working on the AlwaysOn feature, it is crucial to understand how the AlwaysOn commit process works. This blog will walk you through how a synchronous and Asynchronous commit process works in AlwaysOn. Data Synchronization in Synchronous ......(more)

Database Design, Theory and Development

Bitemporal Data Modeling: How to Learn from History - Click to learn more about author Mike Brody. Have you ever called about a real estate listing only to learn that the house has been taken off the market? Or had to pick up mail that should have been routed to your new home? Sometimes our records don’t ......(more)

Data Privacy, Complianace, and GDPR

Google Tracks Location Data Even When Users Turn Service Off, AP Report Finds - Google’s smartphone services store users’ locations even when privacy settings are adjusted to shut these features off, according to a new report by the Associated Press. ...(more)

Data Destruction Services Ensure that Data is Sincerely Dead - Vendor ecosystems offer both system refurb and data destruction services that can help companies meet compliance and security concerns. ...(more)

Conferences, Classes, and Events

Announcing new Guatemala SQL Server user group - I am very happy to announce that after a long time struggling and filling all the requirements, we were able to create the SQL Server local user group for Guatemala.The purpose of creating this group was to empower the local database administrators and ......(more)

Take the SQLChallenge: Tuning a Stored Procedure - I’ve just published a new SQLChallenge course for subscribers, and I think it’s one of the best ones yet. Your mission is to: Identify which statement is slowing down our stored procedure the mostTune the code to speed it up. You can change the query ......(more)

Azure Machine Learning 101 - I just wanted to let you know about a free webinar I’ll be giving as part of Redgate’s SQL in the City Streamed on September 5th. You can register here. The theme is “Adapt and thrive as a data professional.” The tech world is changing fast, and one ......(more)

Teaching a Database Design and Implementation Class in Orlando 9-13 - Thanks to Andy Warren (@sqlandy), on Thursday, September 13, I will be in Orlando, FL teaching an all-day seminar on the process of designing and implementing a relational database, entitled “Constructing a SQL Server Database.” Throughout the following ......(more)

Learn PowerShell in Oklahoma, August 24! - SQL Saturday Oklahoma City is coming up RIGHT SOON, on August 25. The day before SQL Saturday OKC - August 24 - we're teaching our one day Beginning PowerShell class! Buy your tickets TODAY. The post Learn PowerShell in Oklahoma, August 24! appeared ......(more)

SQLpassion Online Trainings for Fall 2018 - (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.) Today I’m quite happy to announce my SQLpassion Online ......(more)

Career Growth

Is the DBA dead... or alive and preparing for the future? - Sure, there’s some doom and gloom out there about the future of the DBA. But in this session, you’ll learn how to adapt, evolve, survive and even thrive in a changing database world. You’ll get to see real-world statistics on the evolving role of the ......(more)

Another Brick in the Wall–T-SQL Tuesday #105 - It’s that time again, and this month we have a good topic from Wayne Sheffield. We’re asked about getting stuck, about being blocked, about encountering a brick wall. I have to say that I don’t think I encounter these very often, usually because I try ......(more)

Doing the bare minimum to stop toxic behavior - I recently saw on Twitter some abusive behavior, and I know that Twitter and trolls go together like peanut butter and jelly, but for whatever reason I’m angry. Maybe because I know the person who was abused, maybe because the abuser is a part of our ......(more)

Employee Agreements & Contracts: Anti-Patterns (31 minute video) - When you take a new job in software engineering or in IT, within the paperwork there often lurks an employee agreement: a contract between you and your employer. In this half-hour live episode, I talk about why these contracts exist, and multiple anti-patterns ......(more)

Hitting the Wall – #TSQL2sDay - Photo by Farrel Nobel on Unsplash Welcome to another edition of T-SQL Tuesday! This T-SQL Tuesday is hosted by Wayne Sheffield ( blog | twitter ) and he has asked us to talk about […] a time when you ran up against your own brick wall, and how you worked ......(more)

Backup and Recovery

Multiple Ways to Backup SQL Server in an Azure VM - This post will discuss several ways that you can back up SQL Server which is running in an Azure virtual machine. SQL Server One of the more common workloads that I encounter in Azure is SQL Server – often deployed in lift & shift scenarios into Azure. ...(more)

Azure SQL Managed Instance

Database ownership chaining in Azure SQL Managed Instance - Azure SQL Managed Instance enables you to run cross-database queries the same way you do it in SQL Server. It also supports cross-database ownership chaining that will be explained in this post. Cross database ownership chaining enables logins to access ......(more)

Azure SQL Data Warehouse and Data Lake

Azure Databricks: Simplifying Big Data and AI - The big data and AI world can be a complex one for companies to navigate alone. This has led to the perception of a high entry barrier among many organizations. Some of the early comments and questions we hear from companies we partner with include: ......(more)

What’s the Difference Between a Data Warehouse and Data Lake? - Click to learn more about video blogger Andrew Brust. The Big Data & Brews video blog series continues with host Andrew Brust, Senior Director of Market Strategy and Intelligence at Datameer. The series touches on hot topics within the business of Big ......(more)

Azure SQL DW – Query Labels - Using a query label in Azure SQL DW (Data Warehouse) can be a really handy technique to track queries via DMVs. You might want to do this to see what problematic queries are doing under the covers. Let’s check out … Continue reading ? ...(more)

Analysis Services / BI on the MS Stack

Microsoft Bot -Part1: Create a sample - Using Microsoft Bot in applications and report make them more agile and interactive. there are many ways to use Microsoft Bot. In this posts and the next one, I will explain how to create a Microsoft Bot in Azure and then embed it in your PowerBI and ......(more)

AI/Machine Learning/Cognitive Services

Machine Learning Helps Tame the Complexities of WiFi Networks--and More - In just one application of what are many more to come, ML helps keep complex WiFi networks on air as human operators struggle to keep up. ...(more)

Administration of SQL Server

CheckDB Error Msg 824 level 24 - One of the common CheckDB errors that I see is the Message 824, level 24. This is something that I regularly work to repair for customers with great success. DBCC results for ‘YourDatabaseName’. CHECKDB found 0 allocation errors and 0 consistency errors ......(more)

SQL SERVER – Useful Queries – Why Huge Transaction Log (LDF) File? Fix Low Disk Free Space on the Drive Used by LDF Files - During my Comprehensive Database Performance Health Check engagement with the client, one of the most common issue found was the huge size of the transaction log file. In this blog, we would learn about useful queries which I use to help my client in ......(more)

SQL SERVER – Error: 17300 – The Error is Printed in Terse Mode Because There was Error During Formatting - One of my clients contacted me in a situation where their business was down! Their production SQL Server instance used by their Human Resource team was unable to start in a clustered environment. In this blog, we would learn about how to fix the error: ......(more)

SQL Server Failover cluster instance upgrade failure issue from 2008 R2 to 2012 - Today, one of my clients' production environment was facing an issue with the failed upgrade of a SQL Failover Cluster Instance from SQL 2008 R2 to SQL 2012. They were trying the upgrade since a couple of days and it was failing with the below error TITLE: ......(more)

How Trace Flag 2335 Affects Memory Grants - This trace flag is documented in KB #2413549, which says, “Using large amounts of memory can result in an inefficient plan in SQL Server.” The details are a little light, so let’s run a quick experiment with: SQL Server 2017 CU 8 (14.0.3029.16) VM with ......(more)

What is Lock Pages In Memory? – Interview Question of the Week #186 - Question: What is Lock Pages In Memory? Answer: When enabled, it allows accounts to keep data in physical memory, instead of paging it to virtual memory on disk. In simple words, Lock Pages in Memory can help improve your system’s performance in the ......(more)

SQL Server detected a DTC/KTM in-doubt transaction - Few days back, one of my clients' faced an issue with their Availability group getting into the resolving state. On checking the logs found that the issue was due to a split brain scenario and as it was a business critical issue, we resolved the issue ......(more)

Cleaning up Query Store data after database restore - Query Store is a very powerful tool for performance tuning, if you are not using it already you should!! It will save you a lot of time and effort because it makes the performance troubleshooting easy for DBA’s. It helps you to quickly find performance ......(more)

Complicated linked servers and a bunch of head-desks: T-SQL Tuesday #104 - Wayne Sheffield (b/t) is our host this month for the long running blog party (104 months now and running!) started by Adam Machanic (b/t). In this case Wayne is asking us to talk about a time when we hit a brick wall, metephorically speaking. At least ......(more)