AzureSQLMigration

KO | EN

gitlite — search

#javascript #ai #typescript #golang #go #deep-learning #linux #windows #awesome-list #vue #pytorch #security #ios #open-source

AzureSQLMigration

(★ 5)

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

AzureSQLMigration 최신버젼 다운로드

최종 버전 다운로드 (.zip)

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# 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:
  ```powershell
  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)

```powershell
.\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

```powershell
.\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

```powershell
.\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)

```powershell
.\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

```powershell
.\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:
```powershell
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:

```powershell
# 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

AzureSQLMigration

AzureSQLMigration 최신버젼 다운로드

오늘의 추천 프로젝트

Was this content helpful?

Recent Feedback

AzureSQLMigration

AzureSQLMigration 최신버젼 다운로드

오늘의 추천 프로젝트

Was this content helpful?

Recent Feedback

Download README