I have had quite a lot of issues in regards to the whole dev to prod process with the SQL database. my last attempt which I wrote in this blog worked well until I shut the project down. Then once reopened I would always lose my Project or GIT, so I went back to the drawing board and did a lot more research and here are my new findings.
There is a Data Factory part to this but I have already made a blog about this and it has consistently worked ever since setting up the dev to prod process
- Azure SQL Database and Server
- Visual Studio (Enterprise)
- Azure DevOps
Azure Devops Repository
First of all you need to have an Azure Devops set up (I wont go into detail on this here)
In the Devops repos I have a Folder for Data Factory in the repository. The Folder for SQLDB will be created later.
- In Devops Ensure you have a GIT repos Created Then Click CLONE to copy the GIT location URL
In this example I am cloning right at the top of the repository
- Click the Copy Button
You cannot do this as yet in Visual Studio code. It has to be Visual Studio and I have Visual Studio Enterprise 2019
- Ensure SSDT is installed in Visual Studio https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15
- Go to visual Studio and In Get Started select Clone Repository
- You can Copy the Repository Location from the Clone Copy (Or Browse a Repository – Azure DevOps or GitHub)
- make sure you are happy with the path for the Local Copy
- Click Clone. Your Local repository is then shows in Solution Explorer
- This has added the folder on your C Drive (It added the top level and the dataFactory and SQL DB Folders)
- And You can see this project in Solution Explorer
- Copy path of C Drive (And the folder for example SQLDB)
- Visual Studio – In the top bar choose create File – New – Project
- Choose SQL Server Database Project – Next
The Project with be SQLDB and will contain the SQL Objects
- Click Create
In Visual Studio SOLUTION EXPLORER: You can see your empty database objects.
On the C Drive, Note you now have a SQLDB folder along with the Data Factory Folder
- Right click on the database name in Solution Explorer and go to properties
Its important to be in the right version for the target platform
- Right click on database name and Import – database
- Select the Connection location of the development database (and Show connection properties to make sure your username and password are ok and the database connects.
- Import objects into the Local Project (No need to Select it in the above box)
- then Click Finish Note that all your objects are now in Solution Explorer and on the C: Drive (Your local copy)
- Is the project Complete? – Build – Rebuild solution which checks and validates the objects
Any time anything changes you need to rebuild your solution to update the code.
Warnings and Errors
For Warning and errors you can see all the issues by clicking on them. The build may fail because of errors. These always need resolving before you send to the target DB e.g. Production
Error Example Warning: SQL71558: The object reference [staging]..[KEY] differs only by case from the object definition [staging].[ST2].[Key].
- Click click on the database in Solution Explorer and go to Properties.
- In project Settings untick validate casing on identities
Error Example Warning SQL71502: Procedure: [dim].[USP_Date] has an unresolved reference to object [sys].[all_objects].
You can add the master database as a reference (Right Click on references)
Add Database Reference
Rebuild your codebase. Its important here to make sure your warnings and errors have been dealt with
Rebuild updates your project locally after updating – I will look at how making changes with for example SQL Server Management Studio changes the process in a later blog.
- Publish to Git Repository – GIT Changes and make a note of your change Commit All
Then click the arrow to Push changes to the GIT repository
We now have the code in the repository in DevOps
It seems annoyingly easy to slightly mess your folder structure up. Here I have a SQL DB Folder and another SQLDB Folder inside.
I only wanted the one. This does keep happening to me and its very frustrating. Any pointers to where I went wrong would be really appreciated
Create your CI (Continuous Integration) Pipeline in Azure Devops
Now we have the Code in GIT we can create our artifacts for the release pipeline.
- In Azure Devops go to Pipelines and Release Pipelines
- Click New Pipeline
- And choose your repository
- Select a template
Right click on tasks and remove selected tasks until you are left with the following
You don’t really have to do much with these three jobs
At the Pipeline level ensure you use the right Agent. For example Windows -2019. We had errors because we use an OpenJson function in the SQL code but setting the right agent resolved this issue
All the other jobs are parameterised. This should now be all set
- Save and Queue and you can then run the pipeline to create your artifacts Save and Run
- There may be warnings here. For some reason the warnings you clear in Visual Studio seem to show in Devops. I would like to do a bit more research on this.
- But If a warning hasn’t failed the process you should now have your Continuous Integration artifacts.
Create your CD (Continuous Delivery) Release Pipeline in Azure Devops
Now we are onto Continuous Delivery. Moving the new code into Azure SQL DB
- In Azure Devops to to Pipelines – Releases – New release
- So for Artifacts click Add
We want to use the latest build
now Add a Stage. In our case we are using Prod so its a simple release
- Start with an Empty Job
Add a task to the job
Here we chose the production Subscription
And we link to the DACPAC file that was created with the build from visual Studio. The DACPAC contains all the objects in SQL
The database uses variables and you can set these up in the variables tab
You can create a release to update your Production database
Once pushed, check your SQL database in Production to make sure you are happy that your changes have gone through.
And you can save your visual studio project and reopen. the next stage is to update some objects and go through the process again so watch this space