2

I am a first-time azure developer and I am taking over a single-tenant cloud system and have been tasked with converting the system to multi-tenant. What is the correct practice to separate each tenant's sql server data?

Its a data collection & reporting system. Each tenant will want to build and run their own custom reports on the data. So in multi-tenant, how are tenants prevented from seeing each others data?

  • 4
    What do you mean by "correct?" State your requirements, rather than asking which process is correct. – Robert Harvey Mar 4 '14 at 17:24
  • I agree that splitting database structures will cause rather major issues in sync, especially if some of the tenants going down or locked. A partition is a good scale-up solution, but if you still desire the flexibility of each tenant-database, maybe try partitiondb. Basically, it's a wrapper platform for RDBMS, especially designed for SaaS systems. It's like an object-oriented programming for databases - You design your master database, and define all partition instances. The platform distribute your data inputs automatically across the partitions. You can – Johns Oct 11 '15 at 9:17
3

There is no one "correct" way to do anything in programming, even with a cloud-computing specific architecture such as Windows Azure.

A cursory search of the MSDN documentation reveals that Azure does in fact have some useful functionality to help you separate your client's data. It appears as if you could decide to give each client their own SQL server instance, or you could instead use a common instance that you either partition directly or leverage some built-in functionality to partition for you.

Each of those solutions have their own pros and cons, which you will need to evaluation on your own. Some conceptual guidance as to the selection:

  • If you use a separate instance for each client you will have built-in separation for your clients' data, but some reduced ability to examine data from multiple clients. Microsoft may also charge more for the additional overhead.
  • If you use the same instance but partition the data manually in a single database, you'll have to manage and adjust the various keys and indexes yourself. You obviously don't want to have a large client's 50,000,000 records slow down the query a smaller client's 500 records.
  • If you give each client a distinct schema or database in your single server instance, query management becomes easier, but you may need to worry about server-locks. (On the plus side, a seperate-db-per-client may let you make the transition easier.)
  • If you use Microsoft's bulit-in partition tools, they may very well wind up doing exactly what you want them to do. Or you may get 70% of the way throuh implementation and discover a need their tools do not cover, requiring you to abandon much of your work. (Research and proper models are important when using anyone else's toolchain.)

As for the question of "how do you move data from single-instance to multi-instance", I imagine it would largely be a matter of constructing your new architecture and then importing the old data. You'll want to know how to do that for future clients, anyway.

  • Also: vendor-specific questions about Azure's specifics may be better asked on a different StackExchange site. – DougM Mar 4 '14 at 17:29
2

Many people will immediately jump to the idea of having one database per customer. In most cases thats unnecessary and creates additional problems of keeping the database structures in sync. Of course, if you specifically want them to have different database structures (different table & column definitions) or you want to give them direct access to the database, then this is what you want to do.

The most typical method for multi-tenant systems is to logically separate the data in the database via an organization or customer ID. If your data is more or less normalized, your database queries shouldn't become that much more complex, if you even have to change them.

  • You would need views which constrain the data to the specific organization. – Robert Harvey Mar 4 '14 at 17:52
  • No, I've never needed views for this. I've always just done what you originally suggested... "WHERE organization_id = NNN". – GrandmasterB Mar 4 '14 at 18:17
  • You don't necessarily need views though it does provide a nice level of abstraction to prevent "oopsies" and a few other benefits. Recommended but not required. – Rig Mar 5 '14 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.