Git, GitHub and Visual Studio Code for SQL Server (2024-08-05)

SQLServerCentral Newsletter for August 5, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Git, GitHub and Visual Studio Code for SQL Server]( - [Level up your learning in 2024 with PASS Data Community Summit]( - [Create Document Templates in a SQL Server Database Table]( - [From the SQL Server Central Blogs - SQL Server Cross Platform Availability Groups and Kubernetes]( - [From the SQL Server Central Blogs - Using Managed Identities with Azure SQL DB]( - [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Question of the Day - [DBCC TRACESTATUS]( The Voice of the DBA  MTTD This editorial was originally published on Sep 11, 2019. It is being re-run as Steve is out of town. There are a lot of Mean-Time-To-xxxx acronyms. Many of us have heard of the mean time between failures (MTBF) for disk drives. Some of us use that information when considering which model to buy. In the DevOps world, there are also the mean time to failure (MTTF) and mean time to resolve/repair (MTTR). There is one more that I think is very interesting, and that is the MTTD: the mean time to detect an issue. This is the average amount of time it takes you to detect there is a problem after the problem occurs. There was an outage at Monzo recently due to a database upgrade, which was [recounted on their blog](. In this case, their MTTD, or rather actual time to detect, was a minute. I think that is amazing. In fact, I'm somewhat skeptical that an alert is raised, someone looks at it, the customer service desk calls the Ops team (who were upgrading servers), and the Ops person realizes in the space of a minute or two that there is an issue. It's possible, but I have found that help desk personnel that discover something can take a few minutes to verify the issue and then scramble to find the on-call phone number. Relaying information can take a minute or two, so if this is accurate, huge props to the IT staff at Monzo. Many of us strive to high a high availability number for our systems, especially databases. This is one of the drivers for the growing use of availability groups in SQL Server systems: to ensure the database is highly available to clients. In determining availability, we often speak of the percentage of time that a system is available. The holy grail is five 9s, or an uptime of 99.999% of the year. This gives you just over 5 minutes of downtime a year. In the case of the Monzo outage, which took place in July 2019, the alert is reported at 13:14 and the incident was declared at 13:15pm, one minute later. The time to diagnose the issue (maybe another MTTxx item) was 63 minutes, just over an hour. At this point, availability is arguably down to 99.988%. The actual fix was completed at 113 minutes, or 99.978%. That's the number if nothing else happens this year. If you're attempting to get to 5 9s of reliability, you get less than 6 minutes of downtime a year. Can you figure out what's wrong in 6 minutes? Much less fix it? That's a difficult task. I think 4 9s, giving you 52-ish minutes of downtime, is realistic, but very hard. Most of us can likely handle 3 9s, which allows for 8:30:00 of downtime a year. While I've exceeded that before, it's been rare. We have a lot of HA (high availability) options in SQL Server, and there are many successful implementations that achieve high levels of availability for the database. The network and the application are another story, but I think the quality of those areas has increased over the years as well. Doing HA well is hard, and if you aren't 100% sure of what you're doing, or your system is very valuable, you might engage a consultant, like [Allan Hirt]( to ensure that you've configured things well. SQL Server runs well in HA configurations, but getting it set up can be more difficult than you expect. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Git, GitHub and Visual Studio Code for SQL Server]( Daniel Calbimonte from SQLServerCentral Learn how to get started with Git and Visual Studio Code. [Technical Article]( [Level up your learning in 2024 with PASS Data Community Summit]( Additional Articles from PASS Did you know that the general program for PASS Data Community Summit 2024 (November 4-8) was recently unveiled? This year's lineup includes over 150 different sessions being delivered by over 140 speakers! This year's program also includes 16 incredible full day pre-cons, taking place on November 4 and 5, plus 6 different learning pathways to choose from. Join PASS Summit on-site for a whole week of connecting, sharing and learning with industry experts. [External Article]( [Create Document Templates in a SQL Server Database Table]( Additional Articles from MSSQLTips.com Learn how to create document templates in a database table and use SQL to replace specific keywords in the templates for content customization. [Blog Post]( From the SQL Server Central Blogs - [SQL Server Cross Platform Availability Groups and Kubernetes]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold Say we have a database that we want to migrate a copy of into Kubernetes for test/dev purposes, and we don’t want to backup/restore. How can it be done?... [Blog Post]( From the SQL Server Central Blogs - [Using Managed Identities with Azure SQL DB]( hellosqlkitty from SQLKitty We are trying to get apps and users off of using SQL accounts to access the Azure SQL DBs where I work. To make our lives easier, we are... [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Site Owners from SQLServerCentral Learn how to write and design simple and efficient T-SQL code. This is a hands-on book that teaches you how to write better T-SQL with examples and straightforward explanations.   Question of the Day Today's question (by Steve Jones - SSC Editor):  DBCC TRACESTATUS I have run this code in my SQL Server 2022 session: DBCC TRACEON (2528) DBCC TRACEON (3205, -1) GO -- run some queries here GO DBCC TRACEOFF (2528) These are the only traceflags changed on the instance. Now I run this: DBCC TRACESTATUS What is returned? 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) More bad date functions? What is wrong with this code? (don't copy it and run it, guess) DECLARE @p NVARCHAR(10) = 'year' EXEC ('SELECT DATENAME(' + @p + ', GETDATE())') Answer: Nothing, this works fine. Explanation: This code works fine. [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 [Dont want to restart Tempdb not able to shrink datafiles in ag primary]( - HI All, Dont want to restart Tempdb not able to shrink datafiles in always on server primary is there any alternative way we can reduce the tempdb files and shrink the space from them . can we run this below command to clear the space from tempdb data files without restart and effect always on […] SQL Server 2016 - Development and T-SQL [How to get a distinct value from my data set]( - Hi, I need help with writing a script that will allow me to pull a distinct value from column A, depending on the value in column B. Here is an example of the data set: Column A | Column B 5000     |  1 5000     |  1 5001 […] [How To Approach Adding A primary Key To An Existing Table]( - In our vendor created/managed DB we have a table that is like an audit table the vendor uses for tracking down issues. It's something you can turn on or off via the application that uses the DB. The table has NO Primary Key. It does have a date stamp like column so we've used it […] [Deadlocks with UPDATE statements using serializable transaction isolation level]( - We are seeing frequent deadlocks occurring due to a particular stored procedure that is using the SERIALIZABLE transaction isolation level. The stored procedure is essentially trying to ensure that the same reference number (concatenated from multiple fields) is never returned more than once. CREATE PROCEDURE dbo.sp_GenerateNextNumber ( @SequenceKey nvarchar(10), @ReferenceNumber nvarchar(25) = NULL OUTPUT ) […] Development - SQL Server 2014 [View works for me ...but doesn't return results for a user in SSMS but no errors]( - Hi I have this view to check if a job is running:  SELECT job.NAME ,job.job_id ,job.originating_server ,activity.run_requested_date ,DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed FROM msdb.dbo.sysjobs_view job JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id JOIN ( SELECT MAX(agent_start_date) AS max_agent_start_date FROM msdb.dbo.syssessions ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date WHERE […] SQL Server 2019 - Development [where is my commit size in my pkg]( - hi, while trying to give our operations guys as much info as possible about a separate ssis issue /post we have with losing connections locally and/or run times of 60 x normal when vpn'd, i looked for my commit size under vs 2022 with ssis data tool 16.0.5397.1.  i thought it was supposed to show […] [error in both ssis and ssms - something about losing connections - new error]( - hi for about 4 or 5 days now, i've been seeing various connections (to our dw server) issues in ssis (excel to sql) under vs 2022 AND SSMS. the ssis error is shown below. in ssms it looks like this ...  The connection is broken and recovery is not possible. The connection is marked […] SQL Azure - Administration [How to Migrate SQL Logins Between Two Azure SQL Instances Without sp_helprevlogi]( - Hello everyone, I am currently working on migrating SQL logins between two Azure SQL instances. Unfortunately, I am facing several limitations and would appreciate your help in finding an effective solution. Using sp_helprevlogin: This stored procedure is not supported in Azure SQL. Using dbatools: The dbatools option is not viable in my current environment. HASHED […] General [AWS RDS MSSQL]( - Since AWS RDS MSSQL does not support logon trigger, is there any alternative way to implement same in RDS MSSQL. In existing logic, I created a logon trigger which allows only some selected group to logon through windows authentication, also restrict some applications from accessing mssql. I want to implement the same in AWS RDS […] Anything that is NOT about SQL! [Microsoft Dataverse]( - So having seen the term dataverse thrown around I pictured it as some sort of marketing buzzword. Recently I've been looking at the MS Power Platform and this term pops up all over the place. So I googled it. Wikipedia talked of an open source product written in Java and I wondered if I may […] SQL Server 2022 - Administration [Error fetching data via Linked Server]( - Hi, SS ver: SS 2017, SE; Oracle: 19.23 SE We have a nightly job that connects from our Sql server env to an Oracle database via Linked server that we have created for this purpose. This job had been running fine for the past several years - it pulls data from multiple tables. However, the […] [Use Polybase with ODBC to create external table]( - I'm trying to use the installed Polybase service on an SQL 2019 server to create an external table by using and ODBC DSN. The connection of the DSN is to a fairly exotic BBj server that hosts 3 databases. Somehow I just do not seem to get the proper syntax for creating the external table. […] SQL Server 2022 - Development [Dark mode, other color schemes]( - All, if you are like me and do not care for the built-in color schemes, try www.sqlshades.com. I just downloaded the EXE and instead of me taking an hour or two to find the right colors and without able to change the docked windows colors, I installed it, restarted SSMS and already love the dark […] [Issues adding and updating a column]( - Hi all  I'm hoping someone will able to say "you're an idiot because....." on this one.  We download a database but we have to add a column to a table and then update it. The code to add/update is as follows: IF NOT EXISTS ( SELECT * FROM UK_Health_Dimensions_New.INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_SCHEMA= 'ODS' […] [how can vs see SSIS under my regular user id but not my admin?]( - Hi, we run vs 2022.  I'm stumped how when i run VS as admin i cant see ssis after hitting create new project unless i want to import ssis or tabular.  but under my regular id i can see new ssis, import ssis, import tabular and new ssrs after hitting crate new project.  its been […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -