Coding Magic Values (2023-02-04 DBW)

Database Weekly for February 4, 2023 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  Coding Magic Values It's 2022. I would hope all software developers would learn that hard coding specific values in your system is more likely to cause issues than not. Across the years, we've learned not everything is installed on the c: drive, or that not everyone wants to put all data in a Documents folder (or in OneDrive). We've learned that any sort of magic number is poor practice, and we ought to know that hard-coded names are problematic as well. Yet, we still see it happening. This week I was reading about [an admin issue in the Microsoft TechCommunity.]( This is related to Azure Managed Instance, but it's really an In-Memory OLTP issue. That was introduced in SQL Server 2014, so I know the code for this was likely written in the 2011-2013 timeframe, but how can this type of issue get through code review and be released? In this case, the name of a filegroup is set specifically to XTP. It's a logical name, and I'm sure that some developer thought that things might be faster with a known location. That doesn't make sense, and while this might not be an issue for most customers, I'm sure there have been some databases built with a filegroup called XTP. After all, there are companies named XTP. What about if this feature evolves to allow a second filegroup, maybe because of some distributed architecture need in the future? Are there then code paths looking for XTP or XTP2? As much as possible, avoid coding values in your code that a user might enter as data. Names, paths, etc. Just don't do it. Use variables, which are in every language, and let those values be read from the environment. This ensures that you don't end up with weird support requests from customers because they chose the same value you did. Steve Jones - SSC Editor [Join the debate, and respond to the 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. Vendors/3rd Party Products [How do your DevOps plans really measure up?]( If you’re championing a DevOps implementation in your organization, or wish to see where your current processes measure up against your peers, read on. [Complex Production Database Deployments and Flyway]( This article explains how, by use of schemas and stub interfaces, we can use Flyway to manage the main development work smoothly alongside any changes or additions required to maintain production-only code. [Optimizing Queries in MySQL: Optimizing Reads]( Optimizing read operations is one of the most prominent problems concerning any database administrator. In this blog, we’re going to walk you through how to solve the issues posed and at the end of the article, we’ll also walk you through some DDL operations to load test data. [AWS Lambdas with C#]( In this article, I will take you through the development process of building an API on the serverless cloud with C#. This API will be built to serve pizzas, with two endpoints, one for making pizza and the other for tasting fresh pizzas. I expect some general familiarity with AWS and serverless computing. Any previous experience with building APIs in .NET will also come in handy. AI/Machine Learning/Cognitive Services [On AI, GPT and the future of developers]( From Ayende @ Rahien When I started using GitHub Copilot, I was quite amazed at how good it was. Sessions using ChatGPT can be jaw dropping in terms of the generated content. The immediate... Administration of SQL Server [SQL Server Wait Type: PREEMPTIVE_OS_CRYPTACQUIRECONTEX]( From Steve Stedman Wait statistics, in the context of SQL Server, refer to the amount of time that a query spends waiting to access data in the database. When a client application... [5 common SQL Server Problems to Troubleshoot with sp_WhoIsActive]( From StraightPath Solutions SQL Blog If there were a Mount Rushmore of SQL Server community tools, Adam Machanic would have to be one of the four faces. You may not know his name, but... [Lesson Learned #314: Cannot start the job 'XXX' because it does not have any job server]( From Azure Database Support Blog Today, we had a new service request where our customer receives the following error when they try to run a created job in SQL Managed Instance: Cannot start the job... [Recovering a TDE Database Without the CertificateÂ]( From SQLServerCentral Blogs If you don’t have the backups of the certificate and private key from the old server, as well as the password used to encrypt the private key backup then... The... [Tempdb Performance Improvements in SQL Server 2022 are Dramatic]( From SQLServerCentral Blogs Tempdb is always a topic for me whether it’s in my sessions or blogs I have written. However, I’ve never been so excited about it then I am when... The... [Lesson Learned #315: 'XTP' name is reserved for the files containing In-Memory OLTP data.]( From Azure Database Support Blog Today, we worked on a service request that our customer got the following two error messages: The file name 'XTP' is reserved for the files containing In-Memory OLTP data or The... [Lesson Learned #316: Connecting to Private Endpoint of Azure SQL from OnPremise using NGINX]( From Azure Database Support Blog Today, I got a question from a customer that they want to connect from their OnPremise environment to Azure SQL Database Private Endpoint. I would like to share with you... [Ola Hallengren Maintenance part 1]( From Ben Miller's World Many of us are using Ola Hallengren’s maintenance solution. This post will assist you in configuring this solution when you add it to your SQL Servers. Jobs are created... Azure Databricks, Spark and Snowflake [Sound of Silence in Snowflake]( From Sherpa of Data One of those functions that can be helpful – in the right circumstances – is SOUNDEX. If you hop into the Wayback Machine with Sherman, Mr. Peabody, and me,... Azure SQL Managed Instance [Lesson Learned #313: how to configure Azure SQL Managed Instance Link]( From Azure Database Support Blog In this video, our colleague Kenny Yu is going to show us how to configure Azure SQL Managed Link.  Career, Employment, and Certifications [SSIS Interview Questions and Answers for Developers and DBAs]( From MSSQL Tips This article contains over 50 questions that can h... [Presenting On Basics Is Difficult]( From Scary DBA (Grant Fritchey) Over the last year, at work and after work, I’ve... Community Interests [Introducing… Data On Rails!]( From Data on Wheels (Steve Hughes) Data On Rails is a Data On Wheels project designed to give a platform for up and coming data professionals in the data community. This project is the brainchild... Computing in the Cloud (Azure, Google, AWS) [Azure AKS when your DNS servers are in a different Azure vNet]( From DCAC If you have an Azure environment when you have your DNS servers in a separate vNet from your new AKS environment you’ll notice that you get an error when... [Lesson Learned #318: Using Archive Access Tier in a blob storage]( From Azure Database Support Blog In case that you are using Archive Access Tier res... [New – Deployment Pipelines Reference Architecture and Reference Implementations]( From AWS News Blog Today, we are launching a new reference architectu... [Deploy SQL Server in Google Cloud]( From MSSQL Tips Learn how to deploy SQL Server instances on a virtual machine (VM) inside the Google Cloud Platform (GCP) in this step-by-step article. Conferences, Classes, Events, and Webinars [Join us in our castle at SQL Bits, March 15-18]( SQL Bits is coming to Wales, home of dragons and D&D, and we’re happy to announce we’re supporting them as platinum sponsors again this year. Meet us at our Red Keep and catch sessions by noble Fighter Grant Fritchey, Sorcerer Steve Jones, and more beloved Redgate Paladins and Druids. For a magical 10% discount on your ticket, use 10REDGATE from our spell book. [Network, Develop & Share @ SQLBits 2023 | Data Exposed Exclusive]( From Azure SQL SQLBits, one of the largest data conferences in the world, will be taking over the ICC Wales for a marathon of top-quality training from global specialists. With a brand-new... [SQL Performance Tuning Training]( From Steve Stedman In this free training I have poured my 30+ years of SQL Server experience and personally curate my favorite performance tuning tips, tricks and tactics. I am excited to... Data Mining / Data Analysis [Creating a Data Sandbox]( From Paul Andrew Data Product Infrastructure as a Service Another b... Data Privacy, Compliance, and Governance [The Case for a Strong Data Governance Program in 2023]( From IT Pro - Microsoft Windows Information, Solutions, Tools As businesses handle ever-expanding amounts of dat... Data Science [The End of the Full-Stack Data Scientist]( From IT Pro - Microsoft Windows Information, Solutions, Tools Organizations should start to rethink how data science experts collaborate and communicate with each other, but a tight IT market means offering opportunities other businesses won't. Data Visualisation [Tips for making colorblind-safe statistical graphs]( From AllAnalytics Did you know that about 8% of the world's men are ... Hardware [Intel Discontinues Optane SSD DC P4800X Drives]( From Tom's Hardware US Intel says it will cease selling Optane SSD DC P48... [Samsung Portable SSD T7 Shield 4TB Review: IP65 PSSD Gets a Capacity Upgrade]( From AnAndTech Samsung has been enjoying market success with their lineup of portable SSDs, starting with the T1 back in 2015. The company has been regularly updating their PSSD lineup with... [Backup Company Reveals Hard Drive Failure Rates For 2022]( From Tom's Hardware US Backblaze has published the company's annual repor... [DirectStorage Testing Shows PCIe 3 SSDs Are Basically as Fast as PCIe 5]( From Tom's Hardware US A comparison in the Forspoken benchmark reveals that, with DirectStorage, PCIe Gen3 SSDs are not considerably slower than PCIe Gen5 SSDs when it comes to load times and real... MDX/DAX [Understanding Time Intelligence with DAX]( From Sqlbi Every Power BI model has dates and the need for ca... [Introducing window functions in DAX]( From Sqlbi In December 2022, DAX was enriched with window fun... [Introducing window functions in DAX]( From SQLBI This video introduces the syntax and functionalities of the new DAX window functions: INDEX, OFFSET, and WINDOW. Oracle/PostgreSQL/MySQL/other RDBMS [PostgreSQL Constraints: Learning PostgreSQL with Grant]( From Simple Talk One of the most important aspects of data management is the ability to ensure that the data in your database is well defined and consistent. Some aspects of that... [Optimizing Queries in MySQL: Optimizing Reads]( From Simple Talk Optimizing read operations is one of the most prominent problems concerning any database administrator. No matter what kind of database management system is in use – MySQL, its flavors... Performance Tuning SQL Server [Query Tuning Essentials: SARGable Lecture]( From Erik Darling Data +OK [SQL Server 2022: Built-in query intelligence [Ep. 3] | Data Exposed]( From Azure SQL In this episode of Data Exposed with Anna Hoffman and Bob Ward, take advantage of performance and availability for faster queries and to help ensure business continuity. Accelerate query... [11 Essential Tips for Avoiding Common SQL Server Performance Tuning Mistakes]( From Journey to SQL Authority with Pinal Dave This blog post will cover 11 tips for avoiding common SQL server mistakes. From indexing to security, these tips will help you optimize your database's performance. First appeared on 11... [Query Tuning Essentials: Implicit Conversion Demo]( From Erik Darling Data +OK PowerPivot/PowerQuery/PowerBI [Top Power BI Interview Questions and Answers]( From SQLShack Microsoft Power BI is a very popular data visualization tool. In this article, we will discuss the most frequently asked Power BI Interview Questions and Answers. What is Power... [Automating the backup of Power BI Premium datasets/databases – Part 1]( From FourMoo Use the script from Azure Runbook Can use this as reference: Steps on how I backed up my Power BI Premium Per User / Premium Database – FourMoo |... [Multiple audiences for Power BI apps, will it change your workspace setup?]( From Data – Marc Back in August 2022, Microsoft released a feature to have multiple audiences for Power BI Apps, or if you will deploying multiple versions of the same app where you... Product Reviews and Articles [Recording What’s Changed when Running Oracle Migrations with Flyway]( From Product learning – Redgate Software This article uses Flyway and a PowerShell framewor... [Flyway Desktop Projects for My PoC]( From SQLServerCentral Blogs In a previous post, I set up the basic databases f... Product Upgrades and Releases [Microsoft Drivers 5.11.0 Beta 1 for PHP for SQL Server Released]( From MS SQL Server Blog We recently released the next beta of the Microsof... R Language [Tips for organising your R code]( From TomazTsql Keeping your R code organised is not as straightforward as one might think. Just think about the libraries, variables, functions, and many more. All these objects can be defined... SQL Server Security and Auditing [Lesson Learned #312: Cannot drop the login 'xyz', because it does not exist or you do not have]( From Azure Database Support Blog Today, our customer got the following error messag... Security News and Issues [Nearly All Firms Have Ties With Breached Third Parties]( From IT Pro - Microsoft Windows Information, Solutions, Tools The average organization does business with 11 third parties, and 98% of organizations do business with a third party who has suffered a breach, an analysis finds. [10M JD Sports Customers' Info Exposed in Data Breach]( From Dark Reading: Dark Reading News Analysis UK sportswear retailer asks exposed customers to s... [Critical VMware RCE Vulnerabilities Targeted by Public Exploit Code]( From Dark Reading: Dark Reading News Analysis Security vulnerabilities in VMware's vRealize Log Insight platform can be chained together to offer a cybercriminals a gaping hole to access corporate crown jewels. [Passwords Are Terrible (Surprising No One)]( From Schneier on Security This is the result of a security audit: More than a fifth of the passwords protecting network accounts at the US Department of the Interior—including Password1234, Password1234!, and ChangeItN0w!—were weak... T-SQL and Query Languages [json_object and json_array Functions SQL Server 2022 Tutorial]( From MSSQL Tips Learn about the new functions JSON_OBJECT and JSON... [Common use cases of SQL SELECT Distinct]( From SQLShack This article talks about the commonly used scenarios of SQL Select Distinct in an easily understandable format equally suitable for beginners and professionals. The article also brings into light... [Fun With KQL – Max, MaxIf, Min, and MinIf]( From Arcane Code Fun With KQL - Max, MaxIf, Min, and MinIf [CONCAT_WS Function Truncates Text]( From SQLServerCentral Blogs I was writing some dynamic SQL that generates some SQL statements to load my facts and dimensions into a data warehouse. Some of those SQL statements can become very... The... [Dynamic SQL: Sessions and Execution]( From Deb the DBA I admit it – I do waaayyyy too much with dynamic SQL. But I just keep running into situations that require it. That being said, I ran into an... [Ready to be a Kusto Detective with Azure Data Explorer?]( From Guy in a Cube Your detective journey starts with the Kusto Detective Agency for Azure Data Explorer. This is a really fun way to learn the Kusto Query Languate (KQL)! Patrick gets you... [Context Info Across Databases–#SQLNewBlogger]( From SQLServerCentral Blogs Does Context Info work across databases? This post shows it does. Another post for me that is simple and hopefully serves as an example for people trying to get... The... Tech News [Intel Cuts Wages and Suspends Bonuses Following Catastrophic Quarter]( From Tom's Hardware US Intel lowers its expenses by cutting employee compensation by 5% – 25%. [Microsoft Stops Selling Windows 10 Digital Downloads]( From Petri IT Knowledgebase Microsoft has officially stopped selling downloadable licenses for Windows 10...  [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -