The Dev to Prod Process
Initially in part one we set up the process with Visual Studio 2019 and Devops and moved all our objects across to Production. Then with Data Factory we moved data from the production data lake into Production Azure SQL DB
We have all the Source data in a data lake (and its been assured that the Production data lake is the same as the Development data Lake)
We have a Data Factory in Production that goes through a DevOps release pipeline so we should now be able to use the Production Data Factory to Load all the Production Data into the Production SQL database on a regular basis.
What happens when you already have Objects and data in Target?
Only the changes will be released. So the next time you release into production you are releasing the delta
Lets see this in action
- The Initial SQL database was released into Production with Visual Studio
- A Production Data Factory moved all the data into the new Objects
- Now we have an updated Dev SQL Database.
Open your visual Studio Project
Open the project that was created in the last session.
In SQL Server Object Explorer
You have the Azure Server and Database. Right click and Refresh
And you have the Local Project DB which contains all the Objects. We can Check the Schema differences between the project DB and the latest DB within Azure
Go to the dev SQL Database (Click on the icon to the left of the server to open it up)
on the Azure SQL Database. Right click and Choose Schema Compare
For Select Target , select Select Target
Open the Local DB Project. The Database in this case is the same name as your Solution in Solution Explorer. (Now I know I should have given my solution the project name and my Project an extension of _DB to differentiate the two)
Now you get to see what has been deleted. in this case a table and a Procedure has been dropped
Next we can see changes. If the Table Change is clicked on, you get more information about that change in the object definitions. In this case a test column has been added.
This creates a quandary when it comes to loading in the data because this table should be fully populated but the new column will be blank. Is it possible to do a full load for these updated tables with Data Factory, OR do we need to look at something more complex?
And finally additions. In this case there are lots of new Tables procedures and two new functions.
Once happy Click Update and your changes will be published into Solution Explorer
To Check, have a look for some of the new tables, SPs etc in Solution Explorer
Once completed you can click the x icon to close the comparison window and you can save your Comparison information
Rebuild the Project in Visual Studio
Now we want to Rebuild our Project within Solution Explorer
Right click on the Project in Solution Explorer and choose Rebuild. this rebuilds all the files.
- Rebuild rebuild your entire project
- Build just rebuilds on the changes
Process your Changes with GIT
Now its in your project you need to process those changes with GIT
in Git changes. Commit all and Push
And remember to add a message
These objects should now be in Devops. You can go to Devops Repos. then to your database specific project and check for new tables, SPs etc
My new junk dimension object are there so this is all working.
Release the new database objects into Production database
now all the code is in the repos we can push the new and updated objects into production with a DevOps Release Pipeline.
There is already data in my production database. As as initial starting point I do a quick check on a few tables to get a feel of the data.
This SQL Script allows you to do a quick check on row counts in the production database
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
ORDER BY [TableName]
Choose the database repository (You should also have a repository for data factory)
Go to Pipelines. Before releasing to Prod we actually have to build all our code into an artifact for the release pipeline
Click on your Project _DBComponentsCI pipeline (Continuous integration) set up in Part 1
Lets remind ourselves of this pipeline by clicking Edit
We build the solution file from the Repos in devops. Then Copy files to the staging directory. Finally publish the artifact ready for release.
Come out of Edit and this time choose Run Pipeline
Once its run, there are warnings again but for the time being I’m going to ignore these
Otherwise the build pipeline runs successfully.
Now we have rebuilt the artifact using the build pipeline, go to pipelines, Releases and to your DB_CD release (continuous delivery)
We have a successful release. I can run the above SQL and check for differences. for a start, there were 39 objects and now there are 43 so you can immediately see that our production database has been updated.
The row count shows that we haven’t lost any data. We have simply updated the objects.
Part three will allow us to look more in-depth at the data side. How we deal with the data in Dev and Prod