Manvendra Singh

Getting started with Azure SQL

November 8, 2021 by

This article will help you understand Azure SQL and its deployment options available in the Azure cloud. This article would be helpful to you if you are planning to migrate your SQL Server workloads to Azure cloud or want to provision a SQL database for your new application development. You will learn which deployment option would be beneficial for your database workload in this article.

These days every company is considering moving to the cloud environment. Journey to the cloud will require a lot of planning like choosing the right set of cloud services as target systems, compatibility checks, deployments, monitoring, and other operational activities. If you are looking for SQL Server offerings in Azure cloud, then you should learn about Azure SQL which is a term being used to represent the SQL Server family and its services available on Azure cloud. It has 3 deployment categories that can be chosen based on the fitment of your business requirements and database workload. You need to consider each aspect before choosing any type of deployment for your database workload. This article will explain all 3 deployments which will help you in deciding on choosing the right deployment options if you are planning to migrate your existing database systems to the cloud or need to provision a SQL database for your application development.

Microsoft offers 3 deployment options under Azure SQL as given below. These deployments on Azure uses SQL Server database engine as its core to handle database workloads.

  • SQL Server on Azure VM
  • SQL Managed Instances
  • SQL Database

You can choose any deployment option based on your priority whether you want to change your database design or want to save cost or minimize the manageability of the databases or want the same database setup as you are running in your on-premises environment. Microsoft has given all options matching your business needs. You just need to carefully understand the pros and cons of each deployment and choose the one which matches your business needs.

You can see all three deployments options by looking into the below image which shows that Azure SQL is not any service but a logical name or term which represents different types of SQL Server deployments in the Azure cloud.

Azure SQL family

You can also get a similar view on the Azure portal. Log in to the Azure portal. Type “Azure SQL” in the search bar and click on it once it will appear in the dropdown menu. You will get its main page where you can see all deployment options of SQL Server databases. If you have chosen the right deployment option as your target database system, then click on the “+ Create” button on that page. You will get all 3 deployment options on your screen to choose anyone to proceed with correct Azure SQL deployment.

Create SQL database as per your need

You can choose any option which will match your requirement and click on the “Create” button to proceed.

Choose Right Deployment option of Azure SQL

Once you have decided to move your SQL Server workloads to the cloud the next key thing you need to decide is to choose between IaaS and PaaS offerings. You need to take a call at this point that whether you want to migrate your workload in the IaaS model (Infrastructure as a Service) or want to move in a PaaS model (Platform as a Service). You can consider various factors like cost comparison between IaaS vs PaaS, you want to manage all your database administration-related activities yourself or you want to get rid of backup, patching, and other operational-related stuff, what kind of availability or data protection you want.

For example, if you are choosing IaaS and your primary concern is availability, then you need to consider other mechanisms like AOAG or clustering additionally to increase the database availability SLA. PaaS offerings guarantee 99.99% availability whereas IaaS guarantees 99.95% availability and that also only for VMs. The point here is you need to consider all aspects before finalizing your deployment option.

If you don’t want to manage database administration activities and want higher availability, you can consider choosing PaaS model deployments which is either Azure SQL Managed Instance or Azure SQL database. Next, you need to choose between these two deployments. Choosing between managed instances and SQL database is not difficult. You need to look at the price comparison of both deployments and the features you want to use in your database deployment. You might be not getting some of the features in SQL database deployment but almost all features of SQL Server are available if you choose Azure SQL managed instance.

SQL Managed instance is very close to the SQL Server in feature comparison. Make sure to run Azure Data Migration assistant to evaluate your source system to understand the road blockers for this migration. These are the high-level approach you need to follow while deciding the right deployment option for your database requirements.

You can get a glimpse of the features of each database deployment in the below image. I have also mentioned when to choose a specific deployment option based on its suitability.

Choose right deployment option

Now, let’s discuss a little more about each deployment option to make you understand more about them.

SQL Server on Azure Virtual Machine

If you don’t want to put additional efforts and time into rearchitecting your application as per cloud environment and want your environment to be in AS-IS form, then SQL Server on Azure VM should be the first choice. This model is also suitable if your application needs access to operating system files. You can see it running as your on-premises SQL Server instance on some remote data center(cloud). There is no difference between SQL Server running on-premises and SQL Server running on Azure virtual machine if we talk about features, compatibility, version selections, etc. It’s all same. Even you can use your on-premises licenses in this cloud deployment under the Azure Hybrid Benefits feature to save your license cost in the cloud. You can use any supported version of SQL Server while deploying it in IaaS form which is popularly known as SQL Server on Azure virtual machines.

Migration is also very easy if you choose SQL Server on Azure VM. It is just like you are migrating on-premises databases from one server to another. You need to support these databases the way you support them in the on-premises environment. It means you must manage backup, patching, and other operational activities. You can leverage Azure native services to get all administration-related activities done easily.

You need to deploy high availability features for your SQL Server databases. Remember, IaaS guarantees 99.95% availability SLA only for virtual machines and not for SQL Server or other processes hosted on that virtual machine. That’s the reason we need to configure other high availability features like availability groups or failover cluster instances to meet database availability SLA as offered in PaaS services which are 99.99%.

Azure SQL Managed Instance

This is a PaaS service offering for SQL Server. If you are looking to migrate your workload on the PaaS environment and want control over SQL Server instance level with having almost equivalent features that are there in SQL server then SQL Managed instance would be the right choice for you. You would not have control over its operating system where managed instance is hosted so keep this in mind while deciding on migrating your workload to the cloud.

SQL Managed Instance would be suitable for most of your workload as it has almost full compatibility with on-premises SQL Server and it has all the benefits from PaaS product SQL databases. You don’t need to manage any administration tasks as Azure will take care of backup, upgrades, and patching activities. You can choose this deployment option if you want to get rid of administration complexities.

Migration from on-premises SQL Server instances to SQL managed instances will require minimal to no changes in databases because it offers almost all features that SQL Server offers in an on-premises deployment.

Azure SQL Database

SQL database is Database as a Service product offered by Azure cloud for modern cloud-ready applications. You should choose this deployment if you want to create the latest version of the SQL Server database for your cloud-designed application and want to leverage hyperscale and serverless options. This deployment will keep you stress-free from a database manageability and administration standpoint. You just need to log in to the Azure portal, enter few details about the database, and create it. Your database is ready for development. You don’t need to deploy a virtual machine or install SQL Server or think about its backup and other operational activities. You can focus on your application development and database-related management work will be taken care of by Azure. This could also be one of the reasons behind choosing this deployment to get rid of administration complexities.

Although SQL databases lower the need of managing the underlying operating system and SQL Server instance, some of the activities will still be taken care of by administrators like login and users manageability, performance tuning, etc.

Azure SQL database offers two deployment approaches:

  • A single SQL database
  • An Elastic pool

A single SQL database is one SQL database having its own system resources whereas an elastic pool is a collection of databases that will use a shared set of system resources among all pooled databases. The elastic pool is a cost-effective solution if you want to choose the SQL database deployment option and you have multiple databases to deploy. You can add those databases to the elastic pool and allow them to use system resources that are sitting ideal.

If you are migrating on-premises SQL Server databases to the cloud, then migration to Azure SQL databases would be challenging as there are a lot of SQL Server features that are still not supported in the SQL database deployment option. You must plan it carefully with having proper testing before taking your production system from on-premises to SQL database in the cloud. I would also recommend you to visit this Microsoft site to see all supported features available in the SQL database as Microsoft keeps on adding new features to their services on regular basis.

Conclusion

This article will help you understand all MSSQL products available under the Azure SQL umbrella in the Azure cloud. I have explained its deployment options along with their pros and cons which will help you to choose the right deployment option easily. The right deployment option must be chosen based on various aspects and points I have discussed in this article.

Manvendra Singh
SQL Azure

About Manvendra Singh

Manvendra is a database enthusiast, currently working as a Senior Architect at one of the top MNC. He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects. You can also find him on LinkedIn View all posts by Manvendra Singh

168 Views