Skip to main content

Azure SQL automation with PowerShell

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.

Pre-requistes;

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.



Comments

Popular posts from this blog

Run PowerShell code with Ansible on a Windows Host

Ansible is one of the Configuration Manager kings in the game. With it's easy-to-understand syntax and even easier to use modules, Ansible is certainly a go-to when you're picking what Configuration Management you want to use for your organization. Your question may be "but Ansible is typically on Linux and what happens when I'm in a Windows environment?". Luckily I'm here to tell you that Ansible will still work! I was pleasantly surprised with how easy it is to use Ansible on Windows with a little WinRM magic. Let's get started.

Pre-requisites for this post:
1) WinRM set up to connect to your Windows host from Ansible
2) Ansible set up for Windows Remote Management
3) SSH access to the Ansible host
4) Proper firewall rules to allow WinRM (port 5985) access from your Ansible host to your Windows host
5) Hosts file set up in Ansible that has your IP or hostname of your Windows Server.
6) At least one Linux host running Ansible and one Windows Server host …

Running PowerShell commands in a Dockerfile

As Docker continues to grow we are starting to see the containerization engine more and more on Windows. With the need for containers on Windows, we also need the same automation we get in Linux with Dockerfiles. Today we're going to create a Dockerfile that runs PowerShell cmdlets.
Prerequisites; 1. Docker for Windows
2. A code editor (VSCode preferred)

Let's go ahead and get our Dockerfile set up. Below is the Dockerfile I used for this post.

from mcr.microsoft.com/windows/servercore:1903 MAINTAINER Michael Levan RUN powershell -Command Install-WindowsFeature -Name Web-Server RUN powershell -Command New-Item -Type File -Path C:\ -Name config
As you can see from the above, this is a tiny Dockerfile. What this will do is install the IIS Windows 

Feature and create a new file in C:\ called "config".
You should see something very similar to the below screenshot;

Next let's create a running container out of our image. First we'll need to run docker container ls to

 get o…

DevOps tooling in the Microsoft realm

When I really started to dive into automation and practicing DevOps with specific tooling, there were a few key players. At the time Microsoft was not one of them. They were just starting to embrace the open source world, including the art and practice of DevOps. Since then Microsoft has went all in and the tech giant has made some incredible tooling. Recently I switched to a Microsoft-heavy environment and I love it. I went from AWS/Python/Ansible/Jenkins to Azure/PowerShell/ARM/Azure DevOps. My first programming language was PowerShell so being back in the saddle allowed me to do a full circle between all of the different types of tooling in both worlds. Today I want to share some of that tooling with you.

The first thing I want to talk about is ARM. What is ARM? ARM is a configuration management tool that allows you to perform software-defined-infrastructure. Much like Ansible and Terraform, ARM allows you to define what you want your environment to look like at scale. With ARM, yo…