The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

In the database industry, there are a wide range of skills, mostly of which masquerade under the overarching job title, 'DBA'. Curiously, there are some highly-skilled DBAs who are as likely to be able to design a database, or create business functionality within it, as they are to turn into ostriches. On the other hand, I've met a few talented database professionals who would struggle to perform a point-in-time restore to save their lives.

At one end of the spectrum are the Database Developers and at the other end are the Operations Database Admins. We're all somewhere along that spectrum, and many of us also diverge greatly in our training and work experiences. I've met DBAs who started out as teachers, rock musicians or roadies, nurses, army veterans, hairdressers, psychologists, lawyers – the list is endless. There is something about having a range of previous experience that fires the essential curiosity, stubbornness and tenacity that DBAs need.

I remember appointing a brilliant DBA who had previously worked as a specialist policeman in the elite anti-riot unit known as the 'riot squad'. He was a bodybuilder and wrestler, and was probably no stranger to the Vulcan Nerve Grip. Although he pined for the hurly burly of containing a good violent demonstration, he was the master of any DBA task and calm in any crisis.

Around the same time, I also appointed as our Ops Manager a tall, well-built, cockney who impressed me at his interview by explaining how to avoid any tiresome clamping and parking penalties using a mixture of sheer cunning and a crowbar. He was so talented in a range of network skills that I politely overlooked a five-year blank period in his CV.

My IT Management team, tall and menacing in their shades and bursting out of their dark suits regularly popped into one of the local pubs after work. Never before or since have I experienced that quite uncalled-for frisson of fear around us was we walked in; the sudden hush. Until they got to know us, several publicans would, unnecessarily, buy us extra complimentary rounds to ensure our continued goodwill. We never had any trouble in our IT department. nor from the Business generally, come to think about it.

While there is nothing whatsoever wrong in single-minded pursuit of a single career in the database industry, our profession is enriched by the variety of skills, experience and understanding that come to us from career change. Although training and experience in databases is essential, nothing beats initiative, clear thinking and tenacity.

Phil Factor.

Phil Factor 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.

Vendors/3rd Party Products

SQL Clone and databases protected with TDE - SQL Clone is designed to make it easy to provision copies of a database for development and test environments in seconds. Often, these ‘clones’ are copies of production databases, which contain sensitive data that needs to be protected from unauthorized access....(more)

Automating DacPac deployments using the SQL Compare command line and PowerShell - How to use PowerShell and the SQL Compare command line to automate the generation of a synchronization script, to build or upgrade a database, starting from a DacPac....(more)

Extending DevOps practices to SQL Server databases - In this free webinar, Steve Jones and Arneh Eskandari show how Redgate’s Database DevOps solution works with Visual Studio Team Services to improve your database delivery process. Register now....(more)

Data masking survey and prize draw - The Foundry team at Redgate know that managing sensitive and regulated data can be both challenging and problematic. They want to solve these problems, but first need your help to better understand them in context. What do you find frustrating? What would save you time? Please fill out this short survey to let them know, and as a thanks for your time, at the end you'll be entered into a draw to win a $100 Amazon gift card....(more)

T-SQL

Fixing Hot-Key issue in SSMS in five steps - Sometimes SSMS "forgets" some hot-key combinations, due to code sharing and reusability with other Microsoft development products. Slava Murygin offers a solution....(more)

Inline Table Valued Functions: Parameter Snorting - Parameter Snorting goes beyond ordinary parameter sniffing, where SQL at least tried to come up with a good plan for something once upon a compile. In these cases, it just plain gives up and throws a garbage number at you. You’ve seen it happen countless times with Table Variables, Local Variables, non-SARGable queries, catch-all queries, and many more poorly thunked query patterns....(more)

Using Trace Flag 2453 to Improve Table Variable Performance - Should you always use trace flag 2453 or OPTION (RECOMPILE) if you are using table variables? Maybe. But I’m leaning towards “it depends”. Microsoft says the “trace flag must be used with caution because it can increase number of query recompiles which could cost more than savings from better query optimization”....(more)

Follow-up #1 on leading wildcard seeks - Aaron Bertrand's solution for getting an index seek for a leading wildcard requires triggers to deal with maintaining the search fragments. Here's he demonstrates how those triggers work....(more)

Does Query Store Pre-Allocate Space - Query Store tables are just system tables. They have a limit on how big they can grow (100mb by default), but that space isn’t going to be pre-allocated in any way. The space will just get used as and when it’s needed, just like any other system table. However, don’t take my word for it, let’s prove that....(more)

Identifying Existence of Intersections in Intervals - Identifying the existence of intersections in intervals is a classic task where, given a table with a set of intervals, you need to check whether any intersections exist. Itzik Ben-Gan explains how to do it....(more)

Understanding Left vs. Right Partition Functions (with Diagrams) - You’re designing table partitioning, or you want to make a change to an existing partition function. It’s critical to understand the difference between how “left” and “right” partition functions behave, but the documentation is a bit confusing on this topic....(more)

Tech News : General Interest

Hacker hijacks thousands of publicly exposed printers to warn owners - The printers were forced to print rogue messages that claimed they were now part of a botnet....(more)

Most of the web really sucks if you have a slow connection - I expected the internet in rural areas too sparse to have cable internet to be slow, but I was still surprised that a large fraction of the web was inaccessible...When I measured my connection, I found that the bandwidth was roughly comparable to what I got with a 56k modem in the 90s....(more)

SQL Server on Linux

ODBC Driver 13.1 for Linux Released - Microsoft ODBC Driver 13.1 for Linux (Ubuntu, RedHat and SUSE). The new driver enables access to SQL Server, Azure SQL Database and Azure SQL DW from any C/C++ application on Linux....(more)

Software Development

User Acceptance Testing and the Application Lifecycle - User Acceptance Testing (UAT) is an important part of the development process. If carried out as early as possible and as regularly as possible, it not only alerts the development team to aspects that don't yet meet the requirements of the users, but also gives governance a better idea of progress. If UAT is delayed, defects become expensive and troublesome to fix. Sophia Segal gives an experienced summary view of UAT...(more)

Replication

Replication Setup -FIX- Unable to list directory contents - You're trying to setup SQL Server Replication on a server, and it fails. Looking thru the error message you find this: "... Destination path .... is not valid. Unable to list directory contents..."...(more)

R Language

DPLYR Tutorial (with 50 examples) - dplyr is a powerful R-package to manipulate, clean and summarize unstructured data. In short, it makes data exploration and data manipulation easy and fast in R....(more)

RevoScaleR package for Microsoft R - RevoscaleR Package for R language is package for scalable, distributed and parallel computation, available along with Microsoft R Server (and in-Database R Services). It solves many of limitations that R language is facing when run from a client machine to perform computations on larger datasets....(more)

PowerShell

Setting up Azure Disk Encryption for a Virtual Machine with PowerShell - Having opted to use Azure Disk Encryption for her virtual machines in Azure, rather than Storage Service Encryption, Melissa Coates now needs the help of PowerShell to configure this encryption for an existing VM....(more)

Opening the PowerShell ISE from Explorer - This is a cool productivity trip, and one that I ran into by accident. I had heard that I could launch a command prompt by typing cmd in the address. That works, and it’s cool. It even works with ConEmu, which is my default command window....(more)

Building a GitHub Dashboard using PowerShell, AzureStorageTable, AzureFunction, and PowerBI - Steve Lee shares and explains the code behind his PowerShell Community Dashboard....(more)

PowerPivot/PowerQuery/PowerBI

Dynamically Changing Shapes in Power BI - Recently someone had a question if it was possible to change the color of a shape depending on the value of a measure. Imagine for example a dashboard with the picture of a database server. If up-time is good (availability >= 95%), the server would color green, if it was bad it would color red. ...(more)

Performance Tuning SQL Server

What Does “monitorLoop” mean in the Blocked Process Report - The monitorLoop is just a number that starts at zero when the server restarts and increments by one each time the deadlock monitor runs....(more)

Using Extended Events in Azure - There are some subtle differences on how you write the T-SQL between SQL database (Azure) and your locally installed SQL Servers. Here I wanted a simple event session to find deadlocks and general T-SQL activity....(more)

Statistics and Data types, What can go wrong? - If we haven’t changed anything in our databases (or model), auto create statistics should be on by default which means SQL Server will create automatically statistics whenever it thinks that information would be beneficial to generate a query plan. This sounds like a treat, right? Sure, it is nice not to have to think which statistics to create and so on, but like everything, it can have a dark side....(more)

Should I Learn Fulltext Indexing? (Dear SQL DBA Episode 29) - A lot of us get the impression that full-text search is designed to handle “full wildcard” searches, probably just because of the name. “Full-Text Searches” sounds like it means “All The Searches”. But that’s not actually what it means....(more)

Internet of Things

Security and Privacy Guidelines for the Internet of Things - My guess is that everyone knows that IoT regulation is coming, and is either trying to impose self-regulation to forestall government action or establish principles to influence government action. It'll be interesting to see how the next few years unfold....(more)

IoT Hub vs. Event Hub - There can sometimes be confusion, in IoT scenarios, between IoT Hub and Event Hub, as they can both be valid choices for streaming data ingestion....(more)

DBA Tools

What are the SDU Tools? - SDU Tools is a toolkit maintained by Greg Low that is shipped as a single small schema. It contains utility functions and procedures. Many of the functions are useful when performing troubleshooting but others are just general purpose functions....(more)

Database Design, Theory and Development

Explicitly naming clustered index key columns in non-clustered indexes – when and why - Always define the index explicitly with the columns needed (in the key, in INCLUDE, wherever they are REQUIRED). If a column is not needed by that query then do not explicitly name it....(more)

Data Mining/Data Analysis

Consumption Based Architecture for Modern Data Analytics - Consumption Based Architecture is based on the following key concepts: 1) Keep the data close to the source, 2) Data interfaces should be easy to use, 3) Modern, in-memory tools make this possible....(more)

Data Access / ORMs

Deprecated SQL Server Data Access Technologies - Dan Guzman reviews common data access technologies for native (unmanaged) Windows applications that are explicitly identified as unsupported, deprecated, or may have a limited future along with remediation considerations. ...(more)

Computing in the Cloud

Create a Virtual Machine in the Azure Cloud - Virtual machines can be provisioned in the cloud in much the same way as on a local VMware or Hyper-V server. The only difference is that you don’t have any real control over the host operating system or hypervisor, and configuration must be done through the cloud OS. The easiest way to provision a simple VM is to use a readymade template....(more)

Deciding on Encryption at Rest for an Azure Virtual Machine - This post covers two options for implementing encryption-at-rest capabilities in Azure virtual machines (VMs): Storage Service Encryption (SSE) and Disk Encryption....(more)

Career Growth

How I Handle Intro Calls with Consulting Prospects - Having done a free 20-30 minute sales call for years, Brent Ozar describes how the process that works for him....(more)

Analysis Services / BI on the MS Stack

Dynamically Changing Shapes in Power BI – Alternatives - Koen Verbeeck explains step-by-step a cool trick to solve the 'changing images' problem....(more)

Disproportionately Popular & Unpopular Outliers by Region, Demographic, etc. - I haven’t done a DAX post in a long time, so I was thrilled to get some inspiration from an unlikely source – an Orbitz infographic designed to sell expensive vacations!...(more)

Finding Out Which MDX Calculations Are Being Evaluated By Your Query In Analysis Services Multidimensional, Part 2 - In part 1 of this series I showed how you can use Profiler to find out which MDX calculations are being evaluated when a query runs on SSAS Multidimensional. In this post I’ll show a practical example of why this is so useful: a situation where SSAS evaluates a calculation that isn’t needed by a query....(more)

Administration of SQL Server

Managing ‘Noisy Neighbors’ in SQL Server 2014 - If you’ve been managing databases for any length of time, you’ve probably had to contend with the “noisy neighbors” scenario...several applications, each with their own database, residing on a single instance of SQL Server, which don’t share the resources of the server nicely. ...(more)

Import and analyze IIS Log files using SQL Server - How to load log files generated by IIS into SQL Server table using BULK INSERT commands, and analyze the date using T-SQL....(more)

Increasing the Number of SQL Server Error Logs – My Automation Journey – Part 2 - In my last post, I discussed how to increase the error logs through the GUI and explained the back end stored procedure that is used to set the value in the registry. In this post, I will cover a technique to automate this and dig a little into Windows Management Instrumentation (WMI)....(more)

Exporting tables from SQL Server in json line-delimited format using BCP.exe - Line-delimited JSON is one common format used to exchange data between systems and for streaming JSON data. SQL Server can be used to export content of tables into line-delimited JSON format....(more)

Let NonAdmins Get Logins - How can we allow some users to see the logins on a server without being a sysadmin or securityadmin? This was in support of a migration effort, so users needed read rights without being able to change anything....(more)

How the SQL Agent Job schedule_uid broke my heart, and my jobs! - When you script out a SQL Agent Job, the job schedule will have a schedule_uid parameter. With UID’s the chances of a collision are low, but if you do a fair amount of copying jobs between SQL Servers there’s a good chance that eventually you'll end up with another job schedule with the same schedule_uid. Any other jobs that are using that schedule_uid will start using the new schedule. That’s what happened to us (more than once)....(more)

Creating SQL Containers from a Dockerfile - Need a new SQL instance spun up for testing? Having a pre-built custom image ready will allow you to do that very rapidly and the simplest way to build a custom image is from a dockerfile....(more)

SQL Server Default Configurations that you should Change - Who here takes the time to adjust default values for SQL Server Configuration after installing it? Unless your company has specific Post-Installation Tasks procedures/document, SQL Server configurations are left as is until an issue comes along....(more)