The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

This year, PASS celebrated the 20th PASS Summit. As it has been many times, this year’s Summit was held in Seattle. PASS Summit has visited other locations such as Orlando and Charlotte, but, at least to me, it feels like home when it’s hosted in Seattle.

For those of us who have participated for many years – or who have made connections with the community through social media and attended for the first time – it feels like a big family reunion. From the day of arrival to the final session on Friday afternoon, this is the best week of the year for me (well, except for that trip to Hawaii).

Most of the people attending PASS Summit are there for the sessions. That was the case for me in the beginning. Now, I have quite a few responsibilities between speaking, Redgate commitments, and Women in Tech activities. I did manage to make it to four sessions and will enjoy watching many of them later when streaming is available.

If you were not able to make it to PASS Summit 2018, what did you miss? The Summit kicked off with the v.20 Celebration, aka Welcome Reception, in the big ballroom on Tuesday evening. This year it featured a slideshow with conference photos from the past two decades and the timeline since 1999. In addition to food and beverages, you could play video games or get a selfie with a Star Wars character. Typically, PASS has given out one beverage ticket per person, but this year you could get a beer without a ticket thanks to sponsors like Redgate!

Wednesday morning began with the keynote presentation by Rohan Kumar talking about SQL Server 2019 and the Azure data related news. I remember when the keynote speakers were 100% male, but this year most people demonstrating the features were women! The highlight for many of us was when longtime community member and now Microsoft employee, Patrick LeBlanc (@patrickdba), presented SSRS in Power BI.

After the keynote, the sessions began in smaller rooms throughout the conference center. The most challenging aspect of PASS Summit is figuring out which presentations to attend when there are so many compelling sessions happening at the same time. Fortunately, you can buy a USB of the session recordings even if you didn’t attend. I recommend it!

Thursday had two highlights for me. The first was the keynote speech of Executive Director and VP of Finance, Wendy Pastrick (@wendy_dance). Wendy managed to make the financial report fun by singing it along to the tune of “I’ll Will Survive.” The second was the ever-popular Women in Technology luncheon. This year, Lauri Bingham talked about how she helped start and grow an excellent program to support gender diversity at T-Mobile.

There is no keynote on Friday, but sessions continue until 4:45 PM. The Speaker Idol final was the most exciting part of the day. There were five finalists who all did a fantastic job – each giving a complete session in five minutes or less. This year, Rob Volk (@sql_r) won by giving his “A Modern DBA Career” session. Congratulations, Rob!

Wow! That’s a lot, but I didn’t even mention so many cool things like dinners and vendor parties each evening, activities with like-minded friends (#sqlkaraoke anyone?), the Community Zone, Microsoft Data Clinic, book launches, and so much more! It’s spectacular and overwhelming at the same time.

Finally, after a week of little sleep and trying to squeeze in everything possible, it was time to say goodbye. So long, #sqlfamily. See you next year!

Kathi Kellenberger 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

Python in Power BI - Geocode using Azure Maps (Webinar Preview) - Many maps in Power BI require latitude and longitude coordinates to display geography, but many data sources only contain place names. Geocoding is the process of obtaining latitude and longitude for a given location. Geocoding with Power BI is nothing ......(more)

Creating a data culture can transform risk calculation for insurers - James Boother from Redgate partner COEO will be joining Redgate for a live video session to discuss the opportunities Data Mastery presents insurers. They’ll be discussing seven steps some organizations are already taking to predict future risks with even greater certainty....(more)

SQL in the City Streamed December 2018 - The theme for December 2018's SQL in the City Streamed is Your Journey to Compliant Database DevOps. The schedule includes sessions designed to broaden your skillset, support your ongoing learning, and keep you up-to-date with what’s new in the industry and at Redgate....(more)

Accomplishing DevOps with Databases - experiences from the wild… - Have you heard the recent industry noise about how people are applying DevOps principles to databases and wondered if it is just hype? MVP Hamish Watson demonstrates how the noise is based on facts, and how using things like Source Control, Continuous Integration and Continuous Delivery will make a huge (positive) difference to how you deploy changes to your SQL Server estate....(more)

What we learnt at PASS Summit - PASS President Grant Fritchey will be joined by fellow Microsoft MVPs Kendra Little and Steve Jones to discuss their highlights and learnings from PASS Summit 2018, the largest Microsoft Data Platform conference....(more)

Vendors/3rd Party Products

Building Better Test Data with SQL Provision - In this article, Microsoft MVP Steve Jones shows how SQL Provision can help create test data that complies with regulations that prohibit the sharing of sensitive or personal data, but still looks and behaves like the real deal. ...(more)

Use SQL Server? We’d 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. As a thank you for your time, they will be giving away one $100 Amazon gift card to one randomly selected participant who completes the survey by December 7, 2018. ...(more)

T-SQL

In Pursuit of Batch Mode on Rowstore - In her excellent blog post entitled “Batch Mode Hacks for Rowstore Queries in SQL Server“, Kendra Little b|t pays homage to Itzik Ben-Gan, Niko Neugebauer, and others. The solutions she details......(more)

Binary to Decimal via STRING_SPLIT - Colin Stasiuk posed an interesting question on Twitter recently:OK #SQLHelp let's see what you can do :) hehehe. I need to convert a binary string to it's hex value ...(more)

Dates and Times in SQL Server: DATEADD() - We are now in the home stretch of the long-running series about dates and times in SQL Server and Azure SQL Database. This week we look at one of my favourite T-SQL functions when it comes to dates and times: DATEADD(). Syntax As with similar functions, ......(more)

Security news and thoughts

How to Spot Insider Threats Before They Wreak Havoc - Too many organizations today spend millions of dollars fortifying their perimeters to keep attackers out of their network, but fail to pay sufficient attention to the malicious users already inside. In fact, insider threats actually represent 60% of ......(more)

Threat Actors Are Exploiting IT Networks to Attack Energy, Utilities - A report by cybersecurity vendor Vectra found that threat actors often begin lengthy, deliberate attacks by compromising a company’s network. ...(more)

R Language

Those “other” apply functions… - So you know lapply, sapply, and apply…but…what about rapply, vapply, or eapply? These are generally a little less known as far as the apply family of functions in R go, so this post will explore how they work. rapply...(more)

PowerPivot/PowerQuery/PowerBI

Creating Calculated Columns Using DAX - In this article, Andy Brown shares his knowledge of how to create calculated columns in tables, using Power BI as the host software (all of the formulae shown would work equally well in PowerPivot or SSAS Tabular). This article is designed to be the first in a series of articles which will show you how to master programming in DAX. ...(more)

Power BI Dataflows, and Why You Should Care - Microsoft recently released a new feature in Power BI called “Dataflows”. You may have seen it pop up in a recent release of Power BI Desktop under Home\Get Data\Power BI Dataflows (beta) as shown below....(more)

Find the Most Expensive Columns for Performance Tuning, Bookmarks, and more with Power BI Helper Version November 2018 - We are excited to share you the November 2018 version of Power BI helper with many interesting features. With this version, you get the list of all pages and list of all bookmarks. You can find all bookmarks related to a page. You can also through this ......(more)

Create a Planning View of Your Outlook Diary in Power BI - I was chatting to my wife the other day discussing all the things we want to do next year. It has long been a pain in the butt to get a high level (big picture) view of your Outlook diary to see what is happening (think forest, not trees). In the past ......(more)

Performance Tuning SQL Server

More information in execution plans - Troubleshoorting performance issues can be hard. Having as much information available at our fingertips does not magically make this easier but it does at least save us the burden of having to collect that information from other places. That’s why I ......(more)

Bad Query Performance Tips Rebutted - Once upon a time, someone, somewhere, wrote a list of bad query performance tips and they’ve been copied all over the internet, over and over. Even worse, sometimes the tips are copied or edited incorrectly, making a bad suggestion even worse. Can we please, stop copying this list? I’ve tried to poke holes in this list before, one point at a time. This time, I’m just taking it all on....(more)

Understanding log buffer flushes - You’ve probably heard many times before that SQL Server provides a guarantee for ACID transaction properties. This article focuses on the D part, which of course stands for durability. ...(more)

Diagnostic Data for Synchronous Statistics Update Blocking - Consider the following query execution scenario: You execute a SELECT query that triggers an automatic synchronous statistics update. The synchronous statistics update begins execution and your query waits (is essentially blocked) until the fresh statistics are generated... ...(more)

High CPU? Implicit Conversion? - At PASS Summit 2018 and SQL Saturday Oregon 2018, Angela Henry aka @SqlSwimmer on Twitter gave a presentation on Data Types Do Matter. Most of us have programmed stored procedures adding parameters without looking at the original datatype. Shortcuts are taken and the correct data type is not chosen, it will not matter. But will it?...(more)

Columnstore Indexes and Key Lookups–The Worst - Key Lookups are one of my least favorite SQL Server execution plan operators. This is where for each record in an index seek, SQL Server goes back to the clustered index and looks up a record. Generally, we either live with this condition (for a very small number of rows) or we fix it by adding columns directly or adding included columns to our nonclustered index....(more)

NoSQL

How to validate JSON Data before you import it into a database - If you are, as you should be, checking JSON data in a whole lot of files before you import them into your database, you would do well to use JSON Schema, because you can run a number of checks such as regex checks that can’t be done any other way, and it is usually possible to detect bad data...(more)

Microsoft News : General Interest

As Microsoft moves more blogs off its MSDN and TechNet sites, here's how to stay current - Finding Microsoft-authored product and strategy information is becoming increasingly challenging. Here are a few resources that might help. ...(more)

HA/DR/Always On/Clustering

Kubernetes, Swap and the VMware Balloon Driver - Kubernetes requires to disable the swap file at the OS level. As stated in the 1.8 release changelog: The kubelet now fails if swap is enabled on a node. Why disable swap? Turning off swap doesn’t mean you are unable to create memory pressure. Why disable ......(more)

Deploying SQL Server Availability Groups in Kubernetes - In this blog post, we’re going to work on deploying a SQL Server Availability Group in a Kubernetes Cluster in on-premises virtual machines. I’m going to walk you through the process as it’s documented by Microsoft at this link here....(more)

DevOps and Continuous Delivery (CI/CD)

Two (or More) Kinds of Developers - I’ve made statements about “two kinds of developers” for years. These statements are false inasmuch as all generalizations are false. The statements are not designed to be truisms. They are designed to make people think. Last week – while presenting ......(more)

The Benefits of Adopting Compliant Database DevOps at PASS - The GDPR presented a challenge for PASS - a community of over 250,000 data professionals with almost 300 Local Groups around the world and 24 Virtual Groups. This whitepaper shows how PASS were able to introduce compliance by default with the new data privacy regulations by using Redgate's solutions to apply DevOps practices to the database. ...(more)

2019 State of Database DevOps – Take the survey! - Database development practices are more integral than ever to DevOps success. How are we saving time, automating processes, deploying more frequently, all whilst keeping business critical data secure? Take part in the survey for an advanced copy of the research report, and a chance to win a $250 Amazon voucher. ...(more)

DBA Tools

Shortcut: Set SQLCMD mode for all new query windows in SSMS - SQLCMD mode changes how queries are executed in SQL Server Management Studio (SSMS). When using this mode, you can work with options that aren't normally part of SQL Server T-SQL scripts....(more)

Database Design, Theory and Development

Data Virtualization Defined: How it Helps Organizations Succeed - Data Virtualization (DV) is unlike traditional Data Integration, where change must be made on multiple layers; Data Virtualization makes change easy for the business as new requirements and sources can be integrated and changed rapidly. The Data Management ......(more)

Data Science

Data Wrangling is a Problem - Data Wrangling is bad. Yet we all do it, every single day. In a nutshell, Data Wrangling , also known by the more technical term of “data munging,” is the process or transforming data from one shape into another ......(more)

Data Privacy, Complianace, and GDPR

SQL Server Auditing for HIPAA and SOX – Part 4 - Regulations may require that you audit exactly who does what in your databases. Robert Sheldon explains how SQL Server can help you meet those requirements with its built-in auditing capabilities....(more)

Build a Culture of Trust to Tackle Data Privacy - The raging debate over privacy has only gotten bigger in the wake of GDPR. Trust is now a competitive factor. How do businesses earn it? How do they keep data open, free and protected? The manifold issues ......(more)

New Whitepaper: Data Protection and Compliance During Provisioning - Data privacy and protection is a large concern across all organizations and knowing how to best approach it can be daunting. Grant Fritchey unpicks US legislations HIPAA and SOX to uncover exactly what is required of you and your team to ensure compliance. Download your free copy. ...(more)

Computing in the Cloud (Azure, Google , AWS)

Geospatial analysis with Azure Databricks - A few months ago, I wrote a blog demonstrating how to extract and analyse geospatial data in Azure Data Lake Analytics (ADLA) (here). The article aimed to prove that it was possible to run spatial analysis using U-SQL, even though it does not natively support spatial data analytics. The outcome of that experience was positive, however, with serious limitations in terms of performance....(more)

Big Data

SQL Server 2019 Big Data Cluster on Azure Kubernetes Service - At the Microsoft Ignite 2018 conference back in September Microsoft released SQL Server 2019 for public preview, and I wrote two short blog posts about it. What Microsoft also announced was SQL Server 2019 Big Data Clusters, which combines the SQL Server database engine, Spark, and HDFS into a unified data platform! Yes, you read that right: SQL Server, Spark, and Hadoop right out of the box ...(more)

Azure SQL Managed Instance

Modify Azure SQL Database Managed Instance using Azure CLI - Azure SQL Managed Instance is fully-managed SQL Server Database Engine hosted in Azure cloud. With Managed Instance you can easily add/remove cores associated to the instance and change the reserved size of the instance. You can use Azure CLI to easily ......(more)

Azure SQL Managed Instance – Migrate a Database Using Backup and Restore - If you have run through my last Managed Instance blog post, you have a Managed Instance at your disposal. The PowerShell script for creating the network requirements also contains steps to create an Azure VM in a different subnet in the same VNet. Unless you have a site-to-site VPN or Express Route between your on-prem environment and Azure, you will use this VM to connect to your Managed Instance....(more)

Azure SQL Data Warehouse and Data Lake

New Features for Workload Management in Azure SQL Data Warehouse - I was lucky enough to attend the PASS Summit in Seattle last week and was therefore able to see some early demos of some new features coming to various Azure products. One of the key new releases that caught my eye was the implementation of Importance for workload management in Azure SQL Data Warehouse. ...(more)

When Should We Load Relational Data to a Data Lake? - This is a question I get fairly regularly these days: Should we extract relational data and load it to a data lake? Architecture diagrams, such as the one displayed here from Microsoft, frequently depict all types of data sources going thru the lake: For ......(more)

Analysis Services / BI on the MS Stack

20 Years Of Analysis Services - Today marks the 20th birthday of Analysis Services: it was released (as OLAP Services) on November 16th 1998. There’s a celebratory blog post and video over on the Power BI blog here: https://powerbi.microsoft.com/en-us/blog/analysis-services-is-20-years-old/ I’m ......(more)

AI/Machine Learning/Cognitive Services

New Development Feature for Azure Stream Analytics - Gaining insights from our data, especially in real-time is an important part of any business. Today I’d like to talk about some new development options for Azure Stream Analytics. If you’re not clear on what Azure Stream Analytics is, it’s a fully managed ......(more)

Examining Microsoft’s Data, Artificial Intelligence and Machine Learning Announcements in the Light of Day - Now that we have some distance from Ignite, experts weigh in on which of Microsoft’s announcements around data, artificial intelligence and machine really stand out--and why. ...(more)

AI Platforms: The Next Step in Artificial Intelligence - The use of Big Data has continued to grow and mature, with some organizations reaping considerable rewards. The processing of Big Data has recently advanced to a new level of evolution, in the form of AI (Artificial Intelligence) platforms. AI platforms ......(more)

Administration of SQL Server

Database Migration Guide – October 2018 Updates - What’s new with the latest updates? The latest updates to the Database Migration Guide include: Launched a new and enhanced user interface that makes it easier to specify your migration scenario and that surfaces the most commonly used guides, which ......(more)

TSQL to find Failed Jobs - It can be frustrating to find recently failed jobs in the job history in SQL Server Management Studio. A quicker way to do it is to just run a query to see what jobs have failed recently. The following query shows the jobs that have failed in the last ......(more)