May 30, 2003 at 8:26 am
Please provide feedback on the following. Thanks!
SQL SERVER DATABASE AND PROGRAMMING STANDARDS,
DESIRED PRACTICES, OPERATING STANDARDS,
AND RELATED GUIDELINES
{MY ORG}/CIS SQL Server Standards
Purpose
This document contains the standards identified by the Department of Human Resources Center for Information Services ({MY ORG}/CIS) to be adhered to by Database Administrators (DBA) and other development team members, i.e. programmers and managers, as they apply to SQL Server database environments.
Database Creation
Databases created in SQL Server 7 and 2000 will follow these standards. If a standard is not addressed here and the DBA does not accept the default values suggested by the SQL Server or adhere to well known general best practices of SQL Server DBAs, it is the responsibility of the DBA to document the aberration in the Database Specifications Document.
The standard location for SQL Server files is as follows.
LOCATION OF DATABASE FILES AND FILEGROUPS (MDF AND NDF)
\\{SQL Server Name}\{Share Name}\SQL_DATA\MSSQL\Data
LOCATION OF LOG FILES (LDF)
\\{SQL Server Name}\{Share Name}\SQL_DATA\MSSQL\DBLogs
LOCATION OF BACKUP FILES
\\{SQL Server Name}\{Share Name}\SQL_DATA\MSSQL\BACKUP
Database Design
RULES OF NORMALIZATION
1. Adherence to the first three rules of normalization is expected. (Refer to The First to Third Normal Forms found at http://www.sqlserversite.com/content/articles/20020418print.asp.)
A. First Normal Form
For a table to be in First Normal Form (1NF) each row must be identified, all columns in the table must contain atomic values, and each field must be unique.
B. Second Normal Form
For a table to be in Second Normal Form (2NF), it must be already in 1NF and it contains no partial key functional dependencies. In other words, a table is said to be in 2NF if it is a 1NF table where all of its columns that are not part of the key are dependent upon the whole key – not just part of it.
C. Third Normal Form
A table is considered to be in Third Normal Form (3NF) if it is already in Second Normal Form and all columns that are not part of the primary key are dependent entirely on the primary key. In other words, every column in the table must be dependent upon "the key, the whole key and nothing but the key."
DATABASE OWNER, ‘DBO’
2. The database owner, ‘dbo’, will be assigned to the ‘sa’ SQL Server Login so that the user name of ‘dbo’ is associated with a non-user specific login id. The DBA will use their own login id, other than ‘sa’, to do their work within the database.
TABLE OWNER, ‘DBO’
3. Table owner will be ‘dbo’ by default. Applications and queries must specify ‘dbo’ as the owner of the table when making use of it; i.e. dbo.myTable.
Note: Any user login id that is a member of the system administration role creates tables with ‘dbo’ as the default owner. Any user login, not a member of system administration role, that is a member of the db_owner or db_ddladmin role creates tables with their user login id as the default owner; i.e. user199c.myTable. The fact that two different user roles behave differently illustrates the importance of specifying the owner of the table in order to avoid confusion and error.
A. Acceptable deviation from ‘dbo’ as owner of table is when a table is created and dropped by the user login id for temporary use purposes, such as reports.
APPLICATION SPECIFIC EDIT AND FORMATTING LOGIC
4. All application specific edit and formatting logic will be performed when the data is under the control of the application rather than when the data is under the control of SQL Server. (This standard was taken from the DB2 Database and Programming Policy document authored by Max Byrd, version 3/25/1998, and adopted here for use with SQL Server.)
Naming Conventions
1. Naming conventions generally adhere to camel notation implementing a combination of Hungarian and Lyzenski Naming Conventions and are presented within this document.
DATABASE
A. Database names are to be named like or related to the title of the project or business system for which the database provides its service. The Project Manager and the DBA decide upon and approve the database name. The name will adhere to camel notation with no spaces, acronyms are acceptable.
Examples of valid database names:
i) FoodStamps
ii) CHC
FILEGROUP
B. FileGroup is to be named in the following format
fgFileGroupName
i) fg ~ prefix for FileGroup
ii) FileGroupName ~ to be replaced by a meaningful name in camel notation, no spaces, as determined by the DBA
Using Filegroups makes it easier for the SQL Server Administrator to implement the backup plan for the database.
TABLE
C. Table is to be named in the following format
tblTableName
i) tbl ~ prefix for Table
ii) TableName ~ to be replaced by a meaningful name in camel notation, no spaces, as determined by the DBA
iii) dbo ~ the default qualifier for all tables
It is expected that queries and applications will specify the owner of the object when referenced; i.e. dbo.myTable. (See Table owner, ‘dbo’)
Example:
Select chrData, dtmStamp from dbo.myTable
LOOKUP TABLE AKA REFERENCE TABLE
D. LookUp Table is to be named in the following format
tlkpLookUpTableName
i) tlkp ~ prefix for LookUp Table
ii) LookUpTableName ~ to be replaced by a meaningful name in camel notation, no spaces, as determined by the DBA
CHECK CONSTRAINT
E. Check Constraint is to be named in the following format
CK_tblName
i) CK_ ~ prefix for Check Constraint
ii) tblName ~ to be replaced by a meaningful name in camel notation, no spaces, as determined by the DBA
PRIMARY KEY
F. Primary Key is to be named in the following format
PK_tblName
i) PK_ ~ prefix for Primary Key
ii) tblName ~ to be replaced by a meaningful name in camel notation, no spaces, as determined by the DBA
FOREIGN KEY
G. Foreign Key is to be named in the following format
FK_tblName1_tblName2
i) FK_ ~ prefix for Foreign Key
ii) tblName1_tblName2 ~ to be replaced by a meaningful name in camel notation, no spaces, as determined by the DBA
INDEX
H. Index is to be named in the following format
IX_FieldName
i) IX_ ~ prefix for Index
ii) FieldName ~ to be replaced by a meaningful name in camel notation, no spaces, as determined by the DBA
COLUMN
I. Column is to be named in the following format
prefixColumnName
i) prefix ~ prefix for Column indicating the data type or use
ii) ColumnName ~ to be replaced by a meaningful name in camel notation, no spaces, as determined by the DBA
PrefixSQL Server Data TypeDescription
binbinarybinary data type, length 0 to 8000 bytes.
chrcharcharacter data type, length 0 to 8000 bytes.
vbinvarbinaryVariable-length binary data type, length 0 to 8000 bytes.
vchrvarcharVariable-length character data type, length 0 to 8000 bytes.
binbinarybinary data type.
bitbitbit data type.
dtmdatetime8-byte datetime data type.
sdtmsmalldatetime4-byte smalldatetime data type.
decdecimaldecimal data type.
realreal4-byte real data type.
fpfloat8-byte float data type.
imgimageimage data type.
tinttinyint1-byte tinyint data type.
sintsmallint2-byte smallint data type.
lintint4-byte int data type.
smonsmallmoney4-byte smallmoney data type.
monmoney8-byte money data type.
nchrncharUnicode character data type.
ntxtntextUnicode text data type.
numnumericnumeric data type.
nvchrnvarcharUnicode variable-length character data type.
txttexttext data type.
ididentityAny autonumbering column of numerical data type
Data Dictionary
1. A data dictionary must be maintained and contain the following element information:
A. Name
B. Data Type
C. Length
D. Precision and Scale (if applicable)
E. Description – valid values
F. Default
G. Key to “Help” System (for element values)
H. Primary Key(s) and sequence
I. Cross Reference of Table(s) in which element is stored
Programming Standards
1. “ Select *” must not be used. Specifying all the required field names affords better performance.
2. “Reads” that are performed only to verify the existence of at least one row should either use an exact key or use the “count” option. (This standard was taken from the DB2 Database and Programming Policy document authored by Max Byrd, version 3/25/1998, and adopted here for use with SQL Server.)
2. It is expected that queries and applications will specify the owner of the object when referenced; i.e. dbo.myTable. (See Table owner, ‘dbo’)
Example:
Select chrData, dtmStamp from dbo.myTable
Backup
At a minimum, a database will be backed up on a weekly basis. The official backup plan is to be documented in the Database Specifications Documentation for its project.
Security
THE ‘SA’ LOGIN
The ‘sa’ login will have a password and will be managed by the Infrastructure group. As it is not any particular person’s login id and is not associated with any one particular person, it will not be used except in specific circumstances. For example, ownership of all tables within a database will be assigned to the login ‘sa’ so that backup and restore of the database will not encounter a need to resolve ownership issues.
‘DBO’ LOGINS
Logins belonging to and granted membership in the database owner role will be established for use by DBAs that uniquely identify the person associated with the login. The DBA is to use his or her unique login when doing DBA work in the system.
USER LOGINS
Users will generally be permitted ‘db_datareader’ and ‘db_datawriter’ permissions only. ‘db_ddladmin’ permission will be granted when users need to be able to drop and create tables. Granular permission settings will be granted when necessary and documented in the Database Specifications Document.
Database Specifications Documentation
The Database Specifications Documentation is a part of documents required by projects developed within {MY ORG} CISP.
It will include and address these elements at a minimum.
1. Purpose
Explain the purpose of the database and its use within the project(s).
2. Entity Relationship Diagram
3. Data Dictionary
See Data Dictionary.
4. Location and Names of Database Files
Locations and names of the database files and filegroups (mdf & ndf), log files (ldf), and backup files. See Database Creation.
5. Security
Describe in detail the security required for the project. See Security.
6. Backup
Describe in detail the Backup plan for backing up database files and filegroups.
June 2, 2003 at 8:00 am
This was removed by the editor as SPAM
June 10, 2003 at 9:31 am
Any feedback is appreciated... Thanks.
June 10, 2003 at 12:44 pm
Programming Standards
2. “Reads” that are performed only to verify the existence of at least one row should either use an exact key or use the “count” option. (This standard was taken from the DB2 Database and Programming Policy document authored by Max Byrd, version 3/25/1998, and adopted here for use with SQL Server.)
-- use exist() instead of count if possible
2. It is expected that queries and applications will specify the owner of the object when referenced; i.e. dbo.myTable. (See Table owner, ‘dbo’)
Example:
Select chrData, dtmStamp from dbo.myTable
-- The more you qualify a name the better chance to avoid a recompile of the execution plan
Example:
Select chrData, dtmStamp from database.dbo.myTable
Other than that it looks pretty good for a starting document.
Wes
July 9, 2003 at 10:20 am
On database creation: Installation of the sql instance should be scripted with setup.iss files for both the base instance and the latest (certified for production in your environment) service pack. The setup.iss file locations should be consistent with your DB creation guidelines. The backup structure could be a little more developed - i.e. under the backup root we have 3 subfolders - DB, TL and TLS. DB for full backups, TL for local log backups and TLS for log backups log shipped from a primary server to a standby.
Second, you may be as well to isolate the backup root from the rest of the instance file structure. This can be useful for putting backups on a separate logical disk volume so you can use SAN infrastructure (via Veritas Truecopy of similar) to move large (> 10G) backups between geographically separate datacentres when the WAN would be unable to handle the file copy. This is entirely dependant on your sites and infrastructure of course.
Also for development standards there is no mention of performance testing or volumentrics. A volumetrics document needs to be part of your mandatory DB specs documentation as does a DR Strategy (that's DR as in total site loss, not server loss).
Also on performance and maintainability froma developers POV. NO CODE-EMBEDDED SQL. All database operations to be via stored procedure calls only. All stored procedures to be run through Query Analyser's Index Wizard at the least. All SQL to be performance tested with loads related to aforementioned volumetrics. Oh yeah, no code-embedded connection strings (both unmaintainable and insecure - you can notepad the login and password out of the .exe's - try it!). Also beware connection cacheing either at application or MTS/.NET level - you need an option to clear all such caches for e.g. when you attempt to fail the system over to a standby database. The number of times the first attempt to fail over a leg fails not because of problems on the data tier but because the developers have not thought how to clear the connection cache (or in some cases how to change the connection string gracefully) is unreal.
OK, what chance a Production DBA Standards doc?
July 9, 2003 at 9:25 pm
quote:
TABLE OWNER, ‘DBO’3. Table owner will be ‘dbo’ by default. Applications and queries must specify ‘dbo’ as the owner of the table when making use of it; i.e. dbo.myTable.
We do a bit differently. DBO is the owner of the database. But there may be other "owners for tables" too.
Eg. All the operation realted information (accounts, sales, procument, stores etc.) will be in one database. Where Accounts may have their schema (Accounts.Tablename), Sales may have another schema, Procument may have another schema, stores may have another schema.
The common tables will be in dbo schema. (Like security related etc.) Based on the application the particular schema will be used.
Actually our applications login using these users(Accounts, sales, Procument etc.)
We allow cross schema table updation too. (When sales occurs, the sales application updates the accounts tables)
Application users won't know what the schema name, password etc. Application user password system is maintained seperately.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
July 10, 2003 at 5:24 am
k
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply