Cloning Azure SQL Database Servers

Question

In Azure SQL Database, is there a way to clone a full SQL Server (Databases, logins firewall rules etc.)? I know you can copy one db at a time and create a new server, but then you have to recreate firewall rules, logins, etc so I want to know if I can just clone the whole thing - thanks!

Answer 1

Geo-replicating the server may be an option for you as a way to clone the Azure SQL logical server. Please read here.

About firewall rules and logins/credentials when choosing Geo-replication, please read here

Answer 2

@Andy Galbraith In addition to Alberto's answer you can use BACPAC file and apply it to Azure SQL - You can script everything out and apply it to Azure SQL. there is no such feature that clone the all SQL Server to Azure SQL.
Hope that helps
Regards,
Oury

Answer 3

Good day @Andy Galbraith

clone Azure SQL Server

I think that there is a confusion here. You asked about "clone a full SQL Server" and you got information related to clone an Azure SQL Database.

Assuming you meant to clone the "server", then you should understand that there is no physical server to clone and what you have a virtual server = an application which gives you the looks & feel like an on-premises SQL Server.

The fact is that this virtual server service does not cost and it used as a wrapper for databases services. You can have databases which are physically in different machines and you can have different types of databases and not only Azure Database.

For example you can have an Azure SQL Database which is based on SQL Server, but you can also have dedicated SQL pool (formerly SQL DW) which is totally different product based on Parallel Data Warehouse. Notice that when you navigate to your Azure SQL Server you have option "create database" and option to create "New dedicated SQL pool (formerly SQL DW)"

As I said this is a wrapper for databases services (database level service) which mean that each database is a separate service.

You can clone the Virtual Server manually by creating new virtual server and then clone the databases from the old server to the new one, but you work on a database level. I mean, in theory you can create a script that do all the work including creating the new Virtual Server and cloning the databases, but the fact is that you to still work on each database behind the scenes. As much as I know there is build-in tool to clone Azure SQL Server, and as I tried to explain it make no sense probably since you choose to get databases level service and NOT a Server level service.

All the links which you got till now are ways to clone the database and not the server, and in most cases I would suggest you NOT to use these options.

clone Azure SQL Database(s)

To clone Azure SQL Database you should probably use the copy option! You can do it in the Azure portal, using PowerShell or the Azure CLI, but my recommendation is to use simple query.

CREATE DATABASE New_DB AS COPY OF Old_DB;  

You can create the copy of the database in the same server or in different server or even different subscription.
https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-using-transact-sql

Note: BACPAC is a nice way to copy data and schema but NOT to create an exact copy of a the database! BACPAK is very useful when you need to copy the data and the schema between two different environments or from newer version to older version (for example from Azure SQL Database to On-premises SQL Server or vice versa) but it probably make not sense to use it when you need a copy of the database on the same environment (for example from Azure SQL Database to Azure SQL Database you should probably use "CREATE AS COPY" and from On-Premises to On-premises on same versions you should probably use BACKUP and RESTORE). Moreover, MOST IMPORTANT!!! BACKPAC is NOT transactional consistency! The tool that create the BACPAC file checks the schema of the database, and works on entity level, one by one. This means that the data might not be consistent across tables. For example, if transactions executed during the time that the tool creates the file, we might get errors when we will try to use the file.

Note! To understand more about BACPAC I highly recommend to search one of the recording of my lecture about the topic. I spoke about it in a few events around the world in Hebrew and in English, so just find the recording in the language that you prefer. Search google for: Ronen Ariely The Internals of the External Sqlpackage Tool