12

I just learned the hard way that disconnecting from a server in the Object Explorer doesn't stop you afterwards from executing query windows that were already open on that server.

My situation is like this: I have one instance of SSMS that I use to connect to our dev/staging server and to our production server. I had to delete a bunch of data on dev so I figured I should close my connection to production, but I didn't pay attention to the query window I was using. (Luckily we had a backup of only a few hours old.)

I'm not the first person to destroy production data and I won't be the last I'm sure. So I'm looking for checklists, best practices, etc. that help you to minimise the risk of executing queries on the wrong database. Have you had this happen to you before, and how have you adapted your workflow to try to avoid this?

  • 4
    pay attention to what you're doing. – swasheck May 22 '14 at 20:12
  • My SQL tool (not SSMS) allows me to turn on a "read only mode" which simply rejects any statement that could potentially change the database. – a_horse_with_no_name May 23 '14 at 11:45
  • Get enough sleep and exercise, pay more attention. – user38898 May 23 '14 at 13:22
11

One thing I like to do in SSMS is to use Custom Colours when connecting to database. So you choose a nice bright Red for Live databases, and a gentle blue or green for dev or test systems. I used to use the inbuilt SSMS, but these days I prefer the SSMS Tools Addon Colour coding.

Like this

Or like this for SSMS Tools (A really nice addon, and I find the colour better when it's on top, rather than on the bottom like the built in on) Or this

  • 2
    +1 This is what I do. Use red for production, yellow for test environments and green for my local development database. The old traffic-light metaphor works well here. – LeopardSkinPillBoxHat May 22 '14 at 22:11
6

Depending on who you ask, it will require a little more work, but I got in the habit of always using the below statement for all production or pre-production query windows, and for all UPDATE, DELETE and INSERT statements in all environments.

BEGIN TRAN
-- END OF QUERY WINDOWS
ROLLBACK TRAN
PRINT 'Transaction rolled back.'

If I see this, I will immediately know, "Oops, that query window was still connected" or "Oh crap, I auto did something I shouldn't have" - and yes, you can close the database in object explorer, but a query window can still be connected. In my mind, all production queries should be highlighted and run with BEGIN TRAN; an accidental F5 on everything, should roll everything back, not COMMIT. What this does is force to user to be conscious of his or her actions; similar to taking a picture of every meal you eat will help you lose weight because you must stop and think about what you're doing.

Does it take longer to do? Yes. Does it stop 100% of errors. Yes, because nothing ever commits, unless I manually force the COMMIT, post typing it, which the very nature of will have forced me to consider the COMMIT.

  • 4
    I preach about this practice too (and it's another feature of SSMS Tools Pack - allowing you to customize the New Query template), but you have to be careful about the opposite scenario - you highlight BEGIN TRAN and the query, but forget to run the COMMIT or ROLLBACK, then whistle as you leave the building for lunch, the weekend, or a 6-month sabbatical. – Aaron Bertrand May 22 '14 at 20:16
6

Create a second user account for production changes and revoke the access your account currently has. When you want to do stuff in production you can run ssms as the second user.

EDIT: This would only be beneficial in the case of domain logins. If you had two separate domain accounts you would be forced to have separate instances of SSMS for DEV and PROD. If you are not using domain accounts, this suggestion wouldn't really help you out much.

Also, if you are using separate domain accounts you can adjust your SSMS color settings per user, maybe having a bright red background for the account that connects to PROD.

Here is a good white paper that came to mind as well: http://download.microsoft.com/download/D/2/D/D2D931E9-B6B5-4E3B-B0AF-22C749F9BB7E/SQL_Server_Separation_of_Duties_White_Paper_Jul2011.docx

It discusses things like not giving your daily login account full SA access.

  • You mean that would somehow disable access to more than one server from a single SSMS instance? Or what have I missed? – Andriy M May 22 '14 at 10:50
  • We're already using different user accounts, I don't really see how that helps. – Stijn May 22 '14 at 11:02
  • 1
    I guess this would only really apply if you were using domain accounts. If that were the case or would force you to use a separate instance if SSMS for your DEV and PROD connections. Maybe I will wrote an SSMS add-in that helps with this scenario, maybe popping up a warning whenever you try to run code on your production connection... – Mark Wilkinson May 22 '14 at 12:16
  • Oof, sorry for all the typos in the comment. Early morning response via mobile phone... but you get the idea. :) – Mark Wilkinson May 22 '14 at 13:23
  • Yeah I did get the gist of your answer :) Perhaps you can work your comment into your answer? – Stijn May 22 '14 at 15:22
4

Have a look at my add-in: SSMSBoost. It has exactly what you need. I have improved SSMS Status-bar coloring feature so that it tracks your current database and changes it's color. Additionally you can add "important DB alert" floating tooltip:

enter image description here

Read more about this feature here: http://www.ssmsboost.com/Features/ssms-add-in-preferred-connections

2

In one of my jobs we developed a tool for this purpose.

If you wanted to run a statement on PROD, it forced you to write:

run_sql servername PROD <file_with_sqlstatements>.sql

It would write the results to a logfile and append the execution into a log in our management database. It came very handy for instance when we wanted to figure out who was the last person to change a certain table.

In SSMS, when you have registered servers, you can apply a certain color to a connection, so that for instance all PROD-connections have a red color at the bottom. But it's best to avoid using GUI-tools on a production server if possible.

  • I'm running SSMS on my machine only, great tip about the color for the connection string. – Stijn May 22 '14 at 11:09
  • 3
    @Stijn note that the built-in color feature doesn't work in all scenarios - depends on how you open the query window. One that is a lot more reliable (but not free on SSMS 2012+) is SSMS Tools Pack. Mladen just released a 2014-compatible version. – Aaron Bertrand May 22 '14 at 13:23
  • @Aaron the tool looks very interesting, I'll take a look at the trial version, thanks! – Stijn May 22 '14 at 13:37
  • 4
    Another, alternate colouring solution is in SQL Prompt which, although isn't free, is a pretty nifty piece of kit. This colours the tabs at the top, rather than just at the bottom which SSMS does. – Mark Sinkinson May 22 '14 at 14:15
1

Just two more tips, since I don't see anything similar here already:

  1. In my workflow I often work with multiple statements in a single window, and I am quite used to select-text-then-run flow. But I am always scared to accidentally press F5 when no text is selected and execute all statements in a window as a result. So every time I open a new window I start with typing whatever garbage SQL will refuse to compile. This effectively makes whole batch non-executable. (Warning! If you use multiple batches separated with GO then garbage is required per batch.)

  2. When doing data changes on production server (or whenever I need extreme care) - implicit transactions are very useful (you either SET IMPLICIT_TRANSACTIONS ON or change an option in SSMS so that the option is effective for every new window). This way every statement which is not in transaction - starts a new transaction. I only commit if I make sure twice that I did what I intended to.

0

Try using a seperate windows user, who is the only one that has the production database configured. Set the whole color theme of this user to red. With fast user switching this should be no problem.

Never use the production credentials in an account that is on a development machine. A short phonecall or coworker question and afterwards you are happily deleting everything for your new testrun...

Another option (same idea) is using a remote desktop or virtul machine with a different theme.

0

Another rather straightforward way of preventing execution of everything in the query window when F5 is pressed would be to surround all the content with /* and */ thus making the whole thing a comment.

You can still execute the statements you want by highlighting them and pressing F5 in the usual way, even though they are enclosed in a comment.

Note: if you choose this method, you will not be able to benefit from syntax highlighting or auto-complete, but if you don't use those features that much then it could be worthwhile sacrificing them to ensure it is 100% impossible to damage the database with an accidental F5.

Edit: you also won't be able to use /* */ anywhere within the query window, otherwise you will inadvertently uncomment subsequent code. Need to stick with the -- notation instead.

-1

In agreement with swasheck's comment on the original question, how about executing...

select @@servername + '\' + @@servicename

...before running any DML, or even looking at the status bar to see what instance you're connected to, or even running all DML in a transaction so that you can rollback if you realize you've made a mistake? A lot of great suggestions here, but basically, when it comes to potentially destructive DML, gimmicks will only get you so far. I always check, double-check, and check again. And if I'm dealing with a small amount of data, I might even SELECT INTO a new table prior to the DML, run my DML, do some comparisons to make sure things worked correctly, then drop the "backup" table. Work smarter, not harder.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.