Modern businesses rely on database systems to store information and make it accessible to the right people throughout the organization.
Why use a Database Server?
Many smaller organisations are used to the way Access works – the database file (an MDB) is placed on a shared drive, and all users can access it via Access forms and reports. By comparison, the server-based approach seems complex and generally needs support from the IT department. As a result, we’re often asked exactly what benefits using a server brings.
There are three main reasons why server-based database systems are better than the shared file approach:
Data Integrity
Systems like Access provide relatively little protection for the data. If one of the programs writing into the Access database crashes – or is powered off at the wrong time, the Access file can be corrupted.
By comparison, server-based database systems have built-in protection against such corruption – it’s built into the design of the database.
Performance
Shared-file databases are much slower than server-based systems because each user is directly reading the whole data file over the LAN. With a server-based system, the user’s program sends a query to the server, which then computes and returns the answer instead of pushing large chunks of data over the network.
Ease of Maintenance
Systems like Access are simply not designed for larger projects – and maintenance of Access DBs can be very time-consuming. As more requirements are added to the Access-based solution, the complexity becomes increasingly harder to track and maintain. In addition, Access developers may implement their own solution to a commonly solved problem, whereas they could have used a standard solution in SQL. It therefore becomes easier to train new developers on the SQL based solution as they only have to learn the design of the databases as opposed to the design in addition to any quirks arising from custom Access code.
In recent years, Microsoft has introduced a free version of MS SQL Server, SQL Server Express. This eliminates cost as a reason for choosing a shared file approach. Because of this, Muki.co.ke recommends server-based databases for most customers.
Note – there are applications where database functionality is required within an installed program – this is termed an embedded database. In this case, server-based solutions are not appropriate.