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
- T-SQL
Introduction:
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
- 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
- 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_copies
1select * 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 completion
1select * from sys.dm_operation_status - 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
- Start Cloud Shell
- Select the following code block and copy the code
-
Paste the code into the PowerShell session
12345678$ResourceGroupName="MyResourceGroup"$sourceSQLServer="myazureDemo12"$SourceDatabaseName="MyAzureDemo"$CopyResourceGroupName="MyResourceGroup"$CopyDatabaseName="MyAzureDemo_Copy"New-AzSqlDatabaseCopy -ResourceGroupName $ResourceGroupName -ServerName $sourceSQLServer -DatabaseName $SourceDatabaseName `-CopyResourceGroupName $CopyResourceGroupName -CopyDatabaseName $CopyDatabaseName - Start CloudShell
- Open PowerShell console
-
Copy and paste the below content in the PowerShell console
123456$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>
- Log in to the master database
-
Run the T-SQL CREATE DATABASE <DatabaseName> … AS COPY OF statement
1CREATE 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
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021
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.
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.
Azure Database Copy using PowerShell
To copy an Azure SQL database, run the following PowerShell commands.
For the demo, I am using CloudShell.
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:
Azure Database Copy using T-SQL
In this section, you’ll see how to create a copy of the database using T-SQL commands.
Conclusion
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 is audited.
That’s all for now…
Table of contents
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram