So I’ve been rolling out the deployment pipelines as I was able and I ran into an issue with our third one: We have a helper database that lives on all of SQL Instances and while we’d like to eventually keep any customization in source control we only want the dbo schema deployed automatically via the pipeline. The Redgate “Deploy From Package” step has a handy option to include an alternative filter to use with the SQL Compare engine so it seemed really straight forward: I fired up SQL Compare: generated a unique filter file that I named DeployFilter.
All right, previously I configured Team City, created templates in the DBA Project directory that will be used by any child projects and then set up Octopus Deploy. Today I’ll talk a little about deploying an actual project in team city. Navigating to the page for the DBA Projects folder I can find the list of subprojects. This includes the handy “Create subproject” button. The project can be built out manually but as I have added my Github information I can also simply browse my organizations repositories to immediate link the TeamCity project to the appropriate source code.
Where Team City will test and validate your code, Octopus Deploy will manage where that code is deployed. Again there are SQL Change Automation tools we’ll be using to ensure our database code is deployed consistently across our environments. You’ll need to ensure you have a service account (or MSA/gMSA eventually) for the application as well as home for the SQL database it needs. Download the install media and deploy it to your server.
The first piece of the database DevOps puzzle is automating the build. Team City is a build server that developers use to compile and test their code. It consists of a central server and a number of agents. Agents are secondary machines that will pull code from source control, run tests, compile binaries and assemble deployment packages. Depending on your environment you may only need one machine. Our deployments are currently infrequent enough (mostly because they not needed often but also because they are manual so we avoid it unless absolutely necessary) that we only need on agent and it runs on the same machine as the central server.
Ever since my first disaster of a manual deployment as a DBA I’ve been on the DevOps train. Maybe I didn’t know exactly what it was called but certainly this is a solved problem I said to myself? How do other organizations find the changes that need to be deployed from their dev/test environment into production? How can I ensure that we have a process that has the best chance of succeeding when we go to deploy?
This is just a fancy way of saying you need to be better friends with who ever is managing your enterprise firewall. I hadn’t had to touch the DTC until a recent vendor insisted their application wouldn’t work without it (despite their only having a single data store). The MSDTC was developed to coordinate transactions that would span multiple machines and was originally introduced in SQL Server 2000.
In theory it’s not super complicated: just enable the DTC service/communication on the servers in question and turn on some built in firewall rules on the servers right?
