Dynamic ELT with Azure Data Factory - Part 3 of 3 (2023-01-25)

SQLServerCentral Newsletter for January 25, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Dynamic ELT with Azure Data Factory - Part 3 of 3]( - [DevOps Process Improvement Course]( - [Azure Function and User Assigned Managed Identities]( - [From the SQL Server Central Blogs - Encryption, Comparing your Options in SQL Server]( - [From the SQL Server Central Blogs - You can run a SQL Server Docker container on Apple M1 and M2 Silicon]( - [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Question of the Day - [Azure Synapse Link for SQL]( The Voice of the DBA  Daily Coping Tip Be gentle with yourself when you make mistakes 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. Losing Track of Data I saw [this article]( a few months ago, which talks about engineers at Facebook not knowing where their personal data is stored. The engineers were being questioned in a legal matter, where they were asked to definitively state where all personal data for any human was stored by Facebook. Their answer was that they didn't think anyone in the company would be able to answer that question. Facebook has been controversial over the years, and plenty of people dislike the way the company conducts business. I noticed no shortage of people commenting on this situation, saying that Facebook should be shut down because they don't know where data is being stored. However, I don't agree. In working with lots of customers, on all aspects of how they handle, process, and manage data, I expect this to be a problem in many organizations. Whether large or small, whether they have few or many software engineers, it is highly possible that there isn't a good list of where personal data is being stored. As we work with customers to classify data with SQL Data Catalog, that process takes a long time, and very often the system administrators or developers who undertake take the task are unaware of all the places where data is stored. That's just in relational databases, ignoring all the Excel spreadsheets, text exports, mail merge operations, and uploads to services for mailing, analysis or something else. Very often the control of personal data is fragmented among groups, with there being few efforts made to coherently manage a customers' data. The world has adopted computing at an incredibly fast pace, often by people with little knowledge or forethought of the implications of gathering and processing data. In many cases, probably most cases, there is no overriding strategy. Just like with applications slapped together quickly, we find data being gathered and stored base on the requirements and demands of business people, with no planning for management or archival, and often not even with any security requirements. I liked the GDPR as a step forward, asking companies to not only handle data appropriately, but remove it when not needed, not use it without consent, and to be able to keep track and delete it if not necessary. I don't know that this has been successful, but it has changed handling practices in some organizations. At least in responsible organizations, and many of them have had to track down personal data to delete it. I'm not sure they know where it all is, but I at least assume they know where all of the data about a person is in their various relational stores. As a technical person, do you know where all data is stored about a customer? Are you sure you know where marketing has been keeping information and what other mailing, analysis, reporting, CRM, etc. systems they've put data? Any idea how many copies the operations group keeps? Test systems, QA, UAT, and others? What about test data sets, are they sanitized? Perhaps legal or finance has gotten extracts of data to reconcile their systems. Tracking down all data can be hard, and I'm not surprised Facebook struggles. I would guess engineers in many organizations would have similar answers. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Dynamic ELT with Azure Data Factory - Part 3 of 3]( Sarah from SQLServerCentral In part 3 of this series, see how you can use the principles from the series to build an Azure Data Factory pipeline. [External Article]( [DevOps Process Improvement Course]( Additional Articles from Redgate However chaotic or predictable your DevOps processes currently are, the new video learning pathway from the Redgate University will help you prioritize what to improve first, provide step-by-step guidance on how to implement those improvements, and show you ways to measure them. [External Article]( [Azure Function and User Assigned Managed Identities]( Additional Articles from SimpleTalk Let’s talk about authentication between Azure Functions and resources used by Azure Functions and conclude with many poorly documented secrets about how to use User Assigned Managed Identity. From the SQL Server Central Blogs - [Encryption, Comparing your Options in SQL Server]( david.fowler 42596 from SQL Undercover The question of encryption seems to be coming up a lot recently. I’ve had a number of people asking me about how to go about encrypting SQL Server. SQL... [Blog Post]( From the SQL Server Central Blogs - [You can run a SQL Server Docker container on Apple M1 and M2 Silicon]( Randolph West from Born SQL with Randolph West Previously the only way to run SQL Server on Apple Silicon was via QEMU emulation or Colima (hat tip to Anthony Nocentino). Docker released beta support today for Apple’s... [SQL Server 2022 Query Performance Tuning]( [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Steve Jones - SSC Editor from SQLServerCentral Troubleshoot slow-performing queries and make them run faster. Database administrators and SQL developers are constantly under pressure to provide more speed. This new edition has been redesigned and rewritten from scratch based on the last 15 years of learning, knowledge, and experience accumulated by the author. The book Includes expanded information on using extended events, automatic execution plan correction, and other advanced features now available in SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Azure Synapse Link for SQL What is the Azure Synapse Link for SQL? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Collins) Case expression with NULL You run this SQL Server code select case when 1=0 then null end; What does the query return? Answer: The query returns an error Explanation: The query returns the error message: Msg 8133, Level 16, State 1, Line 17949 At least one of the result expressions in a CASE specification must be an expression other than the NULL constant. Although no ELSE result is provided the expression default is ELSE NULL. Since both sides of the specification are the NULL constant the CASE expression returns an error. Ref: CASE - [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 - Development [Need to be written comment like "Lost"]( - Dear All, I am new in SSRS, I created one report like the attached file. (Design, Output) I want to add a comment on the last those customers who didn't purchase any item continue two months. if any challenges in understanding pls let me know.  SQL Server 2016 - Administration [Change the exsisting WSFC to a new WSFC in AG Configuration.]( - Hi; We have a production SQL Server setup with AG configuration. The WSFC is not configured properly there. So we had to face lot of issues past days. Now system engineers decided to create a new WSFC and recofigure the AG. If anyone can please share the SQL Server side configuration steps for this. Please […] SQL Server 2016 - Development and T-SQL [TEMPDB Version Store Growth when READ_COMMITTED_SNAPSHOT IS OFF]( - Why would the version store be growing for a database where READ_COMMITTED_SNAPSHOT is OFF? SELECT U.*,D.name,is_read_committed_snapshot_on FROM tempdb.sys.dm_tran_version_store_space_usage U JOIN sys.databases D on U.database_id = D.database_id WHERE reserved_space_kb > 0 order by reserved_space_kb desc Thanks [Help with Joins]( - Hi there, I'm new to T-SQL and am struggling with the join concept in practice. I'd like to join the results of multiple tables to one generic column called "Codes".  I have 7 different tables that use the same name, just have different codes depending on the table. Each row record has a unique identifier […] Administration - SQL Server 2014 [Operating system error 5 Even SQL Service account has Full permissions on folder]( - Hello SQL Experts, SQL Error – Unable to open the physical file. Operating system error 5: “5(Access is denied.)” I am also facing similar issue. Operating system error 5: “5(Access is denied.)”. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. We have SQL Server 2014 server. The SQL Service account […] SQL Server 2019 - Administration [Sql server Execution Plan table format]( - Hi I have searched on net but didn`t found much its possible to have output of sql server execution plan in table format ???? I mean non-graphical dbms like oracle/mysql must giving data in table format only .So its same possible in sql server too. Large/complex query execution plan are difficult to read so if […] [Where i can see logs for AlwaysON]( - Hi In case of SQL Server Failover Cluster we get sql server cluster log by executing command get-cluster log . Can we get saem in case of alwasy-on log or there is some other command Basically i want to check where i can see where can i see logs for Always-ON . is it in […] Amazon AWS and other cloud vendors [AWS : SqlServer to Postgree continous replication]( - Hi, I have RDS mssql serer database on aws. for few tables I want to replicate them continuously (once a day) on postgres. I can write tsql code with linked server to populate data on postgres, but I read on AWS articles that it only allows sqlserver to sqlserver (min standard edition) linked server connections. […] Azure Data Factory [who is using ADF and why?]( - I asked a question some time ago which orchestrator you guys would recommend. Airflow, prefect and Argo were the most common answers but there was also something else that stood out, no one liked Azure Data Factory. I once looked at it and I did not like it either, so it was not really a […] Reporting Services [SSRS Subscription email authorization options]( - We recently had a BI person leave the company and report subscriptions stopped being sent. They had setup a bunch of them and was the owner and since we never configured email at the server level all subscriptions used the owner credentials. We are now going to setup email at the server level using SSRS […] General [No experience with programming; how difficult is SQL for new prospect?]( - I'm looking at a career change. I have bachelors degree in healthcare management and pre counseling. My mental health is going down the drain and do not think I can sustain myself long term in the field of addiction. I was planning on going to grad school for counseling, but entry level data analyst positions […] Analysis Services [Run MDX Query against different Databases on the same server]( - Ahoy, i have few ssas server instances and many tabular models on each of the server. Now i want to gather structural metadata of these models. There are some DMVs which seem to get what i need: $System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS $System.DISCOVER_STORAGE_TABLE_COLUMNS $System.DISCOVER_STORAGE_TABLES But the problem seems to be that these DVMs can not get data across multiple […] PostgreSQL [Create the AS clause for Postgres crosstab dynamically.]( - This is a continuation of Dynamically creating columns for crosstab in PostgreSQL. To be clear, I cannot produce the AS clause if I have a dataset with a variable amount of keys. For example, a table with tests (testdate, pupil, outcome) that begins with the pupils (Tom, Dick, Harry) This would be the crosstab: select […] SQL Server 2022 - Development [How to select the data from xml]( - Dear All, My table have a xml column so how to select the data from xml column my data is: Best beauty advent calendars for Christmas 2022, including skincare & makeup from Liberty, M&S, Body Shop, MAC and Charlotte Tilbury. What top products are inside and when do they […] [Building Application to connect with SQL Server Tables and Edit records]( - Hello, I would like to build a Microsoft-based Application to use SQL Server Tables, and with this application there needs to be the ability to edit, delete, update etc records (in SQL Server). Other than Visual Basic, what other Microsoft Tools/languages are suitable ? Thanks !   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -