Azure SQL Databases 101: Moving databases in and out of Elastic Pools

In one of my previous posts about Azure SQL Databases, I wrote about the differences between Single Database and Elastic Pool deployment options. In that post, I mentioned that it is possible to move databases between different Elastic Pools. The one thing I overlooked to say is, that you can also move Azure SQL Databases in and out from the Elastic Pools.

There is two principal methods for this task. You can do it through Azure Portal, or you can use the command line option.

Database in a wheelbarrow, with some clouds in the background — ^{Moving databases}

Personally, I love the combination of AZ CLI and Windows Terminal, but the Portal is also very handy for some small tasks. If you prefer graphical UI, then I’d recommend bookmarking https://cmd.ms/, for blade shortcuts.

Moving Azure SQL Databases in Azure Portal

Let’s start with the first option, moving the database using Azure Portal. To show how moving the database is done, I am creating a small Azure SQL Database to the Elastic Pool that I’ve created.

^{A demo database inside the Elastic Pool}

As you can see from the screenshot, it’s deployed on a server called pooldemosql, and it’s inside an Elastic Pool called demopool-1. First, I will move it out of the pool. To accomplish this, you actually need to go to Elastic Pool settings in the Portal, not the database configuration.

Once on the Elastic Pool page, we’ll need to find the “Configure” button on the “Overview” page.

^{Finding the right button to click}

This opens up the configuration page for the Elastic Pool. Click the tab link called “Databases“. There we will select the database to remove and click “Remove from pool“.

^{Database selection for removing it from the pool}

Now the database is ready to be removed from the pool. Notice the Pricing tier here, it seems to default to S12 (which can get expensive rather fast). After adjusting the pricing tier, we hit “Save”, which will execute the change.

^{Almost ready, just hit save}

It takes a moment for the configuration change to happen. After it’s completed, the database is now outside the pool.

^{It’s now out of the pool}

One thing to be aware of is, that many of these operations (moving and scaling resources) will have an impact on the availability of the database. It’s not a long one, some seconds, but it can bubble up as an error to applications that don’t have adequate error handling.

Alright, now let’s put it back in the pool.

Moving Azure SQL Databases with AZ CLI

This time, we’ll be using my favorite tool combination. Windows Terminal and AZ CLI. Using Portal often times takes a quite a bit of clicking around the UI to get the task done, I find AZ CLI making things much simpler.

It actually simplifies it down to a single liner, in this case:

az sql db create --resource-group azuresqlpools-demo --server pooldemosql --name PoolDemo1DB --elastic-pool demopool-1

And as we can see, after we run it, the database is successfully moved back into the Elastic Pool. I know what you’re probably now thinking. Using the “create” command doesn’t feel very intuitive at first. But keep in mind, that many operations in Azure often require something new to be provisioned. Especially when moving or scaling resources.

^{It’s back!}

Summary

Azure SQL Databases can be moved in and out of Elastic Pools with relative ease, it’s also possible to move the databases between two different Elastic Pools. Personally, I prefer to use AZ CLI for the simplicity of it, but you can do it rather easily using the Portal too.

If you are keen to learn more about Elastic Pools, then I’d recommend that you visit the Microsoft Learn to read this article, or check my post I linked on top of this page.

Thanks for reading, and feel free to leave feedback if you find this post helpful!

SQLStarters