Need a new Database System? No problem – we can deliver!
Modern businesses rely on database systems to store information and make it accessible to the right people throughout the organisation.
Introduction to Custom Database Systems
Database systems provide organisations with a central repository of information that can be easily searched or used to generate reports. There are a huge number of technologies used to implement database systems – from Access to server-based systems like SQL Server and Oracle.
MS Access is often the starting point for many database systems in smaller companies or workgroups within larger organisations – and it can work well for smaller systems. But Access isn’t really suited for situations where the data is very important, or where it needs to be shared over the internet.
Server based database systems like MS SQL Server, MySQL and Oracle use a dedicated server to hold the database. This makes the whole system faster and provides effective protection against data corruption, as it allows the data to be easily made redundant and backed up.
Although the database server software is at the heart of the system, the process of designing a new database system has more to do with understanding the requirements and then creating the forms and reports required to enter the data and retrieve it.
Blueberry has all the skills required to deliver a custom database system for your organisation:
- We can create a custom Windows Application to deliver the forms and reports you need directly on your PCs
- Or we can create a Web-based Application, so that the forms and reports are delivered via your Internet Browser, and are accessible to any group or user you choose.
You can read here about how we work with you to understand your needs.
If you’d like to understand more about the database technologies we use, please continue reading.
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:
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.
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, Blueberry 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. Please see below for more on embedded databases.
Database Server Technologies
There are a huge number of database systems on the market, but most attention focuses on the top three systems:
MS SQL Server
Microsoft’s flagship database product – available in a number of editions ranging from free to expensive.
The major competitor to Microsoft – and in some ways the original database. Oracle is generally used for larger systems, because most commercial customers tend to go with Microsoft SQL Server.
An open source (free) product that has established itself as the de-facto standard for Linux applications
Blueberry has used all three of these products on live projects, but we generally prefer to use MS SQL Server – and we recommend this to customers due to its ease of configuration and debugging thanks to the bundled tools, in particular SQL Server Management Studio. MySQL has only recently become a serious product – earlier versions omitted data security features that we thought were essential. Oracle is generally seen as more expensive than Microsoft – and most of our customers are far more familiar with MS SQL Server.
We are of course happy to discuss the choice of database on any project with the customer.
MS SQL Server Licence Options
MS SQL Server is available in a number of different licensing options:
An embedded version, designed to be integrated within applications – see below. No new versions or updates are planned, although Microsoft will continue to support it until July 2021.
A free version for small applications – limited to a 1GB database size, but still usable for many situations.
The main commercial version for most normal applications.
MS SQL Enterprise
A high-end version with extra specialist features.
MS SQL Express is particularly useful for small systems, as it allows customers to access the performance and data-security benefits of SQL Server without needing to pay any licence fees.
Advanced MS SQL Server Features
MS SQL Server 2014 is a very substantial product with a huge feature set. The following are some of the advanced features that Blueberry has made use of in live projects:
This allows two systems to exchange information automatically – it is normally used to allow two offices to have a shared view of data. Blueberry has successfully established SQL replication between the UK and India, and between Hong Kong and South China.
This is a feature which allows one system to receive a continuous copy of all changes made to another system. It provides a way to implement a redundant server over the network. Blueberry has used this feature to establish failover backups for a number of clients.
This is an established feature of MS SQL Server – a sophisticated and fast reporting engine. With most applications, we use Crystal Reports, but Crystal is quite idiosyncratic. Having used MS SQL Reporting on a recent project we think it is superior to Crystal in most regards and we will continue to use it going forward.
For some custom software applications, there can be a requirement to deploy a small database within a program that is installed on the user’s computer. This most often happens when designing a software product because most purchasers of software products want it to be self-contained. This type of database is known as an embedded database.
There are two main embedded database technologies available – SQLite and MS SQL Compact. Blueberry has used both of these on live systems. SQLite has the benefit of being cross-platform, so it can be used on cross-platform projects. MS SQL Compact is more powerful, and is compatible with the bigger editions, so applications can be designed with an upgrade path if required.