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.
- Create an Azure SQL Database: You require a source active Azure database. You can create it using the Azure portal, Azure CLI, PowerShell. Refer to the article Create Azure SQL Database using Azure PowerShell for more details
- Create an Azure automation account: Automation provides runbooks to specify your scripts in PowerShell, Python. These runbooks define scripts with the workflow to deploy your resources. To create the runbooks, we require an azure automation account. You can refer to the article Getting started with Azure Automation and Automate Pause and Resume of Azure Analysis Services for more details
- 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
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
-
Import Az.Sql PowerShell module: It has cmdlets for Azure resource manager in PowerShell
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
In the automation account, look for Runbooks and click on Create a runbook as shown below.
Enter the runbook name, type and description to create the automation runbook.
It creates an empty runbook, as shown below.
Open this runbook and click on Edit.
In the runbook editor, copy-paste the following PowerShell script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
PARAM ( [Parameter(Mandatory=$true)] [string]$NewDbName ) Read-Host $NewDbName = "Please Enter First Name?" $connectionName = "AzureRunAsConnection" $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName Write-Output "Logging in to Azure..." Connect-AzAccount ` -ServicePrincipal ` -TenantId $servicePrincipalConnection.TenantId ` -ApplicationId $servicePrincipalConnection.ApplicationId ` -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint $SQLServerName = Get-AutomationVariable -Name "SqlServer" $database = Get-AutomationVariable -Name "Database" Write-Output "Azure SQL Database server" Write-Output $SQLServerName Write-Output "Azure SQL Database name" Write-Output $database $resourceGroupName="azuresqldemo" $replicaDb = (Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -DatabaseName $NewDbName ` -ServerName $SQLServerName -ErrorAction SilentlyContinue) write-Output $replicaDb if($replicaDb) { write-Output "Specified target Azure SQL Database already exists." } else { write-Output "Creating a database copy using Azure Automation runbook.." New-AzSqlDatabaseCopy -ResourceGroupName $resourceGroupName -ServerName $SQLServerName -DatabaseName $database -CopyResourceGroupName $resourceGroupName -CopyServerName $SQLServerName -CopyDatabaseName $NewDbName } |
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.
|
1 2 3 4 5 6 |
PARAM ( [Parameter(Mandatory=$true)] [string]$NewDbName ) Read-Host $NewDbName = "Please Enter New Azure SQL Database Name" |
Retrieve information about azure automation connection
In this section, we use Get-AzAutomationConnection retrieves metadata for AzureRunAsConnection for automation.
|
1 2 3 4 5 6 7 8 |
$connectionName = "AzureRunAsConnection" # Get the connection "AzureRunAsConnection " $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName Connect-AzAccount ` -ServicePrincipal ` -TenantId $servicePrincipalConnection.TenantId ` -ApplicationId $servicePrincipalConnection.ApplicationId ` -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint |
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.
|
1 2 3 4 5 |
Connect-AzAccount ` -ServicePrincipal ` -TenantId $servicePrincipalConnection.TenantId ` -ApplicationId $servicePrincipalConnection.ApplicationId ` -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
#Enter the name for your server variable $SQLServerName = Get-AutomationVariable -Name "SqlServer" #Enter the name for your database variable $database = Get-AutomationVariable -Name "Database" Write-Output "Azure SQL Database server" Write-Output $SQLServerName Write-Output "Azure SQL Database name" Write-Output $database |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$replicaDb = (Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -DatabaseName $NewDbName -ServerName $SQLServerName -ErrorAction SilentlyContinue) write-Output $replicaDb if($replicaDb) { write-Output " Specified target Azure SQL Database already exists" } else { write-Output " Creating a database copy using Azure Automation runbook” New-AzSqlDatabaseCopy -ResourceGroupName $resourceGroupName -ServerName $SQLServerName -DatabaseName $database -CopyResourceGroupName $resourceGroupName -CopyServerName $SQLServerName -CopyDatabaseName $NewDbName } |
Save your azure runbook before testing the execution.
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.
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
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.
As shown below, SSMS is connected to the required database. Its version is 12.0.2000.8
Publish and schedule runbook in Automation account in Azure portal
Once we have tested the runbook, click on Publish.
In the runbook dashboard, it shows authoring status as Published.
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.
In the schedule, we enter the mandatory parameter value because it is required for our runbook execution.
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023