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.

1. Connecting to Azure
2. Creating a new Resource Group
3. Creating SQL Server to the Resource Group created from Step 3
4. Creating Basic Edition SQL Database
5. 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 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, let's 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

Fig 1.1 – SQL Server Provisioned

Fig 1.2 – SQL Database of Basic Edition provisioned In the SQL Server

Fig 1.3 – TDE state is Enabled in the SQL Database

I hope this article seems useful for all the Azure enthusiasts on how they can provision SQL Server and SQL Database using PowerShell scripts.

Keep Learning!

Keep Sharing!