Sql Server Database projects are a great way to define your database structure and keep in in source control. Instead of maintaining a large list of migration scripts, you can instead define your database declaratively in the Sql Project as if you were creating a .net solution. This project style also facilitates the publishing of changes to Sql Servers. The publish process automatically detects what needs to change and creates the necessary Sql code to perform those changes.
One of the challenges of using Sql Server Database projects is to include the Sql Project in your CI/CD pipeline so that it can automatically publish the changes to a Sql Server.
Microsoft has migrated the Sql Server Database project format to the new “SDK style” project (introduced by .NET core).
A generic way to integrate it is to call the commands from the command line as most CI/CD pipelines support some sort of command line invocation. The steps to run from the pipeline are:
- Build the project so that it generates a dacpac file
- Install the SqlPackage dotnet tool
- Run the SqlPackage tool with the “publish” option to publish the dacpac to the desired Sql Server.
The actual commands look like the following (assuming your database is named MyDb and your project is called MyDb.sqlproj):
Cd <folder where your sql project is>
dotnet build /p:NetCoreBuild=true
dotnet tool install -g microsoft.sqlpackage
SqlPackage /Action:Publish /SourceFile:"C:\AdventureWorksLT.dacpac" /TargetConnectionString:"ConnectionString to the db you want to publish to” /UniversalAuthentication=True
The “/UniversalAuthentication=True” is required if you need to use integrated windows authentication to authenticate with the target sql server, i.e. if you use Integrated Security=True in your connection string.
References
Build a Project from the Command Line - Azure Data Studio | Microsoft Learn
Top comments (0)