The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I went with my daughter the other day to set up her bank account. She's becoming a young adult and this is one of those items we need to get done, especially in today's world of electronic banking and money transfers. It's time she takes another step towards being responsible and accountable for her finances.

As we went through the process, one of the steps was her setting up an account with the online presence of this bank. I told her to be sure to use a new password, one not in use anywhere. She agreed, but then said she has 3 or 4 passwords that she uses in different places. I told her that's not enough, and explained why. This week, we have an article about a MySpace security flaw in 2017.

Most of you don't use MySpace, but you might have. Even if you haven't, your kids or parents might use some popular tool, such as Facebook, Snapchat, Instagram, etc. Tomorrow we might discover a similar security issue, or worse, a disclosure of your passwords. If you think it can't happen, go try a few emails over at click here. You might be surprised at the results. My kids were.

It's not that your bank is necessarily vulnerable to hacking a password. However, if you've used the same (or very similar password like PasswordCNNdotcom) for your news reading at CNN, then if that company is hacked, it's not too difficult to take all emails and then try PasswordChasedotcom at Chase National Bank's site.

Reusing passwords is a bad idea. I do it for demo accounts across virtual machines, and even that bothers me. Everywhere else, I use a password manager and I have hundreds of passwords, separate ones for each site. My wife is annoyed if she needs a password for any of our accounts and I give her something like "4Gbv8A^f8" on my phone, but I'm not going to make simpler ones or reuse them.

Separate accounts and separate passwords are a mantra I've used for years at work and at home. Please spread the word and get those in your life to use separate, strong passwords for their online activities.

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.

Webinars

Extending DevOps practices to SQL Server databases – Branching & Merging with VSTS - Join Microsoft Data Platform MVP Steve Jones for the next Database DevOps demo webinar. He’ll be plugging Redgate’s tools into Visual Studio Team Services and showing how you can branch and merge your database changes....(more)

Vendors/3rd Party Products

SQL Data Mask: new masking features, constants and composition - Find out what's new, what's next, and what's coming soon to Redgate's free prototype tool, SQL Data Mask. Don't forget to let them know what you think - if you want to help shape what data masking by Redgate should look like, the team are always available to talk!...(more)

T-SQL

Representing a simple hierarchical list in SQL Server with JSON, YAML, XML and HTML - How difficult can it be to produce a simple hierarchical list in JSON, YAML, XML and HTML from a SQL Server table that represents a simple hierarchy within an organisation. Well once you know, it is easy and William Brewer is on a mission to tell you how....(more)

SQL Data Aggregation Aggravation - When we have to deal with and store a lot of data, it makes sense to aggregate it so that we store only the information we actually need. If we get this right, this works well, but the design of the system takes care and thought because the problems can be subtle and various. Joe Celko describes some of the ways that things can go wrong and end up providing incorrect, inaccurate or misleading results....(more)

Tools

Monospaced Programming Fonts with Ligatures - Typographic ligatures are when multiple characters appear to combine into a single character. In this post Scott Hanselman looks at what's available for ligatures in programming fonts....(more)

SQL Server on Linux

Running Linux Containers on Windows - This post is a step-by-step guide to getting Linux containers running on your Windows 10 machine....(more)

SQL Server News

Microsoft Delivers First SQL Server 2017 Release Candidate - SQL Server 2017 took a major step closer to its official release date this week. After a steady trickle of community technology previews, on July 17 Microsoft announced the general availability of the AI-enabled database software's first release candidate (RC1). ...(more)

SQL Server Internals

The Fundamentals Of Processing A SQL Server Workload In A Scale-able Manner - In this blog post Chris Adkin distills down the most fundamental points to consider when attempting to process a SQL Server workload in a scale-able manner. However, many of the principles outlined can be applied to any kind of data processing platform....(more)

Software Development

Polyglot Programmer - Doug Seelinger has been on something of a language kick lately. In this post he talks about the languages he's fluent in, the languages he'd like to be fluent in, and the language he kind of wishes he wasn't fluent in....(more)

Security news and thoughts

Myspace Security Flaw Allows For Easy Account Takeover (Yes, Myspace) - How many people know your birthday? Well, according to a security researcher, a security flaw offers all those people easy access to your Myspace account. That's not as big a deal now as it would've been when Myspace reigned supreme, but it could still put your personal information at risk....(more)

R Language

Managing Loan Credit Risk using Microsoft R Server - Principal Program Manager at Microsoft, Bharath Sankaranarayan, explains how you can apply machine learning to manage risky loans – the ones that have a high probability of defaulting....(more)

Getting started with R - Slaviana Pavlovich explains what the R programming language is, how to get set up, and how start using it....(more)

The Fast Route from Raw Data to Analysis and Display - There are very few real breakthroughs in IT. Most progress in the industry comes from pure effort and attention to detail. Probably, in retrospect, the greatest breakthrough moment in IT was the emergence of the HTML browser when markup met TCP/IP. I get that same feeling of breakthrough with R and the relational database....(more)

Lightning Talk: R in Minecraft - A five-minute tour of the miner and craft packages and associated book designed to teach kids how to use the R language while manipulating the world of Minecraft....(more)

SQL Server R Services: Digging into the R Language - It is not just the analytic power of R that you get from using SQL Server R Services, but also the great range of packages that can be run in R that provide a daunting range of graphing and plotting facilities. Robert Sheldon shows how you can take data held in SQL Server and, via SQL Server R Services, use an R package called ggPlot that offers a powerful graphics language for creating elegant and complex plots....(more)

PowerShell

Regexp with PowerShell, more than a match - In this article Klaas Vandenberghe shows where regular expressions can appear in PowerShell. You probably know you can use a regexp in -match, but there are some other interesting uses....(more)

PowerShell for Assigning and Querying Tags in Azure - Melissa Coates explains why tags in Azure are useful pieces of metadata for annotation, and shows how to assign and query them using PowerShell....(more)

PowerPivot/PowerQuery/PowerBI

SUM() vs SUMX() - Matt Allington takes a fresh look at the topic of when to use SUM(), and when to use SUMX()....(more)

The Best of Both Worlds: Using Excel and Power BI Together - Excel and Power BI work well together. This allows you to use the two tools together to provide for many types of business workflow and BI practices. You can publish an Excel file to Power BI to share with others, analyse a Power BI dataset in Excel or import either an Excel workbook or Excel data to Power BI. You can gain the workgroup power and business-orientation of Power BI without losing the ease and versatility of Excel.. Saurabh shows how....(more)

Internet of Things

Segway Hoverboard Hacking Demonstrates IoT Security Risks - Riding a hoverboard requires a certain amount of balance, but no amount of physical dexterity will help a rider whose hoverboard has been taken over by hackers. ...(more)

ETL/SSIS/ELT

Compressing files from the SSIS Script Task - Mark Broadbent shows how to compress files within Integration Services using a script task....(more)

Database Design, Theory and Development

SQLskills SQL101: Creating SQL Server Databases - Whether it is done with the SQL Server Management Studio (SSMS) GUI, or with a T-SQL CREATE DATABASE command, many people and organizations are creating new SQL Server databases without really thinking about what they are doing, and without taking advantage of a number of beneficial options and properties....(more)

What I Love About the Stack Overflow Database - Brent Ozar writes a love letter to his favorite replacement for AdventureWorks....(more)

Database Fundamentals #5: Database Properties - Don’t let the ease of creating databases lull you into a false sense of security. They actually can be very complicated....(more)

Azure SQL Database

How to check connectivity to Azure SQL DB - Sometimes when connection to Azure SQL DB has failed you want to test the network layer to confirm this is not a network issue - here's how....(more)

Analysis Services / BI on the MS Stack

What’s new in SQL Server 2017 RC1 for Analysis Services - The RC1 public preview of SQL Server 2017 is here! In this post, Christian Wade takes us through the new features it bring to Analysis Services....(more)

When not to use Management Reporter - Management Reporter is easy to use to create Management Reports and it does not require a developer to do it. But there are times when Management Reporter is not the right tool for the job....(more)

AI/Machine Learning/Cognitive Services

Azure Machine Learning Algorithm Flowchart - When you are working with Machine Learning there are many things you need to keep an eye on. You need to prepare the data, deal with any missing values and you need to select an algorithm you will be using for you model. Choosing an algorithm can be a difficult one though. Azure Machine Learning alone has around 25 different algorithms, each one with its own pros and cons. To help out beginning Advanced Analytics and Azure Machine Learning users Dataheroes designed a flowchart to help you select an algorithm quick and easily!...(more)

Adopting AI in the Enterprise: Ford Motor Company - Driverless cars aren’t the only application for deep learning on the road: neural networks have begun to make their way into every corner of the automotive industry, from supply-chain management to engine controllers....(more)

Announcing the Cognitive Services White Glove Program for All Azure Government Customers - Microsoft Program Manager, Julie Glixon, explains what the Azure Cognitive Services White Glove Program program is, and how you can get involved....(more)

Administration of SQL Server

Downgrading SQL Server Editions - At some point in your career working with SQL Server, you will run into a situation where the wrong edition of SQL Server has been installed on a server and will need to change the edition for licensing reasons....(more)

SQL Server Agent and Daylight Savings time - Matthew Mcgiffen looks at how daylight savings time affects SQL Server Agent jobs....(more)

Office Hours 2017/07/05 - This week, Brent and Tara discuss separating data and logs on separate drives, statistics time, scalar UDFs, licensing, encryption, gathering SQL error logs and agent job history, replication, upcoming training courses from Brent Ozar Unlimited, and what DBA topics they struggle with the most....(more)

SQL 2016 SP1 Shows You Wait Stats in Execution Plans - or does it? - SQL 2016 SP1 Shows You Wait Stats in Execution Plans. Or Does It? Brent Ozar demos why you shouldn't trust this new feature just yet....(more)

Auto Starting SQL Server Agent - When you stop SQL Server service, SQL Server Agent service also stops, but when you start SQL Server again, SQL Server Agent doesn't automatically restart. Dennes Torres shows a way of making this happen....(more)

SQLCLR in Practice: Creating a Better Way of Sending Email from SQL Server - SQLCLR is now considered a robust solution to the few niche requirements that can't be met by the built-in features of SQL Server. Amongst the legitimate reasons for avoiding SQLCLR, there is the fear of getting bogged down in code with special requirements that is difficult to debug. Darko takes a real example, extending the features of sp_send_dbmail, to demonstrate that there need be few terrors in SQLCLR....(more)

Investigating the Cause of SQL Server High CPU Load Conditions When They Happen - Any DBA who is trying to find the cause of an intermittent problem with a server or database dreams of being able to use a query or procedure take a snap of the relevant variables at the point when the problem occurred. Laerte takes an example of a slow-running query hogging resources to show that you can run queries when a WMI alert is fired, and save the results for later inspection, whenever it happens....(more)

.NET Related Articles

Do people write insane code with multiple overlapping side effects with a straight face? - Who writes code like that with a straight face? It's one thing to write it because you're trying to win the IOCCC or you're writing a puzzle, but in both cases, you know that you're doing something bizarre. Are there people who write a -= a *= a and p[x++] = ++x; and think, "Gosh, I'm writing really good code?"...(more)

13 hours debugging a segmentation fault in .NET Core on Raspberry Pi and the solution was... - Debugging is a satisfying and special kind of hell. You really have to live it to understand it. When you're deep into it you never know when it'll be done. When you do finally escape it's almost always a DOH! moment....(more)

Revisiting Script Injection in ASP.NET - The danger of Cross-Site Scripting (XSS) has to be dealt with in any web application. You do this by validating the input from all possible channels. by constraining it in terms of its range, type and length, and by encoding the output from views. ASP.NET has some built-in validation of requests that can be extended to make it more effective, but this approach has changed with ASP.NET Core to place the onus on the application developers to provide the middleware to perform effective validation that is fine-tuned to the application. Dino Esposito explains....(more)

Anti-forgery Validation in ASP.NET Core - Hackers use the cross-site request forgery technique to grab the identity and privileges of legitimate authenticated users of a site to then perform any action that the victims have rights for. Without a way to detect this, the site will have no way to distinguish between a forged and legitimate request, ASP.NET has a robust defense if you remember to implement it, and Core introduces some other ways to ensure that this forgery can't succeed....(more)