The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I read a lot of history (as you no doubt notice from my other editorials). I’m currently reading an excellent book on the air war in World War One, Marked for Death: The First War in the Air. One of the fascinating aspects of the war is just how fast the technology shifted. At the start of the war airplanes were so underpowered that in a stiff breeze they would literally stand still in the air. By the end of the war they were travelling at nearly 200 miles per hour in a dive. Technology moved that fast.

On the one hand, I feel like technology just doesn’t move all that quickly any more. After all, we’re still using relational databases. That theory was worked out fifty years ago. On the other hand, you absolutely get a sense of being one of those early pilots. Think back on technology only 10 years ago. We were still arguing over whether or not virtual machines were viable as a mechanism for managing our data. Today, not only is almost everything virtualized, we’re working with containers and Platform as a Service offerings.

You really get the sense that, like those early pilots and aircraft manufacturers, not only are we just starting to figure out what to do with the machines we’re building, we’re also just starting to figure out the rules and implications of those machines. In World War One, the airplanes went from a novelty for some, possibly a useful scouting mechanism, but overall, not that important, to an absolutely vital part of the strategy to where establishing air superiority at the start of an offensive was the make or break for that offensive. We’re in the same place today where we have to establish exactly what and where we build our systems as a fundamental aspect of ensuring that those systems are successful.

It’s an exciting time to be a technologist.

Grant Fritchey

» Join the debate, and respond to today's editorial on the forums

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Webinars

Database DevOps Demo - Microsoft MVP Grant Fritchey, and Redgate’s Arneh Eskandari, will show you how Redgate’s Database DevOps solution works to improve your database development and deployment processes. You'll see a demo of tools from Redgate's SQL Toolbelt and see how they plug into GIT, Jenkins and Octopus Deploy. There will also be plenty of time to ask Grant and Arneh questions. Register your place....(more)

Virtualization and Containers

How I designed my VMware vSAN based Home Lab - (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) As you might know, I have already a quite powerful mobile ......(more)

Using Windows stored credentials to connect to SQL in containers - I work with SQL Server in containers pretty much exclusively when testing code and one of my real bug bears is that SQL Server in containers does not support Windows authentication (unless you’re using Windocks). So when I’m working I find it quite annoying ......(more)

Vendors/3rd Party Products

How to monitor the impact of patching on SQL Server performance - In this article, Redgate use the Spectre/Meltdown bugs as means to demonstrate how you can use a tool like SQL Monitor to assess the impact of patching on your SQL Server estate....(more)

How to use the SQL Prompt snippet placeholders for selecting and copying text - There are four SQL Prompt snippet placeholders that are all about selection and copying of text. These placeholders are very handy if, for example, you have two blocks of code that need to be part of a snippet. One part you hold on the clipboard, and the other part is a block of snippet text, selected using the $SELECTEDTEXT$ placeholder....(more)

Realistic, simulated data for testing, development and prototypes - This article proposes a scheme for using Redgate SQL Data Generator (SDG) as a ‘data generation and translation’ tool. We generate the data using a free copy of SQL Server Express, and then use standard bulk copy mechanisms to translate it to various formats, then distribute it out to the different data technologies and platforms that require a copy of the data. The tool becomes a mechanism to support ‘polyglot persistence’....(more)

NEW – Create, protect and manage non-production databases with SQL Provision - Just launched, SQL Provision allows users to create copies of SQL Server databases in seconds, using a fraction of disk space, and mask any sensitive data to help address data privacy and protection concerns. SQL Provision serves as a gateway between production and non-production environments, to ensure the safe distribution of database copies from one central location, without blocking the team’s development and release processes....(more)

Finding code smells using SQL Prompt: the SET NOCOUNT problem - SQL Prompt implements two static code analysis rules to check code for potential misuse of the SET NOCOUNT command -here's how they work....(more)

T-SQL

Inline Index Definition - There are a lot of features of SQL Server that I (and I know some of you), never really get around to using, usually because you have your ways, you have done things the same way for 100 years, and it all works fine enough. You may have heard of a feature, ......(more)

SQL SERVER – Performance Observation of TRIM Function - Earlier I wrote a blog post about how to remove leading and trailing spaces with TRIM function. SQL SERVER – 2017 – How to Remove Leading and Trailing Spaces with TRIM Function? After writing this blog post lots of people asked me its impact on the performance ......(more)

SQL Server: Avoiding a Sort with Descending Order - In this month's column I focus on queries that need to process data in descending order. ...(more)

Parsing file names from paths using T-SQL - These files are way neater than the ones on my laptop. SQL Server is great at a lot of things. I spend a lot of time blogging about all the cool things that SQL Server does. But (you knew there was a “but”, didn’t you) SQL Server isn’t great at everything. ...(more)

Testing Software

To TDD or not to TDD - To TDD or not to TDD? Is this question even relevant or maybe it’s enough to just write tests, no matter before the code or afterward? Let’s see. Pros and cons of TDD TDD (Test-driven development) has been a somewhat controversial topic for quite a while ......(more)

Tech News

Microsoft Sees Need For Regulation, Laws For AI Advances - The rapidly advancing area of artificial intelligence will require a new field of law and new regulations governing a growing pool of businesses involved, according to Microsoft Corp., a 25-year participant in AI research. ...(more)

All Democrats and one Republican support net neutrality bill in Senate - Enlarge / Democrats vs. Republicans. (credit: Getty Images | Linda Braucht) A Senate bill to restore the recently repealed net neutrality rules now has support from 50 of 100 senators and would pass if one more Republican backs the effort. The measure ......(more)

SQL Server Security

Checking Your SQL Server Instance for Spectre/Meltdown Patches - If you are running SQL Server 2008 through SQL Server 2017, you should be thinking about what you should be doing to protect your systems from the Meltdown and Spectre vulnerabilities. Microsoft has a number of KB articles that address this issue from ......(more)

SHA1 Collisions in SQL Server - Takeaway: It’s been frowned on for a while, but SHA1 is definitely broken for security purposes. In October of 2010, Michael Coles created a contest on his blog called “Find a Hash Collision, Win $100“. The contest was part of a discussion at the time ......(more)

How to get started with Always Encrypted for Beginners Part 3: One Two Punch - A few weeks ago, I wrote Part 1 and 2 of this series, which was a beginner’s guide to Always Encrypted. In part 3, I am going to go over what road blocks I ran into when trying to implement this solution for a client. The goal of the project was to prevent ......(more)

SQL Server News

Cumulative Update #10 for SQL Server 2014 SP2 - The 10th cumulative update release for SQL Server 2014 SP2 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......(more)

The January release of SQL Operations Studio is now available - This blog post was authored by Alan Yu, Program Manager, Microsoft SQL Server. We are excited to announce the January release of SQL Operations Studio is now available. Download SQL Operations Studio and review the release notes to get started. SQL Operations ......(more)

Cumulative Update #7 for SQL Server 2016 SP1 - The 7th cumulative update release for SQL Server 2016 SP1 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......(more)

Security news and thoughts

Streamlining Data Breach Disclosures: A Step-by-Step Process - Presently sponsored by: Build scalable, reliable and secure cloud native applications with Tech FabricI don't know how many data breaches I'm sitting on that I'm yet to process. 100? 200? It's hard to tell because often I'm sent collections of multiple ......(more)

Patch-Induced Reboot Errors Impact Kaby Lake, Skylake, Ivy And Sandy Bridge, Too - Intel's reboot problems also impact Kaby Lake, Skylake, Ivy Bridge and Sandy Bridge processors. ...(more)

The impromptu Slack war room where ‘Net companies unite to fight Spectre-Meltdown - Enlarge / The early disclosure of Meltdown and Spectre by Google and the fumbled responses by hardware vendors left cloud companies scrambling to react. So they united to fight the dumpster fire of poor communication and bad patches. (credit: US Air ......(more)

Meltdown and Spectre: Good news for AMD users, (more) bad news for Intel - Enlarge / Core M Broadwell (left) vs. Core M Skylake (right). (credit: Andrew Cunningham) The good news: Shortly after its initial release, Microsoft suspended shipping its Spectre and Meltdown Windows patches to owners of AMD systems after some users ......(more)

Reporting Services

Installing SSRS 2017 - Back in 2017 before SQL Server 2017 reached its GA release, I wrote a post about installing multiple instances of SSRS on the same host. I used SQL Server 2017 RC2 to do my testing and provide some screen shots. But by the time the GA release of SQL ......(more)

R Language

Finding the Titles in R - PASS has released the videos to members from this past Summit. I say TJay Belt today ask about relating a video name to a session. I have the USB drive, so I looked on there. Here are the videos: Not terribly helpful. If you run the HTML file from the ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI Custom Visuals Class (Module 86 – Bubble Chart by Akvelon) - In this module you will learn how to use the Bubble Chart Custom Visual by Akvelon. The Bubble chart allows you to show the value of categories proportionally represented by the size of the bubble. Module 86 – Bubble Chart By Akvelon Downloads Power ......(more)

Preventing Data Silos with Power BI - In this video, Patrick and Kalyn look at data silos and how to prevent it using Power BI. Different options exist, such as Analysis Services, Azure Analysis Services or models hosted in the Power BI service. The post Preventing Data Silos with Power ......(more)

Icon map, Report Layouts and Power BI Developer update - Introducing Layouts (@powerbitips) Power BI - Icon Map custom visual - Analysing and animating flight data (@jamesdales) Mean Time Between Failure (MTBF) and Power BI (@GregDeckler) Balanced Scorecards in Power BI Power BI Developer community January ......(more)

Power BI – Banding and stripes in charts - This blog will show a technique that can be applied to certain charts allow banding or stripes to help highlight data. This could be horizontal bands to help show data above or below a certain threshold, or this could be vertical bands to help highlight ......(more)

Creating Machine learning Development and Production Environment in Power BI - There is need to isolate the development and production environment in most of all projects. Setting up these environments help us to deliver a more reliable product to our customers. Machine learning projects also need a development, Test and Production ......(more)

Performance Tuning SQL Server

How Much Can One Row Change A Plan, Part 4 - Monte Carlo Or Bust In Part 3, I showed you how two queries with TOP clauses can produce wildly different plans. To figure some stuff out about why the plans changed, let’s focus on the Nested Loops Join plan. Why? Because when I force the Hash Join ......(more)

SQL Server 2017 CU3 adds optimizer row goal information in query plans - Row Whats? Like it or not, the optimizer has row goals. We’re not going to go terribly in-depth here, because the point is just to make you aware that this is in executions plans, and I’ll be adding it to the FRK in an upcoming release. I am going to ......(more)

When Does Index Size Change Index Choice? - Size Matters? One thing I hear a lot is that the optimizer will take the size of an index into account when choosing which one to use. The tl;dr here is that it may make more of a difference when it comes to index width  (think the number of columns ......(more)

How Much Can One Row Change A Plan, Part 3 - All The Rage We’ve asked how much one column and even one row can change a plan before. The doctor says I’m not allowed to read blog comments anymore on account of my blood pressure, but the AI I wrote in PowerShell assures me they were well-received. So ......(more)

Getting perfmon data into SQL Server, Part III - In this post I show you how to deploy in a simple way the solution previously proposed to Getting perfmon data into SQL Server to multiple servers running multiple SQL Server instances. I didn’t think about this post to be honest, and I believed the ......(more)

Limited Giveaway – The Case of the Slow Temp Table: A Performance Tuning Problem - I have a new course that I’d love to test out on a few willing students! I’m giving away a limited amount of one-month free memberships for “The Case of the Slow Temp Table: A Performance Tuning Problem.” If you’re willing and able to take the course ......(more)

Hardware News

Samsung Starts Mass Production of 16Gb GDDR6 Memory ICs with 18 Gbps I/O Speed - This week, Samsung has announced that it has started mass production of its GDDR6 memory chips for next-generation graphics cards and other applications. The new chips will be available in 16 Gb densities and will feature an interface speed that is significantly ......(more)

ADATA at CES 2018: Incoming XPG SX8200 SSDs, From 240 GB to 1.92 TB in M.2 - LAS VEGAS, NV — At CES 2018, ADATA demonstrated its new high-end XPG SX8200 SSDs. The new drives use Silicon Motion’s SM2262 controller as well as 3D TLC NAND. The SSDs will be available in configurations featuring up to 2 TB of raw 3D TLC NAND memory ......(more)

HA/DR/Always On/Clustering

SQL SERVER – Rule Cluster Remote Access Failed During Installation on SQL Failover Clustered Instance - Not every problem might have a solution, and we must use a workaround to move forward. This blog is also related to one such error message which I observed while installing a clustered instance of SQL Server. Here is the error in the UI Rule “Cluster ......(more)

DevOps and Continuous Delivery (CI/CD)

DevOps for Data Science – Application Performance Monitoring - In this series on DevOps for Data Science, I’ve explained the concept of a DevOps “Maturity Model” – a list of things you can do, in order, that will set you on the path for implementing DevOps in Data Science. The first thing you can do in your projects ......(more)

DevOps, DataOps, and Data Repositories - Click to learn more about author Paul Stanton. There is a disconnect between the goals of DevOps, and the realities of working with relational data.    Relational databases are core to many enterprise applications, and the Gartner Group projects that ......(more)

Bringing DevOps to the database. Part 1: Version control - For some years now, DevOps practices have been exciting application developers with their promise of short iterations, fast releases, and features that get into the hands of users sooner. Those same practices are now entering the database space, but how can database development adapt, and where should it start?...(more)

Data Visualisation

Types Of Visuals, Part 1 - This is part six of a series on dashboard visualization. Today, we will look at a few of the many types of visuals available to us.  For each of these, I’ll cover some general information about the visual, as well as good uses on a dashboard.  All of ......(more)

Data Privacy

A Few Words about GDPR, Data Privacy, and this Blog - GDPR compliance may be hard, but nothing worthwhile is ever easy. To me, data security and privacy are worth the extra effort. The post A Few Words about GDPR, Data Privacy, and this Blog appeared first on Thomas LaRock. ...(more)

Killing Anonymity: The Question of Data Privacy - Click to learn more about author Cathy Nolan. Maybe we should ask, “who or what killed anonymity?”  The question being debated over and over again is “have we given away our privacy for convenience and security?”  It would appear that the answer is “yes”. ...(more)

Are You Ready for GDPR? - Click to learn more about author Victor DeMarines. As the May 25, 2018 deadline for the General Data Protection Act (GDPR) regulations inches closer, there isn’t a compliance officer on the planet who isn’t consumed with making sure every asset is covered. ...(more)

Conferences and Events

Save the date: February 28, 2018 – More free SQL Server training the Redgate way - Following the popular response to SQL in the City Streamed, Redgate’s free virtual event which has been held annually for the past two years, it has just bee announced that SQL in the City Streamed will now be happening every quarter during 2018....(more)

Computing in the Cloud (Azure, Google , AWS)

Microsoft Adds Custom Cloud Assessments to Azure Security Center - Azure customers can now add their own rules to Azure Security Center's OS assessments for cloud instances. ...(more)

Right-sizing for a cloud service - When it’s time to purchase new resources for one or more applications, the non-scientific approach of guesstimating the requirements can be expensive, whether for on-premises capital expenditures or cloud-based services. For stakeholders deciding on ......(more)

Career Growth

Updated YouTube Channel - Hello all, I’ve recently updated my YouTube channel. I’m going to be posting a lot more videos up there. If you would like to take advantage of it, please go here and click on the subscribe button. I’ll be covering pretty much the same type of thing ......(more)

Big Data

Companies Don’t Need BIG Data, They Need Right Data - Click to learn more about author Chirag Shivalker. Customers are engaging with businesses through ever increasing touchpoints including websites, social media, in-store, mobile, and tablets. It’s really ironic that irrespective of how they engage with ......(more)

Backup and Recovery

SQL Server backups to Azure Blob storage - This is an intro level post, specifically written for the new and accidental DBAs that have been told to direct their SQL Server backups to Azure storage…but without any additional information.  It does not include discussions around backup types, recovery ......(more)

Azure SQL Database

Compatibility Level 140 is now the default for Azure SQL Database - Database compatibility level 140 is now the default for new databases created in Azure SQL Database across almost all public regions.    As of this writing, there are already 539,903 databases in Azure SQL Database running in compatibility level 140. Questions ......(more)

Analysis Services / BI on the MS Stack

Historical Stock Prices and Volumes from Python to a CSV File - Python is a versatile language that is gaining more popularity as it is used for data analysis and data science. In this article, Rick Dobson demonstrates how to download stock market data and store it into CSV files for later import into a database system....(more)

Mobile Report Publisher – Dashboards Everywhere - The new feature, Mobile Reports, was added to SSRS in 2016. It provides the ability to create attractive and useful dashboards that can run on devices and most browsers. Robert Cain shows you how to get started building Mobile Reports with the Mobile Report Publisher....(more)

AI/Machine Learning/Cognitive Services

SQL Server Machine Learning Services – Part 4: Finding Data Anomalies with Python - The series so far: SQL Server Machine Learning Services – Part 1: Python Basics SQL Server Machine Learning Services – Part 2: Python Data Frames SQL Server Machine Learning Services – Part 3: Plotting Data with Python SQL Server Machine Learning Services ......(more)

Software Testing Skills Must Shift with AI - Artificial intelligence (AI) is touching many parts of our lives and careers. Rama Anem discusses the impact of AI on software testing and how testers’ skills must adapt....(more)

Administration of SQL Server

What Does REPAIR_ALLOW_DATA_LOSS in SQL Server Signify? - REPAIR_ALLOW_DATA_LOSS is the repair level, which is recommended by DBCC CHECKDB when the database is found in a corrupted state.SQL users and even administrators at times get confused about the real functioning of this command. It is to be noted that ......(more)

Database Corruption and IO Errors - A quick way to have your day turned upside down and rip your gut out with nerves and anxiety is to come in one day to find that users are panicked, applications are not working and the HelpDesk team is curled up in the fetal position in the corner. Why? ......(more)

How to Run DBCC CHECKDB to Check SQL Database Integrity - Database Console Command CHECKDB (DBCC CHECKDB)is used to check the integrity (physical & logical) of objects in a SQL Server database.The command is supported in databases that contain memory-optimized tables but the validation is only supported in ......(more)

SQL Server 2017 DMVs and DMFs for DBAs - Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL ......(more)

Building a Custom Blocked Process Report - Blocking in SQL Server is normal behavior, but excessive blocking can lead to many complaints and is a symptom of poor performance. SQL Server contains a vast amount of information that can help when troubleshooting performance issues like blocking, but understanding and gathering that information when you need it is a lot of work. Darko Martinovic explains his solution for a custom blocking report....(more)

.NET Related Articles

Introduction to Game Development with Unity and C# - The easiest technologies to use are often the most difficult to create. Take video games. Objects in games must obey the laws of physics among countless other details. Developers can spend their time with details like these, or take advantage of game engines so they can concentrate on what the game does instead. Lance Talbert explains how to get started developing games in C# with the game engine Unity....(more)

HTTP Response Headers in ASP.NET Core - ASP.NET Core has the flexibility to add HTTP response headers anywhere in the middleware pipeline. Dino Esposito explains what you need to know to handle the headers in ASP.NET Core....(more)