DB deployments with Fake.Sql.SqlPackage

In my previous post, I presented how we can deploy multiple visual studio database projects using Fake build tool. Here, I will present another option which is possible with version 5.19.0.

This version has a new module called Fake.Sql.SqlPackage, which is a redesign of previous Fake.Sql.DacPac module. The reason, why the previous module needed redesign, it was a missing option (from my point of view crucial option) - publish profiles. I raised that point on GitHub here and solved it with this pull request. If you are interested in SqlPackage itself you should check the documentation.

To sum up this a bit long introduction - all that means that I had a pleasure to be Fake build contributor.

Fake build feature release list

New publish target

All code can be found here and is a continuation of the previous post. The logic of DeployDb target doesn’t change and thus I will skip it here. I will focus only on publish function which is used by this target.

Previously we used MSBuild tool to publish database projects.

let publish env (projectDirectoryPair:string * string) =
    let project, projectName = projectDirectoryPair
    MSBuild.runRelease  (fun p ->
        { p with Properties = [ ("DeployOnBuild", "true"); ("SqlPublishProfilePath","./Profiles/" + env + ".publish.xml") ] } ) (buildDir + projectName + "/") "Publish" (Seq.singleton project)
       |> Trace.logItems "DbDeploy-Output: "

In this approach, we build and publish the database. It means that we build project twice (once in BuildDb target and once in DeployDb target). With Fake.Sql.SqlPackage we can avoid that and use dacpac files created in BuildDb target as it is presented below.

let publishWithSqlPackageModule env (projectDirectoryPair:string * string) =
    let project, directory = projectDirectoryPair
    let dacPacPath = sprintf "./build/%s/%s.dacpac" directory directory
    let profile = sprintf "./%s/Profiles/%s.publish.xml" directory env

    Fake.Sql.SqlPackage.deployDb (fun args -> { args  with Source = dacPacPath; Profile = profile })

The function itself is simpler (in both version we just invoke Fake modules, but here we need to provide fewer parameters), but what is important even more. It is faster too. Publish with MSBuild takes 78 seconds.

Deployments statistics with MS Build

While deployment with SqlPackage takes 66 seconds.

Deployments statistics with SqlPackage

It may not seem like much, but SqlPackage version is 15.5% faster just on this example project. I made a similar switch in my company projects, which has a more complex database structure than this here. Numbers are much better - 172 seconds with MSBuild version versus 90 seconds with SqlPackage version. What gives 47.7% speedup! And it saves not only my time but everyone’s time as we use it on our CI server.

Summary

The conclusion will be really short. It is not the end of the world if open source software doesn’t have needed feature. It can be your opportunity to pay back to the community.

Comments