Stairway to Microsoft Azure SQL Database Part 4 : Azure SQL server , an overview

In the last few posts, we were discussing about database deployment model and purchase model. Out of the three deployment models, managed instance works almost like on-premises SQL server and hence we are not going to discuss much about the managed instance. On the other side, a single database and elastic pool works a completely different ways.

while creating a single database or elastic poor, you need to attach those into the Azure SQL server. In other words, you need to create a SQL server before creating the SQL database. While searching for services in the Azure portal, you can search for 'SQL database' and 'SQL server'. In this post, we are going to discuss about the SQL Server, which is a logical container for your single Azure databases and Elastic Pool.

As per the Azure documentation, "a SQL server is a logical construct that acts as a central administrative point for a collection of databases". At the server level,  you can administrate the following components:

• Logins
• Connectivity 
• Firewall rules 
• Auditing rules 
• Threat detection Policy 
• Auto Failover group
• Transparent Data Encryption 
• Auditing 
• Backup Management 

When you create an empty SQL Server in Azure, it will not cost you anything until you create a single database or elastic pool under this  SQL server. To create an empty SQL server, search for "SQL Server"  in the Azure portal, and follow the instructions. While creating the SQL server, it will ask for a user name and password. This is the only SQL login that you can create from the Azure portal. This login has the highest privilege in the server and can access all databases in that server without providing explicit permission in each database. Azure SQL server does not support window authentication but supports Azure active directory authentication. For enabling the Azure active directory authentication, you need to connect your server with Azure AD. You can achieve this by setting the Active directory Admin from the Azure portal. You can find this option under the Settings section of the server. This can be an individual account or a group. This account also will have the highest privilege and can access all the databases under that server without providing explicit permission.  These are the only two logins that you can create from the Azure portal. There is no option to change the SQL login once you have created it but you can change the Active directory admin at any time. These are the only two logins will have access to all the databases created under this server. There is no option to create server wide login which will have access to all the databases under this server. Still, you can create server wide login and can explicitly create the corresponding user in each database like we used to do in the on-premise installation. 

Azure SQL database provides two types of user :

Login and user: This is the same as we used to do in on-premise installations. Create a login and create corresponding user in each database. For example, connect to master database 

create login njohn with password ='Password@123'

--Connect to DB1

create user njohn from login njohn 

--Connect to DB2 

create user njohn from login njohn 

In this case, the login information is stored at the server level and the password remains same across all databases. While copying the database DB1 and DB2 to another server, you also need to copy the login and need to map the SID properly.

Contained user: These are the users created under the database without having the logins. In this case, the user informations are stored in the database level. After the restoration of the databases, DB1 and DB1 to another server, we don't have to copy the login information separately as the user password and other information are stored in the user database. In this scenario, the same user can have different passwords to connect with each database. For example 

--Connect to DB1

create user njohn with password ='Password1' 

--Connect to DB2 

create user njohn with password ='Password2'

When you create the SQL server, by default a virtual master database will be created. This is the only system database that you can see in the Azure SQL server. If you look into this database, you can see two additional database role added with Azure SQL server :

dbmanager: Members of this database role can create and delete the databases. When a member of the dbmanager role creates the database, becomes the owner of the database, and will have full access to that database as dbo. The members of this role will not have access to the database which is not created by them (owner) unless you explicitly created users under the database for those logins like we do in the on-premise installation. This role exists only in the master database and only the users of the master database can be added into this role. The ideal method is, create a login and create a user for that login and add that user to the dbmanager role. Contained database users in the master database also can be added to the dbmanger role, but they will not have permission to create new databases.

 

loginmanager:  can create and delete logins in the master database. Note that, members of this role does not have the permission to create users in the master database. 

As mentioned earlier, the Azure SQL server is a logical construct and does not represent a specific VM or hardware like on-premise installation. The databases associated with an Azure SQL server, maybe running in different VMs which is not visible for us. In our on-premise installation, the SQL server is installed on  VM and all databases created in that database server will be running on the same VM. Whereas Azure SQL server is a logical server and can deploy the databases under that server in multiple VMs. The below diagram helps you to understand the concept in a better way.

 

We have learned in our earlier post, each Azure SQL database will have one or more standby replica for high availability depending on the service tier. The database name marked in red are standby replica. Azure SQL inherently multi-tenant and it needs to share its resource with multiple clients. You can see one extra database, DB5 which is not there in our on-premises diagram. When you connect to Azure SQL server, "mysqlserver.database.windows.net", based on the database need to be connected, it will redirect the connection to data node hold the primary replica. This is the reason why we need to specify the database name while connecting through SSMS and not allowing us to change the database context using the USE statement. To change the context of the database, it needs to reroute the traffic to different data nodes. If we are not specifying the database name, it will connect to the virtual master database. If the 'datanode1' goes down for some reason, DB1, and DB2 comes online as the primary in Datanode2 and standby replica will get created in another data node. During this time, the connection to these two databases will be broken and the user will get an error message. To overcome these transient errors and to give a smooth experience to the application users, Microsoft suggests to implement retry mechanism for application connecting to Azure SQL. This is also beneficial to 

• Handle the errors happens while azure perform the maintenance or patch update.  Azure SQL Database is a managed service and Azure performs the regular patch and version update. User does not have control over the date and time of these maintenance windows.
• When we scale up the SQL database, there is a possibility to failover the database to another node due to a lack of resources in the current node. In that scenario, the existing connection will be terminated.

In our next post, we will discuss about connection architecture and different settings available under the "firewall and virtual network"

Stairway to Microsoft Azure SQL Database Part 3 : Purchase Models DTU vs vCore continue

In the last post, we have discussed the DTU purchase model. Azure also provides another purchase mode which is called vCore. vCore purchase model available with all three deployment models (Managed instance, Single database, and elastic pool). 

vCore purchase model provides :

• Higher compute, memory, IO, and storage limit.  
• Better control over opting for the hardware configuration.
• Option to bring your own license using the Azure Hybrid Benefit(AHB) and to reserve the instance to reduce the cost.
• Flexibility to choose the compute and storage independently.
• Storage and compute charged separately. In the DTU model ,limited amount od storage bundled with the computing power.  Storage allocated above that is charged separately.

Service Tier: Like in the DTU model, the vCore model also provides multiple service tier to match with your performance requirements and budget. vCore purchase model provides the following service tiers.

• General Purpose 
• Hyper-Scale
• Business Critical

General Purpose :

• Offer Budget-oriented, balanced and scalable compute and storage option.
• Suited for general workload.
• Available with all three deployment model (Single, elastic and managed)
• Option to select 2 to 80 vCore. The options are 2 ,4 ,6 ,8 ,10,12,14,16,18, 20,24,32,40 and 80 vCore
• Storage upto 4 TB (0.12 USD per GB). 
• Remote storage 
• 500 to 20000 IOPS
• 2 to 10 ms latency 
• For high availability, it provides one replica.
• No read replica.
• 7 to 35 Backup retention 

Hyperscale :

• Available only for single database deployment model
• Provide highly scalable storage up to 100TB 
• For rapid read scale-out deployment, provide up to 4 read replica 
• Read replicas are charged.
• Option to select compute power from 2 to 80 vCore (options are the same as in general-purpose) 
• Use local SSD storage.
• Snapshot based backup in Azure remote storage. Restore use these snapshots for fast recovery. 
• Backup/restore does not depend on the size of the database and does not affect the IO or compute as it is performed using the snapshot. Hence backup and restorations are instantaneous.
• For example. 50 TB database can be restored in less than 15 minutes. You can watch the video here 
• Depending on the workloads, IOPS between 500 to 204800 with 1 to 10 ms latency.
• Most suited for databases that will not fit into the 4TB storage limit of general-purpose and business-critical service tier.
•  Hyperscale database engines use distributed function architecture and use the following components (we will discuss the architecture of hyper-scale in a different blog post). This architecture is different from the existing database engine architecture 
• Compute 
• Page Server
• Log Service 
• Aure storage 

Business Critical  :

• Available with all three deployment model (Single, elastic and managed)
• Option to select 2 to 80 vCore. The options are 2 ,4 ,6 ,8 ,10,12,14,16,18, 20,24,32,40 and 80 vCore
• Offers business applications the highest resilience to failures by using several isolated replicas.
• It keeps 4 replicas for better high availability 
• To offload the read workload from the writable replica, provide read replica without additional charge.
• Use local SSD storage for better IO performance.
• Provides up to 4 TB storage with 1 -2 ms latency (0.25 USD per GB)
• 5000 to  204800 IOPS
• 7 to 35 days backup retention.

Compute Tier: Under the service tier,  azure SQL server provides the compute tier. For managed instance and elastic pool deployment models, azure provides only provisioned compute tier and this option will not be available to choose. For the single database deployment, there is one more additional compute tier called Serverless. Let us see the difference between these two compute tier:

Provisioned : 

• compute tier is provisioned through out the lifecycle of elastic poo/ single database/managed instance and billed hourly basis.
• There is no change in the billing based on the workload on the database.
• Suitable for regular usage patterns with higher average compute utilization over time.
• Compute scaling should be done manually (increasing the vCore)

Serverless :

• Suitable for Intermittent, unpredictable usage with lower average compute utilization over time.
• Automatically scale up the compute based on the workload and billed per second.
• Automatically pause the database if there is no activity for a preconfigured time (Auto pause delay)  and start automatically when the wake-up events happen. During this period compute is not charged.
• While provisioning, we need to define the minimum and maximum vCore. 
• Based on the workload, the compute tier is scaled up to the max vCore defined. Compute is billed per second.
• Auto pause delay is a configurable parameter that defines the required inactive period before pausing the database. This can vary from 1 hour to 7 days. For example, if we define the auto-pause delay as 2 hrs, then if there is no activity in the database for 2 hours, then the database will go to pause state and will not be charged for computing until the next wake up event. Till that point, only the storage cost will be charged.
• The minimum and maximum vCore are configurable parameters that define the range of computing power available for the database. The maximum vCore can vary between 1 to 16 vCore. (1,2,4,6,8.1,10,12,14 and 16 are possible options) . The minimum vCore can vary from 0.5 vCPU to maximum vCore configured. Each vCpu provides 3GB of memory. If the workload requires memory more than that will be charged.
• Cost
• Compute cost and storage costs are charged separately. Storage cost remains the same throughout the life cycle unless you scale up the storage.
• When the compute usage is between the min vCore and max vCore configured, compute cost is based on vCore or memory.
• When the compute usage is below the min vCore setting, it will be still charged for min vCpu/ Memory.
• For the duration of the paused state, Computing is not charged. Only storage will be charged.
• Auto pause and auto-scaling
• When the database is in a paused state, the first wake up event (example connection request)  fails with the error message "database is unavailable" with error code 40613. It will take approximately one minute to resume the operation. Applications that are working with a serverless database should have mechanisms to handle this error message and retry the connection after a minute.
• When the load on the database increases, azure automatically scales up the compute / memory up to the max vCore configured. while scaling up the compute, at the end of the process all connections to the database are dropped. The applications which use the serverless database should have a mechanism to handle these error and try to reconnect after a specific time to provide smooth user experience to the end-users. 
• Billing 
• Use per second billing.
• Each vCore in action provides 3GB memory. If the workload uses more memory than the memory bundled with the vCore, billing will be based on the memory. For example, for a specific time interval, the load on the database is using  2 vCore and 10 GB  memory. In this case, billing will be based on the memory as the memory utilization is above 6 GB (2vCore*3 GB). To derive the number vCore to be billed from the memory utilization, memory utilized will be divided by 3 (3GB for each Vcore). In our case, it will be billed for 10GB/3 = 3.33 vCore. The below table gives you a better understanding of the billing. Let us consider one database, that is provisioned with 3 vCore as minimum vCore and 16 as maximum vCore.The auto pause delay is configured as 2 hrs. Each vCore seconds is charged  at 0.000145 

The total vCore seconds are 389952 and it will cost 56.54 USD (0.000145 USD * 389952 vCore seconds) for the workload mentioned in the above table which spread across 24hrs.

• Best suited for :
• Single database with intermittent and unpredictable usage patterns.
• The database workload is only during specific intervals. So after the auto-pause delay, the database enters into stop state and not charged for the compute.
• Workload is not sensitive to the response time and the application is capable fo handling the errors while resuming the service and auto-scaling

Apart from connecting to the database, there are many other events that resume the database from a paused state. You can find those events in the Azure documentation page 

Hardware Configuration Azure provides multiple hardware options to choose from under the service tier. These options differ from region to region. Commonly available hardware options are 

• Gen4 
• Provide 7 GB memory for each vCore.
• Approaching the end of life by Jan 2023
• Gen5 
• Has new generation hardware
• Has network acceleration and provide better IO performance on remote storage  (General purpose service tier)
• It uses faster local SSD disk than Gen4
• M-Series (preview)
• New Memory-optimized hardware 
• Provide 29GB per vCore.
• It provides up to 128 vCore.
• In full capacity memory can be nearly 4 TB (128 vCore X 29 GB per vCore).
• Fsv2-series (Preview)
• Compute-optimized hardware.
• Low CPU latency and high clock speed for the most CPU demanding workloads.
• Can deliver more CPU performance per vCore than Gen5.
• The 72 vCore size can also provide more CPU performance for less cost than 80 vCores on Gen5.
• provides less memory and tempdb size per vCore than other hardware. so workloads sensitive to those limits may want to consider Gen5 or M-series.

The below diagram provides you a visual representation of vCore purchase model options for Single database and Elastic pool.

vCore Purchase Model

Managed Instance deployment model also provides General Purpose and Business Critical service tier. Under the service tier, it provides Gen4 and Gen5 hardware. 

Each combination of these options has many other characteristics like tempdb space allocated, log space allocated, IOPS, no of concurrent session, log write throughput, etc.   All those detailed information can be found in the following Azure documentation.

• Managed instance resource limit 
• Single Database instance resource limit
• Elastic Pool resource limit

I hope, this post helped you understand the vCore purchase mode in detail.  

Stairway to Microsoft Azure SQL Database Part 2 : Purchase Models DTU vs vCore

In the last post, we have discussed about different deployment options available with Azure SQL server. I hope you got a good understanding of various deployment models. In this post let us discuss the purchase models available with each deployment model.

Azure SQL database provides multiple purchase models based on the deployment model to accommodate your performance requirements and budgets. At a high level, these purchase models are divided into two:

• DTU or eDTU purchase model
• vCore purchase model

Each of these purchase model provides multiple service tier for more customization of your performance requirements and budgets.

• DTU or eDTU 
• Basic
• Standard
• Premium  
• vCore 
• General Purpose
• Hyperscale
• Business Critical

DTU and eDTU purchase model 

The database transaction unit (DTU) is a specific composition of compute power, memory, storage, and IO. This purchase model provides us all the resources as a bundle which includes a specific amount of compute power, storage, and IO. It provides the simplicity and full control of your budget. This purchase model available with single database and elastic pool deployment model. As mentioned earlier, Azure provides multiple service tier to match our performance requirements and budget.

• Basic tier: 
• Suitable for very low CPU intensive workload. 
• Provides 5 DTU with 2GB of storage. 
• Each DTU provides 1-5 IOPS. 
• 35 days backup retention. 
• Each DTU costs 1 USD per month. 
• Suitable for initial design and developments. 
• Standard tier: 
• Suitable for low and medium CPU intensive workload. 
• It provides a DTU range between 10 and 3000 DTU with 250 GB storage. 
• Additional storage up to 1 TB with additional cost. To go beyond 250 GB, we have to opt for 100 or more DTU. 
• 35 days backup retention. 
• Each DTU provides 1-5 IOPS. 
• Each DTU costs 1.5 USD per month.  
• The range of DTU in this tier is divided into 9 compute size: S0 10 DTU), S1 20 DTU, S250 DTU,S3 100 DTU, S4 200 DTU, S6 400 DTU, S7 800 DTU, S9 1600 DTU, and S12 3000 DTU. There is no option for us to randomly select the DTU in the range of DTU.
• Premium Tier: 
• Suitable for medium and high CPU and IO intensive workload. 
• Provides DTU range between 125 and 4000 with a storage of 500 maximum to 4 TB depends on DTU.
• Each DTU unit provides 25 IOPS. That makes it suitable for IO intensive workload. 
• It also provides better IO latency (2ms for read and write) compared to basic and standard (5 ms for read and 10 ms for write).
• 35 days backup retention. 
• Support column store index and in-memory OLTP
• DTU range divided into multiple compute sizes. p1 125 DTU,p2 250 DTU ,p4 500 DTU, p6 1000 DTU p11 1750 DTU, p15 4000 DTU 
• Premium tier also supports read scale-out to load balance the read workload with no extra cost. As part of high availability architecture, each database in the premium service tier is automatically provisioned with multiple secondary replicas. These secondary replicas are provisioned with the same compute size of the primary replica. Enabling this feature, allow you to load balance your read-only workload using one of the read replica instead of read-write replica. This is very useful for read-intensive application which can segregate their read and write operations by passing the readintent=true property in the connection string. 
• Premium tier also supports Zone redundancy. As part of high availability architecture, each database in the premium service tier is automatically provisioned with multiple secondary replicas in the same data center. This architecture helps to protect us from the server failure. By enabling the Zone redundant feature, the replicas are placed in different availability zones, it makes the databases resilient to the data center outage 

For more accurate details of each service tier, refer the following Azure documentation :

• Service tiers in the DTU based purchase model
• Resource limit for single databases using the DTU purchase model 

While using the single database deployment model, the DTU purchase model comes into the picture. In the same way, for an elastic pool deployment model, the eDTU purchase model is used. eDTU purchase model also provides different service tiers like in the DTU model but with different limits. For the details of each service tier, refer to the Azure documentation. As you know, in the elastic pool deployment model, the resource is allocated at the pool level and each database shares the resource. To avoid one database consuming all the resources, there is a couple of parameter settings in the elastic pool level.

• Max eDTUs per database: This is the maximum number of eDTU that any database in the pool may use if available based on utilization by other databases in the pool. Max eDTU per database is not a resource guarantee for each database. if the workload on these databases are spread across time, we can over commit the max eDTU per database. For example, we have an elastic pool with 1000 eDTU and 5 databases attached to that. Out of that 2 of them are reporting data and most load comes to those two at night. The other two databases are part of the OLTP system will have varying workload but the peak will be between 10 AM and 5 PM. The fifth database is an internal database most of the load on this database will be between 9 AM and 12 PM. In this scenario, we can safely set max eDTU per database to 300 or 400 even if the total goes beyond the 1000 eDTU. 
• Min eDTUs per database: This is the guaranteed minimum number of eDTU for each database in the pool.  This can set to 0 and that is the default value. This value multiplied by the number of databases in the pool should not exceed the eDTU allocated to the pool. To set the value of this parameter to 100 of an elastic pool which has 20 databases, we need to allocate a minimum of  2000 eDTU to the elastic pool.
• Max storage per database: This is the maximum size of any database attached to this elastic pool. None of the databases can grow beyond this limit. Even if we set this value to a higher number which exceeds, (no.of database X  Max storage per database), the total available storage space of the pool storage limit, it will never allow all database storage to grow more than available storage space fo the pool.

Each service tiers in the elastic pool has a limit on the individual database resource utilization. The below table explains those limitations:

In case if you are migrating your workload from your on-premise installation of SQL Server to Azure SQL and you are not sure how much DTU need to be allocated and which service tier to be provisioned, DTU calculator comes handy. You need to provide values of the following performance counters :

• Processor - % Processor Time
• Logical Disk - Disk Reads/sec
• Logical Disk - Disk Writes/sec
• Database - Log Bytes Flushed/sec

I hope with this you got a good understanding of the DTU and eDTU purchase model.  In the next post, we will cover the vCore purchase model.

Stairway to Microsoft Azure SQL Database Part 1 : Deployment Options

Recently I started working with Azure SQL Database. It took some time for me to understand the concepts of the Azure SQL database. Earlier I have experience in AWS RDS for SQL server, but in Azure it works in a little complex way. Once you understand the different offerings, security, deployment model, etc, it is is easy to provision the resources as per the requirements.

Through a set of articles, I will try to explain the Azure SQL database in a very concise way. All this information is available in Azure documentation. For the granular level of information, always refer to Azure documentation.   

Microsoft Azure database is a fully managed platform as a service (PaaS). Azure provides three different deployment options to cater to the different needs of the customer.

• Managed Instance 
• Single Database 
• Elastic Pool

Let us look into the details of each deployment model :

Managed Instance: Managed instance is one of the most suitable deployment option for those who are moving to cloud from an on-premise server. This deployment model supports most of our on-premise database features.

• Managed instance does not provide access to the Operating system.
• It does not allow you to specify the version of the SQL Server. Managed instance always runs the latest stable SQL engine version, which is equal to or higher than the latest available RTM version of SQL Server. 
• Supports 
• SQL Agent service 
• DB mail with external SMTP server
• Service broker
• SQL Agent service 
• Transactional replication
• Change Data Capture (CDC)  
• CLR creation from binary (Not using the assembly file)
• linked server 
• Managed instance supports SQL Server Integration Services (SSIS) and can host SSIS catalog (SSISDB) that stores SSIS packages, but they are executed on a managed Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF)
• To choose the time zone
• Not yet supporting the file stream or file table.

On top of that, managed instance support all PaaS features like :

• No separate purchase of infrastructure and managing the underlying hardware of the database server.
• Automated patching and version upgrade. There is no option to control the maintenance window of patching or upgrade. To handle the connection error during the maintenance window, you need to implement the retry mechanism in your code.
• No headache of scheduling and monitoring of backups. Automated backups are available 

Migration to the managed instance is much easier as this supports restoring from the native backup created from the on-premises server. To restore the backup in the managed instance, the backup should be available in Azure storage account and should use RESTORE DATABASE FROM URL. Managed instances also allow the customer to take COPY_ONLY backups which do not break the azure automated backup chain. 

Managed instance is placed inside the Azure virtual network and in a dedicated subnet. This provides :

• Secure private IP address.
• The ability to connect an on-premises network to a managed instance.
• The ability to connect a managed instance or another on-premises database server through a linked server.

Managed instances also provide public endpoints. Public endpoint provides the ability to connect to the Managed Instance from the Internet without using a VPN. Access is disabled by default unless explicitly allowed. We need to explicitly whitelist the IP address to access through the public IP address.

managed instance provides two flavors of compute and storage :

• General Purpose: This supports most of the production workload. Support up to 80 vCore and 8TB fast storage
• Business Critical: For IO intensive and compute-intensive workload.Support up to 80 vCore and 4 TB super fast storage 

Managed instance also support to bring your own license with software assurance (SA). Considering all these, managed instances are the best option for lift and shift of your existing workload to the cloud. Note that, if you need direct access to OS/filesystem or dependent on a specific version of SQL server or required specific features that are not supported in the Azure SQL server then Azure Paas database service is not suitable unless you resolve those dependencies.

Single Database:  A single database is a fully-managed database as a service (DbaaS) under the Azure PaaS offering. This deployment option is most suited for modern application development like microservices. The single database deployment option creates a database in Azure with its own set of resources and is managed via a SQL database server. With a single database, each database resources are isolated from each other. A SQL database server is completely different from the SQL server that we used to have in the on-premise installation. In this context of azure DbaaS, an SQL database server is a logical construct that acts as a central administrative point for one or more single databases.

• Single database does not provide access to the Operating system.
• It does not allow you to specify the version of the SQL Server. Single database always runs the latest stable SQL engine version, which is equal to or higher than the latest available RTM version of SQL Server. 
• Does not Supports 
• SQL Agent service 
• DB mail service
• Service broker
• Replication (can be push subscriber )
• Change Data Capture (CDC)  
• CLR creation 
• linked server 
• To choose the time zone
• File Stream and File table

In short, Single database support only database-level features and does not support server level features.  To migrate your existing workload to a single database,  there are three primary methods.

• Export to a BACPAC file and import the BACPAC file into azure single database. (A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from a SQL Server database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation). This is a time-consuming process and requires downtime. The time required to complete the migration depends on the size of the database.
• Using transactional replication: Azure single database can be configured as a subscriber of your on-premise database publication. There is no UI available.  Need to configure through T-SQL. Once the complete data synched with the source server, on migration day cut down the traffic to your on-premise database and after synchronizing the data, point your application to the new Azure database 
• Use the Azure DMS (Data Migration service ).

Azure database server does not allow you to host inside the Vnet. By default, the access is through the public endpoint.  All the traffic through this endpoint is blocked and you need to explicitly whitelist the required IP address to connect to the database server through this endpoint. There is no option to disable the private endpoint but as mentioned earlier, no one can connect through public endpoint unless you provide the access by whitelisting the IP addresses. You can enable the private endpoint by integrating virtual network private endpoints. Private endpoint helps to connect from the same VNet or peered VNet in the same/cross-region or from on-premises using a VPN.  Note that virtual network private endpoint will be billed separately 

Azure Database provides two types of purchase :

• DTU base
• vCore base

Users can select the option based on their workload. In the vCore model, azure allows you to bring your own license. We will discuss these options in another post.

Single database support PaaS features like :

• No separate purchase of infrastructure and managing the underlying hardware of the database server.
• Automated patching and version upgrade. There is no option to control the maintenance window of patching or upgrade. To handle the connection error during the maintenance window, you need to implement the retry mechanism in your code.
• No headache of scheduling and monitoring of backups. Automated backups are available 

Elastic Pool: Elastic Pool Is a set of single databases with a shared resource pool. In a single database, each database will have its own dedicated resource. In the elastic pool, resources are configured in the pool level and each single database connected with that pool shares the resource of elastic pool. SQL Database elastic pools are a simple solution for managing and scaling multiple databases that have varying and unpredictable usage demands. For example, let us assume you are providing some SaaS solutions to multiple clients in different time zone. Each customer data are stored in independent databases. In this scenario, the resource can be utilized in a much better way by adding those databases to the elastic pool instead of allocating dedicated resources to each individual customer database. Apart from this, the rest of the characteristics are the same as the Single database mentioned in the above section.

Hope the below pictorial representation helps you to understand different deployment option available in Azure DbaaS. 

Disks performance in Azure

In our earliest post, we have discussed in detail about the factors that affect the disk performance and different types of disk offerings in the AWS platform. In this blog post, let us go through different types of disk available in the Azure Platform.

Azure offers three types of disk :

• Standard SSD
• Premium SSD 
• Ultra Disk

Apart from this, Azure provides a standard HDD. These disks are low-cost disk support for VMs running low latency workload. Standard HDD offers write latencies under 10ms and read latencies under 20ms.

Before describing the characteristic of each disk type, let us go through some common facts:

• I/O operation less than or equal to 256 KiB of size is considered as a single IO operation. AWS supports the IO size of 128 KiB for each IOPS. In azure, a disk with 1000 IOPS can attain maximum throughput of 1000X256=256000KB=250MiB/sec, but Azure put max throughput limit for each disk type and that will be less than or equal to the possible max throughput (IOPS X 256 KB)
• Azure provides predefined disk size except for ultra disk. Users are allowed to create custom size disks but will be charged at the rate of next available size. For example, if the user creates a disk with 200 GB, he will be billed for the next available disk size offered by Azure. This is not applicable to Ultra Disk. With ultra disk, users can select the custom size and charge for that size.
• Apart from the disk level maximum limit of  IOPS and throughput, Azure implements throttling at the VM level for IOPS and throughput. This cap works on the aggregate value of  IOPS and throughput of all disk attached to the VM.

Standard SSD: Standard SSD disks are low-cost SSD provides better latency than the standard HDD. Standard SSD disks are best suitable for OS disk, web servers, and low IOPS application servers. Azure offers fourteen different disk sizes ranging from 4GB to 32 TB with IOPS ranging from 500 IOPS to 6000 IOPS and throughput range between 25MiB/sec to 750MiB/sec. Actual IOPS and throughput may vary depending on the traffic pattern. As far as I understood from the documentation, these are the maximum IOPS and throughput but not guaranteed always. For exact details of size and  IOPS refer to azure page 

Premium SSD : Azure premium SSD disks are high performing and low-latency disks designed to support IO-intensive workloads. Premium SSDs can only be used with the VM series that are compatible with premium SSD disks. Azure offers fourteen different disk sizes ranging from 4GB to 32 TB with IOPS ranging from 120 IOPS to 20000 IOPS and throughput range between 25MiB/sec to 900MiB/sec. The major difference from the standard SSD, premium SSD provides more consistent performance in terms of IOPS and throughput. For exact details of size and  IOPS refer to azure page 

Apart from this, premium SSD supports bursting for disk size up to 512 GB. These disks can burst up to 3500 IOPS and throughput up to 170 MiB/sec. Bursting is based on a credit system. Credits are automatically accumulated when disk traffic is below the provisioned performance and credits are automatically consumed when traffic bursts beyond the provisioned performance. The max burst limit defines the ceiling of disk IOPS and throughput even if you have accumulated burst credits. This helps to support random unpredictable spikes of the IO workload for disk up to 512 GB in size.

For example, a disk with a size of 128 GB has provisioned 500 IOPS and 100MiB/sec throughput. This disk supports the burstable performance of 3500 IOPS and 170 MiB/sec for maximum of 30 minutes.  The burstable limit is the same for all the disk size. When the disk workload goes below the provisioned IOPS and throughput ( 500 IOPS and 100MiB/sec), it starts accumulating the burstable credits and the same will be used when the workload goes beyond the provisioned IOPS and throughput. For example, if the actual workload is 200 IOPS and 80MiB/sec in the past one-second interval, then the unused 300 IOPS and 20MB are accumulated to the burst bucket of the disk.

Disk bursting will never go beyond VM throttling on  IOPS and throughput. Irrespective of burstable credits available, the disk performance never goes beyond the burstable limit (1700 IOPS and 170MiB/sec). For example, even if the disk has 10,000 IOPS  in the burstable credit bucket, the disk can not support more than the max burst limit of 3500 IOPS. In the same way, if the disk is attached to a VM with an IOPS limit of 1000 IOPS and throughput limit of 150 MiB/sec, the disk can't burst beyond the VM limit.

When a VM starts with the burstable disk, it starts with a full burst credit bucket. This helps the booting speed of the VM if the boot disk is a burstable disk . A burstable disk  credit bucket can be in any of the three states :

• Accruing: When the disk traffic is less than the provisioned IOPS and throughput. 
• Declining: when the disk traffic is using more than the provisioned IOPS and throughput. The burst traffic will independently consume credits from IOPS and throughput.
• Remaining Constant: When the disk performance is exactly at the provisioned IOPS and throughput level.

Ultra Disk : Azure ultra disks provide high throughput, high IOPS and consistent low latency disk storage for IO intensive mission-critical application. Ultra disk can only be used as a data disk(not as OS disk). Ultra disk supports the disk size ranging from 4 GB to 64 TB. When we configure the ultra disk, we can customize the size and performance variables (IOPS and throughput) 

• Ultra disk support 300 IOPS per GB with a maximum limit of 160000 IOPS per disk. For example, an 8GB disk can be configured to support a maximum of 2400 IOPS. All disk above the size of 533 GB, support only  160K IOPS. (533*300=159900)
• The minimum configurable IOPS for an ultra disk is 2 IOPS per GB with a baseline of 100 IOPS per disk. for example, a 32 GB disks can be configured minimum of 100 IOPS instead of 64 IOPS. A 1000 GB disk minimum configurable value for IOPS is 2000
• To get the benefit of the provisioned IOPS, ensure that the VM IOPS limit is higher than the IOPS configured for the disk. 
• The throughput limit is 256KiB/sec for each provisioned IOPS with a maximum of 2000 MiB/sec per disk. The minimum throughput per disk is 4KiB/sec for each provisioned IOPS with a baseline of 1MB/sec.
• Ultra disk supports adjusting the performance variables (Throughput and IOPS) at runtime without detaching disk or restarting the VM. After changing the performance variable, it will take a maximum of one hour to get it effective.

There are lots of restrictions in using the ultra disk. Refer to the azure website for up to date information. 

Disk Stripping for better performance: Use multiple disks and stripes them together to get a combined higher IOPS and throughput. For example, in premium storage , P40 (2TB) and P50(4TB) provide the same  IOPS (7500) and throughput (250MiB/sec). Instead of going for P50 disk, we can allocate two P40 disks and stripes them in OS level to get combined IOPS of 15000 and 500MiB/sec throughput. 

Hope this explains well about the different types of disk available in the Azure portal and keep these points in mind while allocating disk for better performance. In the next blog, we will go through different parameters to measure the performance of the disk.