Reviewing Early And Often, Monitoring, Branching, Junk Dims and so much more

Database Weekly for March 25, 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  Review Early and Often Several years ago, I was brought in on a project to review a database design. I was provided a time for a meeting. No written requirements were available, but I generally knew what the system was supposed to do. No before/after schema images showed what was being changed were available. Still, I was assured that everything would be revealed at the meeting. Yes, my Data Architect/Spidey Sense was buzzing quite loudly. If I had been a contractor deciding whether to take this assignment, I would have likely just politely stood up and walked backward out of the door. It didn't take Svengali to predict the forthcoming train wreck. I put on my confident, civil face and joined the meeting. As I listened to the developers explain their design, I had concerns. It wasn't the worst design ever, but it missed some fundamental database design issues, and some of the logic didn't make much sense to me. I was personally interested not just as a person with the title "Data Architect," but also because I would be involved in architecting the data warehouse components once these changes were implemented. The few changes I could convince them of were places where there was no way that the software would meet the stated requirements, not even with carefully crafted queries they planned to manage the processes. As I asked questions about the design, the developer who did the design defended their database, sometimes with "we already did it this way, can't change," "that is out of scope," and my favorite: "good idea, but we don't have time to make big changes, maybe in the next version." In my 25+ years in IT, I've learned that the only way the next version or any software internals will be different from the current one is if the requirements change. During the meeting, my questions are clearly very annoying to them. Why am I questioning their work? They have already created tables, written much of the interface code, and are close to starting user testing. I don't blame them for reacting to my list of concerns. If you start poking holes in a database (or UI, for that matter) that I have worked hard on and believe I am nearly finished with… I am going to be a bit defensive, too (well, I think the term my boss at the time would have said was "whiny") because I genuinely believe that the work I have done is "awesome." I mean, it has passed all of my tests, right? Who am I? The problem is pretty standard. I think I am an engineer in this process, but all that was expected of me was to be the building inspector. People want to hear: "Yes" or "People are going to die if you do it this way." The Building inspector is there to make sure that what has been built meets wiring standards, plumbing standards, etc. There is no longer time to say: "Wow, this building is less than awesome," "This building won't meet the needs of the tenants," or "...the needs of the tenants in two weeks/years". Just will this kill anyone in the future based on the current standards. In the case of a database design, is there a way this database will work well enough? The building inspector has a checklist that has to be met. Exposed wires? Fail. Wrong size pipes? Fail. Is the room too small for the triplets that will be sleeping in the second bedroom? Nothing in the code about the size of that room handling 3 6'5" basketball players. We are all good. And databases have a lot more "it depends" type rules than they di fixed rules. Even the long-standing normalization rules are more suggestions than rules. (Really, really GOOD suggestions, but still only suggestions. The point When you are building something as foundational as a database design. Get eyes on it. If you have someone around knowledgeable about database design, that is ideal. Even novices that weren't involved in the design may notice difficulties. I have learned a lot from beginners through the years because they don't get fixated on things like poor naming, weird datatypes (why varchar(254) for a name? or numeric(38,2) for a monetary amount? Stuff like that draws my eyes right to it. I may miss more subtle issues!) So please do what you can to have your first reviews with people when the possible answers can be enacted, no matter what. My rule of thumb is that when you are reviewing a design, there should be three base answers to every question about your design: - Great idea. Let me do what I can to meet the requirements better using your suggestion. - That idea is okay, but it does not meet the customer's requirements. - That is not a valid way to solve that problem. A bit more tact is clearly required for that last one, but you get the idea. If you are showing anyone else your work for the first time and can't give these answers, it is too late to be of any value. And then no one will be happy… certainly not your users when they get stuck with your design that will be corrected....next time.  Louis Davidson (@drsql) [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 [NEW: Insights from Redgate]( Find the latest thinking on digital modernization and the role of the database in it in our new resource page. With articles and webinars from Redgate leaders and friends of Redgate. [SQL Prompt Safety Net Features for SSMS: SQL History]( Mistakes occasionally happen. Sometimes you accidentally close an SSMS query tab without saving it, before realizing it contained an essential bit of code. Occasionally, you make some ill-judged 'refinements' to working code and now just wish you could rewind your tab back in time an hour and forget the whole sorry episode. Now and again, SSMS just conspires against you and crashes unexpectedly, and you lose all your currently open query tabs, some of which you hadn't saved. SQL History offers a useful safety net in the event of any of these unfortunate events. [SQL Prompt Safety Net Features for SSMS: Tab Coloring]( The Tab Coloring component of SQL Prompt’s SSMS Tab management could save you from accidentally doing something apocalyptic on a production server, ever again. AI/Machine Learning/Cognitive Services [What Are ChatGPT and Its Friends?]( From O'Reilly Radar - Insight ChatGPT, or something built on ChatGPT, or something that’s like ChatGPT, has been in the news almost constantly since ChatGPT was opened to the public in November 2022. What... [Google Bard Plagiarized Our Article, Then Apologized When Caught]( From Tom's Hardware US When I asked Google's bot to compare two recent CP... Administration of SQL Server [Why a monitoring tool is an essential tool for growing server estates]( As we continue into the era of supporting more and more database platforms in our data estates, self-monitoring goes from being a very difficult task to a truly daunting task. I am personally advanced at diagnosing Microsoft SQL Server performance issues, but when that PostgreSQL server gets added, I just have to hope nothing goes wrong for a few years while I learn. [Try or run Azure SQL Managed Instance for $100 per month]( From Azure SQL Running a SQL Managed Instance can be cost effective, no matter if you are just validating the service, developing, or running your production. In this article we will demonstrate... [Database Branching and Merging without the Tears]( From Product learning – Redgate Software Armed with a schema comparison engine and an object-level directory of the source for every recent version of the database, you'll be able to remove a lot of the... Data Warehousing [What’s a JUNK DIMENSION and when should you use it?]( From Guy in a Cube Maybe you've heard of the concept of a junk dimens... ETL/SSIS/Azure Data Factory/Biml [Data Transformation and Migration Using Azure Data Factory and Azure Databricks]( From MSSQL Tips In this article, learn how to use Azure Data Factory and Azure Databricks for data integration to manage a variety of data formats, including batch-streaming and unstructured and structured... Performance Tuning SQL Server [Tuning SQL Server Queries With Filtered Indexes, Computed Columns, And Indexed Views Recap]( From Erik Darling Data Tuning SQL Server Queries With Filtered Indexes, Computed Columns, And Indexed Views Recap Going Further If this is the kind of SQL Server stuff you love learning about, you’ll... PowerPivot/PowerQuery/PowerBI [Visually see where you BROKE the fold in Power Query]( From Guy in a Cube Rule #1 - DON'T BREAK THE FOLD! There is an amazin... [Refreshing a Power BI Dataset using an HTTPS URL]( From FourMoo I have found that sometimes there are other systems that are loading data, and once they are complete they then want to refresh the Power BI Dataset. Another way... [Power BI TMDL And Version Control Announcements From SQLBits]( From Chris Webb's BI Blog If you missed out on the big announcement last week about Tabular Model Definition Language and the future of Power BI version control at SQLBits last week, then the... Professional Development [Women in Tech Is More Than a Corporate Training Exercise]( From IT Pro - Microsoft Windows Information, Solutions, Tools Why aren’t more women entering and staying in tech? Carrie Goetz proposes how to fix the lack of gender parity in the data center industry. SQL Server Security and Auditing [Best Data Governance Software and Tools]( From Past News - RSS Feeds Data governance tools are software applications th... Security News and Issues [Bug Makes Windows 11 Snipping Tool Images Recoverable After Editing]( From Tom's Hardware US Images cropped or edited using Windows 11's Snippi... [Mass Ransomware Attack]( From Schneier on Security A vulnerability in a popular data transfer tool has resulted in a mass ransomware attack: TechCrunch has learned of dozens of organizations that used the affected GoAnywhere file transfer software... T-SQL and Query Languages [Paging Data in T-SQL]( From Simple Talk Sometimes when working with very large sets of dat... [Use DDL Triggers to Automatically Keep SQL Server Views in Sync]( From MSSQL Tips Learn how to use a SQL Server DDL trigger to automatically refresh SQL Server views when underlying tables are modified. Virtualization and Containers/Kubernetes [Updating SQL Server containers deployed on Kubernetes!]( From MS SQL Server Blog I'm sure you've thought about how to update SQL Server containers running on a Kubernetes cluster at some point. So, this blog attempts to answer the question. According to...  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -