Prerequisites: Solid understanding of SQL Server fundamentals. See DB 01-08 for on-premises concepts.

Moving SQL Server to the cloud? This guide covers your options, trade-offs, and migration strategies.

---

Part A: Cloud Database Models

1. IaaS vs PaaS vs SaaS

2. Comparison Table

Aspect	IaaS (SQL on VM)	PaaS (Managed)
Setup	Install SQL yourself	Click and deploy
Patching	Your responsibility	Automatic
HA/DR	Configure yourself	Built-in options
Backup	Script it	Automatic (PITR)
Scaling	Resize VM	Slider/auto-scale
Cost control	Pay for VM always	Pay per usage
Customization	Full control	Limited
SQL Agent	✓ Available	✗ Not available
FILESTREAM	✓ Available	✗ Not available

---

Part B: Azure SQL Options

3. Azure SQL Family

4. Azure SQL Database

Best for: New cloud-native applications

Feature	Details
Model	Serverless or Provisioned
Scaling	DTU or vCore based
HA	Built-in (99.99% SLA)
Backup	Automatic PITR (7-35 days)
Geo-replication	Active geo-replication

-- Create database in Azure
CREATE DATABASE MyAppDB
(
    EDITION = 'GeneralPurpose',
    SERVICE_OBJECTIVE = 'GP_Gen5_2',
    MAXSIZE = 100 GB
);

DTU vs vCore Explained

Model	What It Is	Best For
DTU	Black-box bundle (CPU + Memory + IO)	Simple apps, predictable workloads
vCore	Explicit CPU cores (like on-prem)	Hybrid Benefit (AHUB), fine control

Tip: If you want to use Azure Hybrid Benefit to save money, you must choose the vCore model.

Serverless vs Provisioned

Aspect	Serverless	Provisioned
Billing	Per-second compute	Fixed monthly
Auto-pause	Yes (save costs)	No
Cold start	~1 minute	None
Best for	Dev/test, variable load	Production, predictable

5. Azure SQL Managed Instance

Best for: Lift-and-shift migration with near 100% compatibility

Feature	SQL DB	Managed Instance
SQL Agent	✗	✓
Cross-database queries	✗	✓
CLR	✗	✓
Linked servers	✗	✓
Service Broker	✗	✓
DB Mail	✗	✓

When to Choose Managed Instance

• Legacy applications with SQL Agent jobs
• Cross-database queries required
• Need CLR or linked servers
• Want minimal code changes during migration

MI Network Complexity

Managed Instance requires a dedicated subnet in your VNet and typically needs VPN or ExpressRoute for on-prem connectivity. This is more complex than Azure SQL Database (which uses public endpoints by default).

Service Tiers: Performance Levels

Tier	Storage	HA Architecture	Read Scale-out
General Purpose	Remote (Standard SSD)	Similar to FCI	✗ No
Business Critical	Local SSD	Built-in Always On AG	✓ Yes (free!)

Performance Tip: Business Critical tier offers significantly lower latency and includes a free read replica for reporting queries.

6. SQL Server on Azure VM

Best for: Full control, special features, or licensing benefits

Scenario	Use Azure VM
FILESTREAM needed	✓
Custom SQL configuration	✓
Bring your own license (AHUB)	✓
Windows clustering	✓
Third-party tools requiring OS access	✓

---

Part C: AWS Options

7. AWS SQL Server Options

8. Amazon RDS for SQL Server

Feature	Details
Editions	Express, Web, Standard, Enterprise
Multi-AZ	Automatic failover (Mirroring or Always On behind the scenes)
Read Replicas	✓ Supported (Enterprise/Standard, up to 5 replicas)
Backup	Automated daily snapshots + Transaction logs (5 min RPO)

RDS Limitations

Feature	Supported?
SQL Agent	✓ Yes (standard jobs work, no OS access)
Linked Servers	✓ Yes (to other cloud/on-prem DBs)
FILESTREAM	✗ No (file system access blocked)
DTC (Distributed Transactions)	✓ Yes (recently supported with prerequisites)
OS/RDP Access	✗ No (it’s PaaS)

9. Comparison: Azure vs AWS

Aspect	Azure SQL MI	AWS RDS
Compatibility	~99%	~95%
SQL Agent	Full	✓ Full (no OS shell access)
Cross-DB queries	✓	✗
Linked servers	✓	Limited
BYOL	✓	✓
Read Replicas	✓ (geo-replication)	✓ (Enterprise, cross-region)

AWS RDS Update (2024): RDS for SQL Server now supports Read Replicas for Enterprise Edition, including cross-region. SQL Agent is fully functional (only OS-level shell commands are restricted).

---

Part D: Migration Strategies

10. Migration Assessment

11. Migration Tools

Tool	Purpose
Data Migration Assistant (DMA)	Compatibility assessment
Azure Migrate	Discover and assess on-prem DBs
Database Migration Service (DMS)	Online migration with minimal downtime
BACPAC	Export/import for smaller databases

12. Migration Methods

Method	Downtime	Best For
Backup/Restore	Hours	Small DBs, test environments
BACPAC	Hours	Schema + data, small DBs
Transactional Replication	Minutes	Large DBs, minimal downtime
Azure DMS (online)	Minutes	Production migrations
Log Shipping	Minutes	Large DBs, controlled cutover

Online Migration with DMS

---

Part E: Cost Optimization

13. Cost Saving Strategies

Strategy	Savings	Applicable To
Reserved capacity	Up to 80%	Predictable workloads
Hybrid Benefit (AHUB)	Up to 55%	Existing SA licenses
Serverless auto-pause	Significant	Dev/test, intermittent
Right-sizing	20-50%	Over-provisioned DBs
Elastic pools	20-50%	Multiple small DBs

14. Azure Hybrid Benefit

Already have SQL Server license with Software Assurance?
→ Apply it to Azure SQL for up to 55% savings!

License Type → Azure Savings:
Enterprise Core → 4 vCores in Azure
Standard Core → 1 vCore in Azure

---

Summary

Decision Quick Reference

Scenario	Recommendation
New cloud-native app	Azure SQL Database
Lift-and-shift with SQL Agent	Azure SQL Managed Instance
Maximum compatibility needed	SQL on Azure VM
AWS ecosystem	RDS for SQL Server
Full control on AWS	SQL on EC2

Migration Checklist

- Run DMA compatibility assessment
- Check unsupported features
- Size appropriately (don't over-provision)
- Plan backup strategy
- Test application connectivity
- Plan cutover window
- Document rollback procedure
- Update connection strings

Key Takeaways

1. PaaS = Less management → patching, backup, HA automatic
2. Managed Instance → best compatibility for migrations
3. Serverless → great for dev/test, variable workloads
4. Hybrid Benefit → significant savings with existing licenses
5. Assess first → use DMA before migrating

---

Practice Questions

Conceptual

1. What is the difference between IaaS and PaaS for SQL Server in the cloud?

2. Compare Azure SQL Database vs Azure SQL Managed Instance. When would you choose each?

3. What is the Azure Hybrid Benefit and how does it save costs?

Scenario

1. Migration Decision: A company has an on-premises SQL Server with heavy use of SQL Agent jobs, linked servers, and cross-database queries. Which Azure option would you recommend and why?

2. Cost Optimization: A dev/test database is used only during business hours (8am-6pm). How would you configure it to minimize costs?