Stairway to Database DevOps Level 2: Manage Code with Red Gate SQL Source Control (2023-10-18)

SQLServerCentral Newsletter for October 18, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Database DevOps Level 2: Manage Code with Red Gate SQL Source Control]( - [What I Wish Everyone Knew About ETL Processes - Traditional to Modern Cloud Solutions]( - [Designing Databases for Distributed Systems]( - [From the SQL Server Central Blogs - Eleven chapters of my data architecture book are available]( - [From the SQL Server Central Blogs - Common Mistakes in SQL Server – Part 3]( - [Microsoft Power BI Quick Start Guide: The ultimate beginner's guide to data modeling, visualization, digital storytelling, and more, 3rd Edition]( Question of the Day - [The Basic Symmetric Key]( The Voice of the DBA  Practicing (Annoying) Better Security At Redgate Software, we've been looking to "level up" our internal security game. While we have had very good security during the 16 years I've been there, there have been a few security issues with our products. The speed at which we address things, as well as the communications with customers, has impressed me. We've had almost no problems with our internal systems, unlike a few other places I've worked. We haven't had the phishing/virus/breach/ransomware issues that I've seen at other employers or heard about from friends. I do think our IT staff is diligent and careful, as well as forward-thinking. It also helps that we've had a relatively small employee staff that worked in physical offices for most of our existence. Recently, we've been on a security push to tighten up the way we deal with systems. As we grow our staff, and as we add more offices, there is a recognition that our attack surface area is growing. We also find more and more people using non-Redgate-owned devices. This year we've had a series of policies rolled out that we are supposed to adhere to in order to ensure strong security, as well as compliance with data privacy rules such as the GDPR. One of these is a bring-your-own-device (BYOD) policy. For years I've used my personal mobile phone for Redgate, with a few settings enabled to allow a remote wipe if I lose it. However, I've also had a personal desktop that I use for daily work in my home office. I've never enabled a lock on this as my wife occasionally uses it to get a picture or other document. Or send me something I forgot to sync in the cloud. Part of our new policy is that I need to enable a lock on my desktop, as there is privileged Redgate information on there. Not much Redgate data, but the machine does connect to our business OneDrive and SharePoint systems. This lock should be a 2-minute timeout, which means that I come back to my desktop after coffee, laundry, or something else to find it locked. After years of always locking my desktop in corporate offices, I somehow find this more annoying. Especially as I've gotten used to rarely typing my 15-character password. I mess this up regularly and have to (more slowly) re-type my password a few times. I know this is better security, and I am always conscious of locking my laptop in our various offices when I go in. However, I find it annoying at home. Especially when I pop in away from work to look up something on the Internet. I keep telling myself this is good security, and good for both Redgate and our customers. I'm still annoyed by the change, but I know it's for the best. Like many who work in organizations, I've been lazy about some security aspects for years, and the change is a disruption. I'm sure some of you feel the same way about the rules and protocols that your employers have implemented. You're not alone in desiring a more convenient workplace, but security is a series of overlapping measures that work together to protect data. Practicing and adhering to good security is a lot like a daily backup. Most of the time it's something never need, but when there's an issue you'll be glad you followed the process that day. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Database DevOps]( [Stairway to Database DevOps Level 2: Manage Code with Red Gate SQL Source Control]( Erin Dempster from SQLServerCentral In this second level of the Stairway to Database DevOps, we learn to use Redgate's SQL Source Control to save and updates changes to objects, as well as tracking data in certain tables. [External Article]( [What I Wish Everyone Knew About ETL Processes - Traditional to Modern Cloud Solutions]( Additional Articles from MSSQLTips.com Learn about the evolutionary journey of ETL (Extract, Transform, Load) from traditional processes to modern cloud solutions. [Technical Article]( [Designing Databases for Distributed Systems]( Additional Articles from DZone Several data management patterns have emerged for microservices and cloud-native solutions. Learn important patterns to manage data in a distributed environment. [Blog Post]( From the SQL Server Central Blogs - [Eleven chapters of my data architecture book are available]( James Serra from James Serra's Blog As I have mentioned in prior blog posts, I have been writing a data architecture book, which I started last November. The title of the book is “Deciphering Data... [Blog Post]( From the SQL Server Central Blogs - [Common Mistakes in SQL Server – Part 3]( Hemantgiri S. Goswami from SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP Last week we discussed advantages of recompilation in Common Mistakes in SQL Server – Part 2. This week in this article Common Mistakes in SQL Server - Part 3 I... [Microsoft Power BI Quickstart Guide cover]( [Microsoft Power BI Quick Start Guide: The ultimate beginner's guide to data modeling, visualization, digital storytelling, and more, 3rd Edition]( Steve Jones - SSC Editor from SQLServerCentral Bring your data to life with this accessible yet fast-paced introduction to Power BI, now in color.   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Basic Symmetric Key Which of these options (multiple) are needed to create a symmetric key with the CREATE SYMMETRIC KEY command 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) Rollback or Not? I have a number of rows in my SQL Server 2019 database in the AddressTable table. I run this code: BEGIN TRAN TRUNCATE TABLE dbo.AddressTable ROLLBACK SELECT top 10 * FROM dbo.AddressTable AS at How many rows are returned? Answer: 10, always Explanation: The setting of implicit transactions doesn't matter. A rollback is possible for truncate table, so 10 rows are returned. Ref: TRUNCATE TABLE - [  [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 [Monitoring Transaction Log usage?]( - I'm curious is there any way for me to monitor the TLog generation in my database? I am trying to pin point which sessions/SQL queries which generated huge amount of transaction log. I have alerts to notify if log space is running out, but I'm trying to find out which particular session or which query […] SQL Server 2016 - Development and T-SQL [Insert a new record in a temp tableq]( - Hi, Not sure if this is possible. I'm looking to insert a new row into a temp table if a field on the record read is a specific value. Example Row reads Name, Address, Date, Flag first row = Jane, 1 Main St, 10/5/23, z second row = Tarzan, 12 Main St, 10/4/23, x Now, […] Administration - SQL Server 2014 [sources\sxs is not a valid installation folder]( - I'm trying to add a SQL feature via programs features - SQL Server - change - add. I have mounted the ISO successfully. When I click add it asks me to specify the installation folder so I browse to J:\sources\sxs and click ok. I keep getting the message that sources\sxs is not a valid installation […] SQL Server 2019 - Administration [Replication subscription attempt fails with master key Error: 15581]( - Hello experts, I'm trying to create a replication subscription and got this error. Does anyone know how I can fix it without inadvertently messing up any encryption that the SQL Server has? Please create a master key in the database or open the master key in the session before performing this operation. Changed database context […] SQL Server 2019 - Development [T-SQL | Group Invoices with concern dates that overide the issue date]( - I have a table with Invoice Documents per Project, in which i need to get statistics per project , year , month with sql. The difficulty is that for some of the invoices the user will have entered concerned dates that must be override the issued month of the Invoice Document and must be splitted […] [Select statement result change as insert statement values]( - Hi All, Looking for following results. Can you please help in this. Original 1 A US Expected result 1,'A','US'  create table #tbl_Test (ID int, Name varchar(20),City varchar(20)) insert into #tbl_Test values (1,'A','US') Select * from #tbl_Test Expected result 1,'A','US' [Select a distinct list of values from tables, return the dataset]( - I am trying to select a distinct list of values from multiple tables, combine them into a single table with all data starting in row one instead of the data being returned on new rows for each distinct data set. This is what I am current getting by using a union: This is how I […] [Setting up a view with parameters]( - Hi Folks, I'm not very well versed in SQL so I come here to the experts for advise. I have a nice little query that is currently using hard coded values for customer name, start and end dates. I need to be able to pass those values as parameters which I don't know how to […] [Stored procedures in Analysis server]( - I have written a stored procedure to format data and return them as a table with custom columns. In this sp, I use #temp table to do some formatting / calculations. Now our powerBI report writers want to access to this SP from Analysis service. For them our admin created SQL analysis service. PowerBI users […] [How to check if SQL is installed on any of my multiple windows servers]( - I have about 400 windows servers. How can I check if SQL Server is installed on any of those servers. Thanks. [Correct syntax for this Contains statement]( - I want to search a table alias doc, column name Result, where doc.Result is full text indexed, using Contains, and I want to find where there is the term BSA and a % sign (percent sign, literally) within 5 words of each other. Do I have to escape the % sign like [%] ? that seems to come back […] Reporting Services [SSRS server randomly get disconnected (sql server 2016)]( - I have a problem with my SSRS server that randomly can't connect to my data source. It works all the time but sometimes I get an error in my reports that indicate that the server failed to connect. Any tips or leads to identify the source of this problem? Here is my connection string: Data […] General [Fetch API in Node.js: Making HTTP Requests]( -  I'm working on a Node.js application where I need to make HTTP requests to external APIs. While I'm familiar with using the Fetch API in browsers, I'm not sure how to use it in a Node.js environment. Here's a simplified example of what I want to do: const fetch = require('node-fetch'); async function fetchData() […] SQL Server 2022 - Development [Query web API and return JSON data]( - curl -X GET " \ -H 'Authorization: Bearer ' \ -H 'Accept: application/json' How do i run this Rest Json API in sql server directly ? I believe its a combination of using he below, but i could not figure out the last syntax. sp_OACreate, sp_OAMethod sp_OAGetProperty Python version is here : # Version 3.6.1 […] [How to get 2nd max value if 1st max is null]( - Hello All, I have written below query to get agent data who completed max tickets for each group. But some times the agent email address is null or blank. So In that case I need to consider the 2nd max tickets completed agentname. If this email also null then 3rd max tickets completed agent. Could […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -