SQL Server Cumulative Updates Released (Database Weekly 7/23/2018)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [Financial Services: Diagnose issues within your SQL Server Estate in 5 minutes or less](#32600) - [Gaining buy in for Database DevOps in a world of increased data governance](#32599) Virtualization and Containers - [Backup and Restore operations with SQL Server 2017 Docker containers using SQL Operations Studio](#32643) - [Creating SQL images in Azure with ACR Build](#32606) - [Understanding Data Sharing in SQL Server Docker Containers](#32562) T-SQL - [Selecting a Random Row From a Table](#32649) - [ALTER TABLE ALTER COLUMN failed because one or more objects access this column](#32642) - [Spoofing Data Convincingly: Altering Table Data](#32635) - [T-SQL Tuesday #104 Roundup](#32617) - [Know How Table Variables Different From Temporary Tables in SQL Server](#32614) - [Optimization Thresholds – Grouping and Aggregating Data, Part 4](#32590) - [The SQL Server Calendar project](#32569) - [Inline Table-Valued Functions (TVFs)](#32565) - [Counting NULLs](#32564) - [The BREAK Keyword in Transact SQL](#32560) - [How SQL DISTINCT and ORDER BY are Related](#32556) Tools - [Removing [square brackets] from generated SSMS scripts](#32604) The Lighter Side - [Jeff Bezos said they’d test the heck out of New Shepard—he wasn’t kidding](#32622) - [Britain joins the microlaunch space race with a new rocket and spaceport](#32593) Testing Software - [TDD for SQL Server using tSQLt course certificates now available](#32637) Tech News - [Walmart Taps Microsoft's Cloud for Next-Generation Retail](#32631) - [Developer faces prison after admitting admin software was really a RAT](#32623) - [Microsoft Urges Lawmakers to Regulate Face-Recognition Tech](#32552) SQL Server on Linux - [SQL Server on Linux: Why Do I Have Two SQL Server Processes](#32619) SQL Server News - [Cumulative Update #9 for SQL Server 2017 RTM](#32646) - [Cumulative Update #2 for SQL Server 2016 SP2](#32628) - [Cumulative Update #10 for SQL Server 2016 SP1](#32627) - [SQL Server 2008 end of support is the first step to tomorrow’s database](#32583) Software Development - [The Bare Minimum You Need To Know To Work With Git](#32586) Security news and thoughts - [$1 million heist on Russian bank started with hack of branch router](#32652) - [Installing a Credit Card Skimmer on a POS Terminal](#32632) - [Timehop Breach Exposes 21 Million Email Addresses](#32588) R Language - [Clean Your Data in Seconds with This R Function](#32618) - [R 3.5.1 update now available](#32571) Python in SQL Server - [Using Python Pandas dataframe to read and insert data to Microsoft SQL Server](#32570) Product Upgrades and Releases - [Cumulative Update #9 for SQL Server 2017 RTM](#32655) - [The July release of SQL Operations Studio is now available](#32654) - [Oracle Patches 334 Flaws in July Critical Patch Update](#32636) - [Released: Public Preview for Version Agnostic SQL Server MP (2012-2017)](#32587) - [Public Preview of Table Variable Deferred Compilation in Azure SQL Database](#32574) - [Public Preview of Row Mode Memory Grant Feedback in Azure SQL Database](#32573) Product Reviews and Articles - [Data Masker for SQL Server and Temporal Tables](#32644) - [Spoofing Data Convincingly: Masking Continuous Variables.](#32634) - [Is this a SQL Provision Cloned Database?](#32612) - [Scaling SQL Monitor to Large SQL Server Estates](#32609) - [The ‘Right to be Forgotten’ and Data Masker for SQL Server](#32608) - [SQL Prompt code analysis: avoiding the old-style TOP clause](#32558) PowerShell - [Figuring out a PowerShell version of Dynamic SQL](#32641) - [Start-DbaMigration](#32596) PowerPivot/PowerQuery/PowerBI - [Using Process Monitor To Find Out How Much Data Power Query Reads From A File](#32645) - [Power BI 101 – Log Files and Tracing](#32639) - [Power BI Introduction: Working with SQL Server data in Power BI Desktop — Part 5](#32633) - [Power BI Custom Visuals Class (Module 111 – Horizontal Bar Chart)](#32616) - [Power BI Tutorial: Sort By A Measure Not Shown In Visual](#32601) - [Getting the Data to Power BI](#32561) - [Power BI Row-Level Security And Where To Filter](#32547) Performance Tuning SQL Server - [A Quirk In Parallel Hash Join Plans](#32624) - [The Horror of Conflicting Database Collations](#32598) - [Trace Flag 7470](#32589) - [[Video] Interpreting Missing Index Recommendations](#32581) - [Index on Key vs Included](#32567) Microsoft News - [Microsoft crosses $100 billion annual revenue mark for the first time](#32650) Internet of Things - [GE and Microsoft Enter into their Largest Partnership to Date, Accelerating Industrial IoT Adoption](#32603) Hardware News - [Two Hard-to-Detect Spectre Flaws Found for Intel, Arm Processors](#32629) - [Samsung Announces First LPDDR5 DRAM Chip, Targets 6.4Gbps Data Rates & 30% Reduced Power](#32621) - [Intel’s High-End Cascade Lake CPUs to Support 3.84 TB of Memory Per Socket](#32594) - [Surface Go is Microsoft's Latest Effort to Grow Surface Brand](#32555) - [Apple Debuts Upgraded Pro Laptops Ahead of Fall Product Blitz](#32554) HA/DR/Always On/Clustering - [How to Test Disaster Recovery Before You Go Live](#32653) - [AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING](#32575) ETL/SSIS/ELT - [Testing the loops in ssisUnit](#32568) DevOps and Continuous Delivery (CI/CD) - [The march of DevOps from the backroom to the boardroom](#32647) - [Making the database key to DevOps with comprehensive, customizable monitoring](#32578) - [Why code quality is vital in the world of database DevOps](#32577) - [Databases and DevOps, Tools are the Easy Part](#32563) Data Privacy and GDPR - [Data collection and data markets in the age of privacy and machine learning](#32630) - [Provisioning Data Catalogs with Metadata: A Variable Challenge](#32602) Data Mining/Data Analysis - [Book Review – Factfulness by Hans Rosling](#32566) Conferences, Classes, and Events - [Query Store Training – Portugal](#32640) - [MN SQLSaturday #796 – Oct 6](#32582) - [What Do You Want to Learn This Fall?](#32580) Computing in the Cloud (Azure, Google , AWS) - [Building SQL ConstantCare®: What Cloud Hosting Costs](#32579) - [Microsoft adds new SSD disk option to its Azure Data Box Disk appliance](#32572) Career Growth - [A Very Unproductive Day in the Life of Brent Ozar: July 18, 2018 #SQLCareer](#32638) - [A Day in the Life of Brent Ozar: July 17, 2018 #SQLCareer](#32605) - [How to Future-Proof a Career in IT](#32553) Backup and Recovery - [Behavior Change for VSS Backups on VMs with Basic Availability Groups](#32648) - [SQL SERVER – Backup to URL – Script to Perform Stripped Backup using Shared Access Signature (SAS)](#32626) - [RESTORE VERIFYONLY what does WITH LOADHISTORY do?](#32613) - [SQL SERVER – Msg 3292: A Failure Occurred While Attempting to Execute Backup or Restore With a URL Device Specified](#32584) Azure SQL Managed Instance - [Storage performance best practices and considerations for Azure SQL DB Managed Instance (General Purpose)](#32651) Azure SQL Data Warehouse and Data Lake - [Data lakes and swamps, oh my](#32611) Analysis Services / BI on the MS Stack - [SQL or M? – SSAS Partitions](#32548) AI/Machine Learning/Cognitive Services - [What Does It Mean to Certify an AI Product as Safe?](#32549) Administration of SQL Server - [A New tempdb Trace Flag Appears!](#32625) - [Restarting the SQL Service may fail occasionally with the error, “TCP port is already in use”](#32620) - [Schedule an Extended Event to run at a Specific Time](#32615) - [SQL Server system databases 101](#32610) - [Monitoring Space Used by Query Store](#32607) - [Log Backups, Auto Growth Settings, & Alerts (Part 1)](#32597) - [Troubleshooting with windows debugger](#32595) - [SQL Server Extended Events in Production](#32592) - [Quickly Change SQL Job Owners](#32591) - [How to Convert Hex Windows Error Codes to the Meaningful Error Message – 0x80040002 and 0x80040005 and others? – Interview Question of the Week #182](#32585) - [Rolling Back SQL Server agent job owner changes](#32576) - [Statistics update job](#32559) - [Finding Database Indexes: “They’re coming outta the walls! They’re coming outta the goddamn walls!”](#32557) - [managing central management server](#32551) - [TSQL Tuesday #104: dbo.Check_FileSize](#32550) [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-07-23 [SQL Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( [Database DevOps](www.red-gate.com/dlmdemo?utm_source=ssc&utm_medium=pubemailad&utm_content=devops-webinar&utm_campaign=databasedevops&utm_term=20180722-slot2) Database DevOps Demo Webinar Learn how to automate your database deployments alongside your app code in this free demo webinar. [Register now](www.red-gate.com/dlmdemo?utm_source=ssc&utm_medium=pubemailad&utm_content=devops-webinar&utm_campaign=databasedevops&utm_term=20180722-slot2) [SQL Monitor]( What’s the top challenge faced by SQL Server professionals in 2018? Learn how 626 SQL Server professionals monitor their estates in our new report on the State of SQL Server Monitoring. Discover the challenges currently facing the industry, and what is coming next. [Download your free copy of the report]( Editorial - Future Proofing an IT Career There was a piece this week on [how to future proof your IT career](. It's by Rod Trent, who built a similar site to SQLServerCentral for Systems Management Server and it's successor, Operations Manager. Rod is the go to person for infrastructure management, and he's also a presenter and organizer of the [IT/Dev Connections]( conference. The piece is a bit of a marketing item for the conference and Rod's site, but I think there is good advice inside. As a side note, if you want to attend a conference that covers more than just SQL Server, give [DevConnections]( a look. The event used to be in Las Vegas every year, but it's starting to move around and is in Dallas this October. Rod has three simple ideas: join a community, go to a conference, and know about more platforms or technologies. All in all, not bad advice because quite simply, there aren't many IT careers that are future proof. I suppose if you're a mainframe person or incredibly good at Cobol you might always have someone that wants to hire you, but there are fewer and fewer opportunities all the time. These days, I think if you aren't learning and growing, you are going to struggle in your career over time. There are lots of ways to stagnate in your career. Even if you're an avid reader of [SQLServerCentral](, unless you are practicing some new skills and better understanding them, you might not be prepared for a rapid change in your career. While I'd like to think that most of us know if we have stable working situations, I'm sure that not all of us do. If something changes, can you respond? Will you have opportunities? It's likely you could find some job, but if it's a large pay cut, in another part of the country, or some other significant change, can you adapt? I think being a part of a community is hugely important, just like I think networking with fellow professionals is the best way to keep your career moving in the direction you want. Others help give you perspective, ideas, inspiration, knowledge of openings, and even may hire you. Communities and networking are beneficial, but you need to be a part of them before you need them. Spending time at user groups, online, lunching with friends, these are long term efforts. I don't necessarily think this means you need to attend a conference though, especially if your employer won't fund the time. Rod does have one great sentence: "Don't be a one-trick pony." These days we are combining and supporting more and more technologies. The idea of being a specialist in one area is good if you're established already as the expert and go-to person, but for most of us, we need to know about a lot of different technologies. At least enough to judge where and when they fit in situations and be able to discuss advantages and disadvantages. You might hate the cloud or relational platforms, or Java, but you should be able to intelligently discuss why. I try to learn about lots of technologies, improving skills regularly, but being aware of what else is out there. I don't worry about whether I should know Power BI or containerization or some specific technology well. I know I can learn more if there's an opportunity. Or if I'm hired for one thing, but they ask me to do another. To me, the best way to future proof yourself is to keep learning, keep building skills (both old and new), and being aware of the world around you by networking with others. That might not prevent you from being let go, but it certainly can help you find a new situation. 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 [] [Financial Services: Diagnose issues within your SQL Server Estate in 5 minutes or less]( - Are your critical servers left vulnerable to attacks and failed deployments, resulting in downtime, loss of data, and loss of revenue? Join us to discuss how to pinpoint the root cause of performance issues in minutes and best practices in proactive monitoring and performance tuning....[(more)]( [] [Gaining buy in for Database DevOps in a world of increased data governance]( - The key to successful DevOps implementation is gaining buy in across the organisation. In this webinar Microsoft MVP and Microsoft Regional Director Simon Sabin discusses some of the challenges you’ll face as you go on your journey to bringing the database into a DevOps rollout....[(more)]( Virtualization and Containers [] [Backup and Restore operations with SQL Server 2017 Docker containers using SQL Operations Studio]( - In this 18th article of the series, we’ll discuss the concepts of database backup-and-restore operations with SQL Server 2017 Docker containers using SQL Ops Studio (SOS). Before proceeding, you need to have Docker engine installed and SQL Ops studio ......[(more)]( [] [Creating SQL images in Azure with ACR Build]( - Whenever I’ve pushed images up to an Azure Container Registry I’ve been building them locally (using Docker for Windows) and then manually pushing them up. However, I don’t need to do this. What I can do instead is use the Azure Container Registry Build ......[(more)]( [] [Understanding Data Sharing in SQL Server Docker Containers]( - This article talks about shared volumes and the preferred mechanism for data persistence using the Docker containers. This article covers the following topics: Introduction of the Docker volumes. Managing volume using Docker CLI commands or Docker API. ...[(more)]( T-SQL [] [Selecting a Random Row From a Table]( - A couple of times recently I’ve seen the question asked, ‘How can I select a single row at random from a table?’. There are often a few ways of doing this suggested, most seem to rely using CTEs or temp tables. I thought I’d share, in a quick post a ......[(more)]( [] [ALTER TABLE ALTER COLUMN failed because one or more objects access this column]( - Recently I ran across an issue with manually created statistics objects preventing datatype modification for a column from varchar to nvarchar. This issue occurred because the client has a policy in place where auto create... The post ALTER TABLE ALTER ......[(more)]( [] [Spoofing Data Convincingly: Altering Table Data]( - When you are developing an existing database, or demonstrating it, you nowadays need pseudonymised data, or even better, completely anonymized data. This data has to look right at first glance, and it needs to have the same distribution as the real data. Although ......[(more)]( [] [T-SQL Tuesday #104 Roundup]( - This month’s T-SQL Tuesday topic asked “What code would you hate to live without?” Turns out you like using script and code to automate boring, repetitive, and error-prone tasks. Thank you to everyone who participated; I was nervous that July holidays ......[(more)]( [] [Know How Table Variables Different From Temporary Tables in SQL Server]( - Know What Table Variables are ?SQL Server support features that allow users to work with temporary data that gets stored in temporary tables. Microsoft introduced table variables for the first time with SQL Server 2000 as an alternate option to using ......[(more)]( [] [Optimization Thresholds – Grouping and Aggregating Data, Part 4]( - This article is the fourth in a series about optimization thresholds. The series covers grouping and aggregating data, explaining the different algorithms that SQL Server can use, and the costing model that helps it choose between the algorithms. In ......[(more)]( [] [The SQL Server Calendar project]( - I’m the type of developer that invents wheels. Yes, every wheel I design is unique in its own way, and hand-crafted for a specific purpose. And so it has also been with calendar dimensions (typically when I do data warehousing work). This got me thinking ......[(more)]( [] [Inline Table-Valued Functions (TVFs)]( - Lets start off with what is a table-valued function (TVF)? A TVF is a dynamic table produced at the time of execution, depending on parameters. Like a view, a TVF creates a result set only when it’s executed, but, unlike a view, it can be parameterized. ...[(more)]( [] [Counting NULLs]( - Recently I was doing one of Kendra Little’s (b/t) SQL Server quizzes. Specifically the Quiz: COUNT() in SQL Server. As always I enjoy these quizzes and in this particular case it gave me an idea for a post. How do NULL values affect the COUNT function? Here ......[(more)]( [] [The BREAK Keyword in Transact SQL]( - BREAK is a useful piece of control-of-flow language in SQL Server, but it only works in specific situations. Want to take the quiz first? Take the quiz on BREAK here, then head back here to see how you compare to other folks who took the quiz. Break ......[(more)]( [] [How SQL DISTINCT and ORDER BY are Related]( - One of the things that confuse SQL users all the time is how DISTINCT and ORDER BY are related in a SQL query. The Basics Running some queries against the Sakila database, most people quickly understand: SELECT DISTINCT length FROM film This returns ......[(more)]( Tools [] [Removing [square brackets] from generated SSMS scripts]( - This red panda is sad because it has to use [brackets] on its code. Might also be sad to find out it’s not actually a panda. The number of people who really love having their code [quoted] with [square brackets] is pretty small. The vast majority of ......[(more)]( The Lighter Side [] [Jeff Bezos said they’d test the heck out of New Shepard—he wasn’t kidding]( - Blue Origin live video With its ninth flight test, the New Shepard launch system put on quite a show on Wednesday morning. Flying from West Texas, the rocket and spacecraft ascended toward space before separating after about 2 minutes and 40 seconds. ...[(more)]( [] [Britain joins the microlaunch space race with a new rocket and spaceport]( - Orbex/Anders Bøggild The United Kingdom has entered the race to develop low-cost, high-volume rockets for small satellites. Orbex, a British-based company with subsidiaries and production facilities in Denmark and Germany, announced Monday that it has ......[(more)]( Testing Software [] [TDD for SQL Server using tSQLt course certificates now available]( - I have been asked a few times now for course completion certificates so I have decided to give out certificates to anyone who pays for a plan and sends me a screen shot of a unit test they have written and answer a question on unit testing. I will generate ......[(more)]( Tech News [] [Walmart Taps Microsoft's Cloud for Next-Generation Retail]( - Walmart plans to use Microsoft's intelligent cloud offerings to modernize its operations and migrate hundreds of applications to Azure. ...[(more)]( [] [Developer faces prison after admitting admin software was really a RAT]( - A Kentucky man has pleaded guilty to federal charges he developed, marketed, and provided technical support for a "remote access trojan," or RAT—that is, software he knew customers used illegally to take control of other people’s computers. Colton Grubbs ......[(more)]( [] [Microsoft Urges Lawmakers to Regulate Face-Recognition Tech]( - Microsoft Corp., which has come under fire for a U.S. government contract that was said to involve facial recognition software, said it will more carefully consider contracts in this area and urged lawmakers to regulate the use of such artificial intelligence ......[(more)]( SQL Server on Linux [] [SQL Server on Linux: Why Do I Have Two SQL Server Processes]( - When starting SQL Server on Linux why are there two (2) sqlservr processes? systemctl status mssql-servermssql-server.service - Microsoft SQL Server Database Engine…  CGroup: /system.slice/mssql-server.service          +-85829 /opt/mssql/bin/sqlservr      ......[(more)]( SQL Server News [] [Cumulative Update #9 for SQL Server 2017 RTM]( - The 9th cumulative update release for SQL Server 2017 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......[(more)]( [] [Cumulative Update #2 for SQL Server 2016 SP2]( - The 2nd cumulative update release for SQL Server 2016 SP2 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......[(more)]( [] [Cumulative Update #10 for SQL Server 2016 SP1]( - The 10th cumulative update release for SQL Server 2016 SP1 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......[(more)]( [] [SQL Server 2008 end of support is the first step to tomorrow’s database]( - Today, Takeshi Numoto blogged about the upcoming SQL Server 2008 and 2008 R2 end of support. If youve been thinking about what to do with your SQL Server 2008 and 2008 R2 databases, youre not alone. These databases reach end of support on July 9, 2019, ......[(more)]( Software Development [] [The Bare Minimum You Need To Know To Work With Git]( - I don’t like using git for source control. It’s the worst source control system (except for all the others). My biggest beef is that many of the commands are unintuitive. Look how tricky some of these common use cases can be: Top Voted Stackoverflow ......[(more)]( Security news and thoughts [] [$1 million heist on Russian bank started with hack of branch router]( - A prolific hacking group has struck again, this time stealing close to $1 million from Russia’s PIR Bank. The July 3 heist came about five weeks after the sophisticated hackers first gained access to the bank’s ......[(more)]( [] [Installing a Credit Card Skimmer on a POS Terminal]( - Watch how someone installs a credit card skimmer in just a couple of seconds. I don't know if the skimmer just records the data and is collected later, or if it transmits the data back to some base station. ...[(more)]( [] [Timehop Breach Exposes 21 Million Email Addresses](37423.html) - A breach revealed the names and email addresses of 21 million Timehop users. More sensitive data was safe, though, because the company doesn't even collect it. ...[(more)]( R Language [] [Clean Your Data in Seconds with This R Function]( - All data needs to be clean before you can explore and create models. Common sense, right. Cleaning data can be tedious but I created a function that will help. The function do the following: Clean Data from NA’s and Blanks Separate the clean data ......[(more)]( [] [R 3.5.1 update now available]( - Last week the R Core Team released the latest update to the R statistical data analysis environment, R version 3.5.1. This update (codenamed "Feather Spray" — a Peanuts reference) makes no user-visible changes and fixes a few bugs. It is backwards-compatible ......[(more)]( Python in SQL Server [] [Using Python Pandas dataframe to read and insert data to Microsoft SQL Server]( - In the SQL Server Management Studio (SSMS), the ease of using external procedure sp_execute_external_script has been (and still will be) discussed many times. But the reason for this short blog post is the fact that, changing Python environments using ......[(more)]( Product Upgrades and Releases [] [Cumulative Update #9 for SQL Server 2017 RTM]( - The 9th cumulative update release for SQL Server 2017 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......[(more)]( [] [The July release of SQL Operations Studio is now available]( - We are excited to announce the July release of SQL Operations Studio is now available. Download SQL Operations Studio and review the Release Notes to get started. SQL Operations Studio is a data management tool that enables you to work with SQL Server, ......[(more)]( [] [Oracle Patches 334 Flaws in July Critical Patch Update]( - Oracle releases it largest Critical Patch Update, with security fixes for products across the company's portfolio. ...[(more)]( [] [Released: Public Preview for Version Agnostic SQL Server MP (2012-2017)]( - Adding Support for SQL Server 2012, 2014, 2016 Monitoring in the Version Agnostic SQL Server Management Pack We moved to version agnostic management packs with SQL Server 2017+ MP. That is, 2017+ MP is built to monitor SQL Server versions 2017 and up. ...[(more)]( [] [Public Preview of Table Variable Deferred Compilation in Azure SQL Database]( - Last year SQL Server 2017 and Azure SQL Database introduced query processing improvements that adapt optimization strategies to your application workload’s runtime conditions. These improvements included: batch mode adaptive joins, batch mode memory ......[(more)]( [] [Public Preview of Row Mode Memory Grant Feedback in Azure SQL Database]( - Last year SQL Server 2017 and Azure SQL Database introduced query processing improvements that adapt optimization strategies to your application workload’s runtime conditions. These improvements included: batch mode adaptive joins, batch mode memory ......[(more)]( Product Reviews and Articles [] [Data Masker for SQL Server and Temporal Tables]( - A customer had a question recently about using Data Masker for SQL Server and temporal (or system versioned) tables. I decided to make a quick demo that will show how this works. This is a simple demo, using just a few changes, but the concepts are useful ......[(more)]( [] [Spoofing Data Convincingly: Masking Continuous Variables.]( - In any real numeric data from a database , you are only rarely going to see any sort of normal distribution of the values. Sales data will rise and fall according to the time of year and the economic cycle. The date of input of a record will vary with ......[(more)]( [] [Is this a SQL Provision Cloned Database?]( - As I work with SQL Provision, I keep finding new questions and concerns from clients and customers. Recently I had someone wonder if we could determine whether or not a database on which they were working was a SQL Clone cloned copy. You can, and it’s ......[(more)]( [] [Scaling SQL Monitor to Large SQL Server Estates]( - Most organizations are finding that the size and number of databases that need to be monitored is increasing, unlike the number of people available to do the work. Now that it is possible for the organization to scale out to cloud-hosted databases, the ......[(more)]( [] [The ‘Right to be Forgotten’ and Data Masker for SQL Server]( - The right to be forgotten is one of the main features of new data protection legislation across the globe. Under Article 17 of one such piece of legislation in Europe, the GDPR, individuals have the right to have personal data erased from all systems ......[(more)]( [] [SQL Prompt code analysis: avoiding the old-style TOP clause]( - The syntax of the TOP clause insists on a bracketed, or parenthesized, expression as its first argument, so that TOP 34 should, strictly speaking be TOP (34), although if you are just supplying a numeric constant, such as 34, as an argument then either ......[(more)]( PowerShell [] [Figuring out a PowerShell version of Dynamic SQL]( - Next step is wondering if we can avoid SQL/PowerShell injection... ...[(more)]( [] [Start-DbaMigration]( - dbatools is such a fun toolset to work on, but specifically, I can no longer live without Start-DbaMigration. Even in smaller shops, migrations are often required and they are always a lot of work....[(more)]( PowerPivot/PowerQuery/PowerBI [] [Using Process Monitor To Find Out How Much Data Power Query Reads From A File]( - This post is really just a quick follow-on from my post earlier this week on using Process Monitor to troubleshoot Power Query performance issues with file-based data sources, which I suggest you read before carrying on. I realised, after playing around ......[(more)]( [] [Power BI 101 – Log Files and Tracing]( - Knowing where log files are and how to turn on debugging is an essential part of any technical job and this goes for Power BI, too. Remember, as I learn, so does everyone else….Come on, pretty please? Power BI Desktop Log files and traces can be accessed ......[(more)]( [] [Power BI Introduction: Working with SQL Server data in Power BI Desktop — Part 5]( - 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)]( [] [Power BI Custom Visuals Class (Module 111 – Horizontal Bar Chart)]( - In this module you will learn how to use the Horizontal Bar Chart. The Horizontal Bar Chart is similar to the native Bar Chart but with a few additional customizations that can be configured. Module 111– Horizontal Bar Chart Downloads Power BI Custom ......[(more)]( [] [Power BI Tutorial: Sort By A Measure Not Shown In Visual]( - In this Power BI tutorial, Patrick shows you how you can sort by a measure not show in a visual within Power BI Desktop. This was something we didn't even know about until recently and it a great little Power BI nugget. If you wanted to sort by something ......[(more)]( [] [Getting the Data to Power BI]( - Welcome to the next post regarding Power BI. This time, we will know Power BI from the more pleasurable side – that is, it will be practically and conveniently. Namely, we will deal with downloading data from Excel, the internet and of course SQL. Power ......[(more)]( [] [Power BI Row-Level Security And Where To Filter]( - In this video, Patrick looks at Power BI Row-Level Security And where to filter. He found that, after applying dynamic security, customers showed up in a slicer when they should not have. Challenge accepted! He walks through Power BI Desktop to show ......[(more)]( Performance Tuning SQL Server [] [A Quirk In Parallel Hash Join Plans]( - Bailing, Takes Me Away Both of these queries will return zero rows, in the 2010 version of the Stack Overflow database.SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON v.UserId = p.OwnerUserId WHERE v.CreationDate >= '2011-01-01'; SELECT ......[(more)]( [] [The Horror of Conflicting Database Collations]( - All these queries with all these JOINs on columns with mis-matched collation, lead to very sad pandas: RBAR operations (row-by-agonizing-row, unable to use index-seeks), and high CPU....[(more)]( [] [Trace Flag 7470]( - Trace flag 7470 works in SQL 2017! I'm always a little reluctant to use trace flags, especially after reading that Trace flags were becoming redundant due to more database level settings.Anyway, I've eliminated the Sort operator spilling to tempdb by ......[(more)]( [] [[Video] Interpreting Missing Index Recommendations]( - This week, we’re sharing Instant Replay videos from our training classes. Next up is Pinal Dave talking about missing index requests from the dynamic management views. It doesn’t matter whose script you use – they’re all built atop SQL Server’s missing-index ......[(more)]( [] [Index on Key vs Included]( - Indexing can be quite confusing at times if you not 100% sure on what to do. Do you just index on key or do you add included columns? Well this depends on what you are doing and what type of performance improvement you are looking for. Lets start with ......[(more)]( Microsoft News [] [Microsoft crosses $100 billion annual revenue mark for the first time]( - Microsoft officials say the company also closed the highest number of multi-million-dollar deals ever across its commercial cloud during its fiscal 2018. ...[(more)]( Internet of Things [] [GE and Microsoft Enter into their Largest Partnership to Date, Accelerating Industrial IoT Adoption]( - According to a new press release, “GE and Microsoft Corp. today announced an expanded partnership, bringing together operational technology and information technology to eliminate hurdles industrial companies face in advancing digital transformation ......[(more)]( Hardware News [] [Two Hard-to-Detect Spectre Flaws Found for Intel, Arm Processors]( - Two academics have uncovered two more Spectre flaws that seem to be "undetectable" by existing tools. ...[(more)]( [] [Samsung Announces First LPDDR5 DRAM Chip, Targets 6.4Gbps Data Rates & 30% Reduced Power]( - Samsung has been on a roll lately with memory & storage-related announcements, and that roll is continuing today with a new DRAM-related announcement out of the juggernaut. This afternoon the company is announcing that they have completed fabrication, ......[(more)]( [] [Intel’s High-End Cascade Lake CPUs to Support 3.84 TB of Memory Per Socket]( - While Intel has yet to detail its upcoming Cascade Lake processors for servers, some of the key characteristics are beginning to emerge. According to a new report from ServeTheHome, some of the new chips will support up to 3.84 TB of memory per socket, ......[(more)]( [] [Surface Go is Microsoft's Latest Effort to Grow Surface Brand]( - After rumors and leaks over the last several weeks, last night Microsoft unveiled their latest edition to the Surface family. The new device, Surface Go, introduces the Surface brand to a new market of buyers with an entry level price tag of $399 which ......[(more)]( [] [Apple Debuts Upgraded Pro Laptops Ahead of Fall Product Blitz]( - Apple released an upgraded MacBook Pro line Thursday that’s faster and has more features, targeting its loyal base of professional users like app developers and movie editors. ...[(more)]( HA/DR/Always On/Clustering [] [How to Test Disaster Recovery Before You Go Live]( - When I build a server, success means not touching the server again for 2-3 years. I already have enough crappy, unreliable servers that fall over when someone walks past. I only wanna build good, permanent stuff going forward. So when I build disaster ......[(more)]( [] [AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING]( - Recently a customer reported an interesting issue, while querying against recently added readable replica, SELECT statement is shown as suspended and session is shown as waiting on HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING Here is a simple way ......[(more)]( ETL/SSIS/ELT [] [Testing the loops in ssisUnit]( - In the Q & A post after the webinar on ssisUnit (in 2013) John Welch answered the question about the loops: “If possible, can you demo if a container can be executed? Especially a For loop or For Each loop?” I didn’t have time to demo this during the ......[(more)]( DevOps and Continuous Delivery (CI/CD) [] [The march of DevOps from the backroom to the boardroom]( - By encouraging collaboration and teamwork, removing the barriers between development and operations, and introducing automation, DevOps speeds up software delivery and enables features to move from the fingers of developers into the hands of customers ......[(more)]( [] [Making the database key to DevOps with comprehensive, customizable monitoring]( - The speed of business today demands that the development and deployment of applications is fast-moving, with frequent yet error-free releases. That’s why the adoption of DevOps is trickling down from Amazon, Facebook, Google and the other usual suspects ......[(more)]( [] [Why code quality is vital in the world of database DevOps]( - Everyone understands the importance of code quality for applications, particularly when DevOps results in releases becoming faster and faster, reducing the room for error. The same issues increasingly apply to databases, which are a vital part of DevOps ......[(more)]( [] [Databases and DevOps, Tools are the Easy Part]( - Moving your database development, deployment and management into a DevOps methodology does involve choosing and implementing tools and tooling. Tools are a necessary aspect of DevOps because, one of the fundamentals of implementing a DevOps approach ......[(more)]( Data Privacy and GDPR [] [Data collection and data markets in the age of privacy and machine learning]( - While models and algorithms garner most of the media coverage, this is a great time to be thinking about building tools in data.In this post I share slides and notes from a keynote I gave at the Strata Data Conference in London at the end of May. My ......[(more)]( [] [Provisioning Data Catalogs with Metadata: A Variable Challenge]( - Click to learn more about author Roland Bullivant. Data Catalogs are a fast-growing critical component of an Enterprise Information Management strategy. They provide a solution to document, and enable users to find and understand, how data is used across ......[(more)]( Data Mining/Data Analysis [] [Book Review – Factfulness by Hans Rosling]( - I recently bought the book Factfulness – Ten Reasons we’re wrong about the world – And why things are better than you think by Hans Rosland (with Ola Rosling and Anna Rosling Rönnlund). The book is a fascinating read and I recommend it to everyone. Not ......[(more)]( Conferences, Classes, and Events [] [Query Store Training – Portugal]( - I am so excited to announce that I am presenting a full day of Query Store Training, in-person, this September in Lisbon, Portugal! The SQLskills team will be in London for two weeks in September for a set of Immersion Events (IEPTO1, IEAzure, IECAG, ......[(more)]( [] [MN SQLSaturday #796 – Oct 6]( - This year’s MN SQLSaturday will be on October 6 at the same venue as last year, the Saint Paul College. This is a free training event for Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence, Database ......[(more)]( [] [What Do You Want to Learn This Fall?]( - New class survey We’re designing a new round of courses for the fall. Let us know what you want to learn in this short survey. After you take it, you get to see the answers, too, so you can learn what other folks wanna learn. What do you want to learn ......[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Building SQL ConstantCare®: What Cloud Hosting Costs]( - In our behind-the-scenes posts about building SQL ConstantCare®, I’ve written about how we picked serverless architecture for the application layer, and how picked AWS Aurora for the database layer. Hosting costs were a big driving factor in those decisions ......[(more)]( [] [Microsoft adds new SSD disk option to its Azure Data Box Disk appliance]( - Microsoft is adding previews of new Azure Box data-migration, Azure Virtual WAN and Azure Firewall services to its line-up. ...[(more)]( Career Growth [] [A Very Unproductive Day in the Life of Brent Ozar: July 18, 2018 #SQLCareer]( - Yesterday, I blogged what I did in a random work day per Steve Jones’ suggestion. And yesterday, I said I’d blog today as well because it was going to be a different schedule. You shouldn’t read this. It’s useless. It’s useless because as I look back ......[(more)]( [] [A Day in the Life of Brent Ozar: July 17, 2018 #SQLCareer]( - Steve Jones asked data professionals to cover four days in our lives, so here goes the first post: what I did on Tuesday, July 17th, 2018. Nothing special about this day, just the day that Steve prodded me to take part, heh. My days are radically different, ......[(more)]( [] [How to Future-Proof a Career in IT]( - Those that have spent their lifetime in IT are faced with either moving on or even finding a different career altogether. Here are some recommendations that can extend the life of the IT pro indefinitely. ...[(more)]( Backup and Recovery [] [Behavior Change for VSS Backups on VMs with Basic Availability Groups]( - This week we released SQL Server 2016 SP2 CU2 and SQL Server 2017 CU9. In this blog, I’d like to go into a little more detail on one of the fixes that is part of these CUs which involves the SQL Writer Service. If you are running SQL Server 2016 (or ......[(more)]( [] [SQL SERVER – Backup to URL – Script to Perform Stripped Backup using Shared Access Signature (SAS)]( - Here are my previous blogs about Backup to URL, I have share scripts to take backup using Access Keys and SAS Token. One of my blog readers wanted to take stripped backup to Azure Blob. In this blog, I am sharing the script to perform a stripped backup ......[(more)]( [] [RESTORE VERIFYONLY what does WITH LOADHISTORY do?]( - Recently someone mentioned the LOADHISTORY option of RESTORE. It’s not horribly hard to find in BOL but it wasn’t the easiest thing (at least for me) to understand at first. LOADHISTORY Supported by: RESTORE VERIFYONLY Specifies that the restore operation ......[(more)]( [] [SQL SERVER – Msg 3292: A Failure Occurred While Attempting to Execute Backup or Restore With a URL Device Specified]( - In my recent project with a customer, they wanted to configure SQL backups to Azure Blob Storage. When we were trying to take a backup, we were facing error. In this blog we would learn how to fix error message 3292 – A failure occurred while attempting ......[(more)]( Azure SQL Managed Instance [] [Storage performance best practices and considerations for Azure SQL DB Managed Instance (General Purpose)]( - Reviewed by: Kun Cheng, Borko Novakovic, Jovan Popovic, Denzil Ribeiro, Rajesh Setlem, Arvind Shyamsundar, Branislav Uzelac In this article, we describe database storage architecture on Azure SQL Database Managed Instance (MI), for General Purpose (GP) ......[(more)]( Azure SQL Data Warehouse and Data Lake [] [Data lakes and swamps, oh my]( - I was lamenting to my friend and fellow MVP Shamir Charania (blog | Twitter) that I didn’t have a topic for this week’s blog post, so he and his colleague suggested I write about data lakes, and specifically Azure Data Lake. What is a data lake? This ......[(more)]( Analysis Services / BI on the MS Stack [] [SQL or M? – SSAS Partitions]( - Part 1: Using SQL Server Table-Valued Functions (UDFs) In SQL Server Analysis Services projects, as of SQL Server Data Tools 2017, you can define table partitions using Power Query. Of course, we still have the option to use SQL Server database objects ......[(more)]( AI/Machine Learning/Cognitive Services [] [What Does It Mean to Certify an AI Product as Safe?]( - Click to learn more about author James Kobielus. Artificial intelligence (AI) scares many people. Personally, I think those worries are seriously overblown. But we should respect the general public’s need for meaningful assurances that the next AI-infused ......[(more)]( Administration of SQL Server [] [A New tempdb Trace Flag Appears!]( - Coming Around Microsoft has been doing a good job of taking stuff that used to be Wizard Hat and making it part of out of the box functionality. Starting with SQL Server 2016, you didn’t need to keep these in your setup checklist: Trace flag 1118 to ......[(more)]( [] [Restarting the SQL Service may fail occasionally with the error, “TCP port is already in use”]( - Today, I was working with one of my clients with an issue where the application fails to connect to SQL server. The issue was encountered with the restart of the SQL services. The initial connections go through but within a very short duration the connections ......[(more)]( [] [Schedule an Extended Event to run at a Specific Time]( - When you are using the extended event to capture some data for troubleshooting purpose, you may want to start or stop it at a specific time. Unfortunately, the Extended Event feature doesn’t provide you the option to schedule it. But, it can easily be ......[(more)]( [] [SQL Server system databases 101]( - In this post I want to give a basic explanation of what system databases are and things we can or cannot do with them It was the other day during the networking time in my SQL User group meeting when someone told the story of the new DBA that was joking ......[(more)]( [] [Monitoring Space Used by Query Store]( - Last week I presented a session on Query Store and when talking about the settings I mentioned that monitoring space used by Query Store is extremely important when you first enable it for a database. Someone asked me how I would do that and as I provided ......[(more)]( [] [Log Backups, Auto Growth Settings, & Alerts (Part 1)]( - Why does missing a few log backups matter? I was asked that question last week when one of our backup repositories was offli