Azure Database Migration Service - source database connectivity failure

# Typical DMS error messages:
# "The connectivity check to the source server failed"
# "Failed to connect to source SQL Server. Reason: A network-related or instance-specific error occurred"
# "Login failed for user 'dms_migration_user'. Reason: The server is not configured to allow remote connections"
# "TCP Provider: No connection could be made because the target machine actively refused it"

# Check migration task status via Azure CLI
az dms project task show \
  --resource-group rg-migration \
  --service-name dms-prod-westeurope \
  --project-name sql-to-azure-project \
  --name full-migration-task \
  --query '{status:properties.state, errors:properties.errors}'

# List all tasks in the migration project
az dms project task list \
  --resource-group rg-migration \
  --service-name dms-prod-westeurope \
  --project-name sql-to-azure-project \
  --query '[].{name:name, state:properties.state}' -o table

# Check DMS events in Activity Log
az monitor activity-log list \
  --resource-group rg-migration \
  --start-time $(date -u -d '2 hours ago' +%Y-%m-%dT%H:%M:%SZ) \
  --query "[?contains(resourceId,'dms-prod-westeurope')].{time:eventTimestamp, status:status.value, message:properties.statusMessage}" \
  -o table

# Find the NSG assigned to the DMS subnet
az network vnet subnet show \
  --resource-group rg-network \
  --vnet-name vnet-migration \
  --name subnet-dms \
  --query 'networkSecurityGroup.id' -o tsv

# List outbound NSG rules
az network nsg rule list \
  --resource-group rg-network \
  --nsg-name nsg-subnet-dms \
  --query "[?direction=='Outbound'].{name:name, priority:priority, access:access, destPort:destinationPortRange, destAddr:destinationAddressPrefix}" \
  -o table

# Add a rule allowing traffic to the source SQL Server (port 1433)
az network nsg rule create \
  --resource-group rg-network \
  --nsg-name nsg-subnet-dms \
  --name Allow-SQL-Source-Outbound \
  --priority 200 \
  --direction Outbound \
  --access Allow \
  --protocol Tcp \
  --source-address-prefixes "10.1.0.0/24" \
  --destination-address-prefixes "192.168.1.50" \
  --destination-port-ranges 1433 \
  --description "Allow DMS subnet to reach source SQL Server"

# If DMS connects via a peered VNet - check peering is active
az network vnet peering show \
  --resource-group rg-network \
  --vnet-name vnet-migration \
  --name peering-to-onprem-vnet \
  --query '{state:peeringState, allowForwarded:allowForwardedTraffic, useRemoteGw:useRemoteGateways}'

# From Azure CLI - check SHIR state (Integration Runtime)
az datafactory integration-runtime show \
  --resource-group rg-migration \
  --factory-name adf-migration-prod \
  --name shir-onprem-agent \
  --query '{state:properties.state, version:properties.version, autoUpdate:properties.autoUpdate}'

# If SHIR is in "NeedRegistration" state - re-register
# 1. Retrieve a new key from the portal or CLI:
az datafactory integration-runtime list-auth-key \
  --resource-group rg-migration \
  --factory-name adf-migration-prod \
  --name shir-onprem-agent \
  --query 'authKey1' -o tsv

# Check DNS resolution from the SHIR or a VM in the DMS subnet
nslookup sql-source.corp.local
# If it does not resolve - add an entry in Private DNS Zone or hosts file

# Check whether the Private DNS Zone is linked to the DMS VNet
az network private-dns zone list \
  --query "[].{name:name, vnetLinks:numberOfVirtualNetworkLinks}" -o table

az network private-dns link vnet list \
  --resource-group rg-dns \
  --zone-name corp.local \
  --query "[].{name:name, vnetId:virtualNetwork.id, registrationEnabled:registrationEnabled}" -o table

# If the link is missing - add the DMS VNet to the Private DNS Zone
az network private-dns link vnet create \
  --resource-group rg-dns \
  --zone-name corp.local \
  --name link-vnet-migration \
  --virtual-network /subscriptions/<sub-id>/resourceGroups/rg-network/providers/Microsoft.Network/virtualNetworks/vnet-migration \
  --registration-enabled false

# Restart the migration task after fixing connectivity
az dms project task restart \
  --resource-group rg-migration \
  --service-name dms-prod-westeurope \
  --project-name sql-to-azure-project \
  --name full-migration-task

# 1. Test network connectivity from the DMS subnet (from a diagnostic VM or SHIR)
# On a machine in the DMS subnet:

# 2. Check DMS task status after restart
az dms project task show \
  --resource-group rg-migration \
  --service-name dms-prod-westeurope \
  --project-name sql-to-azure-project \
  --name full-migration-task \
  --query '{state:properties.state, migrationType:properties.taskType}' -o json
# Expected: state = "Running" or "Succeeded"

# 3. Check migration progress (number of migrated tables)
az dms project task show \
  --resource-group rg-migration \
  --service-name dms-prod-westeurope \
  --project-name sql-to-azure-project \
  --name full-migration-task \
  --query 'properties.output[?resultType==`MigrationLevelOutput`].{status:migrationState, startedOn:startedOn, tables:summary.tableCount}' \
  -o table
# Expected: migrationState = "MIGRATION_IN_PROGRESS" or "MIGRATION_COMPLETED"

# 4. Verify SHIR connectivity
az datafactory integration-runtime show \
  --resource-group rg-migration \
  --factory-name adf-migration-prod \
  --name shir-onprem-agent \
  --query 'properties.state' -o tsv
# Expected: "Online"

# 5. Verify data is flowing to Azure SQL Database
az sql db show \
  --resource-group rg-azure-sql \
  --server sql-target-prod \
  --name TargetDB \
  --query '{status:status, currentSku:currentSku.name, size:maxSizeBytes}' -o json