In the C:\repos\database-deployment-automation\psscripts folder, you’ll see a PowerShell script called New-AzureSqlDatabase.ps1 that includes all of the steps covered in this section in a single script. $database = New-AzSqlDatabase -ResourceGroupName $resourceGroupName ` FirewallRuleName "FirewallRule_AccessRule" -StartIpAddress $publicIp -EndIpAddress $publicIp ` $serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName ` # Create a server firewall rule that allows access from the specified IP range SqlAdministratorCredentials $(New-Object -TypeName -ArgumentList $sqlAdministrator, $(ConvertTo-SecureString -String $sqlPassword -AsPlainText -Force)) ` $azSqlServer = New-AzSqlServer -ResourceGroupName $resourceGroupName ` $publicIp = Invoke-RestMethod '' | Select-Object -ExpandProperty IP
Run the following PowerShell code below and you should configure all of the above.
A resource group called rg-dbaautomation.To create an Azure SQL database with the exact specifications you need to follow along in this tutorial be sure you create:
If you need help, check out the ATA blog post How to Create an Azure SQL Database with PowerShell for specific instructions. Rather than covering each command to create an Azure SQL database, this tutorial will provide the basic instructions. Setting up the Azure SQL Databaseīefore you can automatically deploy changes to a SQL database, you must create one first. Once you understand each step and have mastered performing the tasks manually, you’ll then learn how to bring all of this together into an automated Azure DevOps pipeline. There are many steps in this process so first, you’re going to learn how to manually perform each step. Like with any good automation project, you must first know how to perform each task manually. It is up to you to manage your Azure resources accordingly! If this sounds like an interesting project to walk through, let’s get started! Creating the Environment Manuallyīefore you get started, know that creating Azure resources will incur some kind of cost. Create a new T-SQL script in your GitHub repo, commit changes, and sync to GitHub to demonstrate the entire automation workflow.īy the end, you’ll have a complete Azure DevOps pipeline that will automate database changes. Create the Azure DevOps pipeline and integrate all of the tasks performed manually.Ħ. Manually testing changes the T-SQL scripts made with the PowerShell testing framework Pester.ĥ. Manually running some T-SQL scripts against the Azure SQL database using a database package with the dbops PowerShell module.Ĥ. Manually creating an Azure SQL database.ģ. Forking a pre-configured GitHub repository (repo) to work with on your own.Ģ. This tutorial will be organized in a few different rough steps:ġ. This tutorial will build an entire database deployment automation project from scratch. This tutorial uses a service connection called Github.
A computer to run PowerShell – This tutorial uses Windows 10 using Windows PowerShell v5.1 but later versions of PowerShell should work.If you intend to follow along, be sure you have the following: This tutorial is going to be a step-by-step process to build a specific project. Executing the Azure DevOps Pipeline Manually.Setting up Prerequisites for the YAML Pipeline.Building the Database Deployment Automation Workflow.Introducing SQL Changes to the Database.