February 2, 2013

DBA: Create A SQL Server Database Request Form For Auditing, Change Tracking And Security

Change tracking, resource allocation and security auditing are three very important issues for any IT administrator, particularly DBAs (database administrators). One item that can easily be overlooked is at the point of database creation. Why is the database needed? Who is requesting it? Will the database be used for production or testing/development? What are the backup requirements? — These are just a few questions that could be asked before you proceed with database creation.

Microsoft SQL Server 2012Recently I was assigned the task of creating a SQL server audit procedure for a database that contains financial data. One of the first items identified was lack of control over how databases are created. Because of this deficiency, we have SQL servers with dozens of databases and no means of tracking who owns what. It was decided that a simple starting point would be a database request form that is used for all future database requests. This form can be as simple as a Word document or Excel spreadsheet or it could be integrated into an existing ticketing system. Whatever shape this request form takes is irrelevant until you define the key information you want to gather before each database creation. That brings us to the questions I believe need to be answered.

In the example below, I’ve separated the database request form fields into two sections. The first section is for the initial requester. This section would typically be filled out by the application owner (or whoever is requesting the database). The second section is for the DBA to back-fill with additional technical details. Depending on the system you use to create your database request form you can choose to make certain fields required fields, although these required fields would likely be limited to the initial request section.

Obviously your mileage may vary. Your company could have different needs, but the below form fields should be a good starting point.

 

DATABASE REQUEST FORM

Initial Request (required fields to submit form)

Requestor:

Requestor Email:

Requestor Phone:

Sell Art Online

Business Owner:

Owner Email:

Owner Phone:

Application Name:

Application Version:

Use (Production, Test, Development):

Description of database purpose:

Effective date (need to be live by):

Permanent or Temporary (include time-frame):

 

Additional Technical Details (back-filled by DBA)

SQL server name:

Database name:

Service account name (SQL login):

Service account level of access required (db owner?):

Users/groups who need SQL logins (via Windows AD) to this database (and level of access):

Type of database (Reporting, Warehouse, etc.):

Estimated user count:

Mainly “read” operations, “write” operations or “both”?:

Initial Disk Space Required:

Expected database growth per year:

Backup and availability requirements (to determine backup/maintenance windows):

Business continuity. How long can this database be offline in the event of an outage? (2 hrs, 4 hrs, 8 hrs, 1 day, 2 days, 1 week, etc.):

7-day free trial

Please share your thoughts