Skip to content

README.md

Latest commit

 

History

History
257 lines (201 loc) · 9.98 KB

README.md

File metadata and controls

257 lines (201 loc) · 9.98 KB

Azure SQL Database Migration Script

PowerShell script for automated Azure SQL Database migration using geo-replication, with support for permission migration, SQL authentication management, and elastic pool error handling.

Overview

This script automates the process of migrating Azure SQL databases between servers using geo-replication. It supports cross-subscription migrations, permission migration from a source database, SQL user password management via Azure Key Vault, and automatic recovery from elastic pool storage limitations.

Prerequisites

  • PowerShell 5.1 or later (7.x recommended)
  • Azure CLI (az) installed and authenticated
  • SqlServer PowerShell Module installed: 
    Install-Module -Name SqlServer -Scope CurrentUser -Force
  • Required Azure Permissions:
    • Contributor or higher on both source and target subscriptions
    • SQL Server Contributor on both SQL servers
    • Key Vault Secrets User on the Key Vault (if using Key Vault integration)

Parameters

Required Parameters

Parameter Type Description
RsgSource String Source resource group name
RsgTarget String Target resource group name
SqlSource String Source SQL server name
SqlTarget String Target SQL server name
DbSource String Source database name
DbTarget String Target database name
SubSource String Source subscription ID
SubTarget String Target subscription ID

Optional Parameters

Parameter Type Description Default
ElasticPool String Target elastic pool name (if migrating to an elastic pool) None
MigratePermissions Switch Enable permission migration from source/custom database False
PermissionsSourceDatabase String Custom database name to retrieve permissions from (on target server) None
KeyVaultName String Azure Key Vault name containing SQL password None
KeyVaultSecretName String Secret name in Key Vault None
KeyVaultSubscription String Subscription ID where Key Vault is located (if different) None
SqlLoginName String SQL login name to update with Key Vault password None

Features

1. Geo-Replication Migration

Creates a geo-replica of the source database on the target server, then fails over to make the target primary.

2. Permission Migration

Migrates database permissions from either:

  • The source database (default when -MigratePermissions is used)
  • A custom database on the target server (when -PermissionsSourceDatabase is specified)

3. Key Vault Integration

Retrieves SQL passwords from Azure Key Vault and updates SQL user credentials during migration. Supports cross-subscription Key Vault access.

4. Elastic Pool Error Handling

Automatically detects "ElasticPoolOverStorage" errors, removes the failed database, and retries the operation.

5. Progress Tracking

Provides detailed console output with emoji indicators for each step of the migration process.

Usage Examples

Basic Migration (Same Subscription)

.\AzSQL-Migration.ps1 `
    -RsgSource "source-rsg" `
    -RsgTarget "target-rsg" `
    -SqlSource "source-sql-server" `
    -SqlTarget "target-sql-server" `
    -DbSource "SourceDB" `
    -DbTarget "TargetDB" `
    -SubSource "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -SubTarget "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

Migration with Elastic Pool

.\AzSQL-Migration.ps1 `
    -RsgSource "source-rsg" `
    -RsgTarget "target-rsg" `
    -SqlSource "source-sql-server" `
    -SqlTarget "target-sql-server" `
    -DbSource "SourceDB" `
    -DbTarget "TargetDB" `
    -SubSource "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -SubTarget "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -ElasticPool "target-elastic-pool"

Migration with Permission Migration from Source

.\AzSQL-Migration.ps1 `
    -RsgSource "source-rsg" `
    -RsgTarget "target-rsg" `
    -SqlSource "source-sql-server" `
    -SqlTarget "target-sql-server" `
    -DbSource "SourceDB" `
    -DbTarget "TargetDB" `
    -SubSource "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -SubTarget "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -MigratePermissions

Migration with Custom Permissions Source (Target Server)

.\AzSQL-Migration.ps1 `
    -RsgSource "source-rsg" `
    -RsgTarget "target-rsg" `
    -SqlSource "source-sql-server" `
    -SqlTarget "target-sql-server" `
    -DbSource "SourceDB" `
    -DbTarget "TargetDB" `
    -SubSource "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -SubTarget "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -MigratePermissions `
    -PermissionsSourceDatabase "ExistingTargetDB"

Full Migration with Key Vault Integration

.\AzSQL-Migration.ps1 `
    -RsgSource "source-rsg" `
    -RsgTarget "target-rsg" `
    -SqlSource "source-sql-server" `
    -SqlTarget "target-sql-server" `
    -DbSource "SourceDB" `
    -DbTarget "TargetDB" `
    -SubSource "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -SubTarget "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -ElasticPool "target-elastic-pool" `
    -MigratePermissions `
    -PermissionsSourceDatabase "ExistingTargetDB" `
    -KeyVaultName "your-keyvault-name" `
    -KeyVaultSecretName "your-secret-name" `
    -SqlLoginName "YourSqlLogin" `
    -KeyVaultSubscription "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

Migration Process

The script executes the following steps:

  1. Validation - Verifies source and target servers exist
  2. Geo-Replication Setup - Creates secondary database on target server
    • Automatic retry on elastic pool storage errors
  3. Failover - Fails over to make target database primary
  4. Replication Link Removal - Cleans up replication relationship
  5. Permission Migration (if enabled)
    • Retrieves permissions from source or custom database
    • Applies permissions to target database
    • Updates SQL user password (if Key Vault configured)
  6. Completion - Displays migration summary

Error Handling

Elastic Pool Storage Errors

If the script encounters an "ElasticPoolOverStorage" error during geo-replication:

  1. Automatically detects the error
  2. Deletes the partially created target database
  3. Waits 120 seconds for cleanup
  4. Retries the geo-replication operation

Permission Migration Errors

  • If permissions cannot be retrieved or applied, the script displays detailed error messages
  • Key Vault authentication errors are reported with troubleshooting guidance

Authentication

The script uses Azure CLI authentication for:

  • Azure Resource Manager operations
  • Azure SQL Database operations (via access tokens)
  • Key Vault secret retrieval

Ensure you're logged in before running:

az login
az account set --subscription "your-subscription-id"

Notes

  • Cross-Subscription Support: All parameters support different subscriptions for source, target, and Key Vault
  • Permission Source Flexibility: Use -PermissionsSourceDatabase to copy permissions from a database on the target server instead of the source
  • Idempotent Operations: Script checks for existing resources before creating

Troubleshooting

"Resource group not found"

  • Verify subscription IDs are correct
  • Ensure you have access to the specified subscriptions
  • Check resource group names for typos

"Unable to retrieve Key Vault secret"

  • Verify Key Vault name and secret name
  • Ensure you have "Get" permission on Key Vault secrets
  • If Key Vault is in a different subscription, verify -KeyVaultSubscription parameter

"ElasticPoolOverStorage" keeps failing

  • Check target elastic pool storage limits
  • Consider increasing elastic pool storage capacity
  • Verify other databases aren't consuming excessive storage

Permission Migration Fails

  • Ensure SqlServer PowerShell module is installed
  • Verify you have permission to read/write database permissions
  • Check that the permissions source database exists

Lessons Learned

Elastic Pool Storage Retention After Database Deletion

Issue: During testing, it was discovered that when the script removes a failed database from an elastic pool (as part of the ElasticPoolOverStorage retry logic), the elastic pool does not immediately release the storage allocation. The storage remains reserved even after the database is fully deleted. Running the script multiple times in quick succession can therefore accumulate phantom storage consumption within the pool, causing subsequent attempts to fail even though no databases are present.

Fix: Slightly adjust the elastic pool's max storage size (e.g. increase by 1 GB and then revert it back). This triggers Azure to recalculate the actual used storage and release the reserved-but-unused space. The change does not impact the pool or any of its databases and can be done via the portal or CLI:

# Temporarily bump storage to force recalculation (example: 500 GB → 501 GB → 500 GB)
az sql elastic-pool update `
    --resource-group "your-rsg" `
    --server "your-sql-server" `
    --name "your-elastic-pool" `
    --storage 501GB

az sql elastic-pool update `
    --resource-group "your-rsg" `
    --server "your-sql-server" `
    --name "your-elastic-pool" `
    --storage 500GB

After the revert, the pool's used storage will reflect the actual state and the migration can be retried successfully.

Version History

  • v2.0 - Added elastic pool error handling with automatic retry
  • v1.9 - Added cross-subscription Key Vault support
  • v1.8 - Added flexible permission source database option
  • v1.7 - Added Key Vault integration and SQL password management
  • v1.6 - Added permission migration feature
  • v1.0 - Initial release with basic geo-replication