Best Practices and Band Plans (Database Weekly 2019-08-03)

 Database Weekly for August 3, 2019 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge Editorial  Best Practices and Band Plans Allow me to suggest a few database best practices: - Use enforced referential constraints in your relational databases - Write your code to avoid calculations on columns in the WHERE and ON clauses - Do not enable auto-shrink on the database There are lots of other best practices I could recommend, but we’ll go with these three. To my mind, these are reasonable statements, and I’m making them based on years of experience, knowledge, study, and the advice of experts that I trust (due to their years of experience and knowledge). However, you’ll find that not everyone can agree. Further, some people have decided that, for example, foreign key constraints slow down development, so we’re tossing them. Others that we can’t afford to buy a disk, and keeping the databases shrunk is vital, so we’ll let them shrink every chance we get. I’ve literally heard the statement “Who are you to tell me different?” when it comes to best practices. Short answer: Nobody. But I’m Still Right. Let’s look at something related, but utterly different: Band Plans. I’ve been working with Ham radios for about year since I got my Technician’s license. I’m actively studying for my General license (and will probably pass the test before this gets published). While the licensing is through the United States government by way of the Federal Communications Commission (FCC), that’s as far as government involvement goes. Volunteers manage the rest of Ham radio within the US. While the radio waves can appear to be quite vast, they are a limited resource. To better manage this resource, the Ham radio volunteers have established several best practices. The best practices I’m referring to at the moment are the Band Plans. These define what kinds of radio signals you can send within the spectrum — Upper Side Band above 10mhz and Lower Side Band below. Phone (voice) only between 24.93mhz and 24.99mhz, but RTTY (don’t ask) and data only between 24.89mhz and 24.93mhz. The FCC enforces none of these. These are all volunteer defined best practices to which a good Ham operator adheres. However, be sure, the other Ham operators will point out when you’re violating the Band Plans. The best practices, despite not being hard and fast laws with punishment meted out by the government are still managed. Could I decide that, instead of following the band plan, I’m going to transmit via phone (talking) way down in the data segment between 24.89mhz and 24.93mhz? Yes. Unfortunately, since no one will be looking for phone communications down there, it’s going to be awfully lonely until I step on someone’s data signal and I get an education from my fellow Hams. Frankly, the best practices for databases should be equally well enforced. Yeah, sometimes, someone is going to make up a silly “best practice” that really shouldn’t be one (I’m old enough to remember when I was told that anything more than three joins was against best practices. It was silly 25 years ago, and it’s silly now). However, most of what is defined as best practices are good guidelines for the majority of your operations. In a true emergency, can I transmit pretty much anywhere I please? You bet. In a real emergency, can you violate any of these best practices? Absolutely. However, is this a true emergency, or are you just unwilling to realize that, maybe your development practices are to blame, not foreign key enforcement? I’m able and willing to support any violation of best practices you’re willing to show me, but you have to back it up with valid reasons, not just because it was an easy, short term choice. We data professionals should define our Band Plans and help everyone to operate within them.  Grant Fritchey [Join the debate, and respond to the editorial on the forums](  [Redgate SQL Change Automation]( 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. Vendors/3rd Party Products [Database Build Blockers: Mutually Dependent Databases]( Phil Factor demonstrates a clever way to create 'stub' objects, in SQL Change Automation pre-deployment scripts, in order to overcome the problems caused by 'missing objects' when building databases that have circular, or mutual, dependencies. In the subsequent deployment, SQL Change Automation fully builds every object in each of the databases, so all dependencies are fully tested. [Integrating Redgate SQL Code Guard with SonarQube]( Greta Rudžioniene provides a step-by-step guide to running SQL code analysis checks, as defined by SQL Code Guard, in SonarQube, a general-purpose code quality tool that can perform continuous code analysis on a variety of languages. [4 Pillars of SQL Server Monitoring]( Five SQL Server experts have brought their collect... .NET Related Articles [ASP.NET Core 3.0 Exception Handling]( Dealing with errors is one of the essential elements of programming applications. The way to do so has changed over the years and versions of ASP.NET. In this article, Dino Esposito discusses how handle exceptions with ASP.NET Core 3.0. AI/Machine Learning/Cognitive Services [From a Single Decision Tree to a Random Forest]( Click to learn more about author Rosaria Silipo. T... Administration [DBA Training Plan 6: How to Monitor Drive Space]( Don’t. It’s not your job. No, seriously, hang ... [MaxDOP Added to SQL 2019 CTP3.0 Setup]( You can now set MaxDOP during SQL 2019 CTP3.0 setu... [DBA Training Plan 7: How SQL Server Stores Stuff in MDF Files]( So far in the DBA Training Plan, we’ve been work... [SQL SERVER – Error 35296: Log Backup for Database “SQLAuthority” on Secondary Replica Failed Because the New Backup Information Could Not be Committed on Primary Database]( Log backup for database "SQLAuthority" on secondar... [dm_db_database_page_allocations]( sys.dm_db_database_page_allocations is an undocume... [tempdb Enhancements in SQL Server 2019]( Erin Stellato uses a simple example to demonstrate... [How to Corrupt a Database]( Microsoft goes to great lengths to ensure data int... [Undocumented Commands Can Change]( Thomas Rushton warns us that undocumented commands... [Configure a SQL Server Agent Operator]( SQL Server Agent operators are used by SQL Server ... [The Ironic DBA—Episode 8: The Last Rebuild]( Welcome back to The Ironic DBA Files, a series whe... [Redgate Data Masker]( Azure DevOps [Sample Azure DevOps Pipelines]( I’ve said it before and I will repeat myself on ... [Sample Azure DevOps Pipelines]( I’ve said it before and I will repeat myself on ... Azure SQL Database [Data on demand: Azure SQL Database in serverless mode]( Azure SQL Database has a new “serverless” mode... Career Growth and Certifications [Learning to Manage: Recovering from a crash]( One year and nine months ago, I took my first form... Computing in the Cloud (Azure, Google, AWS) [Learn Azure Part 0 – Create an Azure Account]( If you are interested in learning Azure, cool! I a... [Google Makes it Easier to Migrate VMs from Azure to Google Cloud]( Google has announced a new feature for its cloud p... [At a High Level: AWS vs. Azure vs. Google Cloud]( eWEEK RESOURCE PAGE: The three largest cloud compu... [Finding the Best Azure Datacenter]( When dealing with cloud technology there is a phra... Conferences, Classes, Events, and Webinars [How to boost team productivity with SQL Clone 4]( Wednesday August 21 16.00-17.00 BST/ 10.00-11.00 Central - SQL Clone enables dev, test and CI environments to be created and refreshed in seconds, on demand or through self-service, with the latest copy of production data, masked for compliance. [Fast and Reliable Development with Redgate Solutions for SQL Server]( Thursday August 29 18.00-19.00 BST / 10.00-11.00 Pacific - Register for this Microsoft hosted webinar to learn how Redgate tools for SQL Server help deliver software quickly. [SQL in the City Streamed- the birthday edition]( Wednesday September 4 14.00-19.00 BST/ 08.00-13.00 Central - Register for our free virtual learning event, to enjoy educational and entertaining sessions from Microsoft MVPs and celebrate 20 years of Redgate. [Presenting From Zero to ADF at SQL Saturday Atlanta – BI Edition]( I am honored to deliver my brand-spanking-new full... [Presenting Moving Data with ADF at TriPASS 20 Aug]( I am honored to deliver Moving Data with ADF to th... [SQL SERVER – Pre-Con 21 Essential Scripts in Prague and Amsterdam]( My pre-conferences (a day-long training in Prague ... [Free SQL Server DBA training – Dallas 2019]( Save the Date! September 18, 2019 – Dallas, TX 8... Data Privacy, Compliance, and GDPR [Privacy group asks court to reconsider FTC’s $5 billion Facebook deal]( The settlement does nothing to address the underly... Data Science [Make data science more useful]( The O’Reilly Data Show Podcast: Cassie Kozyrkov ... DevOps and Continuous Delivery (CI/CD) [Learning from the Accelerate “Four Key Metrics”]( There’s been a lot of excitement about the book ... HA/DR/Always On/Clustering [AlwaysOn Availability Groups: Quorum]( SQL Server AlwaysOn Availability Groups is Microso... Hardware [Liqid Element Unveils "LQD450" PCIe 4.0 x16 SSD: 32 TB At Up to 24 GB/s]( Liqid, a maker of SSDs for mission critical and pe... [Intel reveals final details on Ice Lake mobile CPUs]( Ice Lake boasts significant power savings and much... MDX/DAX [Cracking DAX – the EARLIER and RANKX Functions]( So far in this series, Andy Brown of Wise Owl Training has shed light on functions like CALCULATE, VALUES and FILTER, but it’s only when you understand the idiosyncrasies of the EARLIER function that you can claim to have genuinely cracked DAX. This article gives four examples of the use of this peculiar function, in the process explaining why it has such a misleading name. Using the EARLIER function properly all boils down (as is so often the case with DAX) to understanding row and filter context. The article also shows how to use the RANKX function to sort data into your required order. [Cracking DAX – the EARLIER and RANKX Functions]( So far in this series, Andy Brown of Wise Owl Trai... [The FILTER Function in DAX Part 2]( Level: Intermediate In my last article, I introduc... [Filtering in DAX]( Matt Allington takes us through the FILTER() funct... Machine Learning [The tools you should know for the Machine Learning projects]( I would like to start my first Machine Learning pr... [An Intro to k-Means Clustering]( Holger von Jouanne-Diedrich takes us through an ex... Performance Tuning SQL Server [Find queries with high memory grants – using Query Store]( I had a server that looked like it had been suffer... [Spills Week: Hash Join Humiliation]( Thirsty Thursday If you’ve made it this far, you... [Memory Consumption by Object]( SQL Server caches object data in memory in the buf... [Spills Week: Exchange Spill Excruciation]( Many Have Entered, Few Have Left For some backgrou... [Execution Plans: Statistics]( Watch this week’s Statistics video on YouTube. L... [Spills Week: When Sort Spills Start To Matter]( Imbalance In yesterday’s post, we looked at a fu... Polybase/HDInsight [PolyBase and Azul Zulu OpenJDK]( One of the more interesting parts of SQL Server 20... [PolyBase and External Column Names]( The SQL Server 2019 CTP 3.2 release notes includes... PowerPivot/PowerQuery/PowerBI [Power BI Licensing]( Power BI licensing options are a mystery to many. ... [Why Power BI Shared Datasets are Critical to a Self-Service BI Implementation]( This post discusses new features released in May 2... [Some Simple Ways to Debug Your DAX Measure Code in Power BI: Debugging Virtual Tables]( Debugging DAX codes can be sometimes a bit of chal... [Siri Shortcuts with Power BI Mobile for iOS]( Patrick shows you how to create Siri shortcuts wit... [Get Images from Web Page Into the Power BI Report Using Power Query]( Power Query can connect to a web page and get data... [Check Out the Updated Violin Plot Power BI Custom Visual]( I wrote about the violin plot custom visual by Dan... [Easy Navigation with Power BI]( Marc Lelijveld has started a series on storytellin... PowerShell [Data Aggregation with Powershell]( Jess Pomfret shows us how we can aggregate data us... [Collecting Server Performance Metrics: PowerShell]( In a recent post I wrote about collecting server p... Product Reviews and Articles [Resolving Cross Database Dependencies in SQL Change Automation using Local Databases or Clones]( Kathi Kellenberger explains how to avoid build pro... [Managing Data Masking Rules in Larger Teams using Source Control]( What if you have several people in the team who ar... Product Upgrades and Releases [Microsoft ML Server 9.4 now available]( Microsoft Machine Learning Server, the enhanced de... R Language [Program Evaluation: Interrupted Time Series in R]( Are you interested in guest posting? Publish at Da... [Multilevel Models in R]( Are you interested in guest posting? Publish at Da... [Sentiment analysis in R]( Are you interested in guest posting? Publish at Da... [Propensity Score Matching in R]( Are you interested in guest posting? Publish at Da... Replication [Automating Replication On Your Server]( If you use replication, you have had the situation... [Tearing Down and Rebuilding Replication]( Pamela Mooney takes us through tearing down replic... Reporting Services [Creating Professional SSRS Report based on Stored Procedure]( This article talks about creating a professional S... SQL [PolyBase in SQL Server 2019 – The End of ETL?]( PolyBase has been around since 2016, but Microsoft is enhancing it in 2019 with the ability to connect to more data sources. You can now use PolyBase to connect to other SQL Server instances, and it’s easy to set up with a wizard. In this article Benjamin Weissman explains what’s new in PolyBase and walks you through setting it up and connecting to an external table. [What are Columnstore Indexes?]( Microsoft continues to enhance the performance of SQL Server with new features. In this article, Monica Rathbun explains how to work with columnstore indexes, a different way to store tables that can drastically improve the performance of specific workloads. SQL Server News [sp_WhoIsActive Is Now Open Source.]( If you’re still using sp_who, sp_who2, and Activ... [Glenn’s Tech Insights For July 29, 2019]( (Glenn’s Tech Insights… used to be part of our... Security News and Issues [Cybersecurity officials warn state and local agencies (again) to fend off ransomware]( Three Louisiana school districts, Georgia agency p... [Three Cs to secure against leaks like Capital One]( It was revealed today that about 100 Million recor... [Another Attack Against Driverless Cars]( In this piece of research, attackers successfully ... [New advanced malware, possibly nation sponsored, is targeting US utilities]( Dear Engineer. You failed your licensing exam. Ope... [Cisco pays $8.6 million for selling surveillance system it knew was vulnerable]( Whistleblower said Cisco waited more than 4 years ... Software Development [SQL server and Java?]( I was generally reading about SQL Server and how t... [Global Query Filters for Interfaces]( This post describes how to apply an Entity Framewo... T-SQL [How to Remove Quotes of QUOTENAME? – Interview Question of the Week #235]( Question: How to Remove Quotes of QUOTENAME? I re... [Why you Should Avoid Using the @@IDENTITY Function]( Phil Factor demonstrates why SQL Prompt has a 'Bes... [Optional vs Required parameters in Stored Procedures]( If you are executing a stored procedure with a bun... [How to store a password in a database]( Hello, and welcome to today’s class on storing p... [Using Dynamic SQL inside a Stored Procedure]( Preventing direct access to database tables for us... [Nonprintable ASCII Characters And Your Data]( The ASCII character set is comprised of 128 charac... [Winners and Solution – DELETE Qualified Rows From Multiple Tables]( As the price of this class high, I wanted to keep ... [LIKE Pattern Matching with Brackets]( Madhivanan shows how you can find special characte... The Lighter Side [Header Query Crossword]( I did an interesting form of crossword recently an... [The SLS rocket may have curbed development of on-orbit refueling for a decade]( "Boeing became furious and tried to get me fired."... Virtualization and Containers/Kubernetes [More Kubernetes Concepts]( This post continues looking at my process of learn... [Copy Files to a Docker Container]( One of the things that’s handy when working with... [Docker Desktop for WSL 2 integrates Windows 10 and Linux even closer]( Being able to seamlessly run Linux on Windows is m...  [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. 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 follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -