CODEWITHSUNDEEP
c#sqlasp.net-mvcazureazure-elastic-sharding
Eric
Eric

Reputation: 258

Azure Database Horizontal Sharding the best solution for Multi-tenant C# asp.net application

We have an Enterprise application that we are in the midst of moving to the cloud. Up to this point our application has had multiple users accessing information through a database located on their main server.

We are trying to figure out what the best set up would be to move the software to the cloud. I have done some research on Database Sharding for azure Sql Database.

What we are trying to accomplish is when a user goes to our website they would have the option to log in or register for a login. If the user is new to the software we would want to create a new Database for this users company that used the Schema of our main database. Using the Database sharding, I believe this would be called adding a shard and the user would then be added to our ShardMapManager and given a shard key which would allow them to read and write data to the newly created Database(Shard). Inside the software the user could add more users which would give them the same shard key as the admin user.

Is this the best possible solution out there for software that needs to have its own database for each company due to privacy, or is there a better solution for what we are trying to do.

Also if I am thinking of this correctly wouldn't we have to do a query to find the shard key from the shard map manager for the current user to be able to read and write data for the application?

Any input is greatly appreciated!

Upvotes: 1

Views: 1773

Answers (1)

Thiago Custodio
Thiago Custodio

Reputation: 18387

I think you're looking for Azure SQL Elastic Pool: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

The idea of manually control the shards is also possible, but will add complexity to your ops team. Take a look on other patterns described in here too: https://learn.microsoft.com/en-us/azure/sql-database/saas-tenancy-app-design-patterns

Upvotes: 0

Related Questions