In this article, we will see different methods to copy the Azure SQL database. With the advent of all the business
dynamics available in today’s IT industry, there is a huge paradigm shift towards automation. You need to constantly
innovate and keep moving forward.
DBAs most commonly performed activity is determined to be the database cloning—a copy of a database from higher to
lower environment or one database server to another database server or in the same instance with a different name.
In this article, we will discuss the following:
- Discuss the database copy technology
- Internals of the database copy operation
- How to monitor the copy progress
- Different methods to copy the database
- And more…
The following database copy methods are available in Azure to copy the Azure SQL database from one server to other
or to the existing Azure SQL instances:
- Azure portal
- Azure PowerShell
- Azure CLI
In this section, we will see the internals of the Azure Database copy operation.
To copy the database in Azure uses geo-replication technology. Once replica seeding is complete, the
geo-replication link is automatically terminated
The geo-replication technology asynchronously replicates the committed transactions from the source database.
The pre-requisites of using geo-replication is also applied to the database copy process
- A database copy is a process that refers to a transactional consistent snapshot of the source database
The transactional consistent state defines the database that includes only the committed transaction stream
after you initiate the database copy program
- Note: By design, the Azure SQL Database by default provides two secondary copies of the database in the same data
center. These secondary nodes are in sync with the primary copy of the database. All the read/write operations are
performed on the primary copy. Additionally, the writes are replicated to the secondary copies
How to estimate the time it takes for the copy operation
Internally, the database copy time can significantly vary, as the process has to ensure ALL replicas have an exact
copy of the new database. There may be a chance that the secondary nodes are busy at the time of data copy and it
might take a little longer to sync the node.
However, it is recommended to run the process during the different workloads and estimate the total time required to
complete the database copy operation.
It is better to run the database copy operation couple of times to confirm and estimate the copy process duration.
In case, if you need a faster response, you may need to trim the source data in order to meet the outage window.
Different methods to Copy the Azure SQL database
In some cases, the source and the destination databases would be in different Azure subscription. This article
explores different ways to achieve this.
You can follow the steps in this section to create a database copy using the Azure Portal.
- Log in to Azure Portal and click Browse Azure SQL Server
Select Azure SQL Database and locate the database for which you would like to create a clone
Now, you need to enter the target database details
- Type in the database name
- Target server
- Choose “Yes” if you want to host it in the elastic database pool. If not, leave the default value “No”
- Decide the Compute and Storage
- Click Create
The copy target can be of the same server or different server. In addition, you can also choose the same or
different service tier (Service Objects); the same or different compute size (Computer or storage); and the database
can take part in the elastic pool or not
After the database copy process complete, the target database becomes available
The database copy process does not copy the logins, users, and permissions
- Next, Accept the Terms and Conditions
Review the Summary and click the Create button to start the database copy operation
How to monitor the progress of the database copy operation
In this section, we will discuss some of the internal DMVs used to monitor the database copy process.
- Query the sys.databases and check the state_desc column. During the copy process, the database is set to COPYING status
Using sys.dm_database_copiesselect * from sys.dm_database_copies
Note: The database copy process using geo-replication technology. As you see in the above image, the
replication_state_desc column is SEEDING, after the completion of data streams to the target, the geo-replication
link terminated automatically. In this case, you can see that is_interlink_connected is “1” because the streaming is
still in progress
Running sys.dm_operation_status views. You can see the Operation and
state_desc columns to understand the percentage of completionselect * from sys.dm_operation_status
Azure Database Copy using PowerShell
To copy an Azure SQL database, run the following PowerShell commands.
- Note: In this section, I will be showing how to use the Azure PowerShell module. It is recommended and intended
module to use and manage Azure services. Azure PowerShell bundles with extensive and rich support of short cmdlets,
and provides cross-platform support
For the demo, I am using CloudShell.
- Start Cloud Shell
- Select the following code block and copy the code
Paste the code into the PowerShell session$ResourceGroupName=“MyResourceGroup”$sourceSQLServer=“myazureDemo12”$SourceDatabaseName=“MyAzureDemo”$CopyResourceGroupName=“MyResourceGroup”$CopyDatabaseName=“MyAzureDemo_Copy”New-AzSqlDatabaseCopy -ResourceGroupName $ResourceGroupName -ServerName $sourceSQLServer -DatabaseName $SourceDatabaseName `-CopyResourceGroupName $CopyResourceGroupName -CopyDatabaseName $CopyDatabaseName
You can see in the below image, the database is created in the same server with the name myazuredemo copy database.
Azure Database Copy using Azure CLI
Before you can start using the Azure CLI commands, you will need to run through the series of steps to configure the
settings and log into the subscription. First, log in to Azure requires you to provide the authentication details
and paste the code on the Azure web site. After you log in to the Azure subscription, you’re set to use the
accounts. I will discuss more on the installation and configuration of the Azure CLI in the next article
I will be using CloudShell to run the Azure CLI commands. The parameters are no different from Azure Powershell commands
To create a copy of the Azure SQL database, run the following commands:
- Start CloudShell
- Open PowerShell console
Copy and paste the below content in the PowerShell console$ResourceGroupName=“MyResourceGroup”$sourceSQLServer=“myazureDemo12”$SourceDatabaseName=“MyAzureDemo”$CopyResourceGroupName=“MyResourceGroup”$CopyDatabaseName=“MyAzureDemo_2”az sql db copy —resource-group $ResourceGroupName —server $sourceSQLServer —name $SourceDatabaseName —dest-name $CopyDatabaseName
- Note: The database copy operation is an asynchronous operation. The target database is immediately created after you
initiate the request. If you need to cancel, run the drop database command with the target database name using the
following command Drop database <DatabaseName>
Azure Database Copy using T-SQL
In this section, you’ll see how to create a copy of the database using T-SQL commands.
- Log in to the master database
Run the T-SQL CREATE DATABASE <DatabaseName> … AS COPY OF statementCREATE DATABASE MyAzuredemo_1 AS COPY OF myazuredemo12.MyAzureDemo (SERVICE_OBJECTIVE = ‘S2’)
Next, verify and validate the database using SSMS. You can see that the MyAzureDemo_1 database is created in the
same server, which is a copy of the MyAzureDemo database with the service tier setting of S2
- Note: Login must be a server administrator or dbamanager role
In this article, we learned the basics of a database copy operation and underlying technology used to copy a
database in Azure along with the different service models available with cloud computing. We then discussed the
different methods used to copy the database such as Azure Portal, T-SQL, Azure PowerShell, and Azure CLI.
We also discussed a number of considerations that will affect performance and how to estimate the copy progress
operation in detail.
Here is the interesting scenario—the Auditing Azure SQL database. As we all know, the backup information has no
access and it is hidden. The workaround is to restore the database and use database copy operation to get the clone
of the point-in-time database to the target on another subscription. By doing this, we can say that the backup copy
That’s all for now…