The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

A few years ago I wrote a piece about the growth of storage in many devices and the large quantities of data that can be kept. I speculated that we might see terabyte mobile phone storage in a decade. That was 2014 and this past week Samsung announced they had created a 1TB embedded Universal Flash Storage chip for phones. You can already get a Note9 with 512GB of storage, so this isn't a huge leap, but it does feel like a milestone.

For many of us, that seems like a good move. We can keep all the photos, videos, movies, and more that we want on our phones. I take a lot of pictures in life, and lately I've struggled with a 32GB phone. I keep debating upgrading, just because I'd like more storage. There are certainly challenges with keeping all this imagery and video backed up, but that's a separate discussion. I'd like to just be able to capture data and make decisions about how to protect it later. There are also plenty of transient items (movies, Pluralsight courses, and more) that I just want to keep on temporary storage for a limited time.

More is better, but it also creates potential problems. With a 1TB, or potentially larger, storage on my phone, what about the problem of data loss and theft by insiders? Very few of us work in jobs where we can't keep our mobile device with us. Many of us also work with sensitive data, and there is a potential for transferring a lot of data to a relatively innocuous device. These days we might not even need to plug in our mobiles with bluetooth file transfer programs. Even if we plug in a phone, that's innocent and expected? Modern smartphones often need charging during the day.

It's not just us, but also contractors, consultants, and non-privileged users that might be able to move data. Certainly someone might act maliciously, but what about the potential for new types of malware? Android is more open and iOS, and there have been viruses. How long before there are some virus programs that try to connect to every SQL Server, Oracle, MySQL, etc. database when plugged into your laptop?

That might seem far fetched, and certainly our databases grow larger and larger, but much of the data is text, and more ERP/CRM programs are in use that contain known schemas where worms, virii, and other malware might target specific tables. A terabyte can store a lot of text data, especially if it's usernames and password hashes.

I don't know of any mobile to PC infections yet, but I do know there are lots of smart people out there. It's just a matter of time before someone starts to try and exploit the capabilities of modern mobile devices, especially those with large storage capacities.

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

The importance of monitoring your Azure SQL Database - Monitoring Azure SQL Database is still a necessary part of understanding how your system is behaving and ensures that you have the information needed to make necessary decisions about your databases in a timely and accurate manner. ...(more)

The 2019 State of Database DevOps results, live with Donovan Brown! - Donovan Brown, Principal DevOps Manager at Microsoft, joins Steve Jones, Microsoft Data Platform MVP to discuss the latest in all things Database DevOps. They offer a closer look at the key findings in the 2019 State of Database DevOps Report, and investigate the growing importance of the database in successful DevOps and IT performance....(more)

Why the database is at the heart of DevOps success - DORA’s recognition of the critical importance of the database to DevOps in their Accelerate State of DevOps Report should act as a timely wake-up call to those companies that still see DBAs and developers as operating in separate silos. Join Microsoft Data Platform MVP and SQL Server Central Editor Steve Jones to discover how you can build a common understanding and atmosphere of collaboration....(more)

Virtualization and Containers

Azure Kubernetes Service Error – LoadBalancer type service external IP stays pending - Last week I was working on my Azure Kubernetes Service cluster when I ran into a rather odd issue. I’d created a service with a type of LoadBalancer in order to get an external IP to connect to SQL Server running in a pod from my local machine. I’ve ......(more)

Vendors/3rd Party Products

Create, Protect and manage non-production databases with SQL Provision - SQL Provision allows teams to create a secure, scalable and repeatable process for managing data as it moves through your SQL Server estate, without causing administrative burdens. Tony Davis explains how SQL Provision can work alongside your existing database DevOps practices. ...(more)

SQL Provision and Azure SQL Database: Creating Local Development and Test Databases - Your organization, like many others, is probably moving towards use of cloud-hosted database platforms, such as Azure SQL Database. Here Chris Unwin describes how you can create a local copy of your database from a BACPAC of your Azure SQL Database production database, and then use SQL Provision to deploy as many development and test copies (clones) of that database as you need. If your database contains any non-public data, you can also use the Data Masker component of SQL provision to ensure this data is obfuscated, before deploying the clones....(more)

T-SQL

SQL Server CRUD Operations – Part 1 - In the database programming, there are four basic and essential operations: create, read, update, and delete. These operations can be defined as the first step in database programming and are called CRUD operations. CRUD is an acronym of the CREATE, ......(more)

How Does DBCC CHECKIDENT Really Work When Resetting the Identity Seed (RESEED)? - (last updated: 2019-01-31 @ 14:47 EST / 2019-01-31 @ 19:47 UTC ) Today’s “Question of the Day” on SQL Server Central, Cleaning up the Identity, is about using DBCC CHECKIDENT to reset the seed value of an IDENTITY column to a specific starting value. ...(more)

Script to delete a login and associated users from SQL Server - Occasionally, you might need to drop a single login from a SQL Server, however if that login has users in a lot of databases, it may be tedious to delete the associated user account from... ...(more)

DAX Studio 2.8.1 released - Originally posted on: http://blogs.shaunxu.me/archive/2019/01/23/dax-studio-2.8.1-released.aspxSo thanks to a couple of early adopters we’ve found and hopefully squashed a few bugs that managed to sneak into the 2.8.0 release and the 2.8.1 release is ......(more)

Dealing with NULLs in SQL Server - What is NULL? NULL or the NULL marker is the way we represent an unknown value in SQL, by SQL I am referring to the standard Structured Query Language, not MS SQL Server. The last sentence takes us a little back to 1969 when the standard was first defined ......(more)

Introduction to SQL Server Identity - In this article, we will explore the basics and details of the SQL Server IDENTITY property and the IDENTITY column features. Also, we will consider how to insert explicit values to the identity columns through the IDENTITY_INSERT feature. Introduction ......(more)

How Not to Perform a Difficult Update in SQL Server/Azure - I learned a long time ago, that the quickest way to do something was to not do it at all. A friend of mine asked me if I could review an update statement that was confounding their group looking for ways to optimize it. The table has, just short ......(more)

Tools

PolyBase Revealed + Azure Data Studio - As I work on PolyBase Revealed, I made a conscious decision to prefer Azure Data Studio for my demos over SQL Server Management Studio. Because I don’t plan to go into much detail on why in the book (after all, the book is about PolyBase, not SQL Server ......(more)

Tech News

Terabyte smartphones are coming, thanks to new Samsung storage chip - Enlarge (credit: Samsung) Smartphones hold most of our lives now in photos, videos, music, and more, and Samsung's latest tech will make it so they can store even more. The Korean manufacturer announced that it has created the first 1TB embedded Universal ......(more)

SQL Server on Linux

Changing the SSH port for a RHEL Azure VM - I’m working with Red Hat Enterprise Linux (RHEL) VMs on Azure and one of the best practices that Microsoft recommends is to change the default SSH port from 22 to something else. I was looking for a consolidated set of instructions to do this and I couldn’t ......(more)

How to Linux for SQL Server DBAs — Part 3 - DBAs running SQL Server on Linux will not only need to understand SQL Server security well, they will also need to understand how security works on Linux. In this article, Kellyn Pot'Vin-Gorman walks you through several examples, explaining the permissions and ownership of files and directories in Linux....(more)

Security news and thoughts

Security Analysis of the LIFX Smart Light Bulb - The security is terrible: In a very short limited amount of time, three vulnerabilities have been discovered: Wifi credentials of the user have been recovered (stored in plaintext into the flash memory). No security settings. The device is completely ......(more)

FaceTime bug lets callers hear you before you answer (really) - Users have discovered a bug in Apple's FaceTime video-calling application that allows you to hear audio from a person you're calling before they accept the call—a ......(more)

R Language

Tutorial: Sequential Pattern Mining in R for Business Recommendations - by Allison Koenecke, Data Scientist, AI & Research Group, Microsoft In this tutorial, Allison Koenecke demonstrates how Microsoft could recommend to customers the next set of services they should acquire as they expand their use of the Azure Cloud, by ......(more)

Starting with R: Introduction to Vectors - CategoriesIntroductionTagsMatricesVectorsA vector is a workhorse datatype of R and basic data structure in R. It contains elements of a homogenous data type. The data types can be logical, integer, double, character, complex or raw. Creating a vector ......(more)

Python in SQL Server

Azure Notebooks – a nice little tool. - Gday, I’ve been playing with Azure notebooks lately and have found them invaluable as A teaching aidA test sandbox I’ve been using Python code, and this means that I don’t have to install Python on the machine that I am working at. It also means that ......(more)

Product Upgrades and Releases

Released: SSRS2017+ Management Pack with Power BI Reporting Server Support (7.0.12.0) - We are happy to announce that an update to SQL Server Reporting Services 2017+ Management Pack is released. This update introduces Power BI Report Server monitoring capabilities to the existing SSRS MP. It considers Power BI Report Server as a special ......(more)

Cumulative Update #5 for SQL Server 2016 SP2 - The 5th cumulative update release for SQL Server 2016 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 #13 for SQL Server 2016 SP1 - The 13th cumulative update release for SQL Server 2016 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)

Product Reviews and Articles

Monitoring Azure SQL Database with SQL Monitor - SQL Monitor provides a full suite of monitoring specific to Azure SQL Database and SQL Elastic Pools. You can therefore use it to monitor all your databases, regardless of whether they are on local physical servers or virtual machines, or in the Azure ......(more)

Pro SQL Server Linux–Connecting and Building an App - As part of my learning goals for 2018, I wanted to work through various books. This is part of my series on Pro SQL Server on Linux from Bob Ward. I set up my Red Hat server as a command line only instance, so installing VSCode or Azure Data Studio aren’t ......(more)

PowerShell

Using a Server List to Control PowerShell Scripts - As I started to use PowerShell more and more, one of the reasons I found it useful, as have many DBAs, is that it permits running the same script across multiple servers. The scripts in my last article took advantage of this capability. One of the reasons ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI Slicers vs Filters – which will you choose? - Adam picks up on David Eldersveld's blog about Power BI slicers versus filters and looks at if slicers are even needed now. The filter pane can satisfy a majority of scenarios. There are a few that it can't. Which Power BI feature will you choose? David ......(more)

DAX error messages in Power BI - This article describes the real meaning of and the required action for a few common error messages that can appear when users define DAX expressions in Power BI. A DAX expression containing syntax errors stops the execution of the calculation. These ......(more)

Can I add a custom reference layer to ArcGIS and use it in Power BI? - (2019-Jan-28) When you work with maps using ArcGIS visual in Power BI, you always have a feeling that it is a tool within another tool. On a surface level, you have options to set data attributes for geo coordinates, coloring and time controls. However, ......(more)

Getting Started With Dataflow in Power BI – Part 2 of Dataflow Series - In the previous article, I mentioned what is the Dataflow, how it can be helpful, and mentioned some of the use cases of it in real-world Power BI implementation. In this article as the second part of the series for the Dataflow, I like to give you some ......(more)

Power BI Embed Token Using PowerShell - In this video, Adam shows an easier approach to get a Power BI Embed Token using PowerShell. This removes the headache of writing code or having to worry about an authentication token. Power BI Embedded Playground Invoke-PowerBIRestMethod Documentation ......(more)

Copy Measures Between 2 Power BI Files - I have often been asked (and wondered myself) if it were possible to move all the measures from one Power BI PBIX file into another. Imagine you have 2 PBIX files with similar data models (similar enough that the measures will be substantially the same). ......(more)

Performance Tuning SQL Server

Compatibility Levels and Cardinality Estimation Primer - Introduction Between 1998 and early 2014, SQL Server used one cardinality estimator (CE), but would introduce a new database compatibility level with every new major version of SQL Server, (with the exception of SQL Server 2008 R2). The native compatibility ......(more)

Cardinality: Not Just For The Birds - Watch this week’s episode on YouTube. When building indexes for your queries, the order of your index key columns matters. SQL Server can make the most effective use of an index if the data in that index is stored in the same order as what your query ......(more)

Query tuning: The IN clause - This post will cover the IN clause and another way to rewrite the same logic. I don’t intend to say that IN is better or worse than other T-SQL, I just want to showcase what it does. We’ll jump right into the demos. I’m using StackOverflow2010 for this, ......(more)

Index Sortables - Yelling and Screaming If you saw my post about parameterized TOPs, one thing you may have immediately hated is the index I created. And rightfully so — it was a terrible index for reasons we’ll discuss. If that index made you mad, congratulations, you’re ......(more)

Disabling Indexes - Indexes are probably the number one tool we have to improve performance. That said, there are times when we want to put that index on hold. While indexes dramatically improve read performance they do cause a slight dip in write performance. This isn’t ......(more)

Why is my query suddenly slow? - Sometimes query performance problems sneak into SQL Server after a software release that involves database schema or code changes, and you must hunt down the offending query and tune it. Other times, however, the performance changes for less immediately ......(more)

Parameters In TOP Are Sniffed, Too - Thank You For Your Business In yesterday’s post, we looked at how row goals affected performance when data matching our join and where clauses didn’t exist. Today we’re going to look at something similar, and perhaps a workaround to avoid the same issues. Here’s ......(more)

Predicate Selectivity and Index Design - Loose Ends A while back I promised I’d write about what allows SQL Server to perform two seeks rather than a seek with a residual predicate. More recently, a post touched a bit on predicate selectivity in index design, and how missing index requests ......(more)

Microsoft News

Microsoft Expands Azure PostgreSQL Moxy Through Citus Data Purchase - Microsoft's purchase of Citus Data is about more than just the software. It's also about talent. ...(more)

Microsoft cloud services see global authentication outage - Office 365, Dynamics 365, Azure Government Cloud impacted by authentication issue. ...(more)

MDX/DAX

Detect missing parameters in DAX queries - Yesterday I answered to a question related to parameters in DAX that could be useful to other readers who use DAX in Reporting Services (SSRS): how to manage missing parameters of a DAX query? I wrote an article in the past describing how to use parameters ......(more)

Hardware News

CES 2019: New Kingston KC2000 NVMe SSDs for High Performance - At this year’s CES, Kingston demonstrated its first SSDs powered by Silicon Motion’s controllers. The new KC2000-series drives are aimed at high-end client systems. ...(more)

Intel's 10nm Cannon Lake and Core i3-8121U Deep Dive Review - Anyone interested in leading edge semiconductors knows that Intel is late with its newest manufacturing process. The '10nm' node was first announced in 2014, to be released in 2016. While officially 'shipping for revenue' by 31 December 2017, the only ......(more)

HA/DR/Always On/Clustering

How To Check How Far Behind Your SQL Server Log Shipping Secondary Is - Log shipping is one of the simplest and most bulletproof methods to get SQL Server to replicate data to a different server/location. For the most part, you set it up and don’t need to touch it again, it just works. Out of the box the agent jobs SQL Server ......(more)

SQL SERVER – SQL Clustered Resource in Online Pending State for Long Time Before Coming Online - While doing Comprehensive Database Performance Health Check I always ask my client if there is any pain point which they have with the current state of the database/server. Once I got an interesting question which I am going to answer in this blog post ......(more)

DevOps and Continuous Delivery (CI/CD)

DevOps Tutorial: How to Put DevOps Theory into Practice - This DevOps tutorial will help organizations translate DevOps principles into actual on-the-ground practices. ...(more)

What is the purpose of your organization? A lesson in DevOps - I’ve been speaking a lot on the topic of DevOps lately, and there’s a question I like to ask. What is the purpose of your organization? I get all kinds of answers. “We make software that delights our customers and makes their jobs easier.” “Our company ......(more)

The Database DevOps Challenges SQL Provision Solves - SQL Provision helps to accelerate the delivery of database updates by enabling an organization to provide database copies, and the right data, to all parts of the deployment pipeline that need it, with a light footprint, and securely. Tony Davis explains how....(more)

Database Design, Theory and Development

What is Trusted Constraint in SQL Server? – Interview Question of the Week #210 - Question: What is Trusted Constraint in SQL Server? Answer: I received this question during my recent SQL Server Performance Tuning Practical Workshop. I think it is a very interesting concept and I have seen many experts also do not know about this ......(more)

Data Warehousing

Data Warehousing Tip #6 – Use a consistent unknown member key - In this article I’ll be discussing the benefits of using a consistent unknown member key across your data warehouse. And I’ll show you a couple of ways of going about implementing a consistent approach. Each of your dimensions should have an unknown ......(more)

Data Privacy, Complianace, and GDPR

Introducing new features in Microsoft 365 to help prepare for the next wave of privacy regulations - New Microsoft 365 features help organizations deliver on privacy and compliance commitments in a simple, integrated, and intelligent way. The post Introducing new features in Microsoft 365 to help prepare for the next wave of privacy regulations appeared ......(more)

Data Mining/Data Analysis

The Benefits of Master Data - Click to learn more about video blogger Scott Taylor. The Data Rants video blog series continues with host Scott Taylor “The Data Whisperer.” The series covers some of the most prominent questions in Data Management such as Master Data, the difference ......(more)

Conferences, Classes, and Events

New Fundamentals of Index Tuning Course - You’re a developer who needs to make your application go faster, and you don’t want to (or can’t!) change the queries. You’ve never been to an official training class, and your manager isn’t about to spring for travel. In my new Fundamentals of Index ......(more)

How to Think Like the Engine – 2019 Edition - You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this series of videos, you’ll learn how the SQL Server engine looks ......(more)

Computing in the Cloud (Azure, Google , AWS)

Storage options for SQL Server database files in Azure - If you are using SQL Server in an Azure VM (IaaS) you have a number of options of where to store the database files (.mdf, .ldf, and .ndf). Most customers use managed disks, available in a number of offerings: Standard HDD, Standard SSD, Premium SSD, ......(more)

Backup and Recovery

Log Backup Danger, Will Robinson! - For databases in Full Recovery Model, log backups should be taken at a frequency that ensures your recovery point objective can be met. So you schedule log backups to occur, setup email notifications so you... ...(more)

Azure SQL Managed Instance

Azure SQL Managed Instance – full blown SQL Server in the cloud….? - This blog post is one that I have had percolating in the background since around November 2018. My good mate John Martin ( t ) was speaking at PASS Summit on Azure SQL Managed Instances and we had talked about use cases, some of the gotchas and things ......(more)

How to Migrate Azure SQL Database to Azure SQL Managed Instance - The latest version of SQLPackage supports migration to Azure SQL Managed Instance (MI) from multiple SQL sources such as Azure SQL Database. Following the below steps, you can export a SQL database using the SQLPackage command-line utility. If possible, ......(more)

Azure SQL Database

Improve the Performance of Your Azure SQL Database (and Save Money!) with Automatic Tuning - Azure can create sticker shock for some companies as they venture into the cloud. As a database administrator, it is your job to fine-tune your environment to mitigate those costs the best you can. Azure SQL Database Purchasing Models Microsoft gives ......(more)

AI/Machine Learning/Cognitive Services

Yes, “algorithms” can be biased. Here’s why - Enlarge / Seriously, it's enough to make researchers cry. (credit: Getty | Peter M Fisher) Dr. Steve Bellovin is professor of computer science at Columbia University, where he researches "networks, security, and why the two don't get along." He is the ......(more)

Administration of SQL Server

10 Database Tasks That Sound Easy, But Aren’t - All of these are doable with scripts, tools, and/or elbow grease, mind you – they’re just not as easy as they sound at first glance until you know about the existence of those scripts, tools, and/or free people waiting around with spare elbow grease. “Go ......(more)

Inspect server and session User Options in effect - SQL Server can be configured to provide certain behavior to client sessions, via the SET OPTIONS= command, or via the sys.sp_configure 'user options' system stored procedure. The SET OPTIONS= command only affects the current session,... ...(more)

How to Encrypt and Restore Your SQL Server Database Backups - We’ve had backup encryption out of the box since SQL Server 2014, yet I’ve rarely seen it used. In an age where we’re moving more and more things to the cloud including those backup files, backup encryption is becoming more and more necessary. Sure we ......(more)

Defaults In msdb Database - Today is a day to discuss defaults. It started with the day being TSQL Tuesday and having a topic of “Say No to Defaults.” You can read more about that from the invite – here. I already participated in the party but did also want to discuss defaults ......(more)

Important Query Store Fixes – January 2019 - The latest CUs for SQL Server 2016 and 2017 contain some important Query Store fixes that I thought worth mentioning for those of you on either version or those of you looking to upgrade. As of this writing, the current CU for SQL Server 2016 SP2 is ......(more)