Tempdb Configuration for People in a Hurry (2021-06-05 DBW)

Database Weekly for June 5, 2021 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  Scaling Deployment This week there was [an interesting article on DevOps and Ancestry.com's DevOps process](. They are a fairly large enterprise, with 70+ teams that need to deploy code on a regular basis. As they've grown, there was some concern over each team learning how to best build their software pipeline. While they shared knowledge with each other, they had a diverse set of practices for deploying code. As a result, even though each team had a low chance of deployment failure, across 70 teams, that's a significant chance of a deployment failure, especially when most teams could deploy daily. They changed in a way that I've heard about in many companies. They started to coordinate the tooling and limit the number of ways people could deploy. The teams can still deploy code independently, without a large coordinated change review process, but by adhering to a standard process that reduces the chance of a failure for deployments. Their result: zero deployment-related outages for the first quarter. They have allowed teams to move have their own schedules, working on separate work on the website, which sounds great. Teams own all their own quality checks, and can deploy whenever they want to release code. All of this is great for velocity and feature delivery, but what about quality and shared services? There is a note that teams are accountable and they have lots of quality checks, but is that enough? One of the main problems I see with DevOps is that many organizations still have an "Ops" team that runs the production infrastructure. Even the large DevOps success stories, like Google, have an Ops team. For those stories, Dev still own the early support for their services until they mature. At some orgs, Devs own their code support always. However, in many smaller companies adopting DevOps, they forget this part. They don't ensure developers have to respond when their code fails to perform. That's important if quality is going to be a priority. The second thing this article, and many others, fail to discuss are shared services. There are certainly times when there is an API, or a database table that multiple teams and services have to access. What works well with 10 queries a second might fail when it's 10,000 queries a second. Who manages this? Who arbitrates competing needs or requests? Who deals with scale, which is often a part of quality? These are often some of the critical items in your architecture that need debate and resolution. DevOps is a great philosophy for building software, but it isn't magic. Your developers aren't automatically better code writers, and many of the issues you had with teams working together aren't solved by buying tools. Management needs to support, and adhere to the philosophy of looking at the entire system, getting feedback, and learning from that feedback. Too often we deploy fast without adding additional quality checks, or without getting the culture to change and have developers own more than just the building of features. They also need to own the support later. 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 [Running SQL Clone on an Azure VM with Azure File Storage]( How to use SQL Clone in the Azure Cloud, installing it on an Azure Virtual Machine, storing a copy of your SQL Server database as an 'image' on an Azure File Share, and then deploying multiple clones to another Azure VM or to a remote machine. [A Quick Diff Checker for SQL Server Databases]( Compare the schemas of two SQL Server databases using SQL Compare command line then quickly produce a diff report showing you immediately which tables, views and functions have changed. AI/Machine Learning/Cognitive Services [Using Flask to Build A Machine Learning API]( From DataScience+ Are you interested in guest posting? Publish at Da... [AI still sucks at moderating hate speech]( From Technology Review Feed - Tech Review Top Stories For all of the recent advances in language AI tech... [Uninstalling R, Python and Java on SQL Server 2019]( From Steve Stedman So you have upgraded to SQL Server 2019, and figuring that you got the new version, you may as well install all the features, including R, Python and Java,... Administration of SQL Server [TempDB configuration for people in a hurry]( From SQLServerCentral Blogs If you’ve ever had to play administrator to a SQL Server instance, you’ve probably had to deal with TempDB data or log files that have grown unexpectedly. I know... The... [Handling Unused Indexes]( From Callihan Data What do you do if you see that an index is being u... [How to fix operating system error code 80(The file exists.) in SQL Agent Job during BULK LOAD]( From SQLServer-DBA.Com SQL Server Agent job failure with an Operating system error code 80(The file exists.) error READ MORE>> Author: Jack Vamvas () [SQL Server performance tuning: Nine best practices]( SQL Server performance tuning can seem overwhelming when you don’t know where to start. In this article Robert Sheldon explains 9 best practices for performance tuning. [How to check a SQL Server connection is encrypted with SSL]( From SQLServer-DBA.Com Security team requested information about SQL Server encrypted connections and SSL READ MORE>> Author: Jack Vamvas () [Interesting things about Tempdb database]( From DEEPTHI GOGURI'S SQL SERVER BLOG While I was preparing for my Tempdb presentation, I learned many interesting facts about Tempdb. Thanks so much Bob Ward (t|g) for providing me with the resources to prepare... [How to create a DataKeeper Replicated Volume that has Multiple Targets via CLI]( From SQLServerCentral Blogs I often help people automate the configuration of their infrastructure so they can build 3-node clusters that span Availability Zones and Regions. The CLI for creating a DataKeeper Job... The... Analysis Services / BI on the MS Stack [Time Series Forecasting with Azure Analytic Services]( From BlueGranite Blog Despite the ubiquitous impact of time series on a variety of business scenarios, time dependent data can pose a challenge when it comes to modeling and forecasting. Typical regression... [Scalability – Part 2 Migrating AAS to PPU]( From FourMoo Thanks for reading and welcome to part 2 of my ser... Azure SQL Database [Azure SQL Database Restore to different server]( From SQLServerCentral Blogs It is quite a common requirement to restore a copy... Azure Synapse (SQL Data Warehouse and Data Lake) [Create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio]( From Kevin Chant Reading Time: 4 minutes In this post I want to co... [Moving Synapse Databases Between Subscriptions – Practical Guidance]( From Data on Wheels (Steve Hughes) One of the tasks, we often do with migration projects is move large volumes of data. Depending on how you are configured, you may need to do the migration... Career Growth and Certifications [Who’s Hiring in the Database Community? June 2021 Edition]( From Brent Ozar Unlimited Is your company hiring for a database position? Do... Conferences, Classes, Events, and Webinars [How to lead DBAs to embrace DevOps, rather than fear change]( In this 30 minute session, Kendra Little shares three key insights that will help you understand what motivates database professionals and how you can inspire your database staff to become agents for positive change. [The changing landscape of continuous database integration]( Considering introducing continuous database integration? Learn the principles in this ThoughtWorks webinar hosted by Pramod Sadalage, expert in this discipline and author of several books about database refactoring and development. [Getting more control over database deployments with Flyway]( Join Flyway expert Carlos Robles in this session to explore and discuss the new features of Flyway v7 which make it one of best database version control and migration tools in the market. [Announcing My 2021/2022 Training Class Schedule]( From Brent Ozar Unlimited If you want to attend my live online classes, it’s really easy: just buy a Live Class Season Pass and you can drop in on any class that works for... Data Mining / Data Analysis [Data is the New Oil: Strategies for Collecting, Refining, & Extracting Data]( From IT Pro - Microsoft Windows Information, Solutions, Tools Just like crude oil, raw data isn’t valuable in ... [Talking about data literacy in a big data world]( From AllAnalytics Our traditional assumptions about data are evolving, and so is our understanding of data literacy. Data is more than numbers, charts and graphs. And data literacy is not just for... Data Privacy, Compliance, and GDPR [Overcoming Common Data Privacy and Security Challenges in the Cloud]( From Dataversity Click to learn more about author Tejasvi Addagada.... [Why Data Privacy Should Be a Core Aspect of Protecting Human Rights]( From Dataversity Click to learn more about author Deepak Gupta. The U.N. Declaration of Human Rights recognizes privacy as one of the most basic rights that should be afforded to everyone. ... Data Visualisation [#SWDchallenge: reading into red]( From Storytelling with Data Color plays a lot of roles when it comes to visualizing information. Aside from making our messages more interesting to look at, color is an essential tool for focusing... DevOps and Continuous Delivery (CI/CD) [Ancestry's DevOps Strategy to Control Its CI/CD Pipeline]( From IT Pro - Microsoft Windows Information, Solutions, Tools Genealogy company sought a different approach to corral its software development and deployment. [How Value Stream Management Helps Optimize DevOps]( From IT Pro - Microsoft Windows Information, Solutions, Tools DevOps is largely about technology and process, but with value stream management it's possible to optimize the value of software application development. DocumentDB/Key-Value/Graph/other NoSQL Databases [The exabyte club: LinkedIn’s journey of scaling the Hadoop Distributed File System]( LinkedIn runs its big data analytics on Hadoop. During the last five years, the analytics infrastructure has experienced tremendous growth, almost doubling every year in data size, compute workloads, and in all other dimensions. It recently reached two important milestones. ETL/SSIS/Azure Data Factory/Biml [My pipeline is slow, what do I do?]( It was a Sunday, I was working on my tile as usual. Pulling a double shift on weekends has become a norm, but enough about that. This isn’t my construction blog. The email came in about Azure Data Factory. My friend happened to watch my video and reached out for help. Hardware [Samsung Galaxy Book Pro 360 15.6” Review: The Best Samsung PC Yet]( From Past News - RSS Feeds Earlier this month at Galaxy Unpacked 2021, Samsun... MDX/DAX [Auto-exist on clusters or numbers – Unplugged #22]( From Sqlbi Enter the complexities of auto-exist in SUMMARIZECOLUMNS, which returns different results depending on how the filters are applied to the filter context. Same formula, same model, but different results... [EDATE, EOMONTH – DAX Guide]( From SQLBI EDATE: Returns the date that is the indicated number of months before or after the start date. EOMONTH: Returns the date in datetime format of the last day... [Date and time parts – DAX Guide]( From Sqlbi DAY: Returns a number from 1 to 31 representing the day of the month. MONTH: Returns a number from 1 (January) to 12 (December) representing the month. ... Performance Tuning SQL Server [CHAR vs VARCHAR Memory Grants]( From Erik Darling Data Strings Were A Mistake While working with a client... [Empty Strings Aren’t Better Than NULLs]( From Erik Darling Data Scene Prove me wrong. [KEY LOOKUPS]( In working on my presentation for Data Saturday #8 – Southwest US, I hadn’t realized how many topics come up at least briefly in the talk. I wanted to make a few posts about to go into details on each of these topics and why they are important. [Is Dropping a Temp Table in a Stored Procedure Beneficial?]( From Callihan Data Generally speaking, it’s best to put things away... PowerPivot/PowerQuery/PowerBI [BREAKING CHANGES IN POWER BI]( This post reviews the deployment of common changes to Power BI datasets and the implications for the availability of the affected objects for users and reports. [Blog post about Copying Azure files to SharePoint, comparing Logic App and Power Automate Flow for this, and Trust No One]( From SQLServerCentral Blogs (2021-May-31) Recently, I created a prototype solution for one of the projects, that would copy data files from Azure to SharePoint location. The task is trivial, doesn't require a lot... The... [Doing Power BI the Right Way: 10. Designing and Managing Large Datasets]( From Paul Turley's SQL Server BI Blog Part of the the series: Doing Power BI the Right Way Later in this post, you will find a 27 minute framed video including a series of interviews and discussions... [Working With Multiple Row Headers From Excel in Power Query]( From eHansalytics It is fairly common for users to format Excel reports with headers that are comprised of two or more rows for the header, rather than using a single cell... Product Upgrades and Releases [Updated First Responder Kit and Consultant Toolkit for June 2021]( From Brent Ozar Unlimited If you want to quickly understand the top health and performance issues on your SQL Server, there’s no better, easier, free-er way to do it than the open source... [SQL Server Diagnostic Information Queries for June 2021]( From Glenn Berry Introduction These are my SQL Server Diagnostic In... [Amazon Location Service Is Now Generally Available with New Routing and Satellite Imagery Capabilities]( From AWS News Blog In December of 2020, we made Amazon Location Service available in preview form for you to start building web and mobile applications with location-based features. Today I’m pleased to... Replication [SQL Server Replication Publishers and non-standard SQL Server Ports]( From DCAC People use non-standard ports for the default instance of SQL Server. What I mean by this is a port number other than 1433. In you use a non-standard port... SQL Server Security and Auditing [SQL Homework – June 2021 – SQL Audits]( From SQLServerCentral Blogs Last month I had you work on Extended Events, so hopefully now you are a bit more comfortable with them. ... Continue reading The post SQL Homework – June 2021... Security News and Issues [Attack on meat supplier came from REvil, ransomware’s most cutthroat gang]( From Ars Technica Criminals use high-pressure tactics to extort vict... [Microsoft 365: Most Common Threat Vectors and Defensive Tips]( From IT Pro - Microsoft Windows Information, Solutions, Tools Security pros discuss the most typical ways attackers leverage Microsoft 365 and share their guidance for defenders. [Ransomware will now get priority treatment at the Justice Department]( From Ars Technica Directive comes as ransomware is exposing the frag... [This is not a drill: VMware vuln with 9.8 severity rating is under attack]( From Ars Technica Code execution flaw in vCenter is exploited to ins... [The DarkSide Ransomware Gang]( From Schneier on Security The New York Times has a long story on the DarkSide ransomware gang. A glimpse into DarkSide’s secret communications in the months leading up to the Colonial Pipeline attack reveals... [The SolarWinds hackers aren’t back—they never went away]( From Ars Technica A new phishing campaign is less an escalation than... [Security Vulnerability in Apple’s Silicon “M1” Chip]( From Schneier on Security The website for the M1racles security vulnerability is an excellent demonstration that not all vulnerabilities are exploitable. Be sure to read the FAQ through to the end. EDITED TO ADD:... T-SQL [TSQL Basics Part 5: SEMI JOIN – Video Explanation]( From Steve Stedman This is part 5 of a 19 part series on TSQL Basics.... [Use BCP to create a CSV (comma delimited) file from a table.]( From SQLServerCentral Blogs This is a pretty handy little tool in your arsenal... [How to get count of GROUP BY columns]( From SQLServer-DBA.Com A developer wanted to know about GROUP BY and COUN... [Why Expressions Are Better Than Local Variables]( From Erik Darling Data Often Enough I see people writing stored procedures that use local variables either because they read that all functions in a where clause are dangerous, or because they need... [Introduction to Synonyms in SQL Server]( From SQL Server – {coding}Sight Total: 3 Average: 4 A synonyms in SQL Server are database objects that give an alternative name to the database objects existing locally or on the remote server. Also,... [Inlining Financial Functions]( From Erik Darling Data Big Tuning I work with a lot of clients who do things with money. Loads of money. I’m a very trustworthy person. At just about every client site, I... [Data, N-Tiles & Medians]( From Simple Talk Joe Celko takes a look at how SQL Server can do some of the work for you such as calculating medians and enforcing referential integrity.… The post Data, N-Tiles &... Tech News [Semiconductor Shortage Hurts Network Equipment Makers and Customers]( From IT Pro - Microsoft Windows Information, Solutions, Tools Semiconductors are scarce, and so are many types of network gear. The wait for things to get back to normal may be a long one. [Stack Overflow sold to tech investor Prosus for $1.8 billion]( From Ars Technica "Don't expect to see major changes or awkward syne... [Microsoft Teams Up With Voting Machine Maker to Let Voters Track Ballots]( From IT Pro - Microsoft Windows Information, Solutions, Tools The collaboration between the voting vendor and software giant aims to use technology to build transparency into the U.S. voting system, which came under assault last year. [Reducing poverty can actually lower energy demand, finds research]( From Ars Technica Turns out human development is a matter of economi... [Big Tech sues Florida, saying social media law violates First Amendment]( From Ars Technica Facebook/Twitter lobby sues over law that protects politicians from "censorship." The Lighter Side [NASA is ending its 30-year Venus drought with two new missions]( From Technology Review Feed - Tech Review Top Stories The last time NASA launched a dedicated mission to... [Researchers perform magic tricks for birds, who are not amused]( From Ars Technica Can an animal without hands recognize when humans ... [Yes, the military is serious about rocketing supplies around the planet]( From Ars Technica "A rocket can get around the world in 90 minutes, ...  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -