The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I advocate that we should get a long life from our database platforms. In fact, I like to think about a 7-10 year lifetime for many of my database instances. That means I'll get beyond the 5 year lifetime that Microsoft provides and move into the extended support. Actually, for most of my career, I've run instances without support and haven't had issues. We invest a lot in databases, and I want companies to invest a lot because they are important. That means I need a good payback.

Microsoft should provide security patches for products throughout the Extended Support period, which means that I should be able to run SQL Server and Windows securely for 10 years. that probably stretches the lifetime, but certainly that gives me time after five years to begin planning and prepping for an upgrade.

The problems for many customers come when other vendors don't bother to keep supporting older software and providing patches. Even if Microsoft releases a security fix for your OS, the vendor that makes software you run on top of Windows or SQL Server might not. That's an issue, and it's one that will become more of an issue as companies become reluctant to change software that works well.

This article shows that older ATM machines running XP and Windows 7 are having issues. Some of this is physical access, but some is related to issues in the OS. While Windows 7 should be getting patches, Windows XP is not and needs to be upgraded. In fact, I'd argue that any losses here should be born by ATM owners and not covered by insurance for not having upgraded their systems.

While I like to get as much time as I can from a system, it's irresponsible to expect software to run without security patches being actively applied, which includes any upgrades from vendors. In some cases, this might be negligent by the companies doing so, especially for embedded systems. I don't know that I want governments to force vendors to provide patches, but I'd like companies to write contracts that ensure that patches will exist for come lifetime of the products. In the event the company can't provide patches (or certification), then they'd need to release their code as open source so that someone can provide a patch.

The world becomes more intertwined and dependent on computing, from things like convenience services or core systems. We can't have those systems becoming more vulnerable because companies are unwilling to upgrade and vendors abandon older software. Some sort of compromise is needed to ensure that computer systems are protected from known vulnerabilities.

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

Two Code Patterns That Don’t Virtualize Well - Virtualization used to be a really Big Deal for database admins: we had to do a lot of careful planning to get a virtualization project done right. These days, virtualization is more and more of a no-brainer: most apps make the transition just fine. Every ......(more)

Vendors/3rd Party Products

How to reformat a database in one operation - Inherited a database from another team? Changed your team policy on the way that you format SQL? What’s to stop you formatting the code of an entire database nicely, when you’re developing it? It can be done, but the process can take longer than you expect. This article will demonstrate a simple 3-step approach to reformatting a whole database to your standard, in a single operation, using SQL Compare and SQL Prompt....(more)

T-SQL

Matters of Timing in Concurrency - There are a few topics in SQL Server that I love academically, but are truly a pain to get right. The other is security, and it pales in comparison to how much trouble it is to program for truly safe concurrent access to a resource. The biggest issue: ......(more)

SQL SERVER – Nested Triggers – Trigger Calling Another Trigger Error - Recently I have received a very interesting question in an email. The user wanted to know how will trigger behave when there are nested triggers. In an email, he described a scenario where trigger called another trigger. I really liked the question and ......(more)

SQL SERVER – Puzzle – Strange Behavior With Extra Comma in Create Table Statement - Today’s puzzle is very interesting and my favorite puzzle to ask right at the beginning of the presentation. This puzzle helps to break the ice between audience while people are still settling in the audience. Let us see my favorite puzzle of Strange ......(more)

The Fastest Way To Locate Errors In Your SQL Query - Photo by N. on Unsplash In about 60 seconds you will never debug error messages in SQL Server Management Studio the same way again. Coming from a .NET background, I’m used to receiving relatively helpful error messages that point me to the precise location ......(more)

Formatting Dates with CONVERT - Displaying dates and times with different formats in TSQL is a task I run into quite a bit. I used to visit this page so many times, I'm surprised it doesn't have a "Welcome back, Dave!" banner on it at the top. After umpteen million times, I decided ......(more)

The Lighter Side

The Boring Company is really pushing the definition of “Flamethrower” - You've got to hand it to Elon Musk. He has built up such a reputation and fan base over the years, that with a mere video and a few tweets, he can sell millions of dollars' worth of novelty toy flamethrowers in a few days. The Boring ......(more)

Prop Bets for the Big Game - The Periscope Data team put our sharpest minds together to come up with the best gambling guide on the net. We’ll walk you through a few of the prop bet predictions, some code, and give you a dashboard to make some bets for yourself! ...(more)

Books of 2017 - Right, beginning of a new year, so time to look at what I read in the last year and what of it I can recommend. Please ignore that it’s almost February. I’m going to pretend that the year starts with February, that way I don’t have to think about where ......(more)

Building a Raspberry Pi Car Robot with WiFi and Video - Last year I found a company called SunFounder that makes great Raspberry Pi-related kits and stuff. I got their Raspberry Pi 10" Touchscreen LCD and enjoyed it very much. This month I picked up the SunFounder PiCar 2.0 kit and built it with the kids. ...(more)

Tech News

California Senate defies FCC, approves net neutrality law - Enlarge / California State Capitol building in Sacramento. (credit: Getty Images | joe chan photography) The California State Senate yesterday approved a bill to impose net neutrality restrictions on Internet service providers, challenging the Federal ......(more)

What is Blockchain? - Blockchain is a distributed database and storage devices for the database are not all connected to a common processor. “A Blockchain or distributed ledger,” as Gartner describes, is a mechanism for adding trust in an untrusted environment.” Blockchain’s ......(more)

SQL Server Security and Auditing

Server Audit Mystery 2: Filtering action_id gets Error Msg 25713 - This post is, for the most part, a continuation of Server Audit Mystery 1: Filtering class_type gets Error Msg 25713. In that post I was trying to filter on the class_type field / predicate source (i.e. the object, or whatever, that the event is on). ...(more)

SQL Server Security

Encrypting an existing database with TDE - As mentioned previously, the process of setting up TDE (Setting up Transparent Data Encryption (TDE)) is the same whether you’ve just set up a new database, or whether you’re working with a live database. Once you turn encryption on SQL Server will begin ......(more)

Better Security with dbatools - I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items. One ......(more)

Spatial Data

Calculating Distance between Data Centers on a Globe - Calculating distance between two points on a flat plane is straightforward thanks to the Pythagorean theorem. However, what if your plane isn’t flat? What if you need to find the distance between two points on a sphere, like the earth? ...(more)

Software Development

Simplify REST API development for modern Single-page apps with SQL Server - JSON functionalities that are added in SQL Server 2016 and Azure SQL Database enable application developers to easily expose relational data from database tables and create REST API that will provide data to modern JavaScript single-page applications. ...(more)

Other People’s GitHubs: Fork, Clone, and Pull - Let’s say you found an interesting repository (code project) on Github, and you wanted to make a contribution to it. Here’s how it works. Profilin’ Did you know that Richie has a stored procedure call sp_DataProfile? I didn’t even know he had a website ......(more)

Security news and thoughts

After Section 702 Reauthorization - For over a decade, civil libertarians have been fighting government mass surveillance of innocent Americans over the Internet. We've just lost an important battle. On January 18, President Trump signed the renewal of Section 702, domestic mass surveillance ......(more)

In a first, US hit by “Jackpotting” attacks that empty ATMs in minutes - Enlarge (credit: Martin Iturbide) For the first time on record, ATMs located in the US are falling prey to jackpotting, an attack in which malicious hardware or software forces the machines to dispense huge amounts of cash to waiting thieves, KrebsOnSecurity ......(more)

More than 2,000 WordPress websites are infected with a keylogger - Enlarge / A screenshot showing a keylogger extracting user names and passwords. It's currently infecting more than 2,000 WordPress websites. (credit: Sucuri) More than 2,000 websites running the open source WordPress content management system are infected ......(more)

R Language

Scraping a website with 5 lines of R code - In what is rapidly becoming a series — cool things you can do with R in a tweet — Julia Silge demonstrates scraping the list of members of the US house of representatives on Wikipedia in just 5 R statements: library(rvest)library(tidyverse)h (more)

R or Python? Python or R? The ongoing debate. - On every SQL community event, where there could be a cluster of sessions dedicated to BI or analytics, I would have people asking me, “which one would you recommend?” or “which one I prefer?” So, questions about recommendation and preferences are in ......(more)

Product Reviews and Articles

Surround Code with Comments in SQL Prompt - I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try. I can’t believe I haven’t built this snippet ......(more)

Managing SQL Prompt Code Analysis Rules - I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try. SQL Prompt includes new Code Analysis rules ......(more)

PowerShell

Power BI, Azure Analysis Services, and the gateway data region - In this video, Adam looks at data regions and how they work with Power BI, Azure Analysis Services, and the On-Premises Data Gateway. For the use of a single gateway, all services must be within the same data region. Using a second gateway might be your ......(more)

PowerPivot/PowerQuery/PowerBI

Why I cannot download .pbix files from powerbi.com - If like me you thought “Hey, it’s a new feature, I can finally create reports in powerbi.com service and then when I need to mashup with data from other sources, then I just have to download the Power BI desktop file (.pbix), do my mashup and then publish ......(more)

Converting a Power BI Desktop File from Import to Live Query - A customer of mine is in the midst of a proof of concept using SQL Server and Power BI. During the POC, all the modeling was done in Power BI Desktop. Now that the POC is coming to the next phase, the customer is ready to move the Power BI data model ......(more)

Power BI Custom Visuals Class (Module 88 – Pulse Chart) - In this module you will learn how to use the Pulse Chart Custom Visual. The Pulse Chart can easily display trends in your data and any events that could have causes those trends to occur. Module 88 – Pulse Chart Downloads Power BI Custom Visual – Pulse ......(more)

Using SSAS Multidimensional As A Data Source For Power BI (Video) - The nice people at PASS have made a video of my session on “Using SSAS MD as a data source for Power BI” available to view for free on YouTube: I’m honoured that it’s listed one of their “Best of PASS Summit 2017” sessions, and there are lots of other ......(more)

Power BI Data Security – Sharing - Microsoft recently added more sharing capabilities that may change my view on sharing within the enterprise. As with all things Power BI, change is inevitable. Up to this point, I recommended that customers did not use sharing as an enterprise solution ......(more)

Export the Entire M Power Query Script from a Power BI File, New Version of Power BI Helper, Search based on Field Description in the Model - Previous versions of Power BI Helper had some features related to the visualization part and the modelling part of a Power BI file. This latest release of Power BI helper, can export the entire M or Power Query script into a file or into clipboard. Sometimes, ......(more)

Performance Tuning SQL Server

Functions and “the use of indexes” - Wordy Bird This sentence gets repeated a lot. You know the one. “Functions prevent the use of indexes.” Well, knowing you, it’s probably just your indexes. I’ve seen your indexes. Which Indexes? In the SUPERUSER database (yeah, I know, I’m cheating on ......(more)

SQL SERVER – Simple Script to Match Session ID to OS Thread ID - I often get asked a question about how actually Session ID of SQL Server is mapped to Operating System’s’ Thread ID. Well, the answer is very simple, here is the script which provides us three important details – SQL Session ID, OS Thread ID and OS Scheduler ......(more)

Froid: How SQL Server vNext Might Fix the Scalar Functions Problem - Scalar functions and multi-statement table-valued functions are notorious performance killers. They hide in execution plans, their cost is under-estimated, the row estimates are way off, they cause queries to go single-threaded, I could go on and on. ...(more)

Layman’s Guide to Performance Tuning - Performance tuning is one of those things that is hard to get right when you do not have an established methodology of how to approach the problem. Consider this scenario. The application users are complaining about their app being so slow and explaining ......(more)

Microsoft News

Microsoft 2Q18: Trump tax hit turns strong quarter into $6.3B loss - (credit: Julien GONG Min) Microsoft has posted the results of the second quarter of its 2018 financial year, running up until December 31, 2017. Revenue was $28.9 billion, up 12 percent year-on-year, and operating income was $8.7 billion, a 10-percent ......(more)

What is Microsoft's 'Polaris' project? - Microsoft is continuing to try to modernize Windows, both at the core and shell levels. Here's how the 'Polaris' shell may ultimately fit into its strategy. ...(more)

New Windows patch disables Intel’s bad Spectre microcode fix - Enlarge / A closeup shot of an Intel Haswell die, with a pin for size reference. (credit: Intel) Microsoft has released a new Windows patch to disable Intel's hardware-based mitigation for the Spectre attack due to bugs introduced by Intel's mitigation. In ......(more)

Internet of Things

GE Exec on Digital Twins, Predix Platform and Diversity - GE Digital’s Senior Vice President Deborah Sherry says the company is making good progress with digital twins and its Predix platform. ...(more)

Hardware News

Intel Is Working on Chip-Level Patches for Spectre, Meltdown - During Intel's announcements about Q4 and 2017 earnings yesterday, CEO Brian Krzanich said the company is working to update its processors to prevent Spectre and Meltdown, two major vulnerabilities discovered last year. Krzanich added that the first ......(more)

Samsung Announces 860 PRO And 860 EVO SATA SSDs - After more than three years of ruling the consumer SATA SSD market, the Samsung 850 series is being replaced. The 850 PRO and 850 EVO that first brought 3D NAND flash memory to the consumer SSD market are being retired to make room for the new 860 PRO ......(more)

The Intel SSD 760p 512GB Review: Mainstream NVMe Done Right - Intel's third generation consumer NVMe SSD is here. After the big expensive drive based on their enterprise SSDs and the affordable M.2 drive that was barely an upgrade over mainstream SATA SSDs, the new Intel SSD 760p gets it right with solid performance, ......(more)

Samsung Launches Z-SSD SZ985: Up To 800GB Of Z-NAND - Samsung announced today that they are officially launching their first Z-SSD product, the SZ985. The Z-SSD uses Samsung's Z-NAND memory, a high-performance derivative of their 3D NAND flash memory and Samsung's intended competition for Intel's 3D XPoint ......(more)

ETL/SSIS/ELT

SSIS Package Data flow Execution Slowness Troubleshooting - In this blog, we are covering "SQL SSIS package data flow execution slowness" troubleshooting guidelines. The approach mentioned in the blog can be used for reference while troubleshooting SSIS package data load performance issues. Consider ......(more)

DevOps and Continuous Delivery (CI/CD)

Introducing a DevOps culture - A big part of the DevOps discussion is the concept of implementing a DevOps culture across organizations. Unfortunately, I often hear it as something that should just happen. We’ll take care of the process and tools, you go and focus on the DevOps culture. How ......(more)

Database Design, Theory and Development

JSON in SQL Server – Use cases - JSON support in SQL Server 2016+ and Azure SQL Database enables you to combine relational and NoSQL concepts and easily transform relational to semi-structured data and vice-versa. JSON is not a replacement for the exiting relational models, and there ......(more)

Degree of Duplication - The relational model was strongly based on sets. It wasn’t enough that a table had to have a key (originally a primary key, but later we realized all keys were keys). As Jim Gray put it in the early days of RDBMS, “We had no idea what we were doing and ......(more)

Data Visualisation

ggplot Basics: Mappings And Geoms - This is part two of a series on ggplot2. In today’s post, we will look at some of the basics of ggplot. As mentioned in the previous post, ggplot has a number of layers. In today’s post, we will look at two of these layers: the basic mapping layer ......(more)

The Grammar of Graphics - This is part one of a series on ggplot2. I’m starting a new series on using ggplot2 to create high-quality visuals. But in order to understand why ggplot2 behaves the way it does, we need to understand a little bit about the grammar of graphics. Leland ......(more)

Data Science

gdpr - panic part 1 - GDPR is coming (or if you are reading this in a few weeks then gdpr is here, what do you need to know and where do you start? This post is based on how gdpr will apply to the UK. I have nothing against the EU, but in the UK it is the ICO which governs ......(more)

Data Science Education at Syracuse University - In mid-2017, Syracuse University announced it was accepting applications for its new online program, DataScience@Syracuse. It is an 18 month, 36 credit program, ending with a Master’s of Science in Applied Data Science. It was developed collaboratively ......(more)

Data Privacy

Infographic: Big Gaps Emerge Between Countries on Attitudes Towards Data Protection in the Cloud - by Angela Guess A recent press release reports, “Gemalto, the world leader in digital security, can today reveal that while the vast majority of global companies (95%) have adopted cloud services[1], there is a wide gap in the level of security precautions ......(more)

Data Mining/Data Analysis

Three Traditional Storytelling Techniques That Add Value to Data and Analytics - Click to learn more about author Bree Baich. During the holidays, we seek opportunities to slow down and spend time with family and friends. It’s a time for traditions that pull us together. One such tradition for my family is watching National Lampoon’s ......(more)

Conferences and Events

Sign up for more free training from Redgate - Redgate has committed to hosting a free virtual event in every quarter of 2018, and will be kicking this off on February 28 with a livestream themed around data privacy and protection. The agenda has now been released, so you can see who will be presenting, what they will be presenting, and how you can tune in to watch. Find out more about the sessions and register your place....(more)

More live online training from Kimberly in March! - Due to the popularity of Kimberly’s delivery of her new IEVLT: Immersion Event on Very Large Tables: Optimizing Performance and Availability through Partitioning in January, and the fact that many of you told us that you didn’t have a chance to attend, ......(more)

Computing in the Cloud (Azure, Google , AWS)

[Linux] How to login Azure Linux VM’s using SSH key pair - This post is for Linux newbies trying to figure out SSH way login to Azure Linux VM. I am following the steps explained here. Had seen many times our windows users struggle to get this right at first shot. Hope the below screenshots are clear to follow ......(more)

Database Backup Options for SQL on Azure IaaS - Recently I had a requirement to collate and briefly compare some of the various methods to perform SQL Server backup for databases deployed onto Azure IaaS machines. The purpose was to provide a few options to cater for the different types (OLTP, DW, ......(more)

How To: Install SQL Server on an Azure VM - Last month I demonstrated creating an Azure VM with SQL installed using one of the default templates. While this is a very easy way to stand up a SQL Server very quickly there are some downsides. Specifically, you have very few install options. For the ......(more)

Career Growth

Scripting Tip: Start With Crediting Your Source - In the course of your career, you’re going to accumulate a folder of scripts to do various tasks. There’s one really, really important lesson you need to learn as early as possible. In a comment, include a link to the original source. If you started ......(more)

I’m a Speaking Mentor! - I am so excited to announce that I have been accepted as a speaking mentor on SpeakingMentors.com! I struggled for so many years with my fear of public speaking and now is my chance to help someone who may have those same fears. Huge Thank You goes ......(more)

Doing More with Less – My Productivity Guide - Houston, We Have a Problem We are living in a crazy world. And it just gets more and more crazy all the time. The amount of tasks we are confronted with at any given point in time is crazy. The number of interruptions we have in a single hour is crazy. ...(more)

Speaking at PASS Summit and why you need to think about submitting…. - This post is about the honor and experience of speaking at PASS Summit not once (2016) but twice (2017). I recently received an email from PASS HQ that asked past speakers to share our success stories – to help others consider submitting for PASS Summit ......(more)

Backup and Recovery

sp_RestoreScript 1.2 is now available - sp_RestoreScript 1.2 is now available HERE and on GitHub Changes include Service Broker options and the ability to restore a database in stand by, along with a couple of bug fixes. ...(more)

Azure SQL Database

Deleting Azure SQL Database VNETs - This is quite a new feature (currently in preview) but an important one where we now have the ability to isolate connectivity to a database to only a given subnet or set of subnets within your VNET. This is not a theory based blog post but a practical ......(more)

Azure CosmosDB

Designing a Cosmos DB database - Premier Developer consultant Julien Oudot brings us this blog with some considerations when designing a Cosmos DB database. The intent of this article is to summarize some of the key aspects to keep in mind while designing a database using Cosmos DB. Azure ......(more)

Analysis Services / BI on the MS Stack

Ten Ways To Help Your BI Consultant Be Successful - I’ve been working in the field of business intelligence for over ten years, as a consultant for over five years. One thing I’ve learned from that time is that consultants need the client’s help to complete a project on time and on budget. Even if the ......(more)

AI/Machine Learning/Cognitive Services

How Machine Learning, Classification Models Impact Marketing Ethics - Machine learning classification needs accurate data to avoid bad prediction that places marketing efforts in ethical peril. Here's an overview to help explain how data can brands at risk. ...(more)

Administration of SQL Server

SQL SERVER – FIX: Msg 3009 – Could Not Insert a Backup or Restore History Detail Record in the MSDB Database - While preparing for a demo session for my client, I was playing with SQL Server backups on my lab server. I encountered an error which I am going to blog here. Here is the error about backup or restore history details. Msg 3009, Level 16, State 1, Line ......(more)

Operating System Best Practice Configurations for SQL Server - Hi all, In this article, we are covering few important Operating System (Windows) best practice configurations for SQL Server. SQL Server is an user mode application runs on Windows Operating system and hence these configuration settings are important ......(more)

When to Use Row or Page Compression in SQL Server - A colleague asked me a very simple question: "How do I know to use row or page compression in SQL Server?" That's what this post is for, to help provide some clarity on row versus page compression. You're welcome. The post When to Use Row or Page Compression ......(more)

Query Store and “in memory” - If you’ve ever been to one of my sessions you know that I really like demos. I find they can illustrate how things work extremely well and I use them to compliment an explanation. I’m a very visual learner so it also helps me to understand and then ......(more)

Linked Server Elimination - SQL Server has a feature for partitioning tables and indexes. Partitioning can be implemented at many levels, however. You can create multiple tables for one logical data set, you can split the set into multiple databases, and you can even... The post ......(more)

SQL SERVER – How to Kill All the Processes for Any Database? - Recently during Comprehensive Database Performance Health Check, to test our unique fix, we had to run a stress test on my customer’s development environment. Once we were done testing our script, we did not need our stress test queries to run on our ......(more)

Managing SQL Server Transaction Log File - OverviewIn our previous section we have discussed how to create an audit trigger in SQL Server and how it helps for database security in different aspects and also knows how harmful it is for a database. Now, in this section, we will discuss SQL Server ......(more)

.NET Related Articles

Using C# Functions in Your Animations - In this article, Lance Talbert shows you how to add animations to your Unity project that take advantage of C# functions....(more)