Often we’ll hear people describe their Excel spreadsheets as ‘databases’. Whilst an Excel spreadsheet can contain a treasure trove of vital information for a business, it isn’t, strictly speaking, a database.
As a system developer when someone says ‘databases’ to me, I think of them as a tool to organise data in a structured way that can protect it from inadvertent updates and deletions and make the data in it available to the right people in the right place at the right time.
A relational database allows data to be stored in one place but referenced in many others. As a simple example, there can be a list of customers in one table, and can then link one of those customers to a table of orders, and also to a table containing a contact history with that customer, so if we need to update anything for a customer we update it once and everything related will see that update.
Almost all the systems I work with are built using Microsoft SQL Server as a database (Gareth works in our Systems team, dealing with bespoke applications for the health sector). I’ve been working with SQL Server (pronounced ‘sequel’) since the year 2000. In those days I was taken into a freezing cold air-conditioned server room and told that the big box in the corner, which looked like a larger version of the PC on my desk albeit with a built-in tape deck for backup purposes, was the SQL Server. Even back then, we’d rarely interact physically with the machine itself, instead using tools on our own computers that could connect to it to get data from it or to write programs that inserted and worked with the data on it.
I learnt about how you could take backups of the data in your database as frequently as you wanted, and about a thing called a ‘transaction log’ which recorded everything that was updated in the database. You could even use it to almost go back in time to see what the database looked like prior to a change. I also learned that SQL Server could use things called indexes, a subset of your data that could be used to show things on screen or in reports much more quickly than you could do it if you were getting all of the data.
The systems we were building became more and more complicated and as they contained health related data we learnt how to encrypt data within the database so it was only accessible to people with the correct permissions level. With hundreds of users accessing data at the same time, all wanting instant responses we identified ways of getting data out of SQL Server as quickly as possible.
Nowadays there are many different ways of using SQL Server. Dedicated physical servers are less common with the flexibility offered by virtual servers and even cloud based servers meaning it is possible to have SQL Servers in different locations, even different continents, that synchronise data between them, making globally connected systems easy to implement. Microsoft’s Azure services involve a product called SQL Database with the ability to scale up the processing power available to it as a system grows. Microsoft take care of managing the availability of the database, and as developers we can concentrate on getting your system running as fast as possible.
So if you’re using Excel as a database or have spreadsheets that are becoming unmanageable, get in touch and we’ll help you to secure that important business data.
