3 ms·

Strikes me as extremely reckless to edit a production database live with some tab completing editor. Is it really the norm to do that?

by gnu8 9y ago

LandR 9y ago
I don't know if its the norm but we do it here. Right click table, edit top 200 rows to add a new row. We, unfortunately, edit the live db all the time.
- EGreg 9y ago
  It would seem to me that you should strictly control what software can edit the db. If you need some special feature, build it into your app and test it first. A general purpose editor can destroy the consistency of your entire database, or parts of it which you won't know about but your users will!
  - LandR 9y ago
    Yes, I understand it's a disaster waiting to happen. I mean if we want to edit a stored proc in the db, right click it in SSMS, script to modify, make the change, F5. Hope everything doesn't blow up. It's not ideal.
    - gnu8 9y ago
      Why wouldn't you do that in the dev database, then push to test, then push to prod?
      - LandR 9y ago
        We dont' have a dev / test / live database. All dev and testing is dont against the live database. YOu just have to be very, very, very careful. It's something we are trying to implement now, honestly. It's on the TODO list. The problem I'm having is where do you start? How do you simulate the data flowing through live in the test / dev enviornment.
        thehardsphere 9y ago
        Here's two ideas: 1. Set up replication, with your test db the replication slave of your production database, such that changes in production are mirrored in testing nearly instantly. 2. Take your production database backups (you do have those, right?) and restore them to the test database. This is less fresh than a replication setup but if you're just doing functional testing it should be enough to say "Well, okay, this little change isn't going to blow everything up."
        j_s 9y ago
        Upvotes for honest anecdata and asking for advice! > How do you simulate the data flowing through live in the test / dev enviornment. Most don't, rolling with the schema and 'minimum viable' data flow instead. Make darn sure your backups work by testing restores regularly. Restoring a production backup elsewhere may be an option if it's possible to avoid sensitive data (scrubbing afterward introduces risk of missing something, and should be scripted/repeatable). Exactly matching production data flow is not as important as avoiding accidentally hosing the prod db.
        EGreg 9y ago
        Let me tell you from my experience. One time I was editing code on the live server using SFTP, before I knew about version control. And the whole thing was deleted, with no way to get it back. It doesn't take that long to put some safeguards. Here is the thing: WHENEVER you say "you have to be VERY careful" or blame someone for making a mistake, that's a place you need to put more checks. The computer should check routine things, not people. "The problem I'm having is where do you start? How do you simulate the data flowing through live in the test / dev enviornment." I am guessing you use MySQL so simply set up MYSQL REPLICATION and test all your stuff on the replica database first. Actually you should periodically dump the replica and test on a snapshot, so you can mess up the snapshot. Main db -> Replica -> Snapshot.
        LandR 9y ago
        It's a SQLSERVER, which at this point is about 200GB. Can I say just replicate the schema and then run a script to populate some test data. Or just move over a subset of the main DB? Got any good articles on how to setup test environments. I would then need all the dev code to point to the test one, not the live db and any Web APIs to be able to switch to point to the test db (which must mean running 2 web apis, a live and a test one...) Seems like it gets very complicated, but probably worth figuring out. We don't even have CI yet.
      - btschaegg 9y ago
        Not to excuse anything here, but you wouldn't believe how _insane_ some business environments are when it comes to databases. And yes, by that I mean those systems that you wouldn't believe anyone would ever want to risk losing. Depending on what you're working on, chances are there even are people who use _security_ as an excuse to basically prevent any halfway useful emergency strategy one could think of when handling data impossible to pull off (oh the irony).
        gnu8 9y ago
        Availability is one leg of the CIA triad. Security is the justification for having backups and a recovery strategy, not an excuse for ignoring it.
  - dumbmatter 9y ago
    In my job, I edit the production database live because adding a special feature (or even a bug fix) takes like a year to get deployed to production, even if it's as simple as a one line change.

Strikes me as extremely reckless to edit a production database live with some tab completing editor. Is it really the norm to do that?

by gnu8 9y ago

Strikes me as extremely reckless to edit a production database live with some tab completing editor. Is it really the norm to do that?

LandR 9y ago

I don't know if its the norm but we do it here. Right click table, edit top 200 rows to add a new row. We, unfortunately, edit the live db all the time.

EGreg 9y ago
It would seem to me that you should strictly control what software can edit the db. If you need some special feature, build it into your app and test it first. A general purpose editor can destroy the consistency of your entire database, or parts of it which you won't know about but your users will!
- LandR 9y ago
  Yes, I understand it's a disaster waiting to happen. I mean if we want to edit a stored proc in the db, right click it in SSMS, script to modify, make the change, F5. Hope everything doesn't blow up. It's not ideal.
  - gnu8 9y ago
    Why wouldn't you do that in the dev database, then push to test, then push to prod?
    - LandR 9y ago
      We dont' have a dev / test / live database. All dev and testing is dont against the live database. YOu just have to be very, very, very careful. It's something we are trying to implement now, honestly. It's on the TODO list. The problem I'm having is where do you start? How do you simulate the data flowing through live in the test / dev enviornment.
      - thehardsphere 9y ago
        Here's two ideas: 1. Set up replication, with your test db the replication slave of your production database, such that changes in production are mirrored in testing nearly instantly. 2. Take your production database backups (you do have those, right?) and restore them to the test database. This is less fresh than a replication setup but if you're just doing functional testing it should be enough to say "Well, okay, this little change isn't going to blow everything up."
      - j_s 9y ago
        Upvotes for honest anecdata and asking for advice! > How do you simulate the data flowing through live in the test / dev enviornment. Most don't, rolling with the schema and 'minimum viable' data flow instead. Make darn sure your backups work by testing restores regularly. Restoring a production backup elsewhere may be an option if it's possible to avoid sensitive data (scrubbing afterward introduces risk of missing something, and should be scripted/repeatable). Exactly matching production data flow is not as important as avoiding accidentally hosing the prod db.
      - EGreg 9y ago
        Let me tell you from my experience. One time I was editing code on the live server using SFTP, before I knew about version control. And the whole thing was deleted, with no way to get it back. It doesn't take that long to put some safeguards. Here is the thing: WHENEVER you say "you have to be VERY careful" or blame someone for making a mistake, that's a place you need to put more checks. The computer should check routine things, not people. "The problem I'm having is where do you start? How do you simulate the data flowing through live in the test / dev enviornment." I am guessing you use MySQL so simply set up MYSQL REPLICATION and test all your stuff on the replica database first. Actually you should periodically dump the replica and test on a snapshot, so you can mess up the snapshot. Main db -> Replica -> Snapshot.
        LandR 9y ago
        It's a SQLSERVER, which at this point is about 200GB. Can I say just replicate the schema and then run a script to populate some test data. Or just move over a subset of the main DB? Got any good articles on how to setup test environments. I would then need all the dev code to point to the test one, not the live db and any Web APIs to be able to switch to point to the test db (which must mean running 2 web apis, a live and a test one...) Seems like it gets very complicated, but probably worth figuring out. We don't even have CI yet.
    - btschaegg 9y ago
      Not to excuse anything here, but you wouldn't believe how _insane_ some business environments are when it comes to databases. And yes, by that I mean those systems that you wouldn't believe anyone would ever want to risk losing. Depending on what you're working on, chances are there even are people who use _security_ as an excuse to basically prevent any halfway useful emergency strategy one could think of when handling data impossible to pull off (oh the irony).
      - gnu8 9y ago
        Availability is one leg of the CIA triad. Security is the justification for having backups and a recovery strategy, not an excuse for ignoring it.
- dumbmatter 9y ago
  In my job, I edit the production database live because adding a special feature (or even a bug fix) takes like a year to get deployed to production, even if it's as simple as a one line change.