The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Many of us that work with technology will play with side projects at times. The classic application when I was a young man was a software application to organize your book or record album collection. That morphed to CD and DVD tracking systems as I got older, and even Dell got into this genre with their sample DVD store application that customers could use to test their hardware. These days I see plenty of young software developers building all sorts of applications. Tweetbots, Chat with SignalR, websites scrapers, mapping tools, mobile games, and of course, Internet of Things related software that accesses sensors or controls some device like a Raspberry Pi or Arduino.

One of the more interesting projects that I saw recently was the High Altitude SQL Server Project from Drew Furguiele (b|t). Drew had this idea of launching a high altitude balloon with a twist. Since he's a data guy, he wanted to see if he could run SQL Server on some small hardware and include that in a box. His hardware is interesting, and I learned something. I knew SQL Server wouldn't run on a Raspberry PI, so I was curious about the computer chosen. The Intel Joule is a neat choice, though it's being discontinued.

Drew and his team successfully launched his HASSP a few weeks ago and recovered the payload. The balloon went up quickly (Check out the YouTube videos, pop around 1:41) and managed to get up to around 98,000 feet before the balloon burst and the payload returned to earth on its parachute. Unfortunately the computers shut down around 30 minutes into the flight and the team is still analyzing that result. There is a little data from the SQL Server, which should be uploaded soon.

Overall this was a really interesting project, and one that has inspired me to think about what I can do. My wife has been wanting to measure horse activity, so I've started looking to see if there are pedometers we could put on a horse and receive data from. Alternatively, maybe some inexpensive GPS sensors that might stream data around the property. And, of course, the need for a SQL Server somewhere in the barn. Maybe I'll get my own SSP, a BESSP, Barn, Embedded database.

Perhaps there's a data project that's interested you for some time. Take some inspiration from HASSP and let us know what project(s) you've been working on.

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

Running SQL Server in Kubernetes on Azure Container Services - I’ve been meaning to get to grips with Kubernetes for a while now but I have to admit, I was completely overwhelmed when I first approached the subject. There’s so much information and documentation out there, it’s difficult to know where to start. What ......(more)

T-SQL

Using AT TIME ZONE to manipulate and convert time zones - I have a series of blogs about temporal tables in SQL Server 2016, which you can see here . However, one thing I never had mentioned was that the Temporal Row Start and End Times are always stored in UTC times (which is generally inconvenient to work ......(more)

SQL Code Smells - Table of Contents Introduction 1. Problems with Database Design 1.1. Packing lists, complex data, or other multivariate attributes into a table column 1.2. Using inappropriate data types 1.3. Storing the hierarchy structure in the same table as the ......(more)

Database Fundamentals #8: All About Data Types - SQL Server provides all different kinds of data types in order to give you more flexibility and control for how you store your data. You define these data types as you define the columns in your tables. If you wanted to store information about the date ......(more)

System-Versioned Temporal Tables - Every once in a while, I like to take a moment and learn something new about the latest SQL Server gizmos and gadgets. Today I came across system-versioned temporal tables and it peeked my interest, so I figured I’d investigate and share my finding with ......(more)

What is the Biggest Limitation of ISDATE() Function? – Interview Question of the Week #137 - Question: What is the Biggest Limitation of ISDATE() Function? Answer: I really wish the original question was asked in this format. Instead in the last meeting, the question was actually asked was following: Why do the first query returns 1 and second ......(more)

Database Fundamentals #8: All About Data Types - SQL Server provides all different kinds of data types in order to give you more flexibility and control for how you store your data. You define these data types as you define the columns in your tables. If you wanted to store information about the date ......(more)

Create an empty table from a SELECT statement but without the IDENTITY. - A while back I did a post about creating an empty table using a SELECT statement. Basically doing something like this: SELECT TOP 0 * INTO tableNameArchive FROM tableName will create a new table with the exact same structure as the source table. It can ......(more)

The Lighter Side

You might be a DBA if - I’d been trying to think of a fun post to write and an homage to Jeff Foxworth’s You might be a redneck series seemed like a good idea. To that end, I create a hashtag #YouMightBeADBA and wrote my post on twitter :). Of course, it turns out that the ......(more)

HASSP-2 Has Returned to Earth! - We successfully launched HASSP-2! We achieved our goal of ~100,000 feet and captured some awesome footage. But can we call it a success? Well, "it depends." Here's our launch day diary about how it went. ...(more)

Microsoft’s Mixed Reality Controllers, Hands On - When Microsoft first announced its Windows Mixed Reality controllers at Build earlier this year, we noted that they were reminiscent of the superb Oculus Touch controllers. When we gave them a go in person, we felt the same. ...(more)

SQL Server Security

Dynamic Data Unmasking - Dynamic data masking is a SQL Server 2016 feature to mask sensitive data at the column level from non-privileged users. Hiding SSNs is a common example in the documentation. However, the documentation also gives the following warning:...(more)

SQL Server on Linux

Six reasons to consider SQL Server 2017—especially if you’re on Linux - “Data is currency” is the rule of thumb in business. As with money, business data must be accessible, accurate, and usable across disparate contexts if an organization is to realize greater ROI while keeping costs under control. CIOs understand that ......(more)

SQL Server News

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

SQL Server Internals

My Transaction Log File Is How Big? - How bizarre So there’s this funny thing about SQL Server: many units of measure boil down to 8k pages. That’s the size of a data page, so when you measure reads, or size, sometimes the only thing you can do is convert that to MB or GB, or if you’re super ......(more)

Security news and thoughts

Inside the Massive 711 Million Record Onliner Spambot Dump - Sponsored by: Cybercriminals want to interrupt your business. Be unstoppable with Symantec Complete Website Security. Learn howLast week I was contacted by someone alerting me to the presence of a spam list. A big one. That's a bit of a relative term ......(more)

A Framework for Cyber Security Insurance - New paper: "Policy measures and cyber insurance: a framework," by Daniel Woods and Andrew Simpson, Journal of Cyber Policy, 2017. Abstract: The role of the insurance industry in driving improvements in cyber security has been identified as mutually beneficial ......(more)

CeX Data Breach Affects 2 Million Gamers, Techies - CeX announced that the names, addresses, email addresses, and phone numbers of 2 million of its customers have been compromised. ...(more)

Hacking a Phone Through a Replacement Touchscreen - Researchers demonstrated a really clever hack: they hid malware in a replacement smart phone screen. The idea is that you would naively bring your smart phone in for repair, and the repair shop would install this malicious screen without your knowledge. ...(more)

FBI Arrests Chinese National for Supplying Rare, Malicious Malware - A Chinese national has been charged in California with distributing a type of computer malware that has been linked to attacks on U.S. businesses and to the theft of personnel records of millions of U.S. government employees, authorities said. Defendant ......(more)

R Language

3-D animations with R - R is often used to visualize and animate 2-dimensional data. (Here are just a few examples.) But did you know you can create 3-dimensional animations as well? As Thomas Lins Pedersen explains in a recent blog post, the trick is in using the persp function ......(more)

Tokenizing Text With R - Rachael Tatman shows how to tokenize a set of text as the first step in a natural language processing experiment: In this tutorial you’ll learn how to: Read text into R Select only certain lines Tokenize text using the tidytext package Calculate token ......(more)

Packages to simplify mapping in R - Computerworld's Sharon Machlis has published a very useful tutorial on creating geographic data maps with R. (The tutorial was actually published back in March, but I only came across it recently.) While it's been possible to create maps in R for a long ......(more)

PowerPivot/PowerQuery/PowerBI

Languages and timelines in Power BI and previews - Hurricane Harvey Donations: American Red Cross Salvation Army Humane Society Hurricane Harvey Relief Fund Roundup Items: Context Transition in DAX/Power BI: A Visual Guide (@csgpro) Multilanguage Multilingual reports in Power BI (@CurbalEN) Introducing ......(more)

Power BI Custom Visuals Class (Module 66 – Calendar by Tallan) - In this module you will learn how to use the Calendar Custom Visual by Tallan. The Calendar Visual provides a calendar layout that filters and aggregates data across a range of dates. Module 66 – Calendar by Tallan Downloads Power BI Custom Visual ......(more)

Thoughts about submitting a new Power BI Quick Measure - ‘Quick Measures’ is a feature included in Power BI Desktop April update. It’s still in preview, so you need to enable this feature in order to use it. Inside Power BI Desktop, click ‘File’->‘Options and Settings’->‘Settings’->‘Preview Features’ and check ......(more)

Local #dax measures in #powerbi reports are not visible from #Excel - You can connect an Excel workbook to a Power BI model in two ways: Using the Analyze in Excel action in powerbi.com Using the Connect to Data action in the ribbon of Power BI publisher for Excel In both cases, you have the ability to choose either ......(more)

Create a Date Dimension in Power BI in 4 Steps – Step 1: Calendar Columns - I have written multiple blog posts so far about creating a date dimension. However, I still get the question about how to create a date dimension. In this series of blog posts I am going to explain in details how you can create a date dimension easily ......(more)

New Series of Time Series: Power BI Custom Visual (Part 7) - In the last three parts, I have explained about the time series R custom Visual we have in Power BI. as you remember we have 3 main time series chart in Power BI store (see below): Decompose of timeseries (trend, seasonality and irregular components). the ......(more)

Performance Tuning SQL Server

Why Did My Clever Index Change Backfire? (free video) - I had a great time giving a session recently for the 24 Hours of PASS – Summit Preview Edition. My session at the SQL PASS Summit this year is on great index ideas that accidentally backfire… and how you can avoid disaster! In this preview version, I ......(more)

SQL SERVER – Wait Stats Collection Scripts for 2016 and Later Versions - Yesterday I posted a blog about a new DMV, which is introduced in SQL Server 2016. You can read the blog post here: Get Wait Stats Related to Specific Session ID With sys.dm_exec_session_wait_stats. After reading the blog post, I have received quite ......(more)

What Are Poison Waits? - Most of the time, SQL Server performance tuning starts with your top wait stats. Run sp_BlitzFirst @SinceStartup = 1, look at your top couple of wait types, and that’s where to focus your efforts. However, even small amounts of certain wait types can ......(more)

Microsoft News

Microsoft's Windows 10 Fall Creators Update rollout to kick off October 17 - Microsoft will begin rolling out the Windows 10 Fall Creators Update on October 17. Here's what to expect. ...(more)

Microsoft and Amazon agree to enable Cortana, Alexa communication - Amazon's Alexa and Microsoft's Cortana voice assistants soon will be able to communicate with each other, thanks to a new partnership between the two rivals. ...(more)

Microsoft Begins External Testing of Guest Access For Microsoft Teams - When Microsoft announced Teams, one of the first requests was that the product would allow for guest access. At launch, this feature was not available but Microsoft did say that it would arrive in the near future…but then it never did. Guest access was ......(more)

Microsoft starts rolling out biggest changes in two decades to its Premier customer support program - Microsoft is rolling out big changes to its Premier Support plans and structure, which are meant to better support businesses adding cloud services to their mix. ...(more)

MDX/DAX

Obscure MDX Month: Optimising MDX That Uses The RGB() Function - In the first blog post in this series a few weeks ago I mentioned that calling Excel and VBA functions from MDX came with a query performance penalty. In this post I’ll give you an illustration of this using the VBA function that I suspect is most frequently ......(more)

Internet of Things

Why Data Professionals Should Be Careful of Internet Light Bulbs - Click to learn more about author David Schlesinger. Many of us have rejoiced by having a thermostat for our homes that can be adjusted from a cell phone at work, and we can also check the house temperature from afar. Naturally, being data professionals, ......(more)

Hardware News

Upgrading SQL Server– Top Five Intel Xeon Scalable Processors for SQL Server Usage - On July 11, 2017, Intel formally released their new Xeon Scalable Processor Family of processors for data center usage. This new processor family has a somewhat ridiculous 58 different SKUs in the line. They are broken up into Platinum 8xxx, Gold 6xxx, ......(more)

Western Digital Launches New My Book Duo Storage Systems: 360 MB/s, USB-C, 20 TB - Western Digital has released its new My Book Duo external storage systems for consumers. The new devices run two 3.5” WD Red HDDs in RAID 0 or 1 mode and have capacity of up to 20 TB. The drives come in a new enclosure that uses a USB 3.0 Type-C port ......(more)

Intel Launches Xeon-W CPUs for Workstations: Skylake-SP & ECC for LGA2066 - On the professional side of the CPU space, Intel’s new Xeon Scalable Processor family, based on the Skylake-SP architecture, is suiting up against the new EPYC processors from AMD. Part of Intel’s Xeon-SP launch was a rebranding of their product stack: ......(more)

HA/DR/Always On/Clustering

Automatic Seeding & Compression - (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.) Last week I have talked about the basic concepts behind ......(more)

Avoid Availability Group Database Data Loss: Do not Deploy File Share Witness From DFS Namespace - Cluster Quorum File Share Witness Should Not be Part of a DFS Namespace When deploying AlwaysOn availability groups you may decide to add a vote to Windows Cluster quorum by configuring a File Share Witness. A requirement when configuring that File Share ......(more)

ETL/SSIS/ELT

TIL: SSIS Memory errors - New one today….memory errors from SSIS package execution. Background: I’m a Database engine dude. I’m well versed in how the engine uses memory, buffer pool, mem-to-leave, etc. I teach these things to others around me all the time. I rarely use SSIS ......(more)

SSIS and SSRS upgradation process - With the introduction of new MSBI versions, we get excited with new features and love to be upgraded with latest. But it’s not that easy to upgrade our ecosystem of MSBI applications. Below are few options to do the tech refresh (either version up-gradation ......(more)

DevOps and Continuous Delivery (CI/CD)

The evolution of DevOps - Understanding the impact and expanding influence of DevOps culture, and how to apply DevOps principles to make your digital operations more performant and productive.A few years ago, I wrote that DevOps is the movement that doesn't want to be defined. ...(more)

Creatures of habit, snowflakes, and integration tax - When DevOps was first talked about in Flickr’s seminal ’10 deploys per day’ Velocity presentation in 2009, it was regarded by some as strange and alien to corporate culture. It was the antithesis to the accepted way of doing things, a threat to established ......(more)

Creatures of habit, snowflakes, and integration tax - Why are practices like version control, continuous integration and automated deployment being introduced to application development but left on the shelf when it comes to the database? In search of some answers, Redgate spoke to Donovan Brown, Principal DevOps Manager at Microsoft and DevOps advocate. Also known as The Man in the Black Shirt, his unofficial tagline is #RubDevOpsOnIt. He lives DevOps - here's what he had to say....(more)

Database Design, Theory and Development

Should You Enforce Foreign Key Relationships in the Database? - It’s one of those fierce religious wars that divides families and tears loved ones apart. First, if two tables are related, should we tell SQL Server about it? If SQL Server knows that there’s a relationship, then it can make better decisions when building ......(more)

JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage - The SQL language has one great advantage over procedural, object oriented, and “ordinary” functional programming languages. The fact that it is truly declarative (i.e. a 4GL / fourth generation programming language) means that a sophisticated optimiser ......(more)

Object Orientation, Relational Database Design, Logical Validity and Semantic Correctness - Here's what's wrong with last week's picture, namely:"In my experience, using an object model in both the application layer and in the database layer results in an inefficient system. This are my personal design goals:- Use a relational data model for ......(more)

Conferences and Events

Livestreaming the Redgate Hub for the US - The Livestream launch for the Redgate Community Hub is next Wednesday, Sept 6, 2017. To ensure that our customers and friends in the US and the rest of the this hemisphere can enjoy the festivities, we are broadcasting at 8pm GMT. Register today and ......(more)

Presenting a New Training Class on Architecting a Data Lake - I'm very excited to be presenting an all-day session on Architecting a Data Lake on Friday, October 13, 2017. It is a pre-conference session as part of the annual SQLSaturday event in Charlotte, NC -- which means the price is a bargain. The early bird ......(more)

The Game Night Poll - This year we’ve handed off Game Night to the PASS organization and it’s an official event. Volunteers from the community will help setup, manage, and tear down the event. Kevin Hill is taking the lead, but if you’re willing to help run the night, let ......(more)

My TOP(10) Reasons to Attend PASS Summit - I’ve been attending PASS Summit since 2003 and have missed only one year in that time. This year, I am honored once again to speak at the summit. I have a 2.5 hour session on indexing for beginners. I think that will be enough time to give the audience ......(more)

Performance Tuning? Plan the Work, Work the Plan. - In our new 2-day hands-on lab course, Performance Tuning By Example, students have a series of performance tuning goals they have to accomplish. For example, in one scenario they have to fix the performance problem only using indexes, and in another ......(more)

PASS Summit Game Night poll - Hello fellow SQL Server people! I have volunteered to coordinate some of the activities for the 2017 Game Night at PASS Summit in Seattle on November 2, 2017. Please take a second to help me compile a “top 10” list of games that PASS can invest in ......(more)

Computing in the Cloud (Azure, Google , AWS)

Microsoft Azure – How to create a virtual machine using PowerShell – Part 2 - Hello friends, you can find the post at below link – https://social.technet.microsoft.com/wiki/contents/articles/40010.how-to-create-a-virtual-machine-in-microsoft-azure-using-powershell-part-2.aspx Tagged: Azure, Microsoft Azure, Powerhsell, Powershell, ......(more)

Six Ways IT Teams Can Be the Guardian of Their Companies' SaaS Data - It's not just the cloud service provider's responsibility to ensure your SaaS applications are safe. Here are ways IT teams can be the guardians of their SaaS data. ...(more)

Microsoft Azure – How to create a virtual machine using PowerShell – Part 1 - Hello friends, you can find the post at below link – https://social.technet.microsoft.com/wiki/contents/articles/40000.how-to-create-a-virtual-machine-in-microsoft-azure-using-powershell-part-1.aspx Tagged: Azure, Microsoft Azure, Powerhsell, Powershell, ......(more)

What I Learned Today - Configuring tempdb in RDS - Today I learned that you can configure tempdb in RDS! One of the things we DBA’s do is configure tempdb to have multiple data files (if needed). Not going to go into the depths of why in this post, but adding multiple data files can help alleviate contention ......(more)

Columnstore Indexes

Columnstore Indexes – part 111 (“Row Group Elimination – Pain Points”) - Continuation from the previous 110 parts, the whole series can be found at http://www.nikoport.com/columnstore/. The topic of the Segment Elimination (aka Row Group Elimination) is something that I keep on blogging on through all the years, but given ......(more)

Columnstore Indexes – part 110 (“The best column for sorting Columnstore Index on”) - Continuation from the previous 109 parts, the whole series can be found at http://www.nikoport.com/columnstore/. In this blog post I wanted to share my thoughts & my script that I am using for picking the best column for achieving the best possible Segment ......(more)

Career Growth

“I faked my resume… now what?” (video with transcript) - This week’s question from a reader: “How do I deal with interview questions asking about real-world scenarios when I’ve faked three years of experience on my resume?” Hear my answer in this 14 minute video. No time to watch right now or read the transcript ......(more)

Azure SQL Database

Default compatibility level 140 for Azure SQL databases - As of this writing, the default compatibility level for new databases created in Azure SQL Database is 130. Very soon, we’ll be changing the Azure SQL Database default compatibility level for newly created databases to 140....(more)

Azure SQL Data Warehouse and Data Lake

Taking a byte out of U-SQL - I recently had a requirement to combine a set of approx. 100 CSV files into a single file and encountered an interesting problem along the way. The files all had the same simple structure which was just 2 columns. I thought was going to make the job ......(more)

Analysis Services / BI on the MS Stack

Data Channel Interview - 08 - Leila Etaati on Machine Learning using Microsoft BI - Dear All, Very happy to share the 8th interview of #DataChannel. Dr. Leila Etaati MVP, MCT from New Zealand discusses Machine Learning using Microsoft Business Intelligence tools in this interview. Leila gives us an insight into Machine Learning process ......(more)

AI/Machine Learning/Cognitive Services

Does AI Mean More Jobs? - AI has contributed to many industries, specifically customer care, and it is possible that it will be able to create more jobs, not less. Read More ...(more)

Administration of SQL Server

SQL SERVER – How to Install SQL Server Management Studio (SSMS) From Command Line? - SQL Server Management Studio (SSMS) has been removed from the product release cycle. Now SSMS is shipped as a free tool and doesn’t need to have set up media to install. Earlier I wrote a blog which you can read below. SQL SERVER – Installing SSMS 2016 ......(more)

Upgrading SQL Server– Database Experimentation Assistant - Another useful, if somewhat complicated, free tool from Microsoft is the Database Experimentation Assistant (DEA). Microsoft released DEA version 2.1 on July 25, 2017, with many new improvements over the earlier preview versions as detailed here. Here ......(more)

First Responder Kit Release: Fully Hand Automated - Did you know there are more issues closed in this FRK than there are planets in this solar system? Special thanks to Pluto for making that statement possible, and all the other planets that didn’t form. Also thanks to @mches for making the FIRST EVER ......(more)

Determining Which Indexes to Compress Among Thousands - In a shared in environment you may have hundreds of databases with the same schema but depending on the data loaded into them not all of them will benefit from being compressed so you don’t want to incur the overhead of compression on all the of the data....(more)

Restores using Invalid Backup Default Locations - Recently I was looking through the error log on one of my test machines and I spotted some unusual errors:...(more)

SQL Server Query Metadata - SQL Server Query MetadataPop quiz, how you determine the metadata of a query in SQL Server? For a table, you can query the sys.schemas/sys.tables/sys.columns tables but a query? You might start pulling the query apart and looking up each column and its ......(more)

Upgrading SQL Server– SQL Server 2016 Upgrade Considerations - One somewhat obscure issue you might encounter when upgrading from a legacy version of SQL Server to SQL Server 2016 or later is a change to how some data type conversions are handled. Microsoft describes it like this: “Microsoft SQL Server 2016 and ......(more)

Upgrading SQL Server– Microsoft Data Migration Assistant 3.2 - One useful tool that you should definitely take advantage of during an upgrade and migration effort is the Microsoft Data Migration Assistant (DMA), which replaces the old SQL Server Upgrade Advisor. Microsoft released the new DMA 3.2 on August 25, 2017. This ......(more)

An Expensive Reason To Avoid AGs In Azure - Cash Rules Most people, when they get through paying for Azure, and SQL Server Enterprise Licensing, are left with a hole in their wallet that could only be filled with something that says “Bugatti”, and has a speedometer with an infinity sign at the ......(more)

Responsible Log Growth with dbatools - I love dbatools, and I’ve been trying to explore the various cmdlets over time, both to practice my PoSh and see if there are easier ways of accomplishing some tasks. Recently I was browsing the dbatools.io site to see what’s changed and noticed a new ......(more)

Choosing the right server memory for restore and recovery of memory-optimized databases - Recovering database with memory-optimized tables involves hydrating the contents of checkpoint files (data/delta files) into memory and then replaying the tail of the log (see this link for more details). One of the important difference between disk ......(more)