Skip to main content

Flavors of SQL inside Azure (Azure SQL)

Just realized a few days ago that there are so many flavors of SQL inside Microsoft Azure that you need to be careful when you decide which one to use. In this post, I want to present the most important flavors of SQL that you can have inside Azure and to define a demarcation line between them.

1.0 SQL Server inside Azure VMs (IaaS)
The classical IaaS approach where customer needs to manage the VMs and the SQL Server instance that is running on top of Azure. The list of tasks that you need to do when you run your SQL instances on top of Azure VMs are the same as you would have the SQL Server instances inside your own data-centers. From license, to backup and restoration procedures, all of this needs to be managed by yourself.

2.0 SQL Database (PaaS)
The current offer of Azure related to SQL on the PaaS environment is more complex than you would expect. In this moment there are 3 options available, that cover most of the uses cases that you might have.
  • SQL Database (Singleton)
  • Elastic Pool
  • Managed Instance
A short overview on each of one can be found below.

2.1 Azure SQL Database (Singleton)
It’s part of SaaS category from industry perspective, but it falls in both categories SaaS and PaaS. It’s fully hosted and managed by Azure, client is responsible only for the content that is pushed inside the storage. All the maintenance and complex procedures to have a high SLA and good backup and restore procedures and fully managed by the platform.
You just specified the tier and the DB size and from that moment, you don’t care about anything else. You pay based on your use and you can scale up or down dynamically.
There is no management of infrastructure cost, being out of the box fault tolerance. Features like Point-In-Time restoration, geo-restoration and replication are included. It’s a good option when you start from scratch and you want to focus on your business value.
Each instance of database has its own virtual resources. It is important to remember that even if each tier has a specific number of resources reserved this does not mean that physical servers are dedicated to your own SQL database instance. Because of this, you cannot share available resources between different SQL database instances. In comparison with this, Elastic Pool allows us to share resources between database instances.

2.2 Elastic Pool
In comparison with Azure SQL Databases, elastic pool is a little bit different. Even if you still create SQL Database instances, you put all of them in the same pool, where all resources are shared. These means that all your DB instances that are part of the same Elastic pool are using and sharing the same CPU, IO and memory.
These is a model that works great if you have multiple DBs, where the pick of each instance is in different time periods. When you are using Elastic Pool don’t forget that all the DB instances are having the same tier and performance of database instances can be affected by the other databases that are in the same pool.
I compare Elastic Pool with farm with steroids. You can reserve computation power that it is shared between database instances, but without caring about backup, pooling and other stuff that are coming for free.
Just do not forget that things like SQL Agents, SSIS and Service brokers are not available inside Elastic Pool.

2.3 Managed Instances
This new service is part of Azure SQL family. In comparison with Elastic Pool you have all the features that you have on-premises (like SQL Agents, SQL CLR, cross-database querying), but in the same time you can use the full power of Azure SQL offering you out of the box support for automatic backups, high-availability and many more.
An interesting thing of Managed Instance is how you can configure it. You can put it inside a VNET, using his own private IP, without direct access to internet. It’s like having an Azure SQL Database with all the features inside a private network.
In comparison with Azure SQL Database, the DB size can reach 35TB without having to do any custom configuration. It is the right place when you start a migration from on-premises to Azure.

In conclusion I would like to share with you a comparison between SQL Server running on Azure VMs and SQL Database offered as PaaS (Azure SQL Database Singleton)


Popular posts from this blog

ADO.NET provider with invariant name 'System.Data.SqlClient' could not be loaded

Today blog post will be started with the following error when running DB tests on the CI machine:
threw exception: System.InvalidOperationException: The Entity Framework provider type 'System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SqlClient' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See for more information. at System.Data.Entity.Infrastructure.DependencyResolution.ProviderServicesFactory.GetInstance(String providerTypeName, String providerInvariantName) This error happened only on the Continuous Integration machine. On the devs machines, everything has fine. The classic problem – on my machine it’s working. The CI has the following configuration:

TeamCity.NET 4.51EF 6.0.2VS2013
It seems that there …

Entity Framework (EF) TransactionScope vs Database.BeginTransaction

In today blog post we will talk a little about a new feature that is available on EF6+ related to Transactions.
Until now, when we had to use transaction we used ‘TransactionScope’. It works great and I would say that is something that is now in our blood.
using (var scope = new TransactionScope(TransactionScopeOption.Required)) { using (SqlConnection conn = new SqlConnection("...")) { conn.Open(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = conn; sqlCommand.CommandText = ... sqlCommand.ExecuteNonQuery(); ... } scope.Complete(); } Starting with EF6.0 we have a new way to work with transactions. The new approach is based on Database.BeginTransaction(), Database.Rollback(), Database.Commit(). Yes, no more TransactionScope.
In the followi…

GET call of REST API that contains '/'-slash character in the value of a parameter

Let’s assume that we have the following scenario: I have a public HTTP endpoint and I need to post some content using GET command. One of the parameters contains special characters like “\” and “/”. If the endpoint is an ApiController than you may have problems if you encode the parameter using the http encoder.
using (var httpClient = new HttpClient()) { httpClient.BaseAddress = baseUrl; Task<HttpResponseMessage> response = httpClient.GetAsync(string.Format("api/foo/{0}", "qwert/qwerqwer"))); response.Wait(); response.Result.EnsureSuccessStatusCode(); } One possible solution would be to encode the query parameter using UrlTokenEncode method of HttpServerUtility class and GetBytes method ofUTF8. In this way you would get the array of bytes of the parameter and encode them as a url token.
The following code show to you how you could write the encode and decode methods.