The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

GitHub is a good place to store your code, but why stop there? Code is just another document, after all, and the life of any IT professional is awash with documents: the ones that you receive, the ones you edit, approve or publish to your peers.

GitHub has grown up a lot. If you write your documents in AsciiDoc, you can even edit them in-place, within GitHub. The basic operations of code are the same for documents: branch, merge, fork, pull and so on. A team that needs to deal with documents needs GitHub as much as the developer.

There are, of course, drawbacks to a scientific approach to document retention. In the days of paper documents, we used to be able to lose documents. Sometimes it was accidental and sometimes less so. At one company, we had, as was customary, an employee whose job it was to file the vast swathes of documents that swilled around the offices. This poor soul, let's call him 'George', was particularly useless at it, a genius for hapless filing. George was in some danger of getting 'the sack', until we realized suddenly the possible benefits of his unusual talent.

In an IT department, it is essential, occasionally, to lose documents. Then, as now, we would get absurd dictats from the management team. The standard tactic was to mislay, bury or destroy the document and, if challenged, claim that we never knew it existed. To have someone with a flair for making documents disappear was a delight for us and it made the life of IT Management far easier. If ever we wanted to send a coded message to the team about our feelings towards the contents of a document, we'd loudly call George over and ask him to 'file it appropriately'.

Even genuinely accidental loss of documents can prove to be an unexpected blessing, or a chance to escape the tangled briar patch of corporate paperwork. The same goes for source code, strangely enough. Before version control, I once managed to lose the entire source of an application. After a brief period of cursing, I rolled up my sleeves and managed to completely rewrite it, only for the original source to turn up again shortly after. However, the replacement was so much better and more concise that I quietly gave the disk containing the original source to George.

Technology has unintended side-effects and the right to have things lost or forgotten is one of those bittersweet aspects of life, the passing of which we sometimes regret.

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.

Virtualization and Containers

Persisting data in docker containers – Part One - Normally when I work with SQL instances within containers I treat them as throw-away objects. Any modifications that I make to the databases within will be lost when I drop the container. However, what if I want to persist the data that I have in my ......(more)

Vendors/3rd Party Products

The Louis Davidson custom style for SQL Prompt - In this article, Louis Davidson sticks his neck out, and offers what he believes is a sensible code formatting style, based on twenty-plus years of writing SQL. The result is the Louis Davidson custom SQL Prompt style that you can download using the link at the end of the post, try out, and adapt as required....(more)

Provisioning just got BIGGER with SQL Clone v2 - The recent launch of Redgate SQL Clone v2 has removed the previous 2TB size limit, as the tool now supports cloning databases up to a whopping 64TB. In this post, Karis Brummit explains how the increase has been possible....(more)

T-SQL

SQL 2016 – Temporal Tables – Triggers and Historical Data Tidbits - Hello again and welcome back to the series on Temporal Tables! Today we will take a look at two common questions. What happens when I put a trigger on a Temporal Table and can I back populate the historical table? Setup First thing’s first, let’s ......(more)

Using sql_modules System Catalog View to Search Function and Stored Procedure Definitions - Search for code in any stored procedure or function in all SQL Server databases using sys.sql_modules system compatibility view using the following code. read more ...(more)

Getting Started with SQLCMD - Those of you who are regular readers may notice that my content has shifted towards scripting and some of the emerging platforms and tools for interacting with Microsoft SQL Server. There is a method to this madness but I can’t go into details yet on ......(more)

The Lighter Side

From the NFL to MIT: The Double Life of John Urschel - Whether he’s blocking a 300-pound defensive lineman or hitting the math books, this doctoral candidate just won’t quit. ...(more)

HASSP: Meet the Team - This project isn't possible without some pretty awesome people. Fortunately for me, I didn't have to look too far to find the best. Here are the people helping make this a reality. ...(more)

SQL Server on Linux

Using OPENROWSET in SQL Server on Linux - I wanted to import the million song dataset in SQL Server on Linux. There’s a github repo that has the SQL to allow you to use this with the graph database features. However, it’s built for Windows. Linux is a slightly different beast. Once I started ......(more)

Security news and thoughts

New Laws Needed for Govt. Access to Cloud Data, Google Says - Current laws are outdated and are hindering access to data and threatening user privacy, the company says. ...(more)

Is Continuing to Patch Windows XP a Mistake? - Last week, Microsoft issued a security patch for Windows XP, a 16-year-old operating system that Microsoft officially no longer supports. Last month, Microsoft issued a Windows XP patch for the vulnerability used in WannaCry. Is this a good idea? This ......(more)

NSA Insider Security Post-Snowden - According to a recently declassified report obtained under FOIA, the NSA's attempts to protect itself against insider attacks aren't going very well: The N.S.A. failed to consistently lock racks of servers storing highly classified data and to secure ......(more)

Researcher Finds Georgia Voter Records Exposed on Internet - A security researcher disclosed a gaping security hole at the outfit that manages Georgia's election technology, days before the state holds a closely watched congressional runoff vote on June 20. The security failure left the state's 6.7 million voter ......(more)

Combating SQL Injection - Target. Home Depot. NASA. U.S. Army. Anthem. Wall Street Journal. MarketWired. Ashley Madison. What do they all have in common? They are all recent victims of cyber-attacks – several of these are confirmed to involve SQL Injection. Even though the exact ......(more)

R Language

Using sparklyr with Microsoft R Server - The sparklyr package (by RStudio) provides a high-level interface between R and Apache Spark. Among many other things, it allows you to filter and aggregate data in Spark using the dplyr syntax. In Microsoft R Server 9.1, you can now connect to a a Spark ......(more)

Understanding Relative Risk – with T-SQL - In this post we will explore a common statistical term – Relative Risk, otherwise called Risk Factor. Relative Risk is a term that is important to understand when you are doing comparative studies of two groups that are different in some specific way. ...(more)

Genetic Programming: DBA Salaries - This is part of a series entitled Genetics In Action. Today’s post shows off another variant of genetic programming. Last time around, we looked at mathematical functions using rgp. Today, we are going to build conditional programs using evtree, another ......(more)

PowerShell

VSCode – PowerShell extension 1.4.0 new command Out-CurrentFile - Yesterday David Wilson announced version 1.4.0 of the PowerShell extension for VSCode. He also pointed out that there have been over 1 million installs of the extension. If you want to install the PowerShell extension you can hit F1 in VSCode and ......(more)

PowerPivot/PowerQuery/PowerBI

Interactive R visuals in Power BI - Power BI has long had the capability to include custom R charts in dashboards and reports. But in sharp contrast to standard Power BI visuals, these R charts were static. While R charts would update when the report data was refreshed or filtered, it ......(more)

White Paper On “Planning A Power BI Enterprise Deployment” - I’m pleased to announce that a white paper I co-authored with Melissa Coates on “Planning a Power BI enterprise deployment” has now been published. You can download it from the Power BI white papers site here: https://aka.ms/pbienterprisedeploy Melissa ......(more)

Power BI Custom Visuals Class (Module 56 – Rotating Tile) - In this module you will learn how to use the Rotating Tile Custom Visual. The Rotating Tile gives you the ability to display multiple metrics on a single visual that rotates through each value you wish to display. This allows you to save valuable space ......(more)

How to better organise your Power BI Measures - A question I’ve come across recently is around ways to better organise measures in Power BI so they are easier to find. The query is usually along the lines of wishing there was a way to group measures together in the field list so users don’t have to ......(more)

Performance Tuning SQL Server

24 Hours of PASS: Last Season’s Performance Tuning Techniques - Brent, last season (and I have so many good bad photos to share) I’m excited to announce that Erik and I are presenting the opening session at this year’s free online 24 Hours of PASS webcasts! Here’s our session: Last Season’s Performance Tuning Techniques You’re ......(more)

NoSQL

A scalable time-series database that supports SQL - The O’Reilly Data Show Podcast: Michael Freedman on TimescaleDB and scaling SQL for time-series.In this episode of the Data Show, I spoke with Michael Freedman, CTO of Timescale and professor of computer science at Princeton University. When I first ......(more)

MongoDB Atlas Now Available on All Major Cloud Platforms - by Angela Guess A new press release reports, “MongoDB, the database for giant ideas, today announced that MongoDB Atlas, its cloud database as a service, is now available to users all over the world on the top three public cloud platforms. MongoDB Atlas ......(more)

Microsoft News

Microsoft R Server 9.1 on HDInsight is available! - Today, we are excited to announce that Microsoft R Server 9.1 on Azure HDInsight is generally available. With this, we bring the power and innovation of our latest 9.1 release to the cloud on Spark 2.1 on HDInsight 3.6. This release of R Server on HDInsight ......(more)

MDX/DAX

Defining variables in DAX queries - This article shows how to define variables in DAX queries and introduces the differences in scope when there are multiple EVALUATE statements. If you read DAX queries generated by Power BI, you will probably notice that the variables are often defined ......(more)

Hardware News

Asus Releases XG-C100C 10GBASE-T NIC For $99 - Asus rounded out the 10GbE ecosystem today with a desktop add-in network interface card to go with the previously released xg-U2008 switch. For less than $100 you get massive bandwidth with priority queuing for enhanced gaming support. ...(more)

The Intel Skylake-X Review: Core i9 7900X, i7 7820X and i7 7800X Tested - Building a PC is an experience worth having. Finding out what works with what and putting it all together is an experience, and the first time always gives a sense of achievement and accomplishment. In the high-end desktop space, even more so: trying ......(more)

Lenovo Unveils ThinkStation P320 Tiny SFF Workstation - ?Lenovo has unveiled a new ThinkStation model, the P320 Tiny, based on a Kaby Lake / Q270 platform with NVIDIA's Quadro P600 GPU. The unique aspect is the dimensions - At 1.4" x 7.1" x 7.2" (1L in volume), it is one of the smallest systems we have see ......(more)

HA/DR/Always On/Clustering

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

Graph Databases

Modeling an Adjacency List Hierarchy with SQL Server 2017 Graph Database - The Northwind database implements the manager to employee hierarchy using an adjacency list, which is a type of graph. This post shows you how to migrate the adjacency list to a graph of one node and one edge. This is a continuation of what was started ......(more)

Modeling Many-to-Many relationships in SQL Server 2017 Graph Database - Relational databases do not natively support many to many relationships although a junction table is an easy workaround. Graph databases natively support many-to-many relationships. One of the first sample databases Microsoft provided for SQL Server ......(more)

DevOps and Continuous Delivery (CI/CD)

Building the culture and collaboration layer for DevOps - Sean Regan says DevOps requires more than tools. It also needs high-performing people and teams.Continue reading Building the culture and collaboration layer for DevOps. ...(more)

You’re not delivering DevOps to the database - I’ve read through a number of the industry thought leaders to get an understanding of how DevOps is being communicated out there. As with so much else in life, you can start at Wikipedia to get a general understanding: DevOps … is a term used to refer ......(more)

DevOps and incident management: A recipe for success - David Hayes explains why DevOps is now a requirement for success and he outlines challenges all DevOps teams will face over the next five years.Continue reading DevOps and incident management: A recipe for success. ...(more)

Where’s the Ops in DevOps? Part 2 - In this 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. What does the future of IT Operations look ......(more)

Setting up CI/CD for Docker Container Running in Kubernetes using VSTS - This post is provided by Senior Consultant, Najib Zarrari to explain setting up CI/CD for Docker Container running in Kubernetes using VSTS. In my last post, I covered how you can create a simple Web API, run the Web API in a Docker container and then ......(more)

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)

Database Code Analysis - Database code analysis will reduce the number of 'code smells' that creep into your database builds. It will alert the team to mistakes or omissions, such as missing indexes, that are likely to cause performance problems in production. It will allow the Governance and Operations team visibility into production readiness of the code, warning them of security loopholes and vulnerabilities. William Brewer describes the two technical approaches to database code analysis, static and dynamic, and suggests some tools that can help you get started....(more)

Database Design, Theory and Development

Periscope Data | What?You?Need?To?Know?About?SQL's GROUP BY - The SQL 'group by' is one of the most common keywords. Here is how it works, and a few special cases you want to be aware of ...(more)

Periscope Data | SQL History and?Why Your Database Isn't Really Relational - Rather than a true relational database, SQL is a compromise between convenience and the rigour of relational algebra. Here's how it's different. ...(more)

Data Visualisation

Registrant Job Title Data from SQLSaturday Baton Rouge events 2014-2016 - In a blog post on the Sparkhound website, I laid out the strong case for a sponsor's Return on Investment (ROI) for sponsoring local SQLSaturday events. Part of my argument in favor of sponsoring SQLSaturday events are the diversity of attendees and ......(more)

Fact or Fiction? Smart Data Visualization Tells the Tale - Click to learn more about author Kartik Patel. If you are considering a Business Intelligence solution, you ought to give some consideration to the concept of Smart Data Visualization and review your prospective solution to determine its capabilities ......(more)

Data Science

Applications of R at EARL San Francisco 2017 - The Mango team held their first instance of the EARL conference series in San Francisco last month, and it was a fantastic showcase of real-world applications of R. This was a smaller version of the EARL conferences in London and Boston, but with that ......(more)

Data Mining/Data Analysis

Perception and bias and metrics, oh my! - Dawn Parzych shows how understanding assumptions and biases can help your organization. Continue reading Perception and bias and metrics, oh my! . ...(more)

How to use Python in SQL Server 2017 to obtain advanced data analytics - On the 19th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last ......(more)

Conferences and Events

Speaking at the Data Platform Summit 2017 - I am honored to be speaking at the Data Platform Summit 2017. The event takes place August 17-19, 2017 in Bangalore, India. This will be my first trip to India, and I’m excited to be traveling halfway around the world (literally 12:30 hours difference) ......(more)

Computing in the Cloud (Azure, Google , AWS)

Azure DevTest Labs - I have been working at Microsoft now for 3 years and 4 months (side note: it’s by far the best company I have ever worked for). You would think by now I know about every Azure product, but we release new products and services at such a tremendously ......(more)

Blog Series: Cloud “Fear” Busting - I have decided to do a 3-part series on Cloud “Fear Busting” scenarios. Why? Over the past few years working with the cloud (Azure) I have come across 3 main “fears” or “concerns” that stand out in my mind that … Continue reading ? ...(more)

Periscope Data | How to Get Started With AWS Spectrum in Minutes - AWS Redshift Spectrum is Amazon’s newest database technology, allowing exabyte scale data in S3 to be accessed through Redshift. This is not simply file access; Spectrum uses Redshift's brain, deploying workers by the thousands to order, join and aggregate ......(more)

A Brief History of Cloud Computing - In 1963, DARPA (the Defense Advanced Research Projects Agency), presented MIT with $2 million for Project MAC. The funding included a requirement MIT develop technology allowing for a “computer to be used by two or more people, simultaneously.” In this ......(more)

Monitoring Azure Cloud and Hybrid Environments - Cloud-based services and applications must still be monitored just like the on-premise ones. You still need most of your data center activities that ensure that your planning, budgeting, security and service-level obligations are met wherever the data and services are actually hosted. There is much to be said for an integrated approach to providing a unified view of entire application workloads on-premise. hybrid and cloud using the same tools wherever possible....(more)

Career Growth

Seldom Used Keyboard Tricks - I was giving an internal talk on SSMS productivity trips and there were a few that I believe are seldom used but can be a real time or keystroke saver that I would like to mention. To the best of my knowledge these will work in any version of SSMS...(read ......(more)

Working on my Microsoft Professional Program for Data Science - Microsoft announced a new program last year to help you understand the skills that a Data Scientist needs in their daily life. It consists of nine courses and a final project, you can get all the details about it on the Microsoft Academy site. I started ......(more)

12 Ways Smart Data Professionals Act Stupid - There are many articles available today that help data professionals prepare for job interviews. I have also written my share of articles on the subject. What I don’t see are articles that tell you how to be a good data professional once you have landed ......(more)

Microsoft Professional Program in Data Science – Continued - A couple of weeks back I blogged about my progress in the “Microsoft Professional Program in Data Science” curriculum. I’ve now finished the remainder of the courses. Now I only need to finish the capstone project. This project only occurs at the start ......(more)

Thoughts from a New Speaker - Sunday afternoon…enjoying the “Post SQL Saturday” calm… SQL Saturday Houston was yesterday, which in and of itself is not that unusual for me. I’ve been to many technical conferences over the last 18 years…4 SQL Saturdays, 2 PASS Summits, SQL Server ......(more)

What #SQLSaturday Organizers and Volunteers Do - Last weekend, I had the privilege of speaking at SQLSaturday Houston. Allen Kinsel, Devon Leann, Joe Hellsten, and so many more volunteers did a fantastic job of putting on a totally free event for hundreds of attendees. I’ve never organized one of these ......(more)

Backup and Recovery

SQL SERVER – Msg 3168, Level 16 – The Backup of the System Database on the Device Cannot be Restored - In the recent past, I have written a blog about Ransomware. Here is the link. SQL SERVER – How to Protect Your Database from Ransomware?. In this blog post we are going to discuss about system database....(more)

SQL SERVER – Unable to Restore from URL – The Specified URL Points to a Block Blob. Backup and Restore Operations on Block Blobs are not Permitted - With Microsoft Azure pitching in the market, I am getting few clients who want me to assist them for errors related to Azure as well. As a part of growth, I do learn them and try to help the clients. Let us learn about Block Blobs in this blog post. My ......(more)

The Three T’s of Backups - I just read several more horror stories that include, among other things, failed backups. I’ve said it before (at volume, extreme volume), and evidently I have to say it again. Simply creating a backup file is not enough to ensure the protection of your ......(more)

Builder Day: Doing a Point-in-Time Restore in Azure SQL DB - We’re trying something new at the company: Builder Day. We define a slightly-out-of-the-norm task, and then the team splits up and tackles the task on different cloud platforms, writing it up as we go. These posts aren’t going to make you an expert on ......(more)

Builder Day: Doing a Point-in-Time Restore in Amazon RDS for SQL Server - Brent had an idea that we should set aside a day per month where we could build things in the cloud and blog about our experiences. I was tasked with Amazon RDS for SQL Server: create an instance, configure backups and test point-in-time restores. After ......(more)

Azure CosmosDB

Lessons learned from building a globally distributed database service from the ground up - Dharma Shukla covers Azure Cosmos DB, a massively scalable, multitenant, globally distributed database service.Continue reading Lessons learned from building a globally distributed database service from the ground up. ...(more)

New Data Driven Episode: Rimma Nehme on CosmosDB, Planet-Scale Applications, and Selling Door-to-Door - This week on Data Driven , Frank and I are honored to interview Rimma Nehme , CosmosDB Architect at Microsoft. We had a fantastic time chatting about CosmosDB , open-source, reading, and what (or whom, rather) Dr. Nehme finds really important. As always ......(more)

Azure Analysis Services

How to Automate Processing of Azure Analysis Services Models - I’ve been working on a proof of concept for a customer that involved using Azure Analysis Services as a cache for some data in an Azure Data Warehouse instance. One of the things I’ve been working on is scheduling the automatic processing of the Azure ......(more)

Analysis Services / BI on the MS Stack

SQL SERVER – Upgrade Failure – The Cluster Resource is Not Online. Bring the Analysis Services Server Online Before Starting the Upgrade Process - Many customers want to do in-place upgrade of SQL Server instance to avoid the hardware cost and downtime. There are various pros and cons for doing in-place upgrade or parallel upgrade and one need to evaluate them before making a final choice. Let ......(more)

SQL Server R Services: The Basics - It is possible to do a great deal with R within SQL Server, but it is best to start by doing analysis in R on numeric data from SQL Server and returning the results to SQL Server. There is great value to be gained even with this basic foundation. Robert Sheldon is on hand to give you a kick start with the first in his series on beginning with R in SQL Server....(more)

AI/Machine Learning/Cognitive Services

How AI Can Moderate Comments, Eliminate Trolls - The New York Times has only allowed comments to be posted on about 10% of its stories because moderating such comments is a slow process. Now the media company is using artificial intelligence to speed up the process. ...(more)

Cognitive Services: Building Natural Language Analyzer with LUIS(Language Understanding Intelligent Service), using Prebuild Domains - Cognitive Services: Language Understanding Intelligent Service (LUIS) LUIS is natural language analyze engine, to categorize and extract entities from colloquial sentence. You can build App (engine) easily setting up “Intents” (categories) ......(more)

Azure ML Part 9- Cross Validation: Machine Learning Prediction (6) - In previous posts (from Part 1 to Part7), I have explained the whole process of doing machine learning inside the Azure ML, from import data, data cleaning, feature selection, training models, testing models, and evaluating. In the last post, I have ......(more)

Administration of SQL Server

SQL SERVER – FIX: Msg 15170, Level 16, This login is the Owner of 1 Job(s). You Must Delete or Reassign these Jobs Before the Login can be Dropped - In our real life, there are lots of dependencies and same is true with SQL Server as well. This blog also talks about inability to drop a login due to a dependency. Let us see the error related to login is the owner of the 1 job(s). When you try to drop ......(more)

Session State Is Lost in Web Farm If You Use SqlServer or StateServer Session Mode - One of the most common issues that users face when hosting their application in a webfarm is “unexpected loss of session state”. We face these issues when we store the session information inproc, especially when we don’t set client affinity in the load ......(more)

Changing Default Database File Locations in SQL Server 2016 - At some point in your career as a data professional you will need to change the default database file locations in SQL Server 2016. The post Changing Default Database File Locations in SQL Server 2016 appeared first on Thomas LaRock. If you liked this ......(more)

dbfs – command line access to SQL Server DMVs - With SQL Server on Linux, Microsoft has recognized that they’re opening up their products to a new set of users. People that aren’t used to Windows and it’s tools. In the Linux world we have a set of tools that work with our system performance data and ......(more)

How the Initial VLF sequence number gets decided of the default transaction log file - In last three years, I presented two times on the topic Transaction Log File Architecture. During the sessions, when I discussed the undocumented command DBCC LOGINFO, one of the common questions which I got – ‘How the Initial VLF sequence number gets ......(more)

How to Enable/Trace the Query Thread Profile Extended Event in SQL Sever 2014+ - Microsoft recently gave us a more lightweight way to trace data related to execution plan performance. As of SQL Server 2014 SP2, you can trace the query_thread_profile Extended Event. But why doesn’t it show up in the Extended Events Wizard? I’m a pretty ......(more)

SQL 2017 new DMF: Managing VLOGs in our databases - SQL Server 2017 brings some new interesting DMV’s and DMF’s, one of them is sys.dm_db_log_info. This new DMF allows you to manage VLOGs in your databases, as Dennes Torres explains....(more)

.NET Related Articles

Dynamically generate the command line DMA statement for each database - If you are upgrading your instance to 2016 (or 2017 soon) then you probably are going to want to run the DMA (Database Migration Assistant). If you are doing a BUNCH of upgrades at once (say 100 instances or something) then you probably don’t want to ......(more)

Visual Studio 2017 and Swagger: Building and Documenting Web APIs - The OpenAPI ‘Swagger’ Specification defines a protocol that allows applications to discover, and generate documentation of methods, parameters and models of REST APIs, This provides a way for any software to identify the features of a REST API. It does for REST APIs what WSDL (Web Service Description Language) did for web services. Visual Studio now provides it to support proxy creation for REST APIs, as Dennes Torres explains....(more)