1. Open Visual Studio and Create a New Project. From the templates, search for SQL Server Database Project and Click Next.
2. Provide the name of the project and select the location of project.
3. In the Solution Explorer, Right-click on your project name, which in our case is Database Project. Click on Import>Database
4. Set the connection to your database and click Connect.
Once done Click Start.
Once finished, Click Finish.
5. You can see your Tables, Views and Stored Procedures in the Solution Explorer.
6. Now, create a Git Repository. We will be using Github in this example.
7. Once this is done, you can see the Repository name.
8. Now create a new Table named [Testing_Table].
9.Right Click on Database Project, go to Properties and go to Build.
Build output path is where dacpac files will be created after each Build.
Go to the Project Settings and set the Target Platform to Microsoft Azure SQL Database.
Once this is done , Build Solution.
You can see the following files are created in the path which we had set previously.
10. Go to Git Changes and Do Commit All and Push.
11. You can check your Git Repos and see the following files.
12. In Azure DevOps portal, Create a New Release Pipeline and Select an Empty Job
13. Add and Artifact, and set connection to your Github Repos.
14. For Continuous Deployment, do the following. So that whenever we Commit and Push new Changes to the repository, a release pipeline will be triggered to create a new release.
Note: If this step is not done, we will need to manually create a new Release everytime.
15. Click on 1 job, 0 task and click Agent Job and add and SQL Server database deploy task.
16. Provide the location for the DACPAK file, from the git repos
Provide the SQL Credentials. We have used variables here for SQL Username and SQL Password.
Save and Create Release.
Click on Create.
Wait for the tasks to complete.
Once done, you will see the following.
17. Now, If you will check , you can see all the existing tables and stored procedures as well as the newly created Testing_Table in the cicd_db_prod.