3

I am trying to deploy a .dacpac which was generated from a SQL connection string into a SQL server 2017 ubuntu docker container for integration testing.

I am using the DacFx NuGet package from Microsoft to generate the .dacpac. I tried to find options to ignore the Filegroup but couldn't find any.

Localhost SQL Server Extract Connection String: Server=localhost;Database=MyDatabase;Integrated Security=true

What I want to achieve: Get the schema from localhost, without any FILEGROUPS or dependent .mdf file into a dacpac, and apply it to a SQL docker container, without having to mount volumes in docker, as I want everything to be kept in ram memory and when the docker container stops the data is gone. Previously I had successfully generated the dacpac and applied it to the docker instance.. when there was no FILEGROUP localhost DB. Also found IgnoreFilegroupPlacement option forDacDeployOptions objbut for some reason, it does not work.

Code:

I am passing the following options to the extract method:

        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.");

These are the options that I am passing to the deploy method which extracts the dacpac from the connection string:

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;

Error: And this is the error I am receiving from SQL docker container:

If I could ignore the filegroup at all would be awesome because I don't need it.

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];
David P
  • 71
  • 1
  • 6
  • There is something wrong with connection string. – jdweng Nov 02 '22 at 16:31
  • 1
    Is database attached to a server or not attached? When attached you should be using Servername and when not attached use attach LocalDb. Never use both Servername and LocalDb. When attached the Server knows location of mdb file and owns the file and doesn't allow localDb to be used. When attaching a file the file must be physically on same machine as server. It can be a drive like e:\ but the drive must be mounted to the same machine as server software. – jdweng Nov 02 '22 at 16:36
  • Updated the question with SQL connection string. – David P Nov 03 '22 at 07:59
  • 1
    Open Server with SQL Server Management Studio, The login window should show Windows Credentials and the server/instance. the server instance in the connection string must match the instance/server in SSMS. Than try a query using SSMS to make sure you have access to the database. The error indicates the server cannot find the mdf file. So I expect that the query using SSMS will fail. – jdweng Nov 03 '22 at 08:42
  • The connection string looks the same as written in the description for localhost. – David P Nov 03 '22 at 13:01
  • What about the instance? Connection string should be server\instance (localhost\sqlserver). You may have more than on server on machine. Then you should also check log files with SSMS in explorer under Management. If you are connecting to the right server than you should see a log entry when you connect from c#. You should also right click on database and check the path of mdf file. – jdweng Nov 03 '22 at 13:19

1 Answers1

1

For anyone encountering this issue: "The path specified by ".mdf" is not in a valid directory"

I was able to find a workaround using the following steps:

  • Extract a stream Dacpac using the DacServices NuGet package from my connection string, which the source database is a Windows MSSQL using the following DacExtractOptions:

Keep in mind I changed some var names, but the basic idea should work!


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

And I am using the following code to generate the Dacpac stream:


    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);
}

From the extracted Dacpac stream I generate a SQL deploy script which I usually save to the disk using the following code:

    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);
    }
  • Next, I will go manually into the generated deploy script and change the following SQL vars to match with the MSSQL Linux docker version, the reason for this is that if you try to override the SQL vars from DacServices options or try and set the docker container SQL vars it will not work because there is an issue with sqlcmd for Linux not overriding the SQL vars.
GO
:setvar DefaultDataPath "/var/opt/mssql/data/"
:setvar DefaultLogPath "/var/opt/mssql/log/"
:setvar DatabaseName "MyDb"
:setvar DefaultFilePrefix "MyDb"
  • Next, when starting the integration tests I will have the following method written to populate the sql docker container db:
    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);
        }
    }
  • Next, copy the SqlDeploy script to the SQL docker container
  • Then use /opt/mssql-tools/bin/sqlcmd linux command to deploy the SQL deploy script(the -v argument does not currently work, maybe at the moment you read the bug was solved)
    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;
    }
Ahmad Othman
  • 853
  • 5
  • 18
David P
  • 71
  • 1
  • 6