Introduction .us



9525-22860000PA State Police (PSP)Database AdministrationSP – Application nameVersion 1.0Date: mm/dd/ccyySECURITY WARNINGThe 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 DateVersionModified By / Approved BySection(s)CommentMM/DD/YYYY1.0Author NameAllInitial VersionMM/DD/YYYY1.1Updater NameChapter/sectionMinor update detailsClick the show/hide icon on your tool bar to view examples of the required input.Table of Contents TOC \o "1-4" \h \z \u 1Introduction PAGEREF _Toc383169121 \h 41.1Objectives PAGEREF _Toc383169122 \h 41.2Scope PAGEREF _Toc383169123 \h 41.3Audience PAGEREF _Toc383169124 \h 41.4Naming Conventions PAGEREF _Toc383169125 \h 41.5Contacts PAGEREF _Toc383169126 \h 41.6Availability PAGEREF _Toc383169127 \h 42Database Guide PAGEREF _Toc383169128 \h 52.1Database Administration PAGEREF _Toc383169129 \h 52.1.1Database Details PAGEREF _Toc383169130 \h 52.1.1.1Databases PAGEREF _Toc383169131 \h 52.1.1.2Roles PAGEREF _Toc383169132 \h 52.1.1.3User Accounts PAGEREF _Toc383169133 \h 62.1.1.4Administrative Tasks PAGEREF _Toc383169134 \h 62.1.2Automated Scheduled Processes PAGEREF _Toc383169135 \h 72.1.3Linked Servers PAGEREF _Toc383169136 \h 72.1.4Replication PAGEREF _Toc383169137 \h 72.1.5SQL Server Analysis Services PAGEREF _Toc383169138 \h 82.1.6SQL Server Integration Services PAGEREF _Toc383169139 \h 82.1.7SQL Server Reporting Services PAGEREF _Toc383169140 \h 82.1.8SQL Server Configuration PAGEREF _Toc383169141 \h 82.2Server Administrators PAGEREF _Toc383169142 \h 82.3Storage Requirements PAGEREF _Toc383169143 \h 9IntroductionObjectivesThis document describes the database administration and maintenance procedures for the Application’s database. It is developed and maintained by the Database team.ScopeThis document is focused on the applications database details. AudiencePSP authorized personnel who will be administrating the databases.Naming ConventionsProvide a list of acronyms used in this document. BIT – Bureau of Information TechnologyDHQ – Department HeadquartersPSP – Pennsylvania State PoliceSP – State PoliceContactsList the Application contacts include the business owner and technical support group responsible for the application. Do not include developer names.NameOrganizationRole / Responsibility*PhoneEmailApplication SupportBusiness OwnerVendor* (from an application perspective)AvailabilityList the Applications critical availability times. Database GuideDatabase AdministrationSpecify the database-server administration details below. Database DetailsDatabasesList the databases, their purpose, server, projected annual growth, recovery model (see definition below) and encryption. Purpose & annual growth only required for production.DevelopmentDatabase NameInstanceRecovery Model*TDETest/StagingDatabase NameInstanceRecovery Model*TDEProductionDatabase NamePurposeInstanceAnnual GrowthRecovery Model*TDE* Description of Recovery Types: Information Only – DO NOT CHANGERecovery ModelBackup TypeRecoverabilitySimple FullDatabase is recoverable to the previous night’s full backupFullFull + Diff + T-Log Database is recoverable to the most recent Full, Diff or T-Log backupExample:Database NamePurposeInstanceAnnual GrowthRecovery Type*TDEXXAppNameCustomer informationSPCTCSID00110MBSimpleYXXAppName2Audit Log databaseSPCTCSIP0011GBFull @10pm, T-Log every hour and Diff @ 10amNRoles 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 NameRoleRightsAllDevelopment onlyAppDevShow plan, View definitionMSDBDBMailExecute sp_send_dbmailExample:Database NameRoleRightsXXAppNameRole1EXEC on:sp_OAproc1sp_OAproc2sp_OAproc3User Accounts List the user accounts used in the application and the roles to be assigned for these accounts in the Database server.Development Database NameUserNameRoleTest/Staging Database NameUserNameRoleProductionDatabase NameUserNameRoleExample:Database NameUserNameRoleXXAppNameXXApplicationNamedatareader, datawriter, AppExeXXAppNameXXApplicationNameAdminDatareader, datawriter, AppDevXXAppName2CWOPA\Group1Role1Administrative 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 NameSchedulePurposeTypeExample:Mail Requirements – modify parameter “mail” in SSIS package to ra-pspdatabaseadmin@Job NameSchedulePurposeTypeXXAppName - Purge Old TransactionLogsDaily @ 3:00amRetention 7 daysSSISXXAppName - LoadDataDaily every 15 minutesDaily load to table XXXT-SQLXXAppName2 - Purge Old Audit RecordsDaily every 6 hours beginning @ 12:35amRetention 2 years, disabled in stagingSPAutomated Scheduled ProcessesList 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 NameSchedulePurposeTypeExample:Job NameSchedulePurposeTypePurge Old TransactionLogsDaily between 10and 11 PMRetention 7 days to table XXXSPPreProcessorBy secondsChecks DBName to see if processing and DBName2.tblname for special requestsT-SQLLinked ServersList any linked server connections needed for access to a database on a remote server if applicable.Linked ServerData SourceCatalogRemote LoginExample:Linked ServerData SourceCatalogRemote LoginLK_Server1XXX_DSN (xxx.xxx.xxx.xxx)XXApplicationNameSQL accountSPCTCSIP001SPCTCSIP001\SPGLOBALLOOKUPSPGlobalLookupSPLCEITSReplicationList any replication performed on the databases.Publication name: PublisherPublication DatabaseSubscriberSubscription DatabasePublication name: PublisherPublication DatabaseSubscriberSubscription DatabaseExample:Publication name: PSP_UCR_IBR_Transaction_IBR_SupplementalPublisherPublication DatabaseSPQDHQ1A61\UCR_TransactionPSP_UCR_IBR_TransactionSubscriberSubscription DatabaseSPQDHQ1A61\UCR_ANALYTICALPSP_UCR_AnalyticalSQL Server Analysis ServicesProvide the details of SQL Service Analysis Services requirements if applicable. DataSourceDatabase NamePurposeUser NameExample:DataSourceDatabase NamePurposeUser NameXXApplicationNameXXApplicationNameReporting DatabaseUserNameSQL Server Integration ServicesList and provide a brief description of each SQL Server Integration Services (SSIS) package if applicable.Package NameDescriptionExample: Package NameDescriptionPackage1Backup database and secure ftp file to agency and email results to agencySQL Server Reporting ServicesSpecify 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.URLReport ServerCWOPA Account Report BuilderFolderExample: URLReport ServerCWOPA AccountReport BuilderFolderapp.state.pa.usSPCTCISP038CWOPA\AccountNNameSQL Server ConfigurationDefine 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 EditionSQL Server License – 2012 (note only if different than version)Database XXApplicatonName is using FilestreamServer AdministratorsList 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. ServerEnvironmentCWOPA Administrative Group NamePurposeDevelopmentTest/StagingProductionSP-PSP-DB-AdminsPSP DBA AccountExample:ServerEnvironmentCWOPA Administrative Group Name PurposeSPCTCSID001SPCTCSIS001SPCTCSIP001DevelopmentTest/StagingProductionSP-PSP-DB-AdminsPSP DBA AccountSPCTCSID001SPCTCSIS001SPCTCSIP001DevelopmentTest/StagingProductionEN-DB-AdminsOA DBA Ops AccountStorage RequirementsSpecify the required Storage allocation for the servers. This section is only required for dedicated SQL Server instances. Server/Cluster NameDrivePurposeStorage1Data2Log3Temp DB1Data2Log3Temp DB1Data2Log3Temp DBExample: Server/Cluster NameDrivePurposeStorageDevelopment DatabaseServer11Data100 GBDevelopment DatabaseServer12Log12 GBDevelopment DatabaseServer13Temp DB10 GBStaging DatabaseServer11Data500 GBStaging DatabaseServer12Log120 GBStaging DatabaseServer13Temp DB10 GBProduction DatabaseServer11Data500 GBProduction DatabaseServer12Log120 GBProduction DatabaseServer13Temp DB10 GB ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download