Stairway to SQL Server Automated Database Testing Level 1: Why You Need TDD (2022-12-14)

SQLServerCentral Newsletter for December 14, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to SQL Server Automated Database Testing Level 1: Why You Need TDD]( - [Reviewing SQL Migration Files Before a Flyway Migration]( - [Introducing the MySQL DELETE statement]( - [From the SQL Server Central Blogs - Default event_file path for Extended Events]( - [From the SQL Server Central Blogs - Encrypting Your SQL Data Should be Your Default Option]( Question of the Day - [Nested Triggers]( The Voice of the DBA  Daily Coping Tip Offer to help someone who is facing difficulties now I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Upgrading SSC The decision to upgrade database servers can be a complicated one that involves features, costs, and support requirements. While I think many people would love to run database systems for ten years, often there are concerns about support, which effectively ends after five years for SQL Server. While you can purchase extended support, is that worth the cost? A hard question to answer, but one I've had to confront lately. When do you upgrade a database instance? I've discussed this with a few customers, but it also came up in the context of SQL Server Central. We run a SQL Server 2016 database on Windows Server 2016. Someone recently sent a note that since 2016 is out of support, they'd like to upgrade the systems. They were thinking SQL Server 2019 on Windows Server 2022, but with the release of SQL Server 2022 that doesn't make sense. I want to defer this process as long as possible. To me that means always aiming for the latest and greatest version. SQL Server releases roughly every 2-3 years, so this is the best time to upgrade for us. If we can upgrade before 12 months, we get 4+ years before we revisit this topic. If we were to upgrade to SQL Server 2019, then we're already down to 2 years of support before we need to consider the topic again. I think many DBAs would feel the same way, looking to test and certify SQL Server 2022 for their internal apps. This is one reason why getting the RC0 and RC1 releases of new versions for some initial testing make sense. The sooner you can upgrade in version's lifecycle, the longer before you do it again. Of course, if you have more than 50 servers, you might just be upgrading every year anyway, as some system is always falling out of support. Upgrades take time. There's the time considering the decision, the testing a new system, the actual upgrade time, whether in place or migration, all of which eat up labor and time. Doing this for more than a few servers can become a full time job in some cases. That makes me start to really see the wisdom of using a PaaS service that's evergreen. For organizations where support matters, then upgrades are a fact of life and a regular occurrence. However, if formal support isn't an issue, you might feel differently. The more mainstream you keep your feature usage, the more likely that you can go far past 5, or even 10 years, with your database system. I know there are still companies running 2008, 2005, and even a few 2000 servers. I don't know that the SQL Server Central code would run on SQL Server 2000, but it might. [Project Nami]( is using fairly generic SQL. If we were more concerned about database licensing, likely we'd have not upgraded the site from [ASP.NET]( to WordPress. For now, I think we'll likely upgrade sometime in early 2023. I am not worried if things are delayed, but I do know that we also want to upgrade both WordPress and PHP and there is an order to getting all these steps completed. Because of this, I want to ensure that we proceed soon and get things moving while my schedule is fairly light so that I can test and deal with any issues that arise. Hopefully there won't be any, but I've learned to hope for the best and prepare for the worst, especially with software upgrades. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Technical Article]( [Stairway to SQL Server Automated Database Testing Level 1: Why You Need TDD]( Sebastian Meine from SQLServerCentral.com The first article in this Stairway Series makes the case for test-driven development. [External Article]( [Reviewing SQL Migration Files Before a Flyway Migration]( Additional Articles from Redgate This article demonstrates how, once armed with the file path locations of all the scripts, you can use PowerShell to search them for various purposes such to review them for potentially disruptive changes, or run code quality checks, or to verify documentation standards. [External Article]( [Introducing the MySQL DELETE statement]( Additional Articles from SimpleTalk In this article, I focus exclusively on the DELETE statement to help round out our discussion on the core DML statements in MySQL. Overall, the DELETE statement is fairly basic, but one that’s no less necessary to have in your arsenal of DML tools. [Blog Post]( From the SQL Server Central Blogs - [Default event_file path for Extended Events]( Zikato from StraightforwardSQL Foreword Have you ever wondered where the .xel file is saved when you create a new Extended Event session and don’t specify the full path (just the file name)? Like so: Well,... [Blog Post]( From the SQL Server Central Blogs - [Encrypting Your SQL Data Should be Your Default Option]( Matthew McGiffen from Matthew McGiffen DBA Cyber-attacks and data breaches seem to be perpetually on the rise. Every week we hear news of large companies that have had data stolen with both financial and reputational...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Nested Triggers Which types of triggers can be nested and how far can they be nested in SQL Server 2022? 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) Dealing with Deadlocks I have two pieces of code that run separately from our client application, but they sometimes deadlock. When something goes wrong, I want to ensure Code Batch 1 is killed, not Code Batch 2. What should I do? Answer: Set the deadlock priority lower for Code Batch 1 Explanation: You can set a deadlock priority for code batches to low, medium, or high. In this case, to ensure Code Batch 1 is killed, set that lower than code batch 2. Ref: SET DEADLOCK_PRIORITY - [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 2016 - Administration [convert mongodb collection structure in SQL Server]( - Hi. I am new in mongoDB. I have mongoDB all collection structure, I want create tables in SQL Server from collections. Please help me. how convert into SQL Server database from mongoDB collections JSON format.  Thanks SQL Server 2016 - Development and T-SQL [Grouping Same ID's with different Names]( - Hello All, I have a issue where I am looking to get a list of customers with a different names or date of births with a same ID. Please find some sample data. There are 2 records (A123450, X111111) are the correct and the others need to be retuned in the list. create table #tst […] [I have an odd situation that I just can't wrap my head around]( - I have been away from SQL and programming for 9 years due to a severe illness. An old client asked me to do him a favor by designing an application for him. I can't seem to come up with a clean database design. I don't know if it's because I am rusty or because he […] Administration - SQL Server 2014 [Migrating from SQL Server 2014 to 2019]( - Hi Experts, We have a 3 Node SQL Server cluster with Always On, the setup is such that the first two nodes are created with traditional windows cluster & SQL server cluster, the third node is added later to the windows cluster and setup always on. Now I need to upgrade the OS and SQL […] SQL 2012 - General [Looking for help installing Sql express 2012]( - So I normally have no issues with installing this software but I popped open some new systems that came in. Windows 10 pro x64 on HP probook6450b. All solid state drives, and i5 processors. All attempts to install express 2012 fail. I can launch the program without issue. Select fresh install. But while going through […] SQL Server 2019 - Administration [Upgrade from SQL Server 2019 Standard Edition to Developer]( - Hi all Quick question - is it possible to upgrade from Standard Edition to Developer? We have server that is due to go into Production in the new year but we are still testing performance. We have discovered some performance issues that we expect will be resolved in Enterprise, but want to upgrade to Developer […] SQL Azure - Development [Substitute of trigger in a merge statement.]( - Hello Everyone I hope you all are rocking. My question is regarding the merge statement. I am using Microsoft SQL Azure (RTM) - 12.0.2000.8. I am transferring users' data from one table to another table with some manipulation of data. Everything works fine without any issues. Now I have another table that maps the user […] SQL Azure - Administration [Move Azure SQL Managed Instance across subscriptions]( - Hi, Is there a way ( with less downtime ) to move Azure SQL Managed Instance across subscriptions? One way to do it would be to take a COPY-ONLY backup to an URL and then restore to the new subscription. But this can require significant downtime for cases where the database is huge. General Cloud Computing Questions [Silly Cloud computing story]( - A few years ago I was interviewing for a position. Roughly 2016. I was speaking with the IT director (fairly small company, located in the N Chicago suburbs). One of the things he told me was that when he joined, the company was using a cloud service based in Texas. The first thing he did […] Reporting Services [RS.exe is having issue while subscription is completing the report.]( - Hi We have a report that is running via rs.exe utility and it errors out, but when we execute it via subscription it is completing. The error is below, the report is executing an oracle stored procedure  runningjobs!ReportServer_0-18!14cc!12/08/2022-08:37:28:: i INFO: RunningJobContext.IsClientConnected; found orphaned request 35ybh2mbnhiom22ugojqi52l processing!ReportServer_0-18!14cc!12/08/2022-08:37:28:: i INFO: DataPrefetch abort handler called for Report […] SSRS 2016 [List all data sources]( - Hi is there a way to list all the data sources in SSRS 2016, and if possible which reports use them? Cheers Alex Connecting [Recent changes to Azure Data Studio requiring a certificate to connect to SQL]( - Over the weekend I was doing some development on a project at home. I've use SQL Server 2019 Developer edition on an old machine. Anyway, I was presented with a notice as I got into Azure Data Studio, about an update to ADS, which affects connections to SQL Server using ADS and also affecting Microsoft.Data.SqlClient, […] Disaster Recovery [log shipping monitor instance]( - I tried to setup log shipping on two data centers. Server A in location A, serverB in nearby location B. On the server there are multiple databases, I created the first logshiping database, I created the monitor instances on ServerB. It works well. I then tried to create another log shipping for another database, when […] SQL Server 2022 - Administration [SQL 2022 - Contained AlwaysOn restore]( - Hi,  Ive been playing around with the new SQL Contained AlwaysOn, and its looking really promising so far! But one thing im missing from the documentation / my PoC, is how to restore the master database of the contained availability group. Like we normally can do, with a bit of twerks.  The best […] SQL Server 2022 - Development [Update value SQL table from another table]( - I have this tables: I need to update sum_ok and avg_ok considering values from table 1 like this: I have this code SQL Architecture, but don't work fine: update t2 set sum_ok = sum(case when t2.[status]='OK' then 1 else 0 end ) ,avg_ok = avg(case when t2[status]='OK' then status end ) from t1 inner join […]   [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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -