The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

This week I saw a post from Microsoft Tiger Team on the issues with backup compression and TDE databases. Apparently when they added compression, they didn't test a few edge cases. Or a few regular cases, like WITH INIT. There is a possibility that your data could be corrupted, which is a major problem. I have found the SQL Server backup and restore capabilities to be very solid over the years, so this is disturbing. If you use TDE, apply SQL 2016 CU7 for RTM or SP1 CU4 ASAP. Test your restores, and be sure you know what you can recover and what you can't. If Mr. Murphy has anything to say about it, you'll have an issue soon, so test your restores.

Overall, SQL Server security is very good, but there are certainly issues with applications and devices that connect to SQL Server. You never know when some item that queries or alters data in SQL Server will cause issues. This week there were a couple notes from Bruce Schneier on FaceID and Bluetooth security, the latter of which has a vulnerability issue. Be sure you are aware of issues here to actually help protect yourself with your devices, but I was amazed to see this piece on infrared camera hacking. A POC on using light to jump air gaps is truly frightening. It seems that truly anywhere that we are processing data, we need to be thinking about security.

The last few weeks are especially scary for many people, particularly with the Equifax breach. I know they have released numbers on people's data, but I'd assume that everyone who has ever had credit in the US has a potential issue. I know I am being careful with credit and watching for issues myself. You should, too, and demand better security from companies you do business with. We can improve systems, but it will take more pressure to get companies to put more emphasis on better software and security.

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

Overcoming the Challenges of Microservices and Docker Containerisation - A microservice architecture can provide great benefits to certain types of applications that have pronounced troughs and peaks in demand, such as web-based shopping apps. With containerization, there is the promise of deploying containerized services in a matter of seconds to deal with demand. Has the Docker ecosystem now reached the level of maturity to allow this type of application to become standard practice?...(more)

Vendors/3rd Party Products

How to import an existing database to ReadyRoll - The SQL Toolbelt includes ReadyRoll, which allows you to adopt a migrations-first approach to database source control and deployment. There are a number of different ways teams can get started with ReadRoll, in this article Tom Austin outlines the steps you need to take using Visual Studio 2017 Community....(more)

How to examine differences with SQL Compare Summary View and SQL View - This video shows the brand new Summary View tab in Redgate SQL Compare....(more)

The SQL Prompt Functionality Finder - SQL Prompt is a lot more than just an intellisense tool, it also offers lots of other time-saving utilities for formatting, refactoring, and navigating your SQL code. In this article, Phil Factor provides a handy table that lists all of SQL Prompt's functionality, and where in the tool to access it....(more)

Database provisioning from backups using SQL Clone - In a previous article, Tony Davis described how SQL Clone could remove much of the administrative burden from the database provisioning process. This article offers a brief, step-by-step guide to the alternative approach of creating an image from a database full backup, or from a full backup plus differential backup....(more)

Use SQL Server? Redgate would like to hear from you - Redgate are running a survey to better understand trends in adoption of different technologies and platforms. Completing this survey will take just a few minutes, and as a thank you for your time, they will be giving away a $100 Amazon gift card to one randomly selected participant who completes the survey by October 10....(more)

T-SQL

Temporal Tables - Part 6 - Start and End Times in UTC Time Zone - In my first 5 blogs on Temporal , I failed to note something pretty important about their usage. The start and end times for the row (and it follows, the historical rows) are likely not in your local time zone. They are stored in UTC time zone. This ......(more)

The Lighter Side

Linda Hamilton, Arnold Schwarzenegger back for a new Terminator movie - Enlarge / Linda Hamilton as Sarah Connor. (credit: Studio Canal) For fans of the 1984 thriller The Terminator and its 1991 action sequel Terminator 2: Judgment Day, the sequels have been a consistent disappointment. I'm hesitant to get my hopes up too ......(more)

Because it's Friday: Rapid Unscheduled Disassembly - SpaceX has done some amazing work proving the concept of commercial spaceflight services. But that's not to say there haven't been a few bumps along the way, as this "blooper reel" (set to Monty Python music shows). (If now's not a good time for video, ......(more)

Fun with random names - Just for laughs, we’re going to take a look at a slightly-too-complex yet elegant solution to a seemingly simple task: creating a list of 100 fictional company names for a demo database. What I need I’m working on a demo database where I need some fictional ......(more)

When Deadlocks Become Art - We’ve all had to solve deadlock issues. Identify the two conflicting pieces of code, work out an appropriate change to one of them to avoid the issue. Job done. Occasionally you see a deadlock that has three nodes. Okay, a little bit more analysis and ......(more)

I’m Climbing the Space Needle! - Many of you have seen the Space Needle during trips to Seattle, especially if you have visited during PASS Summit week. Have you ever wondered about climbing to the top instead of taking the elevator? That is exactly what I’ll be doing on October 1st ......(more)

SQL Server Security

SQL SERVER – Login Failed – Error: 18456, Severity: 14, State: 38 – Reason: Failed to Open the Explicitly Specified Database - Those who are my regular clients would know that I am very active in replying to emails. My average time of response is around 24 minutes. Many of the emails are for suggestions and I don’t get much time to help everyone, but I do reply to them letting ......(more)

SQL Server on Linux

Active Directory Authentication with SQL Server on Ubuntu - SQL Server on Linux is here, and if you want to use Active Directory authentication, there's a lot of steps involved to get it working. Let's walk through the configuration on Unbuntu! ...(more)

SQL Server News

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

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

SQL Server PolyBase supports Hadoop.rpc.protection - This post was authored by Casey Karst; Program Manager II; Data Systems. We are pleased to announce that PolyBase in SQL Server 2016 and later can connect to Hadoop clusters with the hadoop.rpc.protection configuration set to Integrity, Privacy or Authentication. ...(more)

What Would You Put in SQL Server 2018? - If you were leading Microsoft’s development programs, what would you add or change in SQL Server 2018? Forget licensing or pricing changes – that’s dictated by the marketing teams, and those decisions are made really close to the release date. Instead, ......(more)

The importance of bringing Python to SQL Server - When SQL Server 2017 launched, Microsoft proudly announced that it was the first relational database engine to ship with built-in artificial intelligence (AI) capabilities. In the future, database servers running its software won’t just be able to store, ......(more)

Security news and thoughts

Bluetooth Vulnerabilities - A bunch of Bluetooth vulnerabilities are being reported, some pretty nasty. BlueBorne concerns us because of the medium by which it operates. Unlike the majority of attacks today, which rely on the internet, a BlueBorne attack spreads through the air. ...(more)

Apple's FaceID - This is a good interview with Apple's SVP of Software Engineering about FaceID. Honestly, I don't know what to think. I am confident that Apple is not collecting a photo database, but not optimistic that it can't be hacked with fake faces. I dislike ......(more)

SEC Reveals 2016 Hack that Breached Its Filing System - The Securities and Exchange Commission said Wednesday that a cyber breach of a filing system it uses may have provided the basis for some illegal trading in 2016. In a statement posted on the SEC's website, Chairman Jay Clayton said a review of the ......(more)

Report: Hackers Were Snooping Inside Equifax for Months - Hackers broke into Equifax's computer systems in March, two months earlier than the company had previously disclosed, according to a Wall Street Journal report . That gave the intruders months to probe vulnerabilities and eventually gain access to the ......(more)

Infrared signals in surveillance cameras let malware jump network air gaps - Researchers have devised malware that can jump airgaps by using the infrared capabilities of an infected network's surveillance cameras to transmit data to and from attackers. The malware prototype could be a crucial ingredient ......(more)

R Language

Hurricane Irma's rains, visualized with R - The USGS has followed up their visualization of Hurricane Harvey rainfalls with an updated version of the animation, this time showing the rain and flooding from Hurricane Irma in Florida: This iteration improves the Harvey version by displaying rainfall ......(more)

Confidence Intervals for a proportion – using R - What is the difference between reading numbers as they are presented, and interpreting them in a mature, deeper way? One way perhaps to look at the latter is what statisticians call ‘confidence interval’. Suppose I look at a sampling of 100 americans ......(more)

How to create Polar Charts - In this post I am going to show how to create a polar chart using R codes inside Power BI. imagin that we have below dataset about 4 main groups and their related values. I am going to show you how to draw different polar charts with different shapes. imagin ......(more)

Product Reviews and Articles

Real-World Use Case for Redgate SQL Data Generator - At some point during the development lifecycle someone, be it a developer, QA, or a business owner, asks for "real" data to test against. Typically this is for two reasons, they want the volume of data to match production, or the "fake" data doesn't ......(more)

PowerShell

PowerShell Classes Part 4 — Constructors and Inheritance - Now that you’re familiar with PowerShell classes, as well as its programming concepts and terminology, it is time to show you some other benefits to using PowerShell classes. In this article, I’m going to talk about constructors and how you can use different ......(more)

Test Database Compatability with dbatools - I really like the dbatools project. This is a set of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items. One of ......(more)

TSQL2sDay – Get-PostRoundup - First an apology, this round up is late! The reason for that is an error in the PowerShell testing module Pester (That’s not completely true as you shall see!!) I spoke in Stuttgart at the PowerShell Saturday last weekend and had intended to write this ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI and R- Timeseries series Part 9- Decompose None seasonal Data - In the last posts, I have explained about some main concepts of Time series. How to decompose time series that has irregular, trend and seasonality components have been explained in: seasonality component decompose). now image we have a dataset that ......(more)

Microsoft News

Microsoft: Windows getting more stable, faster, and lasting longer on battery - Enlarge / With Windows breaking less often, scenes like this should become a thing of the past. (credit: Lee Adlaf) Windows 10 is getting better and better, Microsoft insists, as it works to build confidence in the operating system in the run up to ......(more)

MDX/DAX

Using KEEPFILTERS in DAX - This article explains how to use KEEPFILTERS to intersect instead of overriding an existing filter context in DAX, simplifying the code and improving performance. There is an additional disclaimer to this article – if you think that it is too complex, ......(more)

Basics of Value Structures in M – Power Query Formula Language - A couple of months ago, I’ve written a blog post about Basics of M and explained few basics about this language. In this post, I’m going to the next step and will explain few other structure definitions in this language. In this post, you will learn ......(more)

Hardware News

Western Digital Ships 12 TB WD Gold HDD: 8 Platters and Helium - Western Digital has begun to ship its WD Gold HDD with 12 TB capacity to partners and large retailers. The 3.5” drive relies on the same platform as the HGST Ultrastar He12 launched this year, and will initially be available to select customers of the ......(more)

Prices of Intel’s Coffee Lake-S CPUs Published: $400 for Core i7-8700K? - An online retailer in the UK has started to take pre-orders on Intel’s upcoming Coffee Lake CPUs, specifically the socketed 'S' parts for desktop computers. As reported previously, the new processors will have more cores than their direct predecessors, ......(more)

GIGABYTE to Launch the X399 Designare EX: An AMD Motherboard Supporting Thunderbolt 3 add-in Cards - GIGABYTE has another motherboard coming out supporting AMD’s Threadripper CPUs, the X399 Designare EX. The Designare EX is slated to be their flagship motherboard for the X399 chipset and includes additional features over the AORUS Gaming 7, bringing ......(more)

HA/DR/Always On/Clustering

SQL SERVER – How to Move SQL Server Cluster to Different Domain? - One of my blog readers sent an email where he wanted my quick opinion about SQL Server cluster. Hi, We are having SQL Server 2014 clustered instance running on top of Windows Server 2012. As a part of the acquisition, we need to move the domain servers. ...(more)

Graph Databases

Graph Matching with T-SQL Part 2: Maximal Matching - Practical use cases for finding a maximum matching are quite obvious--for example, maximum utilization of agents. But what could be practical use cases for finding a maximal matching? Here's what you need to know. read more ...(more)

DevOps and Continuous Delivery (CI/CD)

DevOps with Containers - This post from Premier Developer consultant Razi Rais talks about his recent Channel9 video series on using Containers for DevOps. Recently I did a video series for Microsoft Channel9 on DevOps with Containers (thanks to Lex Thomas and Chris Caldwell ......(more)

Data Science

Microsoft Data Science Virtual Machine Tutorials - There are so many interesting data science and AI tutorials, which are open-sourced, and ready for the DSVM These tutorials available at https://github.com/Microsoft/ Here some of my favourite tutorials which are perfect for introducing students to Data ......(more)

Do Citizen Data Scientists Mark the Death of Data Scientists? - Click to learn more about author Kartik Patel. To be successful in business, every organization must find a way to accurately forecast and predict the future of its market, and its internal operations, and better understand the buying behavior of its ......(more)

Data Science Use Cases - It is only when Data Analytics start meshing with advanced technologies like Machine Learning and Deep Learning that enterprises will reap the benefits of their analytics and Business Intelligence (BI) activities. Those technologies require well-planned ......(more)

Case Study: Three Strategies for Data Governance Success - “How do we measure the quality of our health care?” asked Laura Tellmann, Healthcare Informatics Executive at BJC HealthCare in St. Louis, speaking at the Enterprise Data World 2017 Conference. She shared a photo of a young boy who had survived a brain ......(more)

Data Privacy

So, what is GDPR and why should Database Administrators care? - You’ve no doubt heard at least something about the GDPR, the EU’s new privacy and Data Management law with its greatly increased maximum fines for non-compliance and tighter definitions for acceptable use of personal information. If you’ve continued ......(more)

Webinar: Your Worst GDPR Nightmare – Unstructured Data - To view just the slides from this presentation, click HERE>> This Webinar is Sponsored by: About the Webinar There’s no question that organizations across the globe are ramping up their efforts to prepare for the EU’s expansive General Data Protection ......(more)

Conferences and Events

SQLpassion Online Training in October 2017 - (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) Today I’ve announced my SQLpassion Online Training for ......(more)

SQLintersection Fall 2017 - As we head towards our 10th SQLintersection in October, I’m excited to say that it’s once again our most diverse, complete, and information-packed show yet! One of the pieces of feedback we hear ......(more)

Using Slack for Conference Session Q&A - Next month at the PASS Summit in Seattle, Erik Darling and I are teaching a one-day pre-conference class on performance tuning. We sold ~300 seats, which is awesome – but that comes with some challenges, one of which is handling Q&A. As classes get over ......(more)

Computing in the Cloud (Azure, Google , AWS)

Azure Virtual Machines: Initial Thoughts - Last week was a weird one for me. For the first time in more than 25 years, I found myself unemployed. I was anticipating using my newfound abundance of free time to learn as much as I could while I continued searching for my next employment opportunity. ...(more)

Career Growth

Monday Coffee: Should I learn powershell? - This month’s T-SQL Tuesday’s topic was set by Rob Sewell (b|t) and entitled Let’s all get PoSH It had a massive response with loads of people writing about how they use powershell to automate tasks in their day to day work. (Shameless self promotion ......(more)

Backup and Recovery

Backup Compression for TDE-enabled Databases: Important fixes in SQL 2016 SP1 CU4 and SQL 2016 RTM CU7 - Backup Compression and Transparent Data Encryption (TDE) have been two immensely valuable and popular features for customers in SQL Server. In SQL Server 2016, backup compression was enabled for TDE databases where if you backup a TDE enabled database ......(more)

Azure SQL Database

SQLskills SQL101: Azure SQL Database Monitoring - As Kimberly blogged about earlier this year, SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there ......(more)

Azure SQL Data Warehouse and Data Lake

Pausing Azure SQL Data Warehouse using an Automation Runbook - When a Azure SQL Data Warehouse is paused, all running queries are canceled. If there are queries running that have data movement, rollback operations will likely happen. These rollbacks can take many hours to complete. While these rollback operations ......(more)

AI/Machine Learning/Cognitive Services

AI is the new electricity - Andrew Ng shares his thoughts on where the biggest opportunities in AI may lie.Continue reading AI is the new electricity. ...(more)

Administration of SQL Server

Geek City: Changing How To Change Your Database Properties — ALTER DATABASE - (This article was originally published at https://www.dbbest.com/blog/ Long ago (in SQL Server years) many metadata changes were implemented with special one-off stored procedures. For example, we had sp_addindex and sp_dropindex , as well as sp_addtype ......(more)

SQL SERVER – FIX: Msg 5123, Level 16 – CREATE FILE Encountered Operating System Error 5 - This blog is about another common error reported by many DBA to me. He faced error related to create file in OS. Here is the text of the message. CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create ......(more)

The Case of the Rowgroup Deadlock in a Columnstore Index - I came across a fun deadlock when writing demos for my session on the Read Committed isolation level this week. (It’s OK to call it “fun” when it’s not production code, right?) I was playing around with a nonclustered columnstore index on a disk-based ......(more)

More SQL Server Features that Time Forgot - SQL Server produces some great features, but it would be impossible to get them spot-on target every time. We are now quietly advised to use caution about using some of them, such as AutoShrink or the Index Advisor. Others, like the database diagramming tool, almost seem to have been quietly abandoned. Robert Sheldon investigates....(more)

.NET Related Articles

Abstracting System Time in ASP.NET Applications - When designing web applications, it is a good idea to be cautious about the way you handle the time of day and calendar. It can get complicated when you have servers in different time zones and the user of the time-sensitive application may, or may not, want his local time, including daylight-saving adjustments, to be assumed. Problems pop up unexpectedly, so maybe it is better to tackle the issue up-front. Dino Esposito explains....(more)