The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Have you ever sent a password to anyone else through email or text? Perhaps a colleague trying to solve a problem on a system? Maybe to family or friends? I know I've had to send passwords (reluctantly) to my wife and kids at times. I hate doing that and usually change the password shortly thereafter. I think the idea of passwords lingering in email, or really any digital plaintext is a bad idea.

Note: It's easy for me to change passwords since I use Password Safe. I think everyone should use a Password Manager, as do others.

I ran across a piece this week that noted 10mm Android phones have malware that has rooted their operating system. For the most part this malware is designed to show ads and install apps. However, is it a reach to think that someone will use malware at some point to read text messages and email? After all, both of those functions are tightly built into iOS, Android, and Windows Phone at the OS level. If you've connected an account, it's possible that malware could access those applications.

From there, it's a short leap to being searching through messages for "password" and perhaps forwarding on information. While many databases aren't connected to the Internet, some are, and certainly others might be valuable enough credentials to sell. While security works well in layers, it's not perfect and determined hackers can likely crack some of those layers. Especially if you also have a VPN on your mobile device.

Mobile devices are becoming ubiquitous, for everyone. It's not just technical people that now have access to internal systems from mobile systems as everyone from low level marketing people to high level executives is becoming comfortable with accessing information regularly, from anywere, at any time. This means that our security is inherently weaker because we allow access.

There isn't a good solution to this, but certainly I think it behooves us to be very careful about what credentials we send in communications, as well as having ways to revoke access and issue new tokens. Education helps as well, but anyone can be taken in by some of the clever individuals out there intent on installing something on your device.

I also think that monitoring and regular audits of access is important, to ensure that when an unauthorized access takes place (and it likely will), that you can shut it down as quickly as possible. This security race is never going to be over, and we'll never prevent all attacks. However, we can learn to respond quickly.

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.

Vendors/3rd Party Products

Redgate at SQLServerGeeks Annual Summit 2016 – Bangalore - For the first time this August, the Redgate team are heading to SQLServerGeeks Annual Summit 2016. Catch some great sessions from Grant Fritchey and Alex Yates over the three days, or just stop by and say hello at the booth. With 5 tracks, over 100 sessions, and multiple deep dive pre-con seminars, Asia’s only Data Platform conference is an event you do not want to miss! There’s only a month to go, so get your tickets now....(more)

Tech News : Data Mining/Warehousing

Google’s DeepMind AI to use 1 million NHS eye scans to spot diseases earlier - Google’s DeepMind division has announced a partnership with the NHS’s Moorfields Eye Hospital to apply machine learning to spot common eye diseases earlier. The five-year research project ......(more)

Tech News : Security

Introducing unverified breaches to Have I been pwned - Data breaches can be shady business. There's obviously the issue of sites being hacked in the first place which is not just shady, but downright illegal. Then there's the way this information is redistributed, the anonymous identities that deal with ......(more)

10 million Android phones infected by all-powerful auto-rooting apps - Security experts have documented a disturbing spike in a particularly virulent family of Android malware, with more than 10 million handsets infected and more than 286,000 of them in the US. Researchers from security firm Check Point Software said the ......(more)

Security insanity: how we keep failing at the basics - Some days, it just feels like the world is working against you or in the case of today, like it's all just going to metaphorical security hell. As much as we like to keep pushing the needle further around the "strong security dial" with things like security ......(more)

Tech News : The Lighter Side

I'm a car enthusiast - bring on self-driving cars! - I've had this post in mind for a while now. It's a little tangential to the sort of stuff I'd normally write, yet it's something I'm passionate about and has become more topical in the last few days. The catalyst for finally completing this piece came ......(more)

SQL Server News

The Top 10 Feature Requests for SQL Server on Connect.Microsoft.com - When a man loves a woman very much, he goes to Connect.Microsoft.com and upvotes her feature requests. I was curious to see what requests were resonating the most with folks, so I went through the list of feature requests sorted by votes: 1. Please fix ......(more)

Developing Databases using Always Encrypted with SQL Server Data Tools - We are excited to announce that SQL Server Data Tools (SSDT) now supports developing databases using Always Encrypted. Always Encrypted is a feature in SQL Server 2016 and Azure SQL Database that ensures your sensitive data is never revealed in plaintext ......(more)

Announcing SQL Server Management Studio – July 2016 Release - Today, we are very pleased to announce the first monthly update of SQL Server Management Studio (SSMS) following the SQL Server 2016 release. This update remains a fully-supported generally-available (GA) quality release. It includes support for Azure ......(more)

Internet of Things

The Internet of Things Could Keep Dirty Coal Plants in Business - Digitization promises lower annual emissions but could increase them over plants’ lifetimes. ...(more)

Hardware News

Samsung Rolls Out Its First UFS Cards: SSD Performance in Card Form-Factor - Samsung today introduced the world’s first flash memory cards in UFS form-factor. The cards are compliant with the UFS Card 1.0 specification and offer performance comparable to that of desktop SATA SSDs. It is worth noting that the new cards are not ......(more)

Blogs : Administration

SQL Server 2016 Query Store, User Data or Metadata? - One of the newest features for SQL Server is the Query Store, let’s explore it to know a bit more about it It’s been a month since the SQL Server community has their new toy, SQL Server 2016, which to my surprise and considering not everyone has yet ......(more)

A potential improvement for statistics updates : MAXDOP - So, in SQL Server 2016, statistics updates using sample mode now run in parallel under compatibility level 130, and this is how it works by default, for all automatic and manual statistics updates. This is explained briefly here: Query Optimizer Additions ......(more)

How to Attach MDF Data File Without LDF Log File – Interview Question of the Week #078 - Just the other day I was busy during a performance tuning exercise I explained to the team of DBA that how much log file is critical and important for their performance. Once I was explaining the importance and role of the LDF file in the performance, ......(more)

SQL Server Diagnostic Information Queries for July 2016 - This month, I have several new improvements in the SQL Server 2014 and 2016 sets, along with additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets. I have gotten quite a bit of interest about making a special version of these ......(more)

Breaking News: 2016 Query Store cleanup doesn’t work on Standard or Express Editions - If you’re using SQL Server 2016’s awesome new feature, Query Store, there’s a new bug with automatic cleanup. Books Online explains: Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store ......(more)

TDE and Backup Compression: Together At Last - TDE is one of those things! You either need it, and quickly learn how many things it plays the devil with, or you don’t need it, and there but for the grace of God go you. Off you go, with your compressed backups, your instant file initialization, your ......(more)

Know How to Handle Deadlock in SQL Server - Introduction To DeadlockDeadlock in SQL server is a condition in which two or more system server processes IDS (SPIDs) are waiting for a resource. No process can get the resource as the other processes are preventing it from getting the resource. It ......(more)

Database Mail Breaks with TLS 1.0 Disabled - Discovery I ran across an issue where I had just configured database mail on a new server, went to send a test email, and I waited to receive it. I waited…and waited…and waited, but still nothing. I started to investigate and verified that database ......(more)

SQL Server Event Handling: DDL Events - For many SQL Server professionals, our first overt need to handle an event was for a data-related action (ie an insert, update, or delete). Naturally, we use triggers to handle these events. Or more specifically, DML triggers. And if you are familiar ......(more)

Blogs : Career

Carpenters vs. DBAs - Let’s get the caveat out of the way up front, I work for a tool vendor. If you look around at the tools landscape for the Microsoft Data Platform, it’s really interesting. There are a few tools vendors, primarily clustered around monitoring tools, and ......(more)

Blogs : Computing in the Cloud

Everything you need to know about loading a free Let's Encrypt certificate into an Azure website - Let us start with what's wrong with the world today, and that's certificate authorities. Just take a look at the trusted root CAs running on a Windows 10 machine: The very premise of having these root CAs on your machine is that they ultimate get to ......(more)

Blogs : Data Mining/Data Analysis

Implementing Fuzzy Sets in SQL Server, Part 1: Membership Functions and the Fuzzy Taxonomy - In the first installment of this amateur self-tutorial series on applying fuzzy set theory to SQL Server databases, I discussed how neatly it dovetails with Behavior-Driven Development (BDD) principles and user stories. This is another ......(more)

Blogs : Events

PASS SQL Saturday #567 Slovenia 2016 - So we are back again The leading event dedicated to Microsoft SQL Server in Slovenia will take place on Saturday, 10th December 2016, at the Faculty of Computer and Information Science of the University of Ljubljana, Vecna pot 113, Ljubljana (http://www.fri.uni-lj.si/en/about/how_to_reach_us/). ...(more)

PASS Business Analtyics VC July Meeting – Incorporating R with SQL Server 2016 - The next PASS Business Analytics Virtual Chapter meeting is coming up this week on Thursday, July 14. This month we welcome Microsoft MVP Ginger Grant (b|t) who will talk to us about using R with SQL Server 2016. –> REGISTER NOW (more)

Blogs : High Availability/Disaster Recovery

Availability Group Direct Seeding: Mind Your Backup Jobs - I’ll get right to the point While you’re Direct Seeding, you have to be careful with any other full or differential backup jobs running on the server. This is an artifact of the Direct Seeding process, but it’s one you should be aware of up front. In ......(more)

Blogs : Integration Services/ETL

SQL Server 2016 Master Data Services service accounts are broken - When setting up the web application for MDS you must specify an Active Directory service account to use for the application pool. What's hilarious about this is that this will break your MDS installation unless you also add that user as a local Administrator ......(more)

SSIS Tip: Using a Filepath Parameter for Multiple Flat Files - SSIS with SQL 2012 and above brought a lot of great enhancements to SSIS that ease deployment and reconfiguration of packages. The project deployment model, SSIS Catalog, and parameters make it a lot easier to manage SSIS. It also helps decouple environmental ......(more)

Use Biml to Remove Spaces from Flat File Column Names - I hate a space in a database column name. Kent Bradshaw and I are updating our demo for loading National Provider Index (NPI) file data (We plan to present on this soon… more later…). The NPI files currently have 329 fields and almost every one of them ......(more)

How to retrieve Cell Properties from a SSAS MDX query using SSIS - Scenario This is a short article where I want to share something I discovered recently. I worked on an SSIS package where I had to query a SSAS cube using MDX and pass cell properties such as the VALUE and FORMATTED_VALUE down the data path to an output/destination ......(more)

Phantom SSIS Changes - That SSIS change just won’t stick! There is a new business requirement, so now you have to change an existing SSIS package. You opened up a new project, imported the package, and made your changes. Adding the original package to the new project. Changes ......(more)

Blogs : Machine Learning

10 Ways Manufacturing is Being Changed by Machine Learning - by Angela Guess Louis Columbus recently wrote in Forbes, “Machine learning’s core technologies align well with the complex problems manufacturers face daily. From striving to keep supply chains operating efficiently to producing customized, built- to-order ......(more)

Inside Intel’s Big Machine Learning Announcements - by Angela Guess Rob Farber of TechEnablement recently wrote in InsideHPC, “Intel provided a wealth of machine learning announcements following the Intel® Xeon Phi™ processor (formerly known as Knights Landing) announcement at ISC’16. Building upon the ......(more)

Machine Learning: From Then Until Now - Machine Learning is a form of Artificial Intelligence (AI) which allows computers to learn by way of observation and experience, rather than rigid pre-programming. Machine Learning uses computer programs that are capable of growth and change as they ......(more)

Blogs : MDX/DAX

Optimizing DAX expressions involving multiple measures - Writing measures referencing other measures is in general a good idea that simplifies the DAX code, but you might face specific bottlenecks. This article describes which performance issues might arise when different measures aggregate the same column ......(more)

Blogs : Performance and Tuning

SQLSweet16!, Episode 3: Parallel INSERT … SELECT - Sanjay Mishra, with Arvind Shyamsundar Reviewed By: Sunil Agarwal, Denzil Ribeiro, Mike Ruthruff, Mike Weiner Loading large amounts of data from one table to another is a common task in many applications. Over the years, there have been several techniques ......(more)

SQL Server Quickie #37 – Inner Joins - (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.) Today I have uploaded SQL Server Quickie #37 to YouTube. ...(more)

Which Indexes are “Disk Based” in SQL Server? - I was looking through some terms in SQL Server documentation the other day, thinking about what it’s like to learn about SQL Server’s indexes when you’re new to the field. I jotted down a note: B-tree = Rowstore = Disk Based And then I realized that’s ......(more)

Blogs : Polybase

Why Use Polybase? Looking At Bigger Data Sets - So far, I’ve used two data sets in my Polybase series: a set of 777 second basemen and a set of about 1.3 million flights. Neither of those really helps us get to the value behind Polybase. All(ish) Flights As a result, I want to look at all flights ......(more)

SQL Server 2016 Polybase Services stuck in Change Pending state - I’ve been using the Polybase services in SQL Server 2016 all throughout the pre-release period without any issue. When I tried using them in the GA (Generally Availability) release, I could never get the two Polybase services to start. They were always ......(more)

Polybase: Forcing Predicate Pushdown - It’s been a few days since my last Polybase post, so time to get back on the saddle. Reminder: Where We Were Going back a few posts, I wanted to join a SQL Server table to an HDFS-driven external table. Here’s the query that stumped me: SELECT ......(more)

Blogs : PowerPivot/PowerQuery/PowerBI

What’s wrong with the Power BI KPI Visual? - Over the past couple of weeks I have been asked several times about the KPI Visual that is native to Power BI. Each time the same, “it does not appear to work properly.” Well, I wasn’t sure what all the confusion about. So, I decided to spend some ......(more)

Step Descriptions And Comments In M Code In Power BI Desktop - The June release of Power BI Desktop has what seems to be a fairly unremarkable new feature in that it allows you to add descriptions to each step in a query in the Query Editor window. However the implementation turns out to be a lot more interesting ......(more)

Power BI Row Level Security - In the June update of the Power BI Desktop there were some really cool features that were added. I’d encourage you to review the full Power BI blog to see all the June updates. My favorite of all these new features is Row Level Security (RLS) for Power ......(more)

Power BI – Checking Query Folding With View Native Query - A really great feature that was silently added into June update of the Power BI Desktop is a feature called View Native Query. This feature is integrated into Power BI Query Editor and appears to give you the ability to see the queries that are running ......(more)

Power BI Custom Visuals Class (Module 06 – Histogram) - In this module you will learn how to use the Histogram, a Power BI Custom Visual. A Histogram is a column chart which shows the distribution of occurrences divided into categories, called bins. This type of chart is useful for estimating density and ......(more)

Blogs : R Language

useR! 2016 Tutorials: Part 2 - by Joseph Rickert Last week, I mentioned a few of the useR tutorials that I had the opportunity to attend. Here are the links to the slides and code for all but two of the tutorials: Regression Modeling Strategies and the rms Package - Frank HarrellUsing ......(more)

SQL Server 2016 R Integration - One of the biggest changes in SQL Server 2016 on the analytics front is the inclusion of R into the database engine. read more ...(more)

Run compiled R packages in AzureML - We've shown a few times here how you can run R code on data in the cloud with Azure ML Studio, and even how to enable that code as a web service to be called from other applications. But what if you want to run code in a compiled language, like C++? ......(more)

Blogs : Reporting Services

Export reports to PDF with improved support for international text - Today’s post is from Andre Milbradt, an engineer who’s been working on Reporting Services and other BI products here at Microsoft since 1999. For SQL Server 2016 Reporting Services we made a set of improvements to our PDF Renderer targeted at our international ......(more)

Blogs : Software Development

Building a Mature DevOps Practice: Implementing DevOps Best Practices Throughout the Application Lifecycle - In our previous two blog posts, we saw that to increase your competitive advantage, you need to improve engineering productivity, and that for engineering to be productive, companies need to embrace DevOps culture, tools, and technologies. DevOps supports ......(more)

Building a Mature DevOps Practice: How to Create a Culture of Experimentation - In our last post, Building a Mature DevOps Practice: Start with Why, we explored DevOps as a critical enabler of competitive advantage, and the importance of developing a DevOps mindset across the organization. Here we’ll expand on a couple of concepts ......(more)

Blogs : SQL Server 2016

SQL SERVER 2016 – InMemory OLTP LOB Datatype Enhancement - My exploration with SQL Server 2016 features are just continuing and I am delighted every other day to figure out something new been added to the capabilities which in the opinion were of limited capacity in the prior versions of SQL Server. I will spare ......(more)

Blogs : T-SQL

T-SQL Tuesday #080 - T-SQL Tuesday time is here again! And I’m honored to be hosting this months block party. Why you ask? T-SQL Tuesday #80 happens to fall on my birthday believe it or not! With that said let’s get this party started and have some fun with it this month ......(more)

TIL: Another Great Use Of APPLY - Yesterday, at DevTeach, I got to see Itzik Ben-Gan give a session on advanced T-SQL operations, mostly around gaps & islands but also including a couple other gems. Something that caught my eye was an interesting way of using APPLY to unpivot data. I ......(more)

July SSMS 2016 changes transaction semantics - This can be a very nasty one if you're not observant. The July update of SSMS now has the option to change XACT_ABORT setting for a query window using Tools Options Query Execution SQL Server Advanced SET XACT_ABORT Now, exposing one more SET option ......(more)

Blogs : Virtualization

Virtualization does not equal a private cloud - I see a lot of confusion when it comes to creating a private cloud. Many seem to think that by virtualizing your servers in your local on-premise data center, you have created a private cloud. But by itself, virtualization falls far short of the characteristics ......(more)