Multi-Tenant Single Application - Multiple Database #962

12 Answer(s)

• 

  0

  hikalkan created 8 years ago

  Support Team

  Hi,

  First of all, I many thank you for your long description of your problem and solution.

  I was also thinking about db per tenant (or a hybrid solution) over the last a few weeks. I think the application code should not depend on db per tenant or single db approach. Data Filtering (<a class="postlink" href="http://www.aspnetboilerplate.com/Pages/Documents/Data-Filters">http://www.aspnetboilerplate.com/Pages/ ... ta-Filters</a>) seems a good candidate for switching between tenants. So, we can set tenantId on filter to make operation on another tenant (independent from db approach). It gets tenantId from session (<a class="postlink" href="http://www.aspnetboilerplate.com/Pages/Documents/Abp-Session">http://www.aspnetboilerplate.com/Pages/ ... bp-Session</a>) as default as currently does. But.. There was some problems about data filters. For a db per tenant approach, we should not use disable filter since when we disable it, we can not know which db to work. So, I started to change Abp, Abp.Zero and AspNet Zero code to work proper in this case.

  Also, we have different dbcontexts, examples: a base (probably abstract) dbcontext for common entities between host and tenant dbs (IMayHaveTenant). A host dbcontext (derived from base) for host specific entities and finally a tenant dbcontext for tenant specific entities (IMustHaveTenant).

  I also will consider your thought while implementing it.

  I can not provide a time for when to finish, but this is a high priority task for us. We will work on it on next weeks.

  Save Cancel
• 

  0

  JeffMH created 8 years ago

  Wow. I am impressed. I have a timeline of about end of July for my project. If you think it might be possible to have this implemented by then, that is more than awesome.

  I agree with you, that implementation sounds awesome. The contexts and the settings were my hangups. You have some specific settings classes and I was thinking there needed to be some concept around Host settings. Basically, you will bring in the idea of a Host DB. Sounds very good!

  And as far as the filter goes. In reality, you could remove the filtering from the code when you are in a Multi-DB environment, but in reality, I would almost be in favor of leaving it in. Let's say I have a trial version of the product, maybe I want to run some tenants on one database. Like you said, the code should not have to be aware of the "separation" of the Host and the Tenants.

  Anything I can help with just let me know. I will gladly help, test, anything I can do I am here.

  Save Cancel
• 

  0

  hikalkan created 8 years ago

  Support Team

  Hi,

  Thank you for your comments. July seems reasonable for me. I suppose it will be finished before.

  I will ask you for help if we need. Thanks a lot.

  Save Cancel
• 

  0

  dirtman created 8 years ago

  Hi, I'm using Azure Database with Multiple Database. any updates of this feature? I cannot moving forward without Multiple Database on SQL Azure.

  Save Cancel
• 

  0

  ismcagdas created 8 years ago

  Support Team

  Hi,

  You can use DB per tenant architecture with ABP and AspNet Zero right now. Which version of ABP and AspNet Zero do you use ?

  Save Cancel
• 

  0

  dirtman created 8 years ago

  I download aspnetzero on last week, so it's supposed the latest one. I know I have to use Azure Elastic Db for multiple database. but I'm stuck at this code:

  UOW.Current.SetTenatnId()
  

  since I cant modify it, I think the Elastic Db logic is supposed to write here.

  Save Cancel
• 

  0

  ismcagdas created 8 years ago

  Support Team

  Hi,

  Sorry, I think I missunderstand you. Current DB Per Tenant architecture does not support Azure's elastic DB. At leas we haven't tried it with Azure's elastic DB.

  As far as I know, cloud services does not support distributed transactions. Because of that, you cannot switch to another tenant in a transactional operation.

  Do you have a specific error message ? Maybe we can continue with that.

  Save Cancel
• 

  0

  dirtman created 8 years ago

  I think they are support the Distributed transactions now <a class="postlink" href="https://azure.microsoft.com/en-us/blog/elastic-database-transactions-with-azure-sql-database/">https://azure.microsoft.com/en-us/blog/ ... -database/</a>

  Here's the example code: <a class="postlink" href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-scale-get-started">https://docs.microsoft.com/en-us/azure/ ... et-started</a> <a class="postlink" href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-scale-use-entity-framework-applications-visual-studio">https://docs.microsoft.com/en-us/azure/ ... ual-studio</a>

  Ideally, I need to modify SetTenantId so it can switch the Database. also, somehow I need to register a shard mapping when Tenant database was created.

  if this can implemented it would be great, since our company using Azure Services.

  Save Cancel
• 

  0

  ismcagdas created 8 years ago

  Support Team

  Hi,

  I didn't know that.

  So, when you use SetTenantId, it switches the Database. connectionString of the provided tenant becames the active connection string.

  Basically, when you create a new tenant, if you provide the connectionString property, tenant database will be created. And when you use setTenantId, database will be switched to that database.

  Have you tried that ? If you did, what happens when you use setTenantId ?

  Save Cancel
• 

  0

  JeffMH created 8 years ago

  Sorry, I saw some of these posts late. Multiple DB's per tenant is up and running for us. It's been live now for about 3 months. Now, we use separate Db's for each tenant, and I also have a separate DB Context for Tenant and Host. I do not want my host tables inside my tenant DB's, and vice versa. So, in order to get all this to work I did have to make a few changes (for azure as well).

  1. The built in create database does not work in Azure. I had to write some code when the CreateTenant is called to execute a SQL script to create the database. I have an App.Setting I set for IsAzure, and if that is set, I run the custom create database code. This way it works locally and in azure.

  2. For Azure, we would get some timeouts during database migration. Since our developers use basic instances of AzureSQL, that instance is pretty slow so as our migrations grew in size, so did the time taken to execute them. Also, creating a database in Azure is a long process, it's not like creating a database in Sql Server. Resources have to be allocated, etc. So I had to add some extended timeouts in a few places.

  3. Migrations didn't always work. I had to modify some of the code in the Migrator app just a smidgen. Now, we fought SetTenantId bug for a while so these changes may not be necessary but I had to make a few changes. Sorry, I can't be more descriptive. I will try and get the changes that we made together for you. This may just work now.

  4. Using multiple context classes, I had to change AbpZeroDbMigrator (in the EntityFramework project) to accept multiple context classes. I basically created a AbpZeroMultipleContextDbMigrator class and instead of inheriting their AbpZeroDbMigrator, I inherit from my own. I had to copy some of their code around a little. Maybe this is something we can get them to integrate into the framework.

  Other than that, the multiple database integration is working fine. I get nervous about it a little because the AspNetZero guys recommend using a single schema / context. As with the AbpZeroDbMigrator, they don't run tests on the code with multiple context in mind. So, if you want to use a single schema / context, then you may not have to do all that I have done. I just hate the idea of having host tables in my tenant DB's.

  My only other complaint / wish list item is having to put a connection string in. I can't have end users creating tenants if they have to have a connection string. I wish instead it was just Server / Database entry (or just auto created based on TenancyName). We could store a template connection string in App.config and just do a string replace on the server / database inside the Context classes. I have to make these changes in our code anyway because I have to allow users to create tenants. Also, when a tenant signs up, I have to create some code that tries to auto create a tenant database with some given name. This is probably hard to make where everyone is happy.

  I will try and get the modifications I did in another follow up post. I am on a deadline so it may be a couple days. Just know, the system so far is working well for us.

  Save Cancel
• 

  0

  dirtman created 8 years ago

  Hi Jeff, Thank you for reply.

  I saw your post on another thread (#2169@40da7595-1cf3-4aa0-ac0b-7b3088e875ff). The logic looks great, I will try to implement on my project. I will post my final solution here. Thank you for sharing

  Save Cancel
• 

  0

  JeffMH created 8 years ago

  Hey, no worries, glad I can help a little. Overall, I love what they have done, it's not perfect, but no architecture / system is perfect. Multi-tenancy is not simple and it's great to get a leap forward. So if I can help make it better while helping others, we all win.

  Here is my MultiContext migrator. The other post I did on the create tenant and this were the biggest changes. As I pasted this in, I think this is where I had to make another change from what they did.

  <a class="postlink" href="https://github.com/aspnetboilerplate/module-zero/blob/dev/src/Abp.Zero.EntityFramework/Zero/EntityFramework/AbpZeroDbMigrator.cs">https://github.com/aspnetboilerplate/mo ... igrator.cs</a>

  If you look at the CreateOrMigrate method of that class compared to mine, within the using where it suppresses the unit of work, I am doing the migration slightly different. I had troubles getting migrations working and this solved the problems for me. I don't remember the circumstances enough to tell you why. I was under the gun on a big deployment and had to get that fixed. I wish I would have documented that one better.

  Here is my migrator. The biggest difference compared to the one above is it has the generic types for both the host and the tenant, the default class only excepts one context type. To use, inside the AspNetZeroDbMigrator class that comes in the downloaded solution, just change what class that inherits from. Pretty simple.

  Hope this helps.

  using System.Data.Entity;
  using System.Data.Entity.Migrations;
  using System.Transactions;
  using Abp.Data;
  using Abp.Dependency;
  using Abp.Domain.Uow;
  using Abp.Extensions;
  using Abp.MultiTenancy;
  using Abp.Zero.EntityFramework;
  
  namespace MyPortal.Framework.EntityFramework.Migrator
  {
      public abstract class AbpZeroMultiContextDbMigrator<THostDbContext, THostConfiguration, TTenantDbContext, TTenantConfiguration> : IAbpZeroDbMigrator, ITransientDependency
              where THostDbContext : DbContext
              where THostConfiguration : DbMigrationsConfiguration<THostDbContext>, IMultiTenantSeed, new()
              where TTenantDbContext : DbContext
              where TTenantConfiguration : DbMigrationsConfiguration<TTenantDbContext>, IMultiTenantSeed, new()
      {
          private readonly IUnitOfWorkManager _unitOfWorkManager;
          private readonly IDbPerTenantConnectionStringResolver _connectionStringResolver;
          private readonly IIocResolver _iocResolver;
  
          protected AbpZeroMultiContextDbMigrator(
              IUnitOfWorkManager unitOfWorkManager,
              IDbPerTenantConnectionStringResolver connectionStringResolver,
              IIocResolver iocResolver)
          {
              _unitOfWorkManager = unitOfWorkManager;
              _connectionStringResolver = connectionStringResolver;
              _iocResolver = iocResolver;
          }
  
          public virtual void CreateOrMigrateForHost()
          {
              CreateOrMigrate<THostDbContext, THostConfiguration>(null);
          }
  
          public virtual void CreateOrMigrateForTenant(AbpTenantBase tenant)
          {
              if (tenant.ConnectionString.IsNullOrEmpty())
              {
                  return;
              }
  
              CreateOrMigrate<TTenantDbContext, TTenantConfiguration>(tenant);
          }
  
          protected virtual void CreateOrMigrate<TDbContext, TConfiguration>(AbpTenantBase tenant)
              where TDbContext : DbContext
              where TConfiguration : DbMigrationsConfiguration<TDbContext>, IMultiTenantSeed, new()
          {
              var args = new DbPerTenantConnectionStringResolveArgs(
                  tenant == null ? (int?)null : (int?)tenant.Id,
                  tenant == null ? MultiTenancySides.Host : MultiTenancySides.Tenant
                  );
              args["DbContextType"] = typeof(TDbContext);
  
              var nameOrConnectionString = ConnectionStringHelper.GetConnectionString(_connectionStringResolver.GetNameOrConnectionString(args));
  
              using (var uow = _unitOfWorkManager.Begin(TransactionScopeOption.Suppress))
              {
                  var dbInitializer = new MigrateDatabaseToLatestVersion<TDbContext, TConfiguration>(
                         true,
                         new TConfiguration
                         {
                             Tenant = tenant
                         });
  
                  Database.SetInitializer<TDbContext>(dbInitializer);
  
                  using (var dbContext = _iocResolver.ResolveAsDisposable<TDbContext>(new { nameOrConnectionString = nameOrConnectionString }))
                  {
                      dbInitializer.InitializeDatabase(dbContext.Object);
  
                      _unitOfWorkManager.Current.SaveChanges();
                      uow.Complete();
                  }
              }
          }
      }
  }
  

  Save Cancel