Q&A
The Biggest Hurdle to Automating Database Deployment is Getting Started
Automation can make IT's job a lot more simple when it comes to database management. All is needed is to take that first step.
Ensuring smooth and reliable database deployments is critical, yet often filled with challenges. Erin Dempster, IT Data Operations Team Lead at Trean Corporation and a Microsoft MVP for Data Platform, offers a wealth of experience from nearly two decades working with SQL Server.
As she prepares to lead her session, titled "Introduction to Automated Database Deployments with Azure DevOps," at this year's Live! 360 Tech Con conference (taking place in Orlando, Fla. from Nov. 17-22), Dempster shares her insights into the common hurdles IT professionals face and the evolving tools that are making these processes more streamlined.
In this Q&A, Dempster delves into the nuances of database deployment, discusses the concerns that often hold back adoption of automated solutions and highlights the best practices that can ensure successful integration with modern development workflows. Whether you’re new to automated deployments or looking to refine your existing processes, her insights offer valuable takeaways for navigating this critical aspect of IT operations.
And for more of her insights, join us in November to hear her thoughts in person for Live! 360 Tech Con 2024. Save $400 when you register by the Super Early Bird deadline of Sept. 27!
Redmond: What are the biggest, most common challenges that IT pros face during database deployments? Given how much technology has evolved in the last few years, have these challenges gotten worse, or are they easier to overcome now?
Dempster: I can talk to the issues I've faced over the years, and I suspect others can relate.
Missing changes -- whether a collection of scripts is kept in a shared folder or a schema comparison tool, like Red Gate SQL Compare, is used, it's really easy to miss a few changes. This leaves the team scrambling to find those changes and deploy them to production to get the application back online.
Deploying changes to multiple databases, especially in a data warehouse environment, it's common to have changes in multiple databases to deploy at the same time. This just compounds the missing changes issue.
Vendors, such as Microsoft and Red Gate have long offered tools to manage code changes, so building a deployment script is easier. However, the automation piece took a longer to come together. With Azure Pipelines (or GitHub Actions), now there's a tool that can utilize those code projects that have languished over the years or were underutilized.
Automation for database deployments sounds great! What's keeping more IT pros from adopting it? What are the biggest concerns that you've heard? Are there best practices that would help guide them?
As a database developer and DBA myself who's discovered automated deployments over the past few years, the single largest hurdle I had was not knowing how to get started. Which tools are needed to complete the process? What is this YAML stuff and how do I work with it? I think this is probably an issue a lot of IT pros have. This session spends very little time making schema changes in the database, so we can focus on the setup of a Visual Studio project, checking it into source control (Git) and finally creating a small CI/CD pipeline in Azure Pipelines.
Popular concerns I've heard include:
- How do I know the deployment will do exactly what I expect it to do?
- How does this process handle data updates?
My upcoming session will briefly touch on approaches using Visual Studio SQL Server Database Projects, but that may not be the best tool to use. Red Gate Flyway takes a different approach than Visual Studio to deploy changes that can be more granular and exacting. In a lot of companies, Flyway is probably preferred over Visual Studio, even with the cost differences.
How do you ensure database deployment processes are aligned with CI/CD pipelines?
Database deployment processes can be built into the CI/CD pipelines. This session will walk through the process of creating a CI/CD pipeline in Azure Pipelines to deploy schema changes. Popular vendors either have a task for Azure Pipeline to aid in the deployment, or they provide a command-line tool that is easy to call from the pipeline.
How do you manage database deployments in a cloud environment, and what unique challenges do they present?
With cloud environments being an extension of our corporate network, deployments are pretty seamless. Self-hosted pipeline agents should be deployed on the internal network for Azure Pipelines to communicate with. Once that's done, if you have SQL Server on an on-premises VM for dev and QA but in an AWS EC2 instance for UAT and production, it's pointing the deployment tasks to the correct servers. With the cloud, it is important that the database servers or instances use the same technology for dev, test UAT and production. Don't try to use Azure SQL DB for dev and test and then SQL Server 2022 in a VM for production, as there will be unexpected issues, because they are close but not the same product.
Can you discuss any recent advancements in database deployment methodologies that attendees should be aware of?
Check out this session, along with "10 Visual Studio Hidden Gems for Database Development," to see what Microsoft has been doing lately for database deployment solutions.