locked
Multi-tenant architeture RRS feed

  • Question

  • User1037983650 posted

    Hello folks

    I am in the situation where I need to create following type of architecture for already hosted and developed application.

    There are many enterprise level customers that are associated with application which are having loads of data. I want to keep data of every customer in a different database. Eg Customer 1 will have its data in DB1 and customer2 will have its data in DB2. Consequently data will be isolated from each other's. At the same time, I want to store the information of each customer's database (Connection String ) in a new db which will act as a metadata so that I can pull the information on request to decide which database should be opened and application can run accordingly. E.g If there is request from User1 and its database in DB1 then this info should come from metadata database.

    Also could you please let me know how can I implement caching for this process as metadata should not be checked on every request (may be)?

    Feel free to ask any query.

    Kindly assist.

    Friday, August 4, 2017 6:51 AM

All replies

  • User-1509636757 posted

    E.g If there is request from User1 and its database in DB1 then this info should come from metadata database.

    How do you decide which DB to use? (Do you use friendly URLs accordingly per customer?)

    How do you store users? (inside metadata database or inside separate customer database?) If you are storing user information in separate customer database then how you decide in which database to check for login?

    Friday, August 4, 2017 9:28 AM
  • User1037983650 posted

    How do you decide which DB to use? (Do you use friendly URLs accordingly per customer?) 

    Every user will have an email address , so metadata will let us know the database (DB1, DB2 and DB3 or any) for that user.  Eg. We get a request from sukhdeep@yopmail.com, then request should go to metadata first, look for this email id and collect the information (ConnectionString) for that email id.

    How do you store users? (inside metadata database or inside separate customer database?) If you are storing user information in separate customer database then how you decide in which database to check for login?

    Email address will be stored in metadata but other info will be stored in respective db. It would be something like following

    MetaData :

    UserEmail                         ConnectionString

    user1@yopmail.com               DB1 (Will be a complete info)

    user2@yopmail.com               DB2 

    user3@yopmail.com              DB1

    user4@yopmail.com              DB3

    user5@yopmail.com             DB3

    DB1:

    Email                                          Password           DOB                   Department

    user1@yopmail.com                    p1                        dob1                    d1

    user3@yopmail.com                    p2                        dob2                    d2

    DB2:

    Email                                          Password           DOB                   Department

    user2@yopmail.com                    p1                        dob1                    d1

    You are free to suggest some other approach to accomplish the objective.

    Please let me know if you have any query.

    Thanks!!!

    Friday, August 4, 2017 9:57 AM
  • User-1509636757 posted

    I believe what you have specified and done until now is correct way. Here are some pointers that I would suggest in-case of Multi-Tenant architecture:

    1. Keep a table in metadata db call it Customer. Store CustomerId, CustomerName, DatabaseName, ConnectionString in this table
    2. Keep a table in metadata db call it Users. Store UserId, UserName, Email, CustomerId (Foreign Key to customer table)
    3. Of course, you will not like to make a metadata db trip every time when you need connection string, so I understand when user logs in, you can make an object of an entity by joining CustomerId to both Customer and User table (SELECT CustomerId, CustomerName, DatabaseName, ConnectionString, UserId, UserName, Email FROM Customer JOIN Users ON Customer.CustomerId = Users.UserID and UserID = <Logged_in_user_id_here>) and keep this object in Session; so that you can access it whenever you need to use Customer db
    4. If you are going to use Entity Framework, then keep two Entity Framework EDMX files (possibly separate class library DAL projects). One for metadata db and other one is for customer db. Please note that you will require to have a "Template Customer database" to connect to EDMX. Why I called it as "Template Customer database" because; practically the only use of this database is to keep updating your customer db EDMX file; since you are going to have multiple customer db file. So, always keep s separate "Template Customer database" which you need to require up to date every time you make any database changes to have those effects in EDMX file
    5. So, when you require to make connection to customer database, you will require to fetch connection from the Session object that is stored in Point #3 and override connection string of customer db EDMX Context object runtime. I have posted a sample example on how you can override connection string runtime for an EDMX Context object. This way, you will not be tripping to metadata database every time and you will also be able to change connection string of customer db runtime and link to proper database
    6. Keep a version table in each customer db (Table: CustomerDbVersion, Fields: VersionId (Primary Key), Version Number (varchar, value can be like: 1.0.1 which ever you prefer, UpdatedDate (date field when updated). Since, you are going to have many customer db and you may going to have changes in db objects (tables, stored procedures etc) in future, that you are going to have requirement of tracking if all databases are up to date, by checking at which version they are holding at currently.
    7. Prefer Repository pattern, It will be easy to create objects by passing Connection string every time. Create a base class that will logic to provide you context object with proper connection string of selected case and use this class as base class to be inherited by all your repositories.

    Here is a kind of rough implementation. First things first, a partial class to be implemented to allow you to pass a connection string from Session to create customer database object of currently logged in user, observe the underlined code:

    public partial class CustomerContextExtension : NorthwindEntities
    {
        #region "Members & Constructors"
    
        /// <summary>
        /// Override the configurational connection string with runtime selected customer
        /// </summary>
        /// <param name="connectionString">Connection string</param>
        public CustomerContextExtension(string connectionString)
            : base()
        {
            Database
                    .Connection
                    .ConnectionString = connectionString;
        }
    
        #endregion "Members & Constructors"
    }

    Create a base class as I have specified in Point #7 that will use and create above extension class object and provide context with right connection string, observe the underlined code:

    public class ContextBase
    {
        #region "Members"
    
        private string connString = string.Empty;
        private NorthwindEntities custEntity;
    
        #endregion "Members"
    
        #region "Properties"
    
        public string ConnString { get { return this.connString; } set { this.connString = value; } }
    
        /// <summary>
        /// Gets DataContext object.
        /// </summary>
        protected NorthwindEntities CUST_DC
        {
            get
            {
                if (string.IsNullOrEmpty(this.connString))
                    throw new Exception("Connection string for the selected customer is blank!!");
                else
                {
                    custEntity = custEntity ?? (custEntity = new CustomerContextExtension(this.connString));
                    return custEntity;
                }
            }
        }
    
        #endregion "Properties"
    }

    You will going to inherit all your repository implementer with above Base Class, so that you can have context connection string overriding logic at one single place.

    Once you do till at this point, you will be having a repository interface, an implementer that is inherited by above Base Class (with a parameterised constructor for accepting Connection String) and the Extension class to your Entity Context that is already in place to replace connection string runtime.

    All is left is to now fetch Connection String from Session, create object of repository and send Connection String as argument of repository constructor, and then call the methods as you like.

    Here is a complete demonstration of what I have talked above (of course it is a demonstration only that is why all code is on aspx code behind; you will going to have separate libraries for each layer I guess). You may come up with further queries in case if you chose above path of implementation:

    using System;
    using System.Linq;
    
    namespace MultiTenant
    {
        //-- the meta object that you will keep in Session
        public class MetaDataObject
        {
            public int CustomerId { get; set; }
    
            public int UserId { get; set; }
    
            public string EmailAddress { get; set; }
    
            public string ConnectionString { get; set; }
    
            public string DatabaseName { get; set; }
        }
    
        //-- base class for all pages, so you don't require to re-code fetching connection from session in all pages
        public class PageBase : System.Web.UI.Page
        {
            public string ConnectionString
            {
                get
                {
                    string ConnString = string.Empty;
                    if (null != Session["MetaDataObject"])
                    {
                        MetaDataObject metaObj = Session["MetaDataObject"] as MetaDataObject;
                        ConnString = metaObj.ConnectionString;
                    }
                    return ConnString;
                }
            }
        }
    
        public partial class WebForm263 : PageBase
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                //-- fetch connection from sesison and call the method
                IEmployee empObj = new Employee(ConnectionString);
                empObj.DeleteById(5);
            }
        }
    
        //-- repository of BAL
        internal interface IEmployee
        {
            //-- methods that you require to implement
            void DeleteById(Int64 EmpId);
        }
    
        //-- implementor/BAL
        public class Employee : ContextBase, IEmployee //-- notice the base class that we inherited, this will provide you the context object with connection you pass
        {
            public Employee(string ConnectionString)
            {
                this.ConnString = ConnectionString;
            }
    
            public void DeleteById(long EmpId)
            {
                //-- to do, delete emplyee by id of course :)
                TestTable empToDelet = CUST_DC.TestTables.Where(record => record.Id == EmpId).FirstOrDefault();
                if (null != empToDelet)
                    CUST_DC.TestTables.Remove(empToDelet);
    
                //-- you see that!!, no connection overriding stuff here, because of your base class and entity extension class defined below
            }
        }
    
        //-- this is the base class to your repository layer, so you do not need to re-code preparing of customer entity object in all repository and in all methods
        public class ContextBase
        {
            #region "Members"
    
            private string connString = string.Empty;
            private NorthwindEntities custEntity;
    
            #endregion "Members"
    
            #region "Properties"
    
            public string ConnString { get { return this.connString; } set { this.connString = value; } }
    
            /// <summary>
            /// Gets DataContext object.
            /// </summary>
            protected NorthwindEntities CUST_DC
            {
                get
                {
                    if (string.IsNullOrEmpty(this.connString))
                        throw new Exception("Connection string for the selected customer is blank!!");
                    else
                    {
                        //-- this is the EXACT place where connection string is being override
                        custEntity = custEntity ?? (custEntity = new CustomerContextExtension(this.connString));
                        return custEntity;
                    }
                }
            }
    
            #endregion "Properties"
        }
    
        //-- the partial class that has a parameterised constructor to pass connection, inheriting your customer db entity class (NorthwindEntities here)
        public partial class CustomerContextExtension : NorthwindEntities
        {
            #region "Members & Constructors"
    
            /// <summary>
            /// Override the configurational connection string with runtime selected customer
            /// </summary>
            /// <param name="connectionString">Connection string</param>
            public CustomerContextExtension(string connectionString)
                : base()
            {
                //-- this is the EXACT place where connection string is being override
                Database
                        .Connection
                        .ConnectionString = connectionString;
            }
    
            #endregion "Members & Constructors"
        }
    }

    Friday, August 4, 2017 2:20 PM
  • User1037983650 posted

    Thanks Kaushal for helpful steps. I really appreciate point # 6, i did not have that in my mind but that's required in fact. We should know which db is at what stage. Thanks for that. 

    I am about start implementing and might come up with some other queries and let you know. 

    Keep posting. 

    Thanks

    Monday, August 7, 2017 5:00 AM