Comprehensive SQL Server to Azure Migration Guide by SkyCore Solutions

Step 1: Conduct Initial Discovery and Assessment

The foundation of a successful migration begins with a thorough discovery and assessment of your existing SQL Server estate. This crucial step identifies compatibility issues, establishes performance baselines, and generates recommendations for the optimal Azure target. Microsoft offers tools like Azure Migrate and Data Migration Assistant (DMA) to automate this process. For comprehensive planning, assess the number and size of your databases, identify any instance-scoped dependencies (e.g., SQL Agent jobs, linked servers), and determine your acceptable business downtime.

# Install Azure Migrate module for PowerShell if not already installed
Install-Module -Name Az.Migrate -Scope CurrentUser

# Log in to Azure
Connect-AzAccount

# Create an Azure Migrate project (if one doesn't exist)
New-AzMigrateProject -Name 'SkyCoreMigrationProject' -ResourceGroupName 'SkyCoreRG' -Location 'EastUS'

# Discover on-premises SQL Servers using Azure Migrate appliance or agents
# This step typically involves deploying an Azure Migrate appliance or installing agents
# on your on-premises servers, which then collect data and send it to the Azure Migrate project.
# The following is a conceptual command as exact syntax for agent deployment is extensive and typically GUI-driven.
# For detailed SQL Server assessment, download and run the Data Migration Assistant (DMA) on the source server.
# DMA provides a detailed report on feature parity and compatibility with Azure SQL Database/Managed Instance.
# It uses the same underlying technology as the Azure Arc readiness assessment, providing recommendations.
# The DMA tool is typically run as a local executable.

Install-Module -Name Az.Migrate: Installs the necessary PowerShell module for Azure Migrate operations.

Connect-AzAccount: Establishes a connection to your Azure subscription.

New-AzMigrateProject: Creates a new Azure Migrate project in your specified resource group and location. This project will house all discovered and assessed resources.

Portal alternative: Navigate to the Azure portal, search for "Azure Migrate," create a new project, and then select "Databases" under the migration goals. Follow the prompts to set up the data collection appliance or agent.

Expected result: A detailed assessment report identifying migration blockers, compatibility issues, and performance recommendations for various Azure SQL target platforms. This report helps in sizing and choosing the correct Azure SQL service tier and deployment model.

Step 2: Choose Your Azure SQL Target Platform

Selecting the appropriate Azure SQL target platform is critical and depends on your specific workload, application requirements, and budget. Azure offers three primary SQL deployment models: Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure Virtual Machines.

• Azure SQL Database (PaaS): A fully managed Platform as a Service (PaaS) offering ideal for modern cloud applications requiring high availability, scalability, and built-in intelligent query processing. It handles most database management functions, allowing you to focus on application development. It requires application modernization to eliminate instance-level dependencies.
• Azure SQL Managed Instance (PaaS): A fully managed PaaS offering with near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine. It supports instance-scoped features like SQL Agent, CLR, cross-database queries, and distributed transactions. Ideal for "lift-and-shift" migrations where minimal application changes are desired, while still benefiting from PaaS.
• SQL Server on Azure Virtual Machines (IaaS): Infrastructure as a Service (IaaS) offering that allows you to run SQL Server within an Azure VM, providing maximum control over the operating system and SQL Server configuration. Best suited for workloads requiring full control, specific hardware configurations, or operating system access, or for applications with very tight dependencies on the underlying OS.

For Azure SQL Database and Managed Instance, you'll also choose a purchasing model and service tier:

• Purchasing Models:
  • vCore Model: Offers maximum flexibility, transparently scales compute and storage, and is the only option that supports the Azure Hybrid Benefit for cost savings by reusing existing SQL Server licenses.
  • DTU Model: Abstracts underlying compute, memory, and I/O resources into a blended Database Transaction Unit (DTU), suitable for simpler, pre-defined performance levels.
  • Serverless Model (Azure SQL Database only): Automatically scales compute resources on demand, billing per second of usage. It pauses databases during inactive periods, charging only for storage, and automatically resumes on activity. Ideal for intermittent, unpredictable workloads.
• Service Tiers (vCore model):
  • General Purpose/Standard: A balanced, budget-oriented option suitable for most database workloads, offering compute and storage for middle and lower-tier applications. Redundancy is built in at the storage layer.
  • Business Critical/Premium: Designed for high-tier applications requiring high transaction rates, low-latency I/O, and high resiliency. Includes secondary replicas for failover and read-scale workloads.
  • Hyperscale (Azure SQL Database only): Intended for all customers requiring higher performance, availability, fast backup/restore, and/or rapid storage/compute scalability. It supports a broad range of workloads from OLTP to analytics, optimized for OLTP and HTAP.

# Example: Provision an Azure SQL Database (General Purpose, vCore)
# This example creates a logical server and then a database within it.
$resourceGroupName = 'SkyCoreSQLMigrationRG'
$location = 'EastUS'
$sqlServerName = 'skycoresqlserver-001'
$sqlAdminLogin = 'sqladmin'
$sqlAdminPassword = (ConvertTo-SecureString "YourStrongPassword123!" -AsPlainText -Force)

# Create a resource group
New-AzResourceGroup -Name $resourceGroupName -Location $location

# Create a logical SQL server
New-AzSqlServer `
    -ResourceGroupName $resourceGroupName `
    -ServerName $sqlServerName `
    -Location $location `
    -ServerVersion '12.0' ` # Use '12.0' for current standard, which supports SQL Server 2019 compatibility level
    -SqlAdministratorLogin $sqlAdminLogin `
    -SqlAdministratorPassword $sqlAdminPassword

# Create an Azure SQL Database
New-AzSqlDatabase `
    -ResourceGroupName $resourceGroupName `
    -ServerName $sqlServerName `
    -DatabaseName 'SkyCoreWebAppDB' `
    -Edition 'GeneralPurpose' `
    -RequestedServiceObjectiveName 'GP_Gen5_4' ` # 4 vCores, Gen5 hardware
    -MaxSizeGB 256 `
    -ZoneRedundant $false # Set to $true for higher availability

# Example: Provision an Azure SQL Managed Instance (General Purpose, vCore)
# Requires a VNet and subnet with specific configurations.
# This assumes the VNet and subnet are already prepared as per Step 3.
$managedInstanceName = 'skycore-mi-001'
$miSku = 'GP_Gen5_4' # General Purpose, Gen5 hardware, 4 vCores
$subnetId = '/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Network/virtualNetworks/{vnetName}/subnets/{subnetName}'

# Create an Azure SQL Managed Instance
New-AzSqlInstance `
    -Name $managedInstanceName `
    -ResourceGroupName $resourceGroupName `
    -Location $location `
    -SkuName $miSku `
    -SubnetId $subnetId `
    -AdminCredentials $sqlAdminLogin `
    -AdminPassword $sqlAdminPassword `
    -LicenseType 'LicenseIncluded' # Or 'BasePrice' for Azure Hybrid Benefit
    # Add other parameters like PublicIPAddressParameters, ProxyOverride, etc., as needed

New-AzResourceGroup: Creates an Azure resource group to logically organize your resources.

New-AzSqlServer: Provisions a logical Azure SQL server, which acts as a central administrative point for multiple databases.

New-AzSqlDatabase: Creates an individual Azure SQL Database within the specified logical server, defining its edition (service tier), performance objective (vCores), and storage size.

New-AzSqlInstance: Creates an Azure SQL Managed Instance within a dedicated subnet, specifying its name, resource group, location, SKU, and administrative credentials. This command assumes the VNet and subnet are already prepared.

Portal alternative: In the Azure portal, search for "Azure SQL," then select "Create." Choose "SQL databases" or "SQL managed instances" and follow the wizard to configure your deployment model, purchasing model, and service tier.

Expected result: Your chosen Azure SQL target (logical server/database or managed instance) is provisioned and ready to receive data.

Step 3: Prepare Source and Target Environments

Before initiating the migration, both your source SQL Server environment and the target Azure SQL environment must be adequately prepared. This involves configuring network connectivity, firewall rules, and ensuring all prerequisites for your chosen migration tool are met. For Azure SQL Managed Instance, this includes setting up a dedicated virtual network (VNet) and subnet.

# Log in to Azure (if not already logged in)
Connect-AzAccount

# For Azure SQL Database: Configure Server-level Firewall Rule to allow source IP
# Replace with your source SQL Server's public IP address or a range.
$resourceGroupName = 'SkyCoreSQLMigrationRG'
$sqlServerName = 'skycoresqlserver-001'
$firewallRuleName = 'AllowSourceSQLServer'
$startIp = 'YourSourceSQLServerPublicIP' # e.g., '203.0.113.45'
$endIp = 'YourSourceSQLServerPublicIP'   # e.g., '203.0.113.45'

Set-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $sqlServerName `
    -FirewallRuleName $firewallRuleName `
    -StartIpAddress $startIp `
    -EndIpAddress $endIp

# For Azure SQL Managed Instance: Configure VNet and Subnet
# This is a prerequisite for Managed Instance creation and DMS.
# Ensure the VNet has connectivity back to your source if needed.
$vnetName = 'SkyCoreMigrationVNet'
$vnetPrefix = '10.0.0.0/16'
$subnetName = 'ManagedInstanceSubnet'
$subnetPrefix = '10.0.0.0/24' # Must be dedicated for Managed Instance
$nsgName = 'ManagedInstanceNSG'
$resourceGroupName = 'SkyCoreSQLMigrationRG'
$location = 'EastUS'

# Create VNet
New-AzVirtualNetwork `
    -Name $vnetName `
    -ResourceGroupName $resourceGroupName `
    -Location $location `
    -AddressPrefix $vnetPrefix

# Create Network Security Group (NSG) for the subnet (if not already done)
New-AzNetworkSecurityGroup `
    -Name $nsgName `
    -ResourceGroupName $resourceGroupName `
    -Location $location

# Create Subnet and associate NSG
Add-AzVirtualNetworkSubnetConfig `
    -Name $subnetName `
    -AddressPrefix $subnetPrefix `
    -VirtualNetwork $vnetName `
    -NetworkSecurityGroup $nsgName `
    -ServiceEndpoint 'Microsoft.Sql' # Required for Managed Instance
    # -Delegate 'Microsoft.Sql/managedInstances' # Required for Managed Instance, specify if not already delegated

Set-AzVirtualNetwork `
    -VirtualNetwork $vnetName

Set-AzSqlServerFirewallRule: Configures a server-level firewall rule for Azure SQL Database, allowing specified IP addresses or ranges to connect to the logical SQL server. This is essential for tools and applications accessing the database.

New-AzVirtualNetwork: Creates a new Azure Virtual Network, which provides network isolation for your Azure resources.

New-AzNetworkSecurityGroup: Creates a Network Security Group (NSG) to filter network traffic to and from Azure resources in a VNet.

Add-AzVirtualNetworkSubnetConfig: Adds or modifies a subnet configuration within a VNet. For Azure SQL Managed Instance, the subnet must be dedicated, have a specific delegation (`Microsoft.Sql/managedInstances`), and service endpoints for `Microsoft.Sql` are recommended.

Set-AzVirtualNetwork: Applies the changes made to the virtual network configuration.

Portal alternative: For Azure SQL Database, navigate to your logical SQL server in the portal, then to "Networking," and add client IP addresses to the firewall rules. For Managed Instance, navigate to "Virtual networks," select your VNet, and configure subnets and Network Security Groups (NSGs) as required.

Expected result: Secure network connectivity is established between your source SQL Server and the target Azure SQL environment, and all necessary Azure resources (VNets, subnets, NSGs, firewall rules) are configured.

Step 4: Select and Configure Your Migration Tool

The optimal migration tool depends on your target Azure SQL platform, required downtime, and specific migration needs. Azure Database Migration Service (DMS) is Microsoft's fully managed service designed for seamless migrations with minimal downtime (online migrations), supporting various database sources to Azure data platforms. SQL Server Migration Assistant (SSMA) is another tool useful for schema and data migration, especially for offline scenarios.

Azure Database Migration Service (DMS) is a powerful, fully managed service available through the Azure portal, PowerShell, and Azure CLI. It excels in enabling online migrations, meaning your source database remains operational during most of the migration process, minimizing business disruption. DMS supports offline migration for Azure SQL Database and both online/offline for Azure SQL Managed Instance and SQL Server on Azure Virtual Machine targets. It migrates schemas but notably does not migrate logins or Transparent Data Encryption (TDE) encrypted databases.

# Install Azure DMS module for PowerShell if not already installed
Install-Module -Name Az.DataMigration -Scope CurrentUser

# Log in to Azure (if not already logged in)
Connect-AzAccount

# Create an Azure Database Migration Service instance
# Choose Basic SKU for offline migrations or Standard SKU for online migrations.
$resourceGroupName = 'SkyCoreSQLMigrationRG'
$dmsServiceName = 'SkyCoreDMSService'
$location = 'EastUS'
$virtualSubnetId = '/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Network/virtualNetworks/{vnetName}/subnets/{subnetName}' # Required for DMS

New-AzDataMigrationService `
    -Name $dmsServiceName `
    -ResourceGroupName $resourceGroupName `
    -Location $location `
    -Sku 'Standard_4vCores' ` # Use 'Standard_4vCores' for online migrations, 'Basic_2vCores' for offline
    -VirtualSubnetId $virtualSubnetId

# After creating the DMS service, you would typically proceed to create a migration project and tasks.
# While the exact CLI command for 'az dms project create' or 'az dms task create' with all parameters
# is extensive and not fully detailed in the provided overview documentation, the service does support
# automation via PowerShell and Azure CLI for configuring these steps.
# The general flow involves:
# 1. Creating a migration project (specifying source/target types).
# 2. Adding a migration task (defining source/target connection info, databases to migrate, and migration type - online/offline).
# 3. Running and monitoring the migration task.
# DMS uses the same underlying technology as Azure Arc readiness assessment to guide changes.

Install-Module -Name Az.DataMigration: Installs the PowerShell module for managing Azure Database Migration Service.

New-AzDataMigrationService: Creates an instance of the Azure Database Migration Service. The SKU (e.g., 'Standard_4vCores') determines the service's capabilities, with 'Standard' tiers enabling online migrations. The -VirtualSubnetId parameter is crucial for network connectivity between DMS, your source, and your target database.

Portal alternative: In the Azure portal, search for "Azure Database Migration Service," click "Create," and fill in the required details like subscription, resource group, name, location, and SKU. After creation, navigate to the DMS instance to create new migration projects and tasks via the guided UI.

Expected result: An Azure Database Migration Service instance is provisioned. You are then ready to define and execute your migration projects and tasks within this service.

Step 5: Perform Database Schema and Data Migration

With the migration tool configured, you can now execute the migration process, transferring your database schema, data, and relevant instance-level objects to Azure. Depending on your chosen tool and acceptable downtime, you will perform either an offline or online migration.

• Offline Migration: The source database is taken offline during the data transfer. This minimizes complexity but results in downtime. Suitable for less critical applications or planned maintenance windows. Azure DMS supports offline migrations for Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs.
• Online Migration: The source database remains online and operational throughout the initial data transfer. Data changes are synchronized incrementally, and a brief cutover window is required at the end. This is ideal for critical applications requiring minimal downtime. Azure DMS supports online migrations for Azure SQL Managed Instance and SQL Server on Azure VMs.

DMS handles schema migration as part of its process, but remember its limitations regarding logins and TDE. For detailed schema objects (views, stored procedures, functions, indexes), DMS will transfer them. Transaction log rate is governed in Azure SQL Database; during migration, you might have to scale target database resources (vCores or DTUs) to ease pressure on CPU or throughput.

# While specific `az dms project create` or `az dms task create` commands
# are not fully detailed with their parameters in the provided reference,
# the general process via Azure CLI/PowerShell for an online migration with DMS
# would involve steps conceptually similar to the following:

# 1. Define source connection properties
# 2. Define target connection properties
# 3. Specify databases to migrate
# 4. Set migration type (online/offline)
# 5. Execute the migration task

# Example conceptual command to initiate a DMS migration task (not exact syntax from provided docs)
# This represents the action of configuring and starting a task within the already created DMS service.

# New-AzDataMigrationProject # (Conceptual: creates a new project within DMS)
#    -ResourceGroupName $resourceGroupName `
#    -DmsServiceName $dmsServiceName `
#    -ProjectName 'SQLServerToAzureSQLMIProject' `
#    -SourceType 'SQL' `
#    -TargetType 'SQLMI' # Example for Managed Instance

# New-AzDataMigrationTask # (Conceptual: creates and starts a task within the project)
#    -ResourceGroupName $resourceGroupName `
#    -DmsServiceName $dmsServiceName `
#    -ProjectName 'SQLServerToAzureSQLMIProject' `
#    -TaskName 'FullMigrationTask' `
#    -SourceConnectionProperties @{
#        ConnectionString = "Data Source=YourSourceSQLServer;Initial Catalog=master;Integrated Security=False;User ID=YourUser;Password=YourPassword;"
#    } `
#    -TargetConnectionProperties @{
#        ConnectionString = "Data Source=YourAzureSQLMI.database.windows.net;Initial Catalog=master;Integrated Security=False;User ID=YourMIAdmin;Password=YourMIPassword;"
#    } `
#    -DatabasesToMigrate @('SourceDB1', 'SourceDB2') `
#    -MigrationType 'Online' # Or 'Offline'
#    -DmsServiceSku 'Standard' # Must match the SKU of the DMS service instance

# Monitor the migration progress
Get-AzDataMigrationTask `
    -ResourceGroupName $resourceGroupName `
    -DmsServiceName $dmsServiceName `
    -ProjectName 'SQLServerToAzureSQLMIProject' `
    -TaskName 'FullMigrationTask'

New-AzDataMigrationProject (Conceptual): This command would define a migration project within your DMS instance, specifying the source and target database types. This is the logical container for migration tasks.

New-AzDataMigrationTask (Conceptual): This command would create and initiate the actual migration task within a project. It involves providing connection strings for both source and target, specifying the databases to migrate, and setting the migration type (online or offline).

Get-AzDataMigrationTask: Retrieves the status and details of an ongoing or completed migration task, allowing you to monitor its progress and identify any issues.

Portal alternative: In the Azure portal, navigate to your Azure Database Migration Service instance, click "New Migration Project," and follow the guided wizard. You'll specify source and target server details, select databases, and configure migration settings. The portal provides real-time progress updates and detailed logs.

Expected result: Your selected databases are successfully migrated, including schema and data, to the target Azure SQL environment. For online migrations, data synchronization continues until the final cutover.

Step 6: Validate and Cut Over Applications

After the migration completes, comprehensive validation is paramount to ensure data integrity and application functionality before redirecting production traffic. This step involves thorough testing, performance benchmarking, and a planned cutover strategy.

# No direct CLI commands for application validation or cutover exist,
# as these are application-specific and procedural.
# However, you can use CLI to verify database connectivity and status.

# Verify Azure SQL Database connectivity (example for Azure SQL Database)
# This assumes you have sqlcmd or Azure Data Studio configured.
# Install-Module -Name dbatools # (If you use dbatools for advanced checks)
# Connect-DbaInstance -SqlInstance "skycoresqlserver-001.database.windows.net" -SqlCredential (Get-Credential)

# Use `az sql db show` to confirm database status
az sql db show `
    --resource-group 'SkyCoreSQLMigrationRG' `
    --server 'skycoresqlserver-001' `
    --name 'SkyCoreWebAppDB' `
    --query '{Name:name, Status:status, ServiceObjective:currentServiceObjectiveName}'

# During cutover, update application connection strings.
# For web apps in Azure App Service:
az webapp config connection-string set `
    --resource-group 'YourWebAppRG' `
    --name 'YourWebAppName' `
    --connection-string-type SQLAzure `
    --settings "DefaultConnection=Server=tcp:skycoresqlserver-001.database.windows.net,1433;Database=SkyCoreWebAppDB;User ID=sqladmin;Password=YourStrongPassword123!;Encrypt=True;Connection Timeout=30;"

az sql db show: Retrieves detailed information about an Azure SQL Database, including its name, status (e.g., Online), and current service objective, confirming its operational state.

az webapp config connection-string set: Updates the connection strings for an Azure App Service application, pointing it to the newly migrated Azure SQL Database. This is the crucial step for redirecting application traffic.

Portal alternative: For validation, use SQL Server Management Studio (SSMS) or Azure Data Studio to connect to the migrated database, run data integrity checks (e.g., row counts, checksums), and execute critical application queries. For cutover, navigate to your application service (e.g., Azure App Service), go to "Configuration," and update the database connection strings. Perform thorough UAT (User Acceptance Testing) immediately after cutover.

Expected result: All data is verified as accurately transferred. Applications connect successfully to the Azure SQL target and function as expected, with performance meeting or exceeding baselines. Production traffic is safely redirected to the new Azure environment.

Step 7: Implement Post-Migration Optimization and Hardening

The migration journey doesn't end at cutover. Post-migration, it's essential to optimize performance, configure robust monitoring, and implement advanced security best practices to fully leverage Azure SQL's capabilities and ensure a secure, efficient environment.

# Log in to Azure (if not already logged in)
Connect-AzAccount

# 1. Configure Automatic Tuning (Azure SQL Database/Managed Instance)
# This enables automatic plan correction and index management.
az sql db update `
    --resource-group 'SkyCoreSQLMigrationRG' `
    --server 'skycoresqlserver-001' `
    --name 'SkyCoreWebAppDB' `
    --set automaticTuning.options.forceLastGoodPlan.state='On' `
    --set automaticTuning.options.createIndexes.state='On' `
    --set automaticTuning.options.dropIndexes.state='On'

# 2. Enable Threat Detection / Advanced Data Security
# This includes SQL vulnerability assessment, advanced threat protection, and data discovery & classification.
az sql db threat-detection update `
    --resource-group 'SkyCoreSQLMigrationRG' `
    --server 'skycoresqlserver-001' `
    --name 'SkyCoreWebAppDB' `
    --email-addresses 'securityadmin@skycore.com' `
    --state Enabled `
    --storage-account 'yoursecuritystorageaccount' # Requires a storage account for audit logs

# 3. Enable Transparent Data Encryption (TDE) (if not already enabled)
# This is for data at rest encryption. Requires careful key management.
az sql db tde set `
    --resource-group 'SkyCoreSQLMigrationRG' `
    --server 'skycoresqlserver-001' `
    --name 'SkyCoreWebAppDB' `
    --status Enabled

# 4. Integrate with Azure Active Directory authentication
# This simplifies identity management and enhances security.
# First, set the AAD admin for the logical server:
az sql server ad-admin create `
    --resource-group 'SkyCoreSQLMigrationRG' `
    --server 'skycoresqlserver-001' `
    --display-name 'AzureADAdmin' `
    --object-id 'your-aad-admin-object-id' # Object ID of an AAD user/group

# Then, create AAD users within the database.
# This requires connecting with the AAD admin and running T-SQL:
# CREATE USER [username@yourdomain.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo;
# ALTER ROLE db_datareader ADD MEMBER [username@yourdomain.com];

az sql db update --set automaticTuning.options...: Configures automatic tuning features for an Azure SQL Database, which can autonomously improve query performance by forcing good plans and managing indexes.

az sql db threat-detection update: Enables Advanced Data Security (including Threat Detection, Vulnerability Assessment) for your database, specifying an email address for alerts and a storage account for audit logs.

az sql db tde set: Activates Transparent Data Encryption (TDE) for the database, encrypting data at rest. This should be a priority for sensitive data.

az sql server ad-admin create: Sets an Azure Active Directory (AAD) administrator for your logical SQL server or Managed Instance, enabling AAD authentication for database access.

Portal alternative: For performance, navigate to your Azure SQL Database/Managed Instance, then "Intelligent Performance" -> "Automatic tuning" to enable and configure options. For security, go to "Security" -> "Advanced Data Security" to enable it, and "Transparent Data Encryption" to manage TDE. For AAD, navigate to "Azure Active Directory" under your logical server/managed instance to set up an admin.

Expected result: Your Azure SQL environment is continuously optimized for performance, robustly monitored for anomalies, and fortified with advanced security measures, aligning with SkyCore Solutions' best practices.