SQL Server 2005 Database Design, Harrier LLC
Harrier LLC provide mail-order photo processing and online digital printing. The company has recently begun supplying photo gifts such as mugs, calendars and photo books though the online Truprint site as well as fulfilling these gift orders for numerous partners. Their current I.T. system tracks orders as they progress through the production process, but it is increasingly difficult to maintain. The project requirement was to create a new system based on new technology to take over the process. Geode was asked to plan the project and in the initial part of the project, to design the database.
Introduction
Since working with Harrier on their retail business, we were again contacted and asked to investigate the work involved in migrating from their existing I.T. system to a new system based on Microsoft SQL Server 2005.
The project scoping started late in 2006. We provided a detailed breakdown of all the work required and the best order to do it in order to migrate all the data safely to the new system.
Summary of the Work
The go-ahead for the initial phase of the project was given early in 2007; the database design and documentation. This required a detailed understanding of the existing data structures and determining the data that would be migrated to the new database. The data was carefully normalised to avoid any duplication, and a key concept in the project was to foresee that future online partners would not provide the same level of detail of customer information or order information. This had a huge bearing on the design for the database.
The database design was completed towards the end of 2007, and included all tables, indexing, T-SQL stored procedures and C#.NET assemblies as well as full documentation for the design. While in the migration phase, the existing system will remain the master, and the new database a (normalised) copy. Changes in the master system are automatically migrated to the copy. Stored procedures are used to provide the translation from the old data structures to the new. The new database is updated with the master copy automatically, and only short delay behind it. This technique allows the reporting system to be migrated at a later stage, along with all screens that show a read-only view of the database.
Technology
- SQL Server 2005.
- C#.NET. Complex transactional work is performed in an assembly.
- Synchronous Mirroring. Provides fail-over support.
- Replication. Provides data to a dedicated report server.
- Microsoft Reporting Services. Allow scheduled reports, cached copies, and output in a variety of formats.
Benefits
- Replacement of obsolete technology.
- Reduced hardware and software maintenance costs.
- Automatic system fail-over to a second location.
- Reports will run much faster, and with no slowdown in the production server.
- Quicker and easier to run ad-hoc queries.
