The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Securing our databases and preventing the release of information our organizations have collected is an ongoing job for many of us. We patch our systems, ensure that our logins and users are granted just a few rights, encrypt data and backups, perhaps have various monitoring solutions, and certainly deal with plenty of stress at times. We work with network staff to ensure firewalls protect our systems. It may be a regular part of our job to actually argue with others that security is important for databases. If we work with regulated data, such as financial or medical information, then we may even have the struggles of compliance with auditors or even regulators.

All of our work can be for naught with a few simple mistakes that someone in our organization might make. Perhaps an employee takes a copy of production data on a laptop home and loses it. An employee might use the same password for a secure portal that they do for Facebook or some other social site, getting hacked and exposing our systems to others. We might even have an employee click on a phishing email or insert a random USB drive into their laptop and compromise entire infrastructures. As a security professional once noted, we have to win every time. The bad guys only have to win once.

It can be distressing, and even more so this week as I read this piece about medical data and Frank Abagnale, the inspiration for Catch My If You Can. In it, Mr. Abagnale states that he doesn't think technology will ever defeat social engineering, which is distressing to me. He may be right, though I certainly hope that machine learning and other technologies, along with lots and lots of data, will find ways to catch abnormal queries and data extraction, which often are a signal that potential data loss may be under way.

What's more distressing in the piece for me is the fact that some of this data, like the birthdays and SSNs, are stored for years. Unlike credit card data, which is more valuable right away, unchangeable data becomes more valuable over time, so it pays to keep it around. Maybe the most distressing item might be someone using your identity to get services, which then become billed to you. How can you prove that you didn't consume the services? I think that can be difficult, especially as we use more and more digital information that doesn't necessarily tie directly to a particular person at a point in time. This might be especially true as we store digital pictures of signatures, which often are a poor imitation of what a person's actual handwriting looks like. I shudder to think of those being used in a court of law.

Perhaps even more disconcerting is the idea that children's information is being taken at early ages, perhaps being sold decades later. I have no idea what to do here, or what I'd want others to do. These are going to be data problems we deal with for a long time, and many of us may end up collecting incorrect data in our organizations, thinking it's correct. I don't know many companies that have good processes for correcting data that's incorrect when it's received. Far too often we assume the data is correct, and only worry about ensuring the bits in a file are transformed correctly to the same bits in a database.

Security is an ongoing problem, with no easy solution. There is one thing I'm sure of. We, as data professionals, are going to be the ones frustrated by many of our efforts at security being thwarted by someone we work with.

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.

T-SQL

Doing Fuzzy Searches in SQL Server - When an application searches for specific text data, you can generally rely on it to get the search term right. For example, if it needs to find ‘sausages’, you won’t expect to receive a search on ‘sossyjez’. , however, When people search your website ......(more)

UnPivot The Output Of RESTORE HEADERONLY - Today's less-than-ugent challenge was to un-pivot the output of RESTORE HEADERONLY. I thought for certain someone else, somewhere, at at some time must have wanted to do the same thing. So I asked the Twitterverse, but no one responded. I guess I'll ......(more)

Parsing Gigantic JSON Text - Jovan Popvic has created a 4.35 GB JSON array: SQL Server 2016 and Azure SQL Database enable you to parse JSON text and transform it into tabular format. In this post, you might see that JSON functions can handle very large JSON text – up to 4GB. First, ......(more)

SQL Server Temporal Tables: How-To Recipes - Temporal, or system-versioned, tables were introduced as a database feature in SQL Server 2016. This gives us a type of table that can provide information about the data that was stored at any specified time rather than just the data that is current. ...(more)

A Program to Find INSERT Statements That Don’t Specify Columns - I’ve got a program here that finds SQL in procedures with missing column specifications. Table Columns Are Ordered Even though they shouldn’t be. Unlike mathematical relations, SQL tables have ordered columns, but please don’t depend on it. In other ......(more)

SQLskills SQL101: Stored Procedures - Sometimes going back-to-the-basics is the best thing to do to fully understand something. Some of us have been doing things in a certain way for so long that we’ve missed a very basic / important point. So… SQLskills is introducing a SQL101 series of ......(more)

Stopping Execution of Future Batches - There is a command that as far as I can tell has been in the product since SQL2008 but I was woefully unaware of. Actually I find many others are unaware as well and hence the blog post. The command is SET NOEXEC which has options for ON or OFF. ...(read ......(more)

Permanent Employment v Contracting: How the figures stack up - In my latest learning tree newsletter, I discussed analysing some of the salary survey data that the good folks over at BrentOzar.com collected and made available back in January 2017. The data is very interesting and in my first post on this topic over ......(more)

T-SQL Puzzler - I have presented on T-SQL Window Functions at least 30 times over the past five years. I love the comments I get after the presentations. Without fail, at least one person will tell me how the session helped them figure out the solution to a problem ......(more)

SQL Server Security and Auditing

SQL Server Encryption: Always Encrypted - Always Encrypted is a new feature included in SQL Server 2016 for encrypting column data at rest and in motion. This represents an important difference from the original column-level encryption, which is concerned only with data at rest. Always Encrypted ......(more)

How to sign a SQL Server CLR Assembly in Visual Studio 2015 - It took me quite a while to figure out how to sign a SQL Server CLR Assembly (like a CLR stored procedure / function / trigger etc) in VS 2015. If you google “sign a SQL Server CLR Assembly”, you will most likely find out content published 5+ years ......(more)

Cannot Generate SSPI Context - I ran into a SQL Server connectivity issue this week that was a puzzler, but (thankfully) only for a short time. Maybe my pain can be your gain. Here's a quick rundown. I installed and configured a new instance of SQL Server. After getting appropriate ......(more)

SQL Server News

SQL Server 2016 Developer Edition in Windows Containers - We are excited to announce the public availability of SQL Server 2016 SP1 Developer Edition in Windows Containers! The image is now available on Docker Hub and the build scripts are hosted on our GitHub repository. This image can be used in both Windows ......(more)

Released: Public Preview for SQL Server Management Packs Update (6.7.16.0) - We are getting ready to update the SQL Server Management Packs. Please install and use this public preview and send us your feedback ([email protected])! We appreciate the time and effort you spend on these previews which make the final product ......(more)

Cumulative Update #4 for SQL Server 2014 SP2 - The 4th cumulative update release for SQL Server 2014 SP2 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......(more)

Cumulative Update #11 for SQL Server 2014 SP1 - The 11th cumulative update release for SQL Server 2014 SP1 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......(more)

SQL Server next version CTP 1.3 now available - Microsoft is excited to announce a new preview for the next version of SQL Server (SQL Server v.Next). Community Technology Preview (CTP) 1.3 is available on both Windows and Linux. In this preview, we added several feature enhancements to High Availability ......(more)

Spatial Data

Natural Earth In SQL Server - Jeff Pries shows how to use the Natural Earth data set in SQL Server: But what about when you need more flexibility in your geographic display? Some examples of this may be wanting to display something that you can’t find a shape file for (maybe all ......(more)

Security news and thoughts

Frank Abagnale, world-famous con-man, explains why technology won’t stop breaches - Enlarge / Frank Abagnale, as played by Leonardo DiCaprio in Catch Me If You Can, once pretended to be a doctor. Now he's teaching the health industry about the threat of identity theft. (credit: Dreamworks) Frank Abagnale is world-famous for pretending ......(more)

At death’s door for years, widely used SHA1 function is now dead - For more than six years, the SHA1 cryptographic hash function underpinning Internet security has been at death's door. Now it's officially dead, thanks to the submission of the first known instance of a fatal exploit known as ......(more)

No key, no login: G Suite admins can now make FIDO security keys mandatory - Enlarge / The Yubikey Neo (the black USB key) is a FIDO U2F-compliant key that works over NFC or USB. Today, Google announced a new G Suite feature that allows admins to lock down accounts so they can only be accessed by users with a physical USB security ......(more)

Hacks all the time. Engineers recently found Yahoo systems remained compromised - Some five months after Yahoo disclosed a security breach that exposed sensitive data for 500 million accounts, some of its systems remained compromised, according to a report published Tuesday. The report said that in light of the hacks, Verizon would ......(more)

R Language

Preview: R Tools for Visual Studio 1.0 - After more than a year in preview R Tools for Visual Studio, the open-source extension to the Visual Studio IDE for R programming, is nearing its official release. RTVS Release Candidate 1 is now available for download, giving you the opportunity to ......(more)

Global Maps In R - The folks at Sharp Sight Labs show how to create high-quality map visuals in R: Maps are great for practicing data visualization. First of all, there’s a lot of data available on places like Wikipedia that you can map. Moreover, creating maps typically ......(more)

Product Reviews

SQL Clone and backups protected with TDE - My last article demonstrated that you can use SQL Clone to make a copy of a live database, even when that database is protected with Transparent Data Encryption (TDE). However, not everyone will want to use SQL Clone against a live database, especially ......(more)

Press Releases

Download Free Power BI book PDF Format - It has been a long time request in my blog posts to release the book in PDF format, and here you go; Free PDF format of Power BI from Rookie to Rock Star book edition 1 is now available. You can download and print it for free! However, this book has ......(more)

PowerShell

Export-DMVInformation Updates - Sander Stad has made changes to his Export-DMVInformation Powershell module: Last Friday I had the chance to show the Export-DMVInformation module to the Dutch Powershell user group. After the presentation I got a couple of suggestions and wanted to ......(more)

Export All Plans From Cache to a .SQLPLAN File - I was asked if there was an easy way to export all the plans from cache to a .SQLPLAN file. My immediate answer was, “I’m sure there’s a PowerShell script out there somewhere.” However, rather than do a Gingle search, I figured I’d quickly knock up an ......(more)

PowerPivot/PowerQuery/PowerBI

Coloring an entire state in Power BI - Was asked during today’s Power BI User Group leaders call how to fill in an entire state with a color based on a measure. Turns out the shape map makes this pretty pretty easy and since a picture is worth a 1000 words have decided to save myself some ......(more)

Mark as Date table in Power BI #dax #powerbi - One year ago I wrote an article describing how the time intelligence DAX functions work in Power BI (I just updated the article including the example described below). In a recent event in Sydney I observed a strange (or at least unexpected) behavior ......(more)

Handling Missing Members In The CubeSet() Function With Power Pivot - Last week I received an email from a reader asking how to handle missing members in MDX used in the Excel CubeSet() function. My first thought was that this could be solved easily with the MDXMissingMemberMode connection string property but it turns ......(more)

Power BI Custom Visuals Class (Module 39 – Waffle Chart) - In this module you will learn how to use the Waffle Chart Power BI Custom Visual. The Waffle Chart visual is most useful for presenting a percentage of data. This chart is a great option to choose over other visuals like Pie Charts, which are not great ......(more)

Performance Tuning SQL Server

Using DBCC CLONEDATABASE and Query Store for Testing - Last summer, after SP2 for SQL Server 2014 was released, I wrote about using DBCC CLONEDATABASE for more than simply investigating a query performance issue. A recent comment on the post by a reader got me thinking that I should expand on what I had ......(more)

Importance of choosing correct bucket count of hash indexes on a memory optimized table - I was working with a customer to troubleshoot memory optimized table issues. In this scenario, our customer uses a memory optimized table variable. He put 1 million rows of data into the table variable and then process it. Based on what he said, I ......(more)

Be aware of 701 error if you use memory optimized table variable in a loop - In blog “Importance of choosing correct bucket count of hash indexes on a memory optimized table”, I talked about encountering performance issues with incorrect sized bucket count. I was actually investigating an out of memory issues with the following ......(more)

Crappy Missing Index Requests - When you’re tuning queries It’s sort of a relief when the first time you get your hands on it, you get the plan and there’s a missing index request. Even if it’s not a super high-value one, something in there is crying for help. Where there’s smoke, ......(more)

Estimated Costs of All Queries - One question constantly comes up; What should the Cost Threshold for Parallelism be? The default value of 5 is pretty universally denigrated (well, not by Microsoft, but by most everyone else). However, what value should you set yours to? What Do Your ......(more)

Improve performance by replacing temp tables with memory optimized tables - Part 3 - Welcome to the last part of this series, you can find the previous post here (http://www.sqlservercentral.com/blogs/denniss-sql-blog-1/2017/02/01/improve-performance-by-replacing-temp-tables-with-memory-optimized-tables-part-1/) and here (http://www.sqlservercentral.com/blogs/denniss-sql-blog-1/2017/02/08/improve-performance-by-replacing-temp-tables-with-memory-optimized-tables-part-2/).Previously ......(more)

Getting more statistics information programmatically - As the building blocks which the Query Optimizer uses to know data distribution on tables, statistics are one of the key aspects in calculating a good enough plan to read data. So, when engaging in query performance troubleshooting, information about ......(more)

Microsoft News

Microsoft Delays February's Batch of Security Updates - Microsoft's Patch Tuesday came and went this week without any patches. The Redmond company's monthly batch of security updates for its range of software, scheduled for release Tuesday, has been delayed until March, the company said, citing an unspecified ......(more)

Microsoft confirms second major Windows 10 update coming in 2017 - MSPoweruser There's a second major Windows 10 update coming later in 2017, Microsoft has confirmed. The second update, code-named Redstone 3, will follow sometime after the putative April release of the Windows 10 Creators Update. This new update was ......(more)

MDX/DAX

Propagate filters using TREATAS in DAX - This article describes how to create a virtual relationship in DAX using the TREATAS function, which is more efficient than approaches based on INTERSECT or FILTER. A virtual relationship is a DAX pattern to transfers a filter context from a table to ......(more)

HA/DR/Always On/Clustering

SQL Server Availability Groups in Azure VM setup with AAD Domain Services - Deploying SQL Server Availability Groups in Azure VMs typically involved provisioning two additional ......(more)

Trouble shooting Availability Group Listener in Azure SQL VM - Last week, I have had one mystery challenge while creating Availability Group listener in Azure. We followed Configure one or more Always On Availability Group Listeners – Resource Manager and found out the listener didn’t work as expect. Let me walk ......(more)

AlwaysOn Availability Groups: Step by Step Setup - An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to eight sets of corresponding secondary ......(more)

Events to attend

Presentation Layer using Excel and SSAS - Tips & Tricks and Real World Examples - June 3, 2014 For more information and to register, please go to https://ssig201406.eventbrite.com Presentation Layer using Excel and SSAS - Tips and Tricks and Real World Examples The old adage, “Too much data, not enough information”, is more apparent ......(more)

ETL/SSIS/ELT

Creating SSIS Packages with the SQL Server Import and Export Wizard - This material was originally posted on the Linchpin People blog . In this post, I demonstrate how to use the SQL Server 2012 Import and Export Wizard to create a SQL Server 2012 Integration Services (SSIS 2012) package. The demo is created on a virtual ......(more)

DevOps and Continuous Delivery (CI/CD)

How to build multiple database versions from the same source: pre-deploy migration scripts - This is the third post in a three-part series that explains how to maintain a single declarative source of truth in version control for a SQL Server database, which can be deployed to multiple environments, despite the fact that some environments have ......(more)

Devops without management buy in? - I was talking to someone at a meetup recently who was really keen on doing continuous deployment for their database but they had a number of issues, the main was that because management wasn't sold on the idea and the DBA's had complete control to push ......(more)

Database Design, Theory and Development

Finding primary key candidates - Probably one of the most common challenges I see when I do ETL and business intelligence work is analyzing a table (or a file) for possible primary keys. And while a bit of domain knowledge, along with a quick eye and some experience will get you really ......(more)

Data Science

Will Data Scientists Automate Themselves Out of Jobs? - Productivity in Data Science isn’t a matter of output in any quantitative sense. It’s more an issue of the quality of what Data Scientists produce. In a Data Science context, quality refers to the validity and relevance of the insights that statistical ......(more)

Data Privacy

Digital Privacy is the Wild West - Who has the legal right to access your personal and private digital assets? The answer can be complex, and will depend on where you live, where you are traveling from and to, and whether or not you’ve been suspected of a crime. The rules governing personal ......(more)

Backup and Recovery

What’s in my backup file? - Restoring a backup file is pretty easy right? RESTORE DATABASE [Test] FROM DISK = 'C:\backups\backup.bak'; Ok, but what if more than one database backup is stored in that single backup file? Didn’t know you could do that? Yep. You can. BACKUP DATABASE ......(more)

Azure SQL Database

Blob Auditing in Azure SQL Database is Generally Available - We are excited to announce that SQL Blob Auditing is now Generally Available in Azure SQL Database. Blob Auditing tracks database events and writes audited events to an audit log in your Azure Storage account. Auditing can help maintain regulatory compliance, ......(more)

Loading files from Azure Blob Storage into Azure SQL Database - Azure SQL Database enables you to directly load files stored on Azure Blob Storage using the BULK INSERT T-SQL command and OPENROWSET function. Loading content of files form Azure Blob Storage account into a table in SQL Database is now single command: BULK ......(more)

Azure SQL Data Warehouse and Data Lake

Ingest data into Azure Data Lake Store with StreamSets Data Collector - Today, I want to give a shout out to one of our partners who has a great offering for Azure Data Lake Store customers. When ingesting large scale data into a data lake, data often requires data transformations such as cleaning and filtering. StreamSets ......(more)

Enabling U-SQL Advanced Analytics for Local Execution - After we announced the ability for U-SQL to massively distributed Python code in the Azure Data Lake Analytics service, a lot of developers have been asking us when the the Python support will work using U-SQL Local Execution. In this post, we’ll describe ......(more)

Making Azure Data Lake Store the default file system for Hadoop - Here’s an article that explains how to make Azure Data Lake Store the default file system for Hadoop. Making Azure Data Lake Store the default file system for Hadoop Please read through and keep your questions/comments coming. ...(more)

Analysis Services / BI on the MS Stack

Encoding Hints and SQL Server Analysis Services vNext CTP 1.3 - The public CTP 1.3 of SQL Server vNext on Windows is available here! The corresponding versions of SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS) will be released in the coming weeks. They include much-anticipated new features, ......(more)

AI/Machine Learning/Cognitive Services

Azure Cognitive Services Text Analytics – An API Calling Application - Just last week we had the fantastic opportunity to present at Microsoft Ignite 2017 in the Gold Coast on Azure Cognitive Services – and we had an absolute blast of a time! I co-presented with Kristina Rumpff who works at Microsoft in the Data Platform ......(more)

The Data Science Process with Azure Machine Learning - It’s no secret today that all our applications and devices are generating tons of data; thus making data analytics a very hot topic these days and Microsoft Azure has all the tools necessary to ingest, manage and process all these data, also called Big ......(more)

An Artificial Intelligence Story: The Robot Boss and Data Operations - Hitachi became an instant sensation when it displayed its first robot in an IT Fair in 1970. Ever since, this Japanese technology company has made great strides in Artificial Intelligence (AI), and more recently, in Machine Learning enabled AI research, ......(more)

Administration of SQL Server

Max Worker Threads: Don’t Touch That - More isn’t faster I’ve had people give me all sorts of janky reasons for changing Max Worker Threads. I’ve even had people tell me that someone from Microsoft told them to do it. The thing is, all changing that setting does is help you not run out of ......(more)

Better SQL Agent Job Alerts - They kinda suck. I mean it is nice that we have the option for the SQL Agent to tell us when a job fails or succeeds, but if you have ever looked at the message, there isn’t much detail in it. Take a look at this (slightly edited) email I used to get ......(more)

How to handle Deadlocks in SQL Server - (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.) In todays blog posting I want to talk about how to handle ......(more)

SQL SERVER – Fix: Error 946, Severity: 14 – Cannot open database ‘DB’ version 782. Upgrade the database to the latest version - I was consulting a client for migration from SQL 2014 to SQL 2016. They were using methodology discussed in books online here. https://msdn.microsoft.com/en-us/library/dn178483.aspx (Upgrading AlwaysOn Availability Group Replica Instances). Let us learn ......(more)

Let’s Corrupt a SQL Server Database Together, Part 1: Clustered Indexes - Hold my beer. CREATE DATABASE [50Ways]; GO ALTER DATABASE [50Ways] SET PAGE_VERIFY NONE; /* Normally a bad idea */ GO USE [50Ways]; GO CREATE TABLE [dbo].[ToLeaveYourLover]([Way] VARCHAR(50)); GO INSERT INTO [dbo].[ToLeaveYourLover]([Way]) VALUES ('Slip ......(more)

Adding a T-SQL Job Step to a SQL Agent Job with PowerShell - In my last post, I explained how to alter an existing job step across many servers. I also had cause to add a T-SQL Job step to a large number of jobs as well. This is how I did it. As before I gathered the required jobs using Get-SQLAgentJob command ......(more)

Adding Partitions to the Lower End of a Left Based Partition Function - I recently got a table partitioning question from a reader: We now need to load some historical data into the table for 2013 so I want to alter the function and schema to add monthly partitions for this. But I can’t work out how to do this using SPLIT? ......(more)