This project has moved. For the latest updates, please go here.

Previous Software Install Account SQL Server Proxy Accounts Next

SQL Naming Standards

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
Drive Letter 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

Drive Letter 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 © 2014 Edward Vassie. License and Acknowledgements
Previous Software Install Account Top SQL Server Proxy Accounts Next

Last edited Oct 23, 2014 at 11:51 AM by EdVassie, version 3