Adit > Case Studies > Access to Sql Server

Access to Sql Server

Background

• The end customer is part of one of the largest packaging businesses in the European region



• Programs developed by a third party software house have been in use since 1995 when the first Estimating and Sales Order Processing modules were tailored to their specific requirements

Position at start of project

The organisation uses a number of software modules, most of which are standard package or lightly tailored, but some are written bespoke

• Estimating


• Sales Order Processing


• Finished Goods Stock


• Costing


• Raw Material Stock


• Purchasing


• Ledgers


• Design

Database/Architecture

• Almost all data within the system is stored in Microsoft Access Databases (internal version 3), though occasional use of flat files is made when speed is a critical factor



• The databases are stored on a central server (set of servers), and users are connected as standard network clients, and via terminal server



• Programs access data via the integral Jet Database Engine running on the client machine

Problems

• With a large and expanding network, and associated number of users, the current architecture is creaking



• Many of the databases although not technically at the limits of size, are clearly demonstrating performance problems



• The volume and nature of the network traffic triggers system level file locking, and this further degrades performance, and the combined factors can lead to damage to individual databases



• On a more positive note, the software generally does what is required of it, and has many features that bring positive benefits to the company

Summary

• There has been a considerable investment in time and money by both the end customer and the software house in the development, customisation and optimisation of the current system, and it is a good match with many of the existing business processes



• Further it has the potential to grow with the company while remaining responsive to changing requirements



• The software is being constrained by the current database technology

General Software Development Trend

• The software house has been through a number of development cycles, mainly triggered by changes in the available programming tools



• Much of the early development work concerned general enhancements in usability, additional program features and modules, and of course direct responses to user demand



• Over the past two years there has been considerably more attention paid to a robust and efficient class-based structure



• Of particular relevance to the current position, there has been a move towards extended use of the database engine and its ability to manipulate and retrieve records via SQL (Structured Query Language)



• Unfortunately most of the benefits of the latter move are masked by the way Microsoft Access functions



• The database engine does the work but on the client rather than on the server

The Project

• The project facilitated the move to a database architecture that can support a larger number of users in a robust and efficient manner



• Because of its enhanced integration with Microsoft programming languages, the choice was Microsoft SQL (originally developed by SyBase), though the essential change (to client server) would be common to many other RDMS

Known problems at start of project

• SQL Server has greater restrictions on object naming (tables, fields, etc) than Access, and some renaming of objects is inevitable



• This should be done as simply and consistently as possible rather than be seen as an opportunity to make names 'more meaningful'



• Some care is needed with data types though Access data types map quite well to Sql Server



• Some small areas of code (mainly in the older maintenance programs) will need to be rewritten because they use methods that are not made available by the proposed database engine



• Ironically, these methods were almost all originally used to enhance speed

Approach

Conversion Team

• Build an initial SQL version of the current core system with all data in a single database



• Undertake a module by module review to identify opportunities to enhance performance



• Prove the system by comparing output from the new and old programs running on 'similar' base data



• Deliver an early test system

Development/Design Team

• Build a generic report generator that supports both the quick and dirty version of the database, as well as likely future versions (keep the user interface and report logic from the actual data access)



• Start reviewing the data structure with a view to removing redundancy