Deployment of Microsoft SQL database with Azure DevOps

Deployment of Microsoft SQL database with Azure DevOps

Hello, data folks!
I have published several articles about databases and their maintenance in SSDT project on the blog. One of the posts explained step by step how to import a database from a production environment and thus create the SSDT project.
In this video, I will explain to you how to set up build and release pipelines in Azure DevOps, so we could deploy and publish SSDT database project to our target SQL Server instance incrementally.
This is a very simple example only – how to do that with a minimum number of steps.
But in the next videos, I will show you how to extend it by adding more and more features.

Stay tuned!

Previous Using SQL Server on Docker
Next Last Week Reading (2019-12-22)

About author

Kamil Nowinski
Kamil Nowinski 171 posts

Blogger, speaker. Data Platform MVP, MCSE. Senior Data Engineer & data geek. Member of Data Community Poland, co-organizer of SQLDay, Happy husband & father.

View all posts by this author →

You might also like

ASF 019 video invitation

Check how many bicycles he has got at home and where was the destination of last year of a 400 km trip? Which tool among Azure is the closest to

ASF 025 video invitation

It’s time to announce and disclose our next guest of the podcast “Ask SQL Family”. Ladies and gents, please to meet Rob Farley, who has come to us from Australia.

Video 1Comments

ASF 021 video invitation (Star Wars Theme)

Since now, we will not keep our guests in secret. We want you to be prepared and wait if you’re interested in. First time 2 vs 2 in #AskSQLFamily podcast.

4 Comments

  1. Floatila
    December 22, 06:26 Reply

    Could you please suggest how to add new seed data/lookup data into two tables which has foreign key relationship. These table already has some data. I want to add new lookup data to existing data.
    Please suggest.
    How to use Merge Into. Any other ways to setup lookup data

    • Kamil Nowinski
      December 23, 22:46 Reply

      Generally speaking, if you have a reference data generated from SSDT – all IDs should be static (and identical between environments). That approach still leaves flexibility to manage the data in the table by users.
      All other scenarios, although possible, making the cases complicated. Check out this post on my blog where I described how to script the data and generate MERGE statement:
      https://sqlplayer.net/2019/10/script-deploy-the-data-for-database-from-ssdt-project/
      In a more sophisticated case – you still can modify the generated script by adding subselect as a lookup for the foreign key(s).
      If I didn’t answer your question – please DM me and describe your scenario and reason for having dynamic values of FK.

  2. Larry
    April 21, 20:57 Reply

    These are great examples, however we’re working with an older application in production, not a new application. So adding a field is simple enough with an alter. I’m curious about two things. What about renaming or deleting a field what has data in it.

    Also, what about rolling back a release. With regular code I would just re-deploy the last Azure DevOps release to restore back to our last good release. What if we’ve deployed the database release but something is wrong and we need to go back to last state. What happens, especially with the data if we had added new fields or tables in the new release?

    • Kamil Nowinski
      May 03, 15:19 Reply

      Hi Larry, thanks for your comment here.
      When you say “working with an older application in production” you meant having migration approach for deployment? If so, that’s also fine and works for many companies. It’s just another approach.
      In terms of renaming columns – SSDT has a special mechanism which keeps these kinds of operation as refactoring and run them only once.
      Every deployment (changes script) can wrap everything up on to transaction. Once something goes wrong, the whole transaction would be rollbacked.

Leave a Reply