I define DBA operation productivity as the total positive values brought to the work environment by a DBA in a unit of time.
How to improve productivity is really a big topic, but every big task can /should start from the small things. So here I’d like to discuss a few things probably commonly-seen in most DBA work environments.
First is SQL Server instance naming convention: This is an “emotional” topic because most of the time it is hard to agree upon a “yard stick” with which to judge the pros and cons of a naming convention.
In my mind, naming convention should serve one and only one purpose: help improving operation productivity.
Before we talk about the relationship of naming convention and productivity, we need to look at where we normally use the the sql server instance names. Other than the commonly-known purpose (i.e. identity of the sql instance), to me, there are two places where we usually do not care enough and yet it costs us something when “bad” naming convention exists.
1. Team conversation / discussion: we frequently have to discuss about the sql system health and also various work on the system. At the time, when I have to pronounce those un-pronounceable and awkward names, I really feel the pain and I have to say that these awkward names interrupt the communication fluidity . In an urgent time, such as when a server is down or has a big problem, these unnecessary and time-consuming interruptions during my talk to various technical managers / stakeholders really drive me crazy, and I cannot help thinking why we should have these weird names in the first place. Just think of the following:
Me: manager, we have a problem on a few production sql server boxes,
Manager: which ones?
Me: DC03SQLCLU-02\BIAPP-01 (explanation: DataCenter 03 SQL Cluster 02,Business Intelligence Application 01) and DC01SQLCLU-01\BIRpt-02
Manager: What is the problem?
Me: Well, you know, DC03SQLCLU-02\BIAPP-01 is the publisher while the DC01SQLCLU-01\BIRpt-02 is the subscriber, but at this time, the replication latency is very high.
Manager: What is the distributor?
Just imagine if you have 10 min talk with all these “strange” names mentioned 20 times, I guarantee you will feel your tongue is so tired and needs a good rest. (The worst is when I have names ended like M1, N1, I have to say Mary 1, Nancy 1 to make myself understood)
My experience is that you will not appreciate a good naming convention until you lose them and are forced to use a mediocre one.
2. DBA automation work: Just think of this scenario, if you want to do the auto system inventory check (inventory can include almost everything, OS, SQL version, Disk, memory etc). With the awkward naming conventions, it will be a difficult to write and maintain an inventory discovery system (assuming you do not have a good reference list of the existing instances which is common in QA / Dev areas). If on the other hand (for example only), we name the server like S1 to S100, we can easily create a loop in Powershell script to (1) detect whether the server is online/available, (2) if available, collect the needed data. I do not even need a list of the existing sql instances and I can still do the work.
The best naming convention I ever met is something like this:
Snnn: S = SQL Serer, nnn=001 to 999, and with a business rule
1 to 100 is for production, 200 to 299 is for QA, 300 to 399 is for development, and so on so forth.
To me this is simple yet extremely beautiful. Any other info, like the server usage / purpose should be embedded in an instance level extended property, not in the instance name.
In terms of productivity improvement, I think little things count a lot.