SQL Naming Standards

Previous DBA Role Accounts	SQL Server Accounts Next

Many sites choose to use standardised names for servers and other objects.

This page explains the naming standards that have been used within SQL FineBuild for SQL instances and accounts. You are free to choose your own naming standards (or no standard) at your site. However, it will help understand the SQL FineBuild documentation if you know what standards have been used within it.

The standards on this page are split into the following sections:

• Server Name Standards
• Folder Name Standards
• Volume Name Standards
• Group Name Standards
• Account Name Standards

Server Name Standards

SQL FineBuild uses server names such as PDGB01SQLS0100 and cluster names such as PDGB01SQLC01N01. The components of this standard are described below.

Server Name PDGB01SQLS0100

Code	Description
PD	Environment. Values: PD=Production, AT=Acceptance Test, DV=Development
GB	Country. ISO Country Code for where Data Centre is located
01	Instance number of Data Centre in the Country
SQL	Server Role. Values: SQL=SQL Server, DOM=Domain Controller, etc
S	Server Type. Values: S=Stand-alone, C=Clustered
0100	Instance number of server in given Role

Cluster Node PDGB01SQLC01N01

Code	Description
PD	Environment. Values: PD=Production, AT=Acceptance Test, DV=Development
GB	Country. ISO Country Code for where Data Centre is located
01	Instance number of Data Centre in the Country
SQL	Server Role. Values: SQL=SQL Server, DOM=Domain Controller, etc
C	Server Type. Values: S=Stand-alone, C=Clustered
01	Instance number of Cluster in given Role
N	Identifies name as a Cluster Node
01	Instance number of Node in given Cluster

Top

---

Folder Name Standards

The folder and path naming standards used by SQL Server allow for multiple instances of SQL Server components to be installed on the same server.

The Microsoft SQL Server naming standard is of the following form. The drive portion of the name resolves to the drive(s) that were used for the install. The path names are version-dependant and contain a somewhat deep hierarchy of folders.

Entity	Path
Program Library, SQL instance	drive:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
Program Library, AS instance	drive:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\

FineBuild uses the standard file paths for program libraries. For user database files, the opportunity has been taken to use a naming standard that is not version-dependant, and to simplify the path structure.

Entity	Path
Data files, default SQL instance	drive:\SQLFiles\MSSQL.MSSQLSERVER.Data
Log files, default SQL instance	drive:\SQLFiles\MSSQL.MSSQLSERVER.Log
Backup files, default SQL instance	drive:\SQLFiles\MSSQL.MSSQLSERVER.Backup
Full-Text files, default SQL instance	drive:\SQLFiles\MSSQL.MSSQLSERVER.FTData
Data files, AS instance	drive:\SQLFiles\MSAS.MSSQLSERVER.Data
Log files, AS instance	drive:\SQLFiles\MSAS.MSSQLSERVER.Log
Backup files, AS instance	drive:\SQLFiles\MSAS.MSSQLSERVER.Backup

For a named instance, the instance name replaces the MSSQLSERVER portion of the above paths.

It is recommended that a sub-folder is created for each user database to hold its data files (e.g. drive:\SQLFiles\MSSQL.MSSQLSERVER.Data\DBA_Data). This will particularly help in managing the files for a database if multiple files or filegroups are configured for that database.

All database log files can exist in the drive:\SQLFiles\MSSQL.MSSQLSERVER.Log folder, without the need for sub-folders. This is because is it not normal for more than one log file to be configured for a given database.

Top

---

Volume Name Standards

Examples of using SQL FineBuild in this Wiki use the following drive letter assignment. Some examples may use additional drives.

Drive Letter	Parameter	Description
C	/DrvSys:	System Drive (Read-only)
E	/DrvProg:	SQL Program Files
F	/DrvDataAS:	Analysis Services Data Files
G	/DrvLogAS:	Analysis Services Log Files
I	/DrvBackup:	SQL Database Backup Files
J	/DrvData:	SQL Database Data Files
K	/DrvLog:	SQL Database Log Files
T	/DrvTemp:	SQL tempdb Data Files

Top

---

Group Name Standards

Access rights for end users should be applied via Windows Groups.

Access rights for Service Accounts and Application Accounts is normally configured on an individual account basis. This is because these accounts typically each have a unique security profile that does not change for the life of the account.

Access rights for end users should be controlled via the use of Windows Groups. The use of Groups for end user security allows for a level of abstraction between the individual user and the object that requires to be secured. A given user can be a member of multiple Groups. The rights experienced by a specific user is the sum of all permissions granted to all of the groups to which they belong.

The use of Groups provides a means to easily apply the same security model to a number of users who have the same access requirements. It also allows security to be easily removed from a given user when they change roles.

It is important to define a specific purpose for each Windows Group, and to put all users who need the defined security model into the specified Group. It is also important to avoid creating Groups that effectively duplicate the purpose of an existing group. If duplication is not controlled, experience has shown that after only a few months there are so many Groups it becomes very difficult to have any consistency in the security within an organisation, A specific set of people should be tasked with the creation and management of Windows Groups, so that the Windows Group structure remains fit for its purpose.

SQL FineBuild uses the standard described below for Windows Group names.

Group Name GBGGDBAS01

The GBGGDBAS01 group is used within FineBuild to hold accounts that require SQL Server sysadmin rights.

Code	Description
GB	Country. ISO Country Code for where users belonging to the Group are based
GG	Group Type. Values: GG=Global Group, LG=Local Group, SG=Security Group
DBA	Group Function. Any 3-letter code that can describe a set of users
S01	Suffix. Any convenient value

Application Groups

Each application that needs its own security should have its own set of groups. For example the application HelpDesk could be assigned the groups GBGGHPD0001 to GBGGHPD0099. It can be helpful to use a standard set of suffixes for the groups in each application to help staff recognise why a particular Access Right has been granted.

Some applications may require the use of specified SQL Server accounts, and these can be used to supplement the security provided via Windows Groups if required.

Within SQL Server, access to items within a database, such as Stored Procedures and Functions, should be provided via a Database Role. This allows a level of abstraction between the Windows security entity and the database objects, and allows multiple Groups to be assigned the same rights within a database by assigning all to the same Database Role.

Code	Description
GB	Country. ISO Country Code for where users belonging to the Group are based
GG	Group Type. Values: GG=Global Group, LG=Local Group, SG=Security Group
HPD	Group Role. The code HPD has been assigned to the HelpDesk application
0001	Suffix. Any convenient value

Standard Suffix values could take the form of:

Suffix	Description
0001	IT application support group
0002	Vendor support group
0003	Application administrators group
0004	Application normal user group

Top

---

Account Name Standards

It is very common to have separate Account Name standards for end user accounts compared to those used as Service or Application accounts.

End user accounts often take the form of the person's name, such as a forename.surname standard.

Service Accounts and Application Accounts often have a structured name, such as described below.

Service Account for SQL Server

Code	Description
SERV	Account Type. Values: SERV=Service Account, APP=Application Account
GB	Country. ISO Country Code for where Data Centre is located
SQL	Account Role. SQL has been assigned for SQL Server service accounts
0001	Suffix. Each SQL Server service account is assigned a unique sequence

Copyright FineBuild Team © 2014 - 2020. License and Acknowledgements

Previous DBA Role Accounts	Top	SQL Server Accounts Next