Now Is a Good Time for Choices (DBW 2020-03-28)

 Database Weekly for March 28, 2020 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge Editorial  Now Is a Good Time for Choices The whole world is going through some interesting times. Chances are pretty high that you’re working from home and your principal interaction with others is through virtual means. I know I’m that way as is my company, [Redgate Software](. It can be challenging or rewarding, depending on how you go about it. What’s even more challenging though are all our virtual interactions, whether through online meetings or social media. That’s why I think this is a time to start making some careful choices. There’s a lot going by online now. Whether we’re talking about your kids school getting cancelled, your job moving to remote work, your favorite event getting cancelled or postponed, or the endless news posts. With all this online content, it’s really easy to make quick, hot takes on just about everything. However, I strongly advise you to make a choice. Stop. Think for just a second. Is the post, tweet, response, forward, whatever, going to help people or hurt people? If it’s going to help, great. If it’s an actually informative, positive, fact-filled post, then, yes, it’s probably needed. Heck, if it’s just simply fun, it’s absolutely needed. If it’s filled with negative emotional content, there’s a great chance that now is absolutely not the time to share it. Let’s make some choices now to ensure that we’re assisting through the current unpleasant situation. Let’s choose the positive outcomes. Let’s choose the supportive approach. Let’s make the choice to not add to people’s woes and problems. For most of you, this is what you’re already doing. However, all of us can slip up. Stop. Think. Then post. We’ll all be better for it. Steve Jones - SSC Editor [Join the debate, and respond to the editorial on the forums](  The Weekly News All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. Vendors/3rd Party Products [Database DevOps is gaining popularity]( Fourty-six percent of the State of Database DevOps survey respondents advised they are performing some form of deployment automation. This figure has risen year on year as the conversation of DevOps in Database development continues, and the benefits become more apparent. If you are interested in more insight, you can download a copy of the 2020 State of Database DevOps report here. Download now Administration [Low Latency Memory]( For the last twenty years, the standard practice h... [sp_helpindex2]( this is my extended version of sp_helpindex sp_hel... [SQL SERVER – Error 9002: The Transaction Log for Database ‘SQLAuthority’ is Full Due to ‘REPLICATION’]( In this blog, I would explain the cause and fix of... [How was a plan forced?]( If you use Automatic Plan Correction, and thus also Query Store, you may wonder how was a plan forced: manually or automatically with APC? The type of forced plan... [Extended Events: Live Data Explorer, Grouping]( Of all the things that Extended Events does, I’v... [Using relog: Creating a smaller file](  Today I had to remember the steps for using relog. If you’ve never heard of relog, stay with me. It’s a tool for merging PerfMon files, and also... Azure CosmosDB [Explaining Optimistic concurrency in Azure Cosmos DB with REST API]( Optimistic Concurrency Control is important for many applications and databases. Let me try to explain what it is without using any fancy technical words. Let’s say you are creating a web application and you have an update page which lets your users to update some type of model in your application. Azure Databricks, Spark and Snowflake [Using Azure Key Vault secrets in Azure Databricks]( Azure Databricks is a core component of the Modern Datawarehouse Architecture. Its features and capabilities can be utilized and adapted to conduct various powerful tasks, based on the mighty Apache Spark platform. In this blog we are going to see how we can connect to Azure Key Vault from Azure Databricks. [Advanced schema management for Spark applications at scale]( Over the years, the popularity of Apache Spark at LinkedIn has grown, and users today continue to leverage its unique features for business-critical tasks. Apache Spark allows users to consume datasets using powerful, yet easy-to-use APIs such as the Dataset interface. Azure DevOps [Cannot delete old build definitions in Azure DevOps]( I have been experiencing a problem for quite a while now in my current environment, in that some of our old builds cannot be deleted. When you attempt to do so it results in the following error: [Azure DevOps, Software Development, and Git Part 4 – Command-Line with Azure DevOps]( I’m writing about my journey with git, sharing my experiences along the way. The series to date: Azure DevOps, SSIS, and Git Part 0 – Getting Started Azure DevOps,... Azure SQL Database [Azure SQL Database: Great Choice for Modern Cloud Applications]( Microsoft offers several options for moving your S... Career Growth and Certifications [Microsoft Delays Retirement of MCSE, MCSA, and MCSD Certifications]( A few weeks ago, Microsoft made a significant anno... [Telecommute Resources]( This is a summary of potential resources for any tech person who is interested in working from home – temporarily or full time. It came about from a twitter... Computing in the Cloud (Azure, Google, AWS) [New – Low-Cost HDD Storage Option for Amazon FSx for Windows File Server]( You can use Amazon FSx for Windows File Server to ... [What’s Different about Backup to the Cloud?]( A lot of businesses have begun to incorporate the cloud into their backup processes and most businesses that haven’t done so yet are looking into the process. Using the... [European users reporting they're hitting Azure capacity constraints]( Some Microsoft Azure cloud customers in Europe are reporting they are hitting capacity limits are unable to spin up virtual machines in their regions. [Moving Your SQL Workload to the Cloud Â]( Every day, more IT organizations decide to move th... Conferences, Classes, Events, and Webinars [New Instructor Led Online Power BI Training Classes]( Well Covid-19 is a right royal pain in the backsid... [PASS Marathon GDPR Edition: Recordings now available!]( You have certainly already heard about the new Eur... [BuildforCOVID19 Global Online Hackathon]( The COVID-19 Global Hackathon is an opportunity for builders to create software solutions that drive social impact with the aim of tackling some of the challenges related to the... [Database Reliability Engineer- The new DBA?]( Free webinar - Date and time: Tuesday March 31, 4-5pm BST / 10-11am Central - The role of the DBA is evolving, with automated builds, cloud and DevOps being the new A,B,C,Ds in the day to day management of databases, you need to up-skill and learn about Database Reliability Engineering. [Redgate Streamed]( Join us from April 1st – 3rd for Redgate Streamed, which brings together world class industry speakers, technical content and opportunities to network with your peers. [It’s Not April Fools – Redgate Streamed is Coming](  DMO/SMO/Powershell [Understanding $PSDefaultParameterValues in PowerShell 7]( Do you have that one PowerShell command you use constantly? If that command has parameters, you have to manually specify the parameter values every time. If you find yourself... Data Privacy, Compliance, and GDPR [Figuring Out How To Hide Production Data]( There’s a really simple conundrum that we go thr... Data Visualisation [What is a line graph?]( Line graphs (or line charts) are best when you want to show how the value of something changes over time, or compare how several things change over time relative to each other. Whenever you hear that key phrase “over time,” that’s your clue to consider using a line graph for your data. [Stress Cases and Data Visualization]( Times are stressful right now. There is an ongoing... DevOps and Continuous Delivery (CI/CD) [DB Automation with Azure DevOps]( I sincerely believe the key to your future as a DBA is your ability to automate everything you do. However, the single hardest thing that you have to do... The... [Time To Learn Git]( I love this quote from Kevin Hill (and not because he mentions me): 3 things I can no longer justify ignoring: #dbatools Git and #Docker for my dev SQL work@cl@sqldbawithbeard@Kendra_Little and @unclebiguns@GFritchey, I blame you... The... [The Staging Phase of Deployment]( Despite some humorous examples of deployments gone wrong, failures are not funny. William Brewer explains why staging is so important and how it can help avoid the types of disasters he recalls in this article. [DevOps is Essential for Analytics]( Database DevOps is essential for DataOps. In the end, Database DevOps means accurate and consistent data which is critical for analytics, artificial intelligence, and machine learning. ETL/SSIS/Azure Data Factory/Biml [Source-Control SSIS Catalog Contents in Azure DevOps]( I did a thing – actually a couple things – that I’ve been dreaming about for a while. In a couple recent blog posts – SSIS Catalog Compare Enterprise... [Transforming JSON to CSV with the help of Flatten task in Azure Data Factory - Part 2 (Wrangling data flows)]( (2020-Mar-26) There are two ways to create data flows in Azure Data Factory (ADF): regular data flows also known as "Mapping Data Flows" and Power Query based data flows also... The... HA/DR/Always On/Clustering [SQL Server HADR overview]( My Pluralsight course for new SQL Server DBAs Image credit to Jeff (t) Back in June of 2019, I published this YouTube video covering the highlights of the various... Hardware [Greenliant Launches 1.92 TB M.2 Industrial SSDs]( Greenliant revealed on Wednesday that it has started shipments of its new industrial-grade ArmourDrive M.2 SSDs. The enhanced-durability drives are rated to operate in a much wider range of... [TeamGroup Announces 32GB T-Force Vulcan Z and Dark Z DDR4 Modules]( One of the world's largest DRAM memory manufacture... [Dell & HPE Issue Updates to Fix 40K Hour Runtime Flaw in Enterprise SSDs]( In a second SSD snafu in as many years, Dell and H... MDX/DAX [Controlling Format Strings in Calculation Groups]( This article describes how to control format strings in calculation groups. Before starting, we suggest you read the previous articles in this series. Each calculation item can change the... Microsoft News [Microsoft Teams-Slack calling integration is coming, says Slack CEO]( Slack may be adding yet another Microsoft integrat... [Microsoft will pause optional Windows 10 cumulative updates for testing purposes starting in May]( Microsoft is prioritizing its Windows update focus on security updates and is temporarily ceasing its optional Windows 10 cumulative updates starting in May. Performance Tuning SQL Server [SQL SERVER – Free Performance Tuning Videos]( Let me share today some of the Free Performance Tu... [What is the Difference Between sql_handle and plan_handle?- Interview Question of the Week #269]( Question: What is the Difference Between sql_handl... [SQL Plan Warnings]( There are many methods available for optimizing the performance of SQL Server. One method in particular is examining your plan cache, looking for query plan warnings. Plan warnings include... [An approach to index tuning – Part 1]( Erin Stellato walks through her multi-step approach to index tuning, with thorough explanations and simple but clever visualizations. The post An approach to index tuning – Part 1 appeared first... [sp_HumanEvents: Tracking Queries and Waits]( Is It Really So Strange? … PowerPivot/PowerQuery/PowerBI [Text Recognizer with AI Builder]( In this short blog, I am going to show how amazing the text recognizer works in Power Apps and AI Builder. [Capturing Power BI queries using DAX Studio]( I recently published a new blog post on SQLBI. You... [Make navigation easier with Power BI buttons]( Power BI buttons got a little more power! Navigati... [Using Precedence to force Power BI Aggregations]( Are your Power BI Aggregations not matching? Setti... [Three Reasons to Consider Power BI Premium]( Inspired by a conversation we recently had with our COO, my colleague (and Microsoft MVP), David Eldersveld, tweeted a question. “What is the TOP reason you use Power BI... [Using Single Sign-on with the Power BI Gateway]( Let's dig into the Power BI gateway and Single Sig... [Updated Data Profiling in Power BI Desktop]( In this month’s (October 2019) release of Power BI Desktop, they have added a ton of cool stuff, you can read all about it via the Power BI Blog. ... Product Reviews and Articles [How PASS won the DBTA Database DevOps Innovation Award for fully automating their database deployments]( On March 2, DBTA announced PASS, the Professional Association for SQL Server, as the winner of its inaugural Database DevOps Innovation Award. Redgate is extremely pleased with the news... [Adding Reports Users to SQL Monitor]( SQL Monitor 10.0.5 now supports a user role called "Reports user". A user added to this role, by an administrator, will be able to 'self-serve' SQL Monitor reports but... Product Upgrades and Releases [Updated First Responder Kit and Consultant Toolkit for March 2020]( In the immortal words of “Orange” Julius Caesar as written by Shake’s peer, “Beware the bugs of March.” It took us 2,064 years, but we’ve finally fixed those bugs,... [Calculation Groups section in the Mastering DAX Video course]( I am glad to announce that we just released an additional module about Calculation Groups to the Mastering DAX video course. All current students of the video course will... Python [Creating a Python function to calculate Pi]( Pi is 3.14159 to 5 decimal places.To work out Pi, we will be using Leibniz’s formula:X = 4 – 4/3 + 4/5 – 4/7 + 4/9 – …This series... [Getting into Python by Referencing PowerShell]( Words: 653 Time to read: ~ 3 minutes. Same Trap, Different Language I’ve been trying to improve my knowledge of Python for the past while. I’ve fallen into the... Security News and Issues [Windows code-execution zeroday is under active exploit, Microsoft warns]( There's no patch available now. Here's what to do ... [Microsoft Issues Security Advisory for Zero-Day in Adobe Type Manager Library]( Microsoft said on Monday that it is aware of an unpatched vulnerability in the Windows Adobe Type Manager Library that is actively being exploited by hackers. The post Microsoft Issues... Software Development [sp_HumanEvents: Code Review]( VeeOner … [Moving to a New SqlClient]( DotNet developers, if you’re using the System.Data.SqlClient, stop. Move over to Microsoft.Data.SqlClient now. It’s easy to do, well, it’s relatively easy to say that. The actual work isn’t that... The... T-SQL [T-SQL 101: #62 Calculating date values from day month and year in SQL Server T-SQL using DATEFROMPARTS]( I mentioned in earlier posts that there's no standard way to write dates, so we end up having to write them as strings. Now that was a real problem... [DROP IF EXISTS]( It’s amazing the things that SQL can do these da... [Why you should never use default string lengths.]( TL;DR; You probably don’t know what you’re goi... [Reduce CPU of Large Analytic Queries Without Changing Code]( Microsoft introduced several features called Intelligent Query Processing for SQL Server in 2017 and 2019 that can improve performance without making changes to code. In this article, Greg Larsen demonstrates the feature Batch Mode on Rowstore. Tech News [Folding@Home Reaches Exascale: 1,500,000,000,000,000,000 Operations Per Second for COVID-19]( Folding@home has announced that cumulative compute... [Broadband speeds fall in dozens of big US cities during pandemic]( Download speeds fell at least slightly in 88 of 20... [How to get Verizon and AT&T data-cap fees waived during the pandemic]( Verizon, AT&T waive mobile fees if you tell th... The Lighter Side [2019 saw over 60 gigawatts of wind power installed]( Slower growth likely as attention shifts and pande... [SpaceX has won a big NASA contract to fly cargo to the Moon]( “This is another critical piece of our plan to r... [Aston Martin designs a new V6 for James Bond—and the Valhalla hypercar]( The mid-engined Valhalla is due in 2022, but Bond ... [Daily Coping–24 Mar 2020]( I’ve started to add a daily coping tip to the SQ... [This is why everyone is hoarding toilet paper]( A consumer behavior researcher explains why toilet... [The First Time I Had to Restore a Database]( I remember it really clearly. In the mid 1990s, long, long before I went into IT as a career, I was working at a photo studio in Muskegon, Michigan.... [How computational power—or its absence—shaped World War naval battles]( Situational awareness before GPS and computers was... Tools for Development [Working around sqlcmd on Mac OS issues]( So I’m busy working on my new dbaSecurityScan mo... [Going Dark in SSMS]( I haven’t been a big fan of dark mode in many to...  [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. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -