Navigating the minefields of Availability Groups, Naming Conventions, Missing index Hints and more in Database Weekly (1/14/2019)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [The 2019 State of Database DevOps results, live with Donovan Brown!](#34214) - [Why the database is at the heart of DevOps success](#34213) - [DevOps: What, who, why and how?](#34212) Virtualization and Containers - [An Introduction to Docker for R Users](#34243) - [How to Check if Your Processor Supports Second Level Address Translation (SLAT)](#34236) - [dbatools and docker](#34220) - [CPU Ready Impact on SOS_SCHEDULER_YIELD](#34217) Vendors/3rd Party Products - [Review your SQL Server permissions with SQL Census (in preview)](#34215) - [Database DevOPs training](#34211) - [Protecting Production Data in Non-Production Environments](#34210) T-SQL - [Adaptive Join](#34250) - [Nullable columns and performance](#34249) - [TSQL : Loop a date range](#34247) - [Closest Match, Part 2](#34238) - [SQL naming conventions](#34237) - [The risks of using EXECUTE (‘SQL script’)](#34232) - [The Whys and Wherefores of Untrusted or Disabled Constraints](#34231) - [Empty Thoughts: Working with NULL](#34230) - [Query memory grants part 2: Varchars and sorting](#34222) - [Generating SQL using Biml (T-SQL Tuesday #110)](#34221) - [Print.. the disruptor of batch deletes in SQL](#34219) SQL Server Security - [Protecting SQL Server Data Using Static Data Masking](#34229) Reporting Services - [Getting SSRS Details via Powershell](#34223) PowerShell - [Multithreading commands](#34246) - [PowerShell to Simulate Load](#34218) PowerPivot/PowerQuery/PowerBI - [Combine All Sheets in a Workbook with Power Query](#34242) - [Power BI Desktop; Getting Started – Part 2](#34241) Performance Tuning SQL Server - [Candy Crush and Missing Index Requests](#34252) - [What Indexes Don’t Do: Redux](#34251) - [Automatic Tuning Monitoring and Diagnostics](#34245) - [Using Track Causality to Understand Query Execution](#34239) - [Optimize for Analytics (Should be a Database Configuration option)](#34235) - [Consultants: want a utility to gather SQL Server data?](#34234) - [Handling Dates in Query Store](#34227) Machine Learning - [How to automate machine learning on SQL Server 2019 big data clusters](#34233) HA/DR/Always On/Clustering - [Replica Failover within the Secondary Availability Group in a Distributed Availability Group](#34224) - [Availability Group Readable Secondaries – Just Say No](#34216) ETL/SSIS/Azure Data Factory - [Send Meaningful Errors for SSIS Packages](#34244) DevOps and Continuous Delivery (CI/CD) - [The 2019 State of Database DevOps](#34209) Computing in the Cloud (Azure, Google , AWS) - [Azure – Backing up to URL](#34248) Administration of SQL Server - [Automating like an Enterprise DBA](#34240) - [Executing xp_cmdshell with Non SysAdmin Account](#34228) - [How to capture errors from SQLCMD](#34226) - [A Monumental Migration to SQL Server 2016 – Part 2](#34225) [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 2019-01-14 [Database DevOps](utm_source=ssc&utm_medium=pubemailad&utm_content=devops-solutions&utm_campaign=databasedevops&utm_term=20190113-slot1) Continuous Delivery for SQL Server Databases Spend less time managing deployment pain and more time adding value. [Find out how with database DevOps](utm_source=ssc&utm_medium=pubemailad&utm_content=devops-solutions&utm_campaign=databasedevops&utm_term=20190113-slot1) [SQL Monitor]( How SQL Server monitoring benefits your whole organization SQL Server monitoring doesn’t just benefit your DBAs. In this new guide from Redgate, we take you through the different ways a robust monitoring solution has a positive impact across your organization, from your development teams to IT management, and from finance to your C-suite. [Download your free copy now]( Editorial - Where's that Snippet? I've written too many SQL procedures and functions to remember. This means that I'm in danger of rewriting perfectly good code I've forgotten. Then, there are other people's perfectly good SQL routines. On top of that, there are the nuggets of code that we all need only occasionally, but always in a hurry. Over the years, I've seen several techniques for keeping tabs on code you need just occasionally. You know when it happens: you get a weird error about, say, a database having no owner. Eh? Oh yes, EXEC sp_changedbowner 'sa'. Or you need to free the cache, but who, while living a normal life, remembers obscure spells such as ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE? We all need somewhere to squirrel this stuff away so that it is easy to retrieve. A friend of mine put all his favourite routines and functions on a website that he intended just for his own use. As he went from site to site, he was able to access his library. After a while it became very popular with other SQL developers. It is still there, raw and undocumented, but very useful. I've now published quite a lot of code and when I'm stuck with a problem, I often stumble across code that looks like it would do the trick, while googling for a solution. While wondering who wrote it, I sometimes see my own name. Really, there must be a better way. I've tried many different tricks to remember code and archive old code for reuse, but I've yet to find the perfect solution. Templates and snippets have their place, but they aren't intended for dredging through your, or your team's, extensive back-catalogue. SSMS templates are fine for general syntax but aren't particularly good for team working, or for large volumes of code and neither are [SQL Prompt's snippets](. There is also the problem of running SSMS while hot-desking. I've tried network-based systems. They're OK, but you probably will want something like the search power of Prompt's Tab History. One utility I've never abandoned, beside SQL Prompt, is Jan Goyvaerts's AceText. In fact, make that three inter-related utilities: AceText, EditPad and Regex Buddy. AceText provides for Windows the clipboard that it should always have had. It provides a huge, permanent list of the text you put on the clipboard, going back years, which you can search easily. You can also categorize what's on that list into other lists. You can edit the text, copy it, format it, save it, or print it. Assuming you remember to copy your routines on to the clipboard as you create or discover them, they are in the system. Once in the Acetext clipboard, you can tidy and sort them at your leisure. The problem, of course, is in making your snippets portable. It also can't extend into a team-based resource. Nowadays, we can generally rely on internet connectivity, so we can use GitHub or OneNote. For teamwork, Github is excellent. My problem is that there are so many good options that I now have my code in lots of different places with no clear idea where the latest version of anything resides, and a maintenance problem when changing PC or hot-desking. Should I feel guilty about archiving code, snippets, templates and so on in lots of different ways? I keep believing that there is a better way of doing it. Perhaps this is just another technological dream, just like reliable [sentiment analysis.]( 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. Webinars [] [The 2019 State of Database DevOps results, live with Donovan Brown!]( - Donovan Brown, Principal DevOps Manager at Microsoft, joins Steve Jones, Microsoft Data Platform MVP to discuss the latest in all things Database DevOps. They offer a closer look at the key findings in the 2019 State of Database DevOps Report, and investigate the growing importance of the database in successful DevOps and IT performance....[(more)]( [] [Why the database is at the heart of DevOps success]( - DORA’s recognition of the critical importance of the database to DevOps in their Accelerate State of DevOps Report should act as a timely wake-up call to those companies that still see DBAs and developers as operating in separate silos. Join Microsoft Data Platform MVP and SQL Server Central Editor Steve Jones to discover how you can build a common understanding and atmosphere of collaboration....[(more)]( [] [DevOps: What, who, why and how?]( - More and more organizations are turning to DevOps as a way of working together to improve the efficiency and quality of software delivery and start adding more value to the business. But what exactly is DevOps and what does it mean for you and your organization?...[(more)]( Virtualization and Containers [] [An Introduction to Docker for R Users]( - Docker is designed to enclose environments inside an image / a container. What this allows, for example, is to have a Linux machine on a Macbook, or a machine with R 3.3 when your main computer has R 3.5. Also, this means that you can use older versions of a package for a specific task, while still keeping the package on your machine up-to-date....[(more)]( [] [How to Check if Your Processor Supports Second Level Address Translation (SLAT)]( - Nearly all systems that are running Windows 10 will run run Docker for Windows with no issues, since they will have a new enough Intel or AMD processor so that SLAT support won’t be an issue. However, if you need to check, here's how....[(more)]( [] [dbatools and docker]( - A Docker Hub to help dbatools users create a non-production environment to test commands and explore the toolset. ...[(more)]( [] [CPU Ready Impact on SOS_SCHEDULER_YIELD]( - Jonathan Kehayias explains how to correlate SOS_SCHEDULER_YIELD waits with signs of a Virtual Machine performance problem, due to high "CPU Ready Time"....[(more)]( Vendors/3rd Party Products [] [Review your SQL Server permissions with SQL Census (in preview)]( - SQL Census allows users to gain a clear picture of SQL Server access, from SQL roles and permissions to Active Directory account and group memberships. The tool provides a simplified view of these permissions, along with the path by which they were gained. There's more to come and you can help shape the future of the tool by joining the preview now....[(more)]( [] [Database DevOPs training]( - Do you need to do more with less as a Database Professional? Why not start your journey with Database DevOPs to assist you in automating tasks so you can achieve greater operational efficiency. Your journey will introduce you to Source Control, Continuous Integration & Continuous Delivery for the Database. ...[(more)]( [] [Protecting Production Data in Non-Production Environments]( - How can you best keep production data secure when it is outside the production environment? Microsoft MVP Grant Fritchey discusses the use of dynamic data masking in production and pre-production servers, and then the strategies that can help us protect the data when it moves into less secure, and less well-controlled environments such as development and test. ...[(more)]( T-SQL [] [Adaptive Join]( - Hugo Kornelis explains the ins and outs of the Adaptive Join operator....[(more)]( [] [Nullable columns and performance]( - We need to talk about the nullable columns in your database. Specifically, because of how NULL values are compared, they can dramatically affect how some lookup operations perform....[(more)]( [] [TSQL : Loop a date range]( - Useful for Incremental processing......[(more)]( [] [Closest Match, Part 2]( - Iterative solutions are typically frowned upon in the SQL community since they don’t follow the relational model. The reality though is that sometimes an iterative approach work better, because you are not limited to the algorithms that the SQL Server optimizer has access to, but rather can implement any algorithm that you like....[(more)]( [] [SQL naming conventions]( - SQL naming conventions for tables, and all the associated objects such as indexes, constraints, keys and triggers, are important for teamwork. Poorly-named tables and other objects make it difficult to maintain databases. ...[(more)]( [] [The risks of using EXECUTE (‘SQL script’)]( - Using dynamic SQL is unavoidable at times, but it is reckless to execute dynamic SQL that is created directly from strings that include values that change at execution time. It can allow SQL Injection and it is also inefficient....[(more)]( [] [The Whys and Wherefores of Untrusted or Disabled Constraints]( - Having untrusted or disabled FOREIGN KEY or CHECK constraints isn’t nearly as bad a fault as not having defined any in the first place. However, it does cause issues with data consistency and integrity, since you can’t be certain that ever row of data complies with the conditions of the constraint. ...[(more)]( [] [Empty Thoughts: Working with NULL]( - Whether or not to have NULLable columns in a table can be a religious debate, and how missing data is represented should be carefully considered during database design. In this article, Joe Celko considers the ways that SQL Server handles NULLs in several situations....[(more)]( [] [Query memory grants part 2: Varchars and sorting]( - What made the optimizer grant 1.4 GB of memory for a sort operation that turned out to need only 25MB? It’s the data types we picked....[(more)]( [] [Generating SQL using Biml (T-SQL Tuesday #110)]( - Biml, Business Intelligence Markup Language, is a programming language that can generate SQL scripts, SSIS packages, SSAS Cubes, Tabular Models, Azure Data Factories and more from your source metadata....[(more)]( [] [Print.. the disruptor of batch deletes in SQL]( - If you use while @@rowcount is greater than 0 to handle batch deletes, be careful. Print statements, and others, will reset @@rowcount back to 0....[(more)]( SQL Server Security [] [Protecting SQL Server Data Using Static Data Masking]( - Databases used by developers and testers must not contain private and sensitive data from production databases. To help avoid this, Microsoft have released a new feature, as part of SSMS, called Static Data Masking....[(more)]( Reporting Services [] [Getting SSRS Details via Powershell]( - PowerShell code to pull back a set of details about every SSRS instance installed on a server including the SSRS instance name, & the first URL port it is running on, the service name and the name of the report server database....[(more)]( PowerShell [] [Multithreading commands]( - In a large organization where you have hundreds of servers to manage and you often face a situation where you want to query all your SQL server estate. For example, checking total RAM and the max memory setting or you want to get version and service pack info for your entire SQL Server estate. Doing these tasks single threaded against a ton of servers is often painful because it takes lot of time!...[(more)]( [] [PowerShell to Simulate Load]( - Gathering metrics is quite difficult if there are no queries. So, if you’re working in non-production environments, but you still want to see some sort of load on the server, how can you do it? I use a simple PowerShell script to simulate load....[(more)]( PowerPivot/PowerQuery/PowerBI [] [Combine All Sheets in a Workbook with Power Query]( - Matt Allington shows how to overcome a couple of tricky issues, such as writing the query in such a way that new sheets are automatically loaded, and handling the errors with repeating header rows coming from the multiple sheets....[(more)]( [] [Power BI Desktop; Getting Started – Part 2]( - Reza Rad adds a touch of modeling and more visualization to the Power BI Desktop guide....[(more)]( Performance Tuning SQL Server [] [Candy Crush and Missing Index Requests]( - Missing index hints are a bit like suggested moves in Candy Crush. Neither are thinking very far ahead....[(more)]( [] [What Indexes Don’t Do: Redux]( - You can read a lot about how indexes might improve queries. But there are certain query mechanics that indexes don’t solve for, and you can not only get stuck with an index that isn’t really helping, but all the same query problems you were looking to solve....[(more)]( [] [Automatic Tuning Monitoring and Diagnostics]( - Evaluating the use of Automatic Tuning in SQL Server using Extended Events....[(more)]( [] [Using Track Causality to Understand Query Execution]( - When dealing with complicated stored procedures, or cases where there’s a lot of redundant code, you often need to know the exact order in which queries ran. Extended Events is a natural choice here. You can often use session_id and the timestamp to understand the order of events, but there’s a session option for XE that’s even more reliable: Track Causality....[(more)]( [] [Optimize for Analytics (Should be a Database Configuration option)]( - Niko Neugebauer describes a feature he would love to have in Azure SQL Database and SQL Server – “Optimize for Analytics”. In this mode, for example, the optimizer would prefer hash joins, batch mode execution for rowstore tables, and more....[(more)]( [] [Consultants: want a utility to gather SQL Server data?]( - When you’re working on a client’s SQL Server – do you waste a lot of time running diagnostic queries, saving their outputs to a file, and then trying to shuffle that data around from place to place? ...[(more)]( [] [Handling Dates in Query Store]( - Erin Stellato explains why it's important to understand how Query Store handles dates, if you’re going to query the data directly....[(more)]( Machine Learning [] [How to automate machine learning on SQL Server 2019 big data clusters]( - How to use automated machine learning (AutoML) to create new machine learning models over your data in SQL Server 2019 big data clusters....[(more)]( HA/DR/Always On/Clustering [] [Replica Failover within the Secondary Availability Group in a Distributed Availability Group]( - A distributed availability group (DAG) is a special type of availability group that spans two availability groups. This blog will clarify some issues regarding failover in a DAG....[(more)]( [] [Availability Group Readable Secondaries – Just Say No]( - Microsoft has made it easy to build an Availability Group, restore and synchronize the databases, join secondary servers, and so on. However, once they are in production usage, you had better know how to troubleshoot problems when they occur, because they will....[(more)]( ETL/SSIS/Azure Data Factory [] [Send Meaningful Errors for SSIS Packages]( - Peter Schott takes Kevin Hill's solution for getting package errors from the SSIS catalog in a single query, and runs with it a little bit....[(more)]( DevOps and Continuous Delivery (CI/CD) [] [The 2019 State of Database DevOps]( - All the latest insights on DevOps adoption rates among SQL Server Professionals, with a foreword from Donovan Brown Principal DevOps Manager at Microsoft. Read the report to understand the challenges – and the opportunities – of adopting database DevOps alongside broader DevOps initiatives...[(more)]( ) Computing in the Cloud (Azure, Google , AWS) [] [Azure – Backing up to URL]( - In a previous post, I talked about some of the available storage options that can be used to back up your databases directly to the cloud. Doing this is a really good way to get your backups off-site and replicated in a single motion, depending on the region and redundancy option select, for minimal cost. So how do you back up to URL, the cloud? ...[(more)]( Administration of SQL Server [] [Automating like an Enterprise DBA]( - Jason Brimhall shares a PowerShell script to audit security on each of your database servers....[(more)]( [] [Executing xp_cmdshell with Non SysAdmin Account]( - Explaining the additional configuration required if you have a non-sysadmin account that needs to run xp_cmdshell....[(more)]( [] [How to capture errors from SQLCMD]( - How to add the -b switch and then add some conditional code in your batch file so that SQLCMD returns the error number....[(more)]( [] [A Monumental Migration to SQL Server 2016 – Part 2]( - In my previous post, I outlined the preparations we undertook to migrate a large SQL Server 2008R2 instance to SQL Server 2016. This post details migration day....[(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 ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com