Press "Enter" to skip to content

Category: Administration

DTUs and vCores in Azure SQL Database

Published 2021-01-20 by Kevin Feasel

Arun Sirpal gives us one take on whether to use DTUs or vCores for Azure SQL Database:

If you are building database solutions in Azure , using Azure SQL Database then you will know that you have a purchasing option decision to make. That being should you use a vCore model or DTU approach?

Arun prefers the vCore model for solid reasons. I agree with the sentiment when we’re talking about production databases. For small-scale, personal stuff, however, the bottom end of the DTU model is much less expensive.

Comments closed

Auto-Pausing Dedicated SQL Pools in Azure Synapse Analytics

Published 2021-01-18 by Kevin Feasel

Fonseca Sergio automates an important cost-saving measure when working with Azure Synapse Analytics dedicated SQL pools:

As Synapse engineer or Synapse Support Engineer you may need to start and test some Pools, and you want this to be the most cost efficient possible. Leaving some Synapse with a lot of DWU left turned on during the weekend because you forget to pause the DW after you shutdown your computers is not a good approach and we can quickly resolve this by using Powershell + Automation accounts.

This is also a good introduction to Azure Automation if you aren’t familiar with it.

Comments closed

Collecting Wait Stats Over Time

Published 2021-01-11 by Kevin Feasel

Michael J. Swart has a repo for us:

I find wait stats so useful that I’ve got Paul Randal’s SQL Server Wait Statistics (or please tell me where it hurts…) bookmarked and I visit it frequently.

But that gives the total amount of waits for each wait type accumulated since the server was started. And that isn’t ideal when I’m troubleshooting trouble that started recently. No worries, Paul also has another fantastic post Capturing wait statistics for a period of time.

You can also get this from various monitoring tools, as Michael mentions, but if you don’t have such a tool in place, here’s how you can roll your own.

Comments closed

Running an mlflow Server on Azure

Published 2021-01-08 by Kevin Feasel

Paul Hernandez configures mlflow on Azure using platform-as-a-service offerings:

It is indisputable true that mlflow came to make life a lot easier not only for data scientists but also for data engineers, architects among others. There is a very helpful list of tutorials and example in the official mlflow docs. You can just download it, open a console and start using it locally on your computer. This is the fastest way to getting started. However, as soon as you progress and introduce mlflow in your team, or you want to use it extensively for yourself, some components should be deployed outside your laptop.

To exercise a deployment setup and since I own azure experience, I decided to provision a couple of resources in the cloud to deploy the model registry and store the data produced by the tracking server.

I concur on the power of mlflow.

Comments closed

Adjusting Database Settings with Powershell

Published 2021-01-05 by Kevin Feasel

Eric Cobb takes a look at some nice functionality in dbatools:

There may be times that you want to ensure certain settings are applied to a database, or multiple databases. For example, if you restore a Production database to a QA environment, you may need to change the Recovery Model. Or if you’re migrating databases to a new SQL Server version you want to make sure to update the Compatibility Level. With dbatools this is really, really easy. Here are some examples:

Read on for examples around setting the database owner, changing the compatibility level, and setting the recovery model.

Comments closed

Collecting Database Name with Extended Events

Published 2020-12-28 by Kevin Feasel

Aaron Bertrand has a philosophical dilemma:

Now, in order to capture this data to DDLEventLog, we have a background process running on a schedule that extracts all the new data from the latest .xel files and loads it into the table. (There’s also a process that moves files we know we’ve consumed and deletes them 90 days later to keep the loading job linear.)

This revealed an edge case where it is possible to lose one aspect of the data: database name. When originally building the session, I thought database_id would be enough because the loading process could always look up the name from there. But this is brittle. Imagine the case where a user drops a table, drops the database, then the loading process pulls data from the session. That data about dropping the table contains just a database_id that no longer exists.

Read on for Aaron’s thoughts and ultimate decision.

Comments closed

Database Snapshots in SQL Server

Published 2020-12-17 by Kevin Feasel

Jamie Wick walks us through SQL Server database snapshots:

A SQL Server database snapshot is a read-only view of what the data pages in the source database looked like, at the time that the snapshot was created. Typically, snapshots are used to provide a point-in-time view of the database (for reporting or auditing purposes) or to allow for quick reversions during database upgrades/modifications. Since the snapshot only contains information on which values have changed, and what they were originally, it’s usually faster to revert the snapshot than having to restore the entire database from backup.

Click through for info on how they work as well as how they perform. I have used database snapshots to great effect in the past when testing changes in development environments back before the days of containers.

Comments closed