A Cool Trick to Help Prevent Ad-Hoc SQL Accidents

A few years ago, I posted a blog I called 5 Things I Will Not Do For My Client. It might be the post that got me the most feedback of anything I have ever written.

Number 5 on that list was the statement that “I do not open both the test environment and the production environment at the same time.” That statement holds true today and is a company policy here at Conexus SG. When I wrote that I felt it applied to any situation where we have the ERP environments exposed, but I was especially thinking about the SQL Server Management Studio (SSMS.)

The picture below shows the situation I suggest we always avoid – A test database and the production database both connected in SSMS. This increases the odds that I might run an update query against the wrong database. What a great way to ruin a holiday weekend!

The Problem

Assuming I have convinced everyone to practice the “one open at a time” habit, there is still one little gotcha. The open query tabs in SSMS remain connected to the database where they were opened, not to the database or table that is highlighted in the SSMS Object Explorer. This is not visually consistent with most Microsoft Windows programs.

In the next picture, the tool tip shows us the query tab is opened in the production database (SLDB) even though it has been closed in Object Explorer. The real danger is forgetting to close this tab while we open several new tabs connected to the test database (SQLSL).

The Solution

There is a feature built into SSMS that we can use to provide a visual cue to where the tab is connected. To try it out, initiate a connection to a database and click through the following path on the login dialog:

1. Click the Options button
2. Click on the “Connection Properties” tab
3. Check the “Use Custom Color” box
4. Click the “Select” Button

This color dialog can be used to color code the tabs that open when they connect to this server. The neat part is SSMS remembers this selection for the next time. I choose to make any production server red and any test server green to indicate the relative safety an update query can be run in this tab.

Here’s my production data connected tab:

And here is my test data connected tab:

And there you have it, an unobtrusive cue that will help keep track of where we are about to run a query!

There is one caveat – This only works between separate server instances. If the test database is located on the same instance, this trick is not available.

Be sure and let us know what you think about this tip or share your own.

Contact us at info@conexussg.com or 469-828-3274 to discuss how we can help you optimize your ERP.