Skip to main content

Azure SQL Database - Elastic Scale, perfect solution for sharding

There is a great news for people that use Azure SQL Database. Elastic scale is available in preview phase. I expecting this feature from some time and now I’m happy that is available to us.
The biggest advantage of a cloud solution is scalability and pay as you go. You pay a resource only when you need it without paying the period of time when you don’t use it. Over this features, a service like automatically scaling can be added very easily. This is applicable when we are talking about web sites, web/worker roles where things are not so complicated.
To be able to scale the data-tier or an application you need to be able to use sharding (joining multiple resources, in our case splitting a big database on multiple databases). This feature was enabled on Microsoft Azure in a very smart way and without adding additional costs to the end user. You will pay the Azure SQL instances that you use. For each shard you will need to use a new instance of Azure SQL Database.
Elastic scale is enabled from a simple template that is created using Visual Studio. From this project, we can control and manage the shards, shard mapping, query multiple shards and adding additional one if needed.

When we should use sharding?

  • The total data that we want to store into one database is too big. 
  • A part of our database should reside in a specific geographical location (a part in Europe, a part in USA)
  • The number of transactions that are executed over a single database hit the maximum capabilities of one instance
  • There is a need of tenant physical isolation

Key features available in this moment

  • Shard Map Management – It is used to create new shards, manage configuration, specify the key range for shards
  • Data Dependent Routing – Enable to create connection to a specific shard and route that given request
  • Shard Elasticity – Enables us to scale vertical (by control the type of SQL Azure that we are using) or horizontally (adding more shards/removing shards)
  • Multi-Shard Queries – When a SQL hit multiple shards, the system will be smart enough to execute the request on multiple shards and merge the result for us.
  • Split-Merge Service – When we increase, decrease the number of shards, the system will automatically balance the sharding data distribution    

How to get a db connection
To get a connection to a specific database you can specify the ID of the entity. In this way you will get a connection directly to the database where your entity is stored.
ShardMapManager shardManager = ShardMapManagerFactory.GetSqlShardMapManager(
RangeShardMap<int> fooShardMapping = shardManager.GetRangeShardMap<int>("fooMapper");
SqlConnection fooDbConnection = fooShardMapping.OpenConnectionForKey(fooId, Configuration.GetCredentialsConnectionString(), ConnectionOptions.Validate)
ShardMapManager can be created only once time per application and is used to execute queries on the appropriate database. In this way we don’t need to track the database connection. You can imagine this manager as a connection pool where all connection to databases are tracked.

This is a great feature, that can save your life. Now, sharding can be configured very easily by anyone.


Post a Comment

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.