13 ms·

SQL Is No Excuse to Avoid DevOps

263 points by YesThatTom2 8y ago

vaultcool 8y ago
Actually, running integration tests against SQL RDBMS works really well. It might be a tad slow but the CI can easily handle that. We have Docker and all of that for this, and it works x-platform.
- nicoburns 8y ago
  Running the RBDMS with `eatmydata` (https://www.flamingspork.com/projects/libeatmydata/ https://www.flamingspork.com/projects/libeatmydata/) can significantly speed up tests in my experience. All of you're data is gone as soon as the process exits, but that doesn't matter for tests.
- taeric 8y ago
  Docker isn't really required for this. Any in memory db will provide good contract verifications. And a simple alpha/beta/gamma setup with the end database can get you the rest of the way there.
  - tracker1 8y ago
    Docker can be a bit of a better option... Unless you're using the same database software for the in-memory operations as the actual deployment. Or aren't using many specific features the rdbms you have chosen (stored procedures, triggers, virtual fields, etc). Not that I'm a fan of triggers and prefer direct table access over SP, but virtual fields are GREAT during transitional changes. It also generally requires an abstraction between the DB variants.
    - taeric 8y ago
      Agreed. In the old world, this is what an alpha/beta/gamma environment could help test against. The in memory could be used to make sure your units and overall components were correctly specified. The actual installs of the DB software to isolated schemas were where you made sure you integrated with the DB correctly. That all said, I did not mean this to be that Docker doesn't provide some value. Just pointing out it is not a prerequisite. If that makes sense.
- rodeoclown 8y ago
  You can even do SQL DDL ALTER TABLEs in a Transaction on production, do the change, run smoke tests in a nested transaction that are all rolled back when complete, and if anything fails you roll back the DDL changes and are back where you started clean.
  - tzs 8y ago
    Support for DDL in transactions seems to vary quit a bit between DB servers. In mysql (and I think also mariadb) ALTER TABLE cannot be rolled back, and also implicitly does a COMMIT on any in-progress transaction before altering the table. In PostgreSQL you generally can do ALTER TABLE in transactions, but there are dome restrictions or exceptions. If the alteration is to add a value to an enum, it cannot be done in a transaction. MS SQL Server seems to be OK with ALTER TABLE in the midst of a transaction, according to this article [1]. Oracle seems to be similar to mysql. Even the ones that support this well, like MS SQL Server, have restrictions on other DDL, such as creating indexes. I think I'd rather just assume no DDL in transactions and design my schema update procedure accordingly, rather than asking whoever is designing the new schema to try to limit themselves to changes from the old that avoid whatever statements whatever DB server we are using doesn't allow. [1] https://www.mssqltips.com/sqlservertip/4591/ddl-commands-in-transactions-in-sql-server-versus-oracle/ https://www.mssqltips.com/sqlservertip/4591/ddl-commands-in-...
CaptainZapp 8y ago
In the old methodology, any schema change requires the entire application to be shut down while a team of experts (or one very overworked DBA) modifies the schema manually. WHAT ? I'm working pretty much in an environment described in the article. Big bank, complicated database setup with multiple replicates and databases up to a size of ~25TB. On a (pre defined) release weekend there are essentially two entities to be released: The application (obviously) and the database changes. For any DB change a patch is created and tested through an environment of half a dozen instances. If bugs are found on already deployed db patches they are never fixed in the patch, but a subsequent patch (also tested through all environments) is created. Needles to say that anything provided, which applies database changes is version controlled. The idea to apply database patches manually is so damn outlandish to beggar believe. Anybody doing this on a production database without a strictly controlled (and tested!) break-fix should be fired on the spot.
- edoceo 8y ago
  Maybe training rather than termination?
  - C1sc0cat 8y ago
    They should have never been put in that position in the first place.
  - CaptainZapp 8y ago
    I'd consider this such a gross case of malpractice on the level of a surgeon forgetting some instruments in patient, before sewing her shut. Errors and mistakes happen, no question about that. And any ol' mistake certainly shouldn't be a firing offense. But this behaviour is so grossly incompetent that I don't believe that it can't be helped with any training.
    - geofft 8y ago
      Surgeons are generally trained with a grueling multi-year academic and practical program about how to conduct surgery. DBAs aren't. If you're lucky you have someone who has been trained to be a DBA professionally - but I've never worked with one, all the SQL I've interacted with has been run by people with unrelated job titles who learned how to operate databases by Googling when necessary. You're welcome to fire people if you think it's going to help. I'm just curious how you think a skilled, trained DBA is going to show up at your desk when that happens.
C1sc0cat 8y ago
"rather than using SELECT . If you do use SELECT , don't assume that the fields are in a particular order." There are professional developers that use Select * EVER! Maybe add write a simple Select, Insert and Update Statement to the FizBuzz test.
- mschuster91 8y ago
  > There are professional developers that use Select * EVER! Old code from the 2000 PHP era is full of this. Never underestimate the horrors of "legacy" code bases, and never underestimate what people do in short lived "maintenance scripts" or "helpers" that are used well beyond the intended life span. Or "proofs of concept" aka "unbelievably dirty hacks to get a MVP" that end up being extended and extended to being the "real product" in the end. For an MVP I will always use "select *" and friends simply for speed of development, but clearly communicate that this is in no way intended for production usage.
  - lowercased 8y ago
    > clearly communicate that this is in no way intended for production usage. But it likely will be anyway. I use a variety of ORMs and hand-rolled SQL, and often will do select . The times when I know there may be a performance impact, or I'm debugging an existing performance impact, hand-rolled SQL always is on the table, but often* the issue is either missing/poor indexes or simply table design that didn't foresee the current use case. There's been a couple of times where a stray select * was, really, pulling back unintended very large blobs and causing a performance issue, but it's been infrequent compared to index issues.
- porpoisely 8y ago
  You should never use "SELECT * FROM SomeTable" to retrieve data because the column order isn't guaranteed. Also, you should always use column names to access data. However, "SELECT * FROM SomeTable" can be used to determine if a table had data ( for "EXISTS" clause ) or to "COUNT" the rows of the table. It's industry standard. You could also use something like "SELECT 1 FROM SomeTable" but it isn't natural.
ed_elliott_asc 8y ago
Microsoft isn’t given enough credit for what they have done investing in ssdt it really is a game changer for sql developers
- mdpopescu 8y ago
  Yep; discovering - and using - SSDT to replace a 3-day manual migration with a half-hour automated one is still one of my "most proud moment" stories.
alexandercrohde 8y ago
No idea how this made front-page. Sounds like the author met one crazy person and wrote an article about it. Totally irrelevant to me, I've never seen a company like this in practice in my long career.
- mschuster91 8y ago
  > Totally irrelevant to me, I've never seen a company like this in practice in my long career. Never worked in government, I assume...
  - stareatgoats 8y ago
    Two private sector industry leader companies that I worked for had never heard of a staging environment before I joined, and the process to implement one was long and painful. The argument against it was: we wouldn't need a staging environment if we had competent developers ... Niche industry, so still not very big corporations, probably explains it.
- GauntletWizard 8y ago
  I've seen paragons of "Devops" with precisely this practice in significant chunks of the company. You've gotten lucky - or possibly just buried your head in the sand.
  - 0xFACEFEED 8y ago
    Probably less to do with luck/head-in-sand and more to do with filtering out potential employers.
- breakingcups 8y ago
  I've seen plenty of companies function pretty much exactly as described. Just because it is irrelevant to you does not mean it's not interesting to others and thus front-page worthy.
- choward 8y ago
  > Sounds like the author met one crazy person He either met a straw man or a troll.
gregw2 8y ago
For scripting database changes so they are deployed with app changes... or so they are automated and auto-tested upon deployment and in version control, I've had good success with open source Liquibase. Worth looking at Flyway also.
- clemson20 8y ago
  For those using Microsoft SQL Server, we've had success using SQL Server Data Tools (SSDT), to automate database deployments for a few years now. Prior to this, developers would check-in 'delta scripts' - which were just all the sql changes that were manually applied. Now, the entire schema for multiple products is stored in source control as the source of truth, and deployments themselves generate and apply the deltas.
giobox 8y ago
The premise of this article is just too bizarre for me - even the most inexperienced software engineer today surely can see the fallacy of claiming a SQL database prevents use of DevOps style practices. Either the author A. Met a crazy person, or B. Invented the anecdote for sake of what they beilieved to be a snappy title.
- onemoresoop 8y ago
  Absolutely B to sell the article
- ColinDabritz 8y ago
  There are a lot of crazy people in industry, unfortunately. Not as much at the big flashy tech startups or places you hear about in the news, but there are still plenty of 'work a day' businesses in corners of industry that do not have expertise in these areas that absolutely should. I helped my current employer make this transition and it was similar to the description in the article. Controlling database change in source was foreign to them.
- hobs 8y ago
  context: I work as a DBA. It is a bizarre premise, and its alive and well at an insane amount of shops today. When I walked in the door at my last gig(and a few others), they were hand running all deployments, and often manually troubleshooting in production when it fucked up because they didn't have a QA environment.
  - ianamartin 8y ago
    Everyone has a QA environment. Some people have separate production environments.
    - dvtrn 8y ago
      You and I may be defining "everyone" differently.
      - 8y ago
andmarios 8y ago
Sometimes, devops, admins, DBAs, etc are there for QA. It's not that they don't know how to automate everything, it's that they know if you permit someone to do a dumb thing, they'll probably do. It's one thing to talk about good practices and another to build a large development team that can understand and follow them. By moving the responsibility to devs, you tax your senior devs, who have to monitor middle and junior devs more closely and sometimes micromanage them as a consequence. A few schema changes a year are more easy to test, justify and —most importantly— track than many small ones going on at the same time. How do you even manage the schema version when there are multiple branches that require changes and you don't know the order they will be merged in? Instead of a version number, you start to use flags. How about deprecated features? How and when do you clean up? Something else that might be important, are performance optimizations. I agree on the suggestions of the article and it's the way I try to write code that uses a SQL store, just saying that sometimes in real life you have to make things harder in order to stay safe.
exabrial 8y ago
Great example of blaming the tool [SQL] to try and bring in hip technology when the people are to actually blame. We use Liquibase, which is event-sourcing for database schema changes, and a database that supports online schema changes.
djohnston 8y ago
what?
deleted 8y ago
[deleted]
LaserToy 8y ago
I always thought that DevOps are developers who can do ops. But in recent years I see more and more people calling themselves DevOps, even all they do is ops with simple scripting. I think we need a new name for developers who run their code in prod, as DevOps is diluted. So, I’m this light name of the article is utter BS. As devs we deal with a lot of different databases, be is SQL, NO SQL or graph and dealing with them is part of our job. If all you do is run stuff and release - you don’t have Dec component, so you just Ops.
- Thaxll 8y ago
  Dev that do Ops is usually a terrible idea.
  - busterarm 8y ago
    It depends on the person and mindset. I switched from dev to ops fulltime (as in not "DevOps") and I'm perfectly at home here. If anything, I'm the one bridging the gap between us and the development teams. My team thinks I'm an ops person who does development, but I don't really find that to be true.
  - bovermyer 8y ago
    Why do you say that?
    - SteveNuts 8y ago
      I've worked at a couple places that had developers build their own environments before I got there (I'm more of a systems engineer). There was no thought to high availability, security, scalability, reliable backups, etc. I'm not saying all devs are like this, but that's just been my experience.
      - Thaxll 8y ago
        This. Honestly Ops is a different mindset that devs, the tldr version is devs don't really know / care about running things in production. I don't blame them it's just different set of skills.
ianamartin 8y ago
Everything about this article is really weirdly written. It's glib in the way that preachers are glib from the pulpit and trying to get you to just believe something while glossing over your particular concerns. Is "doing DevOps" impossible? "No! It's simply a difficult transition." Oh, I see now. That makes it all better. Never mind that the author's "friend" never claimed it was impossible. Rather that it was fundamentally a bad decision. It's like a parable, but a really bad one. I wonder when the author is going to bring up Agile and just assume we're all true believers and that anything that incorporates Agile must be good without question. Oh, there it is. Right there. So the difficult transition of DevOps is presented here as necessary and appropriate because 1. Agile, 2. Faster, and 3. Because it avoids some incredibly horrible practices the friend's DBA team does. 1. I won't get into here. That's a different flame-war for a different time. But people should know better than to write articles with the unquestioning premise that Agile automatically justifies any and every difficult transition. 2. I'm highly allergic to justifications that involve moving faster. Most companies do not have a problem delivering value fast enough. They have a problem delivering any value at all, and delivering a reliable product at any pace. Faster time to value is the worst possible justification if you're trying to convince me to experience some pain. I am a big proponent of DevOps, and I bake it in to all my work, both at my work-work and in my side projects. It's worth the difficulty to me because it allows me to reliably get my code from point a to point b. That it's perhaps faster (is it really faster if you add up the person-hours? I don't know. I don't really care) is a little extra niceness if it's there at all. 3. DevOps has basically nothing to do with the pain that's causing the situation described in this parable. DevOps won't fix bad DBAs or sloppy Devs. DevOps won't fix a manager that allows this kind of situation to happen in the first place. DevOps won't fix a company culture that allows Management to run this kind of a dev shop. It's like the "friend" here is dying of dehydration in the desert and this dude rolls up in his car and says, "Hey man, you really need to eat more vegetables and less red meat and exercise at least 30 minutes a day. It will totally change your life! Also, I have these essential oils that will cleans toxins from your spirit aura and help you focus on your life goals. Good luck!"
pjungwir 8y ago
I really enjoyed Thomas Limoncelli's Time Management for System Administrators. I've also got The Practice of System and Network Administration on my shelf, and hopefully one of these years I'll read it---maybe once my time management is better. :-) I like that Tom was practicing and advocating system-administration-by-writing-code way before DevOps, and to me he represents a tradition of automation that far predates the buzzwords. I've lived in the world of Rails and Django so long, I sort of take schema migrations for granted. It's interesting to see such a high-level person covering this ground; it reminds me of how much these frameworks have given us, and how so many organizations are still practically emailing around zip files of source code. His Technique 2 feels harder to me: write the code to work on both old & new schemas, and deploy the schema change after the code change. I know this is what Google does though, and I think it's the only safe way to deploy rollback-able code changes. I'm curious who else is doing it, and especially how you are scheduling it. Especially with a CI/CD workflow, I'm used to getting things integrated and letting them get deployed "whenever". So how do you make sure the schema changes go out "later", and what gating is required first, and how do you get buy-in to spend time on that after the feature is already live? I'm not asking about the technical how-to, but the organizational/political how-to. For instance if you're using Pivotal Tracker, do you make a separate PT story? Leave the old one open until the schema changes are deployed? What does it concretely look like?
- djrobstep 8y ago
  > I've lived in the world of Rails and Django so long, I sort of take schema migrations for granted. And there's lots more room for improvement over Rails and Django migrations. Some of the problems: - Annoying version number management - Management of long "chain" of migration files - Unnecessary coupling between ORM and migration tool - Heavyweight process to generate and run each migration, which slows down local development - Bad testability Autosyncing local dev databases, and automatically generating migration scripts with a diff tool is a better approach, imho.
  - evanelias 8y ago
    Totally agree -- I wrote some similar thoughts elsewhere in the thread. It might just take some time for more developers to gain experience with the declarative/diff-based approach, to become comfortable with it.
evanelias 8y ago
Ignoring the rather bonkers anecdote at the start of the article, I'd also argue against one of the other recommendations here, even though it is quite common in practice: > The command in array index n upgrades the schema from version n-1 to n. > Thus, no matter which version is found, the software can bring the > database to the required schema version. In fact, if an uninitialized > database is found (for example, in a testing environment), > it might loop through dozens of schema changes until it gets to the > newest version. There are 3 major problems with traditional incremental migration strategies: 1. Once you've accumulated years of migrations, it's unnecessarily slow to bring up a new dev/test db. The tool is potentially running many ALTERs against each new table, vs a declarative approach which just runs a single CREATE with the desired final state. 2. The migration tool does not have a way of handling databases that aren't in one of the well-defined versioned states. This can happen easily on dev databases, where a developer just wants to experiment before landing on the final desired migration. 3. Most of these tools require a developer to code (in a specific programming language, or a proprietary DSL) the migration logic, as well as its rollback. There's plenty of room for human error there, especially since most of these tools don't automatically validate that the rollback actually correctly reverts the migration. In contrast, a declarative approach simply tracks the final desired state (a repo containing a bunch of CREATE statements), and the tool figures out how to properly transition from any arbitrary state to the new desired state. This permits the state to be expressed in pure SQL, with no need for a DSL or custom programming, nor any need to define rollback logic. A declarative tool can also be used in an automated reconciliation loop, which can handle situations like a master failing mid-schema-change in a sharded environment. This is analogous to other declarative tools, such as k8s. There are several recent open source projects using the declarative approach for schema management. I'm the author of one, Skeema [1], for MySQL/MariaDB. Others include migra [2] for Postgres, and sqldef [3] for MySQL or Postgres. Although declarative schema management is only recently picking up adoption, it isn't a new approach. Facebook has been handling their schema changes using a declarative approach for over 8 years now. [1] https://github.com/skeema/skeema https://github.com/skeema/skeema [2] https://github.com/djrobstep/migra https://github.com/djrobstep/migra [3] https://github.com/k0kubun/sqldef https://github.com/k0kubun/sqldef
- tracker1 8y ago
rhacker 8y ago
DevOps is about removing a secondary team that you throw something over the wall to for deployment. Instead the build pipeline (be it Circle, GitabCI, Jenk, Go, etc...) can control ALL environments and things have been optimized, in such a way, that a single click updates an environment. (including prod). The pipeline "code" and all its related scripts are the actual devops work products. They CAN be done by a single team that supports multiple other teams, but truthfully it should be a shared responsibility. Perhaps one team provides base scripts and answers the questions about where logs and monitoring data go (and of course those questions are answered with scripts essentially). So "ops" can still exist if your organization is large enough, but I definitely say there's no "thing" you throw over the wall anymore.
- nickthemagicman 8y ago
  So it's a secondary team to replace the secondary team.
  - camelNotation 8y ago
    Always. The question with any form of process optimization is how much smaller does the human presence need to be? The idea that we would replace people entirely is a sales pitch.
  - chupasaurus 8y ago
    The difference is an automation of a contract to make a new release in production: the process of delivering a new code should make all the necessary checks such as CI, regression tests, monitoring endpoints and so on up to deployment itself, in a reproducible manner.
peterwwillis 8y ago
My lame attempt at clarifying DevOps confusion goes something like this: DevOps is an analogue of Agile and Lean. Lean, which came from the Toyota Production System, doesn't just exist in one department and in one position. Saying that "DevOps is <x position> doing <x thing>" is like saying "Lean is a factory floor worker pulling an Andon cord when a product is defective". It massively understates all of the aspects of the method, and all the different roles involved, and how they get work done. Nothing about these methods prescribes that you have to use X technology, or that there is just one position that does work in one way. They prescribe a method, and you choose how you implement that method, and lots of different kinds of people in an organization are needed to make it all work. I started a Wiki recently to try to give some more in-depth understanding of DevOps (https://devops.yoga/overview.html https://devops.yoga/overview.html). It's still pretty bare-bones, but I think you can quickly get the idea that DevOps isn't one particular thing, or one particular team, or one set of technologies.
- saint_abroad 8y ago
  Agile set about to deliver working software frequently. Devops set about to deliver working software frequently. Both optimise for frequent delivery. The theory and practice of how working is measured is where they differ.
bcoughlan 8y ago
Treat a database as any other service. SQL is its API. Don't break the API contract of a view or procedure, create a new one and deprecate the old one. Not rocket science.
danellis 8y ago
> There is always some fixed cost to deploy. You should always, in principle, be driving down the fixed cost of deployment toward zero. Increasing deployment frequency without driving down that fixed cost is detrimental to the business and irresponsible. Surely it's the incremental cost you want to drive down. If there were only a fixed cost, it wouldn't matter how often you did it.
thoughtexprmnt 8y ago
Even though I've given in to using the "automated schema updates", or migrations, approach (Technique 1 in the article), I'm still uncomfortable with the idea of the application tier having permission/ability to manage database schema and execute DDL. I feel that it removes a key layer of defense-in-depth, and opens up the possibility for bugs and exploited vulnerabilities in application code to have hugely negative impacts. Even ORMs make me nervous for similar reasons, since they essentially enable the application tier to pass into the database any/all queries and DML. But like migrations, I allow ORMs for the convenience they provide during development.
- aschatten 8y ago
  The live application serving traffic does not need to have DDL permissions. The migration code can live in the same code base/application, but can be executed as a separate task with escalated privileges. Ideally, the same build should be used in every environment and all the configurations such as db endpoint, username and password must be external to the built and fed from the environment. The migration command can be a part of application, but only invoked automatically in dev environment. Most of ORMs allow to configure whether schema initialization happens at the startup or not. During deploy, the migration part can be executed separately in prod environment, before the actual deploy. Different tools provide different "hooks": e.g. heroku has release phase(https://devcenter.heroku.com/articles/release-phase https://devcenter.heroku.com/articles/release-phase), in spinnker you can add a separate stage for db migrations: https://blog.spinnaker.io/deploying-database-migrations-with-spinnaker-and-kubernetes-6e6761f9d28a https://blog.spinnaker.io/deploying-database-migrations-with..., which can use a different db user. In this case, migration functionality is disabled at startup in production environment and runtime db user need not have DDL privilege, because by the time it starts up, migration phase must have been finished.
luord 8y ago
This is interesting. For the very first application I had to create from scratch, the first thing I learned was about SQLAlchemy and the second was Alembic. It's almost alien to me to see that there are large organizations that have never heard of migrations.
CoolDev 8y ago
Found a really helpful code generator: codesimple.tools
unnouinceput 8y ago
The technique described in the article would fail if you'd have instances of different versions of software each expecting different version of database to connect to in a distributed database environment. So let me tell you how I did it. So my client was running multiple venues each having a database with local records and the client (meaning the CEO, the company board, and middle managers too, so in total more then 100 people running the software on their laptop) would connect to one venue or even to multiple venues at once to get reports of what is going on. So downtime of the venues was out of the question. Also some of them would have some other problems to solve and would fire up the software on their laptop even a month later, meaning the venues would already have different DB schema. So to solve this I've done the following: -each venue would have a table for SQL statements to upgrade the software local database. Also this would specify what schema No is current one. -each venue would have a table for software upgrades, that's it inside a blob field the software was there for that specific version. The software on client laptops would also have a local database, used for aggregating reports from multiple venues mainly, but also to automatically perform a venue DB schema upgrade if required: -a table with SQL statements to upgrade the connected venue schema DB to latest one. -a table the software upgrades, the same blob as described above. So, when the CTO (usually he was the one receiving the latest and the greatest) would get a new software version from DevOps, he would basically receive it by connecting his software to my DevOps DB. The software on his laptop would see that it has lower number than the connected DB schema No, access the upgrading fields, get the new software version in a temporary file and restart the software. The restart process would upgrade the software from that temporary file, run the necessary SQL commands to modify the local DB as well, if it was the case and launch the software with the latest upgrade. Now the CTO would connect to multiple venues, usually by just asking for the daily report on those and for each connected venue the software would see the connected venues schema No are older. Would run the necessary SQL commands to upgrade those venues, pushing the software latest blob as well and then get the records to compile the actual report. When the other members would connect to those upgraded venues they would receive the latest software as well same as the CTO received from my DevOps one. This ripple effect of upgrading software and schema was done without a single second of downtime from those venues. Also the venues did ran a local software on their own, which was also restarted and upgraded as soon as it was detecting the changes, in the same fashion as the software on the client laptops was upgraded. In practice, since the venues were scattered around US and Mexico from Boston all the way down to California and Mexico City, took around 30 seconds each, as they were all connected through VPN. So the most downtime was experienced by CTO for like 2 hours (~100 venues), and usually the guy would do this during lunch leaving the laptop to do this while he was attending other matters. The rest were only seeing the message "Upgrade in progress, please wait" for only 30 seconds and was very acceptable. Also the local venue software was less then 5 seconds with this message up, since it was connecting to a local DB, not over VPN. I did this in 2010, and while I departed in 2015 from that company this methodology is still in use without hiccups today by them.

263 points by YesThatTom2 8y ago

Actually, running integration tests against SQL RDBMS works really well. It might be a tad slow but the CI can easily handle that. We have Docker and all of that for this, and it works x-platform.

nicoburns 8y ago
Running the RBDMS with `eatmydata` (https://www.flamingspork.com/projects/libeatmydata/ https://www.flamingspork.com/projects/libeatmydata/) can significantly speed up tests in my experience. All of you're data is gone as soon as the process exits, but that doesn't matter for tests.
taeric 8y ago
Docker isn't really required for this. Any in memory db will provide good contract verifications. And a simple alpha/beta/gamma setup with the end database can get you the rest of the way there.
- tracker1 8y ago
  Docker can be a bit of a better option... Unless you're using the same database software for the in-memory operations as the actual deployment. Or aren't using many specific features the rdbms you have chosen (stored procedures, triggers, virtual fields, etc). Not that I'm a fan of triggers and prefer direct table access over SP, but virtual fields are GREAT during transitional changes. It also generally requires an abstraction between the DB variants.
  - taeric 8y ago
    Agreed. In the old world, this is what an alpha/beta/gamma environment could help test against. The in memory could be used to make sure your units and overall components were correctly specified. The actual installs of the DB software to isolated schemas were where you made sure you integrated with the DB correctly. That all said, I did not mean this to be that Docker doesn't provide some value. Just pointing out it is not a prerequisite. If that makes sense.
rodeoclown 8y ago
You can even do SQL DDL ALTER TABLEs in a Transaction on production, do the change, run smoke tests in a nested transaction that are all rolled back when complete, and if anything fails you roll back the DDL changes and are back where you started clean.
- tzs 8y ago
  Support for DDL in transactions seems to vary quit a bit between DB servers. In mysql (and I think also mariadb) ALTER TABLE cannot be rolled back, and also implicitly does a COMMIT on any in-progress transaction before altering the table. In PostgreSQL you generally can do ALTER TABLE in transactions, but there are dome restrictions or exceptions. If the alteration is to add a value to an enum, it cannot be done in a transaction. MS SQL Server seems to be OK with ALTER TABLE in the midst of a transaction, according to this article [1]. Oracle seems to be similar to mysql. Even the ones that support this well, like MS SQL Server, have restrictions on other DDL, such as creating indexes. I think I'd rather just assume no DDL in transactions and design my schema update procedure accordingly, rather than asking whoever is designing the new schema to try to limit themselves to changes from the old that avoid whatever statements whatever DB server we are using doesn't allow. [1] https://www.mssqltips.com/sqlservertip/4591/ddl-commands-in-transactions-in-sql-server-versus-oracle/ https://www.mssqltips.com/sqlservertip/4591/ddl-commands-in-...

CaptainZapp 8y ago

In the old methodology, any schema change requires the entire application to be shut down while a team of experts (or one very overworked DBA) modifies the schema manually. WHAT ? I'm working pretty much in an environment described in the article. Big bank, complicated database setup with multiple replicates and databases up to a size of ~25TB. On a (pre defined) release weekend there are essentially two entities to be released: The application (obviously) and the database changes. For any DB change a patch is created and tested through an environment of half a dozen instances. If bugs are found on already deployed db patches they are never fixed in the patch, but a subsequent patch (also tested through all environments) is created. Needles to say that anything provided, which applies database changes is version controlled. The idea to apply database patches manually is so damn outlandish to beggar believe. Anybody doing this on a production database without a strictly controlled (and tested!) break-fix should be fired on the spot.

edoceo 8y ago
Maybe training rather than termination?
- C1sc0cat 8y ago
  They should have never been put in that position in the first place.
- CaptainZapp 8y ago
  I'd consider this such a gross case of malpractice on the level of a surgeon forgetting some instruments in patient, before sewing her shut. Errors and mistakes happen, no question about that. And any ol' mistake certainly shouldn't be a firing offense. But this behaviour is so grossly incompetent that I don't believe that it can't be helped with any training.
  - geofft 8y ago
    Surgeons are generally trained with a grueling multi-year academic and practical program about how to conduct surgery. DBAs aren't. If you're lucky you have someone who has been trained to be a DBA professionally - but I've never worked with one, all the SQL I've interacted with has been run by people with unrelated job titles who learned how to operate databases by Googling when necessary. You're welcome to fire people if you think it's going to help. I'm just curious how you think a skilled, trained DBA is going to show up at your desk when that happens.

C1sc0cat 8y ago

"rather than using SELECT . If you do use SELECT , don't assume that the fields are in a particular order." There are professional developers that use Select * EVER! Maybe add write a simple Select, Insert and Update Statement to the FizBuzz test.

mschuster91 8y ago
> There are professional developers that use Select * EVER! Old code from the 2000 PHP era is full of this. Never underestimate the horrors of "legacy" code bases, and never underestimate what people do in short lived "maintenance scripts" or "helpers" that are used well beyond the intended life span. Or "proofs of concept" aka "unbelievably dirty hacks to get a MVP" that end up being extended and extended to being the "real product" in the end. For an MVP I will always use "select *" and friends simply for speed of development, but clearly communicate that this is in no way intended for production usage.
- lowercased 8y ago
  > clearly communicate that this is in no way intended for production usage. But it likely will be anyway. I use a variety of ORMs and hand-rolled SQL, and often will do select . The times when I know there may be a performance impact, or I'm debugging an existing performance impact, hand-rolled SQL always is on the table, but often* the issue is either missing/poor indexes or simply table design that didn't foresee the current use case. There's been a couple of times where a stray select * was, really, pulling back unintended very large blobs and causing a performance issue, but it's been infrequent compared to index issues.
porpoisely 8y ago
You should never use "SELECT * FROM SomeTable" to retrieve data because the column order isn't guaranteed. Also, you should always use column names to access data. However, "SELECT * FROM SomeTable" can be used to determine if a table had data ( for "EXISTS" clause ) or to "COUNT" the rows of the table. It's industry standard. You could also use something like "SELECT 1 FROM SomeTable" but it isn't natural.

ed_elliott_asc 8y ago

Microsoft isn’t given enough credit for what they have done investing in ssdt it really is a game changer for sql developers

mdpopescu 8y ago
Yep; discovering - and using - SSDT to replace a 3-day manual migration with a half-hour automated one is still one of my "most proud moment" stories.

alexandercrohde 8y ago

No idea how this made front-page. Sounds like the author met one crazy person and wrote an article about it. Totally irrelevant to me, I've never seen a company like this in practice in my long career.

mschuster91 8y ago
> Totally irrelevant to me, I've never seen a company like this in practice in my long career. Never worked in government, I assume...
- stareatgoats 8y ago
  Two private sector industry leader companies that I worked for had never heard of a staging environment before I joined, and the process to implement one was long and painful. The argument against it was: we wouldn't need a staging environment if we had competent developers ... Niche industry, so still not very big corporations, probably explains it.
GauntletWizard 8y ago
I've seen paragons of "Devops" with precisely this practice in significant chunks of the company. You've gotten lucky - or possibly just buried your head in the sand.
- 0xFACEFEED 8y ago
  Probably less to do with luck/head-in-sand and more to do with filtering out potential employers.
breakingcups 8y ago
I've seen plenty of companies function pretty much exactly as described. Just because it is irrelevant to you does not mean it's not interesting to others and thus front-page worthy.
choward 8y ago
> Sounds like the author met one crazy person He either met a straw man or a troll.

gregw2 8y ago

For scripting database changes so they are deployed with app changes... or so they are automated and auto-tested upon deployment and in version control, I've had good success with open source Liquibase. Worth looking at Flyway also.

clemson20 8y ago
For those using Microsoft SQL Server, we've had success using SQL Server Data Tools (SSDT), to automate database deployments for a few years now. Prior to this, developers would check-in 'delta scripts' - which were just all the sql changes that were manually applied. Now, the entire schema for multiple products is stored in source control as the source of truth, and deployments themselves generate and apply the deltas.

giobox 8y ago

The premise of this article is just too bizarre for me - even the most inexperienced software engineer today surely can see the fallacy of claiming a SQL database prevents use of DevOps style practices. Either the author A. Met a crazy person, or B. Invented the anecdote for sake of what they beilieved to be a snappy title.

onemoresoop 8y ago
Absolutely B to sell the article
ColinDabritz 8y ago
There are a lot of crazy people in industry, unfortunately. Not as much at the big flashy tech startups or places you hear about in the news, but there are still plenty of 'work a day' businesses in corners of industry that do not have expertise in these areas that absolutely should. I helped my current employer make this transition and it was similar to the description in the article. Controlling database change in source was foreign to them.
hobs 8y ago
context: I work as a DBA. It is a bizarre premise, and its alive and well at an insane amount of shops today. When I walked in the door at my last gig(and a few others), they were hand running all deployments, and often manually troubleshooting in production when it fucked up because they didn't have a QA environment.
- ianamartin 8y ago
  Everyone has a QA environment. Some people have separate production environments.
  - dvtrn 8y ago
    You and I may be defining "everyone" differently.
    - 8y ago

andmarios 8y ago

Sometimes, devops, admins, DBAs, etc are there for QA. It's not that they don't know how to automate everything, it's that they know if you permit someone to do a dumb thing, they'll probably do. It's one thing to talk about good practices and another to build a large development team that can understand and follow them. By moving the responsibility to devs, you tax your senior devs, who have to monitor middle and junior devs more closely and sometimes micromanage them as a consequence. A few schema changes a year are more easy to test, justify and —most importantly— track than many small ones going on at the same time. How do you even manage the schema version when there are multiple branches that require changes and you don't know the order they will be merged in? Instead of a version number, you start to use flags. How about deprecated features? How and when do you clean up? Something else that might be important, are performance optimizations. I agree on the suggestions of the article and it's the way I try to write code that uses a SQL store, just saying that sometimes in real life you have to make things harder in order to stay safe.

exabrial 8y ago

Great example of blaming the tool [SQL] to try and bring in hip technology when the people are to actually blame. We use Liquibase, which is event-sourcing for database schema changes, and a database that supports online schema changes.

djohnston 8y ago

what?

deleted 8y ago

[deleted]

LaserToy 8y ago

I always thought that DevOps are developers who can do ops. But in recent years I see more and more people calling themselves DevOps, even all they do is ops with simple scripting. I think we need a new name for developers who run their code in prod, as DevOps is diluted. So, I’m this light name of the article is utter BS. As devs we deal with a lot of different databases, be is SQL, NO SQL or graph and dealing with them is part of our job. If all you do is run stuff and release - you don’t have Dec component, so you just Ops.

Thaxll 8y ago
Dev that do Ops is usually a terrible idea.
- busterarm 8y ago
  It depends on the person and mindset. I switched from dev to ops fulltime (as in not "DevOps") and I'm perfectly at home here. If anything, I'm the one bridging the gap between us and the development teams. My team thinks I'm an ops person who does development, but I don't really find that to be true.
- bovermyer 8y ago
  Why do you say that?
  - SteveNuts 8y ago
    I've worked at a couple places that had developers build their own environments before I got there (I'm more of a systems engineer). There was no thought to high availability, security, scalability, reliable backups, etc. I'm not saying all devs are like this, but that's just been my experience.
    - Thaxll 8y ago
      This. Honestly Ops is a different mindset that devs, the tldr version is devs don't really know / care about running things in production. I don't blame them it's just different set of skills.

ianamartin 8y ago

Everything about this article is really weirdly written. It's glib in the way that preachers are glib from the pulpit and trying to get you to just believe something while glossing over your particular concerns. Is "doing DevOps" impossible? "No! It's simply a difficult transition." Oh, I see now. That makes it all better. Never mind that the author's "friend" never claimed it was impossible. Rather that it was fundamentally a bad decision. It's like a parable, but a really bad one. I wonder when the author is going to bring up Agile and just assume we're all true believers and that anything that incorporates Agile must be good without question. Oh, there it is. Right there. So the difficult transition of DevOps is presented here as necessary and appropriate because 1. Agile, 2. Faster, and 3. Because it avoids some incredibly horrible practices the friend's DBA team does. 1. I won't get into here. That's a different flame-war for a different time. But people should know better than to write articles with the unquestioning premise that Agile automatically justifies any and every difficult transition. 2. I'm highly allergic to justifications that involve moving faster. Most companies do not have a problem delivering value fast enough. They have a problem delivering any value at all, and delivering a reliable product at any pace. Faster time to value is the worst possible justification if you're trying to convince me to experience some pain. I am a big proponent of DevOps, and I bake it in to all my work, both at my work-work and in my side projects. It's worth the difficulty to me because it allows me to reliably get my code from point a to point b. That it's perhaps faster (is it really faster if you add up the person-hours? I don't know. I don't really care) is a little extra niceness if it's there at all. 3. DevOps has basically nothing to do with the pain that's causing the situation described in this parable. DevOps won't fix bad DBAs or sloppy Devs. DevOps won't fix a manager that allows this kind of situation to happen in the first place. DevOps won't fix a company culture that allows Management to run this kind of a dev shop. It's like the "friend" here is dying of dehydration in the desert and this dude rolls up in his car and says, "Hey man, you really need to eat more vegetables and less red meat and exercise at least 30 minutes a day. It will totally change your life! Also, I have these essential oils that will cleans toxins from your spirit aura and help you focus on your life goals. Good luck!"

pjungwir 8y ago

I really enjoyed Thomas Limoncelli's Time Management for System Administrators. I've also got The Practice of System and Network Administration on my shelf, and hopefully one of these years I'll read it---maybe once my time management is better. :-) I like that Tom was practicing and advocating system-administration-by-writing-code way before DevOps, and to me he represents a tradition of automation that far predates the buzzwords. I've lived in the world of Rails and Django so long, I sort of take schema migrations for granted. It's interesting to see such a high-level person covering this ground; it reminds me of how much these frameworks have given us, and how so many organizations are still practically emailing around zip files of source code. His Technique 2 feels harder to me: write the code to work on both old & new schemas, and deploy the schema change after the code change. I know this is what Google does though, and I think it's the only safe way to deploy rollback-able code changes. I'm curious who else is doing it, and especially how you are scheduling it. Especially with a CI/CD workflow, I'm used to getting things integrated and letting them get deployed "whenever". So how do you make sure the schema changes go out "later", and what gating is required first, and how do you get buy-in to spend time on that after the feature is already live? I'm not asking about the technical how-to, but the organizational/political how-to. For instance if you're using Pivotal Tracker, do you make a separate PT story? Leave the old one open until the schema changes are deployed? What does it concretely look like?

djrobstep 8y ago
> I've lived in the world of Rails and Django so long, I sort of take schema migrations for granted. And there's lots more room for improvement over Rails and Django migrations. Some of the problems: - Annoying version number management - Management of long "chain" of migration files - Unnecessary coupling between ORM and migration tool - Heavyweight process to generate and run each migration, which slows down local development - Bad testability Autosyncing local dev databases, and automatically generating migration scripts with a diff tool is a better approach, imho.
- evanelias 8y ago
  Totally agree -- I wrote some similar thoughts elsewhere in the thread. It might just take some time for more developers to gain experience with the declarative/diff-based approach, to become comfortable with it.

evanelias 8y ago

Ignoring the rather bonkers anecdote at the start of the article, I'd also argue against one of the other recommendations here, even though it is quite common in practice: > The command in array index n upgrades the schema from version n-1 to n. > Thus, no matter which version is found, the software can bring the > database to the required schema version. In fact, if an uninitialized > database is found (for example, in a testing environment), > it might loop through dozens of schema changes until it gets to the > newest version. There are 3 major problems with traditional incremental migration strategies: 1. Once you've accumulated years of migrations, it's unnecessarily slow to bring up a new dev/test db. The tool is potentially running many ALTERs against each new table, vs a declarative approach which just runs a single CREATE with the desired final state. 2. The migration tool does not have a way of handling databases that aren't in one of the well-defined versioned states. This can happen easily on dev databases, where a developer just wants to experiment before landing on the final desired migration. 3. Most of these tools require a developer to code (in a specific programming language, or a proprietary DSL) the migration logic, as well as its rollback. There's plenty of room for human error there, especially since most of these tools don't automatically validate that the rollback actually correctly reverts the migration. In contrast, a declarative approach simply tracks the final desired state (a repo containing a bunch of CREATE statements), and the tool figures out how to properly transition from any arbitrary state to the new desired state. This permits the state to be expressed in pure SQL, with no need for a DSL or custom programming, nor any need to define rollback logic. A declarative tool can also be used in an automated reconciliation loop, which can handle situations like a master failing mid-schema-change in a sharded environment. This is analogous to other declarative tools, such as k8s. There are several recent open source projects using the declarative approach for schema management. I'm the author of one, Skeema [1], for MySQL/MariaDB. Others include migra [2] for Postgres, and sqldef [3] for MySQL or Postgres. Although declarative schema management is only recently picking up adoption, it isn't a new approach. Facebook has been handling their schema changes using a declarative approach for over 8 years now. [1] https://github.com/skeema/skeema https://github.com/skeema/skeema [2] https://github.com/djrobstep/migra https://github.com/djrobstep/migra [3] https://github.com/k0kubun/sqldef https://github.com/k0kubun/sqldef

tracker1 8y ago

rhacker 8y ago

DevOps is about removing a secondary team that you throw something over the wall to for deployment. Instead the build pipeline (be it Circle, GitabCI, Jenk, Go, etc...) can control ALL environments and things have been optimized, in such a way, that a single click updates an environment. (including prod). The pipeline "code" and all its related scripts are the actual devops work products. They CAN be done by a single team that supports multiple other teams, but truthfully it should be a shared responsibility. Perhaps one team provides base scripts and answers the questions about where logs and monitoring data go (and of course those questions are answered with scripts essentially). So "ops" can still exist if your organization is large enough, but I definitely say there's no "thing" you throw over the wall anymore.

nickthemagicman 8y ago
So it's a secondary team to replace the secondary team.
- camelNotation 8y ago
  Always. The question with any form of process optimization is how much smaller does the human presence need to be? The idea that we would replace people entirely is a sales pitch.
- chupasaurus 8y ago
  The difference is an automation of a contract to make a new release in production: the process of delivering a new code should make all the necessary checks such as CI, regression tests, monitoring endpoints and so on up to deployment itself, in a reproducible manner.

peterwwillis 8y ago

My lame attempt at clarifying DevOps confusion goes something like this: DevOps is an analogue of Agile and Lean. Lean, which came from the Toyota Production System, doesn't just exist in one department and in one position. Saying that "DevOps is <x position> doing <x thing>" is like saying "Lean is a factory floor worker pulling an Andon cord when a product is defective". It massively understates all of the aspects of the method, and all the different roles involved, and how they get work done. Nothing about these methods prescribes that you have to use X technology, or that there is just one position that does work in one way. They prescribe a method, and you choose how you implement that method, and lots of different kinds of people in an organization are needed to make it all work. I started a Wiki recently to try to give some more in-depth understanding of DevOps (https://devops.yoga/overview.html https://devops.yoga/overview.html). It's still pretty bare-bones, but I think you can quickly get the idea that DevOps isn't one particular thing, or one particular team, or one set of technologies.

saint_abroad 8y ago
Agile set about to deliver working software frequently. Devops set about to deliver working software frequently. Both optimise for frequent delivery. The theory and practice of how working is measured is where they differ.

bcoughlan 8y ago

Treat a database as any other service. SQL is its API. Don't break the API contract of a view or procedure, create a new one and deprecate the old one. Not rocket science.

danellis 8y ago

> There is always some fixed cost to deploy. You should always, in principle, be driving down the fixed cost of deployment toward zero. Increasing deployment frequency without driving down that fixed cost is detrimental to the business and irresponsible. Surely it's the incremental cost you want to drive down. If there were only a fixed cost, it wouldn't matter how often you did it.

thoughtexprmnt 8y ago

Even though I've given in to using the "automated schema updates", or migrations, approach (Technique 1 in the article), I'm still uncomfortable with the idea of the application tier having permission/ability to manage database schema and execute DDL. I feel that it removes a key layer of defense-in-depth, and opens up the possibility for bugs and exploited vulnerabilities in application code to have hugely negative impacts. Even ORMs make me nervous for similar reasons, since they essentially enable the application tier to pass into the database any/all queries and DML. But like migrations, I allow ORMs for the convenience they provide during development.

aschatten 8y ago
The live application serving traffic does not need to have DDL permissions. The migration code can live in the same code base/application, but can be executed as a separate task with escalated privileges. Ideally, the same build should be used in every environment and all the configurations such as db endpoint, username and password must be external to the built and fed from the environment. The migration command can be a part of application, but only invoked automatically in dev environment. Most of ORMs allow to configure whether schema initialization happens at the startup or not. During deploy, the migration part can be executed separately in prod environment, before the actual deploy. Different tools provide different "hooks": e.g. heroku has release phase(https://devcenter.heroku.com/articles/release-phase https://devcenter.heroku.com/articles/release-phase), in spinnker you can add a separate stage for db migrations: https://blog.spinnaker.io/deploying-database-migrations-with-spinnaker-and-kubernetes-6e6761f9d28a https://blog.spinnaker.io/deploying-database-migrations-with..., which can use a different db user. In this case, migration functionality is disabled at startup in production environment and runtime db user need not have DDL privilege, because by the time it starts up, migration phase must have been finished.

luord 8y ago

This is interesting. For the very first application I had to create from scratch, the first thing I learned was about SQLAlchemy and the second was Alembic. It's almost alien to me to see that there are large organizations that have never heard of migrations.

CoolDev 8y ago

Found a really helpful code generator: codesimple.tools

unnouinceput 8y ago

The technique described in the article would fail if you'd have instances of different versions of software each expecting different version of database to connect to in a distributed database environment. So let me tell you how I did it. So my client was running multiple venues each having a database with local records and the client (meaning the CEO, the company board, and middle managers too, so in total more then 100 people running the software on their laptop) would connect to one venue or even to multiple venues at once to get reports of what is going on. So downtime of the venues was out of the question. Also some of them would have some other problems to solve and would fire up the software on their laptop even a month later, meaning the venues would already have different DB schema. So to solve this I've done the following: -each venue would have a table for SQL statements to upgrade the software local database. Also this would specify what schema No is current one. -each venue would have a table for software upgrades, that's it inside a blob field the software was there for that specific version. The software on client laptops would also have a local database, used for aggregating reports from multiple venues mainly, but also to automatically perform a venue DB schema upgrade if required: -a table with SQL statements to upgrade the connected venue schema DB to latest one. -a table the software upgrades, the same blob as described above. So, when the CTO (usually he was the one receiving the latest and the greatest) would get a new software version from DevOps, he would basically receive it by connecting his software to my DevOps DB. The software on his laptop would see that it has lower number than the connected DB schema No, access the upgrading fields, get the new software version in a temporary file and restart the software. The restart process would upgrade the software from that temporary file, run the necessary SQL commands to modify the local DB as well, if it was the case and launch the software with the latest upgrade. Now the CTO would connect to multiple venues, usually by just asking for the daily report on those and for each connected venue the software would see the connected venues schema No are older. Would run the necessary SQL commands to upgrade those venues, pushing the software latest blob as well and then get the records to compile the actual report. When the other members would connect to those upgraded venues they would receive the latest software as well same as the CTO received from my DevOps one. This ripple effect of upgrading software and schema was done without a single second of downtime from those venues. Also the venues did ran a local software on their own, which was also restarted and upgraded as soon as it was detecting the changes, in the same fashion as the software on the client laptops was upgraded. In practice, since the venues were scattered around US and Mexico from Boston all the way down to California and Mexico City, took around 30 seconds each, as they were all connected through VPN. So the most downtime was experienced by CTO for like 2 hours (~100 venues), and usually the guy would do this during lunch leaving the laptop to do this while he was attending other matters. The rest were only seeing the message "Upgrade in progress, please wait" for only 30 seconds and was very acceptable. Also the local venue software was less then 5 seconds with this message up, since it was connecting to a local DB, not over VPN. I did this in 2010, and while I departed in 2015 from that company this methodology is still in use without hiccups today by them.