Rajendra Gupta
Runbook status

Azure Automation: Create database copies of Azure SQL Database

April 27, 2021 by

This article will explore Azure automation for creating a new Azure SQL Database copy.

Introduction

In the article, Create a transactionally consistent copy of Azure SQL Database, we discussed the process to create an Azure SQL DB copy in the same, different subscription. We can create this database on the same or different server. It keeps the same service tier, compute size, backup redundancy of the source database server. The new database copy is independent of the source database, and you can manage your logins, users, permissions, objects like a regular azure database.

Suppose we get frequent requirements of creating database copy for our workload testing, deployment of new code, or performance tuning. You might need to create a database copy in another azure SQL Server hosting the development instance. You can always do the manual task, but you need to connect the Azure portal, go to the required resource, fill in the details, and then implement the things. In this case, It is always best to do task automation to avoid manual intervention. You can save your time and improve efficiency. You can also schedule the deployment during night hours where you don’t have to wake up and do the things.

How do we automatically create a copy of Azure SQL DB? Well, there are multiple ways to do that, but you can quickly implement it using Azure Automation.

In this article, let’s implement an azure runbook and schedule it for automatic copy database creation.

Requirements

To follow along with this article, implement the following stuff.

  • Note: I would suggest you go through these articles before proceeding further. It would give you a basic understanding of azure runbooks, their implementations and usages

In this article, I use the following azure server and database names:

  • Resource Group: azuresqldemo
  • Server FQDN: azuredemoinstance.database.windows.net
  • Database: labazuresql
  • Server admin login: sqladmin
  • Location: Central India
  • Automation Account: myautomationazure

azure server and database names

Create a runbook to create azure database copy using the Azure automation

In this section, we write scripts for the automation runbook using PowerShell scripts. Before we do it, we follow the below steps as a preparation step.

Import PowerShell modules in Azure automation account

  • Import Az.Accounts PowerShell module: Navigate to automation account in Azure portal->modules -> Browse Gallery and import Az.Accounts module

    database copy using azure automation

  • Import Az.Sql PowerShell module: It has cmdlets for Azure resource manager in PowerShell

    Import Az.Sql PowerShell module

Create variables in Azure automation accounts

We use variables to store the azure server and database name in the automation account. We can fetch the values of the variables in the PowerShell script for runbook execution.

Currently, I have two variables, as shown below:

  • sqlserver: It has an azure server name, i.e. azuredemoinstance in my lab environment
  • database: It is my source database name, i.e. labazuresql

Create variables

In the automation account, look for Runbooks and click on Create a runbook as shown below.

Create a runbook

Enter the runbook name, type and description to create the automation runbook.

Create a runbook details

It creates an empty runbook, as shown below.

Empty runbook

Open this runbook and click on Edit.

Edit a runbook

In the runbook editor, copy-paste the following PowerShell script.

Before we deploy this runbook, let’s understand the script in different parts.

Define a mandatory parameter for the new Azure SQL database name

At the beginning of the script, we define a mandatory parameter and ask the user to provide a new Azure SQL DB name. It stores the user input in the $NewDBName parameter.

It is a mandatory parameter ( Mandatory=$true), and the script does not run if we do not enter any value.

Retrieve information about azure automation connection

In this section, we use Get-AzAutomationConnection retrieves metadata for AzureRunAsConnection for automation.

Connect to Azure authenticated account

Now, we use PowerShell cmdlet Connect-AzAccount for connecting to Azure with the authenticated account. Here, we connect using the certificate-based service principal authentication.

Get the azure variables and store them into variables

Earlier, we created variables for storing the Azure server and database names. Now, we fetch the values of the variables and store them into the variables using the Get-AutomationVariable cmdlet as below:

  • $SQLServerName: In this variable, we store the Azure server name
  • $database: It stores the source Azure SQL DB name

We use the Write-Output cmdlet to print this information on the PowerShell console.

Copy the Azure SQL Database

In this section, we implement the following tasks for our azure automation runbook.

  • Initially, it uses Get-AzSqlDatabase to check the target ( copy) azure SQL DB existence and stores information in the $replicadb variable
    • Note here that we specify the parameter value $NewDbName for checking the azure database
    • Our intention here is to check whether the required database ( copied database) already exists in the resource group
  • If the target database already exists, the script terminates and prints the message “DB name already exists
  • If the target database does not exist, the script enters in the else condition
    • It prints a message Creating DB before starting database copy
    • It uses the PowerShell cmdlet New-AzSqlDatabase to copy the database with the specified source and target resource group, azure server and database information
      • Source: We specify source configuration using the -ResourceGroupName, -ServerName, -DatabaseName arguments
      • Target: It uses arguments -CopyResourceGroupName, -CopyServerName and -CopyDatabaseName in the script

Save your azure runbook before testing the execution.

Define a mandatory parameter

Test runbook scripts using Test Pane

Click on the Test Pane and enter the parameter (NewDBName) value and click on Start. In this parameter, we specify a name for the target(copy) azure database name.

It starts connecting to your azure account, fetch required parameters, variables value and deploy a consistent copy of your source azure SQL database [labazuresql]. The PowerShell runbook execution takes a few minutes before it’s completed.

Test runbook scripts

Now, you can refresh the Azure portal and navigate to the Azure SQL server. It shows below two databases:

  • labazuresql: Source database
  • azureautomateddbcopy: A copy of the source database

Refresh the Azure portal

Connect Azure SQL database using SSMS and Azure Data Studio

This article has both source and target (copied) Azure database in an Azure SQL server. You can note down the azure server FQDN from the script or Azure portal.

In the SSMS, connection window, enter azure server FQDN and enter the highlighted text box’s database name.

Connect azure SQL database

As shown below, SSMS is connected to the required database. Its version is 12.0.2000.8

SSMS DB version

Publish and schedule runbook in Automation account in Azure portal

Once we have tested the runbook, click on Publish.

Runbook status

In the runbook dashboard, it shows authoring status as Published.

Status published - Azure SQL Database

Now, you can schedule the runbook as per your requirement. As shown below, I have set it to run at 01/05/2021 5:45 PM IST.

schedule the runbook

In the schedule, we enter the mandatory parameter value because it is required for our runbook execution.

Copy DB

Conclusion

This article explored azure automation for creating a consistent copy of your source Azure SQL database. In further articles, we will implement more automation tasks for Azure infrastructure resources.

Table of contents

Azure Automation: Export Azure SQL Database to Blob Storage in a BACPAC file
Azure Automation: Create database copies of Azure SQL Database
Azure Automation: Automate Azure SQL Database indexes and statistics maintenance
Azure Automation: Automate Pause and Resume of Azure Analysis Services
Azure Automation: Automate data loading from email attachments using Azure Logic Apps
Azure Automation: Building approval-based automated workflows using Azure Logic Apps
Azure Automation: Auto-scaling Azure SQL database with Azure Logic Apps
Azure Automation: Analyzing Twitter sentiments using Azure Logic Apps
Azure Automation: Use Azure Logic Apps to import data into Azure SQL Database from Azure Blob Storage
Azure Automation: Publish LinkedIn posts and tweets automatically using Azure Logic Apps
Azure Automation: Export Azure SQL database data to CSV files using Azure Logic Apps
Azure Automation: Translate documents into different languages using Cognitive Services with Azure Logic Apps
Azure Automation: Azure Logic Apps for face recognition and insert its data into Azure SQL Database

Rajendra Gupta
Azure, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

834 Views