The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I’m reading some more history. In this case I’ve been reading about the Korean conflict back in the 1950s. One of the more fascinating aspects of the history is the disparity of the goals between President Truman along with the UN, and the general in charge, MacArthur. Not only were the goals different, but the communication between them was fairly horrible. Instead of realizing that each side had different communication styles and trying to cater the message to the recipient they both thought they could just dictate what they wanted and it would happen.

You see the same thing all the time in IT. Organizations and individuals don’t, or can’t, agree on the goals. They then proceed to talk past one another. IT is not easy, but communication is brutally difficult under the best of circumstances. Now put a hard deadline in, or shift the requirements, or have the business environment change, or any of the other myriad issues that comes up. Then bad communication becomes horrible communication becomes missed opportunities, lost business and IT failures.

It’s best to get the business and the IT department communicating well and often. Make sure you understand the goals and that your business or your IT organization agrees on those goals. The lesson of history is very clear. Everyone is working together or there will be problems.

Grant Fritchey 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

How to achieve true DevOps by including the database - DevOps success is achieved through a combination of people and technology coming together in processes that remove barriers, improve visibility, decrease risk, and reduce software delivery times. Including the database in DevOps greatly improves performance across these outcomes. In this webinar Andrew Pierce will walk you through Redgate’s Database DevOps solution, showing reliable, scalable and repeatable processes for automating your database development and deployment. ...(more)

How DevOps can help you stay ahead of the competition - Do you want to be the best at sprinting, or overcoming hurdles, or running further than the rest? The key is to find an approach that brings the elements together: removing silos between teams, adopting integrated technologies, automating processes that cause barriers and bottlenecks. ...(more)

Vendors/3rd Party Products

Help Redgate do a better job (and win a $250 Amazon voucher) - Could you spare a few minutes to help Redgate improve its products and services? Redgate are running a survey of their users and the wider SQL Server community and they’d appreciate your insights. As a thank you for taking part, you’ll also be entered in a prize draw for a $250 Amazon Voucher. ...(more)

Deploying Multiple Databases from Source Control using SQL Change Automation - In this article, Phil Factor demonstrates how to use SQL Change Automation, a PowerShell script, to take the contents of a source control directory for a database, check that it is possible to build from it, document it and then create a NuGet package and place the code in it, along with the documentation....(more)

Building Better Test Data with SQL Provision - Development teams make software available for release once they are confident that it behaves consistently, as it was designed to behave, under as many different user workflows as they can test. Unfortunately, their test cells often don’t reflect the harsher reality of the live environments, where their software will encounter large volumes of real data, and some ‘unexpected’ data values....(more)

SQL Change Automation with PowerShell Scripts: getting up-and-running - In this article, Phil Factor demonstrate how to use SQL Change Automation, a PowerShell script, to take the contents of a source control directory for a database, check that it is possible to build from it, document it and then create a NuGet package and place the code in it, along with the documentation....(more)

Improving legacy code using SQL Prompt code analysis - Tom Walsh explains how to use SQL Prompt to improve legacy code...(more)

T-SQL

Using custom types as arrays in T-SQL - One of the missing language features in T-SQL language is array support. In some cases you can use custom types to work with arrays that are passed as parameters to your stored procedures. Custom types in T-SQL enable you to create alias for some table, ......(more)

ORDER BY, OFFSET, and Fetch in TSQL - My most recent Quizletter featured a quiz on ORDER BY in TSQL, complete with questions on the OFFSET and FETCH clauses which we got in SQL Server 2012. One great thing about teaching TSQL School is that it reminds me of these cool options that can be ......(more)

Tools

Why Aren’t You Using SSMS 17? - Last fall, Microsoft split the coding and release of SQL Server Management Studio away from any dependency on the server code. With that, they began an aggressive and exciting series of releases with exciting new functionality in each release. However, ......(more)

The Lighter Side

Porsche shatters the Nürburgring record we thought was unbreakable - Porsche There must be something in the air. On Friday, we brought you news about Romain Dumas and Volkswagen breaking records at the Pikes Peak International Hill Climb. Just five days after Dumas' made it to the top of the mountain, his colleagues ......(more)

Testing Software

Setting package references in ssisUnit - When you set the packages’ references in the ssisUnit tests you have four options for the source (StoragePath) of the package: Filesystem – references the package in the filesystem – either within a project or standalone MSDB – package stored in the ......(more)

SQL Server Security

The SQL Server Defensive Dozen Part 2 – SQL Server Encryption, Key Management, And Data-At-Rest Encryption - Introduction In order to properly secure and harden SQL Server, the use of encryption provides many benefits including safeguarding data, separation of duties, and satisfying regulatory needs such as the Secure Technical Implementation Guide (STIG) or ......(more)

Contained Database – No more need for Server Level Logins - Starting in SQL Server 2012 and in Azure SQL Database, Microsoft introduced the concept of a contained database. A contained database is a database that is completely un-reliant on the instance of SQL Server that hosts the database including the master ......(more)

SQL Server on Linux

Re-platforming and modernizing your data workloads with SQL Server on Linux - This post is authored by Marko Hotti, Senior Product Marketing Manager, SQL Server. Today, were excited to introduce a free e-book, SQL Server on Linux: A guide to re-platforming and modernizing your data workloads. This is the thirdin a series oftechnical ......(more)

SQL Server News

10 SQL Server Feature Requests I’ve Upvoted Recently - In no particular order: Database Level Option for Storing Wait Stats – just like we can store query plan history in Query Store, it’d be awesome to get performance history over time. Give more info on the “String or Binary Data Would be Truncated” error ......(more)

Security news and thoughts

Microsoft Plugs Data Leaks in SharePoint Online Update - The latest updates to SharePoint Online help businesses prevent data leaks and automate their SharePoint migrations to the cloud. ...(more)

Typeform Reports Data Breach That Impacts Users of Survey Platform - NEWS ANALYSIS: Yet another breach reveals supply-chain dangers, as the impact of the Typeform breach could have wide implications. ...(more)

LTE wireless connections used by billions aren’t as secure as we thought - The Long Term Evolution mobile device standard used by billions of people was designed to fix many of the security shortcomings in the predecessor standard known as Global System for Mobile communications. Mutual authentication ......(more)

Replication

A bug in merge replication with FILESTREAM data - I wish I could say that every DBA has a love/hate relationship with Replication, but, let’s face it, it’s only hate. But it could get worse: it could be Merge Replication. Or even worse: Merge Replication with FILESTREAM. What could possibly top all ......(more)

R Language

Visualize the World Cup with R! Part 1: Recreating Goals with ggsoccer and ggplot2 - After posting a couple of my World Cup viz on Twitter, I thought I'll collate some of them into a blog post. This will be Part 1 of a series as the World Cup goes on and I keep improving my viz skills throughout the tournament. I will also explain how ......(more)

R null values: NULL, NA, NaN, Inf - R language supports several null-able values and it is relatively important to understand how these values behave, when making data pre-processing and data munging. In general, R supports: NULL NA NaN Inf / -Inf NULL is an object and is returned when ......(more)

Global Migration, animated with R - The animation below, by Shanghai University professor Guy Abel, shows migration within and between regions of the world from 1960 to 2015. The data and the methodology behind the chart is described in this paper. The curved bars around the outside represent ......(more)

Product Upgrades and Releases

Released: Public Preview for SSAS and SSRS 2017+ Management Packs (7.0.8.0) - We are getting ready to release brand new management packs for SQL Server Analysis Services and SQL Server Reporting Services 2017. Please install and use this public preview and send us your feedback (sqlmpsfeedback@microsoft.com)! We appreciate the ......(more)

PowerShell cmdlets for managing SQL Vulnerability Assessments - We are pleased to announce the availability of PowerShell cmdlets for managing SQL Vulnerability Assessments for your SQL Servers. The cmdlets can be used to run assessments programmatically, export the results and manage baselines. They enable the scenario ......(more)

Released: System Center Management Pack for SQL Server, AS, RS (7.0.7.0) - We are happy to announce that updates to SQL Server (2008-2017+), Analysis Services, and Reporting Services (2008-2016) Management Packs have been released. Downloads available: Microsoft System Center Management Pack for SQL Server enables the discovery ......(more)

Smart Filter Pro Preview - Today we start the preview of Smart Filter Pro. This is the first commercial custom visual produced by OKViz and it will be in preview until the end of 2018. You can start using it today, no registration required, and it will be free for 6 months (during ......(more)

Product Reviews and Articles

Improving legacy code using SQL Prompt code analysis - Code analysis is a useful technique for identifying potential issues in your code base, and ensuring that it adheres to your pre-defined standards, so that you can release your software product with more confidence. SQL Prompt 9.2 and later also allows ......(more)

Building Better Test Data with SQL Provision - Development teams make software available for release once they are confident that it behaves consistently, as it was designed to behave, under as many different user workflows as they can test. Unfortunately, their test cells often don’t reflect the ......(more)

Deploying Multiple Databases from Source Control using SQL Change Automation - Quite often, in a database development project, you need to create several copies of the database under development. They need to be to the current version of the build, or a previous specific version. You need to fill them with a version of the development ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI Introduction: Working with Parameters in Power BI Desktop —Part 4 - The series so far: Power BI Introduction: Tour of Power BI — Part 1 Power BI Introduction: Working with Power BI Desktop — Part 2 Power BI Introduction: Working with R Scripts in Power BI Desktop — Part 3 Power BI Introduction: Working with Parameters ......(more)

Performance Tuning SQL Server

Management Studio Hides Missing Indexes From You. - SQL Server Management Studio only shows you the first missing index recommendation in a plan. Not the best one. Not all of them. Just whichever one happens to show up first. Using the public Stack Overflow database, I’ll run a simple query:SELECT c.CreationDate, ......(more)

SQL Server 2016 Memory Accounting: A Suspicious Surprise - Wading through all of the SQL Server memory-related perfmon counters to understand how they related to each other took me a really long time.  Time-series graphs that show the relationship help me tremendously, and when I started trying to account for ......(more)

MDX/DAX

Data Modeling, Dates and DAX - Presenting data in the format to ease visualization is required for any BI product.  Power BI provides much of this with Data Analysis Expressions, (DAX).   As a DBA, I admit to cringing every time a reference was made how similar it is to functions ......(more)

Creating table functions in DAX using DETAILROWS - This article describes how to use the detail rows expression of a measure to obtain the equivalent of creating table functions in DAX. This allows the reusing of a table expression in multiple CALCULATE filters. A Tabular model in compatibility level ......(more)

SQL, M or DAX: When Does it Matter? - Column-based calculations are part of every BI project. Some of the most common examples include building a street address column from individual fields, concatenating the full name from First, Middle and Last Name fields; or building a location string ......(more)

Hardware News

Seagate Unveils Fast 2TB SSD for Laptops, Desktops - Seagate has a new internal BarraCuda solid-state disk designed to take data movement inside PCs to a higher-than-normal speed level. ...(more)

HA/DR/Always On/Clustering

Read-Scale Availability Group Setup - What is a Read-Scale Availability Group? A Read-Scale Availability Group is a Clusterless Availability Group.  It's sole purpose and design is to scale out a read workload.  More importantly is what it is not.  It is NOT a High Availability or Disaster ......(more)

Configure SQL Server Failover Cluster Instance on Azure Virtual Machines with MSDTC - Configure SQL Server Failover Cluster Instance If you are running a SQL Server Failover Cluster Instance on premises and looking to migrate to Azure, you might be a little intimidated. This architecture is a bit complex because it uses quite a few different ......(more)

Database Design, Theory and Development

Databases 101 - A short time ago a colleague asked me where he could find a “Databases 101” guide for the non-technical professional. As it turns out, the internet is littered with information, and mis-information, regarding data and databases. This makes it difficult ......(more)

Understanding Relations Part 2: Beware the Misconceptions - Note: This is a re-write of two older posts (which now link here), to bring them into line with the McGoveran formalization and interpretation of Codd's real RDM, including his own refinements, corrections, and extensions[1].(Continued from Part 1)I ......(more)

Data Science

Data Science Compensation Survey - Click to learn more about author Steve Miller. I recently came across an article in Forbes on the salaries of Data Scientists. The piece summarized findings from the just-published 5th-annual Burtch Works Study: Salaries of Data Scientists. The study ......(more)

Data Privacy and GDPR

Is this a ‘new dawn’ for the data catalog? - How should you store the information you need to understand the information in your relational databases? Or rather, how should you store the information your new developers need, that your compliance team requires, that your business wants? With extended ......(more)

Why California Data Privacy Law Promises Chaos by 2020 - NEWS ANALYSIS: The new privacy law passed in California after a brief consideration and little preparation is likely to be a harbinger for more privacy laws throughout the U.S. ...(more)

Data Mining/Data Analysis

Streamlining Real-Time Data Integration and Analytics: The Struggle is Over - Data Architects and CDOs today face a variety of demands, including delivering Real-Time Analytics, predicting customer behavior, or operationalizing Machine Learning. All of these initiatives share one critical requirement: real-time data. But to get ......(more)

The Never-Ending Quest for a Single Customer View - Click to learn more about author Kevin W. McCarthy. A “single customer view” is one of those ever-elusive concepts in the world of Data Management, almost a digital “white-whale” for you Moby Dick fans. Everyone talks about it by some name or another: ......(more)

Computing in the Cloud (Azure, Google , AWS)

Microsoft Catching Up to Amazon in Security Clearances for Cloud - Microsoft is getting close to getting the certification needed to host the government's most sensitive, classified information -- something Amazon Web Services already has, Bloomberg reported • The certification would give Microsoft an edge over other ......(more)

Career Growth

Summiting that Technical Challenge Part II - Conquering Challenges Months ago, I posted an article about some of the challenges encountered while migrating from one service level to another for my blog. As it turns out, I had some long lingering effects that I was delaying fixing because I didn’t ......(more)

I Got Here On My Own - Click bait worthy title, eh? That’s what I thought after I read the article from the Harvard Biz Review with the lead in comment, “Women who have already made it to the top say that the only person who will get you there is yourself.”  I found this ......(more)

Mastering Index Tuning–Day 2 - This is a short series of posts on the courses I took with Brent Ozar. I actually completed the courses in the past, but I wrote notes and wanted to revisit the way things went. This post looks at the Mastering Index Tuning class. Other  posts are: Prepping ......(more)

Preparing for Exam 70-767: Implementing a Data Warehouse - After nearly a two year hiatus, I steered my learning path back towards certifications. I'm happy to tell you I recently passed Exam 70-767: Implementing a Data Warehouse using SQL. If you don't know, there's a non-disclosure agreement for Microsoft ......(more)

Backup and Recovery

SQL Server In-Memory Optimized database backup and restore in action - A SQL Server backup and restore strategy is an essential process to safeguard and protect critical data. The vital role of any DBA is to minimize the risk of data loss and preserve data modifications at regular intervals of time. … Continue reading ? ......(more)

Ola Hallengren’s Scripts Keep Getting Better - You Know Him, You Love Him You trust your database backups and integrity checks to him, and he has been KILLING IT on new features lately. A while back, Ola decided to Open Source his scripts on GitHub (Soon to be known as Microsoft LiveHub for Business ......(more)

Testing BackBlaze Restores - The most important thing for a data guy is having a way to restore data. If security, performance, etc. are broken, those can be fixed is we have the data. If we don’t have data, does security matter? It’s important, but secondary. When Crashplan stopped ......(more)

Cloud Backup Comparison - I used to use Crashplan. This was about $150 a year, but I could to 5 machines. I used to do 5. I had My desktop My wife’s laptop My daughter’s laptop My laptop My son’s laptop This changed as my boy decided he didn’t like his data with ours. My laptop ......(more)

Azure SQL Managed Instance

Deploy Azure SQL Managed Instance network environment using ARM - Azure SQL Managed Instance is fully managed SQL Server instance hosted in Azure cloud. You would need to prepare Azure environment where SQL Managed Instance will be hosted before you create your Managed Instances. In this post you will see how to deploy ......(more)

Azure SQL Database

Azure SQL Elastic Pools – Moving Databases - If you read official Microsoft documentation about naming conventions for your SQL elastic pools, it is hard to find any guidelines on the subject. You are probably thinking it is not that big of a deal. I thought the same until I tried to execute some ......(more)

Microsoft Azure – Enable Geo replication of data – Cosmos DB Part 2 - In this article, we will learn how to replicate the data to any Azure region globally. Global Distribution Azure Cosmos DB allows to distribute the data globally to any available Azure region with a click. With, Azure Cosmos DB data is replicated in ......(more)

Analysis Services / BI on the MS Stack

How The New IsAvailableInMDX Property For Analysis Services Tabular Can Reduce Memory Usage And Speed Up Processing - Following on from my post the other week about the new RowsetSerializationLimit server property, I thought it would be a good idea to write about why the new IsAvailableInMDX property (announced in the same blog post) is so important. In fact, I would ......(more)

AI/Machine Learning/Cognitive Services

Machine Learning With F# - Diogo Souza gives us an introduction to using Accord.NET in F#: F# is a scripting as well as a REPL language. REPL comes from Read-Eval-Print Loop, which means that the language processes single steps one at a time like reading the user inputs (usually ......(more)

AI Tips Off Regulators to Possible EU Data Privacy Faults - Among the problems found by the AI software -- which is called "Claudette" -- were policies that did not identify third parties a company might share personal data with. ...(more)

Connecting Azure Machine Learning studio with on-premises SQL Database - Azure Machine Learning Studio has been around for the past 3 years and a lot of new features have been added. I am positive, many more will follow. One of more thrilling features has been the ability to connect to more  data sources. One more welcoming ......(more)

Administration of SQL Server

SQL Server Diagnostic Information Queries for July 2018 - This month, there are improvements to seven of the the SQL Server 2012 and newer versions of the queries, with a new column that shows whether there is a missing index warning in the cached query plan. A reader named Håkan Winther made this useful suggestion. ...(more)

SQL SERVER – Unable to See SQL Server Configuration Manager. How to Open it? - I get many emails every day and I do respond to them as soon as I can.  Sometimes they are about the business and sometimes they are from my blog readers who have followed my other blog and need help with something else. Most of the time, I do have a ......(more)

SQL Homework – July 2018 – Create a SQL Agent Job - For this month’s homework let’s try something a bit different. SQL Agent jobs are a very powerful tool frequently used for backups and maintenance but can also be used for batch processes, exports, etc. To be fair there is better job scheduling software ......(more)

SQL SERVER – Database Mail Error: The SMTP Server Requires a Secure Connection or the Client was not Authenticated. The Server Response Was: 5.5.1 - Have you ever configured database mail using Gmail account? There are many articles on the internet describing the steps. In this blog, we would learn about fixing an error “The SMTP server requires a secure connection or the client was not authenticated” ......(more)

SQL SERVER – gMSA: The Service Did Not Start Due to a Logon Failure - This is one of the most common errors which is seen my most DBA when they are using a domain account as the service account. In this blog, we would learn about the cause and solution of error The service did not start due to a logon failure while using ......(more)

Generating a SQL Server, SSAS, SSIS, SSRS Memory Dump via PowerShell script (and SQLDumper.exe) - Recently, we published a PowerShell script to create memory dumps of SQL Server, SSAS, SSRS, and SSIS - called it SQLDumpHelper.ps1 . The PS script uses SQLDumper.exe and it makes it very easy to generate the dumps by avoiding having to look up SQLDumper.exe ......(more)

TEMPDB – Files and Trace Flags and Updates, Oh My! - TL;DR – Update to the latest CU, create multiple tempdb files, if you're on SQL 2014 or earlier enable TF 1117 and 1118, if you're on SQL 2016 enable TF 3427. And now it's time for everyone's favorite SQL Server topic – tempdb! In this article, I'd ......(more)