SQL Server 2017, Power BI and a Broken View Finder in Database Weekly (10/9/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Virtualization and Containers - [What if the VM Memory Config Exceeds the Memory Capacity of the Physical NUMA Node?](#30105) - [Limiting resources available to containers](#30094) Vendors/3rd Party Products - [Compound FKs with SQL Data Generator](#30098) T-SQL - [How Far Has My Update Got?](#30119) - [Broken View Finder](#30112) - [The Alpha and the Omega. The BEGIN and the END.](#30099) - [Love and Hate for SSMS](#30095) - [Regular Expressions With R And T-SQL](#30089) - [Why You Can’t Use ROW_NUMBER() In Your WHERE Clause](#30086) - [SQLskills SQL101: Running out of ints and bigints](#30083) - [An Adaptive Join Regression](#30082) - [Migrating to Native Scoring with SQL Server 2017 PREDICT](#30079) - [How to record T-SQL execution times using a SQL Prompt snippet](#30078) SQL Server Security and Auditing - [Setting up Change Data Capture (CDC)](#30101) SQL Server on Linux - [Unattended install and configuration for SQL Server 2017 on Linux](#30081) SQL Server News - [SQL Updates Newsletter – September 2017](#30106) - [SQL Server 2012 Service Pack 4 (SP4) is now available!](#30096) - [SQL Server 2017 on Windows Linux and Docker is now generally available](#30090) Software Development - [The Curious Case of the Longevity of C](#30108) Reporting Services - [SQL Server 2017 Reporting Services now generally available](#30110) R Language - [SQL Server R Services: Generating Sparklines and Other Types of Spark Graphs](#30075) PowerShell - [PowerShell Classes Part 5 — Classes or PSCustom Objects?](#30113) PowerPivot/PowerQuery/PowerBI - [Why data modeling is important in Power BI](#30111) - [Monitoring SQL Server with Power BI](#30109) - [Avoiding circular dependency errors in DAX](#30104) - [What is Power BI Embedded?](#30102) - [Using Variables in DAX](#30084) Performance Tuning SQL Server - [IO Patterns for Index Seeks: There May be a Lot of Read Ahead Reads](#30097) Microsoft News - [Why it’s time to think seriously about SQL Server 2017](#30073) Hardware Testing - [Best CPUs for Workstations 2017](#30117) - [The AnandTech Coffee Lake Review: Initial Numbers on the Core i7-8700K and Core i5-8400](#30116) HA/DR/Always On/Clustering - [Connecting to Named Instances without using the Instance Name](#30087) DevOps and Continuous Delivery (CI/CD) - [How Can DevOps Concepts Provide Value in Digital Transformation Projects?](#30076) - [Can the database be included in DevOps in financial services?](#30074) Data Science - [Data Science 101: Sentiment Analysis in R Tutorial](#30118) Data Mining/Data Analysis - [Understanding the Division of Labor between Analytics Applications and DBMS](#30093) Computing in the Cloud (Azure, Google , AWS) - [Picking the right Azure VM size using Load Testing](#30107) - [An introduction to Azure Web Apps on Linux](#30077) Career Growth - [How to Understand Where a Software Vendor Employee is Coming From](#30092) Analysis Services / BI on the MS Stack - [SSAS Multidimensional HOLAP Storage: Even More Useless Than You Might Think](#30103) - [Analysis Services Innovations in SQL Server 2017](#30085) AI/Machine Learning/Cognitive Services - [How to Build and Deploy Scalable Machine Learning in Production with Apache Kafka](#30091) Administration of SQL Server - [Bad Idea Jeans: Finding Undocumented Trace Flags](#30115) - [No More SQL Server Service Packs: Is CU12 the New SP1?](#30114) - [Setting a Processor Affinity in SQL Server – the (unwanted) Side-Effects](#30100) - [SQL Server – Pause VS Stop](#30088) - [Changes to Service Packs and Cumulative Updates for SQL Server 2017](#30080) [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 2017-10-09 [ReadyRoll]( Database migrations inside Visual Studio Want to work on SQL Server databases in Visual Studio alongside your application? Use Redgate ReadyRoll to develop & deploy databases using SQL migration scripts. [Try it free]( [Foundry]( Using SQL Census to audit SQL Server permissions Redgate have just released SQL Census, a prototype tool that makes auditing SQL Server user access permissions much easier. In this post, Ally Parker shows how it works, tells us what's up next in the tool's development, and explains how you can download it for free. [Try the free prototype]( [Redgate Hub]( The best career move you can make On the new Redgate Hub, we’ve gathered together a wealth of information to help you solve problems, share ideas and discover the latest insights. Whatever you need to know about to advance your career, the answer is probably right here. [Move to the Redgate Hub]( Editorial - The Achilles Heel When a company experiences a data breach, or a catastrophic IT failure, there are often some pathetic and unedifying manoeuvres by the senior management to deflect blame onto a single subordinate or team. It is ridiculous to attempt to convince the public that this is a viable explanation. If IT management has done its job properly, there will be a strategy in place that dictates that resilience or security is layered, meaning that even if someone accidentally fails to maintain one line of defence, there are still several other lines. Besides, any successful intrusion, or node-failure, will surely be detected? I can't remember a single catastrophic failure or breach that hasn't been due to a whole plethora of slack and ignorant practices. It is true that, in many cases, one can pinpoint the moment when all was lost. For example, that developer should never have used the placeholder 'Dear Rich Bastard' when testing the new banking software. The Ops guy shouldn't have switched the UPS for the production server off and on again. The developer should have created a new feature switch rather than reuse an old one. The web developers shouldn't have created SQL Queries by concatenating strings from an input form. However, in every case, that was just the tipping point, within the context of a security or resilience architecture that was rotten. When things go wrong in engineering, architecture or software, we can learn a great deal from a frank and meticulous autopsy. Sadly, we find that, unlike other industries, we only get the details of IT failures when matters go to court or public inquiry. This makes it harder to win the battle against the cyber-criminal. This information is educational. In much the same way that airline pilots pore over the accounts of safety incidents, it pays dividends to understand exactly how IT security breaches happen. I wish that there was more of this information in the public domain, but there is enough to rid yourself of any complacency. Security and resilience have to be an intrinsic part of IT culture. In my own work in auditing database systems, I never came across an incident where a system had failed due to a single 'Achilles Heel', or even a single team of people. A well-run IT department has procedures and policies that prevent the classic mistakes made by people under pressure from causing any more than a temporary inconvenience. They also prevent villainy and malignance. It is only when IT Departments fail in promoting a culture that recognises the importance of this, and doesn't allocate resources to plan and implement this properly, that the bad things that sometimes happen in IT result in catastrophe rather than mere embarrassment. Phil Factor. Phil Factor 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. Virtualization and Containers [] [What if the VM Memory Config Exceeds the Memory Capacity of the Physical NUMA Node?]( - The monster VM with a particular memory configuration that exceeds the ESXi host NUMA node memory configuration....[(more)]( [] [Limiting resources available to containers]( - Showing how simple it is to limit the resources available to containers, to avoid having one container consume all the resources on your host....[(more)]( Vendors/3rd Party Products [] [Compound FKs with SQL Data Generator]( - How is data generated with SQL Data Generator and foreign keys. In this case, the person was having an issue with a compound foreign key and Steve Jones helps out....[(more)]( T-SQL [] [How Far Has My Update Got?]( - A script that will script will dive into the transaction log and return you the number of rows that have been modified, inserted or deleted by the SPID that you plug into @SPID....[(more)]( [] [Broken View Finder]( - Recently, we were asked to migrate some code changes and after doing so, the requesting team told us we had broken all of their views, but they couldn't tell us what was broken, just that everything was. After a quick rollback to snapshot, thank you Red Gate SQL Compare, I thought it'd be enlightening to see whether anything was broken before our code had been deployed....[(more)]( [] [The Alpha and the Omega. The BEGIN and the END.]( - A BEGIN...END primer. The trick is to remember that your IF, WHILE, etc only execute one statement. So if you want to do more than that you need the BEGIN and the END....[(more)]( [] [Love and Hate for SSMS]( - People have strong feelings about SQL Server Management Studio: they love it AND they hate it. In this week’s episode, I talk about why people have such conflicting feelings about SSMS, and how to work it all out....[(more)]( [] [Regular Expressions With R And T-SQL]( - Dave Mason has finally found a tangible example of something he can use R for....[(more)]( [] [Why You Can’t Use ROW_NUMBER() In Your WHERE Clause]( - “I want to see the oldest amount due for each account, along with the account number and due date, ordered by account number.”...[(more)]( [] [SQLskills SQL101: Running out of ints and bigints]( - Paul Randal is often asked about using an int identity as a clustering key and the possibility of running out of integers. One solution is to use a bigint identity as the key. This can store 2^64 or about 18.5 quintillion (18.5 billion billion) values, between -2^63 and 2^63-1....[(more)]( [] [An Adaptive Join Regression]( - Joe Obbish is curious as to why Microsoft made the adaptive join tipping point so dependent on cardinality estimates going into the join....[(more)]( [] [Migrating to Native Scoring with SQL Server 2017 PREDICT]( - PREDICT should make predictions much faster as the process avoids having to marshal the data between SQL Server and Machine Learning Services. However, migrating from the original sp_execute_external_script approach to the new native approach tripped me up so I thought I'd share a quick summary of what I have learned....[(more)]( [] [How to record T-SQL execution times using a SQL Prompt snippet]( - Phil Factor shares the SQL Prompt snippet he uses as a standard testbed for getting execution times for procedures and functions....[(more)]( SQL Server Security and Auditing [] [Setting up Change Data Capture (CDC)]( - CDC can be a useful tool for capturing a change history for specific data – which is something we might be thinking about a bit more now the GDPR is heading our way....[(more)]( SQL Server on Linux [] [Unattended install and configuration for SQL Server 2017 on Linux]( - The SQLCAT team share a sample script that will work across multiple supported platforms, and further ease the unattended install process for SQL Server on Linux....[(more)]( SQL Server News [] [SQL Updates Newsletter – September 2017]( - Lots and lots of news from Ignite, plus issue alerts, recent blogs, and more....[(more)]( [] [SQL Server 2012 Service Pack 4 (SP4) is now available!]( - This release of SQL 2012 Service Pack has 20+ improvements centered around performance, scalability and diagnostics based on feedback from customers and the SQL Server community....[(more)]( [] [SQL Server 2017 on Windows Linux and Docker is now generally available]( - Travis Wright, Principal Program Manager, SQL Server Engineering summarizes the 'big ticket' new features of SQL Server 2017....[(more)]( Software Development [] [The Curious Case of the Longevity of C]( - As a programming language, C has an essential simplicity, even if in practice this readily translates to “Be scared. All of the time”. Despite this I do find great pleasure of writing in C, to get something elegant working fast and safe is a reward other languages fail to give....[(more)]( Reporting Services [] [SQL Server 2017 Reporting Services now generally available]( - Offering a lightweight installer, to a modern REST API, to an updated Report Viewer control and web part, and more....[(more)]( R Language [] [SQL Server R Services: Generating Sparklines and Other Types of Spark Graphs]( - By being able to run R from SQL Server, you have available to you not just a convenient way of performing analysis on data but also a wide range of more specialised graphical facilities. Rob Sheldon illustrates the point by demonstrating how you can create sparklines that can then be embedded in reports....[(more)]( PowerShell [] [PowerShell Classes Part 5 — Classes or PSCustom Objects?]( - This article explores a traditional PowerShell programming approach using a custom object and compares it to an object-oriented approach using classes....[(more)]( PowerPivot/PowerQuery/PowerBI [] [Why data modeling is important in Power BI]( - The question is: what is the right data model for the business analysis? The first simple answer is: the model should be designed to answer business questions, and not to simply represent the data as they come from the data source....[(more)]( [] [Monitoring SQL Server with Power BI]( - Dennes Torres shows how we can import query store information into power bi and create a very useful dashboard to monitor SQL Server. ...[(more)]( [] [Avoiding circular dependency errors in DAX]( - This article explains how DAX handles dependencies between tables, columns and relationships, to help you avoid circular dependency errors....[(more)]( [] [What is Power BI Embedded?]( - In this video, Adam walks through what Power BI Embedded is. He talks about how you can get started with Power BI Embedded as well as what resources are available for you....[(more)]( [] [Using Variables in DAX]( - Matt Allington describes some of the benefits of using variables in DAX, then offers an example of how their use improved the performance of one of his formulas....[(more)]( Performance Tuning SQL Server [] [IO Patterns for Index Seeks: There May be a Lot of Read Ahead Reads]( - We tend to think of an index seek as a small, efficient retrieval of a few pages. It “seeks” straight there. But this isn’t necessarily the case. Seeks can read quite a large amount of data — even all the data in a table if it meets the criteria for the key that I’m seeking on....[(more)]( Microsoft News [] [Why it’s time to think seriously about SQL Server 2017]( - SQL Server 2017 has officially landed, and is now on general release. The latest version of the heavyweight platform is more than the sum of its parts, however, because it doesn’t just deliver new functionality. Alongside the list of extra features, it also changes two important ways we think about the platform itself....[(more)]( Hardware Testing [] [Best CPUs for Workstations 2017]( - In our series of Best CPU guides, here’s the latest update to our recommended workstation CPUs list. All numbers in the text are updated to reflect pricing at the time of writing (02-Oct). Numbers in graphs reflect MSRP....[(more)]( [] [The AnandTech Coffee Lake Review: Initial Numbers on the Core i7-8700K and Core i5-8400]( - It has been ten years since Intel introduced quad-core processors into its mainstream product range. It was expected that six-core parts would hit the segment a few years after, however due to process improvements, microarchitecture gains, cost, and a lack of competition, the top-end mainstream processor is still a quad-core a decade later. That changes today....[(more)]( HA/DR/Always On/Clustering [] [Connecting to Named Instances without using the Instance Name]( - How does failover from a primary to a secondary SQL Server work for a named instance, when the named instance is in the connection string, and the instances on the primary and secondary have different names?...[(more)]( DevOps and Continuous Delivery (CI/CD) [] [How Can DevOps Concepts Provide Value in Digital Transformation Projects?]( - For some of us, DevOps means Startups, Cloud, fast-moving social-media applications and Extreme Programming (XP). What about large corporate IT initiatives, Digital Transformation projects, and business process re-engineering? Can Devops be relevant and appropriate? Mohammad Rizvi argues from experience that it most certainly can be....[(more)]( [] [Can the database be included in DevOps in financial services?]( - For many Database Administrators (DBAs) who work in financial services, deploying database changes is the most taxing part of your job. You're likely required to review thousands of lines of script and it can take days, depending on how many errors you find. Even when database deployments are better planned, it’s not an easy journey, and DBAs are often regarded as the blocker in the process, the ones who are causing the problem rather than trying to resolve it....[(more)]( Data Science [] [Data Science 101: Sentiment Analysis in R Tutorial]( - Do you have text data? Do you want to figure out whether the opinions expressed in it are positive or negative? Then you've come to the right place! ...[(more)]( Data Mining/Data Analysis [] [Understanding the Division of Labor between Analytics Applications and DBMS]( - We have instructions on how to do "analytics with SQL", and then tools purporting to enable "analytics without SQL." They are an umpteenth iteration of essentially similar ideas during my 30-plus years in data management and reflect common and entrenched fundamental misconceptions about data fundamentals....[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Picking the right Azure VM size using Load Testing]( - The major cloud providers have essentially become commodities, especially with IaaS, but that doesn't mean every VM configuration is the same across the providers. Bob Walker describes how to configure correctly-sized VMs, using a series of load tests after building out the environment. ...[(more)]( [] [An introduction to Azure Web Apps on Linux]( - Azure App Service has now got more versatile because it can now run Linux. Not only can you now run Web App for Containers, and publish Docker containers to Azure, but there is built-in support for ASP.NET Core, Node.js, PHP and Ruby on Linux. This allows you host microservicess on a fully-managed platform. Christos Matskas explains....[(more)]( Career Growth [] [How to Understand Where a Software Vendor Employee is Coming From]( - Brent Ozar has had a lot of conversations with software vendor employees over the years, and has noticed a few things. Understanding these will help you parse what you’re hearing....[(more)]( Analysis Services / BI on the MS Stack [] [SSAS Multidimensional HOLAP Storage: Even More Useless Than You Might Think]( - In almost 20 years of using Analysis Services, Chris Webb has never used HOLAP storage. However, surely it could help improve query performance for a customer that was using ROLAP storage on top of Exasol? Sadly not....[(more)]( [] [Analysis Services Innovations in SQL Server 2017]( - Summarizing the new features of the latest SSAS release, including the ability to define a custom row set contributing to a measure value, object level security and more....[(more)]( AI/Machine Learning/Cognitive Services [] [How to Build and Deploy Scalable Machine Learning in Production with Apache Kafka]( - Potential use cases for machine learning in real time applications, using Apache Kafka as the central nervous system and its Streams API to build intelligent streaming applications....[(more)]( Administration of SQL Server [] [Bad Idea Jeans: Finding Undocumented Trace Flags]( - Brent Ozar shows how to go searching for undocumented trace flags. Don't ever run this on a server you care about...[(more)]( [] [No More SQL Server Service Packs: Is CU12 the New SP1?]( - Starting with yesterday’s release of SQL Server 2017, Microsoft has a new servicing model: they’re only delivering Cumulative Updates, and not doing Service Packs....[(more)]( [] [Setting a Processor Affinity in SQL Server – the (unwanted) Side-Effects]( - What is Processor Affinity, how it relates to SQL Server, and why setting it might have unwanted side effects....[(more)]( [] [SQL Server – Pause VS Stop]( - Use pause when you want to wait for users to complete work before you stop the service. This enables them to complete transactions that are in progress...[(more)]( [] [Changes to Service Packs and Cumulative Updates for SQL Server 2017]( - Randolph West explains the new 'rapid servicing model' for SQL Server 2017, replacing the old Service Packs plus Cumulative Update model....[(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. --------------------------------------------------------------- This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com