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

Date / Version / Modified By / Approved By / Section(s) / Comment
MM/DD/YYYY / 1.0 / Author Name / All / Initial Version
MM/DD/YYYY / 1.1 / Updater Name / Chapter/section / Minor update details

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.

Name / Organization / Role / Responsibility* / Phone / Email
Application Support
Business Owner
Vendor

* (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.

Development
Database Name / Instance / Recovery Model* / TDE
Test/Staging
Database Name / Instance / Recovery Model* / TDE
Production
Database Name / Purpose / Instance / Annual Growth / Recovery Model* / TDE

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

Recovery Model / Backup Type / Recoverability
Simple / Full / Database is recoverable to the previous night’s full backup
Full / Full + Diff + T-Log / Database is recoverable to the most recent Full, Diff or T-Log backup

Example:

Database Name / Purpose / Instance / Annual Growth / Recovery Type* / TDE
XXAppName / Customer information / SPCTCSID001 / 10MB / Simple / Y
XXAppName2 / Audit Log database / SPCTCSIP001 / 1GB / Full @10pm, T-Log every hour and Diff @ 10am / N

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.

Database Name / Role / Rights
All
Development only / AppDev / Show plan, View definition
MSDB / DBMail / Execute sp_send_dbmail

Example:

Database Name / Role / Rights
XXAppName / Role1 / EXEC on:
  • 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.

Development
Database Name / UserName / Role
Test/Staging
Database Name / UserName / Role
Production
Database Name / UserName / Role

Example:

Database Name / UserName / Role
XXAppName / XXApplicationName / datareader, datawriter, AppExe
XXAppName / XXApplicationNameAdmin / Datareader, datawriter, AppDev
XXAppName2 / CWOPA\Group1 / Role1

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.

Job Name / Schedule / Purpose / Type

Example:

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

Job Name / Schedule / Purpose / Type
XXAppName - Purge Old TransactionLogs / Daily @ 3:00am / Retention 7 days / SSIS
XXAppName - LoadData / Daily every 15 minutes / Daily load to table XXX / T-SQL
XXAppName2 - Purge Old Audit Records / Daily every 6 hours beginning @ 12:35am / Retention 2 years, disabled in staging / SP

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.

Job Name / Schedule / Purpose / Type

Example:

Job Name / Schedule / Purpose / Type
Purge Old TransactionLogs / Daily between 10and 11 PM / Retention 7 days to table XXX / SP
PreProcessor / By seconds / Checks DBName to see if processing and DBName2.tblname for special requests / T-SQL

2.1.3Linked Servers

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

Linked Server / DataSource / Catalog / Remote Login

Example:

Linked Server / DataSource / Catalog / Remote Login
LK_Server1 / XXX_DSN (xxx.xxx.xxx.xxx) / XXApplicationName / SQL account
SPCTCSIP001 / SPCTCSIP001\SPGLOBALLOOKUP / SPGlobalLookup / SPLCEITS

2.1.4Replication

List any replication performed on the databases.

Publication name:
Publisher / Publication Database
Subscriber / Subscription Database
Publication name:
Publisher / Publication Database
Subscriber / Subscription Database

Example:

Publication name: PSP_UCR_IBR_Transaction_IBR_Supplemental
Publisher / Publication Database
SPQDHQ1A61\UCR_Transaction / PSP_UCR_IBR_Transaction
Subscriber / Subscription Database
SPQDHQ1A61\UCR_ANALYTICAL / PSP_UCR_Analytical

2.1.5SQL Server Analysis Services

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

DataSource / Database Name / Purpose / User Name

Example:

DataSource / Database Name / Purpose / User Name
XXApplicationName / XXApplicationName / Reporting Database / UserName

2.1.6SQL Server Integration Services

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

Package Name / Description

Example:

Package Name / Description
Package1 / Backup database and secure ftp file to agency and email results to agency

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.

URL / Report Server / CWOPA Account / Report Builder / Folder

Example:

URL / Report Server / CWOPA Account / Report Builder / Folder
/ SPCTCISP038 / CWOPA\Account / N / Name

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.

Server / Environment / CWOPA Administrative Group Name / Purpose
Development
Test/Staging
Production / SP-PSP-DB-Admins / PSP DBA Account

Example:

Server / Environment / CWOPA Administrative Group Name / Purpose
SPCTCSID001
SPCTCSIS001
SPCTCSIP001 / Development
Test/Staging
Production / SP-PSP-DB-Admins / PSP DBA Account
SPCTCSID001
SPCTCSIS001
SPCTCSIP001 / Development
Test/Staging
Production / EN-DB-Admins / OA DBA Ops Account

2.3Storage Requirements

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

Server/Cluster Name / Drive / Purpose / Storage
1 / Data
2 / Log
3 / Temp DB
1 / Data
2 / Log
3 / Temp DB
1 / Data
2 / Log
3 / Temp DB

Example:

Server/Cluster Name / Drive / Purpose / Storage
Development DatabaseServer1 / 1 / Data / 100 GB
Development DatabaseServer1 / 2 / Log / 12 GB
Development DatabaseServer1 / 3 / Temp DB / 10 GB
Staging DatabaseServer1 / 1 / Data / 500 GB
Staging DatabaseServer1 / 2 / Log / 120 GB
Staging DatabaseServer1 / 3 / Temp DB / 10 GB
Production DatabaseServer1 / 1 / Data / 500 GB
Production DatabaseServer1 / 2 / Log / 120 GB
Production DatabaseServer1 / 3 / Temp DB / 10 GB