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.

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

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.

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

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

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

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:

<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, let's 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 functions 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";
}

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