PostGres and SQL Server - Divided by a common language (2021-07-30)

SQLServerCentral Newsletter for July 30, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [PostGres and SQL Server - Divided by a common language]( - [Share your favorite Redgate tool – win technical training from Pluralsight!]( - [How to enable SQL Server trace flags]( - [From the SQL Server Central Blogs - Exporting Lists in SQL Multi Script]( - [From the SQL Server Central Blogs - SQLSaturday Orlando 2021]( Question of the Day - [Azure Dataset File Formats]( Featured Script - [Excel Formula to create SQL Script for a Values Table]( The Voice of the DBA  Daily Coping Tip Think about what you learned from a recent problem 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. Your MTTR There will always be situations and problems in our software systems. I know many people feel we might get beyond most issues, but as long as we continue to develop and deploy software, I know we'll have issues. Hardware fails, bugs slip through tests and are triggered by edge cases we never anticipated, or perhaps the data is unexpected. That last one might be one of the most common issues, and a source of many security issues. Too many developers think that quality will always be high, but that's not always the case. When things go wrong, how quickly can you get the system back up and running? Over time, a mean-time-to-recovery (MTTR) can help determine if you are getting a better handle on your environment or are things getting worse? Both your operation staff and your developers should better understand the system over time, and hopefully get broken applications back up and running quicker over time. Do you track your MTTR? Or if you're operations, maybe you track a mean-time-to-identification (MTTI). This is the time to actually figure out what's wrong. I don't know anyone tracking this metric, but that's an interesting one to note. If we can't identify problems quickly, or the MTTI grows over time, perhaps we have a training or turnover issue. Or perhaps we have a disconnect between developers and operations staff. Even in a DevOps environment where developers are responsible for parts of the production environment, there will be differing levels of ability, and this metric might help you identify who needs more training or practice in troubleshooting if the number rises. For most of my career, I've reported on uptime (or downtime) to management. That's not a bad metric, but it doesn't help the dev or Ops staff understand if where they might have problems. Many of us have ticketing systems where incidents are logged, and we add notes over time. Knowing how long it takes to find a problem and then fix it can be metrics that help you improve your system reliability over time. That's if you use them to do so. If these are just numbers to try and make your group look good to upper management, then someone will manipulate things, close tickets early or open them late. They might even be more willing to close a ticket quickly and open another one to reduce the MTTI and MTTR times. We can use metrics to improve how we work or just look good. One of these will help build an effective, efficient, strong department that does a great job building and running applications. The other usually ends up building an environment where quality stagnates, people don't stay longer than necessary, and keeps the traditional IT stereotypes alive. There will always be situations and problems in our software systems. I know many people feel we might get beyond most issues, but as long as we continue to develop and deploy software, I know we'll have issues. Hardware fails, bugs slip through tests and are triggered by edge cases we never anticipated, or perhaps the data is unexpected. That last one might be one of the most common issues, and a source of many security issues. Too many developers think that quality will always be high, but that's not always the case. When things go wrong, how quickly can you get the system back up and running? Over time, a mean-time-to-recovery (MTTR) can help determine if you are getting a better handle on your environment or are things getting worse? Both your operation staff and your developers should better understand the system over time, and hopefully get broken applications back up and running quicker over time. Do you track your MTTR? Or if you're operations, maybe you track a mean-time-to-identification (MTTI). This is the time to actually figure out what's wrong. I don't know anyone tracking this metric, but that's an interesting one to note. If we can't identify problems quickly, or the MTTI grows over time, perhaps we have a training or turnover issue. Or perhaps we have a disconnect between developers and operations staff. Even in a DevOps environment where developers are responsible for parts of the production environment, there will be differing levels of ability, and this metric might help you identify who needs more training or practice in troubleshooting if the number rises. For most of my career, I've reported on uptime (or downtime) to management. That's not a bad metric, but it doesn't help the dev or Ops staff understand if where they might have problems. Many of us have ticketing systems where incidents are logged, and we add notes over time. Knowing how long it takes to find a problem and then fix it can be metrics that help you improve your system reliability over time. That's if you use them to do so. If these are just numbers to try and make your group look good to upper management, then someone will manipulate things, close tickets early or open them late. They might even be more willing to close a ticket quickly and open another one to reduce the MTTI and MTTR times. We can use metrics to improve how we work or just look good. One of these will help build an effective, efficient, strong department that does a great job building and running applications. The other usually ends up building an environment where quality stagnates, people don't stay longer than necessary, and keeps the traditional IT stereotypes alive. Which one do you work in and which would you prefer? Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [PostGres and SQL Server - Divided by a common language]( David.Poole from SQLServerCentral A few lessons from a SQL Server DBA that is working on PostgreSQL. David Poole tells us where the similarities in the platforms can cause problems. [Share your favorite Redgate tool – win technical training from Pluralsight!]( Additional Articles from Redgate Level up your skills with Redgate’s latest competition. Redgate is giving you the chance to win a 3-month subscription to Pluralsight, the technical skills platform. To enter, visit the Redgate Forums and answer the question ‘What’s your favorite Redgate tool and why?’ [How to enable SQL Server trace flags]( Additional Articles from SimpleTalk DBAs can control many aspects of SQL Server’s behavior with trace flags. In this article, Robert Sheldon explains how to enable SQL Server trace flags. From the SQL Server Central Blogs - [Exporting Lists in SQL Multi Script]( Steve Jones - SSC Editor from The Voice of the DBA SQL Multi Script is a lesser known tool from Redgate Software that is designed to easily allow you to run scripts against many server instances with one click of... From the SQL Server Central Blogs - [SQLSaturday Orlando 2021]( Andy Warren from SQLAndy We’ve opened up registration and the call for speakers for SQLSaturday Orlando, to be held October 30th at the Orlando Marriott Lake Mary. In-person! We weren’t able to get...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Azure Dataset File Formats As of May 2021, which of the following file formats does Azure Data Factory not support? 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 Python Lambdas I have this lambda defined: x = lambda a: a * 2 What happens with this code? x('a') Answer: 'aa' Explanation: In many languages, this would return an error, but in Python, this doubles the character string. Ref: How to Use Python to Multiply Strings - [Discuss this question and answer on the forums](  Featured Script [Excel Formula to create SQL Script for a Values Table]( allinadazework from SQLServerCentral Here is an Excel formula that generates a SQL script from an Excel table ="SELECT "&CHAR(10)&" ["&TEXTJOIN("],[",FALSE,Table1[#Headers])&"]"&" FROM (VALUES "&TEXTJOIN(",",FALSE,IF(COLUMN(Table1)=1,CHAR(10)&"('","'")&SUBSTITUTE(Table1,"'","''")&IF(COLUMN(Table1)=COLUMNS(Table1),"')","'"))&") t1 (["&TEXTJOIN("],[",FALSE,Table1[#Headers])&"])" [More »](  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 [SQL Server Agent Jobs are not logging history]( - A few days ago, my SQL Agent Jobs stopped logging to the history table. They don't show up in view-history and they are not in the sysjobhistory table. I do limit to 10000 rows with max 500 rows per job, but I've also tried not limiting it at all. The SQL Agent servers runs under […] SQL Server 2016 - Administration [Table Size out of control..]( - In the dev environment there is a table that gets loaded with verbose log data by a DOT NET app. The only thing that I care to retain in that table are entries designated as ERROR level entries. There are a total of nearly 2 million entries into this table every hour. What do you […] [Updating MDS on SQL Server 2016 without updating DB engine?]( - Hi, I am currently investigating removing the need for Silverlight for MDS on a Shared SQL Server 2016 instance I have just inherited. I have been asked to do it as soon as possible. In my research this morning, I have found the following documentation: Upgrading Master Data Services, which specifies an upgrade path without […] [New Index Maintenance Methods Recommended by Microsoft]( - With reference to the following MS article... That article was where the old "Best Method" recommendation of Reorg between 5 and 30% and Rebuild at > 30% fragmentation used to live. You can verify that by doing an "edit" on the page and looking for where they made the change on lines 73 thru […] [AG is continually reporting failover due to mirroring connect error]( - Hello experts, I'm seeing this Always On Availability Group behavior around every 5 minutes. And it is not like the AG is moving back and forth - it is always saying it failed over the BARDB02. The AG group FOOAG01 failed over to BARDB02. In the SQL error log, I see this error, which I […] [Removing "to Disk" Backup Files]( - Hi, I am taking our SQL servers and changing the way we back things up. Right now we use ola scripts to go to disk. We are now going to use a 3rd party tool to take over the backups. Questions Besides trans log shipping, is there anything that SQL Server would use the backup […] [SQL Agent output to file]( - What would the preferred method of outputing a cmdexec step? cmdexec enter: d:\jobs\myjob\step1.cmd > d:\jobs\myjob\step1.log 2>&1 2. go to advanced page and enter output file there?   SQL Server 2016 - Development and T-SQL [Routine to Limit Query Results and Output to a File]( - Dear Group: Please note: I can only use solutions that involve SSIS and/or Stored Procedures due to limits placed on our team and environment. As much as I understand and agree there are better solutions, we are limited to only things that involve SSIS and commands that can be executed inside a stored procedure. We […] SQL 2012 - General [MSSQL 2012 -> MSSQL 2019 -> MSSQL 2012 - Older version of Database]( - Hi, I migrated a Database from MSSQL 2012 to MSSQL 2019 and that's supported. But, now i got this ingenious idea of migrating back from MSSQL 2019 to MSSQL 2012 since i made some changes in the database. SQL 2012 Version: Microsoft SQL Server Standard (64-bit) Version 11.0.7507.2 SQL 2019 Version: Microsoft SQL Server Enterprice(64-bit) […] SQL Server 2012 - T-SQL [datetime column, Day of Week with count, how to add month/yr grouping]( - SELECT count(*) ,DATENAME(dw, [myDateTime]) as DOW FROM [myDatabase].[dbo].[myTable] group by DATENAME(dw, [myDateTime]) this gets me what i need for like a total for day of week but what would i need to do in order to get like a grouping by month, DOW, Count and a grpuping of Year, DOW, count currently i get DOW, […] SQL Server 2019 - Administration [error when using date column with remote oracle table]( - I realize this might be out of scope for the forum but figured it's worth a shot... I have a process where I copy data from a table in our local SQL Server instance to a remote Oracle table via Linked Server. I can do the INSERT without any issue at all. I can also […] SQL Server 2019 - Development [How to extract date from a long string]( - Hi All, have a long string like below in my column and I need to extract only the date (2021-07-05) from it. Could any one please help Name - Koteswararao vp Department - Prod Location - Hyderabad Domain - ND Job Title - Technical-ABAP Date of Hire - 2021-07-05 Vendor ID - v2345 Candidate ID […] SQL Azure - Administration [Migrate On-prem DB]( - Hi Experts, Is there anyway to migrate database with filestream to SQL Managed instanace? Will Azure database migration service will help to achieve this task?  TIA General [Index frangmenting]( - Good day, What would cause fragmentation on the Unique Clustered index on a table (its one and only index) that is only ever read from, and never written to? Thanks, P SSDT [Having users access .dtsx (package file) saved on LAN]( - Hello All, I created a package file (.dtsx) via SSDT. I took the folder containing this file and placed it on a shared drive, with the other associated files there as well (see attached image). The package is being executed from an excel file via this command, dtexec /File \\path\to\file\filename.dtsx; The file executes for me […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -