Using Azure DevOps for Microsoft SQL Databases with SSDT
HTML-код
- Опубликовано: 28 май 2024
- When working on a database with SQL Server Data Tools (SSDT), developers need to deploy changes to further environments while maintaining the consistency of databases between environments. On this week’s episode, Microsoft MVP Kamil Nowinski ( / nowinskik ) presents how to publish the solution manually and then use Continuous Integration and Continuous Deployment (CI/CD) in Azure DevOps. As a result, you will have a fully automated process of building and releasing changes of Microsoft SQL Server database to target SQL Server across multiple environments.
Наука
A really great demonstration. Clearly, Kamil deserves his 'MVP' status.
This was a brilliant explanation! - BUT PLEASE do one for Data deployment
This tension built with the disappearance of the internet and waiting for its return ... priceless :D
We keep it real here at Data Lounge, Michał ;)
Good work guys, this CI/CD pipeline process for the SQL DB on Azure with Azure DevOps tutorial is great
Thanks, Jay. We are very happy that these tutorials can help people.
Been looking for a guide like this. Thanks heaps
You welcome Steelgear! What other topics would you like us to cover?
Absolutely Amazing , i was searching for such a simple non complex demo video.👍
Thanks for the tutorial this exactly my team lucking of.
Thanks guys! Time to play with this :)
Love the energy btw!
Rock on!
Hello Kamil.. Great video... Thanks for sharing...
Thank you so much for this. It would be so useful to see the next step. Im assuming that on the initial creation you then have to run your full data factory pipelines to being all the data in? You you have to do that every time? How do you deal with things like in the next sprint, 3 new columns get added to a table for example. A few days ago I didn't have any understanding of the process at all so to be able to ask more targeted questions now is great
Very useful - thanks for this.
Thanks, Guys for clear and instructive Demo. It covered most of the stuff I need for now as a learner.
Halfway through the CD part, I needed to create a service connection for my Azure subscription and use this service connection name in an Azure Web Site Deployment task in a release pipeline. It would have been nice if you have covered it too.
Thanks Eric. No worries, we will do.
Build is just creating dacpac file right? It means it is not going to change/update anything in the master branch or DEV Env. Please suggest
All is great , what is missing is how you configured git in VS to push code to devops
Hi Gregory : You mean the actual configuration in Visual Studio so it can see the Repo in DevOps?
@@DataLounge Yes, exactly.
Once I run the release pipeline, I'm getting "azure sql pipelines A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
(provider: TCP Provider, error: 0 - No such host is known.)". I Tried allowing all Ips form 0.0.0.0 to 255.255.255.255 but still getting the same error.
Note: I'm able to login into the server using SSMS
Very useful video thx!!!
How do you do selective deployment of tables? e.g. How you can update build to ignore 2 tables out of 5?
Ankur, it's not something you can do by default. It's possible, but not so easy.
by default it will ignore drift, so if the table isn't in your project then it won't drop it. You can also setup the *.publish.xml file under advanced/drop to still remove drift from the target for all object types accept tables. In the copy-files task you must specify both the DACPAC and the *.publish.xml to be in your artifacts. Then in the release pipeline your dacpac deployment task has a param for that can point to that *.publish.xml
@@KamilNowinski Sir, then who will teach that not easy thing? anyone should step out and teach to us.
@@asharnavya The only materials I have on this particular topic are in my course: learn.sqlplayer.net/database-projects-with-ssdt-dacpac
@@asharnavya Take a look at this project as well: github.com/GoEddie/DeploymentContributorFilterer
What if there is already a database created and its empty. Will it create objects?
I have tried from one of my projects where I have created
1. Server
2. Database
However, when I executed the dacpac file, it did not thing 😢
It did not create any objects
Great video!
Thanks for the visit
Great Video. Can we also have unit testing and the DB back up strategy defined via the pipelines ?
@Ajay singh Chouhan, It is just plain deployment, no one is talking about the complexities, specially data loss. I am agree with your question and I wish @NowinskiK will upload a new video
Another Question. I assumed that clicking Rebuild takes a new version of your Azure Database and adds everything into Solution Explorer but When I click Rebuild nothing happens. The only way I can get it to work is each time create a brand new project from the Azure Database but obviously I don't want to do that. Have I missed something important because I just cant get it to work
Hi Kamil,
I have one question regarding dacpac
Can we select only those schema from database, which we want to deploy and ignore others.
You can do it by using an additional extension. We will prepare a separate video for this.
Great vid
Thank you!
Hi Kamil,
a very good video. It helped me a lot to build my first CI/CD pipeline.
Currently I am facing the challenge to implement a solution with multiple DB projects into a CI/CD pipeline. How do I include the SQLCMD variables for references? I get the following error message in the release pipeline during the task Deploy a SQL Server database using DACPAC or SQL scripts: Missing values for the following SqlCmd variables: XXX
Great for deploy new database , but how do you manage the CI/CD for new objects in existing database?
Mauro, the process is exactly the same. EXACTLY. You just need to change something and rerun the pipelines. SQLPackage (app behind CD pipeline task) will do the rest, i.e. generates the incremental script and execute it against the existing database.
Well done both of you, Thanks a lot for your time and energy to create this tutorial. I'm also want to create the build & release pipeline for the database in existing projects in my organization. But my client asks me one question while building the build pipeline then you create a .dacpac file, right? which contains all database schema or only modified tables schema? My Client not interested to deploy every time the dacpac file contains thousand of tables in production. He interested only in modified tables, store procedures, and functions.
Also, it will be so grateful if you can create another tutorial for the build and release pipeline for SQL reports. God bless you.
nice video, the DACPAC file we create as a artifact of build pipeline can also be created directly from SSMS - Why do we need to create a Solution File then a DACPAC file? thanks
The DACPAC file is created as an artifact within the build pipeline so you don't have to manually create the dacpac and rely on a manual deployment/execution against your target environment, which is the complete opposite of the "CI/CD" deployment strategy shown in the video. Also, you can leverage the DACPAC and application artifacts to create a true App/DB versioning control for your CI/CD to target environments.
What I'd like to see is if it creates a 'Pre' and 'Post' dacpac (or collection of .sql files) for application deployments that may need columns added to the application (these would be contained in the 'pre' app deployment file) then new index creation, or maybe even views/functions that need to be created inline with the app deployment, or afterwards altogether.
Thank you so much. Suppose we have added a new column in table. I need to update a default value in this newly added column after the deployment. I used powershell to do this. Any other option is available? is it possible using SQL Server database deploy task? I tried SQL Server database deploy but I am getting the error No files were found to deploy with search pattern. Can you advice?
Hi @Sreejith, are you sure you followed all the steps we presented? I'm not sure what you mean by "with search pattern", but adding new column with default value is pretty easy in database project itself and should be appropriately handle during the deployment process.
The first time running build or release pipeline took time by default and later execution will be fast in a self-hosted agent. But windows hosted agent takes time every time due to it download all required component in an agent then build or release process.
Even "Azure Pipelines" hosted Windows agent contains all required tools like vsbuild.exe, sqlpackage.exe and DacFx to build the solution. It shouldn't download anything extra. The reason might be that these machines are usually slower than VM you can set up by yourself.
What is the point of CD/CI if you can't test before deploying your build?
Main: to automate manual steps that can cause mistakes. Next step you can add unit, integration, smoke tests, etc. The goal of this video was only to show (in the easiest way) how to build it appropriately for Microsoft SQL Server databases.
HI Orlando : Did Kamil´s reply answer your qustion?
Hello Kamil, Thanks for this video, and now i am trying to implement CI CD for SSIS Solution which i want to deploy on UAT,QA, and PROD environment and along with that want to create Job.
Can you please post one video for ssis with steps how you did for database.
Do you mean SSISDDB catalog database?
thanks
Good work but the permissions setup to deliver to Azure or even better, to an on-prem server should have been covered...
Hi Alex: Very good point! When you mean permissions, you mean deployment to actual Resource Group?
I have noticed, every teacher is in fear to not use publish profiles and they are not worried about the data loss during deployments. I haven't found any expert who actually talking about the data loss during deployment and updating the schema. If any changes occur in existing tables then how to deploy Azure SQL Database using CI / CD pipelines without losing Data?
Ashish, the point of such a short video demonstration is to present the major goal and methodology. That's why we do not go deeper - in order to not disturb the audience with additional details. In a real project, you are right, data loss does matter and you need to anticipate such a scenario in a deployment, e.g. sometimes prepare pre/post-deployment scripts to do the extra job. You can find more details and specific scenarios covered in my commercial course here: learn.sqlplayer.net/database-projects-with-ssdt-dacpac