Prashanth Jayaram
Create SQL database summary page

Four different methods to copy your Azure SQL database

September 30, 2020 by

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:

  1. Discuss the database copy technology
  2. Internals of the database copy operation
  3. How to monitor the copy progress
  4. Different methods to copy the database
  5. 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:

  1. Azure portal
  2. Azure PowerShell
  3. Azure CLI
  4. T-SQL

Introduction:

In this section, we will see the internals of the Azure Database copy operation.

  1. To copy the database in Azure uses geo-replication technology. Once replica seeding is complete, the geo-replication link is automatically terminated
  2. 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
  3. A database copy is a process that refers to a transactional consistent snapshot of the source database
  4. 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.

    1. Log in to Azure Portal and click Browse Azure SQL Server
    2. Select Azure SQL Database and locate the database for which you would like to create a clone

      Azure SQL database copy database

    3. Now, you need to enter the target database details
      1. Type in the database name
      2. Target server
      3. Choose “Yes” if you want to host it in the elastic database pool. If not, leave the default value “No”
      4. Decide the Compute and Storage
      5. 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

      Prep step: Create SQL Database

    4. Next, Accept the Terms and Conditions
    5. Review the Summary and click the Create button to start the database copy operation

      Create SQL database summary page

    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.

    1. Query the sys.databases and check the state_desc column. During the copy process, the database is set to COPYING status
    2. Using sys.dm_database_copies

      Internal table to understand the seeding

      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

    3. Running sys.dm_operation_status views. You can see the Operation and state_desc columns to understand the percentage of completion

      Azure SQL Database copy progress verfification and validation

    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.

    1. Start Cloud Shell
    2. Select the following code block and copy the code
    3. Paste the code into the PowerShell session

    You can see in the below image, the database is created in the same server with the name myazuredemo copy database.

    Azure SQL Database copy program using PowerShell screen

    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:

    1. Start CloudShell
    2. Open PowerShell console
    3. Copy and paste the below content in the PowerShell console

      Azure SQL database copy using Az CLI

    • 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.

    1. Log in to the master database
    2. Run the T-SQL CREATE DATABASE <DatabaseName> … AS COPY OF statement

    3. 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

      SSMS connectivity to test the connecting string

    • Note: Login must be a server administrator or dbamanager role

    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

    IT Cloud Automation using PowerShell
    Deep dive into IT Cloud Automation using PowerShell
    Getting started with Azure Automation
    Getting started with Azure SQL Database using Azure CLI
    Provisioning SQL Server 2019 Azure Container Instance using PowerShell
    Four different methods to copy your Azure SQL database

    Prashanth Jayaram
Azure, PowerShell, SQL Azure

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. 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

243 Views