The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I've seen lots of data visualization tools over the years. Cognos, Microstrategy, ProClarity, and more. While I haven't been a BI person for most of my career, in many of my positions, someone has wanted to try some new tool and eventually I'll get involved somewhat. I remember first seeing the OLAP cube browser in SQL Server 7, and showing my boss a quick demo. While it wasn't something that you could let an end user have, it also got my boss excited, and they started a project as I was leaving to implement some OLAP for the sales department.

When I first saw Tableau years ago (2008?) at TechEd, it was the first tool I'd seen that was really slick for the end user. I expected they'd grow quickly, and they have. Many people have looked at the Tableau tools as the standard for data visualization. For years nothing looked close, and as much as I appreciated Microsoft moving PowerPiviot and other tools to Excel, they weren't as nice to use as the graphical tools.

That changed with Power BI. When I first saw this tool, instantly I could start to see the power of the tool. Even altering and changing a tool had an ease and power that I hadn't seen since the Tableau tools. The initial release had lots of limitations and was just on the web. It was slow, and refreshes from remote data weren't great. There were security limitations, and I wasn't sure that the vision for the product made sense from Microsoft, even though I could see tremendous potential.

Things have improved, and I am somewhat amazed with the power of Power BI. A simple sales report is interactive. I can click on something intuitively and the values I see will adjust to focus on that item. What's more, other graphs on the screen adjust. However, I don't have to stick with simple graphs, I can add better imagry, such as this wine report, or this airline maintenance report that drives work. I was especially impressed with the analysis of Stephan Curry (which won the contest). Some of those reports I couldn't imagine trying to build with other tools, especially SSRS.

Power BI is a great tool, and if you need to provide visualizations for end users, I'd urge you to take a look. The Power BI Desktop tool is great for modeling and working with data. I find myself using that for quick looks at data, building a graph in a way that's easier than in Excel. I went through Microsoft's EdX course, and was somewhat amazed to see the vast capabilities available in the product. Guy in a Cube, Adam Saxton, has a YouTube channel where he and Patrick LeBlanc (@PatrickDBA) bring you constant training and tips on how to get the most out of the tool.

What's more, I keep finding more and more Power BI blogs and posts that I can add to Database Weekly every week. So many people are experimenting and finding ways to better analyze data with Power BI. This week we have a continent slicer, data privacy, collaboration, and more. With montly releases, I find that people are constantly digging into the possibilities and helping you learn with them. There are even developers building custom visuals that you can download. There's even one for acquarium lovers. If you want to learn about these, Devin Knight blogs regularly about custom visuals and we include many of those links here and on SQLServerCentral

Power Bi is a great way to empower your users, reduce your reporting load, and make everyone happier. Power BI is a great way to let users experiment with data and actually decide what reports are worth investing in more with IT resources to perhaps optimize the performance of certain visuals. Give it a try to day, especially the desktop version, and see how you can easily start to examine data that might be important to you. Even the SQLDBAWithaBeard finds Power BI helpful.

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.

XML

Free eBook: Stairway to SQL Server XML - XML has been part of the SQL Standard since 2003, and is essential for any DBA because so many of the dynamic management views return XML data. Now that the industry is more used to data defined by document markup, it is becoming more important than ever for Database Developers and DBAs to understand the technology and to know where it makes sense to use XML. In this book Robert Sheldon flexes his talent to make the complicated seem simple....(more)

Vendors/3rd Party Products

Extending DevOps practices to SQL Server databases - In this free demo webinar, Grant Fritchey and Arneh Eskandari will show how Redgate tools enable you to push and pull database changes in Git, then set up an automated database build and deployment process using TeamCity and Octopus Deploy. Register now....(more)

Slow application performance? - How would you address application performance on constrained networks? Answer this quick survey to let us know whether you would be interested in products that help this....(more)

How Redgate helped define a truly automated database deployment process - At the end of 2016, Redgate interviewed Bob Walker, a Lead Application Developer, to find out how he went about setting up automated database deployments and what lessons he’d learned. Here are the highlights....(more)

Video: watch the SQL Clone live stream event - On March 29, over 650 people tuned in from around the world to find out more about Redgate's new database provisioning tool, SQL Clone. The agenda for the virtual launch event was built around the feedback and questions we’ve received so far, and included some in-depth demos and more insight into the technology behind SQL Clone. ...(more)

Why is my clone so small? - Using very few megabytes and taking very little time, SQL Clone can quickly creates multiple copies of very large databases. How? It might sound like magic, but it's not; in this blog post Redgate developer Chris Hurley goes into the details of how the technology works....(more)

SQL Data Mask prototype from Redgate - SQL Data Mask is a tool that copies your database while anonymizing personal data, and is the latest product from Redgate's Foundry to reach the prototype phase. This blog post from Santiago Arias provides a full tutorial on how you can try out the prototype now free of charge....(more)

T-SQL

The Surprising Behavior of Trailing Spaces - In every training class we do, I learn stuff from students. Here’s last week’s surprise. Create a temp table, and put in a value with trailing spaces. Then query for it using the = and operators, using different numbers of trailing spaces: CREATE ......(more)

DB2 timestamps and SQL Server - I've been working with a document database in DB2, getting the document metadata into a SQL Server data warehouse.| I've run across some issues with the way DB2 timestamp data types are converted into SQL Server datetime2 when you access the DB2 server ......(more)

Statistics in SQL: Simple Linear Regressions - Let’s imagine that we have two variables, X and Y which we then plot using scatter graph.| It looks a bit like someone has fired a shotgun at a wall but is there a relationship between the two variables? If so, what is it? There seems to be a weak positive ......(more)

PascalCase and camelCase strings in T-SQL - Yesterday, I discussed changing the case of T-SQL strings to ProperCase, TitleCase, SnakeCase, and KebabCase.| But there are other case options that can be needed. For example, often when I’m programmatically generating code, I want to create identifiers ......(more)

Right-aligning numbers in T-SQL - When you output a series of numbers in T-SQL, people often want to right-align the numbers.| So instead of numbers that look like this: They want an output that looks like this: Now the first thing to understand is that this is generally a client-tool ......(more)

Avoiding invalid object name errors with temporary tables for BizTalk, Reporting Services and apps using SET FMTONLY - When applications need to call stored procedures, they try to work out what the returned data will look like ie: which columns come back, what data types are they, etc.| The old way of doing this was to call SET FMTONLY ON. Unfortunately, many applications ......(more)

Statistics in SQL: Kendall’s Tau rank correlation - Kendall’s Tau rank correlation is a handy way of determining how correlated two variables are, and whether this is more than chance.| If you just want a measure of the correlation then you don’t have to assume very much about the distribution of the ......(more)

Fun With Logging Dynamic SQL - While working on a demo I thought this might make a fun aside to share on the blog, because working with dynamic SQL can be challenging. Especially if you don’t have a monitoring tool or application profiling to figure out who ran what and when, most ......(more)

Automatically guessing foreign key constraints - With good naming and datatyping conventions, an automated script can help you with the process of creating foreign key constraints across your database, or actually, suggest table relations where you’ve forgotten to implement them.| However, the whole ......(more)

Missing index with create statements - Missing index :- Scripts will help you to get all the missing index and prepare script for new index./*Description:- This Query will provide you detail of missing indexes on a table and also prepare sql script for new index*/SELECTDISTINCT @@SERVERNAME ......(more)

Testing Software

A DLM Approach to Database Testing - I always hope there is no place for bugbears in my database and wish that no one ever sees a bugbear in my foreign keys. So bugbear go! for I don’t need’ya bugging up my stored procedure causing table locks, and things suddenly truncating strings, endless ......(more)

SQL Server Security

SQL SERVER – Logon Failure: The User has not Been Granted the Requested Logon Type at This Computer - Sometimes DBA do something which they are not aware of and end up in looking at the logs to see what went wrong.| Here is one of the articles I wrote about the changing service account from configuration manager. Why to Use SQL Server Configuration Manager ......(more)

Working with Data in Always Encrypted - In this post we’re going to create some encrypted columns in a table in a test database and look at some of the practicalities and limitations of working with Always Encrypted (AE).| There are actually a fair few limitations, but not because there anything ......(more)

SQL Server News

SQL Server vNext Release Date: May 31? - At the SQLbits conference in the UK, Victoria Holt sat in on a session about the State of the SQL Nation.| She writes: “SQL Server vNext will be launched this year with the UK launch 31 May 2017.” Keep in mind that “launch” is a marketing term, very ......(more)

DEA 2.0 Technical preview: Release Overview – Database Experimentation Assistant - Overview Database Experimentation Assistant (DEA) is a new A/B testing solution for SQL Server upgrades. It will assist in evaluating a targeted version of SQL for a given workload. Customers who are upgrading from previous SQL Server versions (SQL Server ......(more)

Software Development

Setting up a Shiny Development Environment within Linux on Windows 10 - While I was getting Ruby on Rails to work nicely under Ubuntu on Windows 10 I took the opportunity to set up my *nix bash environment, which was largely using defaults. Yes, I know I can use zsh or fish or other shells. Yes, I know I can use emacs and ......(more)

Security news and thoughts

Financial Firms Report Sharp Increase in Cyber Preparedness - NEW YORK, NY April 11, 2017 -- Duff & Phelps, the premier global valuation and corporate finance advisor, today highlighted research affirming that financial services professionals are poised to significantly accelerate resources dedicated to preventing ......(more)

Random thoughts on the use of breach data for protection of accounts - Sponsored by: Titania - Find your network security gaps before hackers do with world’s first detailed configuration auditing toolSomeone sent me an email today which essentially boiled down to this: Hey, Microsoft's Azure Active Directory alerted me ......(more)

Replication

Who Left Those Old Components in My New Database??? - Recently a client requested some new transactional replication publications on a relatively new pre-production server.| The client has plenty of transactional and snapshot replication in their environment, including two other publications on this instance ......(more)

R Language

Saving input and output with sp_execute_external_script - Again I was at the point, where I needed to store and save to external file all the R code that was executed through sp_execute_external_script.| Soon, you will find out several interesting things. To show the example, I will start with following example: USE ......(more)

The Binomial Formula with T-SQL and R - In a previous post I explained the basics of probability.| In this post I will use some of those principles to see how to solve certain problems. I will pick a very simple problem that I found in a statistics textbook. Suppose I have 7 friends who are ......(more)

Product Reviews and Articles

How to deploy a database plus static data using SQL Compare and SQL Data Compare - We can use the SQL Compare Pro command line with PowerShell to automate the process of creating a new build script for a version of the database, from the object scripts in the VCS.| However, what if we want to include static data in the build? This ......(more)

Upgrading VSTS Redgate Build Tasks - I’ve been putting it off, but in prepping for SQL Bits demos, I decided this was a good time to just upgrade my original build and release tasks on VSTS from v1 to v2 for the Redgate tasks.| The first step was to go into the marketplace and find the ......(more)

PowerShell

AzureRM Module Version - When working with the AzureRM PowerShell module, remember the module is constantly being updated to take advantage of new features added to Azure.Continue reading... ...(more)

Export SQL User Permissions to T-SQL script using PowerShell and dbatools - There are times when DBA’s are required to export database user permissions to a file.| This may be for a number of reasons. Maybe for DR purposes, for auditing, for transfer to another database or instance. Sometimes we need to create a new user with ......(more)

Export SQL User Permissions to T-SQL script using PowerShell and dbatools - There are times when DBA’s are required to export database user permissions to a file.| This may be for a number of reasons. Maybe for DR purposes, for auditing, for transfer to another database or instance. Sometimes we need to create a new user with ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI Desktop – Terms and Definitions - Power BI Desktop – Terms and Definitions The following list of Power BI Desktop terms and definitions are intended to help users better understand the terminology used within the product and referenced in documentation and blogs. As I work on customer ......(more)

Adding a Continent Slicer to Power BI Reports - HOW TO: Adding a Continent Slicer to Power BI Reports After building Power BI reports for many customers over the past couple years, one thing is certain – people love maps. Nearly every organization I support operates at a global scale, with offices ......(more)

Power BI and Data Security – Sharing Data - As Power BI becomes more prevalent in data analytics and visualization within the enterprise, data security becomes a significant concern. Power BI at its best is deployed to the Power BI service hosted on Microsoft’s Azure platform. Every enterprise ......(more)

Power BI and Data Security – Data Classification and Privacy Levels - As Power BI becomes more prevalent in data analytics and visualization within the enterprise, data security becomes a significant concern. Power BI at its best is deployed to the Power BI service hosted on Microsoft’s Azure platform. Every enterprise ......(more)

Microsoft Expands Power BI Desktop's Collaboration Options - This month's Power BI Desktop update brings a new connector that enables business teams to unearth new insights from the same dataset.| ...(more)

Power BI and Data Security – Compliance and Encryption - As Power BI becomes more prevalent in data analytics and visualization within the enterprise, data security becomes a significant concern.| Power BI at its best is deployed to the Power BI service hosted on Microsoft’s Azure platform. Every enterprise ......(more)

Have more Charts by writing R codes inside Power BI: Part 3 - In the previous parts (Part 1 and Part 2) , I have shown how to draw a chart in the power BI (Part 1) visualization.| Also, in Part 2 I have shown how to present 5 different variables in just one single chart. In this post, I will show how to shows some ......(more)

Dynamic filtering with Power BI - In this video, Patrick looks at taking URL filtering to the next level.| He shows how to use dynamic filtering with Power BI reports. He also shows a neat trick of setting a link image for Web URL fields. Filtering in Power BI: https://powerbi.microsoft.com/en-us/blog/filter-a-report-with-a-url-query-string-parameter/ ......(more)

Performance Tuning SQL Server

Does a TempDB spill mean statistics are out of date? - When you see a yellow bang in an execution plan, and you hover over it to discover that SQL Server spilled out to TempDB, your first reaction is to wail in pain and rend your clothes. Crying over spilled memory After that, though, it seems like people ......(more)

Cardinality Estimation for a Predicate on a COUNT Expression - This article looks into selectivity and cardinality estimation for predicates on COUNT(*) expressions, as may be seen in HAVING clauses.| The details are hopefully interesting in themselves. They also provide an insight into some of the general approaches ......(more)

Microsoft News : General Interest

Microsoft Buys Kubernetes Specialist Deis for Its Container Expertise - The technology giant grows its cloud container ecosystem by snatching up the San Francisco company behind the Helm, Steward and Workflow projects.| ...(more)

Microsoft News

Microsoft R Server support for Rattle - Rattle – the R Analytical Tool To Learn Easily – is a popular GUI for data mining using R. It presents intuitive graphical interface for data mining and analysis without actually writing the code. It presents statistical and visual summaries of data, ......(more)

Crossplatform Tools for SQL Server opened for community localization - This post was authored by Mona Nasr and Andy Gonzalez, Program Manager in C+E APEX Global Services In February 2017, we announced that the localization of Crossplatform Tools for SQL Server (mssql for Visual Studio Code and SQL Tools Service) is open ......(more)

Microsoft Discloses Contents Of A National Security Letter For The First Time - Microsoft released its biannual transparency report for the second half of 2016, which includes a lower number of total data requests than the previous year, as well as the disclosure of the contents the National Security Letter received in 2014.| ...(more)

Microsoft News : Patches, Bugs

Microsoft Patches Critical Zero-Day Exploit in Office Suite - This Patch Tuesday, Microsoft issues a fix for a zero-day vulnerability in the Office productivity suite that attackers were actively exploiting.| ...(more)

Microsoft Set to Patch New Zero-Day Office Vulnerability - McAfee and FireEye both report a previously undisclosed vulnerability in Microsoft Office that is being actively exploited by attackers.| ...(more)

MDX/DAX

DAX Studio 2.6.0 Release - Originally posted on: http://blackrabbitcoder.net/archive/2016/12/21/dax-studio-2.6.0-release.aspxWe seem to somehow have gotten into the habit of doing pre-Christmas releases every year, so why break with tradition now :) The latest release of DAX Studio ......(more)

Filtering Tables in DAX - This article describes a number of techniques available to filter tables in DAX, showing possible pitfalls that you can avoid once you know them, in particular using bidirectional filters.| One of the hardest things to do, when learning DAX, is to get ......(more)

Hardware News

Western Digital Expands Purple Lineup with a 10 TB Helium-Filled HDD - Western Digital has expanded its Purple lineup of hard drives, aimed at video surveillance applications, with a 10 TB helium-filled HDD. The drive is optimized for write-intensive workloads and supports various technologies that minimize the number of ......(more)

HA/DR/Always On/Clustering

Step by Step Configuring a Dedicated Network for Availability Group - One of my clients came up with the requirement to isolate the replication traffic from the public network.| The advantage of using a separate network for replication is reduced latency and replication can still run without any latency issues during times ......(more)

ETL/SSIS/ELT

Data Wrangling Concepts: Latency and Staleness - Latency is the amount of time between data creation and load.| Staleness is the amount of time since data in a target was refreshed. In many data integration use cases, a latency or staleness of one day, one week, or even one month is acceptable. Enterprise ......(more)

DevOps and Continuous Delivery (CI/CD)

How Redgate helped define a truly automated database deployment process - At the end of 2016, we interviewed Bob Walker, a Lead Application Developer, to find out how he went about setting up automated database deployments using Redgate tools and what lessons he’d learned. The highlights make interesting reading. You describe ......(more)

Data Mining/Data Analysis

Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 4: Reach insights faster by running analytics at the point of creation - This is the fourth post in a five-part blog series. Keep an eye out for upcoming posts and catch up on the first, second, and third in the series. In addition, join us for Microsoft Data Amp on April 19 at 8 AM PT. The online event will showcase how ......(more)

What is a productive data engineering team? - Merging the gaps between data science and engineering, and what each side can learn from the other.|In my experience working with data engineering teams, I find that most teams don’t realize they have to change their thinking about data and systems to ......(more)

Conferences and Events

Attending SQLBits for the first time - I had the opportunity to attend SQLBits for the first time in the UK.| This was an outstanding event with great people and an awesome attendee party. This clip are some highlights from the event to hopefully convey what it was like to attend. This event ......(more)

Computing in the Cloud (Azure, Google , AWS)

Five ways Microsoft helps you do amazing things with data in the cloud - Microsoft can help you do amazing things with your data in the cloud! Here are five examples to help you get started. If you’d like more information about using the cloud to get the most from your data, please join us for the upcoming Microsoft Data ......(more)

T-SQL Tuesday #89 – There is No Cloud - T-SQL Tuesday is a monthly blog gathering for the SQL Server/Data Professional community It is the brainchild of Adam Machanic (B|T) and is not limited to just things around the SQL Server database engine.| Each month a blogger hosts the event and anybody who ......(more)

Career Growth

PASS Cloud Virtual Group – Call for Speakers - The cloud is calling to you.| Or more specifically, Jody Roberts (b | @) and I (b | @) are looking to the community to find some presenters interested in presenting to the PASS community on building or using data platforms and solutions in the cloud. Wondering ......(more)

Don’t Duck On Responsibilities - Being a data professional you assume a certain amount of responsibility.| It often requires having the right attitude and an action plan in place for finding the solutions to our problems at hand. Too many times we attack the symptoms causing the issue, ......(more)

T-SQL Tuesday #89 – The times they are a-changing - This month’s T-SQL Tuesday is hosted by yours truly!| The topic this month: how do you keep up with the fast changing world of data management? Does this impact your job? Or do you feel there’s no impact at all for your position? Do you fear the cloud? ......(more)

T-SQL Tuesday #89 – New horizons and new opportunities! - G’day, TSQL Tuesday I thought that I’d write a few words about this months T-SQL Tuesday -the blog party started by Adam Machanic (blog | twitter) and this month hosted by hosted by Koen Verbeeck (http://sqlkover.com/t-sql-tuesday-89-invitation-the-times-they-are-a-changing/),- ......(more)

The more things change, the more they stay the same - Koen Verbeeck (b/t) is hosting T-SQL Tuesday this month and our topic is The times they are a-changing.| In other words How does the cloud impact you and your job? Well, to discuss how I feel about the cloud I have to go back a bit. When I first started ......(more)

T-SQL Tuesday #89: The Times They Are A-Changing - For T-SQL Tuesday #89, Koen Verbeeck (b|t) wants to know how we feel about technology and change.| What impact has it had on our jobs, our education, and our psyches? It's a really good topic for us data professionals and Koen gets the ball rolling with ......(more)

You Should Take a Career-Planning Retreat - I’m not talking about a vacation.| Yes, you need to take vacations, but this is different. Every now and then, you need to step back and ask yourself: Do I like what I’m doing? Do I like where I’m doing it? Do I like the exchange of benefits for my ......(more)

Backup and Recovery

Unable to restore a backup – Msg 3241 - I worked on an interesting issue today where a user couldn’t restore a backup.| Here is what this customer did: backed up a database from an on-premises server (2008 R2) copied the file to an Azure VM tried to restore the backup on the Azure VM (2008 ......(more)

Transaction log growth during BACKUP - Over on Stack Exchange, someone asked why their transaction log was growing very fast during a full backup.| An interesting tidbit to this question is that the database is in SIMPLE recovery — so the log should stay tiny, right? Wrong. While taking a ......(more)

Azure SQL Database

Azure SQL Database Auditing – Move from Azure Table Storage to Blob Storage - Microsoft recently deprecated the use of Azure Table storage for SQL Database Auditing. Use Azure Blob Storage to save your auditing files instead. If you were using Azure Table Storage for your auditing data, here is a simple step by step on how to ......(more)

AI/Machine Learning/Cognitive Services

Car Makers Drive Hard Towards AI Advances - One of the most visible applications of AI and machine learning is in the area of connected cars. Here's how data and analytics are driving these advances. ...(more)

Google Tests New Approach to Training Machine Learning Models - Instead of a centralized dataset, the company has developed a way to train machine learning systems using data on individual mobile devices.| ...(more)

Administration of SQL Server

How Database Monitoring Can Improve Engineering and Development Team Productivity - For DBAs, engineers, and developers, effective communication and access to information keeps everyone on the same page, from initial issue identification to after-action knowledge sharing. Read More ...(more)

SQL SERVER – Script Upgrade Failure – RsFxFt.Dll::RsFxMgmtInitialize failed: Error 0x80070002 - In the recent few days, I have found many clients having various script upgrade failures while applying service packs or cumulative updates.| Here are few of them. Let us learn about Script Upgrade Failure. SQL SERVER – Script level upgrade for database ......(more)

SQL SERVER – FIX: Error: 913, Severity: 16 – Could Not Find Database ID 3. Database May Not be Activated Yet or May be in Transition – SQL Service - This blog is a result of a Skype conversation with one of my clients.| The issue, for which they contacted me, was that they were not able to stat SQL Service due to database may not be activated. I asked to share SQL Server ERRORLOG files using below ......(more)