SQL Server 2008 Color-Coding Connections

Say that three times fast!

The SQL 2008 client has a cool, yet obscure, feature that enables you to have quick visual feedback on what server you are connected to.  This is a long overdue addition for most of us who manage multiple servers and typically have many windows open connected to various servers.  For years, I have perfected the 2 second hover, where I instinctively hover my mouse over the execute button (or my finger over F5) and make sure I am connected to the intended server before unleashing that mass update/delete query.

To do this, go into your registered servers (Ctrl+Alt+G) and right-click a server and choose properties from the menu.  Select the “Connection Properties” tab and at the bottom you will see the “Use Custom Color” checkbox.

Once you define this, connect to that registered server.  Create a new query window, and voila!  You have a nice color-coded status bar at the bottom indicating easily what you are connected to.  It should look something like below:

If you have a small number of servers you can have a different color for each server.  If you have many you will probably want to color-code them by groups like:  developer machines, development servers, stage/test servers, production servers.  This just might save you from accidentally executing that mass update query you were testing against production!

Alas, nothing is perfect though.  I have found quite an annoyance with the implementation of this.  This works great if you create a new query window, but in other ways … not so much.  Below are the gotchas I have noticed:

• It does not work when editing objects!  So if you right-click a stored proc and choose “Modify”, you would think that the window would be color-coded to the server you are connected to in Object Explorer but that functionality is missing.  I am shocked that this doesn’t work.
• If you are opening files, the functionality is based on the active query window in SSMS.  That means double-clicking or drag-n-dropping a file will use the properties of the active window, if it is color-coded green then the file you opened will use the same connection and be color-coded green as well.
• If you use the right-click “Connection->Change Connection” functionality in the query window, it is even more retarded.  It changes the connection but keeps the previous connection’s color!

This is a very good idea implemented poorly.  If Microsoft can iron this out to where color-codes are consistently applied, this feature could prevent accidental damage all over the world … a good first step towards world peace!

How soon can I have it?

I wanted to share a pearl of wisdom, especially with the younger guys/girls in the beginning of their IT career. Executives are notorious for asking things like “When can I have it?” or “How long will it take to implement?”, especially when presented with a good idea. What may seem like a casual question, when answered can become something far more insidious. It is somewhat of an art to avoid answering this question and potentially leaving yourself in a bad situation, yet leaving them satisfied with your response.

• Choose the right time to present:  I have always been a creative guy, and come up with a lot of ideas on how to do things better or faster.  Many times I have made the mistake of discussing them in their early phases with people who not only like the idea, but demand an implementation timeline.  Be very careful about presenting ideas that are in their “incubation” phase.  If you do decide to present them, make sure you preface the conversation with the fact that it is just a concept at this point and you have not given any time to think about development effort or time lines.
• Understand the audience: Most executives are not purposely trying to put you on the spot or coerce you into an unrealistic time line.  In the upper levels of management, what matters most is what gets done by one’s subordinates, not what one can do themselves.  So your concept is one more thing that will make them look good.  Underscoring this is typically a huge lack of understanding about the technical complexity involved in IT projects.  Let’s face it, technology changes so fast that even if one comes from a technical background, most are still virtually clueless when discussing the technology-du-jour.  That mainframe experience doesn’t mean a lot when talking about N-tier applications with multiple data sources and web services glueing everything together.
• Know when to say no:  When you present something, you should anticipate these types of questions being asked.  You either need to have given thought to a valid estimate, or be prepared to explain why you cannot give an estimate at this time.  Don’t go in unprepared, and do not be cavalier and venture a guess!  If you do not have an estimate, you have to tactfully explain that without further analysis anything you say would be a wild guess and you would have better luck playing pick-up-sticks with your butt cheeks than actually hitting the deadline.  Properly presented, most executives will appreciate your candor and respect your opinion.
• Fight for time to do proper analysis:  Sometimes you will have no choice but to present an idea that hasn’t evolved to the point of doing real analysis.  This can be due to a variety of reasons:  opportune timing, getting funding on annual budget, annual project planning roadmaps, etc.  In these situations, the burden is on you to explain (without the mind-numbing technical detail) that there is no way in hell you can even proffer a guess-timate without doing more analysis.  If possible, point to other over-time or over-budget projects in recent history and explain that they occurred because of insufficient/improper analysis.

In summary, it is often tough to avoid giving an ad-hoc estimate.  The best idea is don’t put yourself in a position where you are forced to do so.  If, however, you are in this position, do your best not to give an off-the-cuff estimate as this could turn into a hard deadline as soon as the meeting is over!  No lie, I have had it happen where 30 minutes later a congregation approaches my desk to discuss my new deliverable!  Do not let yourself get cornered into an unrealistic deadline.  Over time, your executives and peers will value your discretion and appreciate the fact that you are not going to guess.  Delivering reliably and consistently is far more valuable in the long run.

Orphaned Users

Eric Johnson wrote up a great article about orphaned users.  Any DBA has to deal with this when refreshing databases from one environment to another (e.g. prod to stage, or prod to dev).  If you have a large number of users to repair, the built-in proc sucks because you are doing them one at a time.

Over 6 years ago I wrote this SQL to automate the process because like any good (meaning lazy) developer, I don’t like doing mundane things repeatedly.  I also did this because, like Eric, I don’t want a script auto-magically creating logins for me.  So this SQL simply loops over the orphaned users and tries to repair them if the matching login exists.  If it doesn’t it will generate a script that you can execute to create the missing login(s).

NOTE: This only works if the database users match the SQL logins, which was the case when I wrote it.

TIPS:

1. Set your results to text instead of grid when using this.
2. This executes in whatever database you are connected to.

declare @ExecString nvarchar(255),
        @Name nvarchar(50),
        @Count int,
        @FixString varchar(8000),
        @CurrentDB varchar(50),
        @OrphanedCount int

set nocount on

set @Count = 0

set @CurrentDB = DB_NAME()

select @OrphanedCount = count(*)
  from sysusers
 where issqluser = 1
   and (sid is not null and sid <> 0x0)
   and suser_sname(sid) is null

if @OrphanedCount > 0
   begin
      declare CURS cursor for
       select name
         from sysusers
        where issqluser = 1
          and (sid is not null and sid <> 0x0)
          and suser_sname(sid) is null
        order by name
      open CURS

      fetch next from CURS into @Name
      select ' ' as 'Attempting Repairs'

      while @@FETCH_STATUS = 0
      begin
         print 'Attempting to repair "' + @Name + '"'
         if exists(select * from master..syslogins where name = @Name)
            begin
               set @ExecString = N'exec sp_change_users_login ''update_one'',''' + @Name + N''',''' + @Name + N''''
               exec sp_executesql @ExecString
               print '   "' + @Name + '" successfully repaired...'
            end
         else
            begin
               print '   "' + @Name + '" does not have a matching entry in syslogins...'
               set @FixString = isnull(@FixString,'') + 'exec sp_addlogin ''' + @Name + ''', ''{password}'', ''' +
                                @CurrentDB + '''' + char(13) + char(10) + 'GO' + char(13) + char(10)
               set @Count = @Count + 1
            end

         fetch next from curs into @Name
      end
      print ' '

      close CURS
      deallocate CURS

      if @Count > 0
         begin
            print ' '
            select convert(varchar(5),@Count) +
                   ' user(s) could not be repaired because they do not have a matching entry in syslogins' as Results
            print ' '
            select @FixString as 'Script to Fix Users (copy/paste/edit to add users ... then execute this script again!)'
         end
      else
         begin
            print ' '
            select 'Successfully repaired all users' as Results
         end
   end
else
   begin
      print ' '
      select 'No orphaned users exist in database "' + @CurrentDB + '".' as Results
   end

set nocount off

GO

I wrote this (hopefully obvious to you based on it being 6 years ago) against SQL Server 2000.  I am posting it to help out, but I haven’t used it much against SQL 2005/2008.  I did a few tests and it still seems to work as expected.

Stay tuned, I will write an enhanced version of this script in the next week using a more contemporary style.

Automation vs Craftsmanship

One thing I have always admired is craftsmanship, the expertise developed only through months or years of performing a task.  Whether it is basket-weaving, making a samurai sword, or building a one-off automobile; I can appreciate the skill required whether or not I can do it myself.

So I think automation in general has an inverse relationship with craftsmanship.  That is, the more technology/automation progresses, the less craftsmanship exists.  Now nobody needs to actually understand the intracacies of anything, “I just push button A on the machine and out comes the product!”

This theory was bolstered last night night when I made a trip to Lowe’s to get copies of a key made.  I sauntered up to the counter and presented the worker what I thought was a rather ordinary key, and asked for two copies to be made.  I watched with dismay as he leafed through the plethora of keys, clearly not having a good idea of where to start.  Finally, after clearly making no progress, he started using a rotating implement with key holes in it to help.  Trying to insert the key in hole after hole, this tool’s purpose is to magically reveal the right blank to use.

After about 20 attempts, BINGO!  Blank #89 was the command from the plastic oracle.  I watch as he compares the blank to the original, and then inserts it into the grinding machine and pushes button A.  About a minute later, the new key is ready.  He compares it and decides it is of insufficient quality and throws it in the trash.  I thought “Wow, this guy might actually know a little something about the art of making keys.”  He gets another #89 blank and proceeds to make another key.  After comparing the second key, he states that it will not work.  He shows me the original and the new key, and I see the problem.  The #89 blank was a full 1/4″ shorter than the original!  Two blanks wasted that clearly had no chance of working…

Dismayed, he proclaimed that he couldn’t help me.  I pressed him to continue, as the key was not some skeleton key to a medieval castle, just a normal key.  He then pulled a book from under the counter and found what appeared to be a great match, blank #95.  He got a blank and compared it to the original, and his discerning eye decided it was a good match.  At least I could tell the length was a perfect match!  He ground (or the machine did, I should say) me two keys and I was on my way.

Fast forward to the next morning, when I have the opportunity to try the new key for the first time.  I try inserting the new key and it won’t even START to go in.  I then compare it to the original and can easily see that the blank chosen was not even close to the right one.  The length and basic grooves are the same, but the beveled surfaces are opposite!

See, I would much rather go in and watch a crotchety old man take out a carefully selected blank, and hand-file me a key that would work without question.  It might take 30 minutes or an hour, but I could leave knowing that the key would work without question.  Also, during that time I would actually come to appreciate the skill it takes to make a key.  The guy who helped me at Lowe’s knew probably less than I about the art of key-making, all he knew is what equipment to use to guide his decision making.  No craftsmanship…

Welcome!

This blog is where I will post longer articles/rants.  For less cerebral postings, please visit my tumblog!