Skip to main content

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
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

SqlPackage Publish - SQL Server | Microsoft Learn

Comments

Post a Comment

Popular posts from this blog

Powershell script for converting JPG to TIFF

The following Powershell script will convert a batch of JPEG files to TIFF format: #This Code is released under MIT license [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") $files_folder = 'C:\path-where-your-jpg-files-are\' $pdfs = get-childitem $files_folder -recurse | where {$_.Extension -match "jpg"} foreach($pdf in $pdfs) { $picture = [System.Drawing.Bitmap]::FromFile( $pdf.FullName ) $tiff = $pdf.FullName.replace('.PDF','').replace('.pdf','').replace('.jpg','').replace('.JPG','') + '.tiff' $picture.Save($tiff) }
Post a Comment
Read more

Power Automate: SFTP action "Test connection failed"

When I added an SFTP create file action to my Power Automate flow ( https://flow.microsoft.com ) , I got the following error in the action step, within the designer: "Test connection failed" To troubleshoot the Power Automate connection, I had to: go the Power Automate portal then "Data"->"Connections"  the sftp connection was there, I clicked on the ellipsis, and entered the connection info It turns out, that screen provides more details about the connection error. In my case, it was complaining that "SSH host key finger-print xxx format is not supported. It must be in 'MD5' format". I had provided the sha fingerprint that WinScp shows. Instead, I needed to use the MD5 version of the fingerprint. To get that, I had to run in command line (I was in a folder that had openssh in it): ssh -o FingerprintHash=md5 mysftpsite.com To get the fingerprint in MD5 format. I took the string (without the "MD5:" part of the string) and put
Post a Comment
Read more

Alert if file missing using Powershell

The following Powershell script can be used to send an email alert when a file is missing from a folder or it is the same file from a previous check: $path_mask = "yourfile_*.txt" $previous_file_store = "lastfileread.txt" $script_name = "File Check" ###### Functions ########## Function EMailLog($subject, $message) {    $emailTo = "juanito@yourserver.com"    $emailFrom = "alert@yourserver.com"    $smtpserver="smtp.yourserver.com"       $smtp=new-object Net.Mail.SmtpClient($smtpServer)    $smtp.Send($emailFrom, $emailTo, $subject, $message) } Try {    #get files that match the mask    $curr_file = dir $path_mask |  select name    if ($curr_file.count -gt 0)    {        #file found        #check if the file is different from the previous file read        $previous_file = Get-Content $previous_file_store        $curr_file_name = $curr_file.Item(0).Name        if ($
Post a Comment
Read more