Empowering Lakehouse Users – Data Engineering with Fabric (2024-05-22)

SQLServerCentral Newsletter for May 22, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Empowering Lakehouse Users – Data Engineering with Fabric]( - [Troubleshoot SQL Server Always On Availability Groups with SQL LogScout]( - [From the SQL Server Central Blogs - Migrate datetime data to datetimeoffset with AT TIME ZONE]( - [From the SQL Server Central Blogs - Bicep Your Elastic Jobs]( - [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Question of the Day - [LOGON Trigger Count]( The Voice of the DBA  Invisible Downtime [This article]( has a concept I've never heard about: invisible downtime. This is the idea that there are problems in your application that the customer sees. Your servers are running, but the application doesn't work correctly or is pausing with a delay that impacts customers. From an IT perspective, the SLA is being met and there aren't any problems. From a customer viewpoint, they're ready to start looking at a competitor's offering. Lots of developers and operations people know there are issues in our systems. We know networks go down or connectivity to some service is delayed. We also know the database gets slow, or at least, slower than we'd like. We know there are poor-performing code and under-sized hardware, running with storage that doesn't produce as many IOPs as our workload demands. We would also like time to fix these issues, but often we aren't given any resources. The current buzzword among executives and senior IT leaders is observability. It's the goal of looking at how our entire system, application, database, and network, are linked and performing with an eye on improving performance. Not because they want to spend time or money here, but because customers are becoming more fickle and quick to move to another offering. Leaders know that degraded application performance (another phrase for invisible downtime) can have real bottom-line impacts on revenue. There are a lot of products in this space, application performance monitoring (APM), designed to look at lines of code and determine how well each is performing. They can help you spot issues in application code, but they lack insight into database and network details, at least at a level that the experts need. As a result, digging into performance issues and root cause analysis of problems usually means pulling data from multiple sources and correlating log entries. This is likely an area where AI/ML technologies can help, especially across large estates, though I think in many cases, what we need is just a pointer to poor-performing code. C#, Java, SQL, whatever. We need to know where the bad code is and then we need to train developers to write more efficient code. That might be the best way to improve application and database performance. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Empowering Lakehouse Users – Data Engineering with Fabric]( John Miner from SQLServerCentral Learn how to use the OneLake Explorer and Data Wrangler extension in VS Code to empower users to work with data in Microsoft Fabric. [External Article]( [Troubleshoot SQL Server Always On Availability Groups with SQL LogScout]( Additional Articles from MSSQLTips.com We experienced several unplanned outages and failovers on our SQL Server Always On Availability Groups. We want to know the root cause to prevent them from happening in the future. How do we identify the root causes of unplanned Availability Group outages and failovers? [Blog Post]( From the SQL Server Central Blogs - [Migrate datetime data to datetimeoffset with AT TIME ZONE]( Will Assaf from SQL Tact I recently reviewed, worked on, and added a similar example to the DATETIMEOFFSET Microsoft Learn Docs article at the recommendation of my colleague Randolph West, who guessed (accurately) I... [Blog Post]( From the SQL Server Central Blogs - [Bicep Your Elastic Jobs]( hellosqlkitty from SQLKitty I posted on Terraform and Azure SQL last year but wanted to see what Bicep could do this year. I’m going to test Bicep out with Elastic Jobs. TL;DR—My... [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Site Owners from SQLServerCentral Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.   Question of the Day Today's question (by Steve Jones - SSC Editor):  LOGON Trigger Count I have the need in SQL Server 2022 to create a complex set of checks for logins. I want to use Longon triggers, but I'd like to separate out logic. How many Logon Triggers can I create? 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) Multiple Query Trace Flags I want to enable two trace flags, 4199 and 4137, for a single query. How should I do this: -- A SELECT a.AddressID , a.AddressLine1 , a.AddressLine2 , a.City , a.StateProvinceID , a.PostalCode , p.FirstName , p.LastName FROM Person.Address a INNER JOIN person.Person AS p ON p.rowguid = a.rowguid WHERE City = 'SEATTLE' AND PostalCode = 98104 OPTION (QUERYTRACEON 4199, 4137); -- B SELECT a.AddressID , a.AddressLine1 , a.AddressLine2 , a.City , a.StateProvinceID , a.PostalCode , p.FirstName , p.LastName FROM Person.Address a INNER JOIN person.Person AS p ON p.rowguid = a.rowguid WHERE City = 'SEATTLE' AND PostalCode = 98104 OPTION (QUERYTRACEON 4199, QUERYTRACEON 4137); -- C SELECT a.AddressID , a.AddressLine1 , a.AddressLine2 , a.City , a.StateProvinceID , a.PostalCode , p.FirstName , p.LastName FROM Person.Address a INNER JOIN person.Person AS p ON p.rowguid = a.rowguid WHERE City = 'SEATTLE' AND PostalCode = 98104 OPTION (QUERYTRACEON 4199), (QUERYTRACEON 4137); Answer: B Explanation: If you want multiple flags, you need to repeat the QUERYTRACEON keyword for each one, separated by a comma. This is in the same OPTION clause. Ref: Query 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 [Always on With Different DB name on Secondary]( - Hi Guys I have a request to configure Always ON with the secondary having a different database name Is that possible?  Thanks SQL Server 2016 - Administration [Table partitioning best practice]( - I've inherited a couple of rather large databases from my ex-colleague when I join this company. Today, a developer reached out to me to inform me that in that databases there's a couple of large tables which has partition & they would want to partition ALL of the tables in the DB. Bear in mind […] SQL Server 2016 - Development and T-SQL [Script to Merger duplicate indexes]( - Hello, I am trying to firgure out how to merge duplicate indexes using a script; any help will be greatly appreciated. SQL Server 2019 - Development [SQL Server Encrypt data into a file, send it and then decrypt]( - First off, my apologies for what could potentially be a bad title! I am looking for some general information. I may have a potential project where I need to pull data from a SQL Server, populate a parquet file and then SFTP this parquet file to the end user. Seems simple enough. The caveat here […] [Change IN for EXSITS]( - I have had a hard time to understand how to use EXISTS. I Always use IN instead. Here it dont work, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." Someone who can rewrite this: SELECT minutes FROM dbo.timeaccountmovement WHERE ownertype = 1 AND timeaccountid = […] [get all txt files $filenameAndPath = code please help]( - Hello I need to get txt files from directory and send email, when I put name of the file my code is working, but I need to get all txt files from directory and dont know which to use,please help me thank you here is my code: This code is working,just not sure how select […] SQL Azure - Development [using Openrowset to access delta files in ADLG2]( - Dears, I was told that I cannot access delta files in SQL Server Managed instance. Those delta files are generated by synapse spark But we need to move this information into a SQL Server Managed instance Hence we have created a layer (curated layer) in synapse which converts this deltas into parquet files. Goal is […] SQL Azure - Administration [Azure SQL how to grant ALTER DATABASE permission]( -  I have the user Managed identity , granted it carte blanche alter with GRANT ALTER TO successfully at the application database level with the intent to give the user permissions to alter all tables of all schemas. But before the index maintenance job runs, I have to auto scale up using ALTER […] SQL Server 2005 Integration Services [Remove comma inside Comma Delimited File csv in SSIS Using Script task]( - Hi, I have two tables: one for headers with 9 fields and another for lines with 6 fields. Both header and lines are highlighted in yellow. I need to combine both the headers and lines using a UNION operation to display the result below. From the line table, I need to add 3 empty fields […] Oracle [Subscription to Oracle DB not working after upgrading subscriber to Oracle 19c]( - Hi Replication and publisher configured on the same sql server 2022 version. Subscription to Oracle database was configured and worked without any problem till the subscription Oracle database was on 11g. The subscriber Oracle database version was upgraded from Oracle 11g version to Oracle 19c. The job creates *.pre, *.sch, *.bcp scripts on the repl […] SQL Server 2022 - Administration [Clear Trace - Asking for SQL Server 2008]( - Hi, I have SQL Server 2019 installed and when go the Clear Trace database created on it. When I try to use the Clear Trace tool, the tool is prompting to configure SQL Server 2008 and not allowing to use my trace files. I tried to download SQL Server 2008 from Microsoft Download Central but […] [Always on Availability groups cluster question]( - Hello, I have a question regarding Availability group server architecture. A little background: We want to convert our 20 FCI instances to Availability groups. Those instances will be converted by moving all databases from one FCI instance to one standalone instance (which is on one virtual machine). That means 20 VMs for primary nodes and […] [AG listener cant be removed]( - Testing with AG on Linux with Cluster=NONE. it was all going ok and as expected but now I have a very strange situation after a failover - AG listener is "null" - so I can not drop it and in the same time I cant add a new one cause one already exist. Anyone experienced […] SQL Server 2022 - Development [Error while an ADF pipeline runs stored procedures against Azure SQL Server MI]( - Dears, We are using Azure Data factory pipes to run some stored procedures against a SQL Server Managed Instance in the cloud Almost every day while running we receive the following error : Error Message: Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 64. Error […] [Inserting 100K rows Performance - Baseline Performance]( - We're trying to understand how quick new versions of SQL server can be. Obviously server specs come into play. Can anyone run this simple test and share the output (ms). Create simple Table CREATE TABLE [dbo].[Data]( [Id] [int] IDENTITY(1,1) NOT NULL, [Comment] [varchar](50) NOT NULL, [CreateDate] [datetime] NOT NULL,  CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED ( […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -