How to create multi-tenancy with dedicated database #5893

fguo created 5 years ago

I am trying to create a new tenant with its own dedicated database, but always get "internal error" while saving that tenant. Here is how I did:

Click "Create new tenant" button.
Fill the popup form with new Tenant name as "Tenant1".
Fill the Database connection string as "Server=(localdb)\mssqllocaldb; Database=Tenant1Db; Trusted Connection=True;". This string is almost same with the working host database, which is "Server=(localdb)\mssqllocaldb; Database=HostDb; Trusted Connection=True;". (only difference is database name)
Fill all other info on the form, and click the "Save" button.

I tried to do it without such database (expect to get migration class and automatically generate Tenant1Db). It apparely does not works in that way.

I tried to manually make a blank database and name it as "Tenant1Db", before create the new tenant, but it still throws exception.

From the logs.txt, the ERROR is: System.Data.SqlClient.SqlException (0x80131904); A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

I am stuck here. Where can I get a step-by-step instruction to create a new tenant with its own dedicated database?

Thanks,

14 Answer(s)

0

maliming created 5 years ago
Support Team

There is no problem with your steps. But currently there is a problem with the database connection. Is there any other exception message in the log? Or can you change another sqlserver database connection string and try again?
0

fguo created 5 years ago

Thanks! I tried with another existing DB. It connected and created a new tenant with that dedicated database. It looks like just copy all schema from Host database onto that Tenant's database, although some tables are not necessorary (e.g. AbpTenant), I think.

Now the new question, how do I use that tanent's database to add new DbContext? For example, I need to add an entity, and want the table created on that tenant's database. Do you have an instruction or a code example to explain how to do it?

Thanks,
0

aaron created 5 years ago
Support Team

See Migrator Console Application in https://docs.aspnetzero.com/documents/zero/latest/Development-Guide-Core.
0

fguo created 5 years ago

The link directs me to Development-Guide under "ASP.NET Core MVC & JQuery". I am using " ASP.NET Core & Angular" version, and can't find "Migrator.exe". Can you tell me the full path of this program in " ASP.NET Core & Angular" version?
0

ismcagdas created 5 years ago
Support Team

Hi @fguo

ASP.NET Core & Angular has the same Migrator console app. It should be under Tools folder. You can check https://docs.aspnetzero.com/documents/zero/latest/Development-Guide-Core#migrator-console-application to learn how to use it.
0

fguo created 5 years ago

I searched all of my download versions of "ASP.NET Core & Angular". There is NO "Tools" folder.

On version 3.2, I found out a Migrator.exe under aspnet-core/src/migrator/bin/debug/net461. In all other download version, there is NO Migrator.exe.

Can you search it on your side, and give out a FULL path?? It should just take your several seconds.
0

aaron created 5 years ago
Support Team

Build the Migrator project.
0

fguo created 5 years ago

Just tried. No luck.
0

fguo created 5 years ago

The only way for me to run "Migrator" is to set Migrator as startup project in VS and run (F5) it. The console pops up with similar messages as showed in the development-guide. However, I still has no clue how to use that tanent's database to add a new dedicated DbContext. Isn't it possible? Can you advise?
0

ismcagdas created 5 years ago
Support Team

Hi @fguo

Sorry, I understand your original problem wrongly. Creating a Tenant from UI using a connection string should create the Tenant and it's database as well.

So, Do you have a chance to use an SQL Server instance rather than local db ? The problem might be related to connection string of local db.
0

fguo created 5 years ago

Yes. As I said above, I tried with another existing DB ( an SQL Server instance rather than local db). It connected and created a new tenant with that dedicated database. BUT, I wonder how do I use that tanent's database to add a new DbContext? For example, I need to add an entity, and want the table created on that tenant's database. Do you have an instruction or a code example to explain how to do it?

In the "Step by Step Development" guide, the "DbSet<Person> Persons" is added in Host DbContext (i.e. PhoneBookDemoDbContext). How do I create a TenantDbContext? I want the tenant dedicated database to store the "Persons" table.
0

ryancyq created 5 years ago
Support Team

Hi, @fguo, sounds like you want to have HostDbContext and TenantDbContext.

currently ANZ is using the same DbContext for both host and tenant. Multi tenancy data retrieval is achieved using data filter (MultiTenancySide) so that even if a tenant connection is using DbContext with Host related tables, it will not be able to retrieve any host data.

if you still want a multiple db context solution, you can refer to https://github.com/aspnetboilerplate/aspnetboilerplate/issues/3765#issuecomment-414108163
0

fguo created 5 years ago

I am a little confused. While I create a new tenant with a dedicated connectionstring (different wtih the "default" connectionstring), it automatically adds tables in that dedicated database. I guess a corresponding "TenantDbContext" must be created automatically. Doesn't it?

You memtioned "Multi tenancy data retrieval is achieved using data filter". Is it still true to "Multi tenancy with seprated dedicated database"?

For example, I know an user's tenant-id and user-id, how do I inject a IRepository<UserRole, long> userRoleRepository to retrieve UserRole info for different tenants' user, if tenants using seprated dedicated database?

My "HostDbContext" is configured by the "default" connectionstring, so I can add an entity on to the "HostDbContext", and the corresponding database table is created on the "default" database after migration.

Now, I want to do same thing to add a similar table on "tenant" database. How do I do?

Thanks,
0

ryancyq created 5 years ago
Support Team

DbConnection for each dedicated database is handled by Abp framework. If a tenant has a custom connection string, default db context will be resolved with the dedicated database. Hence, both HostDbContext and TenantDbContext should be identicial.

See https://github.com/aspnetboilerplate/aspnetboilerplate/blob/e0ded5d8702f389aa1f5947d3446f16aec845287/src/Abp.Zero.EntityFrameworkCore/Zero/EntityFrameworkCore/DbPerTenantConnectionStringResolver.cs#L57

Have an answer to this question? Log in and write your answer.