Skip to main content

Sql Server Projects (sqlproj) deployment via CLI

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:

  1. Build the project so that it generates a dacpac file
  2. Install the SqlPackage dotnet tool
  3. 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 /p:Configuration=Release
dotnet tool install -g microsoft.sqlpackage
SqlPackage /Action:Publish /SourceFile:".\bin\release\AdventureWorksLT.dacpac" /TargetConnectionString:"ConnectionString to the db you want to publish to”

Microsoft For the older style of project (i.e.: non sdk style sqlproj), you can replace the second line with:

msbuild "nameofsqlproject.sqlproj" /p:Configuration=Release


References

Build a Project from the Command Line - Azure Data Studio | Microsoft Learn

SqlPackage Publish - SQL Server | Microsoft Learn

Comments

Post a Comment

Popular posts from this blog

Mail labels and letter templates for jasperreports

The following are free (MIT license) mailing labels and letter templates for jasperreports that you can download and use in jasperserver and/or ireport: Update 3/15/2011 : I moved the Mail templates zip file here . Please consider making a small donation if the templates are of help to you, Thank you! If you need more information on how to use those templates please leave a comment in the blog.
14 comments
Read more

How to get the raw data posted to a Java Servlet

While working in porting GeneCMS to Google App Engine and Silverlight , I stumble at one annoying problem: how to get the raw data that was sent by the browser via a POST. In this GeneCMS port, the silverlight client actually generates all the HTML that the server used to generate. The raw generated HTML is then sent to the GAE application, with some metadata about the generated pages. The GAE servlet then reads the posted data as a string and extract the individual generated pages. Each generated page is then saved into the GAE database to allow the CMS to server the pages. After tweking around in the wrong places, I finally found out that HttpServletRequest’s getInputStream method is an input stream to the raw data posted. So I modified my servlet to get the POST data into a string like this: String posted_data = ""; BufferedReader in = new BufferedReader(new InputStreamReader( request.getInputStream())); String line = in.readLine(); while (line != null) { posted...
2 comments
Read more

How to create online multiplayer HTML5 games in Contruct2

  Construct2 can use websockets to send and receive messages between games. By using socket-io , we can use a Node.js script as the server and my modification to the socket-io plugin for Construct2 to allow the games to synchronize data between them in real-time. There are two parts to this design: the Node.js server and the Construct2 clients (the games playing). The main part of building an online multiplayer HTML5 game is to plan: how the clients will communicate how often and what to communicate how much of the logic will go into the server and how much to the client. In my sample game, I chose to have each client own a player and have the server just relay messages: Use string messages in the form TypeOfMessage, Parameter1, Paremeter2, Parater3, etc to communicate. Have the clients send their player position about 16 times a second. Whenever their player shoots, the client needs to send a message immediately. Almost all of the game logic will...
9 comments
Read more