Advanced Data Solutions : Create Azure SQL Server and DTU based SQL Database with TDE State Enabled using PowerShell script

Create Azure SQL Server and DTU based SQL Database with TDE State Enabled using PowerShell script

This article is focused on creating an Azure SQL Server to a newly created Resource Group and then creating a SQL Database of Basic Edition with DTU capacity with its TDE (Transparent Data Encryption) state as Enabled using PowerShell commands

This article is divided into following 5 sections.

Connecting to Azure
Creating a new Resource Group
Creating SQL Server to the Resource Group created from the Step 3
Creating Basic Edition SQL Database
Checking if TDE State is already enabled, if not, setting the TDE State to Enabled

For running the PowerShell commands, make sure you have AzResources Module installed, if not then run the following command to Install it to your local system

PS Script: Install-Module -Name Az.Resources

Once the Module is installed, now it’s the time to follow the 5 steps mentioned above,

Below mentioned all the 5 steps in detail with the PowerShell scripts,

##Step 1 - Creating an Azure Connection with username and password

## Connection to Azure

Write-Host "Connecting to Azure"

Connect-AzAccount

## Connected to Azure

Write-Host "Connected to Azure"

## -------------------------------------------------------------------------------------------------------------------------------------------------

#Step2 - Creating New Resource Group, if you already have a resource group you can use the existing one

##Creating new Resource Group at West Europe Location

$rg = New-AzResourceGroup -Name 'rg-sqlserverautomation' -Location "West Europe"

## -------------------------------------------------------------------------------------------------------------------------------------------------

##Step 3 - Provisioning Azure SQL Server in the Resource Group

## Creating the Azure SQL Server

$azSqlServer = New-AzSqlServer `

-ServerName 'sqlshrushti123' `

-ResourceGroupName $rg.ResourceGroupName `

-Location $rg.Location `

-SqlAdministratorCredentials (Get-Credential)

##Print Output - Azure SQL Server Name

Write-Host "Azure SQL Server $($azSqlServer.ServerName) in the $($rg.ResourceGroupName) is Created"

Write-Host $azSqlServer.ServerName

##-------------------------------------------------------------------------------------------------------------------------------------------------

##Step 4 - Creating Basic Edition SQL Database with DTU Capacity

##Create a Basic Edition DTU based SQL Database in newly provisioned SQL Server

$sqlDatabase = New-AzSqlDatabase -ResourceGroupName $rg.ResourceGroupName -ServerName $azSqlServer.ServerName -DatabaseName 'ShrushtiTestDatabase' -Edition 'Basic'

Write-Host "Azure SQL Server $($azSqlServer.ServerName) in the $($rg.ResourceGroupName) is Created"

Write-Host $sqlDatabase.DatabaseName

##-------------------------------------------------------------------------------------------------------------------------------------------------

## Print the DTU Capacity of the SQL Database

##Check the SQL Database Capacity

$fetchDatabase = Get-AzSqlDatabase -ResourceGroupName $rg.ResourceGroupName -ServerName $azSqlServer.ServerName

Write-Host "For Selected SQL Serve $($azSqlServer.ServerName) the DTU Capacity is $($fetchDatabase.Capacity)"

##-------------------------------------------------------------------------------------------------------------------------------------------------

##Step 5 - Checking if TDE state is Enabled if not, then setting the TDE State as Enabled

##Check if the TDE State is already Enabled

$validateTDE = Get-AzSqlDatabaseTransparentDataEncryption -ServerName $azSqlServer.ServerName -ResourceGroupName $rg.ResourceGroupName -DatabaseName $fetchDatabase.DatabaseName -State "Enabled"

if ($validateTDE.State -eq "Enabled" ) {

Write-Host "TDE State for SQL Database is already Enabled"

}

else {

## Set the Transparent Data Encryption as Enabled to SQL database

$setTDEState = Set-AzSqlDatabaseTransparentDataEncryption -ResourceGroupName $rg.ResourceGroupName --ServerName $azSqlServer.ServerName -DatabaseName $fetchDatabase.DatabaseName -State "Enabled"

Write-Host "TDE State for SQL Database is set to Enabled"

}

##-------------------------------------------------------------------------------------------------------------------------------------------------

Now, lets verify if the script has run perfectly or not by checking it

Navigate to https://portal.azure.com -> Resource Group -> Created Resource Group -> You will find the created SQL Server -> In the SQL Server -> SQL Database -> Transparent Data Encryption -> It should be set as Enabled