Quick Track: Beginner’s Guide to Azure SQL- Learn to deploy

Okay, so you would like to deploy your databases in Azure! Great. Your resource limits are based on the deployment you chose. Managed instance and Azure SQL database resource limits depends on the Service tier, compute tier, the number of vCores you chose.

Before migrating the databases to Azure, knowing the details of what deployment model (IaaS/PaaS), deployment method (Azure portal, Azure CLI, PowerShell, ssms tool), region you wish to deploy, Service tier, compute tier, purchasing model, hardware and size of your databases is crucial (Source).

The deployment option and the service tier you chose directly depends on the resource limits that you get. Compute resource limits like the memory, storage limits like max size of your database log file, how long the backups are secured, IOPS, tempdb size limit etc.;(Source).

There are couple of things that we need to focus on while deploying the Azure SQL database. The region where you would like to deploy, under which resource group you would like to place the database, logical server name to connect to the Azure SQL using ssms, Admin SQL login credentials, purchasing model and service tier.

Creating the Azure SQL database using the portal is the basic method of creating a single database. Remember, you cannot restore a native backup in Azure SQL database but restore using the bacpac file. In MI, you can natively restore the database using the url.

Any resource that you create requires the subscription, resource group, database name, logical server name and credentials,

While configuring, you will have an option to chose if the database needs to be created as a part of the elastic pool (databases in the pool will share the compute resources together).

As you click on the configure compute and storage, you can chose the service and compute tier here.

If you chose the general purpose, you will have the compute tier options “provisioned”/”serverless”.

Chose the hardware configuration.

Select the hardware

Scale up or down to adjust the number of vCores and data max size.

Backup storage redundancy has three options. To find the differences among those, see here.

Networking tab:

Network connectivity:

No access: no one will have access

Public endpoint: quickly connect but not secure.

Private end point: Most secure way to connect by creating the private ipaddress to connect to from your virtual network.

Firewall rules:

Allow Azure services and resources to access this server: Any resources in azure can connect to your server. Not a secure option. try to avoid choosing this option.

Add current IP address: enabling this will add the client IP address so it can connect to the server.

Under the Security tab, you will see the Azure defender for SQL. This is not a free service. You will have to pay 15$ per server per month.

In the additional settings, chose how you want the database to be created. None will create just the database name and no objects. Backup is to get the database from the backup file and sample creates a sample of the adventureworks database. Select the collation while deploying the database. For the Azure SQL database, you cannot chnage the collation later.

Chose the maintenance window for the options provided.

Under the tags, name the tag for this resource and a value. This will help to identify the resources later.

Click on review and create. Click on create at the bottom of the window. This will create a new Azure SQL database.

After the deployment is complete, you will be able to copy the server name, open the ssms and connect to the server using the admin login and password credentials.

Connect to the server using the credentials for the logical server admin with password

Things to remember:

  1. You can chose the size and the number of the files for the Managed instance. You cannot chose the storage in MI and Azure SQL database. For Azure SQL, you cannot chose the number of database files.
  2. T-log file for the Azure SQL database is 30% of the data max size.
  3. Compatibility level is available for both MI and Azure SQL database
  4. Query_store is turned on for all Azure SQL databases
  5. Azure SQL Databases recovery mode is always full
  6. You cannot stop and start the Managed instance and Azure SQL database
  7. Proxy and redirection are the two ways that we can connect to Azure SQK database. Proxy connection is like a friend who visits your apartment always need to connect to the security guard (known as gateway here) before reaching your apartment (database). This is the default setting for connections outside Azure.
  8. Redirect connection is like your friend gets an additional key as an authorized person for the apartment by connecting to the security guard (the gateway) and from that point the next connections can be directly reach the apartment (database). No need of additional hops in between. Redirect connection is default connection type within azure. To use redirect connection, ports 11000- 11999 needs to be opened.

We are going to learn about fundamentals of security in azure in the next blog post. Thanks for reading!

References:

  1. Learn Live – Azure SQL Fundamentals: Deploy and Configure Servers, Instances, and Databases (Ep.2)
  2. Deploy and configure servers, instances, and databases for Azure SQL

Quick Track: Beginner’s Guide to Azure SQL

Source: Image

As I started learning Azure couple of months ago, I was confused in the beginning of where to start and was overwhelmed by looking at the amount of information that I not only have to learn but also remember what I learn.

I believe you can treat this series as my consolidated learnings put together in a simple way. A quick guide to understanding the concepts if you are a beginner or to revise the concepts you already learnt to make your foundations stronger.

Let’s start with a Why Question. Why should you even start learning Azure or any cloud platform?

Azure is one of the cloud platforms available in the market which provides services that you can use to host your applications, build your databases, virtual machines, storage, analytical and many other services without having to buy or manage any hardware. As the Azure supports Infrastructure and platform as a service, you can build, configure and run your databases and related services pretty quickly and with ease of deployments through Azure portal online/command line interface/PowerShell.

Deployment options

Source: Microsoft

IaaS: Infrastructure as a Service

  • SQL Server on Azure VM
    • SQL Server hosted on Azure Infrastructure
    • Automated backups and patches
    • Extended security updates for the older versions of SQL Server (SQL Server 2008)
    • You will have access to operating system and can configure instance level features for SQL Server
    • Easy Install- through Azure portal using images (various version combinations of available packages for both Windows and SQL Server together)

PaaS: Platform as a Service

  • SQL Managed Instance
    • OS is taken care by Microsoft
    • You will not have access to the OS
    • Use and configure all features of the SQL Server instance
    • Useful for lift and shift scenarios where applications don’t have to change their code in order to move to this service
  • Azure SQL Database
    • You will just get the database
    • Database level features can be configured
    • OS and Instance capabilities taken care by Microsoft
    • very useful for Modern cloud application built
    • Very quick to deploy

For the full list of feature comparison between Azure SQL Database and SQL managed instance, read the Microsoft doc here.

Unlike the SQL Server on Azure VM, Azure SQL database and SQL managed instance are versionless. Databases, OS are upgraded, patched behind the scenes by Microsoft.

Purchasing models:

There are two purchasing models, DTU and vCore.

In the DTU (database transaction per unit) model, we choose the number of DTU’s and the resources comes in bundle together (compute and storage resources). vCore model has the advantage of using the Azure hybrid benefit and reserved capacity if you can pay for the resources ahead of time up to three years. When you use vCore model, it is easy to convert the on-prem workloads to the azure as you can independently choose the compute, storage and other resources like memory.

DTUvCore
Bundle togetherIndividual compute and storage
Easy and simpleCustomize and control
Not flexibleFlexible
Azure Hybrid benefit – NO Azure Hybrid benefit – YES
Available for Azure SQL Database onlyAvailable for MI and Azure SQL Database

The cost of the vCore model depends on the service tier, compute, storage, hardware you choose, backup storage you choose.

Note: you can convert the DTU model to vCore model. Know more here.

Service Tiers

We have three Service tiers for Azure SQL database and Managed instance – General purpose, Business critical and Hyperscale. Especially under the General purpose tier, we have two compute tiers. We will learn that in coming section. For the main differences among the service tiers, please follow below table.

Service TierGeneral purposeBusiness criticalHyperscale
PurposeGeneral workloadsLow latency workloadsOLTP, large databases
Compute80 vCores80 vCores80 vCores
Storage typeRemote 5 GB – 4 TBLocal SSD 5 GB – 4 TBLocal SSD cache Up to 100 TB
Backups1 to 35 days (7 as Default)1 to 35 days (7 as Default)1 to 35 days (7 as Default)
Availability1 replica, no Read Scale-out, zone-redundant HA (preview), no local cache3 replicas, 1 Read Scale-out, zone-redundant HA, full local storageMultiple replicas, up to 4 Read Scale-out, zone-redundant HA (preview), partial local cache
In-memoryNot supportedSupportedNot supported
IOPS500 per vCore with max 7,0005,000 with max 200,000Multi-tiered architecture Effective IOPS will depend on the workload

Compute Tiers

Especially under the General purpose service tier with vCore model, we have two Compute tiers, Provisioned and Serverless.

ProvisionedServerless
Predictable workloadsUnpredictable workloads
Manual scalingAutomatic scaling
No pausePause the database
Pay for the fixed resources you choosePay only while database in use
Per hour billingPer second billing

For example, you will need to chose provisioned compute tier if your database has users connected most of the times having more usage of the compute resources and you cannot predict the traffic to the database. On the other hand, if you have predictable workloads with the specific timings of the users connecting to the database, you can use the serverless compute model.

You will be choosing the max amount of resources ahead of time and scale manually and you will be paying even if you are not using the resources in full. With the serverless, it is automatic scaling. You will be choosing the min and max number of compute resources so the automatic scaling happens only in between those min and max limits.

The advantage you have by using the serverless is to pause the database during the specific timings you choose. These are the timings when the users will not be connected to the database. Once the database goes to the pause mode, you will be only paying for the storage and not for the compute. If you are using Serverless option and do not place your database in auto pause mode, then you will be charged for the min amount of resources when users are not connected and charged per second basis on the resources you use during the activity. (Source)

For the provisioned compute tier, the billing is per hour basis where as for the serverless compute tier, billing is per second basis.

Summary:

In this Quick track of basics to Azure SQL, we learnt about the overview about the deployment options, purchasing models, service tiers, compute tiers and how they differ from each other. In the next blog posts, we are going to learn more about what Azure SQL has to offer.

Thanks for reading!

References:

  1. https://docs.microsoft.com/en-us/azure/azure-sql/database/purchasing-models?view=azuresql
  2. https://docs.microsoft.com/en-us/learn/modules/azure-sql-intro/
  3. https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale
  4. https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview
  5. https://www.youtube.com/watch?v=wcRb5RHriUc