Getting Your DBA Teams Scripts into Git (2024-07-01)

SQLServerCentral Newsletter for July 1, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Getting Your DBA Teams Scripts into Git]( - [Concepts and Issues in Test Data Generation]( - [From the SQL Server Central Blogs - MSSQL Database Property LastGoodCheckDbTime to Get the Last Successful DBCC CHECKDB on a Database]( - [From the SQL Server Central Blogs - Dynamic Warehouse & Lakehouse Connections in Microsoft Fabric Data Pipelines]( - [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Question of the Day - [Database Mail Editions]( The Voice of the DBA  The Cost of Rework I often deal with customers who are looking to improve the way they build and manage database software. These could be small companies or large enterprises, with teams of developers trying to enhance their application software to solve new business problems. Often those enhancements require new data, with the related schema changes in a database. Even if there isn't any new data, often we need to query data in new ways, combing, filtering, aggregating, and otherwise transforming data into extra information that a business can use. Solving many of these problems is iterative by nature. Often a developer might experiment with a data model or query, trying to match a requirement they've been given. Once they produce a solution, we may find problems in testing, or far too regularly, in production. These could be data-related, where the developer hasn't considered values in their solution (zeros, blank or long strings, extreme dates, etc.). These could be logical errors, where the developer just made a mistake. There could also be a problem interpreting the requirement, or potentially the customer provided an incomplete or incorrect specification to the developer. In any of these cases, what often happens is rework. We need to re-look at the code we've written and change it in some way. This isn't a complete rewrite, but looking for the logical error, incomplete algorithm, or poorly performing structures. Then we need to (test) it and move it back through our process of deploying to QA, UAT, production and anywhere else the problem exists. The further we are in the process (closer to production) the more costly the effort to rework code. Some rework is unavoidable, but I think quite a bit isn't. If we wrote better-performing and cleaner code the first time and had a pattern for testing our code, we might avoid a fair amount of rework. I think that often developers minimize the effort required to perform rework. They think small changes are easy. After all, we already know the problem space, we've looked at it, and the fix should be quicker than the initial analysis and development. Or is it? One of the things I've seen is that everyone is busy. We are pulled in many directions by many different people and work on lots of different problems. Even when we work on the same application, it takes some time to remember the context under which we wrote the code. If it's not our code, then we spend time trying to understand how the original developer approached the problem. Without good specifications or tests, we might not view things the same and fix one problem, but create another. Automated testing should help here, as the tests should codify the understanding of the requirements, but too few database developers use automated testing for their code. Even developers who embrace testing from the application might not have sufficient test data to ensure their code will work in production. Most of us are expected to do more work to do this year than last year. We're constantly battling a lack of resources. We can't necessarily write more code, but we can learn to be more efficient. We can learn to reduce our rework by writing code better the first time. That takes some effort and knowledge. It takes working as a team, sharing successes and struggles, and adapting our code to use patterns and avoid anti-patterns. It requires some automation to embrace static code analysis and testing to avoid silly mistakes getting deployed and wasting time. More importantly, it takes support from management to spend time learning as a team to produce better code and avoid silly mistakes. A little investment in learning and practice goes a long way. Unfortunately, too many people, both staff and management, are unwilling to commit. Except in fixing bugs and spending time on rework. Far too many people see that as an regular, accepted part of software development. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Getting Your DBA Teams Scripts into Git]( Steve Jones - SSC Editor from SQLServerCentral In this article, you will learn how a team can start to better manage the scripts they use for their daily work. [External Article]( [Concepts and Issues in Test Data Generation]( Additional Articles from SimpleTalk Data generation is the science and art of providing data for database development work that is as realistic and controllable as possible. The skills of generating realistic data are an essential part of being a database developer. It is important: you need plenty of data of exactly the right type, size and verisimilitude [Blog Post]( From the SQL Server Central Blogs - [MSSQL Database Property LastGoodCheckDbTime to Get the Last Successful DBCC CHECKDB on a Database]( SQLPals from Mission: SQL Homeostasis Database Property LastGoodCheckDbTime to Get the Last Successful DBCC CHECKDB on a Database As one of the preventive database maintenance tasks, performing database integrity checks on almost every database you... [Blog Post]( From the SQL Server Central Blogs - [Dynamic Warehouse & Lakehouse Connections in Microsoft Fabric Data Pipelines]( Koen Verbeeck from Koen Verbeeck When you develop data pipelines in Microsoft Fabric (the Azure Data Factory equivalent in Fabric, not to be confused with deployment pipelines), you will most likely have some activities... [SQL Server 2022 Revealed]( [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Additional Articles from SQLServerCentral Know how to use the new capabilities and cloud integrations in SQL Server 2022. This book covers the many innovative integrations with the Azure Cloud that make SQL Server 2022 the most cloud-connected edition ever. The book covers cutting-edge features such as the blockchain-based Ledger for creating a tamper-evident record of changes to data over time that you can rely on to be correct and reliable.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Database Mail Editions In which editions can I use Database Mail? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Updating Sensitivity Classifications I ran this code on a column in my SQL Server 2022 database: ADD SENSITIVITY CLASSIFICATION TO dbo.Customer.ContactEmail WITH ( LABEL = 'Highly Confidential', INFORMATION_TYPE = 'Contact', RANK = CRITICAL ); GO I then realize I've made a mistake and run this: ADD SENSITIVITY CLASSIFICATION TO dbo.Customer.ContactEmail WITH ( LABEL = 'Highly Confidential', INFORMATION_TYPE = 'Contact', RANK = HIGH ); GO What happens? Answer: The RANK value is updated. Explanation: If there is an existing sensitivity classification, it is overwritten. Ref: ADD SENSITIVITY CLASSIFICATION - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Script]( - We have about 30 databases in server A.  All of them need to migrate to server B. Decided to use the backup & restore method. The dat file & log file paths are will be different on source server & destination server. I am looking for the script to backup all the databases on the […] SQL Server 2016 - Administration [SHOWPLAN documentation]( - The most recent documentation I can find is this v=sql.105) which is showing as retired. Does anyone know if there is a newer version which applies to 2016? Apologies for asking for assistance with searching. I have searched extensively and not been able to find it. I'm guessing the content is still accurate for 2016 […] SQL Server 2019 - Administration [Using TDE Best Practice]( - I am trying to understand when best to enable TDE. Our Asst. Director has been pushing to encrypt everything when we can due to more and more cyber crime. We have implemented all SQL instances are backing up using encryption as well as all our SQL connections are encrypted. As far as TDE, Should we […] [Program Code giving duplicate errors where no duplicates exist]( - On our Java Hibernate program it is giving a duplicate value on an insert on a table where there are no values remotely close to the vales being inserted. The developers have stepped through their code, connecting from other source code, though the program has not changed in 2 years. Running the insert directly in […] [Add alarms to database when a query, stored procedure, SSIS package hangs]( - Hello SSC, I am a SQL Server developer that has some DBA skills. I need to add alarms to an instance or database when a query, stored procedure, or SSIS package hangs for longer than a few hours. I did not see anything in the security or maintenance drop down in SSMS. Any assistance would […] [MSOLEDBSQL/MSOLEDBSQL19 - Linked Servers and encryption]( - Yes I know Linked Servers can be evil... but they can be fine and fair enough for small data sets. If trying to setup a linked server and using either the MSOLEDBSQL that ships with SQL2019 or the newer one MSOLEDBSQL19 (that by default wants to be encrypted and trust a valid certificate)... Given using […] SQL Server 2019 - Development [Looking for recommendations for reporting technologies replacements]( - Here at work I've been maintaining an old ASP.NET WebForms app. I was assigned it when I came to this job. I never met the original developers, as they'd all left before I was hired. Anyway, this old app has several problems, include multiple .NET Frameworks have been used for the 9 projects that comprise […] [How to get calculated values from a derived(?) table?]( - Here is my filtered dataset: I would like to calculate the time difference between each 0 and 1 that occur sequentially. In this case, the time difference between each time stamp, since the 0's and 1's alternate. So, from item 10 to 11 - that's just over 25 minutes, from 11 to 12 - almost […] Anything that is NOT about SQL! [I got tired of always having my feet to the fire, so after 42 years I fixed it!]( - SQL Server 2022 - Administration [sp_server_diagnostics in ErrorLog]( - I have the following in the SQL Errorlog, that was never there before: sp_server_diagnostics encountered a long delay that was 2.5 times more than the repeat interval between two consecutive diagnostics result generations. The repeat interval: 300000000, the elapsed time of this diagnostics result generation: *****, the elapsed time between the latest two diagnostics result […] [should i make as many datafiles for temdb as many cpus i have]( - hi, q1 As people said if u ghave GAM conention then u can go for adding as many datafiles of equal size in temdb as many cpus u have is it correct in sqlserver 2019 vesion also. q2) right click on sqlserver instance then select reprots->perfomance dashboard report then click io statistics . i have […] [dbcc clonedatabase issue]( - I have used the 'dbcc clonedatabase'  schema-only backup procedure many times in the past to have control over what I want to backup . Now I have a freshly installed SQL2022 Standard Edition instance that throws the duplicate record error message , like: Cannot insert duplicate key row in object 'sys.syssingleobjrefs' with unique index 'clst'. […] [Cannot Force Encryption - SQL Will Not Take Cert]( - Hi there, I have a sql server on government AWS and we're trying to satisfy a requirement to force encryption on the server. I have tried creating self signed certs and we have obtained official certs from DoD, but the SQL configuration manager will not accept the certificates. I have been able to get it […] SQL Server 2022 - Development [Login failed for user sa sql server 2022 - windows server 2022]( - i got the login failed error for user sa, but not always so I went to server properties - security - server authentication change to windows authentication mode and change again to sql server and windows authentication mode, and restart sql server. this happened when i upgraded to sql 2022, I have Service Pack 13 […] [Login failed for user sa sql server 2022 - windows server 2022]( - i got the login failed error for user sa, but not always so I went to server properties - security - server authentication change to windows authentication mode and change again to sql server and windows authentication mode, and restart sql server. this happened when i upgraded to sql 2022, I have Service Pack 13 installed, […]   [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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -