The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

This past week I ran across a blog post that talks about the difference between database snapshots and database backups. There are certainly some similarities and some differences, as well as an overlap in the places that you might choose to use each of these technologies. Both might be useful as a way to recover from a bad code deployment, but both aren't necessarily helpful for a DR situation where the primary server has a catastrophic hardware failure.

It's often the case that we learn only a bit about some of the technologies in SQL Server. That's understandable as the platform has grown very complex, encompassing a vast array of technologies and options. There is often some overlap between them, and possibly different places where you might choose to use one or the other and solve a problem.

In plenty of cases, one technology will stand out, especially when you have gathered enough requirements to understand the entire situation. More information can help you narrow down your choices, and even make a decision, if you know both (or all) the technologies well.

If.

The key here is that as you look for solutions, you should be sure that you understand the technologies well. Dive deep into each of your choices and make an effort to determine the positives and negatives, the advantages of one over the other, as well as the limitations or holes that may be present. You can work often work around limitations, but you should be aware of what they are.

We can't learn everything today, or even everything that we need to know in a short period of time. However, when we are faced with a situation that has multiple solutions and unfamiliar technologies, we should ensure that we try to learn, ask questions, and do our best to understand the boundaries of the question and the technologies that might solve our problem.

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

Extending DevOps to the database: continuous integration - Join our webinar to learn how Redgate’s Database DevOps solution works to improve your database development and deployment processes. With a focus on continuous integration, we’ll show how Redgate tools plug into Git, Jenkins & Octopus Deploy to enable the build, test and deployment of your database changes. ...(more)

Virtualization and Containers

Creating SQL Server Containers for versions 2012-2017 - I am working on my dbatools and dbachecks presentations for SQL Saturday Finland, SQLDays, SQL Saturday Cork and SQLGrillen I want to show the two modules running against a number of SQL Versions so I have installed 2 Domain Controllers 2 SQL 2017 instances ......(more)

Pushing SQL Server images to the Azure Container Registry - The Azure Container Registry is an online repository for storing Docker images (think the Docker Hub). What’s cool about this is that we can store our images in the same data centre as our deployments, so spinning up containers from the images should ......(more)

Loopback available for Windows Containers - The April 2018 update for Windows brought a few cool things but the best one (imho) is that now we can now connect to Windows containers locally using ‘localhost’ and the port specified upon container runtime. Let’s have a look at how this works. First, ......(more)

Vendors/3rd Party Products

Welcome to Redgate tools for SQL Operations Studio - Built on top of Visual Studio Code, SQL Operations Studio is a free lightweight tool for modern database development and operations that runs on Windows, macOS and Linux. It’s been designed for developers without a deep knowledge of database administration, so that they can manage databases and maintain them....(more)

T-SQL

Optimization Thresholds – Grouping and Aggregating Data, Part 2 - This article is the second in a series about optimization thresholds related to grouping and aggregating data. In Part 1, I provided the reverse-engineered formula for the Stream Aggregate operator cost. I explained that this operator needs to consume ......(more)

Importing JSON Collections into SQL Server - It is fairly easy to Import JSON collections of documents into SQL Server if there is an underlying ‘explicit’ table schema available to them. If each of the documents have different schemas, then you have little chance. Fortunately, schema-less data ......(more)

Each session should be able to have its own temp table, but there can be problems. - You should be able to create a #temp in every session. That’s the idea, right? It’s one of the things that differentiates a global temp table from a local temp table. But there can be some difficulties with that. If you are working with reusable code ......(more)

Who comments code? - I am a firm advocate of commenting code, but you need to make sure that you are commenting the write thing. I worked on a project last year and then got taken off it. I’m now back on it full time, some 6 months later. I wrote most of the database ......(more)

Know About SQL Server Error 6401 - IntroductionThe ACID acronym standing for Atomicity, Consistency, Isolation, and Durability are the properties of SQL transactions. Transactions are used when the database has to be modified by using one or more SQL statements and to make sure the security ......(more)

Tools

Shortcut: Running SQL Server Management Studio as someone else - You don't always want to run SQL Server Management Studio (SSMS) as your current login for Windows. Now if all you want to do is to use a SQL Server login, then that's easy. When you connect to a server in Object Explorer, or when you start a new Database ......(more)

The May release of SQL Operations Studio is now available - This post is co-authored by Karl Burtram, Senior Software Engineer, SQL Server. We are excited to announce the May release of SQL Operations Studio is now available. Download SQL Operations Studio and review the Release Notes to get started. SQL Operations ......(more)

SSMS 17.7 is now available - This post is co-authored bySandy Winarko, Principal PM, SQL Server. We are excited to announce the release of SQL Server Management Studio (SSMS) 17.7! Download SSMS 17.7 and review the Release Notes to get started. SSMS 17.7 provides support for almost ......(more)

Shortcut: Adding multi-level undo, redo to the SSMS toolbar - Years ago, I had the privilege of presenting "what's new in SQL Server 2012" sessions at many locations around the world. When you present sessions, you can sometimes learn as much as you teach. What I learned while delivering those sessions is that ......(more)

Shortcut: Using script projects and solutions in SQL Server Management Studio - I'm puzzled that so few people use script projects and solutions when working with SQL Server Management Studio (SSMS). They are easy to use. Let's see an example: Instead of just starting to create scripts, from the File menu, click New, then Project. ...(more)

SQL Server Security and Auditing

How To Audit Data Changes In SQL Server? - Scenario:I was at a new client, with their previous and only DBA / DEVELOPER/ MASTER OF ALL of 8 years all suddenly gone with no documentation.Their business critical application was having data integrity issues. Some data were updated incorrectly while ......(more)

SQL Server Security

Learn More About SQL Server’s Always Encrypted, And More On This Friday Five - Get A List Of VSTS Users With APIs Jeff Bramwell is Vice President - Enterprise Architecture with Farm Credit Services of America. He has over 20 years of software development experience and has been working with .NET technologies since the early pre-release ......(more)

Software Development

What Are PowerApps and How to Get Started with Them - Do you keep hearing about this PowerApps thing but not really sure what it is? Did you play with it for five minutes but couldn’t really figure out what the heck to do with it? Or worse yet, did you try to build an app and got overwhelmed by all the ......(more)

Connect Node.js to SQL Azure with Tedious - The following shows how to create a simple Node.js project that reads and writes data to SQL Azure. To get a clean foundation for further development i decided to split things into multiple files (dataaccess, model and management). I also added a appdbtest.js ......(more)

Security news and thoughts

Airline Ticket Fraud - New research: "Leaving on a jet plane: the trade in fraudulently obtained airline tickets:" Abstract: Every day, hundreds of people fly on airline tickets that have been obtained fraudulently. This crime script analysis provides an overview of the trade ......(more)

Apple reportedly removing apps that share location data with third parties - Apple is reportedly enforcing some of its privacy rules more stringently for apps in its App Store. According to a report by 9to5Mac, the iPhone maker started removing apps that share location data with ......(more)

Reporting Services

R Chart into SSRS (Show Chart in SSRS)-Part 3 - In the last post, I have shown how you able to install an external package like ggplot2 in ML services and also you see how we can use data in SQL Server to create a chart in R studio. In this post, I am going to show you how to create a chart in SQL ......(more)

R Language

Six Sigma DMAIC Series in R – Part 1 - Welcome to Six Sigma DMAIC Series. I am launching this series to introduce one of most important problem-solving methodology. DMAIC is one of the most important tools in continuous improvement toolbox. It is closely associated with Six Sigma methodology, ......(more)

Product Reviews and Articles

Relaxing on the Tab Management Terraces with #SQLPrompt - I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I’ve written about a few of the other items on the map: Code Snippet Cove Trekking through Formatting Forest Caves of Code Analysis Sands ......(more)

Moving Birthdays with Data Masker - Data Masker for SQL Server is a fantastic product, and one that can reduce your attack surface area and exposure under the GDPR or any other regulation. There are plenty or rules and templates to help you anonymize data, in ways you might not have expected. ...(more)

Randomizing Names with Data Masker - Data Masker for SQL Server is pretty cool. It can reduce your attack surface area and exposure under the GDPR or any other regulation. There are plenty or rules and templates to help you anonymize data, in ways you might not have expected. Data Masker ......(more)

PowerShell

PowerShell quickie: space used per database by table objects - Sometimes the most useful scripts are the most straightforward: here's how you can return all your space used by your table objects on an entire SQL Server instance. ...(more)

PowerPivot/PowerQuery/PowerBI

Clear All Slicers in Power BI; A Bookmark Story - Using bookmarks for clearing all slicers in Power BI is not a a new function, I have been using it for many months, and advising many people to do it that way. However, I still get a lot of questions in my presentations about how to do that. That is ......(more)

Power BI – Part 1: Introduction - Microsoft’s Power BI is not just a cloud service. It’s a suite of integrated business intelligence (BI) tools for accessing and consolidating data and then presenting it as actionable insights. If ......(more)

How To Tell Whether Query Folding Is Taking Place When Importing Data From Analysis Services In Power BI And Excel Power Query - As a quick follow-on from last week’s post on how to detect whether query folding is taking place when importing from OData data sources, if you’re importing data from Analysis Services you have a similar problem: how do you know whether query folding ......(more)

Power BI Custom Visuals Class (Module 102 – Slim Data Bar KPI Visual) - In this module you will learn how to use the Slim Data Bar KPI Visual. The Slim Data Bar KPI Visual is great for showing the progress towards a target while taking up the smallest amount of report space. Module 102 – Slim Data Bar KPI Visual Downloads Power ......(more)

Querying Data in Azure Data Lake Store with Power BI - Let's say you have data in Azure Data Lake Store (ADLS) that you want to report directly from in Power BI. You might be doing this for early exploratory data efforts, or you might have some curated data which has been prepared in the data lake for analysis ......(more)

Removing column filter in Power BI when Sort by Column is active - A recurring issue for Power BI users writing DAX code is that column filter removal might have an unexpected behavior when the Sort by Column is active on the column. I covered this topic in an article related to RANKX, but it’s not easy to find this ......(more)

Common Data Service for Analytics (CDS-A) and Power BI – an Introduction - In this video, Matthew Roche joins Adam for a look at the Common Data Service for Analytics. This is an offering that will be coming out in public preview in the Summer of 2018 and will help you to bring together data sources into a common schema. Video: ......(more)

Performance Tuning SQL Server

DMV To List Foreign Keys With No Index - I was entrusted with a new database for a critical application that was having general performance issue and the CPU usage constantly over 70%.It turned out that I did not have to sweat much to quickly and significantly improve the database performance ......(more)

Query Store Examples: Stories from customers - In the past week I’ve used Query Store to troubleshoot performance issues for two different customers running SQL Server 2016 and higher. I’ve presented a lot of sessions about what Query Store is, how to configure it, and basic use cases, but we really ......(more)

When Query Plans Lie Part 2 - Getting Weirder In Part 1, we looked at how query plans can tell us little lies. Now we’re going to look at how those little lies can turn into bigger lies. Adding An Index Right now, this is our query:SELECT u.UpVotes, u.DownVotes FROM dbo.Users AS ......(more)

SQL Server – Performance Comparison of Function Trim and LTRIM(RTRIM) - While delivering SQL Server Performance Tuning Practical Workshop I recently received a question which is related to performance comparison of Function Trim and LTRIM/RTRIM. Regarding the trim function, I have previously blogged in the following blog ......(more)

When Query Plans Lie Part 1 - Freaking Out Brent Is a full time job. Some days I can get him to hit the desk drawer tequila, and other days I crash and burn. This day was so monumental I almost hired someone to do an action movie narration of this blog post. Let’s Start Easy Over ......(more)

Which are the queries using a particular index or table? - The DMV sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats give you excellent detailed information about how the indexes are being used and what operations are occurring (select, insert, update, delete) in the database. But they don’t ......(more)

When Simple Parameterization…Isn’t - I’m desperately working to finish up a new version of my book on Execution Plans. We’re close, so close. However, you do hit snags. Here’s one. My editor decided to change one of my queries. I used a local variable so that I got one set of behaviors. ...(more)

Fake News about Auto Parameterization/Simple Parameterization - I saw a question on Twitter today that took me down a little rabbit hole. And when I go down rabbit holes, I like to blog about it! There’s a TLDR at the bottom of this post if you’re short on time. Here’s the question: Simple parameterization leads ......(more)

MDX/DAX

DAX Formatter now uses a different color for variables names - In the latest weeks I received several requests to support a different color for variable names in DAX Formatter, just as the formula editor available in Power BI. I finally implemented a solution that I consider a workaround, because of some limitations ......(more)

Hardware News

Latest Windows 10 Version Incompatible With Intel SSD 600p - Microsoft has advised that the most recent version of Windows 10—version 1803, codenamed Redstone 4—is currently incompatible with the Intel SSD 600p and the related Intel SSD Pro 6000p. Windows 10 version 1803 can crash when attempting to update Windows ......(more)

HA/DR/Always On/Clustering

Availability Groups Bug with Indexed Views - The short story: if you drop an index on an indexed view, queries on the Always On Availability Groups replicas that point to that indexed view will cause memory dumps. The long story: to reproduce – AND DO NOT DO THIS IN PRODUCTION – on the primary, ......(more)

DevOps and Continuous Delivery (CI/CD)

Capgemini Survey Finds Enterprises Reaping Profits From IT Automation - Adoption of IT automation is dividing businesses today into the haves and have nots, with the latter missing out on improved agility, product delivery, customer experiences – and profits. ...(more)

Please Help Make the Science of DevOps Even Better! - DevOps Research and Assessment (DORA) is now calling for participation in the 2018 Accelerate State of DevOps Survey. Please click the link to join. We all benefit from having scientifically reviewed data to substantiate the impact of DevOps practices. ...(more)

How to deploy data for the lookup tables, small dimensions and other meta-data with your database code - Deployment process equally responsible for deploying both database objects as well as an initial state of the database/applications. In this post, I will talk about different options on how to bring your own meta-data with the deployment ...(more)

Release Gates – Enable Progressive Exposure and Phased Deployments - We are excited to announce that release gates are now generally available to all VSTS users and accounts so everyone can now add progressive exposure to their continuous delivery pipelines. What are release gates If you haven’t tried them yet, ?Release ......(more)

Database Design, Theory and Development

Converting a Database to In-Memory OLTP - Microsoft In-Memory OLTP was introduced with SQL Server 2014. In the pre-release mode, I saw quite a few videos that demonstrate the strength of this new technology. When the final release came to light, I was very disappointed. First, this technology ......(more)

Meaning Criteria and Entity Supertype-Subtypes - Note: This is a re-write of an earlier post to bring it in line with the McGoveran formalization and interpretation[1] of Codd's RDM. "I have a database for a school ... [with] numerous tables obviously, but consider these:CONTACT - all contacts (students, ......(more)

Data Privacy and GDPR

GDPR – What do we need to know? - On 25th May GDPR comes into force. I’ve been learning everything I can about GDPR to ensure my systems adhere to the regulations and will be attending Redgate’s SQL Privacy summit, details and registration here on Friday 18th May in London . However ......(more)

Data Mining/Data Analysis

Software Usage Data and What to Do with It - Click to learn more about author Victor DeMarines. April 13 marks the day of one of the greatest success stories I can think of that began with the unthinkable. The crew of Apollo 13 was completing a routine check when, a day shy of walking on the moon, ......(more)

Conferences, Classes, and Events

PASS Summit 2018 Pre-con: Digital Storytelling with Power BI - Mitchell Pearson, Brian Knight and myself will be presenting a full day pre-con as PASS Summit on Digital Storytelling with Power BI. You can learn more about it here: http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=80300 ...(more)

First Wave of Sessions for PASSSummit Now Available - The first wave of sessions for the #PASSSummit have been released. You can see the list at: http://www.pass.org/summit/2018/Learn/ConferenceSessions.aspx I am honored to have a session on the list. This year, I’ll be doing a session called Minimizing ......(more)

Enter The Tidyverse, Columbus Edition - In conjunction with SQL Saturday Columbus, I am giving a full-day training session entitled Enter the Tidyverse: R for the Data Professional on Friday, July 27th. This is a training that I did earlier in the year in Madison, Wisconsin, and aside from ......(more)

Watch Microsoft’s opening Build keynote here - SEATTLE—It's day one of Microsoft's annual Build developer conference, and everything kicks off at 08:30am PDT/11:30am EDT with the opening keynote. We're expecting to hear a lot about machine learning/artificial intelligence, the "intelligent edge," ......(more)

Computing in the Cloud (Azure, Google , AWS)

Oracle Extends Capabilities of Autonomous Database in the Cloud - DAILY VIDEO: Oracle expands its AI-driven autonomous cloud database offerings, and Nadella says Microsoft is building Azure cloud as "the world's computer." ...(more)

Extended Events – Using Azure Storage - Last year I wrote about Azure SQL Database extended events (https://blobeater.blog/2017/02/06/using-extended-events-in-azure/) and gave an example where I was capturing deadlocks via the ring buffer. Ever since then I wanted to do a follow-up post but ......(more)

Azure DB, ROUTE ADD, and VPNs - One of the things that I have to content with every now and then with Azure SQL is accessing our Azure SQL databases through the corporate VPN. Our servers are set up to only allow access from within the company office. We have a VPN set up to let me ......(more)

Where can I find the latest Azure training? - We have many free Azure training offerings available to get started learning Azure. Here are several ones I recommend to customers: Where can I see a quick overview of Azure’s capabilities? For getting a quick overview of Azure and its capabilities I ......(more)

An Introduction to Soft Delete for Azure Storage Blobs - On the most basic level, you can think of soft delete as being similar to the Windows recycle bin. ...(more)

Career Growth

T-SQL Tuesday #102: Giving Back - Last month we had the opportunity to discuss some of the most important tools for a data professional. I took that opportunity to discuss how it is important to blog. As it turns out, that article correlates fairly strongly to today’s article. This are ......(more)

Contributing to Community - This post is a response to this month’s T-SQL Tuesday #102 prompt by Riley Major. T-SQL Tuesday is a way for SQL Server users to share ideas about different database and professional topics every month. The prompt I’ve chosen to write about this month ......(more)

TSQL Tuesday #102: Giving Back - This TSQL Tuesday comes to us from Riley Major (b | t), He asks us in what ways we can give back to the SQL Community. He suggests blogging, speaking, answering questions, mentoring, or if we have done some of those already, to discuss what motivated ......(more)

T-SQL Tuesday: Giving back to the community - CoderGirl program for LaunchCode This month’s T-SQL Tuesday from Riley Major (b|t) asks us to figure out a way to give back to the community and then write about it. I’m going to go with the option to write about how and why I got started since I am ......(more)

Azure SQL Managed Instance

CPU and Memory Allocation on Azure SQL Database Managed Instance - Reviewed By: Ajay Kalhan, Borko Novakovic, Drazen Sumic, Branislav Uzelac In the current Azure SQL Database Managed Instance (MI) preview, when customers create a new instance, they can allocate a certain number of CPU vCores and a certain amount of ......(more)

Azure SQL DB Managed Instance – sp_readmierrorlog - Reviewed by: Kun Cheng, Borko Novakovic, Arvind Shyamsundar, Mike Weiner Azure SQL Database Managed Instance is a new offering that provides an instance-based SQL PaaS service in Azure. If you are not yet familiar with this new Azure SQL Database capability, ......(more)

Azure SQL Database

Database scoped default online options is in preview for Azure SQL DB - This post is authored byJosh Gnanayutham, Program Manager, Azure SQL Database. We are happy to announce that database scoped default options for online and resumable are now available for public preview. With this feature, you can set a default behavior ......(more)

Export Azure SQL DB Error: “Missing the required ‘administratorLogin’ parameters for ImportExport operation.” - Few days ago, I have been working on an issue that customer trying to Export Azure SQL Database using Azure Portal, facing this Error Message: ErrorCode: 400 ErrorMessage: Missing the required 'administratorLogin' parameters for ImportExport operation. Symptom: Customer ......(more)

Azure SQL Data Warehouse and Data Lake

Azure Content Spotlight – Azure Data Factory - Welcome to another Azure Content Spotlight! These articles are used to highlight items in Azure that could be more visible to the Azure community. Modern solutions often require an aggregation of many distributed data sources and often requires manipulation ......(more)

Azure CosmosDB

Azure Cosmos DB Pricing Compared to DynamoDB and NeptuneDB - This week at the Microsoft Build conference a new provisioning option for Cosmos DB was announced. The new option, to provision throughput for a set of containers, is a wonderful new feature. However, this meant I needed to take some time to understand ......(more)

AI/Machine Learning/Cognitive Services

Machine learning: A quick and simple definition - Get a basic overview of machine learning and then go deeper with recommended resources.The following overview covers some of the basics of machine learning (ML): what it is, how it works, and what you need to keep in mind before taking advantage of it. This ......(more)

Open-Source Machine Learning in Azure - The topic for my talk at the Microsoft Build conference yesterday was "Migrating Existing Open Source Machine Learning to Azure". The idea behind the talk was to show how you can take the open-source tools and workflows you already use for machine learning ......(more)

Google Flexes AI Muscle, While Chasing Rivals at I/O Conference - Google introduced new products and technology upgrades at its annual developer conference that showed the internet giant lagging behind Apple Inc. and Amazon.com Inc. in some areas while driving home its superiority in artificial intelligence. ...(more)

Administration of SQL Server

SQL SERVER – How to Find Free Log Space in SQL Server? - One of the most popular question on this blog is about how to shrink the log file. I have previously written about that subject on the following blogs. However, today we are not going to talk about Shrinking the log file but rather going to talk about ......(more)

SQL SERVER – Why Suddenly DBCC CHECKDB Running Very Slow? - Recently during Comprehensive Database Performance Health Check, I was asked a fascinating question. They wanted to know why suddenly their DBCC CHECKDB process started to run very very slow. My customer who has no full-time DBAs in their organization. ...(more)

Using DATABASEPROPERTYEX to Find Last Good DBCC CHECKDB Time - For decades, a pain point for SQL Server administrators has been determining when the last known DBCC CHECKDB was run against a database. Microsoft has not exposed this information in an easily digestible format. You can find a handful of options available ......(more)

SQL Server: Database snapshots vs Database backups - In the article, we’ll walk through the concepts to understand database snapshots, and their benefits and limitations. This article will help you decide when to use a database snapshot, and when to use a backup. In some cases, the database relying on ......(more)