Click here to Skip to main content
15,123,851 members
Articles / Web Development / HTML
Article
Posted 29 Nov 2014

Stats

94.8K views
63 downloads
69 bookmarked

Multi-Tenancy System With Separate Databases in MVC

Rate me:
Please Sign up or sign in to vote.
4.64/5 (29 votes)
3 Oct 2017CPOL5 min read
Creating a multi-tenancy system where each tenants data is stored in a separate database, using ASP.NET MVC

Introduction

With improvements in broadband and web technologies, we are seeing a shift away from traditional desktop applications towards web based systems. The cloud is all the range these days. Accounting packages such as Sage and Quickbooks are being replaced by online alternatives such as Kashflow and Wave Apps.

Rather than creating a unique software instance per customers, the likes of Kashflow and Wave Apps have developed their systems as multi-tenancy applications – a single instance of the software is used by all users. In each case, their data is separated from that of other customers by the architecture of the system.

Quote:

Multitenancy refers to a principle in software architecture where a single instance of the software runs on a server, serving multiple tenants. A tenant is a group of users sharing the same view on the software they use. - Wikipedia

This can be achieved in two ways:

Single Database – A single database is created, and all data is stored here. Each record is assigned a tenant key, and only data belonging to that tenant is accessible. Access is restricted by the application software.

Multiple Databases – Alternatively, a separate database can be used to store each customers data. Access to the database can then be restricted by using SQL login credentials.

While I have used the single database approach many times, when starting a recent project, it became apparent that the multiple database approach may be more suitable.

Advantages of the Multi Database Approach

One of the main advantages of the multi-database approach is that it makes it possible to backup and restore an individual users data. With a single database approach, restoring the database would wipe out changes for all customers, and makes it impossible to offer a roll-back functionality in the event a single customer makes a mistake.

Additionally, should the site become extremely successful, multi-database systems allow data to be moved between servers very easily on an individual basis.

The main selling point however in my case, was the anticipation that a number of clients may require customization of the system beyond what can be achieved in a multi-tenancy design. By using separate databases, these can be moved to a new server if need be, and fully customized if needed. While this may break the advantage of a multi-tenancy system in the first place, it does offer flexibility and future-proofing that a single database system would not offer.

Architecture of A Multi-Database System

Architecture of a Multi-Tenancy Application

In a multi-database multi-tenancy system, each users data is stored in its own database. A separate database is therefore required to hold login details, and provide details of where the users data is stored. This could point to a database on the same server, or a remote data location.

How to Create A Multi-Database System With MVC 6

In Visual Studio, create a new ASP.NET Web Application.

Create a new project

Select MVC as the template type, and in “Change Authentication”, ensure “Individual User Accounts” is selected. We will use forms authentication for this example.

Select MVC as the type

Ensure Individual User Accounts is selected

First, create a folder called AccountDAL – we will use this to store all the code for accessing the Account data store.

Create a folder for the account DAL

Create a new class, and name it DataContext.cs. Add the following code:

C#
public class DataContext : DbContext
{
    public DataContext() : base("accountContext")
    {
    }

    public DbSet<Account> Accounts { get; set; }
    public DbSet<User> Users { get; set; }
}

We will use Entity Framework, code first, to generate a DataContext that represents the data stored in our Account database. There will be two tables:

  • Accounts – An account represents a single tenant. This data will contain the location of the tenant’s data store. Each account can have multiple users.
  • Users – Contains the login username and password for all users of the system. Each user is tied to an account.

Add a connection string to web.config to connect to the account database:

XML
<connectionStrings>
    <add name="accountContext"
          providerName="System.Data.SqlClient"
          connectionString="Server=desktop\SERVER2012; Database=Accounts;
          Integrated Security=SSPI" />
  </connectionStrings>

While in web.config, we will also check that the auth mode is set to forms authentication:

XML
<authentication mode="Forms">
    <forms loginUrl="/Account/Login" cookieless="UseCookies" />
</authentication>

Next, let's create two classes to represent the tables in our database, User.cs and Account.cs:

C#
public class User
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public string Name { get; set; }
    public int AccountId { get; set; }
    public virtual Account Account { get; set; }
}

public class Account
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Database { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

We now need to create our database. Create a new database called Accounts, and add two tables called Users and Accounts, as follows:

Accounts:

Users:

Add the following test data to each:

Finally, let’s make a couple of changes to our Login and Logout functions to use FormsAuthentication:

C#
public ActionResult Login(LoginViewModel model, string returnUrl)
{
   if (ModelState.IsValid)
   {
      var dataContext = new AccountDAL.DataContext();
      var user = dataContext.Users.FirstOrDefault
                 (x => x.Email == model.UserName && x.Password == model.Password);

      if (user != null)
      {
         FormsAuthentication.SetAuthCookie(model.UserName, false);
         return RedirectToLocal(returnUrl);
      }
      else
      {
         ModelState.AddModelError("", "Invalid username or password.");
      }
   }

   // If we got this far, something failed, redisplay form
   return View(model);
}

The above code will create a new instance of our Account DataContext, and check the user and password match an existing user. If so, we will set an auth cookie, which will log in the user.

And logout:

C#
public ActionResult LogOff()
{
   FormsAuthentication.SignOut();
   Session.Abandon();
}

The above code will clear the auth cookie we set earlier. This will have the effect of logging the user out of the system.

If we now run the project, we will now able to log in as either of the two companies we created in the test data. All very straightforward.

Now comes the multi database approach.

Let’s create two new databases, one for each of our companies. Call them “Company1” and “Company2”, as we specified in the “Account” table of our test data. In each, create a new table called Jobs, as follows:

Add a couple of test jobs in each database:

Company 1 test data:

Company 2 test data:

Now, back in Visual Studio, create a folder called SystemDAL to store all our data objects that relate to the actual system.

First, create a new class called DataContext.cs:

C#
public class DataContext : DbContext
{
   public DataContext(string database)
     : base("Data Source=desktop\\Server2012;Initial Catalog=" + database + ";Integrated Security=True")
   {
   }
   
   public DbSet<Job> Jobs { get; set; }
}

This is where we implement our multi-database logic. Rather than pass in the name of a connection string to the DataContext base constructor, we will instead build our own, using a database name passed in to the DataContext constructor. This will be taken from the Account table in our database.

Create a second class to represent a job object:

C#
public class Job
{
   public string JobName { get; set; }
   public int Id { get; set; }
}

We will now modify the Home\Index() function to load the current users data:

C#
[Authorize]
public ActionResult Index()
{
   // get the current user:
   var accountContext = new AccountDAL.DataContext();
   var user = accountContext.Users.FirstOrDefault(x => x.Email == User.Identity.Name);

   if (user != null)
   {
      // now we have the current user, we can use their Account 
      // to create a new DataContext to access system data:
      var systemContext = new SystemDAL.DataContext(user.Account.Database);
      return View(systemContext.Jobs);
   }
   return View();
}

The above code first creates an instance of our Account DataContext, and gets an object representing the current logged in user. From this, we can then create a System DataContext instance, passing in the name of the database we wish to connect to.

Once connected, we can then pass a list of all the companies jobs to the View.

Modify the Index view as follows, replacing the existing code:

ASP.NET
@model IQueryable<MultiTenancy.SystemDAL.Job>

@{
    ViewBag.Title = "Home Page";
}

<br/>
<ul>
    @if (Model != null)
    {
        foreach (var job in Model)
         {
             <li>@job.JobName</li>
         }
    }
</ul>

There we have it – a multi-tenancy web application that stores each user's data in a separate database!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Gavin Coates
Software Developer
United Kingdom United Kingdom
Gavin Coates is a software developer from Southwest Scotland. Gavin works primarily with web based technologies such as Microsoft ASP.NET. Gavin also has experience of developing industrial control systems using ICONICS, Modbus and OPC technologies. Gavin also also an aviation enthusiast, and loves to travel.

Comments and Discussions

 
QuestionMulti tenant database and mvc Pin
Member 1146302923-Feb-15 23:13
professionalMember 1146302923-Feb-15 23:13 
AnswerRe: Multi tenant database and mvc Pin
Gavin Coates24-Feb-15 2:16
MemberGavin Coates24-Feb-15 2:16 
QuestionSingle DB example Pin
Member 1147408623-Feb-15 14:41
MemberMember 1147408623-Feb-15 14:41 
AnswerRe: Single DB example Pin
Gavin Coates24-Feb-15 2:29
MemberGavin Coates24-Feb-15 2:29 
GeneralRe: Single DB example Pin
Member 1147408624-Feb-15 7:48
MemberMember 1147408624-Feb-15 7:48 
QuestionError Pin
LiMMoz11-Feb-15 14:14
MemberLiMMoz11-Feb-15 14:14 
AnswerRe: Error Pin
Gavin Coates24-Feb-15 2:18
MemberGavin Coates24-Feb-15 2:18 
QuestionOther considerations Pin
Louis van Alphen1-Dec-14 9:10
MemberLouis van Alphen1-Dec-14 9:10 
Although you identify very valid advantages to the multi-database multi-tenant model, there are some drawbacks as well. One of which is when you have many tenants, upgrading your application means running concurrent versions of the application software while you upgrade each database. This exercise introduces a fair bit of complexity that has to be carefully managed. Anyhow even though your article does does not set out to discuss multi-tenancy per-se, maybe a bit more background on the subject would give more context. Great article anyway.
AnswerRe: Other considerations Pin
Gavin Coates2-Dec-14 6:48
MemberGavin Coates2-Dec-14 6:48 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun1-Dec-14 2:03
MemberHumayun Kabir Mamun1-Dec-14 2:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.