Press "Enter" to skip to content

Curated SQL Posts

Starting a Synapse Proof of Concept

Published 2021-11-03 by Kevin Feasel

Hope Foley shares a secret with us:

I love my job!  One of the things I do for a living is to help customers get started with new services in Azure to finagle their data.  Many times we’ll start with a small POC to just start to understand the parts and pieces, and I teach them along the way.  I work with a lot of customers so being quick and nimble helps.  Lately I’ve been using PowerShell to setup the pieces needed for a full Synapse Analytics environment, including an example set of 4 pipelines (2 to extract to ADLS, 2 to upload to dedicated SQL pool).  Pulling data out of large relational databases into the data lake became a request I heard over and over so I automated it.  I’ve added and tweaked this over the years into a project I called “Synapse Load” and put a version out in my github. 

Click through to see what this includes and how you can use it.

Comments closed

Backing Up a Power BI Premium Database

Published 2021-11-03 by Kevin Feasel

Gilbert Quevauvilliers wants you to back that thing up:

Continuing with my series of using Power BI Premium Per User (PPU), today I am going to show you how to back up your PPU database.

As far as I am aware all the options below will work for Power BI Premium as well.

To me this is critical when my dataset size grows. Especially when it takes multiple days to process all the data into the required partitions.

Not only is having a backup best practice, if something must go wrong with a deployment (let’s say I wipe out the partitions by mistake) it will be quick and easy to restore from a backup.

Read the whole thing.

Comments closed

Azure Synapse Analytics Announcements

Published 2021-11-03 by Kevin Feasel

Kaiser Larsen has some Azure Synapse Analytics announcements for us:

As businesses worldwide navigate a new normal, data teams find themselves pressured to deliver transformative insights quicker than ever. Customer interactions are increasingly digital and multi-channel, supply chains are constantly adapting to changing demand, and operations are being reconfigured to accommodate remote and hybrid work. Business agility has never been more critical. And data teams are being asked to create new solutions, accelerate project deployments, and deliver real-time insights to power that agility.

For Ignite 2021, we’ve focused on delivering new features that enable data teams to deliver insights to the business faster than ever. Here is the summary of the latest innovations on Azure Synapse.

Read on to see some of what they’ve just dropped in.

Comments closed

SQL Server 2022 Preview Announced

Published 2021-11-03 by Kevin Feasel

Peter Carlin ends our long national nightmare:

SQL Server 2022 integrates with Azure Synapse Link and Azure Purview to enable customers to drive deeper insights, predictions, and governance from their data at scale. Cloud integration is enhanced with disaster recovery (DR) to Azure SQL Managed Instance, along with no-ETL (extract, transform, and load) connections to cloud analytics, which allow database administrators to manage their data estates with greater flexibility and minimal impact to the end-user. Performance and scalability are automatically enhanced via built-in query intelligence. There is choice and flexibility across languages and platforms, including Linux, Windows, and Kubernetes.

Click through for a quick overview of what’s making its way into the product.

2 Comments

SQL Assessment for SQL Server on VMs

Published 2021-11-03 by Kevin Feasel

Ebru Ersan announces a new preview:

Wouldn’t it be great if there was a way to learn if your SQL Server on Azure Virtual Machines was configured optimally? Do you have the right options set? Do you have your tempdb on the right disk? Can your queries perform better? All these and more can be answered using the new Azure portal experience on the SQL virtual machine resource page. SQL Assessment feature, once enabled, will evaluate your SQL Server on Azure VM against configuration best practices to determine if your system is healthy and setup for success. This feature is currently in preview. We would love to hear your feedback.

Click through to see it in action.

Comments closed

Event-Driven Programming

Published 2021-11-02 by Kevin Feasel

Gigi Sayfan explains the idea of event-driven programming:

Event-driven programming is a great approach for building complex systems. It embodies the divide-and-conquer principle while allowing you to continue using other approaches like synchronous calls.

When discussing event-based systems, several different terms often refer to the same concept. For simplicity, we’ll primarily use the terms listed below in bold:

– Event, message, and notification

– Producer, publisher, sender, and event source

– Consumer, receiver, subscriber, handler, and event sink

– Message queue and event queue

I really like the ideas behind event-driven programming. It’s most commonly useful when working with cloud services, but also in solving some difficult T-SQL problems.

Comments closed

GROUP BY and Functional Dependencies

Published 2021-11-02 by Kevin Feasel

Lukas Eder illuminates us:

The SQL standard knows an interesting feature where you can project any functional dependencies of a primary (or unique) key that is listed in the GROUP BY clause without having to add that functional dependency to the GROUP BY clause explicitly.

I was unaware that this functionality existed (in some database platforms), and I’m not positive that I like it.

Comments closed

BULK_OPERATION Locks during a NOLOCK Scan

Published 2021-11-02 by Kevin Feasel

Paul Randal does some explaining:

I had an email question over the weekend where someone noticed that while executing a scan of a heap using NOLOCK, there was a BULK_OPERATION lock held on the heap for the duration of the scan. The question was why is the BULK_OPERATION lock needed, as surely there’s no way for the NOLOCK scan to read a problematic page?

Well, the answer is that the extra lock is needed *precisely* because the NOLOCK scan *can* read a problematic page if there’s a bulk operation happening on the heap at the same time.

And don’t call me Shirley.

Click through for a demonstration of this answer.

Comments closed

Restrictions for Parallel Insertion

Published 2021-11-02 by Kevin Feasel

Erik Darling summarizes the main man:

I’d like to start this post off by thanking my co-blogger Joe Obbish for being lazy and not blogging about this when he first ran into it three years ago.

Now that we’re through with pleasantries, let’s talk turkey.

Over in this post, by Arvind Shyamsundar, which I’m sure Microsoft doesn’t consider official documentation since it lacks a GUID in the URL, there’s a list of… things about parallel inserts.

Read on for the summary of limitations.

Comments closed

Secure FTP in Powershell

Published 2021-11-02 by Kevin Feasel

Chad Baldwin needs to send a file:

Over the years I’ve written dozens of these, one thing that often hangs me up are the “copy to SFTP” and “copy from SFTP” steps. usually what happens is I build two scripts…an “export script”, which has a manual step of “open FileZilla and upload file”, and then an Import script with another manual step to download the file.

After some Google searching to see how to handle SFTP in PowerShell, I ran into this StackOverflow answer (the creator of WinSCP) which introduced me to some cool new alternatives for dealing with FTP, FTPS, SFTP, SCP, etc using PowerShell.

Click through to see one way to do it, as well as some additional recommendations from Viewers Like You.

Comments closed