MS SQL Server Project

Project Information

Client: San Francisco Police Department (Forensic Division)

Contract Title: DBA Support & Application Development Services

Service Type: Multiple Project-base Purchase Orders

Responsibilities: Database Management, Database Application Development (MS SQL, MS Access)

Overview

The San Francisco Police Department’s Crime Laboratory identified a need for Database design, customization, management, data retrieval, and maintenance to support criminal investigations for SFPD. There were a number of static ‘home grown’ databases which were unsupported by SFPD IT Division.

Challenges

SFPD’s Crime Laboratory identified a need for Database design, customization, management, data retrieval, and maintenance to support criminal investigations for SFPD. There were a number of static ‘home grown’ MS Access 2003 Databases (e.g. DNA-LIMS, STR and FSD databases) which were unsupported by SFPD IT Division. These databases contained critical data forensic examinations, so migration from MS Access 2003 Databases to SQL Server database application was requested. Additionally, completion of a number of project tasks was required in order to provide a basic level of support for these databases and prevent system failures.

Solutions

DTI Provided the following Solutions

Solution #1

Architect and Develop Database for Crime Scene Investigations (CSI): CSI was dependent on multiple hard copy documents each of which required much of the same information. The Design and Development of a database solution was needed to reduce the amount of manual data entry. The focus of this database is to capture CSI field callout information (who, when, where, why, etc), the technical data (including street scene and vehicle diagrams), evidence collection (biological evidence, firearms and weapons, report generation, etc.). Subsequent tasks included architecting and executing the migration for multiple Crime Lab systems that comprise the LIMS. Systems included Criminalistics, DNA Analysis, and Case Management.

Integration of DNA-LIMS and STR Databases with Genetic Analysis Platforms: Implementation of the current industry standard for DNA typing technology (e.g. ABI 3100 genetic analyzer and GeneMapper ID (GMID) proprietary data analysis software package) required integration with DNA-LIMS and STR databases.

Solution #2

Modification of Current Database Reports: Modification of several current MS Access Reports in DNA-LIMS was needed to adapt to current SFPD workflow. DTI provided design and strategy to migrate multiple Access based systems to SQL Server 2008 64-bit — from the project inception including requirements analysis, resource allocation, categorization of various projects for multiple Contracts/Purchase Orders.

Solution #3

Administrative Support and Training: DTI lead the technical resources and provided optimal strategy for redesign and development. Planned systematic migration paths of front-end, reports, queries to SQL Server; Implement new advanced functionalities and changes to reporting solutions with SSRS. Provided administrative support & training.

Solution #4

General Database Support: General database management and support was provided to amend reports and/or create customize new report functionality.

Solution #5

Identify lost data from previous database corruption/outage; restore lost data: Database corruption occurred at some point in the past. Various repair tools did not resolve the issue. The database was restored from a backup done 12 days prior. Approximately 8 days worth of data was lost when the restore was done from backup. A comparison of the data in the corrupted file vs. restored database for a set time frame was performed to identify the lost data and restore the data to the production database.