Press "Enter" to skip to content

Category: Administration

Azure Data Factory Activity Queue Times

Published 2022-01-06 by Kevin Feasel

Meagan Longoria waits in line:

I’ve been working on a project to populate an Operational Data Store using Azure Data Factory (ADF). We have been seeking to tune our pipelines so we can import data every 15 minutes. After tuning the queries and adding useful indexes to target databases, we turned our attention to the ADF activity durations and queue times.

Data Factory places the pipeline activities into a queue, where they wait until they can be executed. If your queue time is long, it can mean that the Integration Runtime on which the activity is executing is waiting on resources (CPU, memory, networking, or otherwise), or that you need to increase the concurrent job limit.

Click through to see how you can calculate queue times across activities, pipelines, and data factories.

Comments closed

Replication Error 20084 on SQL Server 2019

Published 2022-01-05 by Kevin Feasel

I ran into a weird issue:

Iwas helping out with a SQL Server upgrade recently, going from 2016 to 2019. We ran into a problem when trying to run replmerg.exe for a merge replication subscription. Specifically, we were getting error code 20084, which means that the replication process couldn’t connect to one of the instances. Interestingly, the process couldn’t connect to the local instance, and the failure was immediate—that is, within a couple of milliseconds. There was nothing in the management logs on either the distributor server or the subscriber server which indicated a problem. We were able to connect both sides together just fine—from the subscriber, we could connect to the distributor, and from the distributor, we could connect to the subscriber.

Click through for what error code 20084 typically means, as well as what turned out to be the problem here.

Comments closed

Getting Started with KQL

Published 2022-01-04 by Kevin Feasel

Steve Jones starts learning about the Kusto Query Language:

I saw an episode of Data Exposed with my good friend, Hamish Watson. He talked about KQL (Kusto Query Language) being the next query language you need to learn. I was skeptical of the title, but I decided to give this a try.

In the episode, Hamish points out a cheat sheet from Microsoft, which I thought was a good resource. However, while watching the video, I browsed over to the demo site Microsoft has at https://aka.ms/lademo. You need an Azure account to log in, but this is a demo site where you can query some Log Analytics data. The new query window below is what appears when you go here:

If you’re already familiar with the way Splunk’s filtering language works, KQL follows from it. It’s a worthwhile language for Azure-based administrators to know, as it’s the most powerful way to get data out of Log Analytics.

Comments closed

Cleaning SQL Express Databases

Published 2021-12-31 by Kevin Feasel

Kevin Hill knows the pain:

I was contacted by a lawyer that was using a 3rd party application to store emails, keep track of time, etc.

The backend of the application is SQL Server Express edition, which has a hard limit of 10GB for the data file.

One quick note for people with lots of LOB data, remember to reorganize with LOB_COMPACTION = ON as that’s the only way to be sure. Also, depending on how old the version of SQL Server is, there was a bug with LOB compaction which affected SQL Server 2014 and earlier. But, uh, hopefully you’re patched past that point…

Also, getting up to 2016 SP1 means that Express Edition gets data compression. It wouldn’t directly help in this case, but if you have a lot of non-LOB data on Express Edition, it can work wonders, for some definition of “wonders.” After all, if you’re using Express Edition, wonders are by definition pretty small.

Comments closed

Reference to Database Not Supported in this Version of SQL Server

Published 2021-12-29 by Kevin Feasel

Diego Nieto-Arroyo troubleshoots an issue:

In this article I will show how to resolve and overcome errors while attempting to query a system tables or any table for that matter, via Linked Servers. The error seen below is the result of the issue we encountered.

Msg 40515, Level 16, State 2, Line 1

Reference to database and/or server name in ‘xxxx.sys.sp_tables_info_90_rowset_64’ is not supported in this version of SQL Server.

Read on to see what causes this issue and how you can resolve it.

Comments closed

Operation Requires Server to be a Registered Server

Published 2021-12-23 by Kevin Feasel

Garland MacNeill finds one way to solve a problem:

Anyway, it’s been a while since I worked on this AG and I need to get the migration/upgrade done. As I was working on configuring jobs, I ran into a problem where the AG node (as a target node) wasn’t downloading jobs from the Master node, in fact, the last poll was in July.

When I tried to force a poll, I was met with an error message that the server wasn’t registered, never mind it was clearly listed as a target server. Google didn’t find anything useful, other than some questions from 2013. I did come across the syntax to forcefully eject the server as a target with SQL. 

Read on to see how and what to do in the aftermath.

Comments closed

SERVERPROPERTY() and DATABASEPROPERTYEX() Views

Published 2021-12-20 by Kevin Feasel

Andy Mallon provides a public service:

The thing I hate the most about these two functions is that you need to know the right magic spells to make them work. Let’s look at SERVERPROPERTY() first. The syntax for the function is SERVERPROPERTY( 'propertyname' ), which is easy enough syntax, but the list of values for propertyname isn’t discoverable from SQL Server metadata, DMVs, or even IntelliSense. Instead, I need to check the docs for the list of allowable values. These property names are essentially magic words, and I need to check my spell book to make sure I get it right.

Invalid values for propertyname just return NULL–which is easy enough to handle, but also means your code will compile and run, but might do unintended things if you get your magic spell wrong, due to a typo.

Click through for Andy’s solution to the problem.

Comments closed

Reviewing Azure Options for PostgreSQL and MySQL

Published 2021-12-17 by Kevin Feasel

Maria Zakourdaev has a pair of info sheets. First up is Azure Database for MySQL:

MySQL is an open-source relational database that is widely used for web applications, it’s easy to use, reliable, secure, and fast.

Recently Microsoft have announced a new deployment option, Flexible Server, that is now generally available.

If we have a quick look at the available options, we now have Single Server and Flexible server deployment options.

Then we have Azure Database for PostgreSQL:

PostgreSQL is an open-sourced, feature rich and extendable relational database that handles high concurrency workloads easily. It supports complex structures, many advanced data types, Search Tree indexes and also got highly sophisticated query optimizer.

Azure Database for PostgreSQL is an Azure managed services running PostgreSQL community edition. With Flexible Server announced recently, you now have 3 deployment options: Single Server, Flexible Server and Hyperscale/Citus.

Click through for a quick comparison of each available option.

Comments closed

Log Replay for Azure SQL Managed Instance

Published 2021-12-17 by Kevin Feasel

Joey D’Antoni has some quick notes on the Log Replay Service:

Recently, I’ve started on a project where we are migrating a customer to Azure SQL Managed Instance, which now supports a few different migration paths. You can simply backup and restore from a database backup, but you can’t apply a log or differential backup to that database. You can also use the Database Migration Service, but that requires a lot of infrastructure and Azure configuration. The log replay service, or LRS, is the functional equivalent of log shipping to your new managed instance database. While log shipping is a well known methodology for both database migrations or disaster recovery. However, the implementation is a little different–let’s talk about how it works.

Click through to see how it differs.

Comments closed