Taking Inventory

Its very important to know key bits of information about each database that we watch over.  Things like there purpose and RTO & RPO are obvious examples, but also things like maintenance window and data owner are vital to effective database services management.

SQL Server 2000 introduced the concept of a extended properties for SQL Server Objects (both below
and at the database level.)  This allows for custom name and value pairs to be attributed to
database objects.  There are many uses for these properties such as self documentation and control of maintenance (like index maintenance or backups).   However, our focus is on Database Level properties for the purpose of self describing.

My approach was to standardize the properties for describing the database into a policy covering both some mandatory and options properties.  By formulating them, there purpose and name was clear.  This formed a standard that I was able to roll-out and measure our compliance across all the databases.

Before I go through the properties I defined, it is important to note that I also encouraged developers to implement their own extended properties.

Mandatory Properties

These properties must be defined against all user databases within the production environment.

Authorisers

A semi comma separated list of email addresses authorized to make decisions on behalf of the database.
(such as authorizing changers or permissions.)  This is probably the most important property as it defines the data owners.  Note that I allow for more than one data owner for a database.

DatabaseType

Identifies the functional purpose of the database.  Valid values are: Internal Utility, Internal System, Customer
System.

Description

A description of the databases purpose and function.

NotificationList

A semi comma separated list of email addresses to be notified in the event of outages or system wide
configuration changers.

ProjectName

A descriptive and meaningful name of the project.

Optional Properties

These properties are considered optional.

ApplicationName

Name of the application.

ApplicationServers

A comma separated list of Application/Web Servers.

ApplicationURL

A comma separated list of associated URL’s.

BusinessOwner

The email address of the person(s) considered to be business owner and able to manage client
communication.

Clients

A list of clients utilizing the service.

MaintenanceWindowDays

Day of week restrictions for the maintenance window.  Values can be:
Sun,Mon,Tue,Wed,Thu,Fri,Sat,WD,WE,Any (comma separated for multiples)

MaintenanceWindowDuration

Duration of time allowed for the maintenance window. (formatted [d.]hh:mm)

MaintenanceWindowStart

Time of from which planned maintenance is acceptable. (formatted hh:mm:ss)

Platform

Used to signify a base platform (I.E. Liferay, DNN etc)

ProjectNumber

The internal project number assigned to the project.

ReviewDate

A date when the database should be reviewed for either archive or a new review date set. (formatted yyyymmdd)

RTO

The Recovery Time Objective for the database (formatted [d.]hh:mm:ss)

RPO

The Recovery Point Objective for the database (formatted [d.]hh:mm:ss)

SecurityClassification

The security classification of the database: Public, Internal, Confidential, Strictly Confidential

TechnicalLead

The email address of the person(s) considered to be technical lead and able to answer
 technical questions about the database/project.

Version

A version identifier (preferably using a dot notation such as 1.2 or 2.1.2)

Some self criticism

Email Addresses - The use of Email Addresses for specifying people can make it awkward to identify people.  However, the decision was made to allow automation notification of emails.

Offline Availability - By using extended properties, the information is only available when the database is online and accessible.  This can be a problem when your dealing with an outage and want to notify all the database notification lists.