A Fine Slice Of SQL Server
Kevin Chant shows off the power of Azure Test Plans:
In this post I want to cover using Azure Test Plans for Data Platform deployments. Because using it to manage test plans can be very useful.
By the end of this post, you will know what Azure Test Plans are and how they can be useful for data Platform deployments.
Click through to see how this feature in Azure DevOps works and how you can use it to test your deployments.
Comments closedHamish Watson figures out what’s in a name:
I had created a pipeline using YAML – which was called InfrastructureAsCode as the YAMP file was in the root directory.
However I wanted to move it into a folder .\InfrastructureAsCode\pipelines\… and run the YAML file from there – as I would have a non-prod and PROD version of them (as the schedule was different for each).
Click through to see how Hamish was able to resolve this.
Comments closedBy the end of this post, you will know how to create a dacpac for a dedicated SQL Pool within Azure Pipelines for your CI/CD deployments. Plus, how you can synchronize a database project created in Azure Data Studio with a Git repository in Azure DevOps.
In a previous post I covered how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio. In that post I stated that you could create a dacpac for the database project using Azure DevOps.
With this in mind, I will use the same database project that I created in that post.
Click through for the process.
Comments closedStuart Ainsworth has a process:
A big part of my job these days is looking for opportunities to improve workflow. Automation of software is great, but identifying areas to speed up human processes can be incredibly beneficial to value delivery to customers. Here’s the situation I recently figured out how to do:
1. My SRE team uses a different Azure DevOps project than our development team. This protects the “separation of duties” concept that auditors love, while still letting us transfer items back and forth.
2. The two projects are in the same organization.
3. The two projects use different templates, with different required fields.
4. Our workflow process requires two phases of triage for bugs in the wild: a technical phase (provided by my team), and a business prioritization (provided by our Business Analyst).
5. Moving a card between projects is simple, but there were several manual changes that had to be made:
– Assigning to a Business Analyst (BA)
– Changing the status to Proposed from Active
– Changing the Iteration and Area
– Moving the card.To automate this, I decided to use Azure Logic Apps.
Read on to see how Stuart did this.
Comments closedKevin Chant has a process for us:
In this post I want to cover how you can store dbatools PowerShell module as a package in Azure DevOps. By using the Azure Artifacts service.
I want share some knowledge about this because did a demo of it at Malta Data Saturday. By the end of this post you will have a better understanding of Azure Artifacts and a workaround if you encounter a problem publishing a package.
Read on for the process.
Comments closedKevin Chant demystifies GitHub Actions:
In this post I want to share how to deploy from one source to multiple SQL Server database types using GitHub Actions. Because I did a demo of it at Data Saturday Redmond last weekend.
By the end of this post, you will know more about how to do this using GitHub Actions. If you are used to Azure DevOps, you will find this an interesting comparison.
Previously I did a post about how you can do this using Azure DevOps. You can read that post in detail here. Later in this post I also mention an older post here a couple of times so it’s worth keeping that open.
Read on to learn how.
Comments closedElie Bou Issa does some no-code automation:
Azure Logic Apps is a cloud service to help you schedule, automate, and orchestrate tasks and workflows between apps and across enterprises and organizations. A Logic App can be built using the Azure portal, or infrastructure as code.
By the end of this article, you will have a good understanding of leveraging a Logic App for Azure DevOps to automate the create of work items, in addition to creating an automated approval-based workflow using Office 365.
Click through for the demo. This is useful on its own, especially with non-technical product managers, but you can extend the use of Logic Apps quite a bit and automate more work without writing much code.
Comments closedJohn Martin has an interesting use case for tagging in Terraform:
One of the key properties missing from Azure resources, in my opinion anyway, is a CreatedDate. This can be largely overcomes with Azure policy, but what if you don’t have access to create one that applies a timestamp tag at resource creation?
It is possible to use Terraform to tag the resource and set the value for when the resource is created. There is a little more work that needs to go into it to ensure that once it is set that Terraform does not overwrite it on subsequent deployments. But, it is achievable and brings this into your control if needed.
Click through to see how.
Comments closedKevin Chant wants to deploy to all of the SQL Servers:
To give the above pipeline a bit more context the below types of SQL Server databases were updated after being unit tested (initial unit testing yaml courtesy of Sander):
– Three SQL Server 2019 instances in three Docker containers. Representing Integration, Staging and Production environments.
– At the same time the Git repository in Azure Repos would sync with a GitHub Repo. Which would then start a GitHub Action to update another database.
– An Azure SQL Database.
– Finally, a Synapse Analytics SQL Pool was also updated.
Read on to learn how.
Comments closedGrant Fritchey asks a question:
Building out processes and mechanisms for automated code deployments and testing can be quite a lot of work and isn’t easy. Now, try the same thing with data, and the challenges just shot through the roof. Anything from the simple fact that you must maintain the persistence of the data to data size to up time, and you have real problems in front of you.
However, adopting database deployment automation and testing has enormous benefits. Faster, safer, production deployment enhances the protection built around your production systems. Whether we want to use the loaded term of DevOps or not, the benefits of this style of development and deployment are easily documented and measured.
So, why are so few people doing it?
Grant gives some of the outline and lays out one response. I am seeing a lot more automation over time, but one underappreciated facet in this is a lack of trust for automated processes from humans. I think a good percentage of DBAs don’t trust that the automated process will get things correct, especially when dealing with complex chains of dependencies. An automated process may be less likely to make a mistake in a step, but it will also be unable to reason through an ambiguity and could perform an undesirable action in the event of unexpected circumstances. That’s a pretty big risk for DBAs who are concerned about their data. I can see a few other reasons as well, but this is one which I don’t hear often enough in these discussions.
Also, Grant asks people to fill out the State of Database DevOps survey, especially those people who are not automating database deployments.
1 Comment