The Complete Weekly Roundup of SQL Server News

In this issue:

Vendors/3rd Party Products

Tech News : Security

Tech News : The Lighter Side

Product Reviews

Microsoft News : General Interest

Microsoft News : Patches, Bugs

Hardware News

Blogs : Administration

Blogs : Azure SQL Database

Blogs : Azure SQL Datawarehouse

Blogs : Backup and Recovery

Blogs : Columnstore

Blogs : Data Mining/Data Analysis

Blogs : Database Design, Theory and Development

Blogs : High Availability/Disaster Recovery

Blogs : Integration Services/ETL

Blogs : Machine Learning

Blogs : MDX/DAX

Blogs : Oracle

Blogs : Performance and Tuning

Blogs : PowerPivot/PowerQuery/PowerBI

Blogs : R Language

Blogs : Security and Auditing

Blogs : Software Development

Blogs : T-SQL

Database Weekly - www.databaseweekly.com

The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server News for 2016-02-22

SQL Monitor 26 SQL Server monitoring tips – free course
Protect yourself from the most common causes of SQL Server pain, with this series of 26 free tips. Covers the hows and whys of monitoring, with practical advice and worked examples. Sign up now.
DLM Automation Suite Continuous integration and automated deployment for SQL Server databases
The DLM Automation Suite plugs into the build and release management tools you use for application development so that you can also set up continuous delivery for your databases. Download a free trial. Download a free trial.
SQL Source Control What do Git migrations, fast performance, and support for TFS 2015 & SQL Server 2016 have in common?
They’re all just a few highlights from the SQL Source Control roadmap, due over Christmas and in the next year. To find out what else is in store, check the roadmap now.
Editorial - Power BI Week

This past week was T-SQL Tuesday #75, with the topic of Power BI. There were some interesting posts, and we've included a few in this week's newsletter. This was also the week where the winners of the Power BI report contest were announced, and it was a good entry. This was the one I actually thought was the best one. If you haven't seen it, take a few minutes and go look at the entry and play with the data. It's interactive, so be sure to see how well the Denver Nuggets have competed against Mr. Curry.

I first looked at Power BI last summer, watching a presentation and being intrigued at the ability of the tool to easily get data from web pages. I then talked with a friend that works with Power BI regularly, and they showed me how to loop and gather information from many pages, easily assembling the data into a dashboard that was interactive. I was very impressed, and I'm looking forward to doing a bit more work on the platform myself.

Quite a few people have been concerned that the development of tools like Power BI mean that there is less of a need for IT staff. I find myself thinking quite the opposite. There will always be data quality issues with something like Power BI, and there is a certain level of programming to build more complex reports. Not to mention the refresh and update process is slow, which can make the tool feel cumbersome.

However users will find some reports are more useful, or more important, and need some help from technology professionals. Whether that's developing more DAX formulas and measures to enrich displays or just providing stable, clean data sources that consolidate data from various places, there will be plenty of work for IT staff that support companies using Power BI.

To me, Power BI is the type of tool that empowers users and lets them experiment a bit on their own. Once they determine what they really want, then they can engage me to build a more robust, reliable, and useful tool that helps the business.

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


The Weekly News

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

SQL Instant Clone Beta - Redgate is looking for beta participants to give feedback on a new tool: SQL Instant Clone. With Instant Clone, you can take a ‘data image’ of a database, then rapidly create an unlimited number of clone databases. These clone databases take only a few seconds to create and occupy about 50Mb each, but have the same schema and data as the original. To try it out, sign up now....(more)

Tech News : Security

Hospital pays $17k for ransomware crypto key - Hollywood Presbyterian Medical Center, the Los Angeles hospital held hostage by crypto-ransomware, has opted to pay a ransom of 40 bitcoins—the equivalent of $17,000—to the group that locked down access to the hospital's electronic medical records system ......(more)

Big Data, Health Coverage, and Privacy - by Angela Guess Rachel Emma Silverman reports in The Wall Street Journal, “Employee wellness firms and insurers are working with companies to mine data about the prescription drugs workers use, how they shop and even whether they vote, to predict their ......(more)

Tim Cook says Apple will fight US gov’t over court-ordered iPhone backdoor - Apple chief Tim Cook has attacked the recent court order that compels Apple to unlock and decrypt the San Bernardino gunman's iPhone. "Opposing this order is not something we take lightly. We feel we must speak up in the face of what we see as an overreach ......(more)

Hospital Data Held for $3.6 Million Ransomware Payoff - Hollywood Presbyterian Medical Center has fallen victim to a cyber attack. Hackers took control of the hospital's computer systems and are demanding $3.6 million via $9,000 of bitcoin, a virtual currency, to release the data. Hospital president and ......(more)

Tech News : The Lighter Side

Original 1977 Star Wars 35mm print has been restored and released online - A restored HD version of the original Star Wars Episode IV: A New Hope 35mm print has appeared online. While this isn't the first time that attempts have been made to restore Star Wars to its original theatrical version—that's the one without the much-maligned ......(more)

Product Reviews

Book Review: Big Data Analytics with Spark - This book aims to provide a “...concise and easy-to-understand tutorial for big data and Spark”. How does it fare? ...(more)

Microsoft News : General Interest

U.S. Department of Defense to upgrade 4 million devices to Windows 10 by February 2017 - The U.S. Department of Defense is committing to upgrade roughly four million devices and systems running legacy versions of Windows to Windows 10 by February 2017. ...(more)

Microsoft Does Not Plan To Change Data Gathering Policy - Contrary to a recent report, Microsoft does not plan to change its data gathering policy in the near future. ...(more)

Microsoft News : Patches, Bugs

Microsoft Patches Surface Book And Surface Pro 4 Sleep Issue - When I reviewed the Surface Book, there were a lot of bugs with the software. Some of them have been pretty minor, and Microsoft has been updating the firmware and drivers on it since before it was launched. Most of the issues have been sorted out, but ......(more)

Windows 10 forced update KB 3135173 changes browser and other defaults - The cumulative update not only knocks out PCs' default settings, it prevents users from resetting them.By "default", we mean, "don't change these"/sigh ...(more)

Hardware News

Seagate Begins to Ship 2.5-Inch, 7mm Thick 2 TB Mobile Hard Drives with SMR - Seagate has started to ship its highest-capacity hard drives for notebooks. The new HDDs use shingled magnetic recording (SMR) technology with very high areal density as well as slimmed-down mechanics. The new Seagate Mobile HDD 2 TB is the first hard ......(more)

Seagate Delivers A 2 TB HDD In 7mm - New notebooks use a 7mm format, and now you can get to 2TB in a notebook....(more)

Blogs : Administration

Future Proofing for Concurrency; Blocked Process Reports Are Not Enough - Takeaway: When assessing concurrency, performance testing is the primary way to ensure that a system can handle enough load concurrently. Monitoring blocked process ......(more)

A Guide for Decrypting SQL Server Database Objects - Overview The SQL Server 2005 and SQL Server 2008 provide a new feature for encrypting data to protect it from unauthorized access or hacker’s attacks. To make the data accessible, it needs to be decrypted with the use of key or password. It is necessary ......(more)

Shrinking Database Log Files - Files in SQL Server need to grow as the database grows, and in very specific circumstances need to be shrunk as well with additional maintenance.  This is quite a bit different between log files and data files, so I created a separate post for Shrinking ......(more)

Blogs : Azure SQL Database

Azure SQL Database Error - I was on SQL Cruise where I was scheduled to present a session on Azure SQL Database. I recorded all my demonstrations before we went to sea, but, I planned to attempt to try live demo’s. Yeah, yeah. Well, anyway, I got a unique error when I attempted ......(more)

Azure SQL Database security - Life we be so much easier if we could just trust everyone, but since we can’t we need solid security for our databases.  Azure SQL Database has many security features to make you sleep well at night: Transparent data encryption (TDE).  This encrypts ......(more)

Blogs : Azure SQL Datawarehouse

Using Apache Zeppelin on SQL Server - At the beginning of February I started an exploratory project to check if Apache Zeppelin could be easily extended in order to interact with SQL Server and SQL Azure. In the last week I’ve been able to have everything up an running. Given that I’ve never ......(more)

Loading Data into Azure SQL Data Warehouse - Ouch. Let’s start with the level set. I’m not an ETL expert. In fact, I haven’t done any professional ETL work for several years. My skills are, at best, rusty. With this in mind, I knew I’d have a hard time extracting data from a local database in order ......(more)

Blogs : Backup and Recovery

Misunderstandings about the COPY_ONLY backup option - The COPY_ONLY option for the backup command never ceases to cause confusion. What does it really do? And in what way does it affect your restore sequence? Or not? There are two sides to this. Restoreability and how the GUI behaves: Restoreability If ......(more)

Blogs : Columnstore

SQL SERVER – Showplan Additions for ColumnStore Indexes - The last time when I wrote about ColumnsStore FAQ, one of the readers had an interesting question. The question was around how to identify the traits of using a column store inside an execution plan. The very thought got me thinking. I was thinking where ......(more)

Blogs : Data Mining/Data Analysis

Evolution of the Netflix Data Pipeline - Our new Keystone data pipeline went live in December of 2015. In this article, we talk about the evolution of Netflix’s data pipeline over the years. This is the first of a series of articles about the new Keystone data pipeline.Netflix is a data-driven ......(more)

Your Data’s Rate of Decay - Did you know that data has a half-life? That is the amount of time it takes for the information in your database to go bad. How? Because contact data is always changing. SQL Server Pro Guest Blogs read more ...(more)

Blogs : Database Design, Theory and Development

SQL SERVER – View Dependencies on SQL Server: Hard & Soft Way - As an independent database consultant, I often face a challenge when analyzing the performance of SQL Server instances, which I see for the first time in my life. In many cases, this may seem a difficult task, because in order to fix something you must ......(more)

Blogs : High Availability/Disaster Recovery

How to build a Multi-domain cluster with AlwaysOn Availability Groups – SQL Server 2016 - SQL Server 2016 is making lots of news, and promises to be a revolutionary SQL Server version. In talking about AlwaysOn Availability Groups, a very good number of improvements were already announced as part of the Basic Availability Groups. By the way, ......(more)

Blogs : Integration Services/ETL

SSIS Conditional Processing by day - I'm working on a client where they have different business rules based on the day data is processed. On Monday, they generate a test balance for their accounting process. On Wednesday, the data is hardened and Friday they compute the final balances. Physically, ......(more)

Blogs : Machine Learning

How to call a Azure Machine Learning Web Service from NodeJS - Azure machine learning allows data scientists and developers to embed predictive analytics into applications. To learn more about Azure machine learning visit Azure machine learning documentation . A simplified process flow for Azure machine learning ......(more)

Blogs : MDX/DAX

MDX Cookbook is a popular book - I cannot believe that I have not posted any blog since May 2015! It’s been a busy few months for my co-workers and I, working on an OLAP re-design project. From the re-design of the ETL processes, to the multi-dimensional model, to building the dimensions, ......(more)

Blogs : Oracle

Getting Started With Oracle: Working With Strings - The almighty string It’s so good for holding all sorts of things. Chicken roulade, beef roulade, salmon roulade. It’s also the way you should store phone numbers. If I could go back in time to when I first started working with SQL, that’s what I’d tell ......(more)

Getting Started With Oracle: Working With Dates - Contrary to popular belief You will not burst into eternal flames if you’re a SQL Server guy or gal, and you happen to be within 100 feet of an Oracle database. You might feel lost and confused for a while, but you probably felt the same way when you ......(more)

Blogs : Performance and Tuning

Minimizing impact of widening an IDENTITY column – part 3 - So far in this series, I have demonstrated the direct physical impact to the page when upsizing from int to bigint, and then iterated through several of the common blockers to this operation. In this post, I wanted to examine two potential workarounds: ......(more)

Soften the RBAR impact with Native Compiled UDFs in SQL Server 2016 - Reviewers: Joe Sack, Denzil Ribeiro, Jos de Bruijn Many of us are very familiar with the negative performance implications of using scalar UDFs on columns in queries: my colleagues have posted about issues here and here. Using UDFs in this manner is ......(more)

How to find CPU intensive queries - High CPU usage is a very common problem in SQL Servers. Any query with a bad query plan can steal CPU time and harm system response time. Fortunately we can find queries with high CPU time using sys.dm_exec_query_stats DMV. This DMV, created in SQL Server ......(more)

Performance Monitoring for SSAS – Extended Events Cheat Sheet - Welcome back! This is the 3rd post in a series of posts focusing on performance monitoring for Analysis Services. Performance Monitoring for SSAS – Intro Performance Monitoring for SSAS – Data Collection Performance Monitoring for SSAS – Extended Events ......(more)

Blogs : PowerPivot/PowerQuery/PowerBI

T-SQL Tuesday #75: Jump into Power BI - So this month I’ve decided to bring every participant in this blog series kicking and screaming into the BI world. Power BI, if you haven’t heard or seen it yet, is Microsoft’s analytics and visualization solution. This month I challenged everyone to ......(more)

GROUPBY vs SUMMARIZE in #dax #powerbi #powerpivot - If you are using Power BI Desktop or Power Pivot in Excel 2016, you should learn when and how you can use GROUPBY instead of SUMMARIZE. The SUMMARIZE function is very powerful and internally very complex, so it’s easy to find scenarios where you get ......(more)

New features in Power Pivot Utils v 1.05: list unused columns #powerpivot #dax - The latest version of Power Pivot Utils v 1.05 has a new feature that deserves the immediate upgrade: you can list unused columns! Thanks to Idan Cohen and Amir Hefetz for their contribute to the macros developed by Bertrand d'ARBONNEAU! ...(more)

The Space Report Under the Hood - The infographic “Space Report” submitted to the Best Report Contest in Power BI is not only a collection of nice charts and graphics. There are several little gems implemented to overcome certain limitations, as you will read in this article. When we ......(more)

Working With CSV Files That Contain Rogue Line Breaks In Power Query And Power BI - CSV files often contain text fields, and text fields often contain line breaks that shouldn’t be there (especially if the text is entered by end users). For example, here is are the contents of a simple CSV file that should contain three fields(Product, ......(more)

T-SQL Tuesday #75–Power BI - This month’s host is Jorge Seggara, the @sqlchicken, who works for Microsoft. A busy schedule caused a slight delay, so we’re posting the third Tuesday of this month, but that is OK. This is a great topic for T-SQL Tuesday. Power BI Data While Power ......(more)

Power BI KPI Report: Hospital Charge Data - The Power BI KPI report is probably on top 10 of the most in-demand use-case for Power BI.  In my previous post, I published the report I created with Power BI Desktop based on the Medicare payment data that I downloaded from Data.gov. That report was ......(more)

Blogs : R Language

More R User Group Sites - by Joseph Rickert Last month I wrote about how several R user groups were making use of GitHub and listed some sites that I thought had interesting material. A few readers were kind enough to point out sites that I had missed; so I would just like to ......(more)

Using Microsoft R Server to Address Scalability Issues in R - If you missed the recent webinar presented by Derek Norton, Using Microsoft R Server to Address Scalability Issues in R, you can now catch up with the replay below. In the webinar, Derek compares Microsoft R Open and Microsoft R Server, and demonstrates ......(more)

Blogs : Security and Auditing

SQL SERVER – FIX: Error: 18456, Severity: 14, State: 6. Login failed for user - One of the blog which I wrote earlier was also on Login failed for user with state 58. After reading that blog, one of the blog reader contacted me and told that she is getting state 6. I thought this errors are very common and is important to demystify ......(more)

Row Level Security – Part 1–Simple Read operations - This is part 1 of my Row Level Security blogs for my upcoming book project . Row Level Security is a very interesting feature. Unlike Dynamic Data Masking (link), which looks interesting on it's face, once you dig in the utilization of the feature lookss ......(more)

Blogs : Software Development

Octopus Deploy and ReadyRoll in Under 5 Minutes - When it comes to deploying SQL Server databases, Octopus Deploy and ReadyRoll make a great team. And with our new plugin you can get set up in record time! You can use Octopus Deploy Step Templates to provide a user interface for common tasks. We’ve ......(more)

3 ways of archiving code correctness - Code correctness verification is not an easy task. At the same time, it’s one of the most important problems we have when we build software projects. In this article, I’ll compare 3 different ways of archiving code correctness. 3 ways of archiving code ......(more)

Blogs : T-SQL

Bigram Frequencies in Pure SQL - Basic text analysis on unigram and bigram frequencies can be helpful when digging into datasets of unstructured text. The most frequent bigrams, or pairs of adjacent words, tell you which phrases are most common in your corpus. We'll use bigrams to find ......(more)

Take caution when altering a column using TSQL - When developing in SQL Server you are eventually going to have to modify a column or two (at least). And if you use T-SQL to make the modifications (and I would recommend it most of the time) there is something you need to watch out for. First remember ......(more)

The Luhn Algorithm in SQL - /* The Luhn test is used by most credit card companies to check the basic validity of a credit card number. It is not an anti-fraud measure but a quick check on data corruption. It still allows any digits that are odd or even to be switched in the sequence. ...(more)

Quick Tests–Function Returns - I ran across a neat piece of code recently from Gail Shaw. She answered a question on returning the base path from a path in a string. Meaning if I had this string: c:\Users\Sjones\Documents\text.txt I’d want to return this: c:\Users\Sjones\Documents Her ......(more)

varchar(1) VS char(1) - The other day I made a somewhat flip coment on twitter. (I know, everyone is shocked right?) varchar(1) Really? It’s going to be one of those kinds of days. It started a rather amusing conversation over using varchar(1) vs char(1) and I thought it might ......(more)


Administrative