A Version Control Strategy for Branch-based Database Development (2023-02-08)

SQLServerCentral Newsletter for February 8, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A Version Control Strategy for Branch-based Database Development]( - [Index Types in PostgreSQL: Learning PostgreSQL with Grant]( - [A Simple Example of Flyway Development using GitHub Branching]( - [From the SQL Server Central Blogs - Allow a user to only trigger pipelines in Azure Data Factory]( - [From the SQL Server Central Blogs - Recovering data with crash consistent snapshots]( - [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( - [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Question of the Day - [Backing up a damaged database]( The Voice of the DBA  Daily Coping Tip Say hello to a neighbor and get to know them better 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. Coaching the Digital Transformation One of the challenges for me when working with customers is getting them to think about how to change their software process. Often they want to solve their problems, but no one wants to alter the way they work. Whether that's the protocol for capturing code or managing servers, it seems that changing the way we work is hard for many people. They want everyone else to change. Or they want a magic tool that solves problems without them changing the way they work. Unfortunately, I'm not a magician. This is a common problem in many organizations. We want to be more efficient and effective, but actually changing our habits and culture is hard. Management should lead the charge, and they want to, but they can struggle with how to do this. Often they focus on changing technology, and not actually improving the way their company works. There's [an interesting article on digital transformations]( and whether the efforts are worth the investment. In many companies, someone makes a good argument for a course of action or a project and then drives it. Others participate, but often a person pushes this forward, usually because they have some stake in the outcome. A bonus, a reputation, or just pride, whatever matters to them becomes the reason for continuing, even if there isn't enough value from the effort. This can be because of an institutional culture that wants to finish projects, wants to find some success in a course of action. Whether a human or an organization, pride and inertia often keep us moving forward, often without any other support or analysis of how well things are progressing. Effective dashboards enable everyone to see current status and progress, and to make better course corrections, helping to move from a command-and-control model to a coach-and-communication orientation. Many organizations have adopted KPIs, dashboards, and other ways to analyze parts of the business, but this isn't always something we do well in software development. We tend to look at metrics that management cares about, and on which we are measured, rather than metrics that might help us improve how we work. Part of digitally transforming a business is also transforming how technology is used. Part of that is us, as technologists, learning to be better and more effective. Whether this is in development or operations, we can often improve how we function. We need coaching, and in many orgs, that coaching has to come from within, from the people in a team asking others to do better. And to allow others to ask us to do better . Coaching is often teaching from a different perspective. It's helping someone see what they don't see themselves. This might be new knowledge, but many times it's just reminding the individual of something they know, but aren't doing. As we are asked to do more, be open to coaching and be willing to help coach others. Become a role model that helps transform how your organization uses technology. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [A Version Control Strategy for Branch-based Database Development]( Tonie Huizer from SQLServerCentral The migration to a different VCS should not be underestimated, as there is a lot more involved than just switching tools, and that’s what Tonie Huizer covers in this article. First, the team needed to migrate the monolithic repos over to Git, second the team needed to make sure we had a branching strategy and workflow in place. It involved a big process change for the team as well, but this switch is mandatory to make full use of branch-based database development. [External Article]( [Index Types in PostgreSQL: Learning PostgreSQL with Grant]( Additional Articles from SimpleTalk In this article I’m going to go over the different types of indexes and some index behaviors. We’ll get into what the indexes are, how they work, and how best you can apply them within your databases. I’m hoping you’ll develop an understanding of which indexes are likely to work better in each situation. [External Article]( [A Simple Example of Flyway Development using GitHub Branching]( Additional Articles from Redgate This article demonstrates one way to do branch-based database development with Flyway, using GitHub to manage the branches and Flyway configuration files to allow Flyway to switch smoothly between databases, when we move between branches in GitHub. [Blog Post]( From the SQL Server Central Blogs - [Allow a user to only trigger pipelines in Azure Data Factory]( Koen Verbeeck from Koen Verbeeck Typically you have a bunch of pipelines that are started by one or more triggers. Sometimes, a pipeline needs to be manually triggered. For example, when the finance department... [Blog Post]( From the SQL Server Central Blogs - [Recovering data with crash consistent snapshots]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold When we talk about snapshots of SQL Server there are two types, application consistent snapshots and crash consistent snapshots. Application consistent snapshots require freezing IO on a database allowing... [SQL Server 2022 Query Performance Tuning]( [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Steve Jones - SSC Editor from SQLServerCentral Troubleshoot slow-performing queries and make them run faster. Database administrators and SQL developers are constantly under pressure to provide more speed. This new edition has been redesigned and rewritten from scratch based on the last 15 years of learning, knowledge, and experience accumulated by the author. The book Includes expanded information on using extended events, automatic execution plan correction, and other advanced features now available in SQL Server. [Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen]( [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Greg Larsen from SQLServerCentral Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Backing up a damaged database I have a SQL Server 2019 database that is damaged, but the log file appears fine. What options do I need to include to back up the tail of the log for this damaged database in the BACKUP LOG command besides the name and backup location? 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) Encrypting the Database Encryption Key I am configuring Transparent Data Encryption for a new database and need to create a Database Encryption Key (DEK). As I architect this, I need to secure this key. What are my options for encrypting the DEK? Answer: certificate or asymmetric key Explanation: An asymmetric key or a certificate (which is an asymmetric key) can be used to encrypt the DEK. Ref: CREATE DATABASE ENCRYPTION KEY - [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 [SQLCMD does not write the results of a RESTORE command to the log file or tty]( - I'm in the process of converting some scripts we use to restore SQL Server databases to use SQLCMD. The scripts currently use the osql utility. I thought SQLCMD would have all the functionality of osql plus more but it doesn't seem to output the stats and a "RESTORE DATABASE successfully processed" to a file or […] [blocking issue]( - Hi All, We are seeing SELECT's blocking DML's like UPDATE statements. When we tried to check with application team, they said they are using with (ROWLOCK) hint in most cases. So, I tried to repro but not able to reproduce the issue of SELECT with (ROWLOCK) hint blocking UPDATE statements. Are there any cases where […] SQL Server 2017 - Development [Dimensional table and Stored Procedure]( - To meet these requirements below, I came up with this SQL. Please evaluate my code if it produces the requirement(s) result. A dimensional table and procedure that tracks Primary Accounts’ group movements. An account moves to different groups that are associated with levels of commission paid. Analysts needs to follow how they moved over time. […] SQL Server 2016 - Development and T-SQL [find connection details for all ssis jobs]( - Hi, is there are way using sql that brings a list of the ssis packages deployed along with the details of the connections used. There are approx 100 packages and to manually open is not realistic. So I'm interested to know which packages/dtsx are using a OLE DB connection and which database name it's connecting […] Development - SQL Server 2014 [Use of an NCI - that does not contain the Column required.]( - Hello. I am a bit confused and not sure what terms to use to Google. I have a transaction that overran this week and DPA gave me an Estimated Execution Plan that showed a Missing Index Hint - which I understand. The Table has 3.5Million Rows and the SQL is doing an Equality Search in […] SQL Server 2012 - T-SQL [Anderson-Darling Calculation]( - Hi  I am following an article posted on sql server central on the anderson-darling calculation. The code has worked, but is out by 0.001. I have used two programs, MiniTab and Excel Workbook to compare answers and they get a result of 5.808, but i cam getting 5.807. Excel book I have looked […] SQL Server 2019 - Administration [Re-creating AG after server loss]( - We recently had to rebuild servers that were configured with AG. I built everything from scratch, but I am wondering if there is any way to recover AG configuration from master db? os backups... Thank you. [Does SQL Server eventually terminate idle connections to the database?]( - We are only trying to answer the below questions regarding database connections for an audit. We are not having any issues, and nothing is broken. We are running SQL Server 2019 on Windows. 1. Does SQL Server eventually terminate idle connections to the database? 2. How to terminate an idle connection to a SQL Server […] SQL Server 2019 - Development [Cannot run DynamicSQL Statement to merge table with JSON]( - Hi there I have a problem with a dynamicSQL Statement. In this code, I have some data come in JSON format, with which i want to perform a merge operation on an existing physical table. I declare the JSON as nvarchar(max) and able to print this out However when I place this JSON in my […] [Can you help me understand what this stored procedure call returns?]( - Definition of stored procedure  create procedure [dbo].[smGetMinDate] @O_dMinDate datetime = NULL output as declare @dDefaultMinDate datetime select @dDefaultMinDate = GETDATE() select @dDefaultMinDate = DATEADD(mm,-(DATEPART(mm,@dDefaultMinDate))+1,@dDefaultMinDate) select @dDefaultMinDate = DATEADD(dd,-(DATEPART(dd,@dDefaultMinDate))+1,@dDefaultMinDate) select @dDefaultMinDate = DATEADD(yy,-(DATEPART(yy,@dDefaultMinDate))+1900,@dDefaultMinDate) select @dDefaultMinDate = DATEADD(hh,-(DATEPART(hh,@dDefaultMinDate))+0,@dDefaultMinDate) select @dDefaultMinDate = DATEADD(mi,-(DATEPART(mi,@dDefaultMinDate))+0,@dDefaultMinDate) select @dDefaultMinDate = DATEADD(ss,-(DATEPART(ss,@dDefaultMinDate))+0,@dDefaultMinDate) select @dDefaultMinDate = DATEADD(ms,-(DATEPART(ms,@dDefaultMinDate))+0,@dDefaultMinDate) select @O_dMinDate = @dDefaultMinDate return stored […] [SQL Server 2019 error and Usage reporting not starting]( - we use two SQL instances on one server - both SQL Server 2017 and SQL Server 2019. I can turn-off SQL Server 2017 error and usage reporting service as shown in the screenshots, but when I open error and usage reporting service for SQL Server 2019 in a cmd window, and then close automatically and […] [Storing a text file in a VARCHAR(MAX) column]( - I'm using OPENROWSET to put the contents of a text file into a VARCHAR(MAX) column. However, it is stripping the carriage returns and line feeds out, so when I put the contents of the column back into Notepad I get one long line instead of the original text file. Is there a way to prevent […] Reporting Services [Restrict available values from one parameter per othr parameters selected values]( - Hello Have created a simple 9 column report where there are 7 (multi valued) parameters all reference the same table. I want all these parameters to reference one another. So, for example, once a user selects values from parameter 1, restricting say departments to 3 departments, then the rest of the parameters, once selected should […] SQL Server 2022 - Development ["ID" primary key from two separate tables as a foreign key in a new table]( - I have two tables: tblProfessors with ProfessorID as the primary key and tblStudents with StudentID as the primary key. I'm wondering if it's possible to create a single foreign key (ID) in a separate table that is a combined list of ID's from tblProfessors and tblStudents. Here is the script for both tables: CREATE TABLE […] [Identity column and "EntityID" column naming convention]( - This isn't* specific to 2022, but SQL in general. I will be joining a full green field development project of a new application. One of the things I will be setting as a standard will be to ban the use and visibility of identity primary key columns to end users or (especially) report writers and […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -