The Right Connection

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714297

    Comments posted to this topic are about the item The Right Connection

  • aklt

    SSC Enthusiast

    Points: 160

    I use the same solution for both when using an SSMS on my local computer as well as when using RDP. Use a horrible background color (red works well). That way there is no chance to know your not on a production server. 
    If you are using the same AD account on your own computer as well as on the production server... you nuts?
    If you develop stuff as well as do things in production you really should have 2 accounts, as to limit the risks. A developer account should, in my mind, never have prod access.
    Setting connection color... the color on the Tabs/statusbar is just to little to catch ones eye. But if the entire background is a different color... hard to miss.

  • Phil Factor

    SSCoach

    Points: 19702

    Maybe we should train our subconscious mind into good habits by treating all servers as though they were production servers.

    Best wishes,
    Phil Factor
    Simple Talk

  • rashid.hussain

    SSC Enthusiast

    Points: 130

    My method is taking advantage of having 2 physical monitors, (switch this up the way you choose) so the left monitor is for SSMS into Production or Pre-Prod ONLY and then the monitor on the right is for everything else.
    I also use the coloured tabs in SSMS as an extra fail safe.

  • allinadazework

    SSCarpal Tunnel

    Points: 4361

    Four part naming anyone? Does anyone ever do that? I use three part naming when shifting stuff around different DBs on the same server quite often.

    My tip for those, like me, who have to perform lots of CRUD ops on production databases is to always write your query as a "Select" first to check what you are going to insert/update/delete/screw up!. If you are not sure that the results of your "Select" are what you are expecting then double check it (store a copy somewhere so you can recover it easily). Only change your query to the update/delete/insert at the very end when you are 100 percent confident that it is doing what you want. Even then you will make mistakes because your expectations were incorrect.

    Always consider how you will undo/retrieve/fix what you have done before you do it!

    I do development and also have to manage and update live data. I wouldn't use a different account/connection each time it's just not practical.

  • call.copse

    SSCoach

    Points: 16673

    There's not much you can really do apart from ensuring you think and check what you're doing anytime you run any data modifying query. Check the tab, check the database you're on, verify then run.

  • allinadazework

    SSCarpal Tunnel

    Points: 4361

    It would probably help a great deal if servers had better names... they always seem to be algebraic. How would the average developer know if it was production or not?

  • k.schouten

    Mr or Mrs. 500

    Points: 526

    Maybe there is an easy way to use something like  "where environment <> 'PROD' " in update and delete statements?

  • jonathan 33820

    Valued Member

    Points: 59

    Good article.  Great to know about colors!
    I always start a new instance of SSMS for each database this way all tabs point to the same dm.
    I resize the window that I am not working on making it tall and skinny or or short and wide so it is clear that this is the "other" DB.
    When I write an ad hoc insert or delete I write it like this:
    Select *
    --DELETE
    From TableName WHERE
    A=1 AND ...
    If I execute the whole page then this is just a select.  I can test the where clause with the select like the other poster said.  The WHERE hanging at the end of the line will break the query if I don't also include the conditions (that often will not fit on the same line).
    I drink lot's of coffee.

  • garry.lovesey

    SSC Veteran

    Points: 289

    Personally, this article is more likely to make me trust the company.
    They acknowledged the issue, laid out their findings on what happened, owned up to their shortcomings and outlined improvements going forward. That is the sort of company that I would do business with.

  • Ken Mercadante

    SSC Enthusiast

    Points: 123

    I use the colored connections in Management Studio, but they're inconsistent and don't always change when changing connections.

    It would be nice if Microsoft included an option that allowed you to specify a particular database as "Production", and then any Inserts, Updates, Deletes (or any of the other commands that can ruin a career) generate a modal confirmation dialog before executing.

  • roger.plowman

    SSChampion

    Points: 10114

    Well, the perfect solution is isolation of responsibility. Developers never touch production, DBAs never touch anything BUT production. Easy. 🙂

    That assumes, of course, your company has sufficient financial and personnel resources to actually do something like that. As a lone wolf developer/DBA/Chief Cook and Bottle Washer my company doesn't. Like 99% of other companies out there.

    So...colored tabs are the drowning IT pro's lifeline. I also like the colored background idea, I use it to identify servers for RDP, but I'm not sure how you'd do that for SSMS which is where I develop SPs.

    I try to stay out of production as much as I can, and enforce the "changes migrate, damn it!" rule, but production interruptions bring down the wrath of users and down from on-high, so...sigh.

    I don't know if separating instances or servers for dev/QA/production actually helps or hinders the problem of connecting to the wrong database. Anybody got input on that? I wonder if separate servers/instances for dev/QA/production actually makes the problem worse?

  • aklt

    SSC Enthusiast

    Points: 160

    roger.plowman - Thursday, April 19, 2018 6:35 AM

    So...colored tabs are the drowning IT pro's lifeline. I also like the colored background idea, I use it to identify servers for RDP, but I'm not sure how you'd do that for SSMS which is where I develop SPs.

    If you use a separate AD account for production. You can just start SSMS under that (run as is nice). Does mean that you will have 2 SSMS running under 2 different accounts. One for Dev and one for Prod. But it works well. Doesnt effect anything in terms of usage (just another window)
    If you dont have that and use the same AD account for both Dev and Prod SQL access... then this solution wont work.

  • jay-h

    SSCoach

    Points: 18801

    In general, whenever I'm doing something that can change things, I avoid having production and development connected at the same time (I've almost screwed up a couple of times and learned a lesson)

    Scripts can be a little dangerous, but it's probably helpful to address the full server/database name in the script, so it will error if run on the wrong machine.

    ...

    -- FORTRAN manual for Xerox Computers --

  • ken.trock

    SSCertifiable

    Points: 5147

    allinadazework - Thursday, April 19, 2018 3:53 AM

    It would probably help a great deal if servers had better names... they always seem to be algebraic. How would the average developer know if it was production or not?

    Our servers are named like an automobile VIN where each character in the name means something. So one position will either be d for development or p for production. Not always easy to spot!

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply