Full Text
Policy Title: Database Archiving and Data Warehouse Policy ID: IT-15 Origination Date: August 7, 2015 Update Date: July 13, 2016 Legal Approval: Department Director Approval: Division Manager Approval: IT15-1 Policy It is the policy of Ogden City IT to manage and delete databases that are not needed within the City. IT15-2 Policy Purpose To assure that all databases have been approved before archiving or deleting IT15-3 Evidence of Compliance Data is archived and warehoused such that departments have access to data from retired applications for historical and other purposes. IT15-4 Policy Procedures 1. Database Archive - A retiring database will be disabled and taken to an offline status for up to three years before deletion. Sign off is required by Attorney, City Recorder, IT Manager, and Director(s) over Division/Department(s) that owned the database agreeing to the Archiving Policy before the database is deleted. Signature is required for each database Database Archive Policy Agreement: Database to be retired: Associated applications: Description of why database is going offline: Offline Date: Scheduled Deletion Date: This database has been replaced by….. A retiring database will be disabled and taken to an offline status for up to three years before deletion. A form will be filled out for each database. Sign off required by City Attorney, City Recorder, IT Manager, and Director(s) over Division/Department(s) that owned the database agreeing to Archiving Policy. Signature required for each database. ---PAGE BREAK--- 2. Data Warehouse - This applies to data where the application is replaced but the data is still needed for reporting reasons and data was not converted to new application. a. Reporting needs assessment will be conducted to determine i. Common queries ran/saved ii. Historical reports required iii. How long data is required by law or Recorders Office retention schedule to be searchable before Archiving (going offline for up to 3 years then deletion.) iv. This is to be determine prior to and/or during a parallel conversion/migration to new product while the old program is still available. b. After needs assessment and the new application goes live the entire database will be moved to the Data Warehouse (DW) SQL Server instance and marked as read-only. c. Once data is moved to the DW instance the old database will be taken offline and the old application will no longer be available. This is to prevent any changes to the data. d. A copy of the database will be made called database_staging. i. Only tables that have common queries will be indexed for quick data retrieval ii. Where applicable view will be made and indexed to minimize user joining tables incorrectly iii. Field names will be considered for final e. A new database will be made called database_dw i. Views and tables will be imported into the database and indexed for quick retrieval ii. Field names will be normalized across table for ease of use and recognition f. As new reporting needs are brought to light those tables will be dealt with in staging and pushed to the dw g. User generated reports will be against database_dw. IT15-5 Enforcement: Any employee found to have violated this policy may be subject to disciplinary action up to and including termination of employment. Title Name Date City Attorney City Recorder IT Manager Director