SQL Server v.Next, shiny new toys, and working out what DBAs do all day (Database Weekly 2/18/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Vendors/3rd Party Products - [Extending DevOps practices to SQL Server databases](#28230) - [Does SQL Clone work with encrypted databases?](#28202) - [Getting started with SQL Doc and PowerShell](#28201) - [Data masking survey: win $100 Amazon gift card](#28194) T-SQL - [T-SQL tuesday #87 - fixing old problems with shiny new toys: STRING_SPLIT](#28227) - [CLR Stored Procedure Cannot Be Marked as System Object?](#28216) - [A Quick Look At Two New DMV Columns](#28207) - [Musings on SESSION_CONTEXT()](#28206) - [String Comparisons in SQL: The Metaphone Algorithm](#28195) SQL Server Security and Auditing - [Please don’t feed auditors and lawyers](#28203) - [SQL Server Encryption: Always Encrypted](#28197) SQL Server on Linux - [SQL Server vs SQL Server v.Next](#28222) SQL Server News - [Microsoft to delay its February patches to March 14](#28240) - [What You'll Love about the Next Release of SQL Server](#28239) - [Parsing 4GB JSON with SQL Server](#28236) Security news and thoughts - [Advice to help prevent data breaches at your company](#28220) - [Yahoo Issues Another Warning in Fallout from Hacking Attacks](#28218) Reporting Services - [Scaling out SSRS on SQL Server Standard Edition](#28199) R Language - [Galaxy classification with deep learning and SQL Server R Services](#28226) PowerShell - [Using regex to find PowerShell functions in PowerShell scripts](#28235) - [Export All Plans From Cache to a .SQLPLAN File](#28231) - [Altering a Job Step on Hundreds of SQL Servers with PowerShell](#28215) PowerPivot/PowerQuery/PowerBI - [Power BI and the World Bank API](#28237) - [Power BI Custom Visuals: Mekko Chart](#28208) Microsoft News - [SQL Server in an Azure VM - What's New](#28214) - [Microsoft tries again to tackle healthcare with new services and tools](#28211) - [Visual Studio 2017 is Launching on March 7](#28204) Internet of Things - [Avnet and IBM Advance IoT Solutions with Joint Development Lab](#28223) DBA Tools - [I contributed to dbatools, and you can too](#28228) Data Mining/Data Analysis - [Guarantee Better Data Governance With Capability Based Risk Assessment](#28209) Continuous Delivery (CI/CD) - [Viewing container logs](#28225) Columnstore Indexes - [Overview of Columnstore Indexes in SQL Server](#28234) Career Growth - [Musical Chairs And The DBA](#28212) Big Data - [Did Facebook steal the design for its data center in Sweden?](#28217) - [The Harsh Reality Behind Big Data Misuse](#28196) Azure SQL Database - [Remove Old SQL Database Backup Files from Azure Storage](#28210) Analysis Services / BI on the MS Stack - [Managing Bad Data](#28213) AI/Machine Learning/Cognitive Services - [How I improved my chatbot](#28238) Administration of SQL Server - [Troubleshooting Blocking & Deadlocks for Beginners](#28233) - [Using AT TIME ZONE to fix an old report](#28232) - [What does a DBA do all day?](#28229) - [Deadlock on ALTER DATABASE](#28224) - [Confused by sp_who2 (Dear SQL DBA Episode 30)](#28221) - [Explaining Activity Monitor](#28219) - [DevOps – a DBA’s perspective](#28200) - [SQL Server Temporal Tables: How-To Recipes](#28198) .NET Related Articles - [How Python syntax works beneath the surface](#28205) [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-02-20 [SQL Search]( Find SQL in your database for free For example, want to rename one of your table columns but are not sure what stored procedures reference it? Using Redgate’s free SSMS add-in, SQL Search, you can search for the column name and find all the stored procedures that use it. [Find out more and download now.]( [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more.]( [SQL Toolbelt]( Not enough hours in your day? The SQL Toolbelt lets you reduce the time spent on SQL Server development and administration. Cambridge University developer David Spaxman, for example, says: “I’m saving 10-12 hours a week using the SQL Toolbelt.” Learn how you can double your productivity, speed up deployments and protect your data. [Download a free trial.]( Editorial - Approaching Politics in IT The news cycle seems to be dominated by discussion of politics. Regardless of where you fall on the political spectrum, the talk of politics is inescapable and is probably going to lead to you being displeased with your fellow man. Luckily, we work in IT, so we can avoid politics. BWA-HA-HA-HA-HA! Politics goes way beyond the outcome of elections. Politics is not about which party won or lost this or that vote, not in IT. Politics, at it’s root, is about the [process of decision making](. If there’s one thing that we do in IT, it’s make decisions. Which database management system should we choose? Do you get a training budget? If so, do you get to go to PASS Summit? SQL Cruise? Or are you just supposed to spend it on online courses? Have we done a failover test on our DR system? Should we? Who is testing the backups to ensure we can do a restore? All these are necessary decisions that are a big part of IT. All these decisions directly involve politics. Whether you love or hate politics, and whether your think you are a political creature or not, and whether you wish to be or not, you are absolutely involved in the politics of your team, your department, and your organization. With this in mind, you get to choose how you approach the politics within your sphere. If you lose a decision, do you resort to violence to get the decision to go back your way? If someone disagrees with you, even if they’re wrong, are you going to shout them down in meetings? Or, are you going to approach the politics around you the same way you troubleshoot problematic code or a production outage? Despite whatever passion you bring to the job, of course the right way is the logical and dispassionate approach. Why? Because, being the one to begin shouting doesn’t mean you’ll be the last one shouting. In fact, it frequently means that people stop listening to you. Let's make sure that we focus our passions appropriately and influence the politics around us in a way that reflects well on us and helps us get our way. Grant Fritchey 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. Vendors/3rd Party Products [] [Extending DevOps practices to SQL Server databases]( - In this free webinar, Steve Jones and Arneh Eskandari show how Redgate’s Database DevOps solution works with Visual Studio Team Services to improve your database delivery process. Register now....[(more)]( [] [Does SQL Clone work with encrypted databases?]( - SQL Clone is designed to make it easy to provision copies of a database for development and test environments in seconds. But what if the database files are encrypted using Transparent Data Encryption (TDE), a technology many organizations have implemented to protect their sensitive data? Does it still work? Data Platform MVP Steve Jones finds out with clones from both a live database and a backup. ...[(more)]( [] [Getting started with SQL Doc and PowerShell]( - SQL Doc's command line interface lets you run PowerShell scripts that can automatically generate documentation for all your databases, in a variety of output formats. Phil Factor shows how to use PowerShell and SQL Doc Project Templates to customize what's included in the documentation, and its format, as well as how to circumvent the need to include username and password information in the scripts, when using SQL authentication, by getting the authentication from your registered servers....[(more)]( [] [Data masking survey: win $100 Amazon gift card]( - The Foundry team at Redgate know that managing sensitive and regulated data can be problematic. They want to solve these problems. What do you find frustrating? What would save you time? Fill out this short survey to let them know, and at the end you'll be entered into a draw to win a $100 Amazon gift card. ...[(more)]( T-SQL [] [T-SQL tuesday #87 - fixing old problems with shiny new toys: STRING_SPLIT]( - On the theme of fixing old problems with shiny new toys, Steve Hughes takes a look at splitting strings in SQL using STRING_SPLIT....[(more)]( [] [CLR Stored Procedure Cannot Be Marked as System Object?]( - It is an open secret that in SQL Server, we can “create a system object" by using an undocumented stored procedure sp_MS_marksystemobject - but what about CLR stored procedures?...[(more)]( [] [A Quick Look At Two New DMV Columns]( - Rodney Landrum provides a quick introduction to two new features announced in SQL Server 2016 SP1, columns added to the existing DMVs, sys.dm_server_services and sys.dm_os_sys_info....[(more)]( [] [Musings on SESSION_CONTEXT()]( - As part of last month's T-SQL Tuesday on the theme of "fixing old problems with shiny new toys", Ewald Cress looks at SESSION_CONTEXT() as a replacement for CONTEXT_INFO()....[(more)]( [] [String Comparisons in SQL: The Metaphone Algorithm]( - When exploring the use of the Metaphone algorithm for fuzzy search, Phil couldn't find a SQL version of the algorithm so he wrote one. The Metaphone algorithm is built in to PHP, and is widely used for string searches where you aren't always likely to get exact matches, such as ancestral research and historical documents. It is particularly useful when comparing strings word-by-word. With a SQL version, it is easy to experiment on large quantities of data!...[(more)]( SQL Server Security and Auditing [] [Please don’t feed auditors and lawyers]( - Daniel Hutmacher talks about the need for calculated risks in IT, arguing that fewer formal controls and processes will allow your business to develop faster....[(more)]( [] [SQL Server Encryption: Always Encrypted]( - Is 'Always Encrypted' SQL Server 2016's most widely important new feature? It is significant that 'Always Encrypted' in SQL Server is in all editions of SQL Server. Because of the increasing importance of encryption to data governance, it allows encryption for the sensitive application data for everywhere beyond the application's client connection, including network, server, database and storage. Robert Sheldon explains what it is, why you should try it out, and how to set about it....[(more)]( SQL Server on Linux [] [SQL Server vs SQL Server v.Next]( - Henrico Bekker runs through a performance comparison of SQL Server against SQL Server on Linux....[(more)]( SQL Server News [] [Microsoft to delay its February patches to March 14]( - Microsoft's February Patch Tuesday patches are not going to arrive until Tuesday March 14, officials confirm, while providing no reasons for the delay....[(more)]( [] [What You'll Love about the Next Release of SQL Server]( - The latest version of Microsoft SQL Server, dubbed SQL Server v.Next, is due out soon after being in public preview mode since last year. From the looks of things, there is plenty for IT departments to like about the latest release, including the ability to develop applications on Linux or Docker’s container environment in addition to Windows....[(more)]( [] [Parsing 4GB JSON with SQL Server]( - SQL Server 2016 and Azure SQL Database enable you to parse JSON text and transform it into tabular format. In this post, you might see that JSON functions can handle very large JSON text – up to 4GB....[(more)]( Security news and thoughts [] [Advice to help prevent data breaches at your company]( - Watch the latest Modern Workplace episode, “Cyber Intelligence: Help Prevent a Breach,” to get advice on how to best approach cyber security at your company from two chief information security officers (CISO)—Vanessa Pegueros, CISO at DocuSign, and Mike Convertino, CISO at F5 Networks....[(more)]( [] [Yahoo Issues Another Warning in Fallout from Hacking Attacks]( - Yahoo is warning users of potentially malicious activity on their accounts between 2015 and 2016, the latest development in the internet company's investigation of a mega-breach that exposed 1 billion users' data several years ago....[(more)]( Reporting Services [] [Scaling out SSRS on SQL Server Standard Edition]( - The ability to scale out SSRS, so that multiple report server instances can access a single report server database, is an Enterprise Edition-only feature. So when Rodney Landrum was tasked with implementing a scale-out solution, with failover, for SSRS on SQL Server Standard Edition, it required some creative thinking. This article describes his solution, its merits and drawbacks....[(more)]( R Language [] [Galaxy classification with deep learning and SQL Server R Services]( - One of the wow moments in the keynote where SQL Server 2016 was first introduced was a demo that automated the process classifying images of galaxies in a huge database of astronomical images. Since then a step-by-step tutorial on implementing the galaxy classifier in SQL Server has been published....[(more)]( PowerShell [] [Using regex to find PowerShell functions in PowerShell scripts]( - Want to quickly find all the function definitions within a module? Or the function calls within a function definition? Stuart Moore shares a regex he's written to do just that....[(more)]( [] [Export All Plans From Cache to a .SQLPLAN File]( - Grant Fritchey shares a PowerShell script he's written that exports query plans from the plan cache into a .sqlplan file....[(more)]( [] [Altering a Job Step on Hundreds of SQL Servers with PowerShell]( - A couple of weeks ago Rob Sewell got a request call from a client while at the airport. In this post, Rob details how he was able to make an alteration to hundreds of backup jobs in just five minutes....[(more)]( PowerPivot/PowerQuery/PowerBI [] [Power BI and the World Bank API]( - Scott Larsen walks through the steps of creating a Power BI visualization based on Hans Rosling's 2006 TED talk....[(more)]( [] [Power BI Custom Visuals: Mekko Chart]( - The Mekko Chart custom visual in Power BI is helpful when you need to analyze two different dimensions on a single chart - Devin Knight shows how to use it....[(more)]( Microsoft News [] [SQL Server in an Azure VM - What's New]( - In this video for Channel 9, Luis Vargas shows what's new with SQL Server in an Azure Virtual Machine. No slides and all demo, Luis first provides a quick overview of the available SQL Server offering in Azure Virtual Machines via Azure Marketplace. He shows all the different SQL Server VM images of versions and editions in the gallery, including SQL Server 2016 SP1 Express and Developer as well as SQL Server vNext on Red Hat Enterprise Linux!...[(more)]( [] [Microsoft tries again to tackle healthcare with new services and tools]( - Microsoft is trying again to tackle the healthcare market with a concerted strategy involving artificial intelligence, productivity tools, and partnerships....[(more)]( [] [Visual Studio 2017 is Launching on March 7]( - Microsoft's Sascha Corti shares the details of how you can get involved with the launch of Visual Studio 2017 in a couple of weeks....[(more)]( Internet of Things [] [Avnet and IBM Advance IoT Solutions with Joint Development Lab]( - Led by its Technology Solutions team, Avnet will team with IBM to establish a Watson Internet-of-Things Joint Lab within IBM’s newly opened global Watson IoT Center in Munich, Germany. Avnet will use this unique environment to showcase, demonstrate, develop and sell innovative IoT solutions...[(more)]( DBA Tools [] [I contributed to dbatools, and you can too]( - Drew Furgiuele talks about how he had the chance to be involved with dbatools, the exciting community-created PowerShell module that adds functions to help you manage, migrate, monitor, and validate your SQL Servers....[(more)]( Data Mining/Data Analysis [] [Guarantee Better Data Governance With Capability Based Risk Assessment]( - Data Management and Data Governance enables us to harness the right data fit for raising an organization’s confidence and trust in its data. There is definite value associated by leveraging the right data and at the same time, there is also risk associated with data and its operations....[(more)]( Continuous Delivery (CI/CD) [] [Viewing container logs]( - Andrew Pruski shares a key step to viewing Docker logs that can trip you up if missed out....[(more)]( Columnstore Indexes [] [Overview of Columnstore Indexes in SQL Server]( - SQL Server 2016 SP1 opens up a lot of new possibilities for Standard, Web, and Express editions of SQL Server, including new options for in-memory technologies such as columnstore indexes. In this post, Melissa Coates provides an introduction....[(more)]( Career Growth [] [Musical Chairs And The DBA]( - Hypothetically, if the DBA career path withers on the vine, what's one to do? Dave Mason shares his thoughts on the future or the DBA....[(more)]( Big Data [] [Did Facebook steal the design for its data center in Sweden?]( - Despite Facebook's best efforts to get a trade-secrets theft lawsuit tossed, a federal judge is ruling that the social media giant must defend allegations that the company stole the design of its touted data center in LuleÃ¥, Sweden....[(more)]( [] [The Harsh Reality Behind Big Data Misuse]( - Big Data has its origin in science, but it is now being used commercially to increase the information that organizations have about people. Misuse of personal data is immoral, illegal and can have a devastating effect on peoples lives - William Brewer offers an example....[(more)]( Azure SQL Database [] [Remove Old SQL Database Backup Files from Azure Storage]( - Pinal Dave shares a PowerShell script for removing old SQL database backup files from Azure Storage....[(more)]( Analysis Services / BI on the MS Stack [] [Managing Bad Data]( - In the last post in his ongoing series about ETL best practices, Tim Mitchell discussed the importance of error handling in ETL processes. In this post he digs a bit further into that topic to explore the design patterns for managing bad data that makes its way into ETL processes....[(more)]( AI/Machine Learning/Cognitive Services [] [How I improved my chatbot]( - In his ongoing series about chatbots, Benjamin Perkins now talks us through some recent improvements he's made to his own chatbot....[(more)]( Administration of SQL Server [] [Troubleshooting Blocking & Deadlocks for Beginners]( - In this free online training course from Kendra Little, learn to set up simple, lightweight monitoring using free tools and scripts to find the queries causing your blocking and deadlock problems....[(more)]( [] [Using AT TIME ZONE to fix an old report]( - Rob Farley contributes to T-SQL Tuesday #87, showing how he would use AT TIME ZONE to simplify reports based on an end user's time zone....[(more)]( [] [What does a DBA do all day?]( - In a bid to spread greater understanding about the role, Thomas LaRock gives a summary of the day-to-day tasks that are common for a DBA....[(more)]( [] [Deadlock on ALTER DATABASE]( - Kenneth Fisher talks through how he dealt with a database stuck in single user mode....[(more)]( [] [Confused by sp_who2 (Dear SQL DBA Episode 30)]( - In the latest episode of Dear SQL DBA, Kendra Little clears up the confusion surrounding sp_who2....[(more)]( [] [Explaining Activity Monitor]( - Tibor Karaszi takes a moment to clear up some incorrect assumptions about the Activity Monitor tool in SQL Server Management Studio....[(more)]( [] [DevOps – a DBA’s perspective]( - In this blog post, DevOpsGuys DBA Paul Ibison, gives his views on what DevOps means from a DBA perspective. ...[(more)]( [] [SQL Server Temporal Tables: How-To Recipes]( - Tables that return the value of the data in the table at a particular point of time have been with us since the first relational database, but have always required special queries and constraints, and can be tricky to get right. System-versioned Temporal Tables, new in SQL Server 2016, make such tables behave like any other. How do you create one, or modify an existing table? How can you get an In-Memory Optimized OLTP table to be Temporal? Alan Grinberg shows how....[(more)]( .NET Related Articles [] [How Python syntax works beneath the surface]( - In this video excerpt from his Oriole online tutorial, Aaron Maxwell uses the Panda’s library to illustrate how Python’s syntax, which lets classes define how they behave when used with built-in binary operators like less-than ((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 ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com