In fast-paced development environments, ensuring that non-production databases reflect the latest production data is critical for developers. Instead of manually restoring production data into non-prod environments upon request, automating this process can save time and reduce errors. 

In this post, I will share a real-world example where we automated the process of deleting a non-prod database, creating a copy of the production database, and running necessary SQL procedures to configure users and roles—all using an Azure Automation Runbook. To ensure any failures in the process were immediately flagged, we integrated SendGrid for error notifications, eliminating the risk of unnoticed job failures. 

Background and Objective 

Our development teams often needed refreshed production data for testing, but manually restoring production data into non-prod environments was a time-consuming task. To streamline this, we automated the process of: 

1. Deleting the existing non-prod database. 
2. Creating a copy of the production database. 
3. Executing SQL queries to configure users and roles on the newly copied database. 
4. Send immediate email alerts if any errors occur during the process. 

By automating this workflow, we empowered our developers to test up-to-date production data without the overhead of manual intervention. However, we found that the job logs would mark a task as completed even when an error occurred, leading to delayed resolutions. This problem was solved by integrating SendGrid to notify the team immediately if any part of the script failed. 

Exploring a career path in Azure?
Get started with our Azure Data Engineer DP-203 Training

Database Copy and SQL User Configuration 

The process starts by deleting the non-production database, creating a copy of the production database, and then running stored procedures to set up users and roles. Below is an overview of how the script works. 

Step 1: Deleting the Non-Production Database 

The first step is to delete the existing non-prod database before creating a fresh copy. Below is the PowerShell code that achieves this:

try 
{ 
 Write-Output "Deleting target database : $targetDBName"  
 Remove-AzSqlDatabase -ResourceGroupName $targetResourceGroup -ServerName $targetServer -DatabaseName $targetDBName -ErrorAction Stop 
 Write-Output "$targetDBName Deleted" 
 Send-EmailUsingSendGrid -SendGridAPIKey $apiKey -SendToEmail $toEmail -SendFromEmail $fromEmail -Subject "Database Deleted Successfully" -Body "The database $targetDBName was deleted successfully." 
} 
catch 
{ 
 $errorMessage = $_.Exception.Message 
 Write-Output "Failed to delete database: $targetDBName" 
 Send-EmailUsingSendGrid -SendGridAPIKey $apiKey -SendToEmail $toEmail -SendFromEmail $fromEmail -Subject "Error Deleting Database" -Body $errorMessage 
} 

If the deletion is successful, a confirmation email is sent. In case of an error, the script sends an email with the error details, preventing unnoticed failures. 

Step 2: Creating a Copy of the Production Database 

Once the non-prod database is deleted, the script creates a new copy from production: 

write-Output "Creating a database copy using Azure Automation runbook……"  
try 
{ 
 New-AzSqlDatabaseCopy -ResourceGroupName $sourceResourceGroup -ServerName $SourceSQLServerName -DatabaseName $sourceDatabase -CopyResourceGroupName $targetResourceGroup -CopyServerName $targetServer -ElasticPoolName $targetElasticPoolName -CopyDatabaseName $targetDBName -ErrorAction Stop 
 Write-Output "Database copy created successfully." 
 Send-EmailUsingSendGrid -SendGridAPIKey $apiKey -SendToEmail $toEmail -SendFromEmail $fromEmail -Subject "Database Copy Created Successfully" -Body "The database copy $targetDBName was created successfully from $sourceDatabase." 
} 
catch 
{ 
 $errorMessage = $_.Exception.Message 
 Write-Output "Failed to create database copy: $sourceDatabase" 
 Send-EmailUsingSendGrid -SendGridAPIKey $apiKey -SendToEmail $toEmail -SendFromEmail $fromEmail -Subject "Error Creating Database Copy" -Body $errorMessage 
} 

 This step ensures the database copy is created from the latest production data. Again, SendGrid notifies the team if something goes wrong.

Step 3: Executing SQL Queries for User and Role Configuration

After creating the database copy, the script runs several SQL queries to configure users and roles in the new environment. This includes creating service accounts, assigning roles, and granting the necessary permissions. 

#Define SQL Queries 
$queryList = @( 
 "exec [dbo].[spPostBackup];", # Executes a post-backup stored procedure 
 "CREATE USER [service_user] FOR LOGIN [service_user] WITH DEFAULT_SCHEMA=[dbo]", # Creates a service user 
 "CREATE ROLE [schema_definition_reader]", # Creates a custom role for reading schema definitions 
 "GRANT VIEW DEFINITION TO [schema_definition_reader]", # Grants the role view permissions 
 "ALTER ROLE [schema_definition_reader] ADD MEMBER [service_user]", # Adds the service user to the role 
 "CREATE USER [external_user] FROM EXTERNAL PROVIDER", # Creates an external user (e.g., from Azure AD) 
 "ALTER ROLE [db_datareader] ADD MEMBER [external_user]" # Adds the external user to the data reader role 
) 
  
foreach ($query in $queryList) 
{ 
 try 
 { 
 $SQLOutput = $(Invoke-Sqlcmd -AccessToken $accessToken -ServerInstance $SQLServerName -Database $database -Query $query -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose -ErrorAction Stop) 4>&1 
 Write-Output $SQLOutput 
 Write-Output "Successfully executed SQL query: $query" 
 Send-EmailUsingSendGrid -SendGridAPIKey $apiKey -SendToEmail $toEmail -SendFromEmail $fromEmail -Subject "Successfully executed query" -Body "Successfully executed SQL query: $query" 
 } 
 catch 
 { 
 $errorMessage = $_.Exception.Message 
 Write-Output "SQL Query Failed: $query" 
 Send-EmailUsingSendGrid -SendGridAPIKey $apiKey -SendToEmail $toEmail -SendFromEmail $fromEmail -Subject "SQL Query Execution Error" -Body $errorMessage 
 } 
} 

Explanation of Queries

• Stored Procedure Execution: Executes a post-backup procedure. 
• Service User Creation: Creates a user account for a service. 
• Role Creation and Assignment: Defines a role for reading schema definitions and grants the appropriate permissions to the service user. 
• External User Configuration: Creates a user from an external provider (such as Azure AD) and assigns them to the db_datareader role. 

This automated SQL execution ensures the new database is ready for use with the appropriate access configurations. 

Step 4: Error Handling and Notifications with SendGrid

A critical part of this process is the error handling. By using SendGrid, we ensure that if any step of the process fails—whether it’s deleting the database, copying it, or executing SQL queries—the team is immediately notified via email. 
  
The SendGrid integration works by sending a notification email whenever an error is encountered, with the specific error message and context. This prevents issues from going unnoticed and allows us to resolve problems before they impact developers or downstream processes. 

Conclusion

This azure automation not only streamlines the process of keeping non-prod databases up to date but also adds robust error handling with SendGrid email notifications. By catching errors at every step and alerting the team, we can address issues in real-time, ensuring the databases are always in a ready state for testing. 
This solution has drastically reduced manual intervention and improved the efficiency of our development teams, allowing them to test the latest production data daily without having to request manual restores. 
By implementing this automated workflow, you can increase the efficiency of database management while ensuring that any issues are swiftly addressed. 

Author: Kashif Baksh is a Senior Principal Consultant at Fusion Practices