SQLStarters

I wrote previously about moving databases in and out of Elastic Pools, one of the common DBA tasks, when working with Azure SQL Databases. To continue with a similar topic, I decided to write a short post about moving Azure SQL Databases between different logical servers. While this isn’t any more difficult than moving databases between Elastic Pools, there are some differences that you want to be aware of.

Why move Azure SQL Databases between servers?

The logical server for Azure SQL Databases is, at least from my perspective, mostly an administrative endpoint and not a real server. The reasons for moving databases between servers then tend to have less to do with resource management, and much more about managing access to resources and capabilities.

An excellent example of this is the management of the network traffic. While it is possible to use a firewall at the level of the database, I don’t see this practiced often in a real life. Most of the organizations I work with use firewall at the level of the logical server. Other good examples of this include auditing rules and threat detection policies.

Moving Azure SQL Databases between servers versus Elastic Pools

Compared to moving Azure SQL Databases within the same server, the major difference when moving between servers is, that you’re not directly moving but copying the database. When you copy a database, that’ll result in having the same database on two different servers for a period of time.

While copying a database between servers itself is easy, it can be trickier to manage. For example, it’s easy to end up in a situation where both of your databases are online for the write traffic at the same time. This has happened to a friend of mine.

Copying the Azure SQL Database from Azure Portal

I was going to say that this is the simplest way of doing it, but at least for me the simplest way is to use T-SQL. It’s still pretty straightforward in Azure Portal too, you need to go to the database, and click on the “Copy” button.

After that, you’ll need to pick a logical server and give the name for the target database. By default, it’ll be “yourdatabasename_copy“. Click “Review + Create“, and when satisfied click “Create“. Then there’s nothing to do but wait, as the deployment goes through validation and (hopefully) through execution.

And that’s it, the database is now copied successfully. Now let’s look at my favorite method next.

Copying Azure SQL Database to a new server with T-SQL

This is absolutely the simplest way to perform this task. All you need is a single liner:

CREATE DATABASE TestDB1 AS COPY OF sourceserver.TestDB1;

And the result is (hopefully) this:

Additionally, you can use the “CREATE DATABASE AS COPY” command to create a copy of the database on the same server too. Copying Azure SQL Databases can also be done with Powershell and AZ Cli, naturally. However, I don’t use those as often when the alternatives are this easy.

Considerations when moving Azure SQL Databases between logical servers

Copying an Azure SQL Database to another server will force the owner change. The new owner of the database will be whatever security principal you used to copy the database. The other thing that can (and frequently will, by my experience) catch you surprised is, that unless you’re using contained database users, you might end up breaking database access. Copy will not automatically sync logins from the master database of the source, to the master database of the target server.

As long as it exists inside the database it’ll be copied over. This also includes firewall rules set at the database level. The server level firewall rules will naturally not be copied to a new server. As the server level is the one where I usually see these set, it’s a good idea to test the connectivity before doing the copy.

You will also need to reconfigure anything that might have been connecting to the previous server. This includes monitoring software, integrations, applications, etc.

Summary

Copying Azure SQL Databases between logical servers is one of those tasks that DBAs end up doing for various reasons. Very often these reasons are related to operational or data isolation requirements. While moving databases is easy, as we just witnessed, there are few issues that you can easily run into. Hopefully, this post helps you avoid them.