Press "Enter" to skip to content

Category: Cloud

MAXDOP by Username in Azure SQL DB

Published 2023-07-31 by Kevin Feasel

Jose Manuel Jurado Diaz comes up with a solution:

Azure SQL Database is a powerful platform that provides managed database services with built-in intelligence and robust resource management. While Azure SQL Database doesn’t have a direct implementation of the traditional Resource Governor feature available in SQL Server, we can explore a pseudo-Resource Governor approach using user-defined functions and custom tables. In this article, we’ll discuss the concept, present a sample implementation using a custom function, and highlight the possibilities it opens up for controlling CPU resources in Azure SQL Database.

Click through for the UDF and how to use it. My first inclination was to say that I couldn’t see it working well at all under load, though on second thought, performance won’t be bad like having a UDF execute for each row in a table, so it’s probably more of a manageable overhead.

Comments closed

Full-Text Search in Cosmos DB via Cognitive Services

Published 2023-07-28 by Kevin Feasel

Hasan Savran performs a search:

Incorporating Full-Text Search functionality into your application can enable users to locate what they are searching for effortlessly. Searching for specific words or phrases within a database has always been a difficulty, particularly for relational databases. Throughout my career, I’ve had countless discussions/arguments with DBAs about the importance of implementing full-text search in a relational database. We are in totally different times, now users want to search by voice, image, or video.

     Full-Text Search functionality is not part of Azure Cosmos DB’s Database Engine. Firstly, we must establish the Azure Cognitive Search service and link the data from Azure Cosmos DB to the Search Service. The process of setting up Azure Cognitive Search is relatively straightforward. Like other Azure services, you will need to answer similar types of questions beforehand. (Subscription, Resource Group, a name for the service, region, and tier)

By the way, Azure Cognitive Search is very similar to Elasticsearch, for those of you familiar with that technology.

Comments closed

Azure SQL MI and the WAF: Performance Pillar

Published 2023-07-28 by Kevin Feasel

Niko Neugebauer looks at one of the pillars of the Well-Architected Framework with respect to Azure SQL Managed Instance:

A baseline is a known value against which later measurements and performance can be compared. Baseline helps us define what is a normal database performance and thus comparing against the baseline provides us with insights into any abnormalities. Ideally, one should take performance measurements at regular intervals over time, even when no problems occur, to establish a server performance baseline. Compare each new set of measurements with those taken earlier.

Click through for additional guidance and recommendations.

Comments closed

SQL Agent History on Azure SQL Managed Instances

Published 2023-07-26 by Kevin Feasel

Kenneth Fisher goes back in time:

The defaults for saving SQL Agent Job history are ok (at best), so you should probably check and update them if needed. Sadly, if you are using a Managed Instance this isn’t an option.

SQL Managed Instance currently doesn’t allow you to change any SQL Agent properties because they are stored in the underlying registry values.

That’s a real kick in the pants. Still, Kenneth shows us (via Jovan Popovic) a workaround to store the job history someplace else.

Comments closed

Connecting a SQL Server Instance to Azure Arc

Published 2023-07-21 by Kevin Feasel

Deepthi Goguri has a guide:

When you install SQL Server 2022 through the GUI, you will see an option in the features “SQL Server Extention for Azure”

This is more of a “how” than a “why.” Azure Arc-enabled SQL Server instances let you use Azure’s control plane (their graphs and some configuration options) to manage SQL Server instances, regardless of whether they’re actually in Azure or on-premises. That way, a DBA with one foot in both camps can have a consistent administrative experience for things like inventorying SQL Server instances.

Comments closed

Data Syncs between Azure SQL DB and Amazon RDS

Published 2023-07-19 by Kevin Feasel

Joey D’Antoni crosses clouds:

A while back, a client, who host user-facing databases in Azure SQL Database, had a novel problem. One of their customers, had all of their infrastructure in AWS, and wanted to be able to access my client’s data in an RDS instance. There aren’t many options for doing this–replication doesn’t work with Azure SQL Database as a publisher because there’s no SQL Agent. Managed Instance would have been messy from a network perspective, as well as cost prohibitive compared to Azure SQL DB serverless. Even using an ETL tool like Azure Data Factory would have worked, but would have required a rather large amount of dev cycles to check for changed data. Enter Azure Data Sync.

Read on to see what Azure Data Sync is and how it helps solve this problem.

Comments closed

Migrating Column-Level Encryption to Azure SQL MI

Published 2023-07-19 by Kevin Feasel

Keshav Kiran performs a migration:

One of our customers came up with a requirement where they wanted to Migrate On-prem Database to Azure SQL Managed instance. The databases had traditional column level encryption enabled.

He has restored the database on the SQL Managed instance by Backup/Restore approach. Now when he was trying to read the encrypted column on the destination database, It was showing NULL values after decryption.

Read on for the solution.

Comments closed

Automating Database Copy in Azure SQL Managed Instance

Published 2023-07-18 by Kevin Feasel

Sasa Popovic creates some clones:

Database copy and database move operations for Azure SQL Managed Instance are very convenient in various situations when you want to copy or move database from one managed instance to another in an online way. What does online mean in this context? It means that the database on destination managed instance will be identical to the source database at the moment when operation is explicitly completed by user action. Copying a database is a size of data operation, and you can expect copy will take some time, but what is important and convenient, unlike point in-time restore where database is in state from some point in time in the past, with database copy you get database in state as it was when the operation was completed.

Read on to see how you can set this up for an Azure SQL Managed Instance.

Comments closed

Index Maintenance in Azure SQL DB

Published 2023-07-18 by Kevin Feasel

Kendra Little gives an answer:

Have you ever received advice that was technically correct, but which was delivered in such a way that it was too hard to understand?

I think of this as “accidental bad advice,” because it leads to confusion. There’s a LOT of accidental bad advice out there on index maintenance for SQL Server and cloud versions like Azure SQL, even in the official documentation.

In this post I’m answering a common index maintenance question, and we’re going to keep it simple.

The answer is essentially the same as it would be on-premises: yes, but perform index maintenance when it is appropriate. Read on to learn what that means in this case.

Comments closed

Using the Azure Data Factory Self-Hosted Integration Runtime

Published 2023-07-14 by Kevin Feasel

Chen Hirsh hosts a runtime:

In Azure data factory (ADF), An integration runtime is a compute resource to run your pipelines on. When you run an application on your computer, it uses the computer resources, such as CPU and memory, to run its tasks. When you run activities in a pipeline in ADF, they also need resources to do their job, like copying data or writing a file, and these are provided by the integration runtime.

When you create an instance of ADF, you get a default integration runtime, hosted in the same region that you created ADF in. If you need, you can add your own integration runtimes, either on Azure, or you can download and install a self-hosted integration runtime (SHIR) on your own server.

Read on to understand when you would want to use a self-hosted integration runtime and the process to do so. This SHIR also applies to Synapse pipelines and is one of the few ways to move data out of a Synapse workspace with data exfiltration protection enabled.

Comments closed