Database Administration Document Templates

APPENDIX BB

DATABASE ADMINISTRATION DOCUMENT TEMPLATES

6100038971

PA State Police (PSP)

Database Administration

SP – Application name

Version 1.0

Date: mm/dd/ccyy

SECURITY WARNING

The information contained herein is proprietary to the Commonwealth of Pennsylvania and must not be disclosed to un-authorized personnel. The recipient of this document, by its retention and use, agrees to protect the information contained herein. Readers are advised that this document may be subject to the terms of a non-disclosure agreement.

DO NOT DISCLOSE ANY OF THIS INFORMATION WITHOUT OBTAINING PERMISSION FROM THE MANAGEMENT RESPONSIBLE FOR THIS DOCUMENT.

Version History

Click the show/hide icon on your tool bar to view examples of the required input.

Table of Contents

1Introduction

1.1Objectives

1.2Scope

1.3Audience

1.4Naming Conventions

1.5Contacts

1.6Availability

2Database Guide

2.1Database Administration

2.1.1Database Details

2.1.1.1Databases

2.1.1.2Roles

2.1.1.3User Accounts

2.1.1.4Administrative Tasks

2.1.2Automated Scheduled Processes

2.1.3Linked Servers

2.1.4Replication

2.1.5SQL Server Analysis Services

2.1.6SQL Server Integration Services

2.1.7SQL Server Reporting Services

2.1.8SQL Server Configuration

2.2Server Administrators

2.3Storage Requirements

1Introduction

1.1Objectives

This document describes the databaseadministration and maintenance procedures for the Application’s database. It is developed and maintained by the Database team.

1.2Scope

This document is focused on the applicationsdatabase details.

1.3Audience

PSP authorized personnel who will be administrating the databases.

1.4Naming Conventions

Provide a list of acronyms used in thisdocument.

• BIT – Bureau of Information Technology
• DHQ – Department Headquarters
• PSP – Pennsylvania State Police
• SP – State Police

1.5Contacts

List the Application contacts include the business owner and technical support group responsible for the application. Do not include developer names.

* (from an application perspective)

1.6Availability

List the Applications critical availability times.

2Database Guide

2.1Database Administration

Specify the database-server administration details below.

2.1.1Database Details

2.1.1.1Databases

List the databases, their purpose, server, projected annual growth, recovery model (see definition below) and encryption. Purpose & annual growth only required for production.

* Description of Recovery Types: Information Only – DO NOT CHANGE

Example:

2.1.1.2Roles

List the user-defined database roles and their permissions for each database. Do not list fixed database-level roles such as db_datareader. Create user-defined database roles (AppDev, DBMail) for the databases.

Example:

• sp_OAproc1
• sp_OAproc2
• sp_OAproc3

2.1.1.3User Accounts

List the user accounts used in the application and the roles to be assigned for these accounts in the Database server.

Example:

2.1.1.4Administrative Tasks

Specify any administrative tasks that need to be performed on the database server including scheduled jobs, tuning, replication, data retention, etc. Reminder: Click the show/hide icon on your tool bar to view examples of the required input.

Example:

Mail Requirements – modify parameter “mail” in SSIS package to

2.1.2Automated Scheduled Processes

List all scheduled jobs that need to run against the database server from another server, along with purpose and schedule. This section is only required for Production environment.

Example:

2.1.3Linked Servers

List any linked server connections needed for access to a database on a remote serverif applicable.

Example:

2.1.4Replication

List any replication performed on the databases.

Example:

2.1.5SQL Server Analysis Services

Provide the details of SQL Service Analysis Services requirements if applicable.

Example:

2.1.6SQL Server Integration Services

List and provide a brief description of each SQL Server Integration Services (SSIS) package if applicable.

Example:

2.1.7SQL Server Reporting Services

Specify the associated URL, Report Server, CWOPA Service Account, and Folder and will the reports use Report Builder or Data Subscription, if applicable. Database name and SQL Account are documented in the User Account section.

Example:

2.1.8SQL Server Configuration

Define any special SQL Server configuration that is not documented elsewhere is this document. Include items that go against existing practices.

SQL Server Version –

Example:

SQL Server Version –2008 R2 Enterprise Edition

SQL Server License – 2012 (note only if different than version)

Database XXApplicatonName is using Filestream

2.2Server Administrators

List the administrators for the database servers. At least one administrator must be identified and a CWOPA group will need to be created and documented below for this administrator.

Example:

2.3Storage Requirements

Specify the required Storage allocation for the servers.This section is only required for dedicated SQL Server instances.

Example: