1

We're setting up two clusters. One dev and one prod. The Production will host two SQL instances - a OLTP and a DW.

The development will host 4 OLTP non-production environments and at least one DW non-production. We're working on getting more DW non-prods and possibly more OLTP systems.

I'm considering a naming scheme like this, where PROJ would be 3 initials for the project name.

Dev Cluster

  • MSSQLPROJD1\D1 (DEV)
  • MSSQLPROJD2\D2 (TEST)
  • MSSQLPROJD3\D3 (QA)
  • MSSQLPROJD4\D4 (STAGE)
  • MSSQLPROJD5\D5 (DW)

Prd Cluster

  • MSSQLPROJP1\P1 (PRD)
  • MSSQLPROJP2\P2 (DW)

To the left of the slash, each name must be unique network wide. On each server, the instance name, to the right of the slash, must be unique.

Any thoughts on this? I'm trying to avoid having instance names drifting from reality as the project progresses - say we change what we call a certain environment or want to repurpose one. Then we can update a listing of the purposes for the instances and be done with it.

How has a scheme like this worked out for you? Maybe you do things another way in your shop - tell me about it.

Thanks.

rev2

Dev Cluster

  • SQLERPD1\D1 (DEV)
  • SQLERPD2\D2 (TEST)
  • SQLERPD3\D3 (QA)
  • SQLERPD4\D4 (STAGE)
  • SQLERPD10\D10 (DWDev)
  • SQLERPD11\D11 (DWTest)*

Prd Cluster

  • SQLERPP1\P1 (PRD)
  • SQLERPP10\P10 (DW)

*hoped for, but not spec'd as of now.

Sam
  • 1,990
  • 1
  • 14
  • 21

1 Answers1

3

There are a million different naming standards which people use. There isn't really a right or wrong one to use, as long as the standard that you use works for you in your environment over the long term. The worst thing that you can need to do is to change your naming convention after you have picked one.

Something to think about is how will this convention work if you add another Dev cluster, or another prod cluster. Will it continue to scale well?

Personally I like to use a naming convention like this. You can easily modify this as needed with site names, etc as needed.

Physical machines:

SQL01A
SQL01B

The Windows Cluster Name:

SQL01

The SQL virtual Names:

SQL01V01
SQL01V02\INST1
SQL01V02\INST2

This way you can quickly and easily see which physical machines a virtual name belongs two without having to log onto the server to look. And it scales nicely as you add another cluster that becomes what I've shown below. You can add more clusters easily, you can add more instances to any cluster without making things complex to figure out.

Physical machines:

SQL02A
SQL02B

The Windows Cluster Name:

SQL02

The SQL virtual Names:

SQL02V01
SQL02V02\INST1
SQL02V02\INST2
mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • If we add another cluster, I would think we would continue numbering. What scaling are you referring to, if you wouldn't mind elaborating? And thanks! – Sam Dec 22 '10 at 20:55
  • Suggestion - don't waste the first 5 characters calling it MSSQL. You already know it's a SQL server, right? - Edit, D'oh. That's your servername. I need a beer. – mfinni Dec 22 '10 at 21:36
  • Say you add another production cluster and put two instances on it. So that cluster has MSSQLPROJP3\P3 and MSSQLPROJP4\P4 on it. Then you want to add a third instance to the first cluster so it now has MSSQLPROJP5\P5 which could make it very hard to figure out which physical machines an instance sits on. I've added a sample naming convention that I've used a few places to my answer. – mrdenny Dec 22 '10 at 22:18
  • @mfinni - that's not necessarily the server name, it's the virtual sql name, which could be anything. We're going to shorten to SQLERP, instead of MSSQLPROJ. – Sam Dec 22 '10 at 22:22
  • @denny - yeah I see what you mean, but didn't think of having to find the location of an instance. We should maintain a steady base of users the next decade - we're in govt, so we're not expecting an explosion of use that a private service might get. Thanks for your sample though. – Sam Dec 22 '10 at 22:35