Erik Svensen answers a question:
The last few month I have used CDS a few times in my solutions – and connected the data to PowerBI.
But one of the things I always search for is the Server URL
Read on for the answer.
Comments closedCurated SQL Posts
What i got into was the following. For a project we’re loading an Azure sql database (serverless) with a lot of data (think billions of rows) that has to come from an on-premises Oracle server. We’re using a vpn connection with network peering to connect to the on-premises server and using a VM with a third-party tool to load the data.
Normally we’re delta-loading the database but because it’s a new project we need to perform an initial load. Nothing really weird, just a huge number of records that needs to pass through. And every now and then the application freezes and refuses to thaw. Because it’s hard to find out when the freezing will start, we want to monitor some processes on the database.
Now on a normal SQL Server i’d create a job in the Agent and be done with that part. But not on Azure. Because the agent doesn’t exist there. In SSMS you’ll see a huge empty space where the agent ought to be.
Reitse lists five separate options. A sixth would be to spin up SQL Server in a VM and use its agent for scheduling. And there are a few more alternatives as well in the ‘outside scheduler’ realm.
Comments closedJamie Wick walks us through the key concepts with Accelerated Database Recovery:
Beginning with SQL Server 2019, Microsoft has redesigned the database recovery process (ie. crash recovery and rollback) to improve availability and performance. This new feature is called Accelerated Database Recovery (ADR).
Prior to SQL Server 2019, recovering a database (after a crash or restart) consisted of 3 phases that followed the ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) recovery model. The phases of this model are Analysis, Redo & Undo. The Analysis phase begins with the last successful database checkpoint and forward-scans the transaction log to determine the state of each transaction. The Redo phase begins with the oldest uncommitted transaction (that was active after the checkpoint) and rolls-forward, bringing the database state to the same point it was at immediately prior to the crash. The Undo phase then goes backward from the end of the transaction log to reverse all transactions that were active (uncommitted) at the time of the crash. With this process, the database recovery time is roughly the same as the longest running query that was active at the time of the crash.
Click through for the full story.
Comments closedSteve Jones shows off hash tables in Powershell:
I was watching the GroupBy talk the other day and noticed that Cláudio Silva was using arrays, or what appeared to be arrays, in his talk. That was an interesting technique, one that I haven’t used very much.
Click through for the full story.
Comments closedErik Darling hits on an interesting issue:
You’ll see the exchange event, and you’ll also see the same query deadlocking itself.
This is an admittedly odd situation, but one I’ve had to troubleshoot a bunch of times.
In other words, parallel threads on the same query causing the query to deadlock on itself. Click through to learn what you can do about it.
Comments closedBrent Ozar explains why full-text search in SQL Server can be so slow:
SQL Server’s full text search is amazing. Well, it amazes me at least – it has so many cool capabilities: looking for prefixes, words near each other, different verb tenses, and even thesaurus searches. However, that’s not how I see most people using it: I’ve seen so many shops using it for matching specific strings, thinking it’s going to be faster than LIKE ‘%mysearch%’. That works at small scale, but as your data grows, you run into a query plan performance problem.
When your query uses CONTAINS, SQL Server has a nasty habit of doing a full text search across all of the rows in the table rather than using the rest of your WHERE clause to reduce the result set first.
Read on for the full impact as well as some alternatives. I agree that those alternatives come with costs (whether that be monetary or conceptual), but I’ve used both n-grams and Elasticsearch with some success.
Comments closedBuilding on the work done and detailed in my previous blog post (Best Practices for Implementing Azure Data Factory) I was tasked by my delightful boss to turn this content into a simple check list of what/why that others could use…. I slightly reluctantly did so. However, I wanted to do something better than simply transcribe the previous blog post into a check list. I therefore decided to breakout the Shell of Power and attempt to automate said check list.
Sure, a check list could be picked up and used by anyone – with answers manually provided by the person doing the inspection of a given ADF resource. But what if there was a way to have the results given to you a plate and inferring things that aren’t always easy to spot via the Data Factory UI.
Paul uses an ARM template rather than hitting your Data Factory directly, so there’s a little bit more work for you the user, but Paul explains why it’s both necessary and proper.
Comments closedChris Webb shows off a method for handling multi-select using dynamic M parameters:
Even though the documentation for dynamic M parameters does mention how to handle multi-select in the M code for your Power Query queries, I thought it would be useful to provide a detailed example of how to do this and explain what happens behind the scenes when you use multi-select.
Click through for that explanation and example.
Comments closedWolfgang Strasser shows us how to integrate Azure Synapse Analytics and Power BI:
Sometimes however, would not it be nice to access the data lake in Direct Query mode – to get the most up to date information for every report view? I would say: yes … but how can you achieve this? The options natively provided by ADLS Gen2 and Power BI are not sufficient to solve this requirement. But: there are options to achieve this and, in this post, I would like to show you the possibilities using Azure Synapse Analytics to build a query layer on top of a ADLS Gen2 storage account.
Click through for a step-by-step walkthrough.
Comments closedPhil Factor has some helper functions for us when working with JSON data:
If you know the structure and contents of a JSON document, then it is possible to turn this into one or more relational tables, but even then I dare to you claim that it is easy to tap in a good OpenJSON SELECT statement to do it. If you don’t know what’s in that JSON file, then you’re faced with sweating over a text editor trying to work it all out. You long to just get the contents into a relational table and take it on from there. Even then, You’ve got several struggles before that table appears in the result pane. You must get the path to the tabular data correct, you have to work out the SQL Datatypes, and you need to list the full panoply of keys. Let’s face it: it is a chore. Hopefully, all that is in the past with these helper functions.
Click through for those functions.
Comments closed