The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

This week the next preview version of SSMS v18 was released. This is the sixth preview release, and I'm guessing that this will be one of the last. Six seems like a lot of releases, and I'd like to think that this is getting close to being ready to use by most people, but I'm not sure. I certainly have some some annoying and problematic bugs, so I can't be sure there won't be a seventh, eighth, or ninth preview, but it does seem to be fewer issues are being reported.

With the release of SQL Server 2016, SSMS was decoupled from the database engine, and we saw some SSMS v16 releases. I didn't use many of those before moving to SSMS v17, which came slightly after SQL Server 2017. I'm glad we'll start to get the versions separate from the engine as this is confusing to many people. However, I do expect that plenty of people will call this SSMS 2019 or think there's a SQL Server 2018, etc. If we could get more people to leave the older SSMS versions that came with 2008 R2, 2012, 2014, then I'll take a little confusion in how we talk about SSMS.

However, we don't need to stick with SSMS these days. If you've been heads down and just focused on keeping your existing systems running, you might not realize that not only do we have different SSMS choices, but we also have other tools. I'm not talking about SQLCMD, bcp, and Visual Studio, but we have other ways of working with SQL Server. Visual Studio Code has an mssql extension if you write code in that IDE, which might be something you full stack developers need.

For the SQL Server people, we have a fork of VS Code in Azure Data Studio. This is a lighterweight IDE built for SQL Server work. We also have the mssql-cli tool, giving us way more control over command line work than we have with SQLCMD. I haven't worked with it much, but it's on my list for January to play with a bit. I don't know how well either of these will catch on, but let me know your thoughts. Are you doing more work with either of these tools?

There are certainly plenty of other choices as well. My company (Redgate) and others make plugins for SSMS that improve how you work with SQL Server. There are even other IDEs, such as DataGrip, that you can use and abandon the Microsoft tools altogether. I'm not sure I would look to leave SSMS entirely, but perhaps I should give some of these a try at some point.

Tools matter to many professionals. Mechanics treasure their sets of wrenches, chefs love their knives, and we ought to have tools that we know, use, and are comfortable with. This includes both the actual software and the various scripts, code, and helper applications that allow us to work efficiently. If you don't love your tools, or have a collection, maybe now is the time to start the new year building some skills with the one you use, or try a new one. Wayne Sheffield has a nice series on SSMS and I'm hoping to get some other pieces written for other tools. If you want to tackle one for SQLServerCentral, let me know.

If you're looking for a new tool to try for SQL Server work, might I suggest some PoSh and dbatools. It's an amazing combination for lots of tasks.

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.

Virtualization and Containers

Microsoft unveils Windows Sandbox: Run any app in a disposable virtual machine - Enlarge (credit: F Delventhal) A few months ago, Microsoft let slip a forthcoming Windows 10 feature that was, at the time, called InPrivate Desktop: a lightweight virtual machine for running untrusted applications in an isolated environment. That feature ......(more)

Building A Kubernetes Cluster For SQL Server 2019 Big Data Clusters, Part 1: Hyper-V Virtual Machine Creation - This blog post is the first in a series detailing how to build a Kubernetes cluster to deploy a SQL Server 2019 big data cluster to. For the purposes of learning and on-boarding there are three options: MinikubeThis is a single node cluster that can ......(more)

T-SQL

SQL SERVER – How to Write Correlated Subquery? - This is a follow-up blog post to my earlier blog post written about SQL SERVER – Writing Your First Subquery From a Single Table. In the blog post, we discuss how to write a correlated subquery and why it is actually necessary. In a query, a correlated ......(more)

Calculating Proportional Values in SQL - SQL is great for aggregating: counting, summing, and averaging numbers are straightforward tasks in SQL. When looking at a distribution, it is often useful to look at them in context and compare the relative size of each part of the proverbial pie. ...(more)

SQL SERVER – Writing Your First Subquery From a Single Table - My primary responsibility is to help different organizations with tuning their SQL Server Performance and here is the link to my most popular service SQL Server Performance Health Check. I often encounter complicated performance problems which are not ......(more)

Restarting a Sequence–#SQLNewBlogger - Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. As part of my experiments with the sequence object, I wanted to see what allows me to restart a sequence at a new value. This ......(more)

Identity in a SELECT - Always be learning right? So when I saw this I had to go HU?! That’s new. What’s going on here? SELECT IDENTITY(INT, 1,1) AS Id, Now, looking at it a bit more closely you’ll see that this is a function call, not just a property. Now, in my research ......(more)

Multiple Ways to Remove Duplicates from SQL Tables - Overview This article discusses two different approaches available to remove duplicate rows from SQL table(s) which often becomes difficult over time as data grows if this is not done on time. The presence of duplicate rows is a common issue that SQL ......(more)

Constraining and checking JSON Data in SQL Server Tables - So you have a database with JSON in it. Can you validate it? I don’t mean just to ensure that it is valid JSON, but ensure that the JSON contains values that are legitimate. Are NI values, postcodes or bank codes valid? Can the dates or GUIDs be successfully ......(more)

Sniffed Nulls and Magic Numbers - I Sniff Your Milkshake Building off of A Simple Stored Procedure Pattern To Avoid, I wanted to talk about a similar one that I see quite often that is not nearly as clever as one would imagine. I goes something like this: If this variable is passed in ......(more)

Quickly Compare Data in Two Tables Using CHECKSUM and CHECKSUM_AGG - There are times when we need to compare two tables and figure out if the data matches. I often see a number of ways of doing this suggested, most are quite slow and inefficient. I’d quite like to share a quick and slightly dirty way of doing this using ......(more)

Clustered Column Store Load Pitfalls – Magic number 64 when using WHERE IN clause - The Problem During our workload we have recognized a difference in performance for queries using IN clause. We are building query dynamically, so you can have a different length of list in IN clause. We work on the top of the Clustered Column Store ......(more)

Tools

Savings changes is not permitted. The changes you have made require the following tables to be dropped and re-created. - Microsoft does a lot of things to try to help us not shoot ourselves in the foot. This little error is one of them. It’s an annoying error, and almost everyone turns it off automatically, so what is it and why is it there? Well, by default, when using ......(more)

Tech News

Kroger-owned grocery store begins fully driverless deliveries - Nuro, a startup founded by two veterans of Google's self-driving car project, has reached an important milestone: it has started making fully autonomous grocery deliveries on public streets. Fry's Food, a brand owned by grocery giant Kroger, launched ......(more)

SQL Server Security

Confidential computing using Always Encrypted with secure enclaves in SQL Server 2019 preview - SQL Server 2019 preview brings encryption technology to a broader set of scenarios by enabling rich confidential computing capabilities with the enhanced Always Encrypted feature, Always Encrypted with secure enclaves. Always Encrypted with secure enclaves ......(more)

SQL Server on Linux

SQL Server On Linux: Forced Unit Access (Fua) Internals - Overview SQL Server relies on Forced-Unit-Access (Fua) I/O subsystem capabilities to provide data durability, detailed in the following documents: SQL Server 2000 I/O Basic and SQL Server I/O Basics, Chapter 2 Durability: “In database systems, durability ......(more)

SQL Server Linux: Directory fsync Activities - When Creating, Renaming or Deleting (remove/unlinking) a file, Linux requires the direct parent directory to be synchronized. As documented in the manpage for fsync core changes to the directory require the directory itself to be synchronized. “Calling ......(more)

SQL Server News

Cumulative Update #13 for SQL Server 2017 RTM - The 13th cumulative update release for SQL Server 2017 RTM 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)

Introducing UTF-8 support in SQL Server 2019 preview - With the first public preview of SQL Server 2019, we announced support for the widely used UTF-8 character encoding as an import or export encoding, and as database-level or column-level collation for string data.This is an asset for companies extending ......(more)

Security news and thoughts

IBM Says No Evidence That `Sensitive' Data Was Taken by Hackers - International Business Machines Corp. says it doesn’t have any evidence that hackers took “sensitive” company and client information after Reuters reported China’s Ministry of State Security attacked IBM and Hewlett Packard Enterprise Co. ...(more)

Microsoft issues emergency update to fix critical IE flaw under active exploit - Enlarge (credit: Microsoft) Microsoft has issued an emergency update that fixes a critical Internet Explorer vulnerability that attackers are actively exploiting on the Internet. The memory-corruption flaw allows attackers to remotely execute malicious ......(more)

Congressional Report on the 2017 Equifax Data Breach - The US House of Representatives Committee on Oversight and Government Reform has just released a comprehensive report on the 2017 Equifax hack. It's a great piece of writing, with a detailed timeline, root cause analysis, and lessons learned. Lance Spitzner ......(more)

Memory-Based Attacks are on the Rise - Microsoft updates Windows Defender ATP to detect and auto remediate memory-based attacks. Hackers are moving beyond delivering files with malicious payloads because traditional AV can block them. Security is a cat-and-mouse game where you need to constantly ......(more)

How Shopify Avoided A Data Breach, Thanks To A Bug Bounty - At KubeCon + CloudNativeCon NA 2018, Shopify and Google detail a Kubernetes security incident that was reported by a bug bounty security researcher, that was quickly remediated before any harm was done. ...(more)

Facebook Bug Gave Developers Broader Access to User Photos - The bug, which spanned the 12 days between Sept. 13 and Sept. 25, potentially gave developers access to other images, such as photos uploaded to the site but not yet posted, the Menlo Park, California-based firm said. ...(more)

Reporting Services

SSRS Reporting Basics: When is SSRS the Right Tool? - SQL Server Reporting Services (SSRS) is a server-based reporting tool, ideal for paginated reports. It represents a centralized approached to data governance, with all of your report files located on a central server. However, there are some self-service ......(more)

R Language

AzureStor: an R package for working with Azure storage - by Hong Ooi, senior data scientist, Microsoft Azure A few weeks ago, I introduced the AzureR family of packages for working with Azure in R. Since then, I’ve also written articles on how to use AzureRMR to interact with Azure Resource Manager, how to ......(more)

Product Upgrades and Releases

Announcing General Availability of Azure Database Migration Service (DMS) Premium Tier - The Azure Database Migration Service is a fully managed service designed to enable seamless migrations from multiple database sources to Azure data platforms with minimal downtime (online migrations). The Premium pricing tier, which previously named ......(more)

R 3.5.2 now available - R 3.5.2, the latest version of the R language for statistical computation and graphics from the R Foundation, was released today. (This release is codenamed "Eggshell Igloo", likely in reference to this or this Peanuts cartoon.) Compared to R 3.5.1, ......(more)

SSMS 18.0 Public Preview 6 Released - We are very excited to announce the Public Preview 6 of SQL Server Management Studio (SSMS) 18.0. In the spirit of more frequent releases, Preview 6 brings another incremental update to SSMS 18.0. This release has new features and capabilities, several ......(more)

SQL Source Control v7 Enhancements - With the v7 release, SQL Source Control now enforces Git hooks, and provides full support for working with Git repositories hosed on Azure DevOps. It also comes with pre- and post-deployment scripts to help it easier to automate the build and deploy ......(more)

Product Reviews and Articles

Comparison Review: Microsoft SSDT vs Redgate SQL Source Control - Hi. I’m still not Brent. My name’s Alex, and I care about databases, DevOps and data protection. Last year I delivered a session on GroupBy all about how to do Database DevOps right, and back in May I wrote a blog post for BOU about how to screw it up. Now, ......(more)

The Data Catalog from Redgate - One of the important tasks that is needed for any organization that wants to better protect their sensitive data is some sort of list of what data is sensitive. Often we might assume all data matters, in which case we embark upon projects to over protect ......(more)

First impressions: SQL Server 2017 Administration Inside Out - I recently had the pleasure of reading SQL Server 2017 Administration Inside Out by William Assaf, Randolph West, Sven Aelterman, and Mindy Curnutt. I love this book. While I haven’t finished reading it yet, I wanted to post my first impressions. I’ll ......(more)

Database DevOps for SQL Server using SQL Toolbelt - To many people, DevOps, means automating the processes involved in development and provisioning. This focus on automation is important, of course, because it allows us to deliver releases to our users reliably and frequently. Automation becomes more ......(more)

PowerPivot/PowerQuery/PowerBI

Displaying a list of selected months - Today I woke up with an interesting question, about how to show a selection of months in a nice way, detecting contiguous selection. You can easily understand the desired solution from the following figure: I enjoyed writing a quick solution, which ......(more)

Load the Latest Version of a File in Power Query - There are quite a few common business scenarios where you get data in the form of a file on a regular basis from some source system and you need to process these files into your Power BI reports. There are 3 common patterns that come to mind. Each day/month/year ......(more)

Power BI And Column Order In M Queries And Dataset Tables - The order of the columns in a table in a Power BI dataset doesn’t matter all that much, especially because the Fields pane in Power BI Desktop ignores the original column order and lists the columns in a table in alphabetical order. However there are ......(more)

Custom Power BI Themes: Page Background Images - In a previous blog post, we looked at how to change the background colors of Power BI reports. In this blog post, we will take it one step further and look at how to add background images to Power BI reports using custom themes. Fancy! In Power BI Desktop, ......(more)

Performance Tuning SQL Server

5 Ways to Change Execution Plans Without Tuning - A couple years back, I realized there were at least Ten Ways to Set MAXDOP. These days, as I write and tune queries, I realize there are an awful lot of ways to change the Cardinality Estimator (CE) and Query Optimizer, too. Let’s take a quick survey ......(more)

Bitmap - Introduction The Bitmap operator is used to build a bitmap that, based on a hash, represents which values may be present in a data flow. Due to the chance of hash collisions in the hash function used, the Bitmap process can produce false positives but ......(more)

Do Functions Stop You From Using Indexes? - Say I’ve got a function in my WHERE clause:SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; If I have an index on DisplayName, will SQL Server use it? Sure: Function in the WHERE clause Even though SQL Server can’t seek ......(more)

Seeing WriteLog wait type on your SQL Server? - One of the more common wait types for SQL Server is called WriteLog. In its most basic form it is a wait that is experienced as things are being written to the log file. This wait could be an indication of a major performance problem dealing with log ......(more)

NoSQL

MongoDB – Basic - MongoDB is a great Database NOSQL and easy to use and learn. if you know java scripting and database concept you can learn MongoDB. Surprisingly realize that I have not written much about MongoDB so started writing on this very powerful … Continue reading ......(more)

MongoDB – Docker Install - Docker and Container making my life great. these days I can explore every thing using this. I have installed MongoDB quick on my Docker using Docker pull Mongo or docker run –name MyMongo –restart=always -d -p 27017:27017 mongo this is … Continue reading ......(more)

Microsoft News

Microsoft Re-Re-Releases the Windows 10 October 2018 Update - Microsoft has made the Windows 10 October 2018 Update available to everyone, so long as they check Windows Update and manually install it themselves. ...(more)

MDX/DAX

Bidirectional relationships and ambiguity in DAX - Activating bidirectional cross-filter in a Tabular data model might create ambiguous paths in the chain of relationships, resulting in very dangerous models as numbers become unpredictable. This article provides a deep explanation of the kind of ambiguity ......(more)

Hardware News

Quick Look: Kensington SD7000 Surface Pro Docking Station - Earlier this year, Kensington announced their new docking station specifically designed for the Microsoft Surface Pro. The SD7000 transforms the Surface Pro into a miniature version of the Surface Studio, and offers some unique features as well. Looking ......(more)

The Lenovo ThinkPad A285 (12.5-Inch) Review: Ryzen Pro Gets Down to Business - A couple of months back Lenovo released the ThinkPad A285; a 12.5-inch business-class notebook featuring AMD’s Ryzen Pro mobile processor, complementing their 14-inch A485 Ryzen Pro powered model. These are the first two Lenovo ThinkPad models to feature ......(more)

HA/DR/Always On/Clustering

Infrastructure matters, even in the Cloud - I am still amused by terminology in the Information Technology field. Words like “Kubernetes,” “containers,” and the BASIC keywords PEEK and POKE, all bring a smile to my lips every time I read or say them. Equally amusing are marketing ideas, except ......(more)

ETL/SSIS/Azure Data Factory

File Maintenance – Cleaning Up Old Files - Using SSIS to Maintain the File System We have all run into a need or a desire to clean up old stale files from the file system, whether it be to remove old backup files or flat files that are created from one process or another. And based on this need/desire, ......(more)

SSIS Catalog Automatic Log Cleanup - Built into the SSIS catalog is a mechanism that can automatically purge log data after a set period of time. In this post, I’ll show you how to set up and manage that functionality. SSIS catalog automatic log cleanup The SQL Server Integration Services ......(more)

Extract the File Name in SSIS Data Flows using the FileNameColumnName Property - When extracting data from a flat file, it is handy to have the name of the file from which the data was retrieved. Whether you capture this information for auditing purposes, or you include it directly in the output table, the odds are good that you’ll ......(more)

Using the SSIS Multiple Flat Files Connection Manager - When building an ETL pipeline to import data from a text file, it’s very common to have the incoming data spread across multiple files. For example, if you are ingesting files generated on a periodic basis (per day, per hour, etc.), you could have dozens ......(more)

SSIS Catalog Execution Parameter Values - When doing any new development or major overhaul of existing SSIS architecture, I almost always recommend to clients that they deploy those packages to the SSIS catalog. Using the catalog to store and execute SSIS packages takes a lot of the manual work ......(more)

Fundamentals of Azure Data Factory - (click to enlarge) This past week I delivered Fundamentals of Azure Data Factory twice. Once for Brent Ozar Unlimited and once for a client – a private training delivery. The course introduces Azure Data Factory and covers: Azure Data Factory Essentials ......(more)

DevOps and Continuous Delivery (CI/CD)

The Seven Steps of the DevOps Lifecycle Process - A well implemented devops process should mean delivering better code, faster. But getting it right does mean investing in an appropriate process. Here’s a framework for seven steps to successful devops. ...(more)

Database Design, Theory and Development

Visualizing Merge Join Internals And Understanding Their Implications - This post is part 2 in a series about physical join operators (here’s part 1 on nested loops joins). Merge joins are theoretically the fastest* physical join operators available, however they require that data from both inputs is sorted: The base ......(more)

Data Privacy, Complianace, and GDPR

Washington, D.C., Suing Facebook Over Data Privacy Violations - DAILY VIDEO: Facebook faces disturbing new litigation over data privacy, and AT&T 5G services that use a 5G hotspot are now available in some U.S. cities. ...(more)

Data Mining/Data Analysis

Three Reasons Fast Databases Will Never Be Fast Enough - Click to learn more about author Mathias Golombek. If you look at the Data Management market (e.g. the Data Trends from the 451 Group), you are likely to be overwhelmed by the diversity of today’s technologies. Many decision makers think that this broad ......(more)

Conferences, Classes, and Events

New class: Immersion Event on Power BI, Power BI Report Server, and SSRS - No only has Tim been working heavily with Azure in 2018, he’s also been diving into the various reporting solutions that SQL Server has, and he’s ready to share his knowledge with the world in his new class on Power BI! It’s a two-day class called IEPowerBI: Immersion ......(more)

Computing in the Cloud (Azure, Google , AWS)

Azure Portal Enhancements – Switching Accounts - More and more I am impressed on the Azure Portal. Microsoft continues to make enhancements to the user interface (UI) and add in new features. One of these new features that I’ve recently become aware of is the ability to easily switch accounts. As ......(more)

Career Growth

4 Online SQL Server Help Resources for DBAs ('Accidental' and Otherwise) - There are several good online SQL Server options DBAs can turn to for advice, support and help. ...(more)

Translating your Current Skillset to a Modern Cloud Data Warehouse - If you are like many data professionals, you may have years of experience building and supporting on-premises data warehouse implementations, but have become overwhelmed by the blisteringly fast rate of change in the industry over the past few years. ...(more)

Backup and Recovery

SQL Server Backup To Azure Storage - Once again I would like to share with you an article I wrote about backing up (and restoring) a SQL Server database to Azure Blob Storage. In this article I write about some important concepts and show you the code … Continue reading ? ...(more)

Azure SQL Database

Azure SQL Database DTU Versus vCore - I wrote a guest article for an IT consultancy on Azure SQL Database, more specifically DTUs and vCore options. At a high level I discuss what both options are, the differences and what you should select. Hopefully you will find … Continue reading ? ...(more)

Administration of SQL Server

How to Proactively Gather SQL Server Indexes Fragmentation Information - Introduction to SQL Server Indexes Microsoft SQL Server is considered as one of the relational database management systems (RDBMS), in which the data is logically organized into rows and columns that are stored in data containers called tables. Physically, ......(more)

What is the Max Degree of Parallelism setting for SQL Server? - Max Degree of Parallelism is a setting that is often changed to improve performance in SQL Server. What exactly does Max Degree of Parallelism mean and do?Modern processors found in servers today are much more powerful and advanced compared to when ......(more)

SQL Server 2008 end of support - 2018 is about to end. In the case you didn't know or haven't heard, the extended support for SQL Server 2008 (and R2) reaches to an end in 2019.This beloved version (lets be honest, is like windows XP for the database world) was launched on 8/6/2008, ......(more)

Performance and Stability Related Fixes in Post-SQL Server 2014 SP3 Builds - As of December 12, 2018, there has been one Cumulative Update (CU) for the Service Pack 3 branch of SQL Server 2014. There were a relatively large number of hotfixes in this first cumulative update. If you are running on the SQL Server 2014 SP3 branch, ......(more)

Encrypting a Database Never Finishes - There is plenty of legislation and regulation in place these days that strongly suggest the encryption of data within a database. In SQL Server, we have the ability to comply with these regulations in a couple of different ways. We can choose to encrypt ......(more)

Installation of SQL Server using GUI - It is a second post on series related to installation and configuration of SQL Server installations. This series is for professionals who starts their journey with SQL Server administration and also for those who want to extend and structure their knowledge ......(more)

Query Store And Cruel Defaults - Gosh Darn Now, look, it’s no secret that I think Query Store is cool. Heck, I went and wrote sp_BlitzQueryStore so you can examine it similarly to how sp_BlitzCache looks at the plan cache. For a variety of reasons, though, we run into people who can’t ......(more)

SQL SERVER – FIX: Msg 15190 – There are still remote logins or linked logins for the server - One of the most successful offerings from me has been Comprehensive Database Performance Health Check. Sometimes during my assistance, some random issues appear which I try to solve as well. In a recent engagement, one of their developers asked a question ......(more)

SQL SERVER – Information Message: Cannot shrink file in database to xxxx pages as it only contains yyyy pages. - I often get emails from my blog readers asking various types of clarifications about my own blog. Here is one of the interesting questions from a reader about message Cannot shrink file in the database to xxxx pages as it only contains yyyy pages which ......(more)