Data Privacy and Protection Laws (Database Weekly 9/17/2018)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report](#33106) - [Simplifying SOX Compliance in DevOps](#33105) - [Data Catalogs: the key to SQL Server compliance](#33104) T-SQL - [Them Con+cat+enatin’ Blues](#33129) - [Finding Table Defaults, Primary and Unique Keys and Foreign Keys with sp_helpconstraint](#33127) - [Special Islands](#33122) - [SQL SERVER – Simple Method to Extract Scale Part From Decimal Number](#33120) - [Pride and Prejudice... and Triggers](#33115) - [SQL Server Operations Studio and VSCode: The wrong default datetimeoffset format](#33113) - [Bulk insert issue with UTF-8 fixed-width file format](#33110) - [TSQL Tuesday #106: Triggers and the Spiral To Oblivion](#33090) - [“Disabling” Triggers Without ALTERs](#33089) - [TSQL Tuesday 106 – Trigger Headaches or Happiness: Capturing Database Creations.](#33087) - [Why I Love DML Triggers in SQL Server](#33086) - [T-SQL Tuesday #106 - Triggers](#33085) - [Are scalar functions really a bad thing?](#33083) - [Using WITH in an IF Condition](#33079) - [Which is Optimal – TOP 0 OR WHERE 1 = 2? – Interview Question of the Week #190](#33072) The Lighter Side - [402 horsepower and “up to 200 miles’” range for electric Mercedes-Benz EQC](#33070) SQL Server Security and Auditing - [TSQL Tuesday: Who is on my server?](#33111) SQL Server Security - [SYSTEM Permissions in SQL Server](#33064) Security news and thoughts - [Google Outlines Incident Response Process for Cloud Customers](#33142) - [Intel Fixes Yet Another Flaw In Management Engine](#33141) - [The Effectiveness of Publicly Shaming Bad Security](#33103) - [British Airways site had credit card skimming code injected](#33097) - [British Airways Data Breach Exposes 380,000 Customers to Risk](#33076) - [Unpatched routers being used to build vast proxy army, spy on networks](#33069) Reporting Services - [Enter Data feature now available in SQL Server 2016 Report Builder](#33121) Replication - [Only one Log Reader Agent can connect to a database at a time error – Transaction Replication/CDC](#33057) R Language - [Lesson 12 - Data Frames](#33132) - [Lesson 11 - List Subsetting](#33123) - [Tips for analyzing Excel data in R](#33062) - [Who wrote that anonymous NYT op-ed? Text similarity analyses with R](#33061) Product Upgrades and Releases - [SQL Audit logs in Azure Log Analytics and Azure Event Hubs](#33133) - [Released: Public Preview 2 for Version Agnostic SQL Server MP (2012-2017)](#33074) Product Reviews and Articles - [Highlighting Personally Identifiable Information (PII) across SQL Server instances and databases](#33099) - [Redgate University has opened its doors online](#33098) - [How to Detect SQL Injection Attacks using Extended Events and SQL Monitor](#33088) - [The Changing Role of SQL Server Monitoring](#33054) PowerShell - [new release with significant but non-breaking changes](#33051) PowerPivot/PowerQuery/PowerBI - [Power BI and the On-premises Data Gateway: What is it?](#33107) - [Master Detail Design with Drillthrough Filter in Power BI](#33047) - [Pareto, Burn-down & Accumulating Trend Charts in Power BI](#33046) - [Power BI For Report Consumers](#33045) - [Power BI Desktop And Role-Playing Dimensions: Can you do it?](#33044) Performance Tuning SQL Server - [Microsoft Dynamics CRM – Max Degree of Parallelism Settings and Slow Performance](#33140) - [Should You Use the New Compatibility Modes and Cardinality Estimator?](#33119) - [Can Forced Parameterization Go Wrong?](#33100) - [Different methods to generate query execution plans](#33094) - [Your SQL Server is Bored: What Low Wait Times Mean](#33071) - [Estimated Plans and Forced Plans from Query Store](#33055) MDX/DAX - [Performance Problems With MDX Calculated Measures That Return Constants In Analysis Services Multidimensional](#33131) - [DatesInPeriod vs DatesBetween; DAX Time Intelligence for Power BI](#33109) - [Optimizing DAX with cardinality estimation: computing working days](#33095) Hardware News - [ADATA Unveils IUSP33F BGA SSDs: Up to 1.2 GB/s Throughput](#33137) - [Toshiba’s Portégé X30T: An Uncanny Mix of Clamshell and Tablet](#33136) - [Seagate Unveils 14 TB Consumer HDDs: BarraCuda Pro, SkyHawk, IronWolf, & IronWolf Pro](#33096) - [AMD’s Ryzen Threadripper 2950X CPU Now Available](#33068) - [Lenovo Reveals Yoga C930 Convertible: 13.9-Inch LCD with Dolby Vision, 8th Gen Core i7](#33067) - [Samsung Adds 32 GB UDIMMs to Memory Lineup](#33066) - [The Toshiba XG6 1TB SSD Review: Our First 96-Layer 3D NAND SSD](#33065) HA/DR/Always On/Clustering - [The Hidden Danger of Readable Secondaries in SQL Server AlwaysOn Availability Groups](#33135) - [SQL SERVER – Scripts to Overview HADR / AlwaysOn Local Replica Server](#33102) - [AGLatency report tool introduction](#33078) Graph Databases - [The Right Tool for the Job: Azure Data Factory V2 vs. Integration Services](#33124) ETL/SSIS/ELT - [SQL101: Import/Export wizard](#33144) - [What’s new in ssisUnit?](#33116) - [CCI – How to load data for better Columnstore Segment Elimination](#33108) - [Flexible Data Pipelines: The Key to Competitiveness in the Data Age](#33050) DevOps and Continuous Delivery (CI/CD) - [A Quick Intro to DevOps in Azure](#33128) - [What’s new in Azure DevOps Launch Update](#33063) Database Design, Theory and Development - [Designation Property and Assertion Predicate](#33084) - [Next and Prior: Pointing in Data Models](#33048) Data Warehousing - [Do You Need a Relational Data Warehouse?](#33091) Data Science - [Blue Bikes Sharing in Boston](#33060) - [Simple Experiments with Smoothed Scatterplots](#33059) Data Privacy, Complianace, and GDPR - [The California Consumer Privacy Act: What You Need to Know](#33075) - [Why GDPR and the California Consumer Privacy Act of 2018 Are Valuable and Dangerous](#33052) - [Master Data Management for Achieving GDPR Compliance](#33049) Conferences, Classes, and Events - [MN SQLSaturday #796 Pre-Cons](#33139) - [New live online training class in October: Transactions, Locking, Blocking, Isolation, and Versioning](#33101) - [SQL in the City Summits–Coming to New York, London, and Chicago](#33092) Computing in the Cloud (Azure, Google , AWS) - [Customer Roadblocks to Azure](#33130) - [AzureCAT Whitepaper: SQL Server database files and backups on Azure Storage for SAP workloads](#33118) - [Azure Storage – Hot, Cold, Archive](#33112) - [What Happened to Microsoft’s Clouds Last Week?](#33077) Career Growth - [A Day in the Life of Brent Ozar: July 23, 2018 #SQLCareer](#33126) - [Watch My Keynote: Adapt and Thrive](#33053) Backup and Recovery - [Steps to Restore Database from Backup File in SQL Server](#33093) Azure SQL Managed Instance - [Connect to SQL Managed instance](#33134) - [Sending emails in Azure SQL Managed Instance](#33081) - [Working with Azure SQL Managed Instance using Azure CLI](#33080) Azure SQL Database - [Steps for SQL DB Geo Replication](#33143) - [What is built-in intelligence in Azure SQL Database?](#33117) - [MAXDOP and Azure SQL DB](#33114) Azure SQL Data Warehouse and Data Lake - [Azure Data Warehouse and 60 Servers](#33056) AI/Machine Learning/Cognitive Services - [How AI is leading to Autonomous Enterprise Integrations](#33125) Administration of SQL Server - [Should Index Changes Require Change Control?](#33138) - [Perfect Storm for Upgrading to a Modern Version of SQL Server](#33082) - [SQL SERVER – Rename Physical Database File Name for Any Database](#33073) - [Service Broker Target for Extended Events](#33058) [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 2018-09-17 [SQL Change Automation]( CI/CD for your SQL Server database Feeling the pain of managing and deploying database changes manually? Redgate SQL Change Automation completes your database delivery process by building, testing, and deploying the database changes you and your team check into version control. [Try it free]( [GDPR](www.red-gate.com/gdpr?utm_source=dbw&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20180916-slot2) How to make your SQL Server development GDPR ready Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. [Find out more about how our tools help with GDPR compliance](www.red-gate.com/gdpr?utm_source=dbw&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20180916-slot2) [Webinar]( How to create a living SQL Server data catalog With data often spread across a growing number of different environments, it's hard to know what to restrict and where it's located. In this session you’ll learn about practical steps you can take to create a durable and living record of what you hold in your SQL estate. [Register now]( Editorial - We Need Data Privacy Consistency For most of the last year, I've had quite a bit of my time devoted to the GDPR and related topics. My company is affected, as it's based in the UK. Not only must we comply, but we know many other companies must as well. As a result, some of our product focus was aimed at helping companies solve their data privacy issues, especially with regards to data. That continues to be a good idea as the GDPR isn't the only regulation out there affecting organizations' data handling practices. There are other laws around the world, but the US is a big market, one of the biggest we have, and we are seeing increased need in the US for the same types of data privacy and protection solutions mandated by the GDPR. California recently passed [their own data protection legislation](, and it's leading the way in the US. Tim Ford wrote [a short piece]( on how this affects his company. He notes that as a consumer, he's glad to see stricter data handling practices being required. However, as a business owner, he's concerned and I think there is some basis to be worried. There are other laws that might pass soon in the US. [New York has a bill](, Colorado has signed [a weaker, but still new, law](. Other states are considering items, but the US Congress has yet to really move forward on any legislation, which might lead us to have multiple data handling practices that are required. That would be a nightmare, much more difficult than the hiring and tax practices of different states. I couldn't imagine having to work with different processes, and certainly wouldn't want to have more restrictive laws being passed in the future that might cause us to change practices multiple times. I can only hope that the US gets a common law for all our states, and that the practices are in line with what the GDPR requires. Other countries have used that as a basis for their laws, and I can only hope the US does the same. Steve Jones from [SQLServerCentral.com]( » [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. Webinars [] [Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report]( - Microsoft MVP Steve Jones is joined by acclaimed author and researcher Gene Kim to discuss the latest in all things DevOps. They offer a closer look at the key findings in the 2018 Accelerate State of DevOps Report, and investigates the growing importance of the database in successful DevOps and IT performance....[(more)]( [] [Simplifying SOX Compliance in DevOps]( - Being compliant shouldn’t have to result in restricting your work or slowing down your release time. Discovering where you fit and how to be compliant can be confusing. Grant Fritchey will explore how IT Teams fit into this story and how to best protect your company and customers....[(more)]( [] [Data Catalogs: the key to SQL Server compliance]( - Protecting personally-identifiable information (PII) can be difficult. With data spread across a growing number of different environments, it's hard to know where PII is located. In this session, you’ll learn about regulatory requirements for maintaining a data catalog, common data classification frameworks, and options that currently exist to assist you....[(more)]( T-SQL [] [Them Con+cat+enatin’ Blues]( - We ran into a funny problem not long ago, revolving around a stored procedure that was building up a dynamic SQL string. The code had worked without a hitch in our development and test environments but blew up when it was moved to production due to the ......[(more)]( [] [Finding Table Defaults, Primary and Unique Keys and Foreign Keys with sp_helpconstraint]( - A little while back I wrote a post going over sp_help in a beginning effort to find table dependency information. � That expanded to reviewing sys.dm_sql_referenced_entities & sys.dm_sql_referenced_entities ......[(more)]( [] [Special Islands]( - Gaps and Islands tasks are classic querying challenges where you need to identify ranges of missing values and ranges of existing values in a sequence. The sequence is often based on some date, or date and time values, that should normally appear in ......[(more)]( [] [SQL SERVER – Simple Method to Extract Scale Part From Decimal Number]( - In my earlier post on “SQL SERVER – Different Methods to Extract Scale Part From Decimal Number“, I had shown you how to extract only the decimal part from a number. The post had three different methods to Extract Scale Part From Decimal Number. Recently ......[(more)]( [] [Pride and Prejudice... and Triggers]( - This post is part of #tsql2sday event :)I know, in the world of the database administrators and developers, the triggers are the ugly duck of the database development.They are hard to track, hard to maintain, can have a weird transaction behavior if ......[(more)]( [] [SQL Server Operations Studio and VSCode: The wrong default datetimeoffset format](C3%A1udio-silva/2018/09/12/sql-server-operations-studio-and-vscode-the-wrong-default-datetimeoffset-format/) - This post is to answer the question: "You are used to seeing in the format of yyyy-MM-dd right?" that I have raised on my blog post Don’t cutoff yourself with dates in T-SQL – Did you know…. As you could see from that blog post, my screen shots were ......[(more)](C3%A1udio-silva/2018/09/12/sql-server-operations-studio-and-vscode-the-wrong-default-datetimeoffset-format/) [] [Bulk insert issue with UTF-8 fixed-width file format]( - Fellow Canadian Doran Douglas brought this issue to my attention recently, and I wanted to share it with you as well. Let’s say you have a file in UTF-8 format. What this means is that some of the characters will be single-byte, and some may be more ......[(more)]( [] [TSQL Tuesday #106: Triggers and the Spiral To Oblivion]( - It’s the second Tuesday of the month which must mean only one thing… We’re all skint and payday feels like a long way off??? Not quite, well maybe… but it’s also T SQL Tuesday! This month’s invitation comes from Steve Jones and he’s asking us to write ......[(more)]( [] [“Disabling” Triggers Without ALTERs]( - I’ve worked with my share of Triggers over the years. Most of the time, they were relatively simple affairs – do a simple update to a date if a row changed or track some basic audits somewhere. Other times they performed some necessary, but complex, ......[(more)]( [] [TSQL Tuesday 106 – Trigger Headaches or Happiness: Capturing Database Creations.]( - I see your hidden database table trigger and raise you a more hidden server one! ...[(more)]( [] [Why I Love DML Triggers in SQL Server]( - It’s TSQL Tuesday today! If you’re not familiar, TSQL Tuesday is a monthly blogging event where a Microsoft Data Platform community member chooses a writing prompt. This is a great thing if you’re interested in starting a technical blog and wonder, “What ......[(more)]( [] [T-SQL Tuesday #106 - Triggers]( - For T-SQL Tuesday #106, Steve Jones (b|t) asks us to write about an experience with triggers, either good or bad. I'd bet that most of you are thinking about INSERT, UPDATE, and DELETE triggers. But for my DBA sensibilities, the best trigger is a DDL ......[(more)]( [] [Are scalar functions really a bad thing?]( - I’ve read a lot of things lately pointing to scalar functions as if they were the devil. In this blog I’m going to explore if that’s the case. Let’s have a look. It’s true that in many situations a scalar function is often a performance bottleneck; ......[(more)]( [] [Using WITH in an IF Condition]( - I am in the middle of working on my DB Design conference that occurs in a bit over a week from now. But I had this come up in some work I was doing and wanted to put it down in a blog. One of the most exciting features of SQL Server 2005 was the inclusion ......[(more)]( [] [Which is Optimal – TOP 0 OR WHERE 1 = 2? – Interview Question of the Week #190]( - Question: Which is Optimal – TOP 0 OR WHERE 1 = 2? Answer: They both are the same! I had received this question the other day via social media group. This question is also very popular in various organizations as well. I have heard it quite many times ......[(more)]( The Lighter Side [] [402 horsepower and “up to 200 miles’” range for electric Mercedes-Benz EQC]( - Mercedes-Benz Most of the traditional automakers have been slow to embrace electrification, but there's no denying that process is underway now. Mercedes-Benz is the latest automaker to unveil a new battery electric vehicle, taking the wraps off its ......[(more)]( SQL Server Security and Auditing [] [TSQL Tuesday: Who is on my server?]( - Here is my entry for this month’s T-SQL Tuesday. I once had to some auditing for a customer and it was a complicated, multi-stage process. We had to be able to demonstrate who had admin access and what kind of activity was going on, on the server. But ......[(more)]( SQL Server Security [] [SYSTEM Permissions in SQL Server]( - Okay folks, we need to have a conversation about SYSTEM permissions in SQL Server. SYSTEM is constantly over-permissioned in SQL Server . Some of the reasons given for why SYSTEM is over-permissioned are: My backup application runs as SYSTEM and needs ......[(more)]( Security news and thoughts [] [Google Outlines Incident Response Process for Cloud Customers]( - The four-phased approach is designed to quickly identify incidents, mitigate damage and restore services, the company says. ...[(more)]( [] [Intel Fixes Yet Another Flaw In Management Engine](37794.html) - Positive Technologies discovered a new vulnerability in Intel's Management Engine that could allow hackers to compromise sensitive information. ...[(more)]( [] [The Effectiveness of Publicly Shaming Bad Security]( - Presently sponsored by: Netsparker - dead accurate web application security scanning solution - Scan websites for SQL Injection, XSS & other vulnerabilitiesHere's how it normally plays out: It all begins when a company pops up online and makes some sort ......[(more)]( [] [British Airways site had credit card skimming code injected]( - Enlarge / Thousands of BA customers had their credit card data "skimmed" by malicious JavaScript code inserted into the airline's website. (credit: Alf van Beem) Last week, British Airways revealed that all the payment information processed through ......[(more)]( [] [British Airways Data Breach Exposes 380,000 Customers to Risk]( - At least 380,000 British Airways customers are at risk after the international airline reveals that its website and mobile application were somehow exploited by hackers. ...[(more)]( [] [Unpatched routers being used to build vast proxy army, spy on networks]( - Enlarge (credit: Dirk Hoffmann / EyeEm/Getty Images) Researchers at China's Netlab 360 have discovered that thousands of routers manufactured by the Latvian company MikroTik have been compromised by malware attacking a vulnerability revealed April. While ......[(more)]( Reporting Services [] [Enter Data feature now available in SQL Server 2016 Report Builder]( - We recently released a new version of SQL Server 2016 Report Builder that contained a brand new feature – the ability to enter data directly into an RDL report as an embedded dataset. This new feature is similar to the one found in Power BI Desktop. Now, ......[(more)]( Replication [] [Only one Log Reader Agent can connect to a database at a time error – Transaction Replication/CDC]( - An interesting issue regarding SQL Server Transaction Replication/CDC log reader agent job can’t be started with the following error: Msg 18752, Level 16, State 1, Procedure sp_replcmds, Line 1 [Batch Start Line 229] Only one Log Reader Agent or log-related ......[(more)]( R Language [] [Lesson 12 - Data Frames]( - Those of you with a strong background in databases will see some familiar concepts in this post. For lesson 12, we will consider datasets and structures in R that can accommodate them. Thinking back on what's been covered so far, we know vectors and ......[(more)]( [] [Lesson 11 - List Subsetting]( - Despite the similarities we've seen between vectors and lists, subsetting is considerably different. Let's revisit the division list from the last post. It has elements for "Name", (number of) "Teams", and "Conference". There are also five list elements--one ......[(more)]( [] [Tips for analyzing Excel data in R]( - If you're familiar with analyzing data in Excel and want to learn how to work with the same data in R, Alyssa Columbus has put together a very useful guide: How To Use R With Excel. In addition to providing you with a guide for installing and setting ......[(more)]( [] [Who wrote that anonymous NYT op-ed? Text similarity analyses with R]( - In US politics news, the New York Times took the unusual step this week of publishing an anonymous op-ed from a current member of the White House (assumed to be a cabinet member or senior staffer). Speculation about the identity of the author is, of ......[(more)]( Product Upgrades and Releases [] [SQL Audit logs in Azure Log Analytics and Azure Event Hubs]( - We are pleased to announce that Azure SQL Database Audit logs can now be written directly to Azure Log Analytics or Azure Event Hubs. This ability, now available in public preview, provides SQL Database Auditing customers with an easy way to centrally ......[(more)]( [] [Released: Public Preview 2 for Version Agnostic SQL Server MP (2012-2017)]( - We are adding support in the existing version agnostic SQL Server MP (2017+) to monitor older versions of the server, specifically 2012 through 2016. SQL Server 2008 and R2 end of support is nearing (July 2019) so we will leave monitoring 2008 in its ......[(more)]( Product Reviews and Articles [] [Highlighting Personally Identifiable Information (PII) across SQL Server instances and databases]( - With the recent regulatory pressures such as GDPR, being able to know what type of data we are holding onto within our business became a bigger challenge. At Redgate, we have been exploring ways to make this easier for our heavy SQL Server users and ......[(more)]( [] [Redgate University has opened its doors online]( - Redgate has built a great reputation for writing in-depth technical and useful how-to guides to help users make the most of our tools and solutions. But when you’re installing a piece of software for the first time, or wanting to know how to use an advanced ......[(more)]( [] [How to Detect SQL Injection Attacks using Extended Events and SQL Monitor]( - Even if all precautions have been taken to prevent SQL Injection attacks, as laid out in the OWASP website, it is still wise to be able to detect if an attempted attack is taking place, and it is essential to know if such an attack is successful. There ......[(more)]( [] [The Changing Role of SQL Server Monitoring]( - As SQL Server estates continue to grow quickly, and change in nature, due to the ease with which new cloud-based, containerized or virtual machine-based SQL Servers can be provisioned, so too the role of the monitoring tool changes. In organizations ......[(more)]( PowerShell [] [new release with significant but non-breaking changes]( - Marching onward to dbatools 1.0, a ton of commands have been renamed to align with our now mature naming scheme. These changes were made in today’s release, version 0.9.410 aka regularlegs, now available on GitHub and the PowerShell Gallery. Here’s the ......[(more)]( PowerPivot/PowerQuery/PowerBI [] [Power BI and the On-premises Data Gateway: What is it?]( - In this video, we look at the On-Premises Data Gateway. What is it exactly? This can be used with more than just Power BI. This includes PowerApps, Microsoft Flow, Azure Analysis Services and more. The entire Power Platform! If you found this video helpful, ......[(more)]( [] [Master Detail Design with Drillthrough Filter in Power BI]( - One of the most common requirements in any reporting solution is the ability to drill through from master page to the details page and see the details of an item or category. In this post, I will explain how this feature is simply possible in Power BI, ......[(more)]( [] [Pareto, Burn-down & Accumulating Trend Charts in Power BI]( - I’m managing an Agile team project using Microsoft Teams – the new project management platform integrated with Office 365. Teams is a simple and useful project management tool but it’s new and light on features. Using Power BI, we want to show the ......[(more)]( [] [Power BI For Report Consumers]( - In this video, we look at Power BI for report consumers. Those folks that maybe don't author reports, but just view reports within Power BI. We introduce some basic Power BI concepts and introduce you to areas of the Power BI service to get you familiar ......[(more)]( [] [Power BI Desktop And Role-Playing Dimensions: Can you do it?]( - In this video, Patrick talks about Power BI Desktop and role-playing dimensions. Can you even do this in Power BI Desktop? Patrick shows you why one particular way is not recommended as it will cause trouble. And then show you how you can use DAX within ......[(more)]( Performance Tuning SQL Server [] [Microsoft Dynamics CRM – Max Degree of Parallelism Settings and Slow Performance]( - My primary source of income for me is the Comprehensive Database Performance Health Check for any application using SQL Server. Recently, I was hired by a large organization which was struggling with the poor performance of their Microsoft Dynamics CRM. ...[(more)]( [] [Should You Use the New Compatibility Modes and Cardinality Estimator?]( - For years, when you right-clicked on a database and click Properties, the “Compatibility Level” dropdown was like that light switch in the hallway: You would flip it back and forth, and you didn’t really understand what it was doing. Lights didn’t go ......[(more)]( [] [Can Forced Parameterization Go Wrong?]( - App Like That If you’ve got the kind of application that sends bare-assed strings to SQL Server, you may end up with a weird choice. Brent will be sitting on one shoulder telling you to use Forced Parameterization. I’ll be on the other shoulder asking ......[(more)]( [] [Different methods to generate query execution plans]( - One of the things I enjoy about my job is when I have to develop or review some T-SQL code, for my own projects or reviewing someones else code. In both cases, you often have to tune some queries or review them to see if there is room for some performance ......[(more)]( [] [Your SQL Server is Bored: What Low Wait Times Mean]( - Let’s say you have an assistant. (I know, unlikely, but bear with me.) And say you give your assistant a task – hey, go fetch me a coffee. Your assistant would nod obediently, go head out to the neighborhood coffee shop, get your preferred Americano, ......[(more)]( [] [Estimated Plans and Forced Plans from Query Store]( - While all plans are estimated plans, there is still a difference between capturing an estimated plan and looking at a plan from the cache or from query store. Or is there? A question came up during a recent presentation; what happens to capturing an ......[(more)]( MDX/DAX [] [Performance Problems With MDX Calculated Measures That Return Constants In Analysis Services Multidimensional]( - Recently I was working on an MDX calculation that calculated a value at a low granularity and aggregated the result – basically what I described in my blog post here. Here’s a simplified version of the calculation written on a cube built from Adventure ......[(more)]( [] [DatesInPeriod vs DatesBetween; DAX Time Intelligence for Power BI]( - There are many time intelligence functions in DAX, and each of those is helping in aspects of analyzing data on dates. There are two functions which work very similar to each other but have a bit different usage; DatesInPeriod, and DatesBetween. In this ......[(more)]( [] [Optimizing DAX with cardinality estimation: computing working days]( - Estimating the cardinality of iterators is an important skill when optimizing DAX code. This article explains how to use this technique, evaluating different ways of computing the number of working days between two dates. Excel offers a simple function ......[(more)]( Hardware News [] [ADATA Unveils IUSP33F BGA SSDs: Up to 1.2 GB/s Throughput]( - ADATA has announced its first SSDs in a BGA form-factor. The IUSP33F drives are designed for devices that require a storage solution with a minimal footprint, such as thin-and-light laptops, convertible notebooks, or tablets. Meanwhile, the performance ......[(more)]( [] [Toshiba’s Portégé X30T: An Uncanny Mix of Clamshell and Tablet]( - Back at the IFA event in Germany, I noticed that Toshiba had announced a new laptop. However the headline didn’t quite grab my eye. It was only by chance that while walking through the show floor, I stumbled into the Toshiba booth where the device, called ......[(more)]( [] [Seagate Unveils 14 TB Consumer HDDs: BarraCuda Pro, SkyHawk, IronWolf, & IronWolf Pro]( - Seagate on Monday introduced a portfolio of consumer-oriented 14 TB hard drives. The new HDDs belong to the BarraCuda Pro, SkyHawk, IronWolf Pro, and IronWolf series, and rely on the same helium-filled platform that powers the datacenter-oriented Exos ......[(more)]( [] [AMD’s Ryzen Threadripper 2950X CPU Now Available]( - As pre-announced, AMD has begun selling its latest Ryzen Threadripper 2950X processor. The new CPU features 16 cores and is aimed at high-end desktops that can be overclocked for additional performance. And with half the cores, the new CPU is priced ......[(more)]( [] [Lenovo Reveals Yoga C930 Convertible: 13.9-Inch LCD with Dolby Vision, 8th Gen Core i7]( - Lenovo has introduced its new Yoga C930 convertible notebook. Just like last year’s Yoga 920 PC, the new generation Yoga laptop is based on Intel’s 8th Gen Core i-series processor with four cores, however the C930 comes in a new CNC-machined aluminum ......[(more)]( [] [Samsung Adds 32 GB UDIMMs to Memory Lineup]( - Samsung has quietly added 32 GB unbuffered DDR4 memory modules to its lineup of products. The UDIMMs are based on the company’s 16 Gb chips, which were introduced earlier this year that are already used for 32 GB SO-DIMMs as well as 64 GB RDIMMs. Samsung’s ......[(more)]( [] [The Toshiba XG6 1TB SSD Review: Our First 96-Layer 3D NAND SSD]( - For the second year in a row, Toshiba is the first to ship a new generation of 3D NAND. The new XG6 NVMe SSD for OEMs otherwise changes little from last year's XG5, which makes it another good all-around drive with excellent power efficiency. ...[(more)]( HA/DR/Always On/Clustering [] [The Hidden Danger of Readable Secondaries in SQL Server AlwaysOn Availability Groups]( - A long time ago in a galaxy far, far away, I had to troubleshoot interesting performance issue in SQL Server. Suddenly, the CPU load on the server started to climb up. Nothing changed in terms of workload. The system still processed the same amount of ......[(more)]( [] [SQL SERVER – Scripts to Overview HADR / AlwaysOn Local Replica Server]( - Today, I have received a very interesting script from SQL Server Expert Dominic Wirth. He has written a very helpful script which displays a utilization overview of the local availability group replica server. The overview will contain the number of ......[(more)]( [] [AGLatency report tool introduction]( - I wrote an article to discuss about data movement latency between AG groups: Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups Now I develop a tool to analyze AG log block movement latency between replicas ......[(more)]( Graph Databases [] [The Right Tool for the Job: Azure Data Factory V2 vs. Integration Services]( - With the features of Azure Data Factory V2 becoming generally available in the past few months, especially the Integration Services Runtime, the question persists in our practice about which data integration tool is the best fit for a given team and ......[(more)]( ETL/SSIS/ELT [] [SQL101: Import/Export wizard]( - Do you need to quickly Import or Export data to/from SQL Server? Do you want to avoid the hassle of creating and deploying an SSIS pacakge? The SQL Server Import/Export wizard might be perfect for you! The wizard steps you through the necessary pieces ......[(more)]( [] [What’s new in ssisUnit?]( - ssisUnit has a stable version for SSIS 2005 – 2014. It didn’t change much since August 2014, until August 2017. Then my Pull Request was merged, and it added some new functionality for ssisUnit. First – it works with SSIS 2017. You can probably use it ......[(more)]( [] [CCI – How to load data for better Columnstore Segment Elimination]( - Introduction In this blog post, I would like to explain what a columnstore segment elimination is and how to load data to achieve better segment elimination. I’ll cover the following scenarios of loading data into a CCI: Loading a Small Data Set: Create ......[(more)]( [] [Flexible Data Pipelines: The Key to Competitiveness in the Data Age]( - Click to learn more about author Margaret Scovern. Humans are arguably the most adaptable creatures on the planet — on both a micro and macro scale, we’re able to evolve to meet new challenges or circumstances all the time. It makes sense, then, that ......[(more)]( DevOps and Continuous Delivery (CI/CD) [] [A Quick Intro to DevOps in Azure]( - Today, I’d like to discuss DevOps in Azure and tell you why you should consider using DevOps when you start moving to Azure. What DevOps is, can be hard to define. Everyone seems to know what it is, but no one can seem to define it in a simple way. Microsoft’s ......[(more)]( [] [What’s new in Azure DevOps Launch Update]( - We shared some exciting news this morning on the Azure blog: We’re bringing Azure DevOps to developers and enterprises around the world who need flexible and efficient tools for their development process. With the evolution of Visual Studio Team Services ......[(more)]( Database Design, Theory and Development [] [Designation Property and Assertion Predicate]( - “A set is an identification of zero or more objects (depending on context, the terms “elements” or even “entities” may be used) that can be referred to as a group by name (usually a symbol), and which are drawn from some pre-defined universe of objects. ...[(more)]( [] [Next and Prior: Pointing in Data Models]( - Click to learn more about author Thomas Frisendal. Pointers have been in and out of data models. From the advent of the rotating disk drive in the 60s and until around 1990, pointers were all over the place (together with “hierarchies”, which were early ......[(more)]( Data Warehousing [] [Do You Need a Relational Data Warehouse?]( - Are you looking to do a major update to your data warehouse or looking to modernize? Many technologies have come about that are changing the landscape of what data warehouses are made of. In this Azure Every Day session, I’d like to talk about 3 new ......[(more)]( Data Science [] [Blue Bikes Sharing in Boston]( - Blue Bikes is a bicycle sharing system in the Boston, Massachusetts. The bikes sharing program started on 28 July 2011. This program aimed for individuals to use it for short-term basis for a price. The program allows individuals to borrow a bike from ......[(more)]( [] [Simple Experiments with Smoothed Scatterplots]( - Scatter plots reveal relationships between two variables X, Y when both are numeric variables. Traditional scatter plots suffer from datapoints overlapping as the number of (Xi, Yi) pairs increases. Datapoints overlapping makes the relationship between ......[(more)]( Data Privacy, Complianace, and GDPR [] [The California Consumer Privacy Act: What You Need to Know]( - Join eWEEK Senior Editor Sean Michael Kerner as he discusses what the California Consumer Privacy Act is and how it will it impact US vendors, its relationship to GDPR, and what controls and approaches are needed for compliance. ...[(more)]( [] [Why GDPR and the California Consumer Privacy Act of 2018 Are Valuable and Dangerous]( - Good data stewardship is critical, but constantly shifting regulations--such as the new California Consumer Privacy Act of 2018--are costly and confusing. ...[(more)]( [] [Master Data Management for Achieving GDPR Compliance]( - Click to learn more about author Bruck Assefa. Organizations are constantly at risk of paying a hefty penalty for not complying with rules and regulations that dictate how they should operate and do business. A recent research by the Ponemon Institute ......[(more)]( Conferences, Classes, and Events [] [MN SQLSaturday #796 Pre-Cons]( - We are just a few weeks out from #sqlsatmn and over 250 have registered! Don’t miss out on securing your spot, register today! Checkout pre-con events and the earl-bird pricing while it lasts:) Early-bird ......[(more)]( [] [New live online training class in October: Transactions, Locking, Blocking, Isolation, and Versioning]( - Continuing our series of live, online classes, Kimberly will be delivering her new IETLB: Immersion Event on Transactions, Locking, Blocking, Isolation, and Versioning in October! The class will be delivered live via WebEx on October 9-11 (roughly 12-13 ......[(more)]( [] [SQL in the City Summits–Coming to New York, London, and Chicago]( - I love New York. Every time I get to travel to the city, I get excited and look forward to the trip. I was able to spend a night in Brooklyn earlier this year, touring a college with my daughter. Now I’m very lucky to get a second trip this year, with ......[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Customer Roadblocks to Azure]( - When I speak with customers, I often find they share common roadblocks to using Azure. I recently sat down with our Consulting Sales Manager in Texas, to ask what he hears from customers, to see if together, we can shed some light to clear up some of ......[(more)]( [] [AzureCAT Whitepaper: SQL Server database files and backups on Azure Storage for SAP workloads]( - As more users migrate their SAP workloads to Azure, they will discover the benefits of migrating the Microsoft SQL Server data tier as well. Storing SQL Server data files on Azure can help speed migration, improve security, and provide high availability ......[(more)]( [] [Azure Storage – Hot, Cold, Archive]( - There’s a lot of talk about storage options. When working with customers, I often introduce them to Azure through storage, since it’s a great way to leverage cloud assets in a positive, non-threatening way. In Azure there are 3 types of storage within ......[(more)]( [] [What Happened to Microsoft’s Clouds Last Week?]( - The Problems trying to set up some new Azure subscriptions for customers but the required permissions work that Microsoft does in the back ground (Azure AD permissions) didn’t appear to be working. I suspected there was a glitch in Microsoft somewhere ......[(more)]( Career Growth [] [A Day in the Life of Brent Ozar: July 23, 2018 #SQLCareer]( - Steve Jones asked data professionals to cover four days in our lives, so I blogged about a normal non-client-facing day, and then an unproductive day. Today is different: I’m client-facing. We specialize in a 3-day SQL Critical Care: Day 1 – we meet ......[(more)]( [] [Watch My Keynote: Adapt and Thrive]( - Do you know where your career is going? Do you know what skills you need to be successful in the current climate? In this 30 minute segment of the SQL in the City Livestream, I discuss the challenges, as well as the opportunities facing you as a data ......[(more)]( Backup and Recovery [] [Steps to Restore Database from Backup File in SQL Server]( - OverviewIn most of the cases, users create a backup file of their database on their machine. This help the users to perform recovery at the time of any disaster, or manage large amount of database onto the SQL server. However, users generally knows the ......[(more)]( Azure SQL Managed Instance [] [Connect to SQL Managed instance]( - SQL Managed instance is located inside a Vnet. The options to connect to it are : ...[(more)]( [] [Sending emails in Azure SQL Managed Instance]( - Azure SQL Database Managed Instance enables you to use most of the SQL Server features in fully managed cloud environment. One of the features is database mail system that enables you to send emails directly from Managed Instance. In this post we will ......[(more)]( [] [Working with Azure SQL Managed Instance using Azure CLI]( - Azure Command Line Interface (CLI) is a command line library that enables you to quickly and easily work with your Azure SQL Managed Instances. In this post, you will see some basic CLI commands that can be used with Managed Instance. You can run Azure ......[(more)]( Azure SQL Database [] [Steps for SQL DB Geo Replication]( - Unless you are living under a rock, you have most likely heard of the hurricane that is bearing down on the east coast of the United States. Some areas have mandatory evacuation and I have seen reports of 80’ plus foot waves so this hurricane is not ......[(more)]( [] [What is built-in intelligence in Azure SQL Database?]( - Azure SQL Database is a fully managed latest stable version of SQL Server Database Engine hosted in Azure cloud that has several built-in features that protect and manage your data. These features may autonomously perform various maintenance tasks instead ......[(more)]( [] [MAXDOP and Azure SQL DB]( - The infamous setting that we all know and love – MAXDOP. Did you know that you can actually control MAXDOP when using Azure SQL Database? You might not be able to tinker with the Cost Threshold for Parallelism setting but … Continue reading ? ...[(more)]( Azure SQL Data Warehouse and Data Lake [] [Azure Data Warehouse and 60 Servers]( - In today’s segment of Azure Every Day, I’d like to talk about Azure Data Warehouse and to help you understand some of the underlying architecture and its impact on the workloads you’re planning to put into Azure. Azure Data Warehouse is a parallel data ......[(more)]( AI/Machine Learning/Cognitive Services [] [How AI is leading to Autonomous Enterprise Integrations]( - Click to learn more about author Prasanna Kumar Illa. We all are aware that AI (Artificial Intelligence) plays an increasingly significant role in automating highly repetitive tasks, at scale. By repeatedly analyzing the data, AI adapts progressive learning ......[(more)]( Administration of SQL Server [] [Should Index Changes Require Change Control?]( - We got a phenomenal series of questions from a client, and I wanted to encapsulate the answers into a blog post to help more folks out: Should all index changes require testing in a staging environment, no matter how big or small? What would be a reasonable ......[(more)]( [] [Perfect Storm for Upgrading to a Modern Version of SQL Server]( - A number of ongoing and upcoming developments in software, hardware, and storage technology are shaping up to make the next twelve to eighteen months an ideal time to migrate from a legacy version of SQL Server to a modern version of SQL Server. Personally, ......[(more)]( [] [SQL SERVER – Rename Physical Database File Name for Any Database]( - During the recent Comprehensive Database Performance Health Check, I was asked is there any way we can rename the logical database file name. I have blogged about it here: SQL SERVER – Rename Logical Database File Name for Any Database. Right after the ......[(more)]( [] [Service Broker Target for Extended Events]( - (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.) During one of my last Online Trainings about Extended ......[(more)]( --------------------------------------------------------------- Administrative To be removed from this list, please click [here]( Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can [change your profile]( or follow the instructions on the daily newsletter. -----------------------------------------------