The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

"Today's database professionals must be engineers, not administrators"

That's a quote from an interview at O'Reilly on Database Reliability Engineering. I don't want to quibble about whether those of us working in technology are engineers in name or not, but in function, I do think the world is changing to one where we need to do more than be caretakers of systems. We need to be actively involved in ensuring we have very reliable, well-designed systems that use the best practices and patterns of highly available and reliable database systems.

There is advice to learn and work closer with developers. I like that, and I do think the idea of learning to script and automate tasks is important, perhaps more important all the time, for data professionals. Some of the advice to look towards the quick moving, limited lifetime of many software components today doesn't make sense. A database, at least some parts of a database, don't fit within that model. There are valid reasons why a datastore must provide some stability and persistence for an application and won't fit within a model of multiple systems. Or at least not practically within a model.

However, there is good advice in the piece to learn more about different types of database services and patterns, and consider other ways of implementing datastores other than a RDBMS. I wouldn't necessarily abandon a RDBMS for some NoSQL store just because developers think it's easier, but I would consider whether I actually need to very tight coupling between different parts of the database and complete consistency. I think there are a fair number of domains where a CQRS pattern or some distributed store would work well. The move to microservices might be an enabler for your business if you consider the advantages for OLTP type transactions.

The flip side of all the power that many stores provide is that many also make reporting and aggregating information more difficult. I would venture that in many cases, a data warehouse (perhaps even a RDBMS-based or columnar store) is necessary, along with the ETL process necessary to keep it up to date. These aren't simple processes, and take resources to build. For most of us, because we don't work at extreme scales, I'm not sure it's worth leaving a relational platform, but I do think that we can learn to evolve and enhance our relational databases faster with DevOps ideas and techniques.

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

Orchestrating SQL Server with Kubernetes - T-SQL Tuesday is a monthly event where SQL Server bloggers write a post based on a subject chosen by the month’s host. You can find a list of all past topics at tsqltuesday.com. This month, it’s Grant Fritchey’s (t|b) turn with his topic Databases and ......(more)

Changing default location for docker containers - A question that regularly comes up when I talk about containers is, “can you specify where the containers/images live on the host?” This is a good question as the install for docker is great because it’s so simple but bad because well, you don’t get ......(more)

GroupBy Conference – SQL Server & Containers - Morning all, busy week last week as I was lucky enough to have my session on SQL Server & Containers in the top ten voted for sessions in GroupBy’s June conference. This was my first webinar and even though it was nerve wracking, I’m really glad I did ......(more)

Vendors/3rd Party Products

Extending DevOps practices to SQL Server databases - Story Tracking - Join Microsoft Data Platform MVP Grant Fritchey for the next Database DevOps demo webinar. He’ll be plugging Redgate tools into Atlassian's Jira, Bitbucket, and Bamboo, as well as showing how you can integrate database changes, code, and issues....(more)

T-SQL

Utility to temporarily drop FOREIGN KEY constraints on a set of tables - I was working on a database a few weeks back (maybe longer, I am kind of busy with several projects right now), and I wanted to truncate a set of tables. There was one central table, and 8 tables related by a FOREIGN KEY constraint. The central table ......(more)

How to Add Column at Specific Location in Table? – Interview Question of the Week #126 - Question: How to Add Column at Specific Location in Table? Answer: Order of the column in the table should not matter. Need of Column in Specific Location in a Table I personally see no reason to create a column in a specific location in the table. However, ......(more)

SQL SERVER – Learning New Multipurpose FORMAT Function - In versions prior to 2012, you need to use the CONVERT function with different styles to convert the date values into a different format. Let us learn about the FORMAT function in this blog post. Let us create this simple dataset CREATE TABLE #date_test(dates ......(more)

Be Aware of CARTESIAN PRODUCT When Using Join Keys in SQL - I got a massive satisfaction last week when I was able to bring down the execution time of a sql stored procedure from > 3 hours to mere 2 minutes. It all boiled down to a missing joining key that led to a cartesian product within the tables.Here is ......(more)

SQL Puzzle 1: Magic Squares - This is the first in what I hope will be a semi-regular series of recreational puzzles where SQL can be used to find the answer. I set these puzzles on occasional Fridays in my workplace, and as I now have quite an archive I thought I should start sharing ......(more)

When does a Snapshot Transaction Really Begin? - Somebody needs a selfie stick They say, “never read the comments” on the internet, but I’m lucky to get lots of interesting points and questions in my comments. Recently, Jim mentioned that he was doing some testing in a database that allows snapshot ......(more)

The Lighter Side

The SQL Hall of Shame - SQL Server is a big product, with lots of features. Many of the features—hopefully the vast majority of them—are solid and allow us to build trustworthy, secure, and reasonably usable database applications. But nearly everyone has stumbled into a few, shall we say, “darker areas” of the product now and again. ...(more)

Buzzword Bingo - Grant Fritchey (b/t) is our host for T-SQL Tuesday this month and surprise surprise he’d like to talk about DEVOPS. (As he put it, it was either that or execution plans.) Now I’m not highly knowlegable about DevOps myself. I mean I have a general idea ......(more)

Tech News : Disaster Recovery

British Airways Owner Says Data Center Outage Cost £80M - IAG chief apologizes for disruption, but says cuts not to blame Read More ...(more)

SQL Server Security

Working with Data in Always Encrypted - In this post we’re going to create some encrypted columns in a table in a test database and look at some of the practicalities and limitations of working with Always Encrypted (AE). There are actually a fair few limitations, but not because there anything ......(more)

SQL Server on Linux

Install SQL Server Agent on Ubuntu (Linux) - With the release of SQL Server vNext CTP 1.4, the SQL Server Agent support is included. It enables DBAs to schedule any task using a SQL Server Agent Job on Linux machine. In this post, I will explain how to install the SQL Server Agent package on the ......(more)

SQL SERVER ON LINUX INSTALLATION – PART4 – Install SQL Server Tools on Ubuntu – Updated! - Installing SQL Server on Linux does not install SQL Server tools by default. You have to install it individually. In my previous posts of this series, I discussed how to create a virtual machine using Windows Hyper-V, how to install Ubuntu Server on ......(more)

Software Development

SSDT: Unable to connect to master or target server. - Every now and then I come across this error in SSDT, normally when trying to publish and the odd thing is that the description never matches the actual cause (if you desperate for the cause it is because you can't connect). The thing I like about the ......(more)

Security news and thoughts

Healthcare Industry Cybersecurity Report - New US government report: "Report on Improving Cybersecurity in the Health Care Industry." It's pretty scathing, but nothing in it will surprise regular readers of this blog. It's worth reading the executive summary, and then skimming the recommendations. ...(more)

Malware Hiding in Britney Spears' Instagram and Where Else? - The Turla hacker group is up to its old tricks, but with an interesting new twist. Now, the group is using Britney Spears' Instagram account to cover its tracks. The new tactic could make it more difficult for organizations to defend themselves against ......(more)

New Malware Threat Targets National Power Grids - As if the threat posed by malware was not terrifying enough, researchers from the network security firm ESET said today that a Russian hacker group may have developed a way to take down the power grids of entire countries. The researchers described ......(more)

Replication

Leaping before looking: Transactional Replication and Temporal Tables - Did you know that you can enable system-versioned temporal tables on something that's marked for replication, even though it isn't supported? Yeah, me either. Here's what happened, and how you can fix it. ...(more)

R Language

Demo: Real-Time Predictions with Microsoft R Server - At the R/Finance conference last month, I demonstrated how to operationalize models developed in Microsoft R Server as web services using the mrsdeploy package. Then, I used that deployed model to generate predictions for loan delinquency, using a Python ......(more)

Plot SQL Server CPU Usage With R - This is a quick way of grabbing the last 256 minutes of CPU usage data from SQL Server and plotting it using R. The SQL Server script comes from Glenn Berry’s outstanding set of DMV queries. Here’s the R code: if(!require(RODBC)) { install.packages("RODBC") ......(more)

Installing R packages with rxInstallPackages in Microsoft R Server - In MicrosoftML package comes – in my opinion – long anticipated function for installing R packages for SQL Server and Microsoft R Server. And, I am super happy. Last year, in one of my previous blog posts, I have been showing how to install R package ......(more)

Genetic Algorithms In R: The Holyfield Problem - This is part of a series entitled Genetics In Action. In the last post, we looked at solving a basic genetic algorithms problem: finding a global maximum for a function consisting of a single real-valued variable. Today, we are going to look at a more ......(more)

Interfacing with APIs using R: the basics - While R (and its package ecosystem) provides a wealth of functions for querying and analyzing data, in our cloud-enabled world there's now a plethora of online services with APIs you can use to augment R's capabilities. Many of these APIs use a RESTful ......(more)

Cochran-Mantel-Haenzel Method with T-SQL and R – Part I - This test is an extension of the Chi Square test I blogged of earlier. This is applied when we have to compare two groups over several levels and comparison may involve a third variable. Let us consider a cohort study as an example – we have two medications ......(more)

Using Genetic Algorithms In R - This is part of a series entitled Genetics In Action. In today’s post, we are going to look at the GA package in R, which implements genetic algorithm. This package allows us to build different types of genetic algorithms in just a few lines of code. ...(more)

PowerShell

VS Code – Automatic Dynamic PowerShell Help - VS Code is my coding tool of choice. I love that one lightweight editor can do so much and as PowerShell is usually the language that I write in I really love the PowerShell extension Help When you write a PowerShell function that is going to be used ......(more)

PowerPivot/PowerQuery/PowerBI

Power Query Pivot Swap - I recently was given a CSV file that listed users and the groups they belonged to, from an Active Directory dump. The person asked me if it I could transform it to show the list of groups and the users that belonged to them. I thought I could, but I ......(more)

New Whitepaper on Planning a Power BI Enterprise Deployment - I'm excited to share that a new technical whitepaper I co-authored with Chris Webb is published. It's called Planning a Power BI Enterprise Deployment. It was really a fun experience to write something a bit more formal than blog posts. My interest in ......(more)

Running a native query within an M project - A few weeks ago, Microsoft published the Power Query SDK, an extension of Visual Studio, on the Visual Studio Marketplace. It is available for free for VS2015 and VS2017. ...(more)

Query Folding And Writing Your Own SQL Queries In Power Query/Power BI/Excel Get & Transform - When you connect to a relational database like SQL Server in Power BI/Power Query/Excel Get & Transform you have two choices about how to get the data you need: You can choose a table from the database and then either use the Query Editor UI or write ......(more)

Power BI Custom Visuals Class (Module 55 – Enlighten World Flag Slicer) - In this module you will learn how to use the Enlighten World Flag Slicer Custom Visual. The Enlighten World Flag Slicer takes a text field from your dataset and if the value uses a country’s name or abbreviation it will display a flag. This flag visual ......(more)

Where’s the Trend Line in Power BI Desktop? - A quick blog post on finding where the trend line is hiding in Power BI Desktop. Docs will state it is in the analytics pane for certain types of visualization. However, it doesn’t always show up: (I really resisted the urge to post another “Dude, where ......(more)

Performance Tuning SQL Server

Query Plans: Trivial Optimization vs Simple Parameterization - Facemaking You know when you think you know something, and the obviousness of it makes you all the more confident that you know it? That’s usually the first sign that there’s a giant gotcha waiting for you. And that’s what happened to me over the weekend. The ......(more)

Performance Myths : Oversizing string columns - Back in March, I started a series on pervasive performance myths in SQL Server. One belief I encounter from time to time is that you can oversize varchar or nvarchar columns without any penalty. Let's assume you are storing e-mail addresses. In a previous ......(more)

Identifying failed queries with extended events - Back to some core SQL this week and one of my favourite features, extended events. Introduced in SQL Server 2008 they are a big improvement on SQL profiler, in the amount of information that can be tracked (more) and the impact that they have on the ......(more)

What’s an “Index” and how do they work? - What is an Index? We often hear indexes explained using the analogy of an index in the back of a book. You want to find the information about “rabbits” for instance – and so you look that up in the back and find the list of pages that talk about rabbits. That’s ......(more)

Microsoft News

Microsoft Previews Space-Saving OneDrive Feature on Windows 10 - Files stored exclusively on Microsoft's cloud still show up in File Explorer using the OneDrive Files On-Demand feature. ...(more)

Microsoft Launches Capacity-based Power BI Premium Licensing Option - Microsoft officially releases the flexible licensing option for businesses wanting to outfit their workforces with data exploration tools. ...(more)

Microsoft News : Security

Microsoft Boosts Ransomware Defenses for Windows 10 - Users of newer, patch-supported versions of the Windows operating system aren't the only ones to receive security updates aimed at protecting them against ransomware attacks such as last month's WannaCry. Citing the "elevated risk for destructive cyberattacks ......(more)

Hardware News

Microsoft Releases 5th Generation Surface Pro - Microsoft launched the newest version of its Surface Pro 2-in-1 device today -- a convenient cross between a laptop PC and tablet. The new model comes in a 12.3-inch form factor that features a 2736 x 1824 resolution touchscreen display. The latest ......(more)

Review: Microsoft's Surface Laptop running Windows 10 S - For the past 10 days, I've put Microsoft's Surface Laptop through its paces. Here's my 'non-reviewer's review' of Microsoft's newest member of the Surface family. ...(more)

HA/DR/Always On/Clustering

“Ex-Admin Deletes All Customer Data and Wipes Servers” - My headline is borrowed from a headline on bleepingcomputer.com. This just happened to a Dutch hosting provider in 2017. You can read the article. Actually, you must read the article. If you didn’t here’s the basic gist: An administrator quits or was ......(more)

ETL/SSIS/ELT

Using SSIS Scale Out to improve the performance of execution log writing - As you may already known, SSIS Scale Out is available in SQL Server vNext CTP1. it provides the capability to parallel package execution by distributing the execution to multiple machines. In addition, you can get better performance of execution log ......(more)

Using ETL Staging Tables - Most traditional ETL processes perform their loads using three distinct and serial processes: extraction, followed by transformation, and finally a load to the destination. However, for some large or complex loads, using ETL staging tables can make for ......(more)

SQL Server 2016 Import and Export Wizard and Excel - SQL Server 2016 is a 64-bit application and it comes with two versions of the Import and Export Wizard. If you launch the Import/Export Wizard from the SSMS, the 32-bit version is invoked. The 64-bit version does not natively provide support for Excel ......(more)

DevOps and Continuous Delivery (CI/CD)

T-SQL Tuesday #091 – Round-Up - Thank you to everyone who participated in T-SQL Tuesday #091 which was on databases and DevOps. As I anticipated, this brought out quite a bit of variety on the posts. This is because DevOps is still… not quite cooked…(?) in many peoples minds. I think ......(more)

T-SQL Tuesday #91–Early DevOps - This month the T-SQL Tuesday invitation is from Grant Fritchey, my colleague at Redgate Software. Surprise, surprise, the topic is DevOps. I write and talk about this a lot, but for this month I’ll look back at DevOps for me in 2001. If you want to participate ......(more)

Where's the Ops in DevOps? Part 1 - In the first of a three-part series, guest bloggers from DevOpsGuys look at the real role of Ops in DevOps. Where it changes, how it changes, and why Ops has an important part to play in the brave new world of DevOps....(more)

DevOps and your database - I’m a consultant. That means I have to deal with whatever I come across at customer sites. I can recommend change, but when I’m called in to fix something, I generally don’t get to insist on it. I just have to get something fixed. That means dealing ......(more)

DevOps and Visual Studio - The hardest part about implementing DevOps is not the tools you choose, but the processes you use to make DevOps work. That said, you do need to think about the tools you’re going to use to automate those processes. Frequently the emphasis is on third ......(more)

T-SQL Tuesday #091 – Databases and DevOps - Implementing DevOps with databases presents a unique set of challenges. However, just because something might be hard doesn’t mean that it shouldn’t be done. I had the opportunity to work with a team of developers, database developers and DBAs under ......(more)

VSTS–Changing the Default Build Queue - I wrote recently about the Release agent using the hosted queue as the default, which is sometimes a problem. The Build process has the same issue, though the way you change things is different. In a build, you see a set of tasks with various menu items ......(more)

How/Why/What is DevOps? - Thanks to Grant for hosting this month’s T-SQL Tuesday found (http://www.scarydba.com/2017/06/06/t-sql-tuesday-091-databases-devops/) where it is a chance to share our DevOps stories. I am not going into specifics of databases but I am going to write ......(more)

T-SQL Tuesday #91 – Start Talking - T-SQL Tuesday is a monthly blog gathering for the SQL Server/Data Professional community It is the brainchild of Adam Machanic (B|T) and is not limited to just things around the SQL Server database engine. Each month a blogger hosts the event and anybody who ......(more)

Book Review: Google’s Site Reliability Engineering #tsql2sday - For this month’s T-SQL Tuesday, Grant Fritchey’s topic is Databases and DevOps. Summary: you should skim this free online book to see inspiring ideas of how administration works at scale, although don’t expect to put the practices into place without ......(more)

Database Design, Theory and Development

Database reliability engineering - Five Questions for Laine Campbell about building dependable databases.I recently sat down with Laine Campbell, principal consultant at OpsArtisan, to talk about the practice of database reliability engineering and ways that DBAs can build their expertise ......(more)

Data Warehousing

Data Warehouse vs. Data Lake Technology: Different Approaches to Managing Data - Solving business problems using Big Data depends upon the approach taken. For example, if an organization only knows Data Warehouses, then challenges will be framed to fit using a Data Warehouse. As Abraham Maslow, a prominent psychologist eloquently ......(more)

Data Visualisation

Data Science Virtual Machine - The Data Science Virtual Machine (DSVM) is a customized VM image on Microsoft’s Azure cloud built specifically for doing data science. It has many popular data science and other tools pre-installed and pre-configured to jump-start building intelligent ......(more)

Graphing Ironman Race Data - This past weekend, I was a volunteer helping with an Ironman 70.3 race. I was really impressed and inspired by the athletes. I was also excited about the quantity and variety of data generated by this type of race (compared to a regular marathon). And as a 'graph guy' I just had to try my hand at visualizing that data!...(more)

The Art of Data Visualization - In this article, we look at different chart types that are used more frequently in performance analytics and that are used in various scenarios based on the data type....(more)

Data Science

Announcing Data Science Daily - At Data Driven we’re experimenting with new show formats – especially what we call “mini-shows.” Earlier this week we started a new feature called Data Science Daily . Frank has done an outstanding job on these first three installments! We’d love your ......(more)

Data Privacy

Data classification: understanding and protecting your data - A data discovery and classification research project from Foundry In Foundry, we’re responsible for developing new products and technology to support the changing needs of our customers. We’ve seen a huge shift in our customers’ needs: driven by new ......(more)

Conferences and Events

Microsoft Data Insights Summit Videos Published - The Microsoft Data Insights Summit finished today, and the videos of the sessions have already (!!) been published online here: https://www.youtube.com/user/mspowerbi/playlists?sort=dd&view=50&shelf_id=9 I can’t remember ever attending a conference where ......(more)

PASS Summit Precon on Virtual SQL Server Performance - PASS has just announced this year’s precon sessions for the PASS Summit in Seattle this October, and I am humbled to announce that my session, “Virtual SQL Servers – Right to Ludicrous Speed” has been selected for one of the Tuesday precon slots. This ......(more)

Precon at PASS Summit 2017 – Columnstore Indexes: Practical Solutions & Techniques - It is with an incredible joy and gratitude that I am able to share that I will be delivering a full-day precon (workshop) at PASS Summit 2017: Columnstore Indexes – Practical Solutions & Techniques on 31st of October 2017 in Seattle. What should you ......(more)

New Class: Always On Availability Groups: The Senior DBA’s Field Guide - Availability Groups are all the rage right now, especially since they’re included with SQL Server 2016 Standard Edition. Our Availability Groups blog post category is one of the most popular on the site, and in my 4-day Senior DBA class, people have ......(more)

Early Bird Rate for the 2017 IT/Dev Connections Conference Ends Soon - The rate is rising soon for IT/Dev Connections. Don't delay if you plan to attend. read more ...(more)

PASS Summit Pre-Con on Query Store - PASS has announced the pre-cons for this year’s Summit and I am *thrilled* to have the opportunity to present one centered around Query Store! This is a topic I’ve been presenting on since before SQL Server 2016 was released in June last year. I’m ridiculously ......(more)

Columnstore Indexes

Columnstore Index: How do I find tables that can benefit from Clustered Columnstore Index - Most of us understand that clustered columnstore index can typically provide 10x data compression and can speed up query performance up to 100x. While this sounds all so good, the question is how do I know which tables in my database could potentially ......(more)

Career Growth

Hallmarks of a good technical leader - Five Questions for Camille Fournier about the challenges engineers face when transitioning to managers, and how to foster great technical leadership.I recently sat down with Camille Fournier, the head of Platform Engineering at Two Sigma, to talk about ......(more)

Azure SQL Database

Azure SQL databases in logical servers, elastic pools, and managed instances - Azure SQL Database is a Database as a Platform service designed for applications that will use database as self-contained services. Databases can be grouped together to simplify management options, share the resources. There are different options that ......(more)

Azure CosmosDB

Introduction to Azure Cosmos DB: Microsoft’s Globally Distributed, Multi-Model Database Service - It’s time for something NoSQL at the PASS Application Development Virtual Group. Next week Denny Lee will present on Cosmos DB, the latest evolution of DocumentDB: Introduction to Azure Cosmos DB: Microsoft’s Globally Distributed, Multi-Model Database ......(more)

Analysis Services / BI on the MS Stack

5 interesting points from MDIS 2017 - After a whirlwind 4 days in Seattle I finally have some time to think back over the Microsoft Data Insights Summit and digest what were probably some of the more interesting points to come out of the Summit. Most of these came from the keynote so if ......(more)

Administration of SQL Server

UNDERCOVER TOOLBOX: Sp_FailedJobs the quick way to check for Failed Agent Jobs. - I know I know, Why not just use the GUI right? well that’s ok but sometimes the GUI just takes too long for me…maybe I am a little impatient but sometimes I just need something to run that will give me the answers in just a couple of seconds with the touch of a button....(more)

SQL SERVER – Startup Issue – Unable to Use Domain Account as Service Account When Read Only Domain Controller (RODC) is Involved - Recently, one of my client was trying to troubleshoot the below error which they were getting while trying to start the SQL Server service under the domain account. The interesting thing which they told me was that they are using Read Only Domain Controller ......(more)

SQL Server Diagnostic Information Queries for June 2017 - This month, there are some minor updates to the all of the versions of the queries. I usually make additional minor updates to the queries during the month, so if you are in doubt, downloading the latest version is always a good idea. Rather than having ......(more)

Solved : Microsoft SQL Server Error 3403 - From its powerful internal structure, fast performance, and reliability, SQL server is now widely accessed by several enterprise users. It is used to deal with bulk transaction processing, business programs, and content management. The Microsoft SQL ......(more)

Renaming MDF/LDF Files–SQLNewBlogger - Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. I would guess many people would run into this situation at some point. A developer or DBA creates a database, then decides to ......(more)

SQL SERVER – Msg 15137, Level 16 – Error Related to sp_xp_cmdshell_proxy_account - So far, I have written many blogs and I still feel that there are many errors and scenarios which are still uncovered. Whenever I see a new error and find a new solution, I always share it via this blog. Let us see how we can fix errors related to sp_xp_cmdshell_proxy_account. One ......(more)

How Much Longer Does it Take To Update Statistics with FULLSCAN? - Of all the gin joints in all the world, this stats update walked into mine. Admittedly, because I told it to. When I was recently working on the course, “Should Developers Manage Index Maintenance?” I explained that in my experience, statistics maintenance ......(more)

DBCC CLONEDATABASE Cannot insert duplicate key Error - If you’ve been using DBCC CLONEDATABASE at all, you might have run into a cannot insert duplicate key error (or something similar) when trying to clone a database: Database cloning for ‘YourDatabase’ has started with target as ‘COPY_YourDatabase’. Msg ......(more)

Database Migration / Upgrade problem – Fulltext Wordbreaker, filter, or protocol handler does not exist - I was migrating a few databases to a slightly upgraded server – new hardware, same OS/SQL main version, but the new one was fully service packed / updated etc. The environment dictated that I was doing this by the backup-restore method....(more)