T-SQL FizzBuzz (Database Weekly 5/22/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Virtualization and Containers - [SQL Server on Docker with new Environment Variables](#29053) - [Using Docker in production](#28995) Vendors/3rd Party Products - [Extending DevOps practices to SQL Server databases](#28994) - [Masking your on-premise database with SQL Data Mask](#28987) T-SQL - [T-SQL FizzBuzz](#29056) - [TIL: Joining across databases with different collations](#29046) Tools - [Do you use Visual Studio Code?](#28985) SQL Server Security - [Create a Whitelist for SQL Server - UPDATED!!!](#29027) - [Guidance for WannaCrypt/WannaCry Attacks](#29026) SQL Server on Linux - [Migrate an SQL database from Windows to Linux using backup-and-restore and SMB Protocol](#29057) SQL Server News - [SQL Server Command Line Tools for macOS released](#29030) - [SQL Server 2017 CTP 2.1 now available](#29029) - [Cumulative Update #9 for SQL Server 2012 SP3](#29022) - [Cumulative Update #3 for SQL Server 2016 SP1](#29021) - [Cumulative Update #6 for SQL Server 2016 RTM](#29020) - [ODBC Driver 13.1 for macOS released](#28982) - [Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 5: Consistent data environment across hybrid cloud environments](#28981) SQL Server Internals - [SQLskills SQL101: Why is restore slower than backup](#29025) Software Development - [How do I build an API?](#28997) - [What is an API?](#28996) - [Trying GO with SQL Server](#28971) Security news and thoughts - [Don't tell people to turn off Windows Update, just don't](#29018) - [Everything you need to know about the WannaCry / Wcry / WannaCrypt ransomware](#29017) - [Keylogger Found in HP Laptop Audio Drivers](#29003) - [WannaCry Ransomware](#29002) - [Huge, Unprecedented Ransomware Attack Hits Around the Globe](#28983) R Language - [Pretty scatter plots with ggplot2](#29045) - [AzureDSVM: a new R package for elastic use of the Azure Data Science Virtual Machine](#29042) - [Analyzing the home advantage in English soccer, with R](#28974) Product Reviews and Articles - [Masking your on-premise database with SQL Data Mask](#29036) Press Releases - [TimeXtender’s Discovery Hub® Now Available in the Cloud](#28968) PowerShell - [Pester for Presentations – Ensuring it goes ok](#29058) - [PowerShell Remoting Basics](#29001) PowerPivot/PowerQuery/PowerBI - [Power BI Report Server–Trial](#29049) - [Identifying Number of Cluster in K-mean Algorithm in Power BI: Part 7](#29043) - [BI Beginner: Simple X-Y Plot](#29013) - [BI Beginner: Stacked Charts in Power BI](#29012) - [A closer look at Power BI Report Server](#29007) - [Deploying, Securing, and Updating Power BI Reports](#29004) - [Deploying, Securing, and Updating Power BI Reports](#28990) - [Power BI Premium. Is It For You or Not?](#28969) Performance Tuning SQL Server - [Knowing the Options](#29047) - [Weaning yourself off of SQL Profiler (Part 2)](#29044) - [SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions](#29035) - [Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?](#29033) - [Troubleshooting CPU Performance on VMware](#29016) - [Automatic plan correction in SQL Server 2017](#29009) - [What Do You Do When Performance Sucks?](#29008) - [Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint](#28980) - [SQL Server 2017: Interleaved Execution for MSTVFs](#28979) - [SQL Server 2017 Showplan enhancements](#28977) Microsoft News : General Interest - [Microsoft to Open Africa Data Centers to Seek Edge in Cloud Push](#29031) - [Story Remix Bridges The Old And The New Microsoft](#28991) Microsoft News : Patches, Bugs - [In Rare Move, Microsoft Offers Critical Security Patch For Windows XP, 8, Other OSes](#28992) LINQ/Entity Framework - [High Performance PowerShell with LINQ](#28988) Internet of Things - [Microsoft: Government Should Regulate IoT Security](#29032) - [Google Launches Cloud Service For Managing Internet of Things Devices](#29000) - [IoT Platform Maker infiswift Aims to Modernize Farming](#28999) - [The stages of enterprise IoT adoption](#28998) Hardware News - [WD Elevates Red, Red Pro HDDs To 10TB With Helium](#29019) - [Western Digital: Sales of Helium-Filled HDDs Accelerating, 15M Sold So Far](#28978) HA/DR/Always On/Clustering - [SQL SERVER – FIX: Msg 35250, Level 16, State 7 – The Connection to the Primary Replica is Not Active. The Command Cannot be Processed](#29023) ETL/SSIS/ELT - [Passively Mine SSIS Data Flow Rows Loaded From the SSIS Catalog](#29011) DevOps and Continuous Delivery (CI/CD) - [The language of DevOps ROI](#29037) - [Continuous Database Deployment with Confidence](#29005) - [Continuous Database Deployment with Confidence](#28989) - [The language of DevOps ROI](#28986) Database Design, Theory and Development - [To Really Understand Integrity, Don't Start with SQL](#28970) Data Visualisation - [Setting Up InfluxDb, Chronograf, and Grafana for the SqlServer Dev](#29055) Conferences and Events - [New class: Immersion Event on Clustering and Availability Groups](#29024) Computing in the Cloud (Azure, Google , AWS) - [TIL: Microsoft Azure Part 1](#29052) - [5 Common Challenges to Building BI in the Cloud](#29048) - [Azure Resource Manager simply allows you to configure and implement your infrastructure with a JSON Template.](#29040) - [Building a Faux PaaS, Part 2: Choosing and Testing a Cloud Vendor](#29034) - [In The Cloud: The Importance of Being Organized](#29014) Azure SQL Database - [Automatic index management in Azure SQL database](#29010) Azure SQL Data Warehouse and Data Lake - [SQL Data Warehouse reference architectures](#29054) - [Azure SQL Data Warehouse loading patterns and strategies](#29015) Azure Analysis Services - [Building an Azure Analysis Services Model on Top of Azure Blob Storage—Part 1](#28975) Analysis Services / BI on the MS Stack - [SSAS Multidimensional: Are Your Indexes Processed?](#29059) - [Analysis Service 2016 Model gets corrupted if you add unsupported parameter while configuring the data source](#28976) AI/Machine Learning/Cognitive Services - [Why Machine Learning Can Improve Customer Service](#29039) - [Why Automation and AI are Cool, Until They're Not](#29038) - [How AI is used to infer human emotion](#28993) Administration of SQL Server - [Setting SQL Server Max Memory Dangerously Low](#29051) - [Implementing Buffer Pool Extension](#29050) - [SQL Server offline after applying service pack](#29041) - [Try new SQL Server command line tools to generate T-SQL scripts and monitor Dynamic Management Views](#29028) - [Troubleshooting CDC enabling failure – Part 1](#29006) - [SQL SERVER – Fix: The EXECUTE Permission was Denied on the Object ‘xp_cmdshell’, Database ‘mssqlsystemresource’,schema ‘sys’.](#28984) - [SQL Server 2017 sys.dm_os_enumerate_fixed_drives](#28973) - [Why shouldn’t I shrink my database log file?](#28972) [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-05-22 [SQL Prompt]( Could your SQL coding be more efficient? Data Platform MVPs and SQL Server experts share their tips on how to standardize formatting whilst stripping out the repetition of coding with SQL Prompt. Over these 20 short videos we will help you write better, shareable SQL faster. [Check out the tips.]( [Database DevOps](www.red-gate.com/dlmdemo?utm_source=dbw&utm_medium=pubemailad&utm_content=devops-webinar&utm_campaign=databasedevops&utm_term=20170520-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=dbw&utm_medium=pubemailad&utm_content=devops-webinar&utm_campaign=databasedevops&utm_term=20170520-slot2) [SQL Monitor]( New SQL Monitor Reporting Module SQL Monitor now includes a new Reporting module. It allows you to create customized reports, combining the individual server performance metrics available in SQL Monitor with summary information on your entire SQL Server estate. These reports can then be exported to pdf and emailed to you on a scheduled basis. [Download SQL Monitor now and get a 14 day free trial.]( Editorial - Security and Patching Struggles A couple weeks ago we had a huge security issue with the WannaCry ransomware racing across the world. I was out of the office, and mostly offline, so I was a bit out of touch. However, many organizations were affected, and I'm sure many infrastructure people were scrambling to patch and protect vulnerable systems, possibly even restore affected systems. I certainly hope that most (or all) organizations didn't pay any ransoms. For the future, my view is it's better to lose a little data and restore systems than pay ransoms. I can remember when we got patches at random times, as vendors wanted to provide more functionality or finally had enough bugs fixed to release a patch. Sysadmins struggled to deploy these patches, being out of practice from the infrequency and struggling with non-standardized ways of applying updates. There was also the concern about the quality of patches, many of which introduced more bugs and issues. In the Windows world, I found many companies wanting to wait until an SP2 was released before applying, or even upgrading, many systems. That changed, with many companies moving to regular patches, and standard ways of applying, or even slipstreaming, patches easily onto machines. I welcomed the Microsoft Patch Tuesdays, as this provided a regular release, an expectation, and both admins and users became comfortable with the idea of regular patches. I haven't loved the auto patches in Windows 10, but I find myself agreeing with Troy Hunt that [we should just be patching](. In an organization you may want someone to be responsible, but for home users, just patch. And, by the way, vendors, you need to do more work, and be more responsive to any issues that come up from patches. Your quality issues lead to greater security issues. If you want to ready about the WannaCry issue, there's [a good general post](, as well as [some guidance from the SQL Server perspective](. If you haven't patched, that's something you should do ASAP, and while you're at it, be sure you have the latest security patches for SQL Server applied. I've got a series of [Build Lists at SQLServerCentral](List/71065/), one for each version. I'm still nervous about applying Cumulative Updates too quickly, but I certainly would download and have them ready, perhaps applying each a month late once once the early adopters have had a chance to report any major problems. Patching is a reality for the modern software world. We get regular patches for applications, but our core infrastructure (including servers, desktops, and various devices) also need patches to the OS and platforms. There's a balance between ensuring stability with known softwre versions and keeping up with patches to prevent problems. We need to find a balance, which is probably different for each organization, and re-evaluate periodically if we are updating an an appropriate level. I lean towards fewer patches when I can, but I always want to keep up with security patches. We never know when someone will take advantage of those. 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. Virtualization and Containers [] [SQL Server on Docker with new Environment Variables]( - As of CTP 2.1 for SQL Server 2017 a set of new environment variables are available. These variables allow us to configure each SQL Server container as we spin them up. The first version of SQL Server on Linux came with: ACCEPT_EULA SA_PASSWORD These ......[(more)]( [] [Using Docker in production]( - Five questions for Laura Frank about orchestration, security, and beyond.I recently sat down with Laura Frank, Docker Captain and director of engineering at Codeship, to discuss the evolution of the Docker ecosystem and how it compares to other orchestration ......[(more)]( Vendors/3rd Party Products [] [Extending DevOps practices to SQL Server databases]( - In this free demo webinar, Steve Jones and Arneh Eskandari will show how Redgate’s database DevOps solution works to improve your database development and deployment processes. With a focus on using Team Foundation Server (TFS) work items, the webinar will demonstrate how Redgate tools plug into TFS, TFS Build, and Microsoft Release Management....[(more)]( [] [Masking your on-premise database with SQL Data Mask]( - Redgate's latest prototype tool is rapidly developing. Using SQL Data Mask, you can now mask on-premise SQL Server and Azure SQL databases. Here's how it works, and how you can currently use it for free....[(more)]( T-SQL [] [T-SQL FizzBuzz]( - I was looking around for something to write about this evening and came across one of Russ Thomas’ (b/t) old monthly challenges (Feb 2016). The challenge is two-fold. A FizzBuzz problem is a common programming interview challenge that asks a coder to ......[(more)]( [] [TIL: Joining across databases with different collations]( - Quick post today… Yesterday I was approached by a developer with something it has never occurred to me to try in 18 years of being a DBA. “Can I write a query across two databases with different collations?” My first thought took me back to SQL 7, when ......[(more)]( Tools [] [Do you use Visual Studio Code?]( - Visual Studio Code is rapidly gaining in popularity, but is it all it could be, or is there room for improvement? Redgate is embarking on some research to better understand how you are using this lightweight editor, and where it can be improved. Have your say by filling in this short survey!...[(more)]( SQL Server Security [] [Create a Whitelist for SQL Server - UPDATED!!!]( - A while back, I posted an article about creating a WhiteList for access to SQL Server. Since then I have received a bit of feedback that it was not working as designed. My apologies for taking so long, but I believe I have come up with a fix.The main ......[(more)]( [] [Guidance for WannaCrypt/WannaCry Attacks]( - There has been quite a bit of media coverage about the WannaCrypt/WannaCry ransomware over the past several days. Microsoft has a new page with information about this particular issue and steps that can be taken to protect your systems. I have also collected ......[(more)]( SQL Server on Linux [] [Migrate an SQL database from Windows to Linux using backup-and-restore and SMB Protocol]( - In an attempt decouple systems, we may modularize them. In most cases, we would need to move databases from one server to another—sometimes, even move them from Windows to Linux. In the case of moving a database to Linux, SQL Server’s Backup and Restore ......[(more)]( SQL Server News [] [SQL Server Command Line Tools for macOS released]( - This post was authored by Meet Bhagdev, Program Manager, Microsoft We are delighted to share the production-ready release of the SQL Server Command Line Tools (sqlcmd and bcp) on macOS El Capitan and Sierra. The sqlcmd utility is a command-line tool ......[(more)]( [] [SQL Server 2017 CTP 2.1 now available]( - Microsoft is excited to announce a new preview for the next version of SQL Server (SQL Server 2017). Community Technology Preview (CTP) 2.1 is available on both Windows and Linux. In this preview, we added manageability features to make it easier to ......[(more)]( [] [Cumulative Update #9 for SQL Server 2012 SP3]( - The 9th cumulative update release for SQL Server 2012 SP3 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 #3 for SQL Server 2016 SP1]( - The 3rd 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)]( [] [Cumulative Update #6 for SQL Server 2016 RTM]( - The 6th cumulative update release for SQL Server 2016 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)]( [] [ODBC Driver 13.1 for macOS released]( - This post was authored by Meet Bhagdev, Program Manager, Microsoft Hi, all. We are delighted to share the production-ready release of the Microsoft ODBC Driver 13.1 for macOS El Capitan and Sierra. The new driver enables access to SQL Server, Azure SQL ......[(more)]( [] [Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 5: Consistent data environment across hybrid cloud environments]( - Have you ever seen a tree that simultaneously bears completely different species of fruit? It’s a real thing: apples, plums, oranges, lemons, and peaches all growing on the same tree. The growers have the advantage of a consistent environment (the same ......[(more)]( SQL Server Internals [] [SQLskills SQL101: Why is restore slower than backup]( - As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there ......[(more)]( Software Development [] [How do I build an API?]( - Learn how to build both web and traditional application programming interfaces (APIs). Continue reading How do I build an API?. ...[(more)]( [] [What is an API?]( - Learn the basics of application programming interfaces (APIs), their purpose, and the key concepts underlying the technology. Continue reading What is an API?. ...[(more)]( [] [Trying GO with SQL Server]( - Not the batch separator, but the GO language. I’m always interested in learning new things, and GO has been one of those items that I’ve wanted to look at and see how it works. This is a big language at Google and I saw a new blog from Mat Hayward-Hill ......[(more)]( Security news and thoughts [] [Don't tell people to turn off Windows Update, just don't]( - Sponsored by: Netsparker - Scan your websites & detect SQL Injection, XSS and other vulnerabilities with the dead accurate Netsparker web security scannerYou know what really surprised me about this whole WannaCry ransomware problem? No, not how quickly ......[(more)]( [] [Everything you need to know about the WannaCry / Wcry / WannaCrypt ransomware]( - Sponsored by: Netsparker - Scan your websites & detect SQL Injection, XSS and other vulnerabilities with the dead accurate Netsparker web security scannerI woke up to a flood of news about ransomware today. By virtue of being down here in Australia, ......[(more)]( [] [Keylogger Found in HP Laptop Audio Drivers]( - This is a weird story: researchers have discovered that an audio driver installed in some HP laptops includes a keylogger, which records all keystrokes to a local file. There seems to be nothing malicious about this, but it's a vivid illustration of ......[(more)]( [] [WannaCry Ransomware]( - Criminals go where the money is, and cybercriminals are no exception. And right now, the money is in ransomware. It's a simple scam. Encrypt the victim's hard drive, then extract a fee to decrypt it. The scammers can't charge too much, because they want ......[(more)]( [] [Huge, Unprecedented Ransomware Attack Hits Around the Globe]( - Thanks to a lucky find by a UK-based security researcher, yesterday's massive global ransomware attack is reported to be slowing down today. Dubbed WannaCry or WannaCrypt, the monstrous ransomware hack hit hospitals, schools, government agencies, and ......[(more)]( R Language [] [Pretty scatter plots with ggplot2]( - @drsimonj here to make pretty scatter plots of correlated variables with ggplot2! We’ll learn how to create plots that look like this:...[(more)]( [] [AzureDSVM: a new R package for elastic use of the Azure Data Science Virtual Machine]( - by Le Zhang (Data Scientist, Microsoft) and Graham Williams (Director of Data Science, Microsoft) The Azure Data Science Virtual Machine (DSVM) is a curated VM which provides commonly-used tools and software for data science and machine learning, pre-installed. ...[(more)]( [] [Analyzing the home advantage in English soccer, with R]( - It's well-known that the home team has an advantage in soccer (or football, as it's called in England). But which teams have made the most of their home-field advantage over the years? Evolutionary biologist (and Liverpool fan) Joe Gallagher analyzed the ......[(more)]( Product Reviews and Articles [] [Masking your on-premise database with SQL Data Mask]( - Things move fast in Foundry, Redgate’s research and development division. In our last update three weeks ago we announced our intention to build a version of SQL Data Mask that would mask on-premise SQL Server databases, as well as Azure SQL Databases. ...[(more)]( Press Releases [] [TimeXtender’s Discovery Hub® Now Available in the Cloud]( - TimeXtender, a software leader dedicated to democratizing access to corporate data, today announced that customers now have the flexibility to deploy Discovery Hub® in three models: in the cloud, on-premises or in a hybrid model. ...[(more)]( PowerShell [] [Pester for Presentations – Ensuring it goes ok]( - Whilst I was at PSCONFEU I presented a session on writing pester tests instead of using checklists. You can see it here During the talk I showed the pester test that I use to make sure that everything is ready for my presentation. A couple of people ......[(more)]( [] [PowerShell Remoting Basics]( - PowerShell remoting enables an administrator to remotely manage many Windows Servers from a central location. With open-source releases of PowerShell, your target servers could be running Linux or macOS as well. PowerShell remoting simplifies cross-platform ......[(more)]( PowerPivot/PowerQuery/PowerBI [] [Power BI Report Server–Trial]( - I just installed the Power BI report server following @SQLDusty YouTube 13 minute Step-by-Step video. You have to download both the Power BI Report Server and a new Power BI Desktop. Once installed, it is easy to get your Power BI Report onto the Server ......[(more)]( [] [Identifying Number of Cluster in K-mean Algorithm in Power BI: Part 7]( - I have explained the main concept behind the Clustering algorithm in Post 5 and also I have explained how to do cluster analysis in Power BI in Part 6. In this post, I will explain how identify the best number of cluster for doing cluster analysis by ......[(more)]( [] [BI Beginner: Simple X-Y Plot]( - When I worked for Microsoft giving presentations on data platform products, it was a common occurrence to have people tell me that they didn’t know how to use Power BI. This is the first in a series of posts showing how to do simple, useful tasks in ......[(more)]( [] [BI Beginner: Stacked Charts in Power BI]( - People new to Power BI Desktop have asked me how to create a columnar chart where each column has different colors for different values stacked on top of each other. There’s no reason to be intimidated. Creating such a chart is very simple with only ......[(more)]( [] [A closer look at Power BI Report Server]( - On May 3, Microsoft announced Power BI Premium, a capacity-based licensing model that increases flexibility for how users access, share and distribute content. The new offering also introduces the ability to manage Power BI reports on-premises with the ......[(more)]( [] [Deploying, Securing, and Updating Power BI Reports]( - Power BI app is used to provide updateable visualizations of an organization’s business data; its graphs, plots, tables, reports and other types of visualization. It is designed to make it easier and quicker to deploy summarizations of business data ......[(more)]( [] [Deploying, Securing, and Updating Power BI Reports]( - Before Excel and other data-linked spreadsheets, business reports needed to be hand-crafted by IT experts, and changes were arbitrated slowly via change control. Power BI was designed to take the liberation that Excel pioneered to the max, by allowing 'power' users to not only to create Power BI desktop reports, graphics and visualisations in Power BI App, but then link and publish these to the Power BI app. These in turn can be given row level security and have their underlying data kept up-to-date so that users can log in and see the relevant and appropriate data in a browser. Saurabh Desai shows how....[(more)]( [] [Power BI Premium. Is It For You or Not?]( - There were many announcements earlier this month about Power BI. One of them which was and still is the breaking news of all is Power BI Premium. Many of you already know that it is a new licensing plan, but how it works? Is this a licensing plan that ......[(more)]( Performance Tuning SQL Server [] [Knowing the Options]( - Have you every executed a query in SQL Server Management Studio, looked at the execution plan, and noticed that it was a different plan than what was generated on the server? A potential reason for this could be a different option settings.  The options ......[(more)]( [] [Weaning yourself off of SQL Profiler (Part 2)]( - In a prior post, I shared a script that will take a running trace and show you the XE events that it relates to, and what columns are available within those XE events. Specifically, this was for converting a deadlock trace into an XE session; however the process is the same for converting any trace into an XE session. In today’s post, we’ll compare the deadlock trace and the new XE by running both, creating a deadlock, and comparing the captured data. ...[(more)]( [] [SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions]( - But is it faster? Now, I know. There are very few “always” things out there in SQL Server. This is also true for functions. A lot of the time — I might even say most of the time, inline table valued functions are going to be faster that scalar and multi ......[(more)]( [] [Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?]( - I think I’ve mentioned that the most fun part of new features is testing them with old ideas to see how they react. It occurred to me that if Adaptive Joins didn’t work with APPLY, I might cry. So, here goes nothin’! Cross Simple Cross Apply…ies can ......[(more)]( [] [Troubleshooting CPU Performance on VMware]( - When troubleshooting CPU performance issues on virtualized SQL Servers running on VMware, one of the first things I do is verify that the virtual machine configuration isn’t a contributing factor to the performance problem. Where a physical server has ......[(more)]( [] [Automatic plan correction in SQL Server 2017]( - Automatic plan correction is a new automatic tuning feature in SQL Server 2017 that identifies SQL query plans that are worse than previous one, and fix performance issues by applying previous good plan instead of the regressed one. When you need a ......[(more)]( [] [What Do You Do When Performance Sucks?]( - So you are a SQL Server DBA, and you are responsible for a database, which gives you a hard time. Users constantly complain about poor performance, and the developers blame the database (what else?). It’s time to dig in and do some serious performance ......[(more)]( [] [Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint]( - Hot Diggety! After reading an excellent tip by Dmitry Pilugin about the new hint for forcing a parallel plan in SQL Server 2017, I figured I’d test it out on SQL Server 2016’s latest CU to see if it had been back ported yet. Good news! It has. Obviously. ...[(more)]( [] [SQL Server 2017: Interleaved Execution for MSTVFs]( - What I don’t want you to take away from this Is that I want you to start using Multi Statement Table Valued Functions all over the place. There are still problems with them. Backed by table variables Lots of hidden I/O cost Number of executions may ......[(more)]( [] [SQL Server 2017 Showplan enhancements]( - Cross post with In my previous post on Easy way to get statistics histogram programmatically, I referred to statistics as the building blocks on which the Query Optimizer reasons to compile a good enough plan to resolve queries. ...[(more)]( Microsoft News : General Interest [] [Microsoft to Open Africa Data Centers to Seek Edge in Cloud Push]( - Software maker vies with Amazon to target African customers; customers including Standard Bank to benefit from new hubs Read More ...[(more)]( [] [Story Remix Bridges The Old And The New Microsoft](34415.html) - The app is a successor to Windows Movie Maker that will let you create videos from your photos and clips, as well as mixed reality content borrowed from the Remix 3D community. ...[(more)]( Microsoft News : Patches, Bugs [] [In Rare Move, Microsoft Offers Critical Security Patch For Windows XP, 8, Other OSes]( - The critical security fix should offer Windows XP, Windows 8, and Windows Server 2003, among others, the same protections as their Windows 10 counterparts. ...[(more)]( LINQ/Entity Framework [] [High Performance PowerShell with LINQ]( - PowerShell is a scripting language, and like all scripting languages it struggles to perform well with rapid iterative processes such as aggregation. It isn't well-known that PowerShell can use LINQ for many of those task which would otherwise use iteration, though somewhat awkwardly. However, some of the speed improvements you can get are startling. To get you well started, Michael explains every LINQ function , gives you example code in C#, The PowerShell way of getting the result, and finally Powershell's use of LINQ. This article could change the way you use PowerShell....[(more)]( Internet of Things [] [Microsoft: Government Should Regulate IoT Security]( - Azure IoT engineering head says bar is low in IoT security, and government should get involved Read More ...[(more)]( [] [Google Launches Cloud Service For Managing Internet of Things Devices]( - Cloud IoT Core will integrate with Google analytics services such as Dataflow and BigQuery on the Google Cloud Platform to help enterprises manage their internet of things environment. ...[(more)]( [] [IoT Platform Maker infiswift Aims to Modernize Farming]( - The infiswift IoT platform deploys several patent-pending technologies that enable what the company claims is best-in-class performance for agriculture and energy customers at scale. ...[(more)]( [] [The stages of enterprise IoT adoption]( - Teresa Tung on building a business case for the Internet of Things.Continue reading The stages of enterprise IoT adoption. ...[(more)]( Hardware News [] [WD Elevates Red, Red Pro HDDs To 10TB With Helium]( - WD increased the Red and Red Pro offerings to include new 10TB options. The drives are purpose-built for NAS environments where vibration and 24/7 operation intensify disk requirements. ...[(more)]( [] [Western Digital: Sales of Helium-Filled HDDs Accelerating, 15M Sold So Far]( - Western Digital recently announced that it had sold 15 million helium-filled hard drives, indicating that sales of such HDDs are accelerating. Since the launch of the HGST HelioSeal platform about 3.5 years ago, it has enabled HGST to make a number of ......[(more)]( HA/DR/Always On/Clustering [] [SQL SERVER – FIX: Msg 35250, Level 16, State 7 – The Connection to the Primary Replica is Not Active. The Command Cannot be Processed]( - Along with my performance consulting, I also assist many clients in fixing some quick issue. You can refer for various consulting services which I provide. This blog is an outcome of one such engagement where the client has deployed standalone SQL Server ......[(more)]( ETL/SSIS/ELT [] [Passively Mine SSIS Data Flow Rows Loaded From the SSIS Catalog]( - As I wrote in Parsing SSIS Catalog Messages for Lookup Performance, v2 : I prefer to passively gather performance metrics – even if it’s a little convoluted and more work (for me). I don’t often need these metrics immediately available, so I execute ......[(more)]( DevOps and Continuous Delivery (CI/CD) [] [The language of DevOps ROI]( - How do you quantify the value of DevOps? The answer might depend on what value actually means for your organization, which stakeholder you’re talking to, and what type of lens they’re looking through. DevOps is clearly becoming more popular. 33% of respondents ......[(more)]( [] [Continuous Database Deployment with Confidence]( - Overview Deploying many changes a day to a SQL Server database sounds hard; but as long as you have the tools and processes in place, and you understand the steps involved, then it is straightforward. In this article, I would like to show one example ......[(more)]( [] [Continuous Database Deployment with Confidence]( - To achieve gains from Database Lifecycle Management in general, and in Continuous Deployment in particular, it pays to aim for lightweight but consistent techniques and processes, refining them iteratively in the light of experience. Edward Elliott describes a Test-driven development process for developing SQL Server databases that worked for CD, based on a series of steps. Though the tools for CD may be necessary, they are not sufficient, without an effective structured process....[(more)]( [] [The language of DevOps ROI]( - How do you quantify the value of DevOps? The answer might depend on what value actually means for your organization, which stakeholder you’re talking to, and what type of lens they’re looking through....[(more)]( Database Design, Theory and Development [] [To Really Understand Integrity, Don't Start with SQL]( - Here's what's wrong with the picture of two weeks ago, namely:"Constraints are categorized as follows:Domain integrity ConstraintsEntity integrity ConstraintsReferential integrity ConstraintsNot nullUniqueForeign keyCheckPrimary keyConstraints are always ......[(more)]( Data Visualisation [] [Setting Up InfluxDb, Chronograf, and Grafana for the SqlServer Dev]( - There are some beautiful ways to visualize time series data with the tools I'm going to go over. This post is purely focused on the initial setup and saving you some time there. In a future post, I'll show how some of these tools can help you visualize ......[(more)]( Conferences and Events [] [New class: Immersion Event on Clustering and Availability Groups]( - We have a third exciting new class debuting this October in Chicago: Immersion Event on Clustering and Availability Groups. It’s a 2-day class, taught by Jonathan Kehayias, our resident expert on all things AlwaysOn. We’ve seen a huge surge in clients ......[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [TIL: Microsoft Azure Part 1]( - I thought maybe it would be a good idea to start a multi-part series on Today I Learned (TIL) about Microsoft Azure. As part of my new job I am currently learning as much about Azure as possible. As I learn things, I will blog to share what I am learning. ...[(more)]( [] [5 Common Challenges to Building BI in the Cloud]( - After spending 20 years building analytics, BI and database solutions, I've focused on Cloud data solutions over the past 2 years. I've chosen 5 common challenges that I face every day with Cloud migrations and that you'll face in your Cloud BI projects. read ......[(more)]( [] [Azure Resource Manager simply allows you to configure and implement your infrastructure with a JSON Template.]( - I had a very interesting meeting with a University recently, who were interested in using ARM Templates to deploy their teaching and learning environments. Azure Resource Manager Templates simply allow you to configure your infrastructure within a JSON ......[(more)]( [] [Building a Faux PaaS, Part 2: Choosing and Testing a Cloud Vendor]( - Background: I’m working with kCura to build a Faux PaaS: something akin to Microsoft’s Azure SQL DB, but internally managed. In Part 1 of this series, I talked about why we’re building it, and today, I’ll talk about where. The database team rarely gets ......[(more)]( [] [In The Cloud: The Importance of Being Organized]( - People often ask me about learning how to use Azure SQL Database as well as many other Azure products. If you want to learn, you’ve got to have an Azure account. Get one for free or use your personal or corporate MSDN account. Where I see people struggling ......[(more)]( Azure SQL Database [] [Automatic index management in Azure SQL database]( - Azure SQL Database has a built-in intelligence mechanism that can automatically tune and improve performance of your queries by dynamically adapting the database schema to your workload. In Azure SQL Database, you don’t need to worry about the index ......[(more)]( Azure SQL Data Warehouse and Data Lake [] [SQL Data Warehouse reference architectures]( - With so many product options to choose from for building a big data solution in the cloud, such as SQL Data Warehouse (SQL DW), Azure Analysis Services (AAS), SQL Database (SQL DB), and Azure Data Lake (ADL), there are various combinations of using the ......[(more)]( [] [Azure SQL Data Warehouse loading patterns and strategies]( - This article provides an overview of the Microsoft Azure SQL Data Warehouse architecture. This platform-as-a service (PaaS) offering provides independent compute and storage scaling on demand. This ......[(more)]( Azure Analysis Services [] [Building an Azure Analysis Services Model on Top of Azure Blob Storage—Part 1]( - In a comment to a recent blog article, Bill Anton raised a question about the target scenarios for the modern Get Data experience in Tabular 1400 models, especially concerning file-based data sources. So, let’s look at a concrete example from my personal ......[(more)]( Analysis Services / BI on the MS Stack [] [SSAS Multidimensional: Are Your Indexes Processed?]( - If you are using SSAS Multidimensional and you use Process Update to process your dimensions, here’s something for you to try: run a Process Default on your cube. Does it finish in a few seconds? Then you’re ok. If it doesn’t, and it takes minutes or ......[(more)]( [] [Analysis Service 2016 Model gets corrupted if you add unsupported parameter while configuring the data source]( - Hello Everyone, today I am going to discuss about a known issue is Analysis Service 2016 Tabular Model. If you have built an Analysis Service tabular project and added some unsupported content ......[(more)]( AI/Machine Learning/Cognitive Services [] [Why Machine Learning Can Improve Customer Service]( - Machine learning-powered chatbots offer companies the possibility of saving money while improving customer service. ...[(more)]( [] [Why Automation and AI are Cool, Until They're Not]( - Automation displaced manufacturing jobs, but it also enabled the creation of new businesses and career opportunities. Will knowledge workers and their employers adapt fast enough? ...[(more)]( [] [How AI is used to infer human emotion]( - Rana el Kaliouby discusses the techniques, possibilities, and challenges around emotion AI today.Rana el Kaliouby is the co-founder and CEO of Affectiva, an emotion measurement technology company that grew out of MIT's Media Lab. Rana is giving a talk, ......[(more)]( Administration of SQL Server [] [Setting SQL Server Max Memory Dangerously Low]( - Hopefully you know the relevance and importance of setting a correct value for max memory on your SQL Server. By default it will be the value 2147483647 which is not a random number but the 8th Mersenne prime! In a … Continue reading ? ...[(more)]( [] [Implementing Buffer Pool Extension]( - Buffer Pool Extension was released in SQL 2014 so it’s not new. It is also not advertised very much, but that’s not for lack of usefulness. RAM is far faster than SSD and certainly arm based disks, but most databases are too big to cache into RAM. ......[(more)]( [] [SQL Server offline after applying service pack]( - In this post I´ll review two cases that you may bump into when applying service packs in SQL Server. Case 1 You need to install a SQL Server service pack, so you plan, test and got everything ready to apply it in production. You run the installation ......[(more)]( [] [Try new SQL Server command line tools to generate T-SQL scripts and monitor Dynamic Management Views]( - This post was authored by Tara Raj and Vinson Yu, Program Managers – SQL Server Team We are excited to announce the public preview availability of two new command line tools for SQL Server: The mssql-scripter tool enables developers, DBAs, and sysadmins ......[(more)]( [] [Troubleshooting CDC enabling failure – Part 1]( - My customer had recently experienced 2 interesting CDC issues and I would like to share the troubleshooting process and root cause for these issues today....[(more)]( [] [SQL SERVER – Fix: The EXECUTE Permission was Denied on the Object ‘xp_cmdshell’, Database ‘mssqlsystemresource’,schema ‘sys’.]( - I write and play around a lot with SQL object level permissions and I love working with them. Having said that, I also explore the ways people want to run code to achieve certain objectives. In the DBA’s world when the number of servers we are talking ......[(more)]( [] [SQL Server 2017 sys.dm_os_enumerate_fixed_drives]( - In SQL Server 2017, you can use the new DMV sys.dm_os_enumerate_fixed_drives to identify free disk space. The DMV is replacement of xp_fixeddrives. Old way XP_FIXEDDRIVES New Way SELECT * FROM sys.dm_os_enumerate_fixed_drives; I wish Microsoft could ......[(more)]( [] [Why shouldn’t I shrink my database log file?]( - TL;DR: It’s pretty pointless and can cause performance issues. Let’s start by asking why you might want to shrink your log. It’s too big I find that people who say this frequently don’t have a firm idea of what is too big or even why it might be as ......[(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