Options for Running SQL Server on Cloud-Part I(Azure)

I started my career with database development project on SQL Server version 2000 and with a distinguished background as a former Microsoft Cloud Solution Architect, boasting a wealth of over 15+ years in the database realm.

Over the course of time, I have been privileged to engage with diverse iterations of SQL Server versions from 2000 to SQL Server 2022 nurturing a comprehensive expertise in its evolution.

A question that consistently finds its way to me is, ‘What are the options for deploying SQL Server in the cloud?’ A query I am more than equipped to address. In response, I am excited to unveil a three-part series that delves into the running SQL Server within cloud environments — spanning (AWS, Azure, and GCP)

Before going further details , I recommened you to please go through my earlier Article on “Cloud Computing Landscape” ( This article will cover when to choose IaaS, PaaS, and SaaS )

Today I will be focussing on Microsoft Azure — a platform that has been an integral part of my journey. I will deep dive into the manifold options, advantages, and insights that come with running SQL Server on Azure

Introduction to Azure SQL:

Azure SQL is an term to represent the family of SQL offerings available in Microsoft’s Azure cloud. There are three Azure SQL options available:

• SQL Server on Azure virtual machines ( Iaas)
• Azure SQL Managed Instance (Paas)
• Azure SQL Database (Paas)

Choosing the right option for your business needs?

It is important to choose the right database based on your unique requirements. The first choice is between Infrastructure as a service (IaaS) and Platform as a service (PaaS).

Simply infrastructure as a service refers to pay-as-you-go storage, networking, and virtualization services. Whereas platform as a service provides a complete cloud platform, both hardware and software, for use in developing, running and managing apps.

• SQL Server on Azure Virtual Machines (IaaS): This option gives you the most control over your SQL Server environment. You can choose the hardware configuration, operating system, and SQL Server version. You’re also responsible for managing the database backups and security.
• Azure SQL Managed Instance (PaaS): This option is a fully managed service that takes care of all the database administration tasks. You don’t need to worry about hardware, software, or security. Azure SQL Managed Instance is a good choice for production workloads that need high availability and scalability.
• Azure SQL Database (SaaS): This is the simplest option for running SQL Server on Azure. You don’t need to worry about any of the infrastructure or administration tasks. Azure SQL Database is a good choice for small workloads or for developers who want to get up and running quickly.

Now before getting to understand the Pros and Cons of each service, let us understand the key features now.

Azure SQL Virtual Machine (VM):

• SQL Server and OS server access
• Expansive SQL and OS version
• Windows, Linux, Containers
• File steam, DTC, Simple Recovery model
• SSAS, SSRS and SSIS

Azure SQL Managed Instance:

• Single instance or instance pool
• SQL Server surface area (vast majority)
• Native virtual network support
• Fully managed service
• On-premise identities enables with Azure AD and AD Connect

Azure SQL Database:

• Single database or elastic pool
• Hyperscale storage (100TB+)
• Serverless compute
• Fully managed service
• Private link support
• High availability with AZ isolation

By conducting a comprehensive assessment of the features, as well as going through below advantages and disadvantages of the available services, I hope this should help business owners can make informed decision to fulfill their specific business requirements.

Azure SQL Virtual Machine (VM):

Pros:

• Full Control: Offers complete control over the SQL Server instance, including OS level and SQL Server configuration.
• Customization: Allows installation of custom applications and extensions.
• Migration Flexibility: Suitable for lift-and-shift scenarios where you want to migrate existing SQL Server workloads to the cloud without significant code changes.

Cons:

• Management Overhead: Requires more management, including OS updates, backups, high availability configuration, and patching.
• Scalability Challenges: Scaling can be more complex and less flexible compared to Azure SQL Database.
• Higher TCO: Can result in higher total cost of ownership (TCO) due to additional management and infrastructure costs.

Use Cases:

• Legacy Applications: For applications that require specific versions of SQL Server or custom configurations.
• Complex Workloads: When you need advanced features or customizations not available in Azure SQL Database.
• Specialized Hardware: When specific hardware configurations are necessary, such as GPU-intensive workloads.

Azure SQL Database:

Pros:

• Managed Service: Fully managed by Microsoft, reducing administrative overhead.
• Automatic Updates: Patches, updates, and backups are handled automatically.
• Scalability: Easily scales up or down based on workload requirements.
• Built-in High Availability: Provides automatic failover and built-in disaster recovery.
• Elastic Pools: Cost-effective for managing multiple databases with variable workloads.

Cons:

• Limited Customization: Less flexibility compared to SQL VM for custom configurations.
• Compatibility Challenges: Some applications might require modifications to work optimally with Azure SQL Database.
• Higher Latency: Network latency could impact performance for certain latency-sensitive applications.

Use Cases:

• Web and Mobile Applications: Well-suited for modern, cloud-native applications with varying workloads.
• Dev/Test Environments: Convenient for development and testing scenarios.
• Small to Medium Businesses: Provides cost-effective database solutions without the need for extensive management.

Azure SQL Managed Instance:

Pros:

• Compatibility: Offers near 100% compatibility with on-premises SQL Server, making migration easier.
• Scalability: Allows vertical and horizontal scaling, providing flexibility for growing workloads.
• VNET Integration: Supports virtual network integration for enhanced security and performance.
• Simplified Management: Provides a balance between control (compared to SQL Database) and management ease (compared to SQL VM).

Cons:

• Cost: Generally more expensive than Azure SQL Database, but offers additional features.
• Limited Customization: Not as customizable as SQL VM for certain configurations.
• Complexity: While less complex than SQL VM, it still requires more management than Azure SQL Database.

Use Cases:

• Enterprise Applications: Suitable for large-scale, mission-critical applications requiring high performance and compatibility.
• Data Warehousing: Useful for analytical workloads and data warehousing.
• Hybrid Scenarios: When you need a balance of control and managed services, especially for hybrid cloud setups.
• Remember, the choice between these options depends on factors like your application’s requirements, performance needs, existing skillsets, and budget constraints. Always evaluate your specific use case before making a decision.

Azure offers a variety of options for running SQL Server, so you can choose the option that best meets your needs. Whether you need a highly scalable production environment, a simple development environment, or a secure database for your IoT devices, Azure has a SQL Server option for you.

I hope this post was helpful. If you have any questions, please feel free to leave a comment below.