c# - 尝试部署 dapac : The path specified by File Ggroup FG. mdf 文件不在有效目录中时，SQL Linux docker 容器抛出错误

Question

我正在尝试将从 SQL 连接字符串生成的 .dacpac 部署到 SQL Server 2017 ubuntu docker 容器中以进行集成测试。

我正在使用 Microsoft 的 DacFx NuGet 包来生成 .dacpac。我试图找到忽略文件组的选项，但找不到任何选项。

本地主机 SQL Server 提取连接字符串:Server=localhost;Database=MyDatabase;Integrated Security=true

我想要实现的目标:从本地主机获取架构，无需将任何文件组或相关的 .mdf 文件放入 dacpac，并将其应用于 SQL docker 容器，而无需在 docker 中挂载卷，因为我希望一切都是保存在 ram 内存中，当 docker 容器停止时，数据消失了。以前我已经成功生成了 dacpac 并将其应用于 docker 实例。当没有 FILEGROUP 本地主机数据库时。还发现了 DacDeployOptions obj 的 IgnoreFilegroupPlacement 选项，但由于某种原因，它不起作用。

代码:

我将以下选项传递给提取方法:

        DacServices dacServices = new(targetDacpacDbExtract.ConnectionString);

        DacExtractOptions extractOptions = new()
        {
            ExtractTarget = DacExtractTarget.DacPac,
            IgnorePermissions = true,
            IgnoreUserLoginMappings = true,
            ExtractAllTableData = false,
            Storage = DacSchemaModelStorageType.Memory
        };

        using MemoryStream stream = new();

        dacServices.Extract(stream,
                    targetDacpacDbExtract.DbName,
                    "MyDatabase",
                    new Version(1, 0, 0),
                    extractOptions: extractOptions);

        stream.Seek(0, SeekOrigin.Begin);

        byte[] dacpacStream = stream.ToArray();

        this.logger.LogInformation("Finished extracting schema.");

这些是我传递给从连接字符串中提取 dacpac 的部署方法的选项:

SQLConnectionStringDocker:Server=127.0.0.1, 47782;Integrated Security=false;User ID=sa;Password=$Trong12!;

 this.logger.LogInformation("Starting Deploy extracted dacpac to Docker SQL container.");

        DacDeployOptions options = new()
        {
            AllowIncompatiblePlatform = true,
            CreateNewDatabase = false,
            ExcludeObjectTypes = new ObjectType[]
            {
                ObjectType.Permissions,
                ObjectType.RoleMembership,
                ObjectType.Logins,
            },
            IgnorePermissions = true,
            DropObjectsNotInSource = false,
            IgnoreUserSettingsObjects = true,
            IgnoreLoginSids = true,
            IgnoreRoleMembership = true,
            PopulateFilesOnFileGroups = false,
            IgnoreFilegroupPlacement = true
        };

        DacServices dacService = new(targetDeployDb.ConnectionStringDocker);
        
        using Stream dacpacStream = new MemoryStream(dacBuffer);
        using DacPackage dacPackage = DacPackage.Load(dacpacStream);

        var deployScript = dacService.GenerateDeployScript(dacPackage, "Kf", options);

        dacService.Deploy(
            dacPackage,
            targetDeployDb.DbName,
            upgradeExisting: true,
            options);

        this.logger.LogInformation("Finished deploying dacpac.");

        return Task.CompletedTask;

错误:这是我从 SQL docker 容器收到的错误:

如果我能完全忽略文件组那就太棒了，因为我不需要它。

Could not deploy package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 5121, Level 16, State 2, Line 1 The path specified by "MyDatabase.CacheItem_FG_195A905.mdf" is not in a valid directory.
Error SQL72045: Script execution error.  The executed script:
ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [CacheItem_FG_195A905], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_CacheItem_FG_195A905.mdf') TO FILEGROUP [CacheItem_FG];


Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 5009, Level 16, State 14, Line 1 One or more files listed in the statement could not be found or could not be initialized.
Error SQL72045: Script execution error.  The executed script:
ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [CacheItem_FG_195A905], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_CacheItem_FG_195A905.mdf') TO FILEGROUP [CacheItem_FG];

Answer 1

对于遇到此问题的任何人:““.mdf”指定的路径不在有效目录中”

我能够使用以下步骤找到解决方法:

• 使用 DacServices NuGet 包从我的连接字符串中提取流 Dacpac，源数据库是 Windows MSSQL，使用以下 DacExtractOptions:

请记住，我更改了一些 var 名称，但基本思路应该可行!

    private readonly DacExtractOptions dacExtractOptions = new()
    {
        ExtractTarget = DacExtractTarget.DacPac,
        IgnorePermissions = true,
        IgnoreUserLoginMappings = true,
        ExtractAllTableData = false,
        ExtractApplicationScopedObjectsOnly = true,
        VerifyExtraction = true
    };

我正在使用以下代码生成 Dacpac 流:

    public Task<byte[]> ExtractDacpac(TargetDbInfo targetDacpacDbExtract, bool writeToFile)
    {
        this.logger.LogInformation($"Starting extraction: {targetDacpacDbExtract.DbServer}");

        using MemoryStream stream = new();
        DacServices dacServices = new(targetDacpacDbExtract.ConnectionStringExtract);

        dacServices.Extract(
            packageStream: stream,
            databaseName: targetDacpacDbExtract.DbName,
            applicationName: ApplicationName,
            applicationVersion: new Version(1, 0, 0),
            extractOptions: this.dacExtractOptions);

        if (writeToFile)
        {
            string pathExtract = Path.Combine(IntegrationTestsConstants.IntegrationTestsConfigsPath, $"{IntegrationTestsConstants.DatabaseName}.dacpac");
            dacServices.Extract(
                targetPath: pathExtract,
                databaseName: targetDacpacDbExtract.DbName,
                applicationName: ApplicationName,
                applicationVersion: new Version(1, 0, 0),
                extractOptions: this.dacExtractOptions);
        }

        stream.Seek(0, SeekOrigin.Begin);

        byte[] dacpacStream = stream.ToArray();

        this.logger.LogInformation("Finished extracting schema as dacpac.");

        return Task.FromResult(dacpacStream);
}

我从提取的 Dacpac 流中生成一个 SQL 部署脚本，我通常使用以下代码将其保存到磁盘:

    public Task<string> GenerateSqlDeployScript(TargetDbInfo targetDeployDb, byte[] dacBuffer, bool writeToFile)
    {
        this.logger.LogInformation("Starting Deploy extracted dacpac to Docker SQL container.");

        using Stream dacpacStream = new MemoryStream(dacBuffer);
        using DacPackage dacPackage = DacPackage.Load(dacpacStream);

        DacServices dacService = new(targetDeployDb.ConnectionStringExtract);

        string deployScriptContent = dacService.GenerateDeployScript(dacPackage, IntegrationTestsConstants.DatabaseName, this.dacDeployOptions);

        if (writeToFile)
        {
            File.WriteAllText(IntegrationTestsConstants.KfDeployScriptPath, deployScriptContent);
        }

        return Task.FromResult(deployScriptContent);
    }

• 接下来，我将手动进入生成的部署脚本并更改以下 SQL 变量以匹配 MSSQL Linux docker 版本，这样做的原因是如果您尝试从 DacServices 选项覆盖 SQL 变量或尝试设置docker 容器 SQL vars 它将无法工作，因为 Linux 的 sqlcmd 没有覆盖 SQL vars 存在问题。

GO
:setvar DefaultDataPath "/var/opt/mssql/data/"
:setvar DefaultLogPath "/var/opt/mssql/log/"
:setvar DatabaseName "MyDb"
:setvar DefaultFilePrefix "MyDb"

• 接下来，在开始集成测试时，我将编写以下方法来填充 sql docker 容器数据库:

    public async Task StartDbContainerAndApplyDacpac()
    {
        string deployScript = null;
        if (this.overwriteSqlDeployScript)
        {
            TargetDbInfo targetExtractDbInfo = this.TargetExtractDbInfoBuilder();

            byte[] dacpac = await this.buildDacpac.ExtractDacpac(targetExtractDbInfo, writeToFile: false);
            deployScript = await this.buildDacpac.GenerateSqlDeployScript(targetExtractDbInfo, dacpac, writeToFile: false);
        }

        // testcontainers: MsSqlTestcontainer
    
        // https://dotnet.testcontainers.org
        await this.sqlDbContainer.StartAsync();

        await this.ReadSqlDeployScriptCopyAndExecInDockerContainer(deployScript);

        DbContext dbContext = TestHost.Services.GetService<DbContext>();
        await dbContext.Database.OpenConnectionAsync();
        await dbContext.Database.EnsureCreatedAsync();
    }

    private async Task ReadSqlDeployScriptCopyAndExecInDockerContainer(string deployScript = null)
    {
        if (deployScript == null)
        {
            deployScript = File.ReadAllText(IntegrationTestsConstants.SqlDeployScriptPath);
        }

        ExecResult execResult = await this.CopyAndExecSqlDbCreateScriptContainerAsync(deployScript);
        this.logger.LogInformation(execResult.Stdout);

        const int successExitCode = 0;
        if (execResult.ExitCode != successExitCode)
        {
            this.logger.LogError(execResult.Stderr);
            throw new Exception(execResult.Stderr);
        }
    }

• 接下来，将SqlDeploy脚本复制到SQL docker容器
• 然后使用/opt/mssql-tools/bin/sqlcmd linux 命令来部署SQL 部署脚本(-v 参数目前不起作用，也许在您阅读错误的那一刻已经解决了)

    public async Task<ExecResult> CopyAndExecSqlDbCreateScriptContainerAsync(string scriptContent, CancellationToken ct = default)
    {
        await this.sqlDbContainer.CopyFileAsync(IntegrationTestsConstants.DockerSqlDeployScriptPath, Encoding.Default.GetBytes(scriptContent), 493, 0, 0, ct).ConfigureAwait(false);

        string[] sqlCmds = new[]
         {
            "/opt/mssql-tools/bin/sqlcmd", "-b", "-r", "1",
            "-S", $"{this.sqlDbContainer.Hostname},{this.sqlDbContainer.ContainerPort}",
            "-U", this.sqlDbContainer.Username, "-P", this.sqlDbContainer.Password,
            "-i", IntegrationTestsConstants.DockerSqlDeployScriptPath,
            "-v", $"{IntegrationTestsConstants.DefaultDataPathSqlEnvVar}={IntegrationTestsConstants.DefaultDataPathLinux} {IntegrationTestsConstants.DefaultLogPathSqlEnvVar}={IntegrationTestsConstants.DefaultLogPathLinux}"
        };

        ExecResult execResult = await this.sqlDbContainer.ExecAsync(sqlCmds, ct).ConfigureAwait(false);

        return execResult;
    }