Complete Step-by-Step Guide for SQL Server to Azure Migration
Moving a SQL Server database from an on-premises/local server to Microsoft Azure is a common requirement when organizations want to modernize their infrastructure, improve scalability, increase availability, and reduce the operational effort involved in maintaining physical database servers.
In this article, we will walk through the complete migration process:
Local SQL Server → Azure Storage → Azure SQL → Application Connection
We will also discuss the different Azure SQL options, backup and restore, Azure Database Migration Service (DMS), validation, application connection-string changes, and post-migration activities.
1. What Are We Migrating?
Assume we currently have the following environment:
LOCAL / ON-PREMISES SERVER
|
| SQL Server
|
+-- CustomerDB
+-- OrderDB
+-- ProductDB
|
+-- ASP.NET Core Web API
+-- Angular ApplicationWe want to move the database to Azure:
AZURE CLOUD
|
+---------+---------+
| |
Azure SQL Azure Storage
|
CustomerDB
OrderDB
ProductDB
|
ASP.NET Core API
|
AngularThe migration does not necessarily mean simply copying the .mdf and .ldf files to Azure.
The correct migration approach depends on the Azure target you choose.
2. Azure SQL Deployment Options
Before migrating, the first and most important decision is:
Where should the SQL Server database run in Azure?
There are three major options.
Option 1 – Azure SQL Database
Azure SQL Database is a fully managed database platform.
Microsoft manages many infrastructure tasks such as:
Hardware
Operating system
Database patching
Backups
High availability
Infrastructure maintenance
It is generally the best choice when your application can work with a database-as-a-service model.
ASP.NET Core API
|
v
Azure SQL Database
|
v
Managed by AzureHowever, Azure SQL Database is not identical to a traditional SQL Server instance, so compatibility should be assessed before migration.
3. Option 2 – Azure SQL Managed Instance
Azure SQL Managed Instance provides much greater compatibility with traditional SQL Server.
It is particularly useful when your existing application depends on SQL Server instance-level capabilities or you want a more lift-and-shift-oriented migration.
For example:
ON-PREMISES
SQL Server Instance
|
+-- Database A
+-- Database B
+-- Database C
|
+-- Logins
+-- SQL Agent Jobs
+-- Cross-database functionalitycan be moved toward:
AZURE
SQL Managed Instance
|
+-- Database A
+-- Database B
+-- Database CMicrosoft describes SQL Managed Instance as a suitable target when you need maximum compatibility and when your applications depend on instance-level or cross-database functionality. (Microsoft Learn)
4. Option 3 – SQL Server on Azure Virtual Machine
This is the closest approach to your existing environment.
You create an Azure VM and install/run SQL Server on it.
Azure
|
+-- Virtual Network
|
+-- SQL Server VM
|
+-- SQL Server
|
+-- DatabaseThis approach is useful when you need very high compatibility with the existing SQL Server environment.
It is essentially a lift-and-shift approach.
Microsoft's current migration tooling supports SQL Server migrations to SQL Server on Azure VMs using Azure Database Migration Service. (Microsoft Learn)
5. Which Azure Option Should You Choose?
A simplified decision table is:
| Requirement | Recommended Target |
|---|---|
| Fully managed database | Azure SQL Database |
| High SQL Server compatibility | Azure SQL Managed Instance |
| Almost identical SQL Server environment | SQL Server on Azure VM |
| Cross-database functionality | Managed Instance / Azure VM |
| SQL Agent and instance-level dependencies | Managed Instance / Azure VM |
| Modern cloud-native application | Azure SQL Database |
| Lift-and-shift migration | Managed Instance / Azure VM |
Do not choose the target only because it is easy to create.
First perform a compatibility and dependency assessment.
6. Migration Architecture
A typical migration architecture looks like this:
ON-PREMISES
+----------------+
| Local SQL |
| Server |
| |
| CustomerDB |
| OrderDB |
+-------+--------+
|
| Backup
v
+---------------+
| Azure Storage |
| Blob |
+-------+-------+
|
v
+---------------+
| Azure Database|
| Migration |
| Service |
+-------+-------+
|
v
+---------------+
| Azure SQL |
| Managed |
| Instance / |
| SQL Database |
+-------+-------+
|
v
ASP.NET Core API
|
v
Angular AppFor some migration scenarios, Azure DMS uses database backup files stored in Azure Storage. (Microsoft Learn)
7. Step 1 – Analyze the Existing SQL Server
Before moving anything, collect information about your existing SQL Server.
Check:
SQL Server version
SQL Server edition
Database size
Number of databases
Database compatibility level
SQL Agent jobs
Logins
Users
Linked servers
Stored procedures
Functions
Views
Triggers
SSIS packages
Cross-database queries
CLR dependencies
Encryption
External dependencies
Application connection strings
For example:
SELECT
name,
database_id,
compatibility_level,
state_desc
FROM sys.databases
ORDER BY name;Check database size:
SELECT
DB_NAME(database_id) AS DatabaseName,
SUM(size) * 8 / 1024 AS SizeMB
FROM sys.master_files
GROUP BY database_id
ORDER BY SizeMB DESC;This gives you an initial understanding of your environment.
8. Step 2 – Perform a Compatibility Assessment
This step is extremely important.
Do not immediately migrate a production database.
First determine whether your existing SQL Server features are supported by the Azure target.
Microsoft's current SSMS migration functionality can assess a SQL Server instance and recommend Azure SQL targets such as:
Azure SQL Database
Azure SQL Managed Instance
SQL Server on Azure VM
It can also identify compatibility considerations before migration. (Microsoft Learn)
A typical process is:
Discover
|
v
Assess
|
v
Identify blockers
|
v
Fix compatibility issues
|
v
Migrate9. Step 3 – Create an Azure Account
Open the Azure Portal:
https://portal.azure.comSign in with your Azure account.
You need an Azure subscription.
10. Step 4 – Create a Resource Group
A Resource Group is a logical container for Azure resources.
For example:
Resource Group:
rg-production-databaseYou may place resources such as:
rg-production-database
|
+-- Azure SQL
+-- Storage Account
+-- Database Migration Service
+-- Key Vault
+-- Monitoring resourcesFor production environments, use a naming convention appropriate for your organization.
Example:
rg-prod-sql-eastus
rg-prod-storage-eastus11. Step 5 – Create Azure Storage
Azure Storage can be used as an intermediate location for database backup files.
Create:
Storage Account
|
+-- Blob Container
|
+-- sqlbackupsFor example:
https://mystorageaccount.blob.core.windows.net/sqlbackups/You can place backup files here:
CustomerDB.bak
OrderDB.bak
ProductDB.bakFor migration projects using Azure DMS, Microsoft recommends using a dedicated storage account for migration-related backup files rather than sharing it with unrelated workloads. (Microsoft Learn)
12. Step 6 – Take a Full SQL Server Backup
On your local SQL Server, take a full backup.
Example:
BACKUP DATABASE CustomerDB
TO DISK = 'D:\SQLBackups\CustomerDB.bak'
WITH
INIT,
COMPRESSION,
STATS = 10;Verify that the backup completed successfully.
You can check backup history:
SELECT
database_name,
backup_start_date,
backup_finish_date,
type,
backup_size
FROM msdb.dbo.backupset
WHERE database_name = 'CustomerDB'
ORDER BY backup_finish_date DESC;13. Step 7 – Upload the Backup to Azure Storage
Upload:
CustomerDB.bakto:
Azure Storage
|
+-- sqlbackups
|
+-- CustomerDB.bakYou can use Azure Storage tools or Azure-supported migration workflows.
For large databases, uploading the backup can take significant time, so estimate the transfer duration before scheduling production migration.
14. Step 8 – Create the Azure SQL Target
Now create the target.
For example, if you selected Azure SQL Managed Instance:
Azure Portal
|
+-- Azure SQL
|
+-- SQL Managed InstanceConfigure:
Subscription
Resource Group
Region
Instance name
Compute
Storage
Networking
Authentication
Security settings
For Azure SQL Database, create:
Azure SQL Server
|
+-- Azure SQL DatabaseFor SQL Server on Azure VM:
Azure VM
|
+-- SQL Server15. Step 9 – Configure Networking
This is one of the most commonly overlooked parts of a database migration.
Your application must be able to reach the Azure database.
Typical architecture:
Internet
|
v
Azure Front Door
|
v
Application
|
v
ASP.NET Core API
|
v
Private Network
|
v
Azure SQLFor production systems, consider:
Virtual Network
Private Endpoint / Private networking
Network Security Groups
Firewall rules
DNS configuration
VPN
ExpressRoute where required
Avoid exposing a production database unnecessarily to the public internet.
16. Step 10 – Choose Your Migration Method
There are several approaches.
Method A – BACPAC
Useful for certain Azure SQL Database migration scenarios.
Local SQL Server
|
| Export
v
.bacpac
|
v
Azure SQL DatabaseAzure SQL supports importing a BACPAC file into Azure SQL Database or SQL Managed Instance. (Microsoft Learn)
17. Method B – Backup and Restore
This is a traditional SQL Server migration approach.
Local SQL Server
|
| .bak
v
Azure Storage
|
v
Azure SQL Managed InstanceThis approach is especially useful for SQL Managed Instance.
Microsoft supports native backup/restore of SQL Server backups stored in Azure Storage for SQL Managed Instance. (Microsoft Learn)
Example:
RESTORE DATABASE CustomerDB
FROM URL =
'https://mystorageaccount.blob.core.windows.net/sqlbackups/CustomerDB.bak';The exact credential and restore configuration depends on your target and storage authentication method.
18. Method C – Azure Database Migration Service
Azure Database Migration Service, or Azure DMS, is designed specifically for database migration scenarios.
The general workflow is:
Source SQL Server
|
v
Azure Database Migration Service
|
v
Azure SQL TargetMicrosoft currently provides DMS migration paths for SQL Server to:
Azure SQL Database
Azure SQL Managed Instance
SQL Server on Azure VM. (Microsoft Learn)
19. Step 11 – Create Azure Database Migration Service
In the Azure Portal:
Portal
|
+-- Search
|
+-- Azure Database Migration ServiceSelect:
CreateConfigure:
Subscription
Resource Group
Region
Service name
NetworkingAfter deployment:
Azure Database Migration Service
|
+-- New Migration20. Step 12 – Configure Source SQL Server
Provide your source SQL Server information.
Example:
Source Server:
192.168.1.50
Database:
CustomerDB
Authentication:
SQL Authentication / Windows AuthenticationThe exact connectivity setup depends on your migration scenario and network architecture.
If backup files are located on an on-premises network share, some DMS scenarios require a self-hosted integration runtime so the migration service can access the source environment and backup files. (Microsoft Learn)
21. Step 13 – Select the Target
Select the Azure destination.
For example:
Source:
SQL Server
Target:
Azure SQL Managed InstanceThen select:
Subscription
Resource Group
Managed Instance
Target Database22. Step 14 – Configure Backup Storage
Tell the migration service where the backup files are located.
Example:
Storage Account
|
+-- sqlbackups
|
+-- CustomerDB.bak
+-- CustomerDB_Log.trnDepending on the migration mode, full and subsequent transaction-log backups may be involved.
23. Step 15 – Start the Migration
Once configuration is complete:
Validate
|
v
Start MigrationAzure DMS will perform the migration according to the selected migration scenario.
Monitor:
Migration Status
|
+-- Starting
+-- In Progress
+-- Validating
+-- Completed
+-- FailedMicrosoft's DMS documentation describes monitoring the migration from the DMS monitoring experience. (Microsoft Learn)
24. Step 16 – Monitor Migration
Do not immediately switch your application to Azure.
First verify the migration.
Check:
Database Status
Data Size
Tables
Indexes
Stored Procedures
Functions
Views
Triggers
Users
PermissionsRun:
SELECT
name,
state_desc
FROM sys.databases;Then connect to the Azure target using SSMS.
25. Step 17 – Validate the Data
This is one of the most important steps.
Compare:
LOCAL DATABASE
vs
AZURE DATABASECheck table counts:
SELECT
t.name AS TableName,
SUM(p.rows) AS RowCount
FROM sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
WHERE p.index_id IN (0,1)
GROUP BY t.name
ORDER BY t.name;Run the same query against the Azure database.
Compare:
Customer
Local: 1,250,000
Azure: 1,250,000
Orders
Local: 8,500,000
Azure: 8,500,000Also validate:
Primary keys
Foreign keys
Indexes
Constraints
Stored procedures
Functions
Views
Triggers
Permissions
26. Step 18 – Test Application Connectivity
Suppose your existing ASP.NET Core application has:
{
"ConnectionStrings": {
"DefaultConnection": "Server=LOCALSERVER;Database=CustomerDB;Trusted_Connection=True;"
}
}After migration, the application must connect to the Azure database.
The new connection string depends on your selected Azure service and authentication method.
Conceptually:
{
"ConnectionStrings": {
"DefaultConnection": "Server=<AZURE-SQL-SERVER>;Database=CustomerDB;..."
}
}Do not hard-code production passwords into source code.
For production applications, consider Azure Key Vault and managed identity-based authentication where appropriate.
27. Step 19 – Update ASP.NET Core Configuration
For example:
builder.Services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(
builder.Configuration.GetConnectionString("DefaultConnection")));The application code does not necessarily need to change.
Usually, the major change is the database connection configuration.
This is one of the major advantages of using Entity Framework Core with SQL Server-compatible Azure targets.
28. Step 20 – Test CRUD Operations
After changing the connection string, test:
Create
Create CustomerRead
Get CustomerUpdate
Update CustomerDelete
Delete CustomerAlso test:
Login
Search
Reports
Transactions
Batch jobs
Background services
Stored procedures
File uploads
Notifications29. Step 21 – Performance Testing
Do not assume that migration automatically means better performance.
Measure:
Before Migration
----------------
API Response: 250 ms
DB Query: 100 ms
After Migration
---------------
API Response: 180 ms
DB Query: 70 msor identify queries that became slower.
Check:
CPU
Memory
DTU/vCore usage depending on Azure SQL offering
Query duration
Blocking
Deadlocks
Index usage
Database waits
Connection pool usage
Use Azure monitoring capabilities and SQL performance tools to investigate performance.
30. Step 22 – Security Configuration
Production databases should be secured properly.
Consider:
Azure SQL
|
+-- Firewall
+-- Private Endpoint
+-- Microsoft Entra authentication
+-- Managed Identity
+-- Encryption
+-- Auditing
+-- Defender for SQL
+-- Key VaultNever publish credentials in:
GitHub
appsettings.json
Source Code
Dockerfile
Azure DevOps YAMLFor production, use secure secret-management mechanisms.
31. Step 23 – Configure Backups
One major advantage of Azure SQL services is that Azure provides managed backup capabilities.
However, you should still understand:
Backup
|
+-- Retention
+-- Point-in-time restore
+-- Long-term retention
+-- Disaster recoveryYour backup strategy should match your organization's Recovery Point Objective (RPO) and Recovery Time Objective (RTO).
32. Step 24 – Configure Monitoring
Use Azure monitoring capabilities to observe your database.
Typical monitoring architecture:
Azure SQL
|
+-- Azure Monitor
|
+-- Log Analytics
|
+-- Application Insights
|
+-- AlertsCreate alerts for important conditions such as:
High CPU
High storage usage
Connection failures
Long-running queries
Availability problems33. Step 25 – Application Cutover
Once testing is successful, perform the final cutover.
A typical production cutover is:
1. Notify users
|
2. Stop application writes
|
3. Complete final synchronization
|
4. Validate Azure database
|
5. Update connection string
|
6. Deploy application
|
7. Start application
|
8. Perform smoke testing
|
9. MonitorFor migration methods that maintain synchronization, Microsoft recommends ensuring the target is synchronized and validated before switching application traffic. (Microsoft Learn)
34. Example Production Migration
Suppose we have:
LOCAL SERVER
Server: SQLSERVER01
Database:
SalesDB
Size:
500 GB
Application:
ASP.NET Core Web API
Frontend:
AngularWe want:
AZURE
Azure SQL Managed Instance
Database:
SalesDBThe migration architecture becomes:
ON-PREMISES
|
|
SQLSERVER01
|
SalesDB
|
Backup
|
v
Azure Storage
|
v
Azure Database Migration
Service
|
v
Azure SQL Managed
Instance
|
v
ASP.NET Core API
|
v
Angular35. Complete Migration Flow
The complete process can be summarized as:
LOCAL SQL SERVER
|
v
Database Assessment
|
v
Compatibility Check
|
v
Choose Azure Target
|
+----------+----------+
| | |
v v v
SQL DB MI Azure VM
| | |
+----------+----------+
|
v
Create Azure
Infrastructure
|
v
Create Storage
|
v
Take Backup
|
v
Upload / Configure
Backup Storage
|
v
Azure DMS / Restore
|
v
Migration Complete
|
v
Validate Database
|
v
Test Application
|
v
Performance Test
|
v
Cutover
|
v
Azure Production36. Common Migration Problems
Problem 1 – Unsupported SQL Server Feature
Your local SQL Server may use a feature that isn't available on the selected Azure target.
Solution
Perform compatibility assessment before migration.
Problem 2 – Application Cannot Connect
Possible causes:
Firewall
Networking
DNS
Authentication
Connection String
Private Endpoint
CredentialsCheck each layer systematically.
Problem 3 – Database Is Very Large
For a very large database, BACPAC export/import may not be the best approach.
Consider:
Azure Database Migration Service
Backup/Restore
Managed Instance Link
Log Replay Servicedepending on your target and downtime requirements.
Microsoft documents multiple migration paths for SQL Managed Instance, including DMS, Managed Instance link, Log Replay Service, and native restore. (Microsoft Learn)
37. What About Minimal Downtime?
Suppose your database is:
2 TBand your application cannot be stopped for several hours.
A simple:
Backup
↓
Upload
↓
Restoremay result in too much downtime.
Instead, consider a migration method that supports ongoing synchronization/replication, depending on your target.
The architecture becomes:
LOCAL SQL SERVER
|
| Initial migration
v
AZURE DATABASE
^
|
| Continuous changes
|
LOCAL SQL SERVERThen:
Stop application
|
v
Final synchronization
|
v
Validate target
|
v
Change connection string
|
v
Start applicationThis can significantly reduce the final cutover window.
For SQL Managed Instance, Microsoft documents DMS, Managed Instance link, and Log Replay Service for migration scenarios designed to reduce downtime. (Microsoft Learn)
38. What Happens to SQL Agent Jobs?
This depends heavily on your target.
If you move to SQL Server on Azure VM, you retain a traditional SQL Server environment.
With SQL Managed Instance, many SQL Server instance-level capabilities are supported, including SQL Agent-related workloads, although you should still assess individual dependencies.
With Azure SQL Database, you should not assume that every SQL Server instance-level feature will move directly.
Therefore:
SQL Server Agent Jobs
|
+-- Identify
|
+-- Assess
|
+-- Recreate / redesign where required39. What Happens to Logins and Users?
Database users and server-level logins are different concepts.
You should inventory:
Server Logins
Database Users
Roles
Permissions
Application Accounts
Service AccountsAfter migration, verify that every application identity has the correct permissions.
Do not simply grant:
db_ownerto every application account.
Use the minimum permissions required.
40. What Happens to Stored Procedures?
Stored procedures generally migrate well when supported by the selected target.
After migration, execute important procedures and compare results.
For example:
EXEC dbo.GetCustomerOrders
@CustomerId = 1001;Compare the results between:
Local SQL Server
vs
Azure SQL41. What Happens to SQL Server Jobs and SSIS?
Create an inventory before migration.
SQL Agent Jobs
SSIS Packages
Linked Servers
Maintenance Plans
Database Mail
Cross Database Queries
CLR
ReplicationSome workloads may need redesign when moving to a fully managed Azure SQL platform.
This is one reason why assessment before migration is critical.
42. Rollback Strategy
Never perform a production database migration without a rollback plan.
Before cutover:
LOCAL DATABASE
|
+-- Final backup
|
+-- Keep source availableIf something goes wrong:
Azure
|
X
|
Rollback
|
v
Local SQL ServerDo not immediately delete the original database after migration.
Keep the source environment available until:
Application validated
Data validated
Business users approved
Performance validated
Monitoring stable
Rollback window completed43. Production Migration Checklist
Before migration:
☐ Inventory SQL Server
☐ Check database size
☐ Check SQL Server version
☐ Assess compatibility
☐ Identify dependencies
☐ Identify SQL Agent jobs
☐ Identify logins/users
☐ Identify linked servers
☐ Choose Azure target
☐ Create Azure subscription/resource group
☐ Configure networking
☐ Create target database/server
☐ Configure Azure Storage
☐ Take backup
☐ Test restoreDuring migration:
☐ Start migration
☐ Monitor migration
☐ Check errors
☐ Verify database state
☐ Compare row counts
☐ Validate schema
☐ Validate indexes
☐ Validate users
☐ Validate permissionsBefore cutover:
☐ Test application
☐ Test APIs
☐ Test reports
☐ Test transactions
☐ Test performance
☐ Prepare rollback
☐ Notify stakeholdersAfter cutover:
☐ Update connection string
☐ Deploy application
☐ Smoke test
☐ Monitor database
☐ Monitor API
☐ Monitor errors
☐ Monitor performance
☐ Obtain business approval44. Recommended Architecture for a .NET + Angular Application
For a modern application, the architecture could look like:
USERS
|
v
Angular SPA
|
v
Azure Front Door
|
v
Azure API Management
|
v
ASP.NET Core APIs
|
+----------+----------+
| |
v v
Azure Service Bus Azure SQL
| Managed Instance
| |
v v
Background Workers Database
|
v
External ServicesAdditional Azure services can be introduced based on requirements:
Azure Key Vault
Azure Monitor
Application Insights
Azure Storage
Azure Container Registry
AKS / App Service
Azure Functions45. Azure SQL Database vs Managed Instance vs Azure VM
| Feature | Azure SQL Database | SQL Managed Instance | SQL Server Azure VM |
|---|---|---|---|
| Fully managed | Yes | Yes | No |
| SQL Server compatibility | Moderate | High | Very High |
| OS management | Azure | Azure | Customer |
| SQL Server instance access | Limited | High | Full |
| Lift-and-shift | Sometimes | Excellent | Excellent |
| Cross-database scenarios | Limited compared with MI | Strong | Strong |
| SQL Agent | Not traditional SQL Agent | Supported scenarios | Full |
| Infrastructure control | Low | Medium | High |
| Administration effort | Lowest | Medium | Highest |
Always verify the current feature support for your exact SQL Server version and Azure target before production migration.
46. Recommended Approach
For a typical enterprise application currently running:
ASP.NET Core
Angular
SQL Server
Azure ServicesI recommend this decision process:
Existing SQL Server
|
v
Compatibility Assessment
|
+-----------+-----------+
| |
Minimal changes Cloud modernization
| |
v v
SQL Managed Instance Azure SQL Database
|
|
Very high SQL
compatibility
|
v
SQL Server Azure VMFor a large existing SQL Server environment with many instance-level dependencies, Azure SQL Managed Instance is often a strong candidate.
For a modern application that can adapt to Azure SQL Database capabilities, Azure SQL Database can reduce infrastructure administration.
For applications requiring maximum control and near-traditional SQL Server behavior, SQL Server on Azure VM is the closest lift-and-shift option.
47. Final Takeaway
Migrating SQL Server to Azure is not simply:
Copy Database
↓
AzureA successful migration is:
ASSESS
↓
PLAN
↓
CHOOSE TARGET
↓
PREPARE AZURE
↓
BACKUP / MIGRATE
↓
VALIDATE
↓
TEST
↓
CUTOVER
↓
MONITOR
↓
OPTIMIZEThe most important rule is:
Never migrate a production database directly without first performing compatibility assessment, testing the migration, validating the data, and preparing a rollback plan.
Azure provides several migration approaches, and the correct choice depends on the target platform, database size, SQL Server features, network architecture, and acceptable downtime. Microsoft currently documents Azure DMS, native backup/restore, Managed Instance link, and Log Replay Service among the available migration approaches for relevant SQL Server-to-Azure scenarios. (Microsoft Learn)
