Stairway to Database DevOps Level 3: Managing Code as a Team in Git (2023-10-25)

SQLServerCentral Newsletter for October 25, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Database DevOps Level 3: Managing Code as a Team in Git]( - [How We Ate Our ​Own Dog Food​ To Level-Up Internal Testing with Redgate Clone]( - [Top 10 Methods to Improve ETL Performance Using SSIS]( - [From the SQL Server Central Blogs - Handling physical deletes from the source and continue populating your analytical data store]( - [From the SQL Server Central Blogs - The Evolution of Database Administration: From Administration to Database Reliability Engineering]( - [Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data]( Question of the Day - [Extracting JSON]( The Voice of the DBA  The Justification Database This editorial was originally published on Jan 18, 2019. It is being republished as Steve is on vacation. I saw [an article on the security at various Department of Defense facilities]( that control the ballistic missile systems in the US. It's interesting reading, especially if you want to shake your head at bad practices. I think this article summarizes just how quickly the digital world has grown faster than the ability of humans to keep up and adopt new practices, especially where constant staff turnover is the norm. This is a good read for anyone in an organization that must plan for security and account for new staff on a regular basis. It certainly made me think a bit about how information is presented, disseminated, and the view from the other side. It's easy for me to consider security and focus on that from my position, but for others that are tasked with other jobs, their view is completely different. There was one item in here that made me stop and think. There is a section, titled "No database with written justifications", that notes that there wasn't a database of reasons why someone was granted access. Through random sampling, the found that lots of employees didn't have forms filled in completely with justifications and approvals. In some cases there weren't even forms. I assume these are digital forms, but they could be paper. That made me think about the ways in which I've granted access to individuals in databases in the past. Often in small companies the request is verbal or in email, with approvals made the same way. Certainly there's no tracking. In larger companies there might be tickets in some helpdesk system, but how do I track those back to clicking in SSMS to add a user to a role? This week I'm wondering how well managed your system is for managing and approving security changes? If someone was concerned that a hacker gained access through social engineering, could you track down where, when, and why someone was granted access? Are you regulated or audited? I know some people have great processes that limit the potential for abuse, but I also know that far too many people use exceptions to get work done. I wonder how many of you allow for exceptions to your process. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Technical Article]( [Stairway to Database DevOps Level 3: Managing Code as a Team in Git]( Erin Dempster from SQLServerCentral In this level of the Stairway to Database DevOps, you'll get an introduction to branching and merging. Learn how to create a branch for making your changes to the codebase, submitting these in a code review, and then merging the changes into those made by other developers. [External Article]( [How We Ate Our ​Own Dog Food​ To Level-Up Internal Testing with Redgate Clone]( Additional Articles from SimpleTalk Most applications have large and complex databases at the back end, making it hard for developers to adequately test their work before it goes out. Having a fast, repeatable process to deliver data on demand is an essential part of an effective software development lifecycle, ultimately leading to improved customer satisfaction. In this article, we’ll explore the journey our own engineering team went on to leverage our own tool, Redgate Clone, to spin up short-lived database instances in containers for automated testing. [Technical Article]( [Top 10 Methods to Improve ETL Performance Using SSIS]( Additional Articles from developer.com Extraction Transformation Load (ETL) is the backbone for any data warehouse. In the data warehouse world data is managed by the ETL process, which consists of three processes, Extraction-Pull/Acquire data from sources, Transformation-change data in the required format and Load-push data to the destination generally into a data warehouse or a data mart. From the SQL Server Central Blogs - [Handling physical deletes from the source and continue populating your analytical data store]( Rayis Imayev from Data Adventures (2023-Oct-15) When something important disappears, it's natural to start asking questions and looking for answers, especially when that missing piece has had a significant impact on your life.Similarly, when data... [Blog Post]( From the SQL Server Central Blogs - [The Evolution of Database Administration: From Administration to Database Reliability Engineering]( MarlonRibunal from Marlon Ribunal - SQL, Code, Coffee, etc. In the fast-paced world of IT, where trends like DevOps and Infrastructure as Code (IaC) dominate the landscape, the concepts of Reliability and Observability have seamlessly woven themselves into... [Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data]( Site Owners from SQLServerCentral Use the graph table features in Azure SQL that were introduced in SQL Server 2017 and further refined in SQL Server 2019. This book shows you how to create data structures to capture complex connections between items in your data. These connections will help you analyze and draw insights from connections in your data that go beyond classic relationships.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Extracting JSON I have this code: DECLARE @JSON NVARCHAR(MAX) = '{ "ChannelIDs": [11,14,15,16], "SerialNumber": "939029", "ReadingStartDate": "2022-05-20T13:49:13", "ReadingEndDate": "2023-01-09T17:44:05", "Amount": 50, "SortOrder": "Ascending", "IncludeFirstDate": 1 }' Which query will get me a list of the ChannelIDs as separate rows? 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) The Column Synonym Can I create a synonym for a column? Answer: No Explanation: Synonyms are for other objects, and columns are not objects. They are part of other objects. Ref: CREATE SYNONYM - [ [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 - Development [Can’t return role based on employee no from three columns?]( - I need to write a SQL Server query return only one role from table dbo.F6000059 based on employee no, but I face issue I don't know how to return the correct role. I will return only one role from table dbo.F6000059 if "employee no" exists in columns REAN82 or REAN83 or REAN84. And role name will return will be only one roles from below […] SQL Server 2016 - Development and T-SQL [Delete large number of rows help]( - I have a batch process where i have to delete around 75 MIL rows out of 1.3 Billion rows table. And then insert the new rows back. I have been told that there is no way to do incremental load on these. I believe the DELETE operation is taking very long (2-3 hours). Database is […] SQL 2012 - General [how to calculate the RAM used while running a query]( - how to calculate the RAM used while running a query SQL Server 2012 - T-SQL [Adding records between records]( - Hi, Trying to figure out how to approach this... I will have data where these is an ID and a date. I want to add records between the two dates Max up to 6 days between the actual dates. example DATA: ID, SomeDate, Flag 3642570, 2023-09-01 00:00:00.000, X 3642570, 2023-09-06 00:00:00.000, X 3642570, 2023-09-13 00:00:00.000, […] SQL Server 2019 - Administration [How to enable Always Encryption in SQL Server Agent Jobs]( - I am trying to run a SQL Agent job to select an encrypted column, but it's still shows as encrypted. How can I run SQL agent job to run with Always Encryption enabled. [SQL SSIS job failing with out of memory error]( - Hi All, Most of the time SQL SSIS job failing with out of memory error. The package are hosted in SSIS catalog. My question is - Will SSIS & SSAS use memory within SQL max and min memory (or) out of SQL server? I have SQL SSIS, SSAS & two instance of DB engine. I […] [Ola IndexOptimize - USER_DATABASES failed]( - EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y' Job Name                           IndexOptimize - USER_DATABASES Step Name                         IndexOptimize - USER_DATABASES Duration                             01:00:14 Sql Severity       16 Sql Message ID 50000 Operator Emailed Operator Net sent Operator Paged Retries Attempted         0  Message Executed as user: NT SERVICE\SQLSERVERAGENT. ...0) Server: SERVER1 [SQLSTATE 01000] (Message 50000) Version: 15.0.4316.3 [SQLSTATE 01000] […] [Ola IndexOptimize - USER_DATABASES failed]( - EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y' Job Name                           IndexOptimize - USER_DATABASES Step Name                         IndexOptimize - USER_DATABASES Duration                             01:00:14 Sql Severity       16 Sql Message ID 50000 Operator Emailed Operator Net sent Operator Paged Retries Attempted         0  Message Executed as user: NT SERVICE\SQLSERVERAGENT. ...0) Server: SERVERABC [SQLSTATE 01000] (Message 50000) Version: 15.0.4316.3 [SQLSTATE 01000] […] [Expand disk size for SQL 2019 High Avalability Group]( - Hi, Due to the data growth, we have to increase the disk size where the data files resides. Our SQL 2019 high availability group is running in VMWare v.7. 2 nodes. Do I right click the VM, Edit Settings, then increase the disk size, then expand it in disk manager? I assume I have to […] [KB5029375 - SQL Server 2017 GDR]( - I have been trying, on and off, to install the latest CU/GDRs on test servers since Friday by obtaining the updates from: Latest updates and version history for SQL Server - SQL Server | Microsoft Learn I have had no problem with SQL2019 CU23 or the SQL2016 GDR. I cannot get the SQL2017 GDR to […] SQL Server 2008 - General [MSSqlServer 10.50.6000.34 Event-ID 18456 LoginError]( - Hi Experts, how can I fix this error NT-AUTHORITY\SYSTEM Cause: Error opening the explicitly specified database. [CLIENT: ] Thank you, Stephan [MSSqlServer 10.50.6000.34 Event-ID 18456 LoginError]( - Dear Experts, Nothing special, just an annoying returning entry in Windows-Event- Protocol. => SQL Server 2008 However, the Event viewer is throwing permanently an error: Error logging in for the user 'NT-AUTHORITY\SYSTEM'. Cause: Error opening the explicitly specified database. [CLIENT: ] - System - Provider [ Name] MSSQLSERVER - EventID 18456 [ Qualifiers] 49152 […] SSRS 2016 [100% stack bar chart with periods and time axis]( - Hello, I am trying to develop a report that would display periods of time (segments) on time axis and grouped by equipment, like below: Colored bars are segments (Duration in seconds). On X-Axis i would like to display time. But as long as i am using X-Axis as a default 1-100 i can see segments, […] Integration Services [Trouble Upgrading SSIS Project and Packages from Targeting SQL 2014 to 2022]( - Taking an existing Visual Studio SSIS Project that targets SQL Server 2014 to now target SQL Server 2022 through the suggested course of the "Upgrade All Packages" has been unsuccessful. The reason being was we were trying to avoid having to recreate the project and packages from scratch. We are using Visual Studio 2019 version […] SQL Server 2022 - Development [Extract file in zip format]( - Good afternoon, Devs. My name is André, and I'm new here. I'd like to ask for your assistance with a problem I'm currently facing. I've taken over the migration of an application where the database is in SQL Server, and there's a table containing files stored in a column of type "ntext." According to the […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -