Have you outgrown your Access database?

There is a simple rule of thumb that says that if you have a large records set and/or more than half a dozen concurrent users on an Access database, then you might benefit from a move to Microsoft SQL Server. At above these levels you are likely to find that Access becomes slow to respond and occasionally locks up whole pages of records that are being used by other users.

Increasingly databases are the life blood of a company.  They dictate the level of service that can be offered to customers, they are a repository of company knowledge and as such can facilitate opportunities for cross selling and opening new markets, and they are fast becoming a key component in any Due Diligence process prior to a company’s sale or purchase.

Limits of Access

From a strictly business point of view the idea of having 6 or more members of the staff wasting anything up to 10% of their time waiting for the database to respond is normally a sufficient reason for making the change.  But there is a more subtle reason relating to personnel motivation that comes into play. There are few things that are more irritating than waiting for the screen to refresh when you have an irate customer on the line.

Technical Differences

From a technical point of view, the Microsoft SQL server runs a genuine Client-Server architecture which means that queries are executed on the server and then returned to the client machine. Access works by locking blocks of 20 record when it needs to use them, and this is what causes the growth of ‘record lock’ errors as more concurrent users try to access the same or similar data..

On top of that immediate performance gain, MS SQL Server will deliver a wealth of opportunities for continuous improvements in usability, security and backup.  

Features such as ‘synchronisation’ enable you to offer remote users (salesmen, engineers etc) the power to read or update the database on their local PC off line and then integrate those changes at the end of the day/week with the master database via the internet.

XML features are an increasing features of database usage so the facility to build Web Services is a definite plus (Web Services are an excellent way of delivering data to third parties to whom you do not wish to give full access to your database.)

What does it cost

Using an SQL database is effectively free for up to 12 concurrent users, because it can run on the MSDE which is a free application supplied by Microsoft.

However, creating and editing MS SQL databases requires that you run the server with all the tools that you need to gain value from it.

If you are starting from scratch, there are some amazing deals around the SBS (Small Business Server) - See Dell – at time of writing they were offering a package of Fast hardware with RAID hard drives and SBS Standard edition for just over £1,000. The SBS Premium which includes SQL is about another £1,000.

So the main cost is in the labour. And remember that not all programmers are created equal.

The skills needed to create a database correctly are significantly higher than those needed to just maintain it and use it. And the skills needed to fix a badly created database with loads of valuable data are very rare indeed, so you can expect to pay a premium for them.

Any consideration of the conversion of the database from one platform to another, needs to take into account:

• Time for working with users to understanding the current application and how it is being used. This invariably brings up a few change requests.
• Conversion of the database structure
• Migration of the data
• Recreation of a front end for the users (so it has a familiar look and feel)
• Consideration of increased functionality that the users would like to see.
• Consideration of likely future uses for the data – eg website access, web services, security restrictions  etc
• Arrangements on how the database is going to be supported in the future.

The process of converting a database can take anything from a couple of days to a several weeks of work, and its eventual success will be determined by the initial planning and the skill of the programmer.

One final issue worth thinking about. If you use bespoke software it makes you dependant on the creator of that application and it does not matter whether that is an in house employee, a sub-contractor or a freelance programmer, this can create difficulties. By using industry standard database structures you increase the likelihood that you will be able to find a new person to support your application if the creator is no longer available. Just make sure that you have copies of any source code that has been used, and all passwords.