Four different methods to copy your Azure SQL database

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

    Prashanth Jayaram
    Latest posts by Prashanth Jayaram (see all)

Author: admin

Leave a Reply

Your email address will not be published.