Multi-Tenancy System With Separate Databases in 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.

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 customisation 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 customised 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 it’s 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:

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

   public DbSet Accounts { get; set; }
   public DbSet 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:

<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:

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

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

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 function to use FormsAuthentication:

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:

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:

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:

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:

[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:

@model IQueryable<MultiTenancy.SystemDAL.Job>

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

<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 users data in a separate database!

  • Karel

    Hello Gavin, Great tutorial so far! I am working on a .NET application and want to make multi-tenant, multiple databases, just like this tutorial. But It is not working right for me because I can’t login.
    The form does validates, when I don’t use the right username / password, I get “Invalid username…..”.
    Then I go back to the index page, but I am not logged in. I don’t see, hello email@company1.com / logout. I see: login / register. I guess it has something to do with my connectionstring. I’m using my localhost.

    Have you any idea what i’m doing wrong?

    • Gavin Coates

      it sounds like your connection string is fine, otherwise it wouldn’t report an invalid user/password. It sounds like it may be an issue with the authentication provider – check you have cookies enabled. It’s hard to say without seeing the source code though. Email me at gavin.coates@gmail.com if you wish.

  • Rodolfo

    Hi, thank
    you for sharing your acknowledge. I’m having the same issue, can’t login, but
    if I register I got logged in, no idea where that new login is stored but then
    I create the user on the DB and then load Index again and there it is.

    I’m really
    new at MVC, still learning. I still don’t get why there is 2 methods Login:

    public async Task
    Login(LoginViewModel model, string
    returnUrl)

    public ActionResult
    Login(string returnUrl)

    * I replaced the
    first one.

    Do you have
    the source code for this sample?, tank you in advance.

  • Nik

    Hi,
    Its look great and what I was looking for. Is this will work if I go with EF Database First approach.
    Thanks
    Nik

    • Gavin Coates

      Yes, it will work with any approach.