SSMS: Help prevent the "oh ****" moment

Its funny how sometimes you think of something which would be great idea and then the solution falls into your lap. This very thing happened to me the other day after an "oh ****" moment when I executed a query on a production server rather than the test server. Unfortunately, there weren't any fail safes in place (eg restricted privileges) so the query ran successfully and promptly updated a stack of live data. Fortunately, the database in question was not being used and so there was no impact on users but this could so easily have not been the case.

I'm sure I haven't been the only one bitten by this, and it occurred to me that this is far too easy to do. When in fire-fighting mode it can often be the case that I'm flicking between windows with many connection windows open at one time and even the most diligent person can get caught out running a query on the wrong database. When its just a SELECT statement or you issue a query that is in the wrong database thats no big deal but if its a DML (or worse a DDL!!) statement then things can get pretty ugly quickly.

What would have been really helpful for me was an obvious indicator showing which server I was on - probably a different coloured query window (red for Prod please!!) for each database server. And then, on my daily trawl through my twitter timeline I came across this tweet:

RT @tsqlninja: [Blogged] Tools of the trade. My list of top sql server tools tsqlninja.wordpress.com featuring @redgate @MladenPrajdic...
— SQLServerCentral (@SQLServerCentrl) July 25, 2012

I won't go into details of all the cool tools that are mentioned in the post, but the SSMS Tools Pack has (amongst other things) functionality that replicates what I was hoping for. You can essentially configure a colour strip for a database server which is a simple visual aid to help you recognise when you in "the danger zone". 

Even better than this is the fact that you can configure a template for a New Query connection which I tweaked to try and remind the user to be aware of the colour. This means that every time I hit the New Query button the query opens with the text to remind the user to check the server with arrows pointing to the colour bar.

This obviously isn't a full proof solution and is no substitute for having more stringent measures in place to prevent issues such as issuing a delete statement on a production server in error. However, it is simple and effective and is another tool you can have in your armoury to help prevent downtime or dataloss. Every little helps!

Powershell: String concatenation with underscore confusion

In putting together a simple powershell script to download a file and store it on a filesystem, I came across some strangeness with the user of Underscore (_) when concatenation variables. Essentially, I wanted to create a filename based upon 2 powershell variables, separated by an underscore.

I wanted my filename to be: 

Richard_Brown.txt

And my powershell read something like:

$File = "C:\$FirstName_$LastName"

Interestingly, when run in my script the output of $File would be 

C:\Brown.txt

I was suspicious of the underscore and sure enough this was the causing the issue. I stumbled upon this thread on Stackoverflow which explains the issue as the underscore is a valid character in identifiers so its looking for a variable named $FirstName_.

There are 2 workarounds for this:

1) $File = "C:\$FirstName`_$LastName"

2) $File = "C:\${FirstName}_$LastName"

T-SQL Tuesday #32 - A Day In The Life

T-SQL Tuesday has been a little off my radar for the last few months (as has all blogging activity!), so I was delighted when Twitter reminded me that it was that time of the month. Even better, the topic chosen by Erin Stellato (Blog | Twitter) was a great one that I felt I could really engage with.

So here we go. For the record, my job title is Database Administrator.

The task as laid out in the invitation suggested to talk through your day on the 11th or 12th July which just so happened to be the day I resigned from my current role. Writing down a handover list was an interesting task in itself as it gave me the opportunity to put down in words what the key parts of my job were and what skills gap would be left. I have to say, I was surprised (and pleased) that the number of tasks on the list that required significant handover to another collegue were minimal as I have been very diligent in ensuring that I was not a Single Point of Failure in my organisation. As such, I have written plenty of documentation and made extensive use of Source Control and Task Tracking systems to ensure that even if people do not know what I've done, there is a paper trail to explain it.

What was apparent was the varying skills I employ in my day to day, many of which are not even related directly to SQL Server. Much of my day is spent in project meetings, planning or analysing roadmaps and although the applications typically have a database backend, its not the focus of my attention. I am fortunate to be able to dedicate some time each day to reading blogs and technical articles to help stay up to date although I also spend my fair share of time in firefighting mode. The traditional DBA element probably takes up less than 1 day a month partly due to the small size of the estate but also because I've been effective (backslap for myself here!) in automating many of the tasks.

Is my job really a Database Administrator role? I don't think so and in truth it wasn't from the outset but this hasn't been a bad thing. I enjoy the variety of being involved in the different aspects of SQL Server and feel it makes me a much more effective professional and I imagine that this is the case for many other fellow SQL Server pros. However, I've encouraged my company to not advertise the role under this job description as I believe it may attract candidates with unrealistic expectations.

As for me, I'm not sure what my next role has in store but I hope it affords me the time to participate in future T-SQL Tuesdays!!