Click here to Skip to main content
14,331,612 members

Multi-Tenancy System With Separate Databases in MVC

Rate this:
4.64 (29 votes)
Please Sign up or sign in to vote.
4.64 (29 votes)
3 Oct 2017CPOL
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:

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:

Image 6

Users:

Image 7

Add the following test data to each:

Image 8Image 9

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.

Image 10

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:

Image 11

Add a couple of test jobs in each database:

Company 1 test data:

Image 12

Company 2 test data:

Image 13

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!

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

 
QuestionCouldn't reaching to home index after login. Pin
dawood abbas21-Jun-19 23:35
memberdawood abbas21-Jun-19 23:35 
QuestionLogin Pin
motabz2-Aug-18 0:39
membermotabz2-Aug-18 0:39 
QuestionWhat do you mean by single instance ? Pin
Mou_kol4-Oct-17 4:28
memberMou_kol4-Oct-17 4:28 
Questionhow single instance can give service to multiple tenants Pin
Mou_kol3-Oct-17 23:31
memberMou_kol3-Oct-17 23:31 
QuestionCommon User Database is right approach incase of Multi-Database System Architecture ? Pin
DotNetDev20163-Oct-17 21:18
memberDotNetDev20163-Oct-17 21:18 
QuestionADO.NET instead of EF Pin
Member 1227391831-Mar-17 6:42
memberMember 1227391831-Mar-17 6:42 
QuestionActionResult Index() vs 2015 Pin
Member 1276467830-Sep-16 0:42
memberMember 1276467830-Sep-16 0:42 
AnswerRe: ActionResult Index() vs 2015 Pin
Member 1276467830-Sep-16 3:44
memberMember 1276467830-Sep-16 3:44 
QuestionASP.Net Identity Pin
Member 15737234-Aug-16 5:20
memberMember 15737234-Aug-16 5:20 
GeneralPlz provide demo Pin
virel rakholiya4-May-16 2:49
membervirel rakholiya4-May-16 2:49 
QuestionASP.NET 5 Identity - single app instance multiple databases Pin
Member 1129212230-Oct-15 1:17
memberMember 1129212230-Oct-15 1:17 
AnswerRe: ASP.NET 5 Identity - single app instance multiple databases Pin
Gavin Coates9-Dec-15 0:46
memberGavin Coates9-Dec-15 0:46 
QuestionImages not showing Pin
Roelof Janssen7-Oct-15 11:24
memberRoelof Janssen7-Oct-15 11:24 
QuestionSpeed Issue Pin
Rameshwar Trivedi1-Jun-15 20:27
memberRameshwar Trivedi1-Jun-15 20:27 
GeneralRe: Speed Issue Pin
Member 117628933-Sep-15 2:33
memberMember 117628933-Sep-15 2:33 
QuestionUsing DataContext within a new Controller to establish a default DBContext Pin
Member 1155861821-Apr-15 14:16
memberMember 1155861821-Apr-15 14:16 
QuestionLogin data also in tenant data example Pin
swiebertje1510-Apr-15 2:04
memberswiebertje1510-Apr-15 2:04 
QuestionMulti-Tenancy System With Separate Databases in MVC Pin
Member 115934379-Apr-15 1:11
memberMember 115934379-Apr-15 1:11 
QuestionHow migrate all database? Pin
Member 115683496-Apr-15 16:56
memberMember 115683496-Apr-15 16:56 
QuestionRequest for Sample Project Pin
Sanjay Gopal28-Mar-15 13:41
memberSanjay Gopal28-Mar-15 13:41 
QuestionMulti tenant database and mvc Pin
Member 1146302923-Feb-15 22:13
professionalMember 1146302923-Feb-15 22:13 
AnswerRe: Multi tenant database and mvc Pin
Gavin Coates24-Feb-15 1:16
memberGavin Coates24-Feb-15 1:16 
QuestionSingle DB example Pin
Member 1147408623-Feb-15 13:41
memberMember 1147408623-Feb-15 13:41 
AnswerRe: Single DB example Pin
Gavin Coates24-Feb-15 1:29
memberGavin Coates24-Feb-15 1:29 
GeneralRe: Single DB example Pin
Member 1147408624-Feb-15 6:48
memberMember 1147408624-Feb-15 6:48 

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.

Article
Posted 29 Nov 2014

Stats

77K views
44 downloads
69 bookmarked