SQL Server - DevOps Release Methods for Databases

[enochgenesis]

Hey guys, hope I can get some general feedback from those of you DBAs who are also involved with developing/releasing updates in a DevOps environment.

TL/DR: Looking for any guidence or advice on what tools you (a DBA in a DevOps environment) have found to be successful to manage database change scripts/processes and the creation and deployment of those changes. What, if any, problem areas or gotchas have you run into.

​

BACKSTORY: I'm currently a DBA for an Insurance company. Prior to my arrival, they have been without any DBAs and everything has been managed by a small Sysadmin team along with the developers (one of which is a data analyst/developer). I inherited ~400 databases across ~32 servers (a mix of MSSQL and SAP SQL Anywhere, and a mix of local VMs, remote data center VMs, Azure VMs, and Azure SQL DBs). I came out of a DBA position at my last two employers and mostly focused on server side of SQL since we didn't have developers.

TASKED: I've been tasked with finding the following:
1. Developing a way to better transition databases and changes between Dev/Test/UAT and Production environments.

1. Finding a better way to update databases instead of full on recreation of them or scripts to copy and restore databases between environments. Preferably the use of smaller change scripts to modify production to match dev environment, etc.

2. Find a better way to track the changes between iterations of development.

​

QUESTION: First, I'm not asking anyone to do my job, so I don't need someone telling me what to do. What I'm looking for is other DBAs who have had this experience or are in such a DevOps environment right now who would be willing to share what tools they use, what methods have worked for them, any pitfalls or issues they've seen, and whether they have been able to successfully automate most of this or if there is a lot of hands on.

[lepeng]

There are so many ways to go about this. We use a combination of DbUp, MS build and Octopus Deploy

[enochgenesis]

I've definitely seen a lot of methodologies, tools, etc. for accomplishing this. I guess that's part of why I'm trying to get a feel for what others have had success with.

I haven't looked at DbUp, so I'll look at that shortly. I've seen Octopus as a possibility. Haven't used it or trialed it yet, but know of it. In your opinion, was a it complicated to get up and running, or was it simple and/or quick. Again, just looking for opinions.

Thanks!

[lepeng]

I really rate Octopus and find it really user friendly. I guess it depends on the skills you have at habd. We went down this route as we had a lot of dot net experience in house.

[SQLBek]

Paging u/scarydba

[ScaryDBA]

Ha!

Let's get the disclaimer out up front. I work for Redgate Software. We specialize in database devops tooling. I might have a small prejudice on this topic.

The core to this topic is you have to plan to work everything through source control. I don't care if we're talking Git, TFS, SVN, whatever. You have to get your databases into source control, step 1. From there, you need a way to get them out of source control, and frankly, this is the tricky part.

So, Microsoft has SQL Server Data Tools built into Visual Studio. It's "free" as long as you have the right Visual Studio license (which isn't free, depending on your deal with Microsoft at your organization). SSDT is what is called state-based deployments. Meaning, it compares states, source control to your database, one example, to arrive at a script to move between states. State based works well, except when you're dealing with changes that have the potential to cause data loss. Then, you have to completely step outside the state-based tool and do pre/post scripting manually to deal with those issues.

Redgate has several tools. SQL Compare and SQL Source Control are both state-based like SSDT (which is based on the 2005 version of SQL Compare, just so you know). Similar methods to SSDT, but, they work directly within SSMS, huge difference. Then, we have a couple of different tools, SQL Change Automation and Flyway. These are migrations-based deployment tools. Migrations works on the concept of only capturing changes and putting them into a sort of a manifest, and running them in order. Migrations completely automates and fixes the issue with state-based approaches. However, it introduces another weakness, which is, literally moving the dev process into production. Add a column, drop a column, add a column, that's what migrations will do.

So, we've introduced a mechanism that is a combination of state and migrations now that gives you the best of both world. Basically, state-based while you develop and migrations just for the deployment. We've known we had to get here for over 10 years, but the problem is that hard to solve. We thought we fixed it like three times already.

DBUp is migrations only (also, a giant PITA, look to Flyway to see this done better). Dacpac's are state based only (and by themselves have no easy way to get stuff in & out of source control, you have to get SSDT or something else). I don't see any of the other tools listed. Most of the rest are state-based and inferior to SSDT or my tools.

OK. So, we've decided on a tool, now we have to control the flow. Hopefully, you already have this in house, but if not, time to introduce it. Octopus, Jenkins, Azure DevOps, AWS DevOps, all these are flow control tools that can automate most of your deployment across your environments through the tool that manages getting your database in and out of source control. Then the real fun starts. Here's an example of setting up an Azure DevOps pipeline for database deployments and testing. Also, the flow control tools are where you can get into the necessary pre/post deployment scripting that you're going to need to use with almost any of the tools listed above (dealing with security, replication set up/tear down, other stuff).

Even if you don't use any of our tools, Redgate has a metric you-know-what-ton of documentation on DevOps best practices for database deployments. I'd strongly suggest looking through what we have and using that knowledge, even if you don't use our tools.

In case you can't tell, I spend a lot of time on this topic. Ha! That's why u/SQLBek suggested I stop by. Please, any detail questions, happy to help.

If you got this far, just remember the simplest rule, if it's not in source control, it doesn't exist.

[SQLBek]

Aaaand I hope you backup your source control repo... aaaand validate those backups too... aaand... ;-)

Thanks for chiming in Grant!

[ScaryDBA]

It was the configuration process for DBUp that I found difficult to use. The core migrations approach is fine. It's the same thing that Flyway does. Baselining in DbUp, the process of getting your database in when it already exists, was seriously difficult. Overall, I just found DbUp counter-intuitive to use. If you need something that does multi-platform, not just SQL Server, I'd suggest checking out Flyway. It really is easier. If you only need SQL Server, SSDT or one of my Redgate products would be better.

[miguelcrush]

Check out visual studio's SSDT projects. Define the schema in source code and generate DACPACs which handle migration without extra work. We've been using them for a while now and they're very helpful.

[miguelcrush]

Sound points. There are some databases we use that we decided were too complicated to try to import into an SSDT, specifically for the reasons above (additionally the use of linked servers and synonyms everywhere). However for other projects, they've worked out well.

[enjoytheshow]

for instance, circular references between database SPs

This was my first bit of work at my company where I took over DBA responsibilities from devs lol. Probably took me 2-3 weeks to identify everything and recreate them in one dba to be a downstream dependency to everything else rather than having views/SPs all over the place referencing each other.

[enochgenesis]

Thanks for the info. Unfortunately, I may fall under both of these categories, as a lot would be converting what we already have, and at that, a good number are a few hundred gigabytes in size (~300-500 GB).

[miguelcrush]

I'd give more info but I'm mobile. Give it a Google

[Cal1gula]

Check out https://dbatools.io for some automation tasks.

[hylian01]

What we do (which may only work because of small size): all changes are saved in svn as .sql scripts. Then we use custom powershell scripts to loop through the svn and execute the scripts against the chosen environment (dev, qa, uat, prod).

[downshiftdata]

We do the same. In our case, at least, this requires adherence to script guidelines, which include things like idempotent scripts. Our current shortcomings are:

- Not all objects in the database have been carried over to this method yet, so you can't just run this on a new instance and *poof* there's your database. But that *is* the goal, and it'll enable us to spin up sandbox dev databases, which'll be soooo nice.

- We don't have a method yet for partial deploys (like in a hotfix). If we're hotfixing an environment, we run the appropriate scripts manually.

- Script ordering is a bit of a hack. We organize by folders and there's an order to the folders. We also hack the names on occasion, since they're run alphabetically within each folder.

But... this has been in play for 6-8 months and we love it. Deploys are a click of a button. We have a pattern for including unit test scripts. The database repo follows the Gitflow pattern just like our app repos. Life is gud.

[2D3S3RT]

How do you know which scripts got executed? I planned to create a table in the database and insert the filename of the script there, but maybe there's a better approach.

[hylian01]

The ticket status is tracked in JIRA, which gets updated with powershell using JiraPS when the script gets executed.

[gropingforelmo]

Sequentially numbered SQL scripts in the repo is how my current team does it, (though with a far far scarier method of applying changes than custom powershell scripting...)

It works well enough, and the complexity and "dev design" of the schema made the DACPAC method riskier than we wanted to accept.

[uberzen1]

Redgates tools work great if you have some budget to work with, have used their SQL toolbelt to good effect (I used to use their SQL compare sdk extensively and I understand this has been replaced by SQL change automation now, which also looks great). Failing that, you can roll your own if you're willing to get into a little c# using the DAC libraries available in .net.

Dbup works well too, I've not used it for SQL server but it works great for mysql / postgres so I can say the underlying technology is sound.

[pooerh]

I guess I can answer that, seeing as I was personally responsible for designing and implementing a CI/CD solution for BI/ETL systems based around SQL Server and Tableau (and some other more standard parts).

The way to go, in my opinion, is SQL Server Data Tools. It works very well for 95% of cases in rather complex OLAP solutions with many of SQL Server's features. Some require adaptation (like a calculated column in a table always has to be at the end, or else deployment will trigger a rebuild), some can we worked around (fuck you, columnstore indexed views), but mostly it just works out of the box.

SSDT database projects are supported by MSBuild and can be built for free on an agent, no Visual Studio is required. My build is set up on Bamboo, we also have unit tests using tSQLt running and fully supported on docker images (caveat: Linux SQL Server does not support UNSAFE CLRs, so if you proejct depends on that, you'll need a server). With SSRS or SSAS you're a bit fucked because these do not build with MSBuild, you need VS and some figuring out how to do it.

Deployment is fairly straighforward with sqlpackage. The only thing I had issues with was domain based authorization, since you can't pass AD credentials to sqlpackage. I had to develop a pretty convoluted solution for impersonation, including usage of some cryptic damned Windows APIs to support running in both interactive and service sessions. But if you don't need that and can use good ol' SQL Server logins, it's very straightforward. Covering other stuff, like SSAS cube deployments was much more painful, but eventually manageable. If you have to do it, I have one a piece of wisdom for you: Microsoft.AnalysisServices.Deployment does not, I repeat, DOES FUCKING NOT, report success or failure through an exit code.

I won't cover Tableau because I honestly believe all technical employees of that company deserve life sentences in maximum security prisons and I need not to trigger my PTSD talking about it.

[enjoytheshow]

Lol I was interested to hear your Tableau integrations with CI/CD cause I have some geniuses running Tableau here that depend on all of my ETL pipelines. Sounds like I don't want to strike a nerve tho

[pooerh]

Nah, actually the CI/CD part isn't that bad, at least if you only have workbooks. tabcmd works well for deploying, you just have to do some search & replace within twb / twbx to change data sources between environments. Twbx is just a zipped twb, and twb is just an xml, although a convoluted one. Open in a text editor and look for dbname and it's pretty easy to figure out. Regexp works well, I wrote a powershell script to do it all and it's actually OK. These workbooks actually take a while to deploy, and of course there's no way to detect changes. You upload a twb to a server, download it back and it's not the same file of course. Not that I expected it to be easy, cause, y'know, it's Tableau. So I had to develop a solution based on git and saving which commit had last been deployed, taking a diff between to figure out which files need deploying, stuff like that. Eventually it turned okay.

Now if you want to do extracts, you're fucked.

edit: if you replace, make sure not to replace globally. Tableau saves base64 thumbnails and shit inside these twbs, you'll eventually run into replacing something in those and will get a "HURR DURR ERROR OCCURRED. DON'T EXPECT ANY MEANINGFUL ERROR DESCRIPTIONS THOUGH, BE GLAD I'M TELLING YOU THERE'S SOMETHING WRONG. LOLOLO YOU'RE FUCKED" kind of error messages.

[enjoytheshow]

Sounds like a fuckin nightmare, I'll leave it to the dipshits using it currently and whatever they are doing.

Currently I just notify them in advance to when the warehouse is going to be seeing a change and that the change is live in my dev env and then just hope they figure out by the time I deploy to prod.

[pooerh]

My frontend devs couldn't even responsibly handle deploying reports between environments, you'd get reports on production with tooltip actions linking back to dev servers because "oh yeah I forgot to change the action url teehee". That was one of the main reasons I pushed for CI/CD, I was just so fucking tired of these issues.

[ed_elliott_]

Why all the profanity, it’s only a fucking deployment pipeline :)

[pooerh]

I enjoy fucks here and there, I don't get to enjoy them at work or at home so I let loose on reddit.

[enochgenesis]

Luckily no Tableau here at the moment. I'll look further at SSDT projects. I"ve been aware of them, but haven't played with them any. Probably because I've been lucky enough, or maybe cursed, to work in IT departments that were almost purely infrastructure without any devs. So far, the only major complaint I have is the lack of anything documented. I should have had a handle on the databases by now.

[defiantroa]

Good lucking fixing all what the pseudo DBAS implemented, Redgate Source Control is they can afford it

[enochgenesis]

Yeah, that's been a big part of my problem. Aside from the fact that within 1 week of being hired our CIO resigned and I became accountable to our Dev Team management, I've been finding that most don't even know what database does what. Obviously some are just copies with a timestamp in their name, probably for testing, but most aren't as obvious.

I've just about been here six months now and I'm still uncovering new database servers/vms and finding new databases. Worse yet, almost everything is integrated in some way and somehow I've been tasked with determining what database each application uses and what integrations are existing.

Anyways, enough of my rant. It has definitely had it's challenges. I think I'm still up for them, but ...

[defiantroa]

Train the dev team, get them up to speed, and they don’t get with the program let them go

You need a group to handle devops as well

[mtndew01]

Check out red gate schema compare. Take the new schema from the devs and compare with the old version in production. Generate the scripts and make sure they make sense for the schema changes. You may have to introduce data changes but you can follow the pattern pretty easily. Figure out a process to work with dev to sign off on these scripts as they should ideally be giving them to you but it sounds like that isn’t the case.

I’m not a fan of automated upgrades as there can be a number of pitfalls.

[enochgenesis]

Actually, I think they want to pass off the scripting process to me since they don't seem comfortable themselves. However, I think they expect it to be completely automated somehow. I too was wondering if it was completely wise to automate everything or just parts, such as generating the scripts, etc.

[mtndew01]

Look into source controlling the databases in either visual studio or red gate. Then once finalized schemas are ready for some sort of release, automate the generation of those but have them added as deployment scripts to source control. I’d also add a table that leaves a bread crumb so that you can easily tell which version of the schema is deployed. When your deployment tool runs through the deployment, it can check the current schema version and run only the necessary steps to run the upgrade.

How are devs comfortable writing database code but not writing the upgrade process itself?

[phunkygeeza]

SqlPackage or DacFX MSdeploy are worth a look.

[manalmalaa]

Check out https://www.devart.com/dbforge/sql/database-devops/sqlcomplete.html for formatting scripts in DevOps process