Azure SQL makes our lives much easier as much IT professionals and DevOps professionals. There was a time that we had to set up virtual machines or physical hosts, networking, an operating system, then finally install SQL Server. With Azure's built-in Azure SQL service, it makes as a much easier way to store our application data. Today we're going to take a look at spinning up an Azure SQL Server, database, and firewall rules.

Prerequisites;

1. Azure account

2. Visual Studio Community or Enterprise

If you want to follow what code I'm using, you can find it on my Github: https://github.com/AdminTurnedDevOps/AZ-203-Code/blob/master/azureSQL/New-azureSQL.ps1

The first thing we'll need to do is open up VSCode and create a new PowerShell file (.ps1). The first part of our script will be creating out function and param block.

function New-AzureSQLDB {
   [cmdletbinding(SupportsShouldProcess, ConfirmImpact = 'low', DefaultParameterSetName = 'newDB')]
   param()

After we have this, we can start putting in our parameters.

param(
       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 0,
           HelpMessage = 'Please enter your Resource Group name')]
       [ValidateNotNullOrEmpty()]
       [Alias('RG')]
       [string]$resourceGroup,

       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 1,
           HelpMessage = 'Please enter your location')]
       [ValidateNotNullOrEmpty()]
       [string]$location,

       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 2,
           HelpMessage = 'Please enter your SQL server name')]
       [ValidateNotNullOrEmpty()]
       [string]$serverName,

       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 2,
           HelpMessage = 'Please enter your database name')]
       [ValidateNotNullOrEmpty()]
       [Alias('DBName')]
       [string]$databaseName,

       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 3,
           HelpMessage = 'Please enter starting IP address of IPs that should have access to your SQL server')]
       [ValidateNotNullOrEmpty()]
       [string]$startingIP,

       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 4,
           HelpMessage = 'Please enter ending IP address of IPs that should have access to your SQL server')]
       [ValidateNotNullOrEmpty()]
       [string]$endingIP

)

Let's take a look at why we need each of these params going from the first parameter to the last.

1. The first parameter is to specify our resource group that we wish to place our Azure SQL server and database in.

2. The second parameter is the location/region that we wish to deploy our Azure SQL Server and database to.

3. The third parameter is our SQL Server name that we wish to use.

4. Our fourth parameter is our database name that we wish to use.

5. Our fifth and sixth parameters is for setting up our firewall rules. This is the range of IPs that we wish to have access to our SQL server.

begin {

$azSub = Get-AzContext
       if (-not($azSub.Name)) {
           Write-Warning 'No az subscription is set. Please set one...'
           Set-AzContext

}

}

In our begin block we have a "checker" that will confirm our AZ context is set up (our subscription). If it is not set, it will ask us to set one.

$newServer = Read-Host 'Would you like to create a new SQL server and firewall rules or use an existing? 1 for yes or 2 for no'

           switch ($newServer) {
               1 {
                   Write-Output 'Prompting for SQL username and password. Text will be hidden...'
                   $user = Read-Host 'Please enter username'
                   $pass = Read-Host 'Please enter password' -AsSecureString

                   $creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $pass

                   $serverParams = @{
                       'ResourceGroupName'           = $resourceGroup
                       'ServerName'                         = $serverName
                       'Location'                               = $location
                       'SqlAdministratorCredentials' = $creds
                   }

                   $firewallRuleParams = @{
                       'ResourceGroupName' = $resourceGroup
                       'ServerName'        = $serverName
                       'FirewallRuleName'  = 'allowedIPs'
                       'StartIpAddress'    = $startingIP
                       'EndIpAddress'      = $endingIP
                   }

                   New-AzSqlServer @serverParams
                   New-AzSqlServerFirewallRule @firewallRuleParams
               }

               2 {
                   $null

}

}

For one out of two of the core pieces in our process block, we have a switch that asks us if we want to create a new SQL server/firewall rules, or use an existing.

try {

$databaseParams = @{
               'ServerName'        = $serverName
               'DatabaseName'      = $databaseName
               'ResourceGroupName' = $resourceGroup
           }

           New-AzSqlDatabase @databaseParams

}

In the above block of code, we have our "create database" portion, which runs despite of what choice is picked for our switch.

After putting it all together with some error handling, we have the final piece of code which is below.

function New-AzureSQLDB {
   [cmdletbinding(SupportsShouldProcess, ConfirmImpact = 'low', DefaultParameterSetName = 'newDB')]
   param(
       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 0,
           HelpMessage = 'Please enter your Resource Group name')]
       [ValidateNotNullOrEmpty()]
       [Alias('RG')]
       [string]$resourceGroup,

       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 1,
           HelpMessage = 'Please enter your location')]
       [ValidateNotNullOrEmpty()]
       [string]$location,

       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 2,
           HelpMessage = 'Please enter your SQL server name')]
       [ValidateNotNullOrEmpty()]
       [string]$serverName,

       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 2,
           HelpMessage = 'Please enter your database name')]
       [ValidateNotNullOrEmpty()]
       [Alias('DBName')]
       [string]$databaseName,

       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 3,
           HelpMessage = 'Please enter starting IP address of IPs that should have access to your SQL server')]
       [ValidateNotNullOrEmpty()]
       [string]$startingIP,

       [parameter(Mandatory,
           ParameterSetName = 'newDB',
           Position = 4,
           HelpMessage = 'Please enter ending IP address of IPs that should have access to your SQL server')]
       [ValidateNotNullOrEmpty()]
       [string]$endingIP
   )

   begin {
       $azSub = Get-AzContext
       if (-not($azSub.Name)) {
           Write-Warning 'No az subscription is set. Please set one...'
           Set-AzContext
       }
   }

   process {
       $newServer = Read-Host 'Would you like to create a new SQL server and firewall rules or use an existing? 1 for yes or 2 for no'

       try {
           switch ($newServer) {
               1 {
                   Write-Output 'Prompting for SQL username and password. Text will be hidden...'
                   $user = Read-Host 'Please enter username'
                   $pass = Read-Host 'Please enter password' -AsSecureString

                   $creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $pass

                   $serverParams = @{
                       'ResourceGroupName'           = $resourceGroup
                       'ServerName'                  = $serverName
                       'Location'                    = $location
                       'SqlAdministratorCredentials' = $creds
                   }

                   $firewallRuleParams = @{
                       'ResourceGroupName' = $resourceGroup
                       'ServerName'        = $serverName
                       'FirewallRuleName'  = 'allowedIPs'
                       'StartIpAddress'    = $startingIP
                       'EndIpAddress'      = $endingIP
                   }

                   New-AzSqlServer @serverParams
                   New-AzSqlServerFirewallRule @firewallRuleParams
               }

               2 {
                   $null
               }
           }
       }

       catch {
           Write-Error 'An issue occurred while creating your SQL server and/or firewall rules'
           $PSCmdlet.ThrowTerminatingError($_)
       }

       try {
           $databaseParams = @{
               'ServerName'        = $serverName
               'DatabaseName'      = $databaseName
               'ResourceGroupName' = $resourceGroup
           }

           New-AzSqlDatabase @databaseParams
       }

       catch {
           Write-Error "An issue occurred while creating a database in: $serverName"
           $PSCmdlet.ThrowTerminatingError($_)
       }
   }
   end { }

}

Now we're ready to run our code. For myself, I'm going to choose to create a new SQL server and firewall rules. To do this, I'll initiate my function and run the following line;

New-AzureSQLDB -resourceGroup Development -location eastus -serverName mjldbserver92 -databaseName mjldbname92 -startingIP "0.0.0.0" -endingIP "0.0.0.0"

I'll be asked if I want to create my SQL Server and firewall rules. I'm going to choose 1 for yes.

Next I'll be prompted to type in my username and password for SQL. The password will be input as a secure string.

Now I can head over to the Azure Portal and see my SQL server. Let's click on it and take a look.

If I go to Settings > SQL Databases on the left pane, I can see my databases.

Now that I can see my database, I can click the "Connect with..." button to connect with Visual Studio.

Once I click the "Open in Visual Studio" button, I'll be prompted for my password.

We are now able to see our SQL server, database, and run queries.