Managing Files and Folders with Python – Data Engineering with Fabric (2024-03-27)

SQLServerCentral Newsletter for March 27, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Managing Files and Folders with Python – Data Engineering with Fabric]( - [A First Look at SSMS 20]( - [What is Eventstream in Microsoft Fabric? - Part 1]( - [From the SQL Server Central Blogs - DevOps: It’s About the Team, Not Just the Tech]( - [From the SQL Server Central Blogs - What Does Redgate Test Data Manager Do?]( - [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Question of the Day - [Backing up the Resource Database]( The Voice of the DBA  The Cloud Database Cost Analysis There is a skill that I think DBAs and sysadmins will need to develop: cloud cost analysis. I've thought this was important for quite a few years, and I've been (unsuccessfully) lobbying for cost information to be gathered and analyzed in [Redgate Monitor](. Hopefully, this work will get done soon, as I see more companies asking their technical people to provide analysis and justification of the resources being billed for in the cloud. Basecamp analyzed its costs in 2023 and [decided it could save money by leaving the cloud](. I've seen other companies decide they were saving money in the cloud. Many, however, are likely unsure of the total return they get compared to the costs of cloud computing. I have seen some posts (like [this one]( that try to help you get a handle on your costs, but there is often a lot of complexity in cloud costs when multiple departments have different accounts (AWS) or subscriptions (Azure) with a provider. In many ways, I think the large cloud vendors haven't really considered how to support large enterprises that need a lot of resources, a lot of different administrators, and a wide variety of ways to both secure those resources as well as aggregate billing. I know we have an Azure account I didn't create, where I have very limited rights to do things, but I can access some resources. However, my group has an AWS account that appears to be sending us billing statements instead of our corporate finance group. What can be even more difficult to understand is that applications using the cloud might want a complete picture of billing, but various different technical groups might be responsible for the different parts of the system. Data services could be managed separately from web servers or application servers, with different groups creating, configuring, and destroying them as the needs change. Billing, however, be something accountants would want to separate by function/area/application, not group. Knowing all data services for all databases cost $xx/month is different than knowing the retail website (containing web servers, databases, and networking charges) costs $yy/month. This is far different from the days when one group was responsible for most of our computing resources. Often we had a group of IT staffers who managed hardware, ordered it, and could link costs of different machines to a specific application or department. Now we often don't have a central group who is even aware of all the resources that the company has provisioned. I suspect this will mean that many more technical people will not only be asked to account for the cloud resources being used, but also split out those costs in different ways, allowing people in finance departments to aggregate the different costs from the various technical groups. I don't know who will actually track network resources, but I suspect applications will often have this data included with the servers or services that access public networks. Controlling costs and carefully removing unnecessary or unused resources is going to be a continual problem in the cloud. I suspect that quite a few data professionals are going to be integral to helping manage this, especially for dev/test systems that are easily forgotten about over time. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Managing Files and Folders with Python – Data Engineering with Fabric]( John Miner from SQLServerCentral In this article, learn how you can manage files and folders for both full and incremental loading situations. [SQLServerCentral Article]( [A First Look at SSMS 20]( Steve Jones - SSC Editor from SQLServerCentral Recently Microsoft released SQL Server Management Studio (SSMS) v20.0, which is a major release of the primary tool that many of us use to work with SQL Server. Over the last few years, the tools team at Microsoft has worked to separate the tools from the various editions, giving us separate SSMS downloads. There have […] [External Article]( [What is Eventstream in Microsoft Fabric? - Part 1]( Additional Articles from MSSQLTips.com Learn about Eventstream in Microsoft Fabric to ingest, transform, and route real-time data to certain destinations in a low-to-no-code SaaS fashion. [Blog Post]( From the SQL Server Central Blogs - [DevOps: It’s About the Team, Not Just the Tech]( HamishWatson from The Hybrid DBA's Blog In November 2023 I did a session at PASS Summit and one of my sessions was voted That session was called DevOps is about Growing People rather than... [Blog Post]( From the SQL Server Central Blogs - [What Does Redgate Test Data Manager Do?]( Steve Jones - SSC Editor from The Voice of the DBA At the PASS Data Community Summit in 2024, Redgate launched Test Data Manager (TDM), which was a new product for us. You can watch the video linked above to... [Big Data Analytics cover]( [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Site Owners from SQLServerCentral Big Data Analytics with Spark is a step-by-step guide for learning Spark, which is an open-source fast and general-purpose cluster computing framework for large-scale data analysis. You will learn how to use Spark for different types of big data analytics projects, including batch, interactive, graph, and stream data analysis as well as machine learning. In addition, this book will help you become a much sought-after Spark expert.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Backing up the Resource Database How does the Resource database in SQL Server 2022 get backed up? 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) Join Limitations I have a query that uses a RIGHT OUTER JOIN. Which join hint cannot be used with this query? Answer: LOOP Explanation: The LOOP hint cannot be used with a RIGHT or FULL join type. Ref: Join Hints - [ [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 [need help in optimizing long running UPDATE statements]( - Hi All, Need help in tuning below UPDATE statements. Have this long running 3 UPDATE statements doing lot of reads. we r trying to refresh prod to one of sub-prod env. Will share the plans in some time. Is there a good way to optimize below queries? Few i can think of is, please provide […] [How Important archiving old data]( - Hi All, How important is it archiving old data ? In recent days, We saw some timeouts and blockings happening for app API calls to the database. Later, found that they are using timeout parameter in their connection strings, The database size is 5TB.This is kind of warehouse but used as OLTP mixed workload. Again, […] [Precuations of reducing the VLFs]( - Hi All, We have a database with 5TB size and has VLF near to 900. Transaction log file size 700GB. We are on SQL Server 2017 EE Always on availability group. During restores/db refreshes and during AG fail over times it is taking 30-45 mins to bring the database online. Question is how to reduce […] [blocking by MSDTC txn]( - Hi All, We are seeing MSDTC transactions are blocking other sessions within sql server. Is there a way to get more details of whats queries are running,isolation level etc .. within the MSDTC spid?  Regards, Sam  [blocking issue]( - Hi All, Today, I came across a blocking issue from the application. A very simple INSERT ran and the spid is in "sleeping" "AWAITING_COMMAND" with an open transaction and lock on the table. After 15 hours, then comes a spid which "truncate that table" and it is waiting on Sch-M lock. There is no implicit_Tran […] [some questions related to Ola hallengren maintenance scripts]( - Hi All, I have couple questions related to Ola Hallengren's maintenance script. If anyone has the answers, please do reply. 1. In which order the rebuild and update stats run. Is it based on tables sorted by name or something else? 2. Is there a way to tell how much % of work is left […] SQL Server 2017 - Development [Strange Behavior with MSOLEDB19 driver]( - We have recently changed many of our SSIS packages from using the depreciated SQL Native 11 driver to the now recommended Microsoft OLEDB 19 driver. Primarily because it support the MULTISUBNETFAILOVER attribute required when pointing a data connection at an availability group listener. Everything appeared to be working but then some packages randomly stopped working. […] [How to Alert When SQL Queries Exceed a Time Limit During Execution?]( - I've been exploring various methods to monitor and alert for long-running queries in SQL Server. I've tried 'extended events', 'resource governor', 'SQL Server Profiler', 'Database Query Analyzer', and even 'Custom Monitoring Scripts'. However, each approach seems to have its limitations. For instance, with extended events, I can't log events during query execution; it only captures […] SQL Server 2012 - T-SQL [Pivot 1 row to multiple columns using T SQL]( - Hello. I have data in a table that I want to pivot from 1 row to multiple columns. I only want the colums with dx to pivot if there is a value in them. Thanks. This is a sample of what I have: MemberNbr     dx       […] SQL Server 2019 - Development [Help with date range subquery]( - I have two large tables. One table(Table A) contains trouble tickets that are created when applications fail. The other table(Table B) contains warning notifications that are emailed whenever there may be an issue with an application. I need to write an efficient query that will select fields from Table A where a NotificationDate (Table B) […] [can i query roles and users from my various tabular databases]( - hi we've begun recording apps x roles at my company. Currently the apps are erp's but i think roles attached to tabular models would blend nicely, especially if i can find a way to also expand the groups assigned to certain roles. i'd feed the data daily. from what i've seen AD has some kind […] [create XML]( - Hello.. I want to create an XML file with the below structure. I have all the elements in respective tables. And I am using FOR XML AUTO , ELEMENTS to generate the file. My only issue is that I am not able to generate the incremental recordIdentifier tag that increases in value under each node […] [blank page appears after every printed page in subreport]( - Hi, i'm getting a blank page after every printed page of my invoices in both rendering and pdf export. In some of the tweaks i've done, i see the blank pages only intermittently. Below are images of the sub report and master report that calls the sub report. I'm calling the sub report for every […] [3rd Friday of the Month]( - Hi everyone I need to find the last business of the week. In most cases this will be a Friday. However, if Friday is a holiday then the last business day is Thursday. The way to know if a Thursday is the last business day is to apply the following logic...The data is stock market […] SQL Server 2022 - Administration [case for 'DBCC TRACEON - Trace Flags']( - Please share a situation when enabling and taking further advantage of DBCC TRACEON - Trace Flags made a serious difference for you, solved a problem, etc. - in other words, in what situation did you ENABLE a certain Trace Flag and it helped you tremendously? (in what, how..) THANKS!   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -