John
I’ve been dealing with SQL Server databases for over 4 years now, but never minded the server names. I just think — ”Oh hey, this data is on server A and not server B, okay!”. Well, what about when you work for a bigger company that has multiple development environments and such? That single server “A” could turn into 4 different servers like “A-Test”, “A-QA”, “A-Stage”, “A-Prod”; and what if there were different versions of “A-Test” like “A-Test-DateAdvanced”? Wouldn’t it be awesome to have a naming convention for that?
There is one standard that I’ve grown accustomed to. It goes something like so;
SRI-PROJECTNAME
– S is for server (I think?)
– R is the region/environment such as T for Test
– I is the iteration of that server such as 2 for the second iteration of this server
– PROJECTNAME is self-explanatory.
So the example of server “A” would be the following; “ST1-PROJECTNAME”, “SQ1-PROJECTNAME”,”SS1-PROJECTNAME”, “SP1-PROJECTNAME”. By doing so, it makes it easy to follow what is going on with servers such as if the Test server gets upgraded. Then it would be “ST2-PROJECTNAME”. So fancy, right? I must admit though, when I first saw the sheer number databases with this naming convention, my eyes got a little blurry, but I’m sure you’d enjoy it if you gave it a chance.
In terms of server naming conventions, why not dig into the databases as well. In a nutshell, prefix the databases with the server’s development region and iteration. Say we have a database called Customers on the ST3-JMART server; It shall now be called TEST3_Customers, or whatever you’d like that determines the database location; T3_Customers, T3Customers, T3C (I don’t know whatever you’d like).
Now you might ask, “Well, what about four-part identifiers and such?”. I say good point. If you utilize that, there can’t be any ambiguity between databases on different servers. BUT, what if you thought you were on the testing server and you dropped tables, only to find out you were on the prod server? Well, if you tried to drop dbo.TEST3_Customers on the PROD server, you’d get a “Cannot drop the table ‘dbo.TEST3_Customers, because it does not exist or you do not have permission.” message. Boom.
Also, what I’ve also learned to love is the database project deploys through Visual Studio; That is a magical thing. The use of SQL variables allows you to do such efficient things like deploy the same code to different servers with the click of a button.
As a summary,
Server naming convention:
SRI-PROJECTNAME
– S — server
– R — environment like T for Test and P for Production
– I — iteration of server
– PROJECTNAME — Project name
Database naming convention:
RI_DBNAME (dependent on server)
– R — environment like T for Test and P for Production
– I — iteration of server, the database is on
– DBNAME — database name
(Originally posted on Medium)
