AzSQL-Migration.ps1

<#
.SYNOPSIS
    Azure SQL Database replication and migration script using az cli.

.DESCRIPTION
    Creates a geo-replica, deletes the replication link, handles database renaming 
    with _old suffix, and migrates permissions from source to target database.

.PARAMETER RsgSource
    Source resource group name

.PARAMETER RsgTarget
    Target resource group name

.PARAMETER SqlSource
    Source SQL server name

.PARAMETER SqlTarget
    Target SQL server name

.PARAMETER DbSource
    Source database name

.PARAMETER DbTarget
    Target database name (final name on target server)

.PARAMETER SubSource
    Source subscription ID or name

.PARAMETER SubTarget
    Target subscription ID or name

.PARAMETER ElasticPool
    Optional: Target elastic pool name

.PARAMETER MigratePermissions
    Switch: Migrate database permissions from source to target

.PARAMETER PermissionsSourceDatabase
    Optional: Database to retrieve permissions from (defaults to source database).
    Use this when you want to get permissions from a different database than the source.

.PARAMETER KeyVaultName
    Optional: Azure Key Vault name for retrieving SQL credentials (default: actt01-data-sdc-orch-kv)

.PARAMETER KeyVaultSecretName
    Optional: Key Vault secret name containing the SQL password (default: act-tg-azure)

.PARAMETER KeyVaultSubscription
    Optional: Subscription ID or name where the Key Vault is located (if different from source/target subscriptions)

.PARAMETER SqlLoginName
    Optional: SQL Login/User name to set password for (default: Azure)

.EXAMPLE
    .\AzSQL-Migration.ps1 `
        -RsgSource "source-resource-group" `
        -RsgTarget "target-resource-group" `
        -SqlSource "source-sql-server" `
        -SqlTarget "target-sql-server" `
        -DbSource "SourceDatabase" `
        -DbTarget "TargetDatabase_DEV" `
        -SubSource "source-subscription-name" `
        -SubTarget "00000000-0000-0000-0000-000000000000"

.EXAMPLE
    .\AzSQL-Migration.ps1 `
        -RsgSource "source-resource-group" `
        -RsgTarget "target-resource-group" `
        -SqlSource "source-sql-server" `
        -SqlTarget "target-sql-server" `
        -DbSource "SourceDatabase" `
        -DbTarget "TargetDatabase_PRD" `
        -SubSource "source-subscription-name" `
        -SubTarget "00000000-0000-0000-0000-000000000000" `
        -ElasticPool "target-elastic-pool" `
        -MigratePermissions

.EXAMPLE
    .\AzSQL-Migration.ps1 `
        -RsgSource "source-resource-group" `
        -RsgTarget "target-resource-group" `
        -SqlSource "source-sql-server" `
        -SqlTarget "target-sql-server" `
        -DbSource "SourceDatabase" `
        -DbTarget "TargetDatabase_PRD" `
        -SubSource "source-subscription-name" `
        -SubTarget "00000000-0000-0000-0000-000000000000" `
        -ElasticPool "target-elastic-pool" `
        -MigratePermissions `
        -PermissionsSourceDatabase "PermissionsDatabase" `
        -KeyVaultName "my-keyvault" `
        -KeyVaultSecretName "sql-password-secret" `
        -SqlLoginName "SqlLoginName"
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true, HelpMessage="Source resource group name")]
    [string]$RsgSource,
    
    [Parameter(Mandatory=$true, HelpMessage="Target resource group name")]
    [string]$RsgTarget,
    
    [Parameter(Mandatory=$true, HelpMessage="Source SQL server name")]
    [string]$SqlSource,
    
    [Parameter(Mandatory=$true, HelpMessage="Target SQL server name")]
    [string]$SqlTarget,
    
    [Parameter(Mandatory=$true, HelpMessage="Source database name")]
    [string]$DbSource,
    
    [Parameter(Mandatory=$true, HelpMessage="Target database name (final name)")]
    [string]$DbTarget,
    
    [Parameter(Mandatory=$true, HelpMessage="Source subscription ID or name")]
    [string]$SubSource,
    
    [Parameter(Mandatory=$true, HelpMessage="Target subscription ID or name")]
    [string]$SubTarget,
    
    [Parameter(Mandatory=$false, HelpMessage="Target elastic pool name")]
    [string]$ElasticPool = "",
    
    [Parameter(Mandatory=$false, HelpMessage="Migrate permissions from source to target")]
    [switch]$MigratePermissions,
    
    [Parameter(Mandatory=$false, HelpMessage="Database to retrieve permissions from (defaults to source database)")]
    [string]$PermissionsSourceDatabase = "",
    
    [Parameter(Mandatory=$false, HelpMessage="Key Vault name for retrieving SQL credentials")]
    [string]$KeyVaultName = "",
    
    [Parameter(Mandatory=$false, HelpMessage="Key Vault secret name for SQL password")]
    [string]$KeyVaultSecretName = "",
    
    [Parameter(Mandatory=$false, HelpMessage="Key Vault subscription ID or name (if different from source/target)")]
    [string]$KeyVaultSubscription = "",
    
    [Parameter(Mandatory=$false, HelpMessage="SQL Login/User name to update password for")]
    [string]$SqlLoginName = "Azure"
)

$ErrorActionPreference = "Stop"

#region Helper Functions

function Get-KeyVaultSecret {
    <#
    .SYNOPSIS
        Retrieves a secret from Azure Key Vault
    #>
    param(
        [string]$VaultName,
        [string]$SecretName,
        [string]$Subscription = ""
    )
    
    Write-Log "Retrieving secret '$SecretName' from Key Vault '$VaultName'..." "INFO"
    
    try {
        $kvArgs = @(
            "keyvault", "secret", "show",
            "--name", $SecretName,
            "--vault-name", $VaultName,
            "--query", "value",
            "-o", "tsv",
            "--only-show-errors"
        )
        
        if (-not [string]::IsNullOrWhiteSpace($Subscription)) {
            $kvArgs += "--subscription", $Subscription
            Write-Log "Using subscription: $Subscription" "INFO"
        }
        
        $secret = & az @kvArgs
        
        if ($LASTEXITCODE -ne 0 -or [string]::IsNullOrWhiteSpace($secret)) {
            throw "Failed to retrieve secret from Key Vault"
        }
        
        Write-Log "Successfully retrieved secret from Key Vault" "SUCCESS"
        return $secret
    }
    catch {
        Write-Log "Failed to retrieve secret '$SecretName' from Key Vault '$VaultName': $_" "ERROR"
        throw
    }
}

function Set-SqlUserPassword {
    <#
    .SYNOPSIS
        Sets or updates the password for a SQL user/login
    #>
    param(
        [string]$Server,
        [string]$Database,
        [string]$LoginName,
        [string]$Password
    )
    
    Write-Log "Setting password for SQL user '$LoginName'..." "INFO"
    
    $serverFqdn = "$Server.database.windows.net"
    $accessToken = az account get-access-token --resource https://database.windows.net/ --query accessToken -o tsv
    
    # Try to alter the user password first
    $alterUserQuery = @"
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = '$LoginName')
BEGIN
    ALTER USER [$LoginName] WITH PASSWORD = '$Password'
    PRINT 'Password updated for existing user'
END
ELSE
BEGIN
    CREATE USER [$LoginName] WITH PASSWORD = '$Password'
    PRINT 'User created with password'
END
"@
    
    try {
        $result = Invoke-Sqlcmd `
            -ServerInstance $serverFqdn `
            -Database $Database `
            -Query $alterUserQuery `
            -AccessToken $accessToken `
            -ErrorAction Stop
        
        Write-Log "Successfully set password for user '$LoginName'" "SUCCESS"
        return $true
    }
    catch {
        Write-Log "Failed to set password for user '$LoginName': $_" "WARNING"
        Write-Log "Attempting to drop and recreate user..." "INFO"
        
        # Try to drop and recreate the user
        $recreateUserQuery = @"
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = '$LoginName')
    DROP USER [$LoginName]

CREATE USER [$LoginName] WITH PASSWORD = '$Password'
"@
        
        try {
            Invoke-Sqlcmd `
                -ServerInstance $serverFqdn `
                -Database $Database `
                -Query $recreateUserQuery `
                -AccessToken $accessToken `
                -ErrorAction Stop
            
            Write-Log "Successfully recreated user '$LoginName' with new password" "SUCCESS"
            return $true
        }
        catch {
            Write-Log "Failed to recreate user '$LoginName': $_" "ERROR"
            return $false
        }
    }
}

function Write-Log {
    <#
    .SYNOPSIS
        Writes formatted log messages with timestamp and color coding
    #>
    param(
        [string]$Message,
        [ValidateSet("INFO", "SUCCESS", "WARNING", "ERROR")]
        [string]$Level = "INFO"
    )
    
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $color = switch($Level) {
        "ERROR"   { "Red" }
        "WARNING" { "Yellow" }
        "SUCCESS" { "Green" }
        default   { "White" }
    }
    
    $output = "[$timestamp] [$Level] $Message"
    
    if ($Level -eq "ERROR") {
        Write-Host $output -ForegroundColor $color -ErrorAction Continue
    } else {
        Write-Host $output -ForegroundColor $color
    }
}

function Test-DatabaseExists {
    <#
    .SYNOPSIS
        Checks if a database exists on a SQL server
    #>
    param(
        [string]$ResourceGroup,
        [string]$Server,
        [string]$Database,
        [string]$Subscription
    )
    
    try {
        $null = az sql db show `
            --resource-group $ResourceGroup `
            --server $Server `
            --name $Database `
            --subscription $Subscription `
            --output none 2>$null
        return $LASTEXITCODE -eq 0
    }
    catch {
        return $false
    }
}

function Get-DatabasePermissions {
    <#
    .SYNOPSIS
        Extracts all database users and their role memberships using SqlServer module
    #>
    param(
        [string]$ResourceGroup,
        [string]$Server,
        [string]$Database,
        [string]$Subscription
    )
    
    Write-Log "Extracting permissions from source database..." "INFO"
    
    # Check if SqlServer module is installed
    if (-not (Get-Module -ListAvailable -Name SqlServer)) {
        Write-Log "SqlServer PowerShell module not found. Installing..." "INFO"
        try {
            Install-Module -Name SqlServer -Scope CurrentUser -Force -AllowClobber -ErrorAction Stop
            Write-Log "SqlServer module installed successfully" "SUCCESS"
        }
        catch {
            Write-Log "Failed to install SqlServer module. Permission migration requires this module." "ERROR"
            Write-Log "Please run: Install-Module -Name SqlServer -Scope CurrentUser -Force" "WARNING"
            throw "SqlServer module installation failed"
        }
    }
    
    Import-Module SqlServer -ErrorAction Stop
    
    $query = @"
SELECT 
    dp.name AS UserName,
    dp.type_desc AS UserType,
    STRING_AGG(drole.name, ',') AS Roles
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
LEFT JOIN sys.database_principals drole ON drm.role_principal_id = drole.principal_id
WHERE dp.type IN ('S', 'U', 'G', 'E', 'X')
  AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'public')
  AND dp.name NOT LIKE '##%'
GROUP BY dp.name, dp.type_desc
ORDER BY dp.name
"@
    
    try {
        # Use Azure AD authentication with Invoke-Sqlcmd
        $serverFqdn = "$Server.database.windows.net"
        
        Write-Log "Connecting to $serverFqdn/$Database using Azure AD authentication..." "INFO"
        
        $result = Invoke-Sqlcmd `
            -ServerInstance $serverFqdn `
            -Database $Database `
            -Query $query `
            -AccessToken (az account get-access-token --resource https://database.windows.net/ --query accessToken -o tsv) `
            -ErrorAction Stop
        
        return $result
    }
    catch {
        Write-Log "Failed to extract permissions: $_" "ERROR"
        throw
    }
}

function Set-DatabasePermissions {
    <#
    .SYNOPSIS
        Applies user permissions and role memberships to target database
    #>
    param(
        [string]$ResourceGroup,
        [string]$Server,
        [string]$Database,
        [string]$Subscription,
        [object[]]$Permissions
    )
    
    Write-Log "Applying permissions to target database..." "INFO"
    
    $serverFqdn = "$Server.database.windows.net"
    $accessToken = az account get-access-token --resource https://database.windows.net/ --query accessToken -o tsv
    
    foreach ($user in $Permissions) {
        $userName = $user.UserName
        $userType = $user.UserType
        $roles = if ($user.Roles) { $user.Roles -split ',' } else { @() }
        
        Write-Log "Processing user: $userName" "INFO"
        
        # Create user if it's an Azure AD (External) user
        if ($userType -eq "EXTERNAL_USER") {
            $createUserQuery = "IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$userName') CREATE USER [$userName] FROM EXTERNAL PROVIDER"
            
            try {
                Invoke-Sqlcmd `
                    -ServerInstance $serverFqdn `
                    -Database $Database `
                    -Query $createUserQuery `
                    -AccessToken $accessToken `
                    -ErrorAction Stop
                
                Write-Log "Created user: $userName" "SUCCESS"
            }
            catch {
                Write-Log "Failed to create user $userName : $_" "WARNING"
            }
        }
        
        # Assign role memberships
        foreach ($role in $roles) {
            if ([string]::IsNullOrWhiteSpace($role)) { continue }
            
            $roleQuery = "ALTER ROLE [$role] ADD MEMBER [$userName]"
            
            try {
                Invoke-Sqlcmd `
                    -ServerInstance $serverFqdn `
                    -Database $Database `
                    -Query $roleQuery `
                    -AccessToken $accessToken `
                    -ErrorAction Stop
                
                Write-Log "Assigned role '$role' to $userName" "SUCCESS"
            }
            catch {
                Write-Log "Failed to assign role '$role' to $userName : $_" "WARNING"
            }
        }
    }
}

#endregion

#region Main Script

try {
    Write-Log "==========================================" "INFO"
    Write-Log "Azure SQL Database Migration Process" "INFO"
    Write-Log "==========================================" "INFO"
    Write-Log "Source: $SqlSource/$DbSource (Subscription: $SubSource)" "INFO"
    Write-Log "Target: $SqlTarget/$DbTarget (Subscription: $SubTarget)" "INFO"
    Write-Log "" "INFO"
    
    # Step 1: Store permissions if requested
    $sourcePermissions = $null
    if ($MigratePermissions) {
        # Determine which database and server to get permissions from
        if ([string]::IsNullOrWhiteSpace($PermissionsSourceDatabase)) {
            # Use source database on source server
            $permissionsDb = $DbSource
            $permissionsServer = $SqlSource
            $permissionsRsg = $RsgSource
            $permissionsSub = $SubSource
        }
        else {
            # Use specified database on target server
            $permissionsDb = $PermissionsSourceDatabase
            $permissionsServer = $SqlTarget
            $permissionsRsg = $RsgTarget
            $permissionsSub = $SubTarget
        }
        
        Write-Log "Capturing database permissions from $permissionsServer/$permissionsDb..." "INFO"
        try {
            $sourcePermissions = Get-DatabasePermissions `
                -ResourceGroup $permissionsRsg `
                -Server $permissionsServer `
                -Database $permissionsDb `
                -Subscription $permissionsSub
            Write-Log "Successfully captured permissions from $permissionsDb" "SUCCESS"
        }
        catch {
            Write-Log "Failed to capture permissions: $_" "WARNING"
            Write-Log "Continuing without permissions migration..." "WARNING"
            $sourcePermissions = $null
        }
    }
    
    # Step 2: Create geo-replica
    Write-Log "Creating geo-replica on target server..." "INFO"
    
    $replicaArgs = @(
        "sql", "db", "replica", "create",
        "--resource-group", $RsgSource,
        "--server", $SqlSource,
        "--name", $DbSource,
        "--partner-server", $SqlTarget,
        "--partner-resource-group", $RsgTarget,
        "--subscription", $SubSource,
        "--partner-sub-id", $SubTarget
    )
    
    if (-not [string]::IsNullOrWhiteSpace($ElasticPool)) {
        $replicaArgs += "--elastic-pool", $ElasticPool
        Write-Log "Using Elastic Pool: $ElasticPool" "INFO"
    }
    
    $replicaOutput = & az @replicaArgs 2>&1 | Out-String
    
    if ($LASTEXITCODE -ne 0) {
        # Check if error is due to elastic pool storage limit
        if ($replicaOutput -match "ElasticPoolOverStorage") {
            Write-Log "Elastic pool storage limit reached. Checking for existing database to remove..." "WARNING"
            
            # Switch to target subscription to check and delete database
            az account set --subscription $SubTarget
            
            if (Test-DatabaseExists -ResourceGroup $RsgTarget -Server $SqlTarget -Database $DbTarget -Subscription $SubTarget) {
                Write-Log "Deleting existing database '$DbTarget' to free up storage..." "WARNING"
                az sql db delete `
                    --resource-group $RsgTarget `
                    --server $SqlTarget `
                    --name $DbTarget `
                    --subscription $SubTarget `
                    --yes
                
                if ($LASTEXITCODE -eq 0) {
                    Write-Log "Database '$DbTarget' deleted successfully" "SUCCESS"
                    Write-Log "Waiting 120 seconds for storage to be released..." "INFO"
                    Start-Sleep -Seconds 120
                    
                    # Switch back to source subscription and retry replica creation
                    az account set --subscription $SubSource
                    Write-Log "Retrying geo-replica creation..." "INFO"
                    
                    $replicaOutput = & az @replicaArgs 2>&1 | Out-String
                    
                    if ($LASTEXITCODE -ne 0) {
                        Write-Log "Replica creation output: $replicaOutput" "ERROR"
                        throw "Failed to create geo-replica after freeing storage"
                    }
                } else {
                    throw "Failed to delete database '$DbTarget' to free storage"
                }
            } else {
                Write-Log "Database '$DbTarget' does not exist. Cannot free storage." "ERROR"
                Write-Log "Replica creation output: $replicaOutput" "ERROR"
                throw "Elastic pool storage limit reached and no database to remove"
            }
            
            # Switch back to source subscription
            az account set --subscription $SubSource
        } else {
            Write-Log "Replica creation output: $replicaOutput" "ERROR"
            throw "Failed to create geo-replica"
        }
    }
    Write-Log "Geo-replica created successfully" "SUCCESS"
    
    # Wait for replication to sync
    Write-Log "Waiting for replication to synchronize (30 seconds)..." "INFO"
    Start-Sleep -Seconds 30
    
    # Step 3: Delete replication link (promotes secondary to standalone)
    Write-Log "Deleting replication link to promote secondary database..." "INFO"
    
    az sql db replica delete-link `
        --name $DbSource `
        --resource-group $RsgSource `
        --server $SqlSource `
        --partner-server $SqlTarget `
        --partner-resource-group $RsgTarget `
        --subscription $SubSource `
        --yes
    
    if ($LASTEXITCODE -ne 0) {
        throw "Failed to delete replication link"
    }
    Write-Log "Replication link deleted successfully" "SUCCESS"
    
    # Small delay to ensure link is fully removed
    Start-Sleep -Seconds 10
    
    # Step 4: Handle database renaming on target
    Write-Log "Handling database renaming on target server..." "INFO"
    
    # Switch to target subscription
    az account set --subscription $SubTarget
    
    $oldDbName = ${DbTarget}
    
    # Check if target database name already exists
    if (Test-DatabaseExists -ResourceGroup $RsgTarget -Server $SqlTarget -Database $DbTarget -Subscription $SubTarget) {
        Write-Log "Database '$DbTarget' already exists on target server" "INFO"
        
        # Check if _old version exists and delete it
        if (Test-DatabaseExists -ResourceGroup $RsgTarget -Server $SqlTarget -Database $oldDbName -Subscription $SubTarget) {
            Write-Log "Deleting existing '$oldDbName'..." "INFO"
            az sql db delete `
                --resource-group $RsgTarget `
                --server $SqlTarget `
                --name $oldDbName `
                --subscription $SubTarget `
                --yes
            Start-Sleep -Seconds 5
        }
        
##        # Rename current database to _old
##        Write-Log "Renaming current '$DbTarget' to '$oldDbName'..." "INFO"
##        az sql db rename `
##            --resource-group $RsgTarget `
##            --server $SqlTarget `
##            --name $DbTarget `
##            --new-name $oldDbName `
##            --subscription $SubTarget
##        Write-Log "Successfully renamed database to '$oldDbName'" "SUCCESS"
    }
    
    # Rename the replicated database to the target name
    if ($DbSource -ne $DbTarget) {
        Write-Log "Renaming replicated database from '$DbSource' to '$DbTarget'..." "INFO"
        az sql db rename `
            --resource-group $RsgTarget `
            --server $SqlTarget `
            --name $DbSource `
            --new-name $DbTarget `
            --subscription $SubTarget
        Write-Log "Successfully renamed to '$DbTarget'" "SUCCESS"
    }
    else {
        Write-Log "Database already has the target name: $DbTarget" "INFO"
    }
    
    # Step 5: Apply permissions to target database
    if ($MigratePermissions -and $sourcePermissions) {
        Write-Log "Migrating permissions to target database..." "INFO"
        try {
            Set-DatabasePermissions `
                -ResourceGroup $RsgTarget `
                -Server $SqlTarget `
                -Database $DbTarget `
                -Subscription $SubTarget `
                -Permissions $sourcePermissions
            Write-Log "Permissions migrated successfully" "SUCCESS"
        }
        catch {
            Write-Log "Some permissions failed to migrate: $_" "WARNING"
        }
        
        # Step 5b: Set SQL user password from Key Vault if specified
        if (-not [string]::IsNullOrWhiteSpace($KeyVaultName) -and -not [string]::IsNullOrWhiteSpace($SqlLoginName)) {
            Write-Log "Updating password for SQL user '$SqlLoginName' from Key Vault..." "INFO"
            try {
                $kvSecretParams = @{
                    VaultName = $KeyVaultName
                    SecretName = $KeyVaultSecretName
                }
                
                if (-not [string]::IsNullOrWhiteSpace($KeyVaultSubscription)) {
                    $kvSecretParams.Subscription = $KeyVaultSubscription
                }
                
                $sqlPassword = Get-KeyVaultSecret @kvSecretParams
                $passwordSet = Set-SqlUserPassword `
                    -Server $SqlTarget `
                    -Database $DbTarget `
                    -LoginName $SqlLoginName `
                    -Password $sqlPassword
                
                if ($passwordSet) {
                    Write-Log "SQL user password updated successfully" "SUCCESS"
                } else {
                    Write-Log "Failed to set SQL user password" "WARNING"
                }
            }
            catch {
                Write-Log "Failed to update SQL user password: $_" "WARNING"
            }
        }
    }
    
    # Step 6: Summary
    Write-Log "==========================================" "SUCCESS"
    Write-Log "Migration completed successfully!" "SUCCESS"
    Write-Log "==========================================" "SUCCESS"
    Write-Log "Target Database: $SqlTarget/$DbTarget" "INFO"
    
    if (Test-DatabaseExists -ResourceGroup $RsgTarget -Server $SqlTarget -Database $oldDbName -Subscription $SubTarget) {
        Write-Log "Previous Database: $SqlTarget/$oldDbName (kept as backup)" "INFO"
        Write-Log "Remember to delete '$oldDbName' once you confirm the new database is working correctly" "INFO"
    }
    
    Write-Log "You can now update connection strings to point to: $SqlTarget/$DbTarget" "INFO"
    
    # Switch back to source subscription
    az account set --subscription $SubSource
    
    Write-Log "Script completed successfully!" "SUCCESS"
}
catch {
    Write-Log "Script failed with error: $_" "ERROR"
    Write-Log $_.ScriptStackTrace "ERROR"
    
    # Try to switch back to source subscription
    try {
        az account set --subscription $SubSource
    }
    catch {
        # Ignore errors when switching back
    }
    
    exit 1
}

#endregion