Press "Enter" to skip to content

Curated SQL Posts

Loading Azure Synapse Analytics using PolyBase

Published 2021-03-19 by Kevin Feasel

Gauri Mahajan needs to load some data:

Azure Synapse Analytics is Microsoft’s data warehousing offering on Azure Cloud. It supports three types of runtimes – SQL Serverless Pool, SQL Dedicated Pool, and Spark Pools. As there are a variety of data sources on Azure, it’s very obvious that there can be varying types and volumes of data that would have to be loaded into Azure Synapse pools. There are three major types of data ingestion approaches that can be used to load data into Synapse. The COPY command is the most flexible and elaborate mechanism, where someone can execute this command from a SQL pool to load data from supported data repositories. This command is convenient to load ad-hoc and small to medium-sized data loads into Synapse. The second method of loading data is the Bulk Insert, where the method name is self-relevant regarding the approach functionality. To ingest the data from supported repositories into dedicated SQL pools, PolyBase is as efficient and at times it’s even more efficient than the COPY command. This article will help you understand the process to ingest data into Azure Synapse Analytics using PolyBase to load the data.

Click through for the process.

Comments closed

Calculating Pagination Metadata in SQL

Published 2021-03-19 by Kevin Feasel

Lukas Eder has a single query which includes pagination data:

This is rather straightforward. It will give us page 2 out of N pages, with a page size of 10. But how do we calculate these values? How do we know we’re on page 2? How do we know the number of pages N? Can we calculate this without an extra round-trip e.g. to calculate the total number of actors:

-- Yuck, a second round-trip!
SELECT COUNT(*)
FROM actor

We can do it with a single SQL query and window functions, but before I explain how to do this, please consider reading this article on why OFFSET pagination is a bad thing for your performance

If you’re still convinced OFFSET pagination is what you need, as opposed to keyset pagination, let’s look at how to calculate the above meta data with SQL.

Click through for the query, as well as Lukas’s explanation of how it works. But also heed that warning about keyset pagination, as it’s usually a lot better.

Comments closed

Logging Powershell Script Details

Published 2021-03-19 by Kevin Feasel

Patrick Gruenauer has logging boilerplate code for us:

So you have already created your first PowrShell scripts? Now you want to enhance this scripts with error logging ? If your answer is yes, jump in this this article. I will show you how to implement a custom function that captures the errors and writes errors in an error log file. Let’s get started.

Click through for the code, as well as an explanation of each bit.

Comments closed

Continuous Backup with Cosmos DB

Published 2021-03-19 by Kevin Feasel

Hasan Savran reviews a new bit of functionality in Cosmos DB:

     Azure Cosmos DB announced Continuous Backup in Cosmos DB on March 2021. This feature is currently in public preview mode and It is not recommended to use in production. This option gives you more options for your backup requirements. You might be using Azure Data Factory to handle your custom backup needs. Azure Data Factory is the SSIS in cloud. ETL jobs can be problematic. Backing up a database is half of the problem; other half is restoring a database. Until now, we had to call Microsoft to restore Cosmos DB databases/accounts.
    By using Continuous backup, you can easily backup and restore your database. For now, this option is available only for SQL API and Mongo API. There are many limitations in this public preview version. I am sure many of these limitations will go away when it becomes generally available to everybody.

Click through for more details about the offering, as well as how to enable it. We’ll have to wait until it’s out of public preview to see how much it will cost, but it does look interesting.

Comments closed

Viewing SQL Server Logs on Linux

Published 2021-03-19 by Kevin Feasel

Jack Vamvas answers a question:

Question: How can I view SQL Server Logging on Linux ?

Answer: SQL Server on  Windows – logs details into SQL Server Error logs and Application Logs. Windows event viewer details are available in the Windows Event Logs via the event viewer or Powershell Get-EventLog

The Windows answer is pretty easy for SQL Server DBAs, as we’ve lived in it for so long. Click through for the Linux answer.

Comments closed

Load Testing using SqlQueryStress

Published 2021-03-19 by Kevin Feasel

Chad Callihan walks us through the SqlQueryStress program:

Do you have a new SQL server that you need to load test against? What about a new stored procedure that needs tested with various parameters? Maybe you’re just trying to punish your CPU? Whatever the reason, my favorite tool for these scenarios is Adam Machanic’s SqlQueryStress. Before we run through some examples, check out SqlQueryStress on GitHub or get SqlQueryStress from the Microsoft Store.

It’s a pretty simple program which I’ve used for well over a decade. Chad does a good job of walking us through the tool.

Comments closed

The DBA Role Over Time

Published 2021-03-18 by Kevin Feasel

Brent Ozar has started a retrospective. The first post covers “What would you say you do here?”:

When the database goes down, the business stops. If you’re selling things online, and your web site goes down, that’s an emergency. Even if it’s not down – if it just slows down – that can be an emergency too. DBAs help prevent that problem.

When someone accidentally deletes data, the business can stop. Unfortunately, in all too many companies, there are too many folks with access to that data – and those folks are often untrained. They’re just human, and they make mistakes. Database administrators help recover from that problem.

The second post looks at the lack of progress in many areas:

When I started working with databases, we had to:

– Provision the right CPUs and memory
– Provision the right storage throughput and size
– Install the database
– Configure the database
– Protect it – which sounds easy, but that also includes designing the right high availability and disaster recovery solution
– Design the right tables, and put indexes on them based on how we wanted to query the data
– Write fast, accurate queries to load & retrieve data
– Troubleshoot the whole thing when it went wrong

Today, decades later…yeah.

We’re still doing all of that stuff. It’s just that we’re cave-people using rocks for tools instead of banging on things by hand.

I’ve got some thoughts on this but they won’t fit on this stamp. I’ll have to put them together some other day.

Comments closed

Checking on Transactional Replication Status

Published 2021-03-18 by Kevin Feasel

Steve Stedman wants to see how far behind transactional replication is:

Working with Transactional Replication with many of our SQL Server clients, one of the big questions that we are always asking is replication keeping up? Recently when deleting a few hundred million rows from a large table that is synced with transactional replication, I needed to programmatically check how the distribution was keeping up. The one measure that indicated that things were behind was the subscriber undistributed commands tab in the replication monitor. When the number of commands waiting to be applied was too large, I had to slow down my delete process.

Click through for an easy script. The really frustrating part is that transactional replication is easy but answering the same question with merge replication is a royal pain.

Comments closed

Building VBA Macros for Excel in Powershell

Published 2021-03-18 by Kevin Feasel

Mikey Bronowski has a case of developer inception:

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

In this last post of the series, I am going to mix Excel, PowerShell and VBA. If you weren’t using PowerShell to manage Excel files before, you might have used VBA (Visual Basic for Applications) to do so. Excel is a powerful tool and even this area can be improved a little bit with PowerShell.

It’s a shame to see this series wrap up, but Mikey has provided a huge amount of content around automating Excel spreadsheet creation. If you haven’t been reading these, I definitely recommend it.

Comments closed

Tracking Azure Resources with Tags

Published 2021-03-18 by Kevin Feasel

Jess Pomfret explains the value of tags:

One of the vital parts of this learning and experimenting needs to be cleaning up after myself.  We all know the risks of leaving things running in Azure- it’s likely to drain your training budget pretty quickly.  To be fair, this is also a good lesson for real world scenarios. Getting used to turning off or scaling down resources based on need is a good way to reduce your Azure spend.

This brings me to one morning last week. I logged in to the portal and got a pop up that my credit was down to under $5, which is not what I was expecting. I started looking around and wondering what I’d left running – it isn’t always easy to spot though.

Read on to see how tags can help with this, as well as other forms of cloud governance. If you remember to set them, that is.

Comments closed