Azure CLI, PowerShell, and SQL Server Operations Automation Flow

The Azure portal is great for first understanding your resources. You can visually see which settings are where and quickly follow the relationships between resources such as VMs, VNets, NSGs, Public IPs, and Disks. However, if you want to create the same configuration multiple times, clean and recreate the lab environment, or leave a history of changes to the operating environment, just clicking on the portal is not enough.

The reason why Azure CLI and PowerShell are organized together is because of this point. Both allow you to treat Azure resources like code. CLI is good for shell scripts and CI/CD, and PowerShell is good for handling Windows management tasks and Azure resource manipulation in the same context. Connecting SQL Server VM configuration and Azure SQL migration changes the perspective from “how to create resources” to “how to prepare an operational data service environment.”

CLI operations reveal resource dependencies

Even when creating one Azure VM, multiple resources are actually required. Resource Group, VNet, Subnet, Public IP, NSG, NIC, and VM are connected in order. In a portal, it passes on one screen, but if you divide it into CLI, the dependencies become clearer.

az login
az account show

az group create \
  --name rg-ubuntuvmkty \
  --location koreacentral

az network vnet create \
  --resource-group rg-ubuntuvmkty \
  --name vnet-ubuntukty \
  --address-prefix 10.0.0.0/16 \
  --subnet-name snet-ubuntukty \
  --subnet-prefix 10.0.1.0/24

Up to this point, we are at the stage of creating a network boundary, not a VM. It is easy to think of VMs first, but in operation, network design comes first. You must first check which address band to use, which subnet to place which role of resource, and whether there is space to attach a bastion or private endpoint later.

Public IP and NSG create an external access path.

az network public-ip create \
  --resource-group rg-ubuntuvmkty \
  --name pip-ubuntukty \
  --sku Standard \
  --allocation-method Static

az network nsg create \
  --resource-group rg-ubuntuvmkty \
  --name nsg-ubuntukty

az network nsg rule create \
  --resource-group rg-ubuntuvmkty \
  --nsg-name nsg-ubuntukty \
  --name AllowSSHFromMyIp \
  --protocol Tcp \
  --priority 1000 \
  --source-address-prefixes 203.0.113.10/32 \
  --destination-port-ranges 22 \
  --access Allow

In practice, we often open SSH to check, but it is better to leave an intent like AllowSSHFromMyIp rather than ending the rule name with AllowSSH. In operation, “where are port 22 allowed to come from” is more important than the fact that “port 22 is open.”

The NIC is the point that connects network elements to the VM.

az network nic create \
  --resource-group rg-ubuntuvmkty \
  --name nic-ubuntukty \
  --vnet-name vnet-ubuntukty \
  --subnet snet-ubuntukty \
  --network-security-group nsg-ubuntukty \
  --public-ip-address pip-ubuntukty

VM creation is closer to the end.

az vm create \
  --resource-group rg-ubuntuvmkty \
  --name vm-ubuntukty \
  --nics nic-ubuntukty \
  --image UbuntuLTS \
  --size Standard_B2s \
  --admin-username azureuser \
  --authentication-type ssh \
  --ssh-key-values ~/.ssh/id_rsa.pub \
  --tags Dept=IT Project=Demo

If you leave this flow as a script, you can recreate the same environment and see what values ​​have changed using a diff. Furthermore, it is a good practice before moving to Terraform/Bicep.

After creation, check the status and connection path without stopping at “VM has been created.”

az vm get-instance-view \
  --resource-group rg-ubuntuvmkty \
  --name vm-ubuntukty \
  --query "instanceView.statuses[].displayStatus" \
  --output table

az vm list-ip-addresses \
  --resource-group rg-ubuntuvmkty \
  --name vm-ubuntukty \
  --output table

If it is an Ubuntu VM, check the SSH connection, and if it is a Windows VM, check the RDP path. However, in an operating environment, it is better not to set the direct connection method using public IP as the default.

ssh azureuser@203.0.113.20

practice
  - Public IP + source IP restricted NSG

production preference
  - Azure Bastion
  - VPN or ExpressRoute
  - private subnet
  - no public management port

To make a CLI operation repeatable, place a create command and a confirm command together. Resource creation may have been successful, but the NSG may have been attached incorrectly, the NIC may have been connected to a different subnet, or the public IP may have been created dynamically. Therefore, it is better to output the current status in table form at the end of the script.

az resource list \
  --resource-group rg-ubuntuvmkty \
  --query "[].{name:name,type:type,location:location}" \
  --output table

This output is just for verification purposes, but it can also be helpful when comparing before and after changes. CLI scripts that do not use the plan/apply model, such as Terraform, must explicitly leave “what has been created.”

PowerShell fits well into the Windows operating flow

PowerShell is a natural when used with Windows VMs, SQL Server, RDP, and management scripts. You can create Azure resources and even carry out Windows management commands in one context.

$location = "KoreaCentral"
$rgName = "rg-myPowerShell"

$rg = New-AzResourceGroup `
  -Name $rgName `
  -Location $location

$subnet = New-AzVirtualNetworkSubnetConfig `
  -Name "frontendSubnet" `
  -AddressPrefix "10.0.0.0/24"

New-AzVirtualNetwork `
  -Name "vnet-MyPSNet" `
  -ResourceGroupName $rg.ResourceGroupName `
  -Location $location `
  -AddressPrefix "10.0.0.0/16" `
  -Subnet $subnet

The important thing about PowerShell is that variables and objects can be used continuously. The result of creating a Resource Group is stored in $rg and its properties are passed to the next command. If it is a script to be executed repeatedly, it is best to check its existence with Get-AzResourceGroup, and if it already exists, reuse it or explicitly stop it.

$existing = Get-AzResourceGroup -Name $rgName -ErrorAction SilentlyContinue

if ($null -eq $existing) {
  New-AzResourceGroup -Name $rgName -Location $location
}

You must be especially careful with delete commands. Deleting a Resource Group deletes all resources within it. It is convenient in a lab environment, but in operation, tags, locks, permissions, and approval procedures are required.

# Remove-AzResourceGroup -Name rg-myPowerShell

Even if these commands are left in the script, it is safer to comment them out by default and separate them into a separate cleanup script to prevent them from being executed accidentally.

PowerShell has the advantage of handling local Windows management tasks and Azure tasks in one file. For example, you can naturally connect the flow of creating a local directory, creating a settings file, querying Azure resource information, and passing it as a deployment parameter.

$ErrorActionPreference = "Stop"

$workspace = "C:\ops\azure-lab"
if (-not (Test-Path $workspace)) {
  New-Item -ItemType Directory -Path $workspace | Out-Null
}

$subscription = Get-AzContext
$subscription.Subscription.Name | Out-File "$workspace\subscription.txt"

In the operating script, input values ​​are collected into the top variable and important values ​​are output to the user during execution.

$location = "KoreaCentral"
$environment = "dev"
$owner = "platform"

Write-Host "Location: $location"
Write-Host "Environment: $environment"
Write-Host "Owner: $owner"

This output may seem like a small difference, but it reduces mistakes when switching between multiple subscriptions and resource groups. In particular, the command itself can be successful even if the Azure task is executed with the subscription context incorrectly set, so it is a good idea to make it a habit to check Get-AzContext.

For SQL Server VMs, setting boundaries is more important than “installation complete”

When loading SQL Server on Azure Windows VM, operating conditions vary, starting with image selection. If you choose an image that includes SQL Server, the installation process will be shortened, but you will need to check the license, version, and cost. If you install directly on a regular Windows Server image, you have a wide range of options, but you must take care of installation, patches, service accounts, ports, and firewall settings yourself.

VM OS: Windows Server 2019 or later
SQL Server: Developer / Express / Standard / Enterprise
VM size: at least 2 vCPU, 8 GB RAM for basic practice
OS disk: SSD
Data disk: separate managed disk for SQL data
Network: private subnet preferred
Access: Bastion or VPN preferred over public RDP

After installing SQL Server, test the connection using SSMS or Azure Data Studio. However, what you actually need to check is not just whether the service is running.

SQL Server Configuration Manager
- SQL Server service running
- TCP/IP enabled
- port 1433 only if remote access is required

Windows Firewall
- allow TCP 1433 only when needed

Azure NSG
- avoid public 1433
- prefer private IP, VPN, Bastion, peering, or private endpoint

In practice, you can test the connection externally using Public IP,1433. However, it is best to avoid opening SQL Server 1433 to the Internet in a production environment. At a minimum, the source IP should be restricted, and if possible, access should be allowed only within a private network.

Bad default
Internet -> VM public IP:1433 -> SQL Server

Preferred
Admin -> VPN/Bastion -> Private IP -> SQL Server
Application subnet -> Private IP:1433 -> SQL Server

SQL Server VM must also consider the data disk separately. If the OS disk, data files, log files, and backup files are all on the same disk, IO bottlenecks and recovery problems can increase. Although a single disk may be sufficient for small practices, it is a good idea to be conscious of data/log/backup boundaries when organizing the structure.

C: OS and SQL binaries
F: data files
G: transaction logs
H: backup files

What is needed in operation is recoverability rather than installation procedures. It's more important to know where your backups are stored, whether you understand the difference between snapshots and SQL backups, whether you can revert after a patch, and whether you separate administrator and application accounts.

When looking at the SQL Server VM from an operational perspective, the checklist changes as follows:

Compute
  - VM size is matched to workload
  - accelerated networking considered
  - availability option selected when needed

Storage
  - data and log disks separated when workload requires it
  - backup target separated from primary data disk
  - disk performance tier reviewed

Network
  - SQL port is private by default
  - RDP is not open to Internet
  - NSG source ranges are explicit

Operations
  - SQL backup job exists
  - Windows patching policy exists
  - SQL Server patching policy exists
  - monitoring and alerting enabled

SQL Server connection testing does not end with a simple login success. You need to check whether only the necessary DB is accessed by the application account, whether there are no unnecessary server permissions, and what network path remote access comes through.

select
  name,
  type_desc,
  is_disabled
from sys.server_principals
where type in ('S', 'U', 'G');

When it comes to backups, “I restored it” is more important than “I set it up.” If you repeat the restore procedure even for a small DB in a development/staging environment, the backup location, permissions, file name rules, and recovery time are actually visible.

Pre-assessment is half the battle with Azure SQL migration

The Azure SQL Migration extension in Azure Data Studio helps you evaluate and plan before moving SQL Server to Azure SQL Database or Azure SQL Managed Instance. Migration does not end with just “copying data.” Compatibility, features in use, downtime tolerance, network connection, authentication method, and application connection string changes must also be considered.

Migration assessment
- unsupported SQL features
- compatibility level
- database size
- schema complexity
- linked servers, jobs, CLR, cross-database queries
- downtime tolerance
- network path to Azure

The target must also be distinguished. Azure SQL Database has less management overhead and is closer to PaaS, but may have limitations if you need instance-level features. Azure SQL Managed Instance has greater compatibility with SQL Server, but may require greater network configuration and costs.

Azure SQL Database
- database-level PaaS
- easier management
- strong fit for new cloud-native apps

Azure SQL Managed Instance
- higher SQL Server compatibility
- useful for lift-and-shift
- VNet integration and instance-level features

Online migration and offline migration must be viewed differently. Offline is simple, but causes downtime. Online requires synchronization and cutover planning. Regardless of which method you choose, you must prepare separate backup and rollback plans before migration.

After migration, only the application is not checked to see if it runs normally. View slow queries, missing indexes, collation differences, connection pool settings, timeout, deadlock, and DTU/vCore utilization.

select
  object_name(object_id) as table_name,
  name as index_name,
  type_desc
from sys.indexes
where object_id > 0
order by table_name, index_id;

In the cutover stage, changing application settings is key. The DB itself has been moved, but if the connection string, firewall rule, managed identity, secret store, and connection pool settings do not match, the application will fail.

Cutover checklist
1. source DB backup completed
2. migration validation completed
3. application maintenance window announced
4. connection string updated in secret store
5. firewall and private access confirmed
6. smoke test completed
7. monitoring dashboard watched
8. rollback condition agreed

Migration may seem like a one-time event, but it actually requires a period of observation. There are different indicators to look at for 1 hour, 24 hours, and 1 week immediately after cutover. Immediately afterward, error rates and connection failures are important, after a day or so you see slow queries and resource utilization, and after a week or so, backup/maintenance/cost patterns emerge.

Automation scripts require operational safeguards

Being able to create resources using CLI and PowerShell means that they can be easily deleted and changed. So we put some safeguards in the script.

set -euo pipefail

RESOURCE_GROUP="rg-ubuntuvmkty"
LOCATION="koreacentral"

az group show --name "$RESOURCE_GROUP" >/dev/null 2>&1 || \
  az group create --name "$RESOURCE_GROUP" --location "$LOCATION"

PowerShell also specifies error handling.

$ErrorActionPreference = "Stop"

try {
  Get-AzResourceGroup -Name $rgName
} catch {
  New-AzResourceGroup -Name $rgName -Location $location
}

Tags are also important. Tags are the simplest way to leave operational information such as cost, owner, environment, and services.

az resource tag \
  --ids "$RESOURCE_ID" \
  --tags Environment=dev Owner=platform Team=backend CostCenter=lab

As the number of resources increases, naming conventions are also needed. Prefixes such as rg-, vnet-, snet-, nsg-, pip-, nic-, and vm- can be used to quickly identify roles in the list. The naming convention is not for style, but rather a device to reduce mistakes that involve touching the wrong resource during a failure.

Cleanup

Azure CLI and PowerShell are closer to tools that explicitly leave an operational flow rather than tools that replace the portal. If you create Resource Group, VNet, Subnet, Public IP, NSG, NIC, and VM in order, dependencies between resources are revealed. When configuring Windows VM and SQL Server, network boundaries, disk separation, account permissions, backups, patches, and access paths become more important than installation success.

Azure SQL migration is in the same context. Before moving data, you need to evaluate compatibility and feature differences, select target services, prepare network and authentication paths, and plan cutover and rollback. In the end, automating cloud operations is not about knowing a lot of commands, but is more about incorporating security and recoverability into repeatable tasks.