The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

There was a report that Spotify is writing lots of junk data to users' local drives. Some people have noted GB a day or writes, even when they aren't using Spotify for listening to music. Some debugging by users show that a local database (SQLite) is getting a number of maintenance calls to the database that are being repeated over and over. This appears to be a bug, and one that is hopefully being fixed. I know it hasn't hit me, so perhaps there are more factors in play than just using Spotify.

This does bring to mind a few things to me that we should be careful of as software and data professionals. The first is that while bugs will creep into our software, being able to deliver fixes is important. I've seen updates made to Spotify in a smooth fashion, one that doesn't seem to require much effort from me. While I don't usually get to decide when these updates will apply, and they can be annoying if I reboot and attempt to use the application, this isn't a critical piece of software for me. For some of us, though, our users can be impacted if we don't allow them the chance to choose when updates occur, or at least, when the final time is when the old version might no longer be supported (or stop working).

The bigger issue, to me, is that the local database is being used in a way that users don't expect. There's likely a bug here, causing lots of additional activity, but what's a normal level of activity? What would our users expect? Do they even know that we're keeping data locally on their system? In many cases they might not care, or even assume we are, but we should disclose this to our users. Security concerns over data leakage or loss may be an issue in some environments, and it's important that we ensure our users are aware, or at least they can find, information about the data our software may collect, store, and use.

Data becomes more important all the time, and we are constantly finding new issues surrounding it's capture, storage, security, and use. The way we even think about data is somewhat immature. Data ownership, protection, lifetime, and more are concerns. The problem is that many of us would struggle to articulate how exactly we want to handle the data that is related to our lives. In fact, we may have vastly different ideas on how different types of data are handled. Perhaps we don't care about our musical listening habits, but we care deeply about someone learning about our reading history.

If you develop software that uses a database, and many of you do, it would be nice to document (maybe self document with code) the ways in which data is stored and managed. However, if you're systems are like mine, there might not even be any policies on how data is managed and handled over time. That's something I hope changes in my lifetime and we start to treat data like a very valuable asset that can impact our lives.

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.

SQL Server Security and Auditing

Is it safe to grant Administer Bulk Operations? - First I guess we had best start with definitions right? The permission Administer Bulk Operations and the role bulkadmin are required (one or the other) to perform bulk imports. Nothing more, nothing less. There is a really nice comparison of the two ......(more)

Login from an Untrusted Domain – Back to Basics - Remember When… Back in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet. I ......(more)

Finding Deprecated Uses in SQL Server - How well do you know your environment? You probably know all of the jobs that are running, the frequency that indexes need to be rebuilt, and even which users have which level of access to each object in the SQL Server instance. Do you know that your ......(more)

Software Development

Spotify is writing massive amounts of junk data to storage drives - Enlarge / SSD modules like this one are being abused by Spotify. (credit: iFixit) For almost five months—possibly longer—the Spotify music streaming app has been assaulting users' storage devices with enough data to potentially take years off their ......(more)

Why put your database into version control? - Software development is a difficult and demanding discipline. It gets even more difficult when you bring teams of developers and database administrators together on a single project. One of the fundamental issues arises around the code itself. Who is ......(more)

Security news and thoughts

Yahoo Took Its Time Investigating Massive Security Breach - Yahoo detected evidence that a hacker had broken into its computer network at least 18 months before launching an investigation that discovered personal information had been stolen from about 500 million user accounts. The timeline outlined in a regulatory ......(more)

Lessons From the Dyn DDoS Attack - A week ago Friday, someone took down numerous popular websites in a massive distributed denial-of-service (DDoS) attack against the domain name provider Dyn. DDoS attacks are neither new nor sophisticated. The attacker sends a massive amount of traffic, ......(more)

Meet the world's first autonomous computer security systems - Michael Walker explores the results of DARPA’s Cyber Grand Challenge (CGC), a contest to develop first-generation autonomous cyber defense systems.Continue reading Meet the world's first autonomous computer security systems. ...(more)

Indiana county government shut down by ransomware to pay up - Enlarge / The Madison County courthouse has been shut down since last week by ransomware. (credit: Nyttend) Madison County, Indiana, suffered a widespread ransomware attack that shut down virtually all county services last week. Over the weekend, the ......(more)

Fancy Bear goes all out to beat Adobe, MSFT zero-day patches - It's been go time for spear phishing as the window for Adobe and Windows zero-day exploits closes with recent patches. (credit: Wikipedia) A Russia-based hacking group is seeking to maximize the value of its zero-day exploits before patches issued by ......(more)

Product Upgrades and Releases

Coming Soon: A Command-Line Interface for Managing SSIS Catalogs - I’m excited to announce CatCompare – a command-line interface (CLI) for SSIS Catalog Compare – is in pre-release testing. I plan to release the next update of SSIS Catalog Compare with an option to purchase the GUI product stand-alone or the GUI + CLI ......(more)

Licensing – everyone’s invited! - A couple of months ago, we updated you on the changes we’ve been making to our licensing system.  Since then, user account licensing has been incorporated into even more Redgate products (you can see the current list here). We’ve also been busy adding ......(more)

Product Reviews

Integrating SQL Monitor alerts with other systems - One of SQL Monitor’s most valuable capabilities is raising alerts when bad things happen in SQL Server. The alerts are listed within SQL Monitor but can also be sent out as emails. However, many of our customers have said they’d like to see those alerts ......(more)

An introduction to SQL Source Control - In our series of blog posts about Version Control and the Database, we’ve explored the history of version control, why databases have some unique requirements, and why it’s important to include them in your VCS. In this concluding post, I focus on Redgate ......(more)

The GUI Helps Build SQL Prompt Snippets - SQL Prompt is an amazing tool for writing T-SQL. It helps you to concentrate on your code, without having to go off and examine Books Online or other documentation, or even worry about formatting. One of the keys to making Prompt efficient for you, in ......(more)

Press Releases

Announcing our Black Friday 2016 Sales! - Every year on Black Friday (the first Friday after Thanksgiving), we have a little fun here and celebrate the same way other stores do. Here’s the sales we’re running this year: 5 $1 seats will be available in each of our 4-day online classes (Performance ......(more)

PowerPivot/PowerQuery/PowerBI

Pushing Data From Excel To Power BI Using Streaming Datasets - One Power BI feature that almost passed me by (because it was released in August while I was on holiday) was the ability to create streaming datasets in the Power BI web app and push data to them via the Power BI REST API. This blog post has the announcement: ......(more)

Power BI Custom Visuals Class (Module 24 – Globe Map) - In this module you will learn how to use the Globe Map Power BI Custom Visual.  The Globe Map is a 3D globe visual that can display both bar chart and heat map overlays on top of a map visual. Module 24 – Globe Map Downloads Power BI Custom Visual ......(more)

Polybase/HDInsight

Flat File Compression In Polybase - For today’s post, I want to look at how well Polybase on Hadoop can handle different compression formats.  I’m going to use the same compression codec (org.apache.hadoop.io.compress.DefaultCodec) for each.  Each file will be a small, 777-record file ......(more)

Data Manipulation with sparklyr on Azure HDInsight - by Ali Zaidi, Data Scientist at Microsoft Apache Spark and a Tale of APIs Spark is an exceptionally popular processing engine for distributed data. Dealing with data in distributed storage and programming with concurrent systems often requires learning ......(more)

Let’s Install Polybase - The inevitable first post in any technical series is the installation post.  We’re going to set up a new instance of SQL Server 2016 and install Polybase.  Installing Polybase is straightforward if you follow the installation guide on MSDN. Grab That ......(more)

Performance Tuning SQL Server

Top 5 Overlooked Index Features - I see a lot of indexes Most of them are pretty vanilla. Some are pretty memorable, like one that I saw with 93 INCLUDES. A lot of things that people could do with indexes get overlooked, either because they don’t know about them, or they’re not sure ......(more)

Migrating from SQL Server to MySQL, PostgreSQL, and Aurora - I’m getting an increasing number of requests these days that say: “We’re considering migrating our application from SQL Server to a cheaper, easier-to-scale database. Can you help?” Well, kinda: why are you doing it? If you’re doing it to get easier ......(more)

Query tuning 101: Problems with IN () - If you’re a DBA or database developer, chances are you will have to write or rewrite queries from time to time. Rewriting may be for new features or simply to improve the performance of existing code. There are tens of thousands of tips and tricks for ......(more)

SQL Server 2016–Live Query Statistics - WOW!!! The next great update to Execution Plans is a way to watch the processing of rows through iterators while the query runs. Never saw that coming. And even a bonus, you can use SQL Server 2016 Management Studio (SSMS) to watch queries run on versions ......(more)

Can comments hamper stored procedure performance? - Every once in a while, a conversation crops up where people are convinced that comments either do or don't have an impact on performance. In general, I will say that, no, comments do not impact performance, but there is always room for an "it depends" ......(more)

Can Indexes My Query Doesn’t Use Help My Query? - This came up during Office Hours And I love questions like this, because they reveal some interesting underpinnings of how the cardinality estimator works. It was something along the lines of “I have a slow query, and when I add an index the query goes ......(more)

Query Store - Part 3 - How it works? - Query Store collects 2 important kinds of data. They are1) Compile time statistics - Query Plan, Estimated Costs, Estimated number of rows etc..2) Runtime statistics - Cost of the query, row count, IO cost, memory, Degree of parallelism etc.How it actually ......(more)

Query Store Instance Dashboard - Last week I shared my custom Query Store Dashboard that can be used to view additional performance information that isn’t always directly available through the built-in Query Store reports. The dashboard I shared last week returns information on a per-database ......(more)

sp_executesql Is Not Faster Than an Ad Hoc Query - This requires an immediate caveat. You should absolutely be using sp_executesql over any type of non-parameterized execution of T-SQL. You must parameterize your T-SQL because the lack of parameters in building up and executing strings is a classic SQL ......(more)

NOSQL

External Data Sources: Hadoop - Today’s post will look in some detail at the first of several external data sources.  First, let’s check out MSDN. There are a half-dozen options, but for today, we’re focusing on a Hadoop cluster: -- PolyBase only: Hadoop cluster as data source -- ......(more)

MDX/DAX

Analyzing events with a duration in DAX - This article analyzes how to efficiently work with events that lasts over time, such as the duration of an order considering the distance between order date and ship date. Whenever we speak about orders, we think at sale orders. In a more generic way, ......(more)

Internet of Things

Regulation of the Internet of Things - Late last month, popular websites like Twitter, Pinterest, Reddit and PayPal went down for most of a day. The distributed denial-of-service attack that caused the outages, and the vulnerabilities that made the attack possible, was as much a failure of ......(more)

IoT: Pioneering New Technologies - Despite all the criticisms of the Internet of Things concept, it's very real, but probably is going through a shakeout stage. What will emerge will be a model focused on efficiency and based on new compute models. ...(more)

HA/DR/Always On/Clustering

SQL SERVER – Added New Node in Windows Cluster and AlwaysOn Availability Databases Stopped Working - Almost all the time, whenever there is a wizard, it’s a human habit to go with the defaults and finally click finish. Once of my client sent below email to me. In this blog post we are going to learn about Added New Node in Windows Cluster and AlwaysOn ......(more)

When disks have different sector sizes for primary and secondary replica log files in an AlwaysOn Availability Group Config - Recently I was analyzing a stress test result for an AG configuration, and as usual I was reading to SQL error logs to see if there is anything out of normal happened during the stress window. In one of the asynchronous replicas, I started noticing a ......(more)

ETL/SSIS/ELT

SSIS Catalog Project Versioning - If you’ve spent much time working with the SSIS catalog, you likely already know that the catalog is where all assets related to package storage, configuration, and execution are stored: all of the projects and their associated packages, configuration ......(more)

Playing audio via Biml - Playing audio via BimlHow often do you need to play audio while you're compiling your Biml packages? Never? Really? Huh, just me then. Very well, chalk this blog post as one to show you that you really can do *anything* in Biml that you can do in C#. ...(more)

ETL Atomicity - I still remember the first real ETL process I developed. I was working for a hospital at the time, going through a major system implementation as we replaced a 17-year-old UNIX-based system with a more modern healthcare application suite with a SQL Server ......(more)

Database Design, Theory and Development

Understanding Database Scalability – Horizontal and Vertical Scalability Better Together - When I wrote the blog around Understanding Database Scalability – Vertical and Horizontal, I saw few have conversations with me on if it is this or that. And these discussions could take a course all by itself. There is no one size fits all when it comes ......(more)

Damon Feldman on multi-model databases - O'Reilly Podcast: Working with databases that go beyond traditional models.In this podcast episode, I talk multi-model databases with Damon Feldman, solutions director at MarkLogic. If this conversation gets you interested in multi-model databases, be ......(more)

Principle of Orthogonal Database Design Part II - (Continued from Part I, which I have just revised significantly enough to recommend a re-read.)To recall, adherence to the POOD means independent base relations--no base facts are derivable from the other base facts--that have unique relation predicates ......(more)

Data Mining/Data Analysis

Implementing Fuzzy Sets in SQL Server, Part 5: The Mystery of the Missing Left Join - By Steve Bolton …………Information on set operations like complements, intersections and unions is plentiful in the literature on fuzzy sets, which made the last three articles in this series of amateur self-tutorials easier to write in a certain sense. ...(more)

Creating a Data-Driven Organization Depends on a Data-Driven Culture - Data Strategy and Business Intelligence aren’t really about data; they’re about the way data is used. The end goal isn’t more silos, with a team that owns the data, the process, and its value. The end goal is a data-driven culture where everyone sees ......(more)

Continuous Delivery (CI/CD)

How to deploy your SQL Server database to a remote server - CASE 1: you have direct access to both the SQL Server where the source database is and the SQL Server where the target database is.First time deploymentBackup / restore methodBackup the database on the sourceCopy the backup file to the target machineRestore ......(more)

Computing in the Cloud

Azure SQL Server 2016 VM - With Windows Server 2016 just been released, now is the perfect time to build an Azure VM with SQL Server 2016 on Windows Server 2016.  In a matter of minutes you can be playing and learning both platforms.  Below I will document the steps I took to ......(more)

Azure Data Platform Learning Resources - I know that more and more of you are moving more and more resources into the Azure Data Platform. In an effort to help you learn about all that Azure offers I created a GitHub repository of blogs, teachers, courses, etc. It’s open to the community to ......(more)

Career Growth

How I prepare for a presentation - Some people say I talk a lot – but I guess it depends on the context. Certainly, for many years, I’ve been fairly comfortable about standing up in front of people and explaining things. Whether it’s teaching a course, leading a workshop, presenting at ......(more)

T-SQL Tuesday #84: Growing New Speakers – Dealing with Failure - I’m joining this month’s #tsql2sday blog party as Andy Yun (blog | Twitter) has asked speakers to provide advice to new presenters. I’ve been presenting technical content for several years, and I think it’s one of the best things I’ve done for my career. ...(more)

Knowledge vs Ability - Knowledge and ability, they are very inter-related. We born with only survival ability, ability to eat and drink, that’s pretty much about it. After that we started to gain knowledge through learning, and become ability, through school to learn language, ......(more)

TSQL Tuesday #84: Zip to Speaker - Well, it’s that time of the month again. It’s T-SQL Tuesday yet again, and this time round it’s Andy Yun (b/t) hosting with a subject of Growing New Speakers. Well, I’ll admit I’m a fairly new speaker myself. In fact, I’ve spoken a grand total of 4 times ......(more)

Growing Speakers for T-SQL Tuesday #84 - It’s T-SQL Tuesday again, and for $#84, we have a non-technical topic. Hopefully this will then let us produce more technical content by growing new speakers. That’s the topic from Andy Yun, and it’s a good one. This is the monthly blog party, and you ......(more)

The Three Parts of Every Presentation (#tsqltuesday #sqlnewspeakers) - T-SQL Tuesday: Growing New Speakers Every presentation has three parts: The topic you want to cover The material you write to cover the topic The way you deliver the material After picking your topic, that’s where writer’s block comes in. You stare ......(more)

T-SQL Tuesday #84: Getting Ready for your Presentation - I’ve been super busy lately, but I wanted to at least post something for this month’s T-SQLTuesday. The topic is about encouraging new speakers, something I’m very passionate about. I think that speaking is one of the best things you can do to boost ......(more)

Bugs/Patches for SQL Server

.Net 4.6.2. Framework client driver for Always Encrypted resulting in intermittent failures to decrypt individual rows - The SQL Product team has identified an issue with .Net 4.6.2 framework client driver for Always Encrypted enabled database on SQL Server 2016 and Azure SQL Database. The issue can lead to intermittent failure while trying to decrypt the records from ......(more)

Blogs : Events

Webinar on Change Tracking for Data Warehouse Loads - Join me online on Tuesday, November 15th at 1:00PM as I host a webinar entitled “Managing Data Warehouse Incremental Loads with SQL Server Change Tracking”. In this webinar, I’ll discuss the essentials of incremental loads, and will introduce SQL Server ......(more)

Notes from the 2016 SQLSaturday Roundtable Meeting - Each year PASS holds a meeting for SQLSaturday event leaders and key players on Tuesday morning. It’s a valuable meeting, though I have some criticisms about both format and content this year, more on that in a minute. Watching and listening, I appreciate ......(more)

The ROI of SQLSaturday - Following up on my notes about the 2016 SQLSaturday Roundtable I wanted to write more about the ROI of SQLSaturday. I suppose there are many ways to measure it, but I think it boils down to two views: How many people did we train/hours we deliver to ......(more)

Pick the Logo for GroupBy, the Free Online Conference - The free online database conference I wrote about two weeks ago now has a name: Group By. Now, we need a logo. I ran a 99designs contest, and I’ve narrowed it down to 5 choices. Don’t worry about the font or the color – we can swap those out pretty easily. ...(more)

Free Webinar – Redgate DLM Demo - Learn how to use Redgate’s DLM solution to improve your database change management process in our free webinar on November 15th. Microsoft Data Platform MVP Steve Jones, and Redgate’s Arneh Eskandari, will demonstrate how Redgate’s tools plug into Subversion, TeamCity & Octopus Deploy so that you can build, test and deploy your database alongside your application code. Register now....(more)

Blogs : Reporting Services

Community contributions to the PowerShell scripts for Reporting Services - Today’s blog post is from Parth Shah, an engineer on the Reporting Services team. This summer, the Reporting Services team experimented with new ways to engage with our community. We realized that there was a strong need for a place where both the engineering ......(more)

Blogs : T-SQL

SQL Server: Discuss Execute(SQL) At LinkedServer - Introduction I recently end up in calling a remote stored procedure(“X”) from a remote machine (“Y”)and  the script needed to pull data store it on a remote server(“Z”).  I set up the linked server and everything was working great. Executing queries ......(more)

Drag & Drop Table & Column Names in SSMS - I was working with a developer the other day and he was typing out each table name and all the column names he needed when working in Management Studio. He didn’t know that you can drag a table or column from object explorer into the query window and ......(more)

In-Memory OLTP: Is your database just in memory or actually optimized for memory? - In my many conversations with customers during Microsoft events, people often confuse between the terms ‘In Memory’ and ‘Memory-Optimized’ and many think that they are one and the same. If you continue reading this blog, you will realize that they are ......(more)

Backup and Recovery

Oops Recovery with Temporal Tables - Reviewed by: Kun Cheng, John Hoang, Sanjay Mishra, Borko Novakovic, Denzil Ribeiro, Murshed Zaman Have you ever got that sinking feeling after hitting the Execute button in SSMS, thinking “I should not have done that”? DML statements with missing WHERE ......(more)

Azure SQL Datawarehouse

PASS Summit Announcements: SQL DW free trial - Microsoft usually has some interesting announcements at the PASS Summit, and this year was no exception.  I’m writing a set of blogs covering the major announcements.  Next up is the free trial of Azure SQL Data Warehouse (SQL DW). Azure SQL Data Warehouse ......(more)

Azure Analysis Services

Please Lend Me Your Vote for Documentation of TMSCHEMA DMVs - I spent a good bit of time looking for the definitions/descriptions of the TMSCHEMA DMVs that allow us to view metadata and monitor the health of SSAS 2016 tabular models. As far as I can tell there are no details about them on any Microsoft site. Many ......(more)

Analysis Services / BI on the MS Stack

Optimizing Multi-Billion Row Tables in Tabular - Learn how to optimize huge tables in Analysis Services Tabular in this session from PASS Summit 2016. ...(more)

Columnstore Index: Why do I need to create clustered columnstore Index on In-Memory OLTP table for Analytics? - The columnstore index on In-Memory table(s) targets real-time operational analytics (HTAP) allowing customers to eliminate the need for ETL (Extract, Transform and Load) into a separate Data Warehouse.  For details on the scenario, please refer to https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/18/real-time-operational-analytics-a-short-video-on-the-value-proposition-in-sql-server-2016/  You ......(more)

AI/Machine Learning/Cognitive Services

79% of US Consumers Want “Artificial Intelligence” to Know the Limits - by Angela Guess A recent press release reports, “Ahead of the opening of the world’s largest international trade fair for the electronics industry in Munich, Electronica reveals its trend index 2020, an international survey highlighting how people see ......(more)

IBM Launches Experimental Platform, Project Intu, for Embedding Watson into Any Device - by Angela Guess According to a new press release, “IBM today unveiled the experimental release of Project Intu, a new, system-agnostic platform designed to enable embodied cognition. The new platform allows developers to embed Watson functions into various ......(more)

Goofing around with the Cognitive Services Translator API - Recently, I was asked to get familiar with the Translator API in Azure Cognitive Services.  Not being a developer, I was a little leery of how I might approach this, but I was surprise how easy it was to tap into this functionality.  I thought I would ......(more)

Administration of SQL Server

Tempdb misconfiguration when SQL Server fails to create a secondary data file - One of the tempdb best practices when you configure SQL Server is to create additional data files for tempdb according the number of logical processor per NUMA node up to eight. Even with SQL Server 2016 you can specify the creation of those secondary ......(more)

Filtered Indexes: Rowstore vs Nonclustered Columnstore - SQL Server has two types of filtered indexes: The “classic” filtered nonclustered rowstore index, introduced in SQL Server 2008, available in all editions The newfangled filtered nonclustered columnstore index, introduced in SQL Server 2016, available ......(more)

Troubleshooting intermittent connection timeout - In this blog, we are going to take a deep dive into a connectivity issue my customer encountered a while ago. DBA was pulled into this case because the error was caused by SQL Server connection timeout. We looked at SQL Server log and there was no messages ......(more)

SQL Server Diagnostic Information Queries for November 2016 - This month, I have added several new queries to SQL Server 2016 set, along with adding additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets. I have gotten quite a bit of interest about making a special version of these queries ......(more)

SQL Server – RECONFIGURE - When you want to change configuration settings for your SQL Server you would either do it via Management studio (under server properties) or via TSQL calling on sp_configure. For example, I usually change the cost threshold for parallelism from the … ......(more)

Shrinking SQL Server Data Files – Best Practices, and Why It Sucks - I’ve gotten a few questions about shrinking SQL Server data files lately. What’s the best way to get shrink to run? And why might it fail in some cases? Traditionally, every time you ask a DBA how to make shrinking suck less, they start ranting how shrinking ......(more)