Open Closed

Seperate DB for Audit tables #3212

User avatar
0
shobhit created
  • ABP Framework version: v4.2.2
  • UI type: Angular
  • DB provider: EF Core
  • Tiered (MVC) or Identity Server Separated (Angular): yes
  • Exception message and stack trace:
  • Steps to reproduce the issue:"

Hi Team, we are facing performance issue when Audit table size is large like 1 GB. Can we move All Audit related tables to seperate SQL DB. If yes, how.

Go to accepted answer
9 Answer(s)
  • User Avatar
    0
    gterdem created
    Support Team Senior .NET Developer

    You can use AbpAuditLogging connection string for separate audit logging in appsettings like:

    "ConnectionStrings": {
  "Default": "Server=localhost;Database=ApplicationDb;Trusted_Connection=True;",
  "AbpAuditLogging": "Server=localhost;Database=AuditLoggingDb;Trusted_Connection=True;"
}
  • User Avatar
    0
    shobhit created

    Thanks gterdem. I am using Window10 and VS 2022. After making connection string changes in appsettings in host, identity and DBMigrator project, i run the DBMigrator project but Audit tables not created in new audit db.

    What step i should take to create Audit feature realted tables in new DB.

  • User Avatar
    0
    gterdem created
    Support Team Senior .NET Developer

    Please check https://support.abp.io/QA/Questions/2929/Separate-database-for-AuditLog-module-in-Application#answer-d9465c93-5195-6314-7e51-3a0351ac859d

    1
  • User Avatar
    0
    shobhit created

    Could not understand where exactly i have to make changes. As it seems i have seperate set of files.

    In entity framework core i have 4 files: EzpandCCDbContext EzpandCCDbContextModelCreatingExtensions EzpandCCEfCoreEntityExtensionMappings EzpandCCEntityFrameworkCoreModule

    in sample 7 files provided BookStoreDbContext.cs BookStoreDbContextFactory.cs BookStoreEfCoreEntityExtensionMappings.cs BookStoreEntityFrameworkCoreModule.cs BookStoreSecondDbContext.cs BookStoreSecondDbContextFactory.cs EntityFrameworkCoreBookStoreDbSchemaMigrator.cs

    *DbContextFactory, EntityFrameworkCoreBookStoreDbSchemaMigrator is surely missing. Do i have to create them also. If yes can you confrim me all Audit related tables exist so that i can keep them in secodDbContext and will emove from *DBContext. Please remember i am using 4.2.2

  • User Avatar
    0
    berkansasmaz created
    Support Team .NET Developer

    Hello, I have explained how to do this step by step below. You can do it this way too. If you have any questions, please don't hesitate to ask.

    • Create EzpandCCSecondDbContext.cs in EzpandCC.EntityFrameworkCore.DbMigrations/EntityFrameworkCore/

    • Create EzpandCCSecondDbContextFactory.cs in EzpandCC.EntityFrameworkCore.DbMigrations/EntityFrameworkCore/

    • Remove below line from EzpandCCMigrationsDbContext in EzpandCC.EntityFrameworkCore.DbMigrations/EntityFrameworkCore/

    builder.ConfigureAuditLogging();

    • Add below lines to MigrateAsync method in EntityFrameworkCoreEzpandCCDbSchemaMigrator class in EzpandCC.EntityFrameworkCore.DbMigrations/EntityFrameworkCore/
    await _serviceProvider
                .GetRequiredService<EzpandCCSecondDbContext>()
                .Database
                .MigrateAsync();
    • Add below line to ConfigureServices metod in EzpandCCEntityFrameworkCoreDbMigrationsModule class in EzpandCC.EntityFrameworkCore.DbMigrations/EntityFrameworkCore/

    context.Services.AddAbpDbContext<EzpandCCSecondDbContext>();

    • Add the following connection string under Default for all appsettings.json.

    "AbpAuditLogging": "Server=(LocalDb)\\MSSQLLocalDB;Database=EzpandCC;Trusted_Connection=True"

    • Run below commands in EzpandCC.EntityFrameworkCore.DbMigrations/

    dotnet ef migrations add "Initial" --context EzpandCCMigrationsDbContext dotnet ef migrations add "Initial" --context EzpandCCSecondDbContext

    • Finally, run the EzpandCC.DbMigrator project.
  • User Avatar
    0
    shobhit created

    Hello , Thanks a lot for providing detaild steps. in second last step i am getting this error:

            But my other dotnet commands are working fine.
  • User Avatar
    0
    berkansasmaz created
    Support Team .NET Developer

    Run below commands in EzpandCC.EntityFrameworkCore.DbMigrations/

    1. dotnet ef migrations add "Initial" --context EzpandCCMigrationsDbContext

    2. dotnet ef migrations add "Initial" --context EzpandCCSecondDbContext

    These two commands need to be run sequentially, not together.

    Additional Information

    If you changed the name of the second DbContext you created, if EzpandCCSecondDbContext is different from this name, it should change accordingly in the command.

    For example: My second DB context name is MyNewDbContext and so I have to write the following code.

    dotnet ef migrations add "Initial" --context MyNewDbContext

  • User Avatar
    0
    berkansasmaz created
    Support Team .NET Developer

    Closing the question. Feel free to re-open if the problem is not resolved.

  • User Avatar
    0
    shobhit created

    Thanks berkansasmaz for great help.

    Solution is working as expected.

    1 1
Assignee
User avatar berkansasmaz
Labels
Non yet
Made with ❤️ on ABP v8.2.0-preview Updated on March 25, 2024, 15:11