MS SQL自定义日志表分离至独立文件/文件组的方案及EF Core可行性咨询

Hey there! Let's walk through your question step by step, since you've already dug into the SQL Server docs and have a clear goal in mind.

Why a Custom Filegroup is the Right Choice #

Your top priority is being able to exclude the log tables from backups, and a custom filegroup solves this perfectly—here's why:

• Backup flexibility: If your log tables live in a standalone filegroup, you can run filegroup-level backups that skip this group entirely, or back it up separately on a different schedule. If you add a secondary file to the Primary filegroup, you can't exclude just that file—backing up Primary means including all its files, which defeats your purpose of separating log data for backup exclusion.
• Aligns with SQL Server best practices: The docs you referenced make this clear:

  If you use multiple data files, create a second filegroup for the additional file and make that filegroup the default filegroup
  This guidance is all about isolating large, high-volume, or differently managed data from system core objects (which live in Primary), making maintenance and storage management way easier.

• Bonus performance boost: You can place the custom filegroup's .ndf file on a separate physical disk, which reduces IO contention on the disk hosting the Primary file—critical for write-heavy log tables.

Why Avoid Adding a Secondary File to Primary #

• Primary is tied to system metadata: The Primary filegroup contains system tables and database metadata. Any object not assigned to a specific filegroup defaults here, so your log tables would be mixed with core system data. You can't selectively exclude a single file from Primary backups.
• Future maintenance headaches: If you later need to move log tables to a different disk or adjust storage settings, doing so within a custom filegroup is far simpler than modifying Primary's files (which carry more risk of affecting system stability).

First, create your custom filegroup and corresponding .ndf file:

-- Create the custom filegroup
ALTER DATABASE YourDatabaseName
ADD FILEGROUP LogFileGroup;

-- Add the .ndf file to the new filegroup
ALTER DATABASE YourDatabaseName
ADD FILE
(
    NAME = LogDataFile,
    FILENAME = 'D:\SQLData\LogDataFile.ndf', -- Replace with your preferred path
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB
)
TO FILEGROUP LogFileGroup;

Then move your existing log tables to this filegroup:

-- For heap tables (no clustered index)
ALTER TABLE YourLogTable
REBUILD WITH (DATA_COMPRESSION = NONE, FILEGROUP = LogFileGroup);

-- For tables with a clustered index (rebuild the index to target the filegroup)
CREATE CLUSTERED INDEX PK_YourLogTable
ON YourLogTable(YourPrimaryKeyColumn)
WITH (DROP_EXISTING = ON, FILEGROUP = LogFileGroup);

EF Core doesn't have native APIs for creating filegroups (since this is a server-level configuration, not ORM core functionality), but you can handle it in two ways:

1. Modify EF Core Migration Scripts #

Generate a migration, then add filegroup creation logic to the Up() method, and specify the filegroup when creating your log table:

protected override void Up(MigrationBuilder migrationBuilder)
{
    // First create the filegroup and .ndf file
    migrationBuilder.Sql(@"
        ALTER DATABASE YourDatabaseName
        ADD FILEGROUP LogFileGroup;

        ALTER DATABASE YourDatabaseName
        ADD FILE
        (
            NAME = LogDataFile,
            FILENAME = 'D:\SQLData\LogDataFile.ndf',
            SIZE = 100MB,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 50MB
        )
        TO FILEGROUP LogFileGroup;
    ");

    // Create the log table and assign it to the custom filegroup
    migrationBuilder.CreateTable(
        name: "YourLogTable",
        columns: table => new
        {
            Id = table.Column<int>(nullable: false)
                .Annotation("SqlServer:Identity", "1, 1"),
            // Add your other log columns here
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_YourLogTable", x => x.Id);
        })
        .Annotation("SqlServer:FileGroup", "LogFileGroup"); // Assign to the custom filegroup
}

2. Run Pre-Initialization SQL Scripts #

Execute the filegroup creation logic before running EF Core migrations in your app startup:

var context = services.GetRequiredService<YourDbContext>();

// Create the filegroup if it doesn't already exist
context.Database.ExecuteSqlRaw(@"
    IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name = 'LogFileGroup')
    BEGIN
        ALTER DATABASE YourDatabaseName
        ADD FILEGROUP LogFileGroup;

        ALTER DATABASE YourDatabaseName
        ADD FILE
        (
            NAME = LogDataFile,
            FILENAME = 'D:\SQLData\LogDataFile.ndf',
            SIZE = 100MB,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 50MB
        )
        TO FILEGROUP LogFileGroup;
    END
");

// Run EF Core migrations
context.Database.Migrate();

Once your log tables are in the custom filegroup, you can run backups that skip it entirely:

-- Backup only the Primary filegroup (excludes LogFileGroup)
BACKUP DATABASE YourDatabaseName
FILEGROUP = 'PRIMARY'
TO DISK = 'D:\SQLBackups\PrimaryOnlyBackup.bak';

-- Backup all filegroups except LogFileGroup (add other non-log filegroups if needed)
BACKUP DATABASE YourDatabaseName
FILEGROUP = 'PRIMARY'
TO DISK = 'D:\SQLBackups\ExcludeLogBackup.bak';

内容的提问来源于stack exchange，提问作者balron