Five Mistakes Performance Tuners Make, Exploring with PowerShell and DevOps and the DBA (Database Weekly 8/12/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [Extending DevOps practices to SQL Server databases with Redgate, Git & Jenkins](#29696) Vendors/3rd Party Products - [New permissions features brings access control to SQL Clone](#29681) - [Using multiple custom SQL formatting styles in SQL Prompt](#29680) The Lighter Side - [Password Guru Bill Burr's New Advice: Never Mind](#29698) - [Painting with Data](#29695) SQL Server News - [SSMS 17.2 is now available](#29692) - [New in SSMS: Performance Dashboard built-in](#29690) - [New in SSMS: Searching in Showplan](#29689) Security news and thoughts - [Scientists Hack a Computer Using DNA](#29691) - [Using PowerShell to check if your password has been in a breach](#29688) Reporting Services - [Adding JavaScript to Your PowerShell HTML Reports](#29697) R Language - [Tutorial: Publish an R function as a SQL Server stored procedure with the sqlrutils package](#29694) PowerShell - [Exploring SQL Server With Powershell And SMO Basics](#29705) Performance Tuning SQL Server - [Five Mistakes Performance Tuners Make](#29700) DevOps and Continuous Delivery (CI/CD) - [DevOps and the DBA: The Ghost in the Machine](#29708) Database Design, Theory and Development - [Why Parameter Sniffing Is Making Your Queries Receive Sub-Optimal Execution Plans](#29710) - [How Indexing Affects Deletion Queries](#29709) - [The Power of Python and SQL Server 2017](#29682) Data Privacy - [Who was that masked man anyway?](#29685) Computing in the Cloud (Azure, Google , AWS) - [Beware the automated backup window when running native SQL Server backups in RDS](#29707) - [How to choose a cloud provider](#29701) Career Growth - [How to be a bad interviewer](#29706) - [The DBA Job I Turned Down](#29686) - [Learning designed for the data professional](#29683) Azure SQL Database - [SQL Server to Azure SQL Database data movement options and considerations with changing schemas and triggered refreshes](#29704) - [Initializing Azure SQL Database with SQL Server Transactional Replication](#29703) - [Azure SQL: work with JSON files where they are](#29693) AI/Machine Learning/Cognitive Services - [Podcast: Integrating data with AI](#29702) Administration of SQL Server - [How to Drop All Your Indexes – Fast](#29699) - [Do Index Changes Remove Execution Plans from Cache?](#29687) [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-08-14 [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.]( [Foundry]( How might classification and better documentation improve data safety? SQL Atlas is the latest prototype to come from the Redgate Foundry - find out more about what the tool does and try out the interactive demo for yourself. [Try the free prototype]( [Redgate Hub]( Stop your database being a bottleneck The new Redgate Hub is the shortcut to the latest insights into database development. Constantly updated with invaluable content, it’s the one resource you need to solve problems, share ideas, discover new tool features, and expand your database skills. [Discover the Redgate Hub]( Editorial - The art of getting it right This week's editorial is a guest post from Phil Factor. I once managed a software house that did applications for several local businesses. It wasn't easy, because these businesses ran on a very tight budget, and they didn't want the frills. We worked on fixed-price contracts, so if we got it wrong we made a loss. It is a great way of developing a sense of urgency, and making sure that your programming work was sufficient but never fancy. Our database guy was a quiet, methodical chap who went about his work slowly and carefully. He took every deployment seriously, even if he was making a change to the custom music player in the server room, cobbled together from an ancient Unix box. As a manager, I gradually began to feel resentful that all his database deployments were taken so seriously, and took so long, even when it didn't matter, and that our development time was getting squeezed in consequence. Eventually, I challenged him about it. To my surprise, he came up with a very good reason. The reason that every database deployment is taken with the same caution and seriousness, he explained, was that they generally went well; to the point that he could easily become rusty on the many roll-back procedures, belt-and-braces precautions and tests. When anything failed, he tested his skill in bringing things to a happy conclusion. We were managing applications where failure would cause businesses and their staff real catastrophe. A deployment gone wrong could, unless quickly remedied, cause significant loss of trading or reputation to these guys. Because our database guy treated every deployment with the same attention to detail, he occasionally had failed deployments to deal with that weren't important to the outside world, but allowed him to fine-tune his deployment scripts, procedures, and techniques. I bought this explanation. We never ever had to apologise to a customer. It reminded me of the craftsman restoring a cathedral who maintained his meticulous work, including lively and beautiful carvings, in parts of the building that could never be seen close-up. When challenged to explain why he took such care over those parts of his work that nobody would ever see, he merely replied enigmatically "Well, God will see it." Of course: what they were driving at was that by honing your skills where mistakes don't matter, it allows you a better chance of getting it right when it does matter. 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. Webinars [] [Extending DevOps practices to SQL Server databases with Redgate, Git & Jenkins]( - Join Microsoft Data Platform MVP, Grant Fritchey, for our next Database DevOps demo webinar. He’ll be plugging Redgate’s tools into Git and Jenkins to show you how Redgate's Database DevOps solution helps you build, test and deploy your database changes alongside your app code. Sign-up here...[(more)]( Vendors/3rd Party Products [] [New permissions features brings access control to SQL Clone]( - SQL Clone permissions has been high on the wish-list for users and the new feature addresses it in an intuitive way that enables you to allocate and change permissions quickly and easily....[(more)]( [] [Using multiple custom SQL formatting styles in SQL Prompt]( - Usually, when coding SQL, you will want to go along with the coding style of the team you are working with. This article shows how to use SQL Prompt formatting styles to create and maintain multiple code styles, each for a dedicated purpose, and to switch between them and apply a new style to existing code, with ease....[(more)]( The Lighter Side [] [Password Guru Bill Burr's New Advice: Never Mind]( - The man responsible for setting the guidelines for complex passwords says he regrets writing the advice, and acknowledged that it "drives people bananas."...[(more)]( [] [Painting with Data]( - The accidental aRt tumblr continues to provide a steady stream of images that wouldn't look out of place in a modern art gallery, but which in fact are data visualizations gone wrong....[(more)]( SQL Server News [] [SSMS 17.2 is now available]( - The newest version of SQL Server Management Studio is now available for download....[(more)]( [] [New in SSMS: Performance Dashboard built-in]( - With the new SSMS 17.2, Performance Dashboard will be embedded as a built-in Standard Report. This means that it is available for any SQL Server instance starting with SQL Server 2008, without any extra downloads or running any extra scripts....[(more)]( [] [New in SSMS: Searching in Showplan]( - SSMS 17.2, introduces the ability to search table name, index name, column name and more in graphical showplan....[(more)]( Security news and thoughts [] [Scientists Hack a Computer Using DNA]( - Malware can be encoded into a gene and used to take over the computer operating a gene sequencer....[(more)]( [] [Using PowerShell to check if your password has been in a breach]( - This PowerShell script checks haveibeenpwned.com/ to see if your personal data has been exposed in a security breach....[(more)]( Reporting Services [] [Adding JavaScript to Your PowerShell HTML Reports]( - If you want to add some interaction to your reports, this tutorial shows you how....[(more)]( R Language [] [Tutorial: Publish an R function as a SQL Server stored procedure with the sqlrutils package]( - In SQL Server 2016 and later, you can publish an R function to the database as a stored procedure. This makes it possible to run your R function on the SQL Server itself....[(more)]( PowerShell [] [Exploring SQL Server With Powershell And SMO Basics]( - Diving into the Sql Server Management Objects library can be a pretty interesting process. You get to work with database objects as in a new way, and begin manipulating and execute code in a much different approach than purely using T-SQL. Powershell offers a unique way to interact with prebuilt cmdlets....[(more)]( Performance Tuning SQL Server [] [Five Mistakes Performance Tuners Make]( - There’s no Top in the title, and that’s because a TOP without an ORDER BY is non-deterministic, and you’ll get yelled at on the internet for doing that....[(more)]( DevOps and Continuous Delivery (CI/CD) [] [DevOps and the DBA: The Ghost in the Machine]( - Kellyn Pot’Vin-Gorman gives a dose of DevOps 101 to database administrators....[(more)]( Database Design, Theory and Development [] [Why Parameter Sniffing Is Making Your Queries Receive Sub-Optimal Execution Plans]( - Parameter sniffing isn’t always a bad thing. Most of the time it’s good: it means SQL Server is caching and reusing query plans to make your queries run faster. Parameter sniffing only becomes a problem when the cached plan isn’t anywhere close to being the optimal plan for given input parameters....[(more)]( [] [How Indexing Affects Deletion Queries]( - Indexing can affect the performance cost of row deletion as well as insertion....[(more)]( [] [The Power of Python and SQL Server 2017]( - Python is new to SQL Server 2017. It is intended primarily to allow the use of Python-based machine-learning within SQL Server, but it can be used for far more than this, with any Python libraries or Frameworks. To provide an example of what is possible, Hitendra shows how to use the feature securely to provide intelligent application caching, where SQL Server can automatically indicate when data changes to trigger a cache refresh....[(more)]( Data Privacy [] [Who was that masked man anyway?]( - How anonymous is anonymous data?...[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Beware the automated backup window when running native SQL Server backups in RDS]( - If you’ve ever fired up an RDS instance you know you can set an automated backup window for your instance. During this window Amazon will kick off an automated snapshot of your RDS instance each day. Set it and forget it. Kinda nice. Backups are good, right?...[(more)]( [] [How to choose a cloud provider]( - Practical questions to help you make a decision.If you look up the phrase “boiling the ocean,” it’s defined as writing a post on choosing a cloud provider—there are so many different facets and use cases, and each variable complicates your choice....[(more)]( Career Growth [] [How to be a bad interviewer]( - You’re not obligated to accept a job once it’s offered, and if you’ve had to put up with a lot of rubbish during the application process then seriously consider if it’s just going to get worse once you’re in the role. Here are some antipatterns to look out for....[(more)]( [] [The DBA Job I Turned Down]( - Brent Ozar shares a tale of a job he turned down, and the warning signs that sent him running....[(more)]( [] [Learning designed for the data professional]( - PASS Summit offers five educationally-packed days lead by highly-rated, experienced speakers and sessions focused on the way you work with data. Speaker highlights include Bob Ward, and Sunil Agarwal from the Microsoft Tiger Team. Join them Oct 31 to Nov 3....[(more)]( Azure SQL Database [] [SQL Server to Azure SQL Database data movement options and considerations with changing schemas and triggered refreshes]( - This post looks at the problems caused when moving the data at irregular intervals from an on-premise SQL instance with a constantly changing schema to an Azure SQL database....[(more)]( [] [Initializing Azure SQL Database with SQL Server Transactional Replication]( - For migrating databases from an on-premise SQL Server to Azure SQL Database, Transactional Replication has become more and more popular. This approach has some important advantages: you may maintain a live copy of your production data in Azure SQL Database, and reduce the downtime for the actual switch to the cloud to a minimum....[(more)]( [] [Azure SQL: work with JSON files where they are]( - Dealing with CSV or JSON data today is more and more common. SQL Server 2017 and the latest editions of Azure SQL Server offer new ways to work with these data types....[(more)]( AI/Machine Learning/Cognitive Services [] [Podcast: Integrating data with AI]( - Tamr’s Eliot Knudsen on algorithms that work alongside human experts, integrating data from different silos....[(more)]( Administration of SQL Server [] [How to Drop All Your Indexes – Fast]( - If, for whatever reason, you need to drop all of your indexes in a hurry, Brent Ozar has you covered....[(more)]( [] [Do Index Changes Remove Execution Plans from Cache?]( - When you modify the indexes on a table, SQL Server needs to reconsider how it executes queries that reference that table. But the way this appears when you’re looking at your execution plan cache is far from obvious....[(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