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(
     connectionString, 
     ShardMapManagerLoadPolicy.Lazy);
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.

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

Comments

  1. This comment has been removed by a blog administrator.

    ReplyDelete

Post a Comment

Popular posts from this blog

How to check in AngularJS if a service was register or not

There are cases when you need to check in a service or a controller was register in AngularJS.
For example a valid use case is when you have the same implementation running on multiple application. In this case, you may want to intercept the HTTP provider and add a custom step there. This step don’t needs to run on all the application, only in the one where the service exist and register.
A solution for this case would be to have a flag in the configuration that specify this. In the core you would have an IF that would check the value of this flag.
Another solution is to check if a specific service was register in AngularJS or not. If the service was register that you would execute your own logic.
To check if a service was register or not in AngularJS container you need to call the ‘has’ method of ‘inhector’. It will return TRUE if the service was register.
if ($injector.has('httpInterceptorService')) { $httpProvider.interceptors.push('httpInterceptorService&#…

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 http://go.microsoft.com/fwlink/?LinkId=260882 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 …

[Post-Event] Codecamp Conference Cluj-Napoca - Nov 19, 2016

Last day I was invited to another Codecamp Conference, that took place in Cluj-Napoca. Like other Codecamp Conferences, the event was very big, with more than 1.000 participants and 70 sessions. There were 10 tracks in parallel, so it was pretty hard to decide at  what session you want to join.
It was great to join this conference and I hope that you discovered something new during the conference.
At this event I talked about Azure IoT Hub and how we can use it to connect devices from the field. I had a lot of demos using Raspberry PI 3 and Simplelink SensorTag. Most of the samples were written in C++ and Node.JS and people were impressed that even if we are using Microsoft technologies, we are not limited to C# and .NET. World and Microsoft are changing so fast. Just looking and Azure IoT Hub and new features that were launched and I'm pressed (Jobs, Methods, Device Twin).
On backend my demos covered Stream Analytics, Event Hub, Azure Object Storage and DocumentDB.

Title:
What abo…