Microsoft SQL Server, along with many other enterprise level database solutions, runs as a service. It is important to understand the differences between non-service and service databases.
Non-Service vs. Service
There are two different types of database systems; Non-service and Service.
Non-service databases refer to solutions that have a database file that you open in an application. A well-known example of this would be Microsoft Access. When using this system you are effectively creating a file that you open in an interpreter application allowing you to access and modify the data contained within the file. The file is mobile so you can move the file from one location to another just as you would a document or image. You can open the file on any device that has an application that is capable of interpreting the data.
Sounds great! Why on earth would I want anything else?
Well portability like this comes at a price. When you are accessing the data your performance is not only restricted to that of your database design and coding, but also to that of the device that you are opening it on. All the processing will be done on your local device along with everything else that your device is doing at the same time.
There is also the big issue with multiple people or systems accessing the data. As soon as you start looking at allowing multiple people access to the data you instantly lose portability because the database file needs to remain in a location that is accessible to all users. Have a think about this and soon you will start to realise the issues that will come knocking at your door.
So what will a service give me to combat these issues?
One of the big advantages of a service is that it is always ready and waiting. A service is still based on a database file but whereas the non-service solution has a portable file, the service solution has a static file that is treated as nothing more than somewhere to store the data. The file is always open and is never directly accessed by anything other than the service. This instantly gives you a massive bump in performance because you are relying on the service to tend to your needs rather than the database file (which is controlled by the operating system). With this in mind, the daunting task of allowing multiple systems to access the same data suddenly vanishes. A solution simply adds its request to the service’s queue and when it is processed it will get a response.
Staying on performance, I mentioned that a non-service database is reliant on the performance capabilities of the device opening the database. This can cause you issues with slower or older devices and can ultimately make a solution unusable. The cost of ensuring that all devices have the capabilities of running your solution and accessing the database can be costly and there are so many factors that need to be taken into account that it has to be asked, is it really worth it? With a service solution you are taking all the hit from the database side of things away from the device and letting a dedicated server deal with it for you. In an environment where you have many devices accessing the data at the same time, the cost of purchasing a high-end server to handle the dirty work as opposed to upgrading everyone’s devices is a no-brainer and when the database gets bigger and requires more resources, you only have to upgrade the server or alternatively have the data available over multiple servers called a farm.
Another advantage of a service based solution, especially Microsoft SQL Server, is that you can have multiple instances and different versions running on the same system. When you have various solutions using various databases, it is fair to say that they may use different versions of SQL Server. When you have some systems that have a higher priority than others, you may wish to have databases on a separate instance and allow that instance to use more resources on the server or have priority of those resources compared to other instances. All this is possible with SQL Server.
How It Works
Without going into detail that is outside the scope of this post, the principles of the SQL Server service are pretty straight forward.
When your solution requires an operation to be carried out on a database, whether that operation is reading data or modifying it, it makes a request to the SQL Server service giving it all the details of the operation required. The service then adds that request to its queue and when it’s time to execute the request it does so. At this point the service is in a position to ensure that nothing else is going to interfere with your request and once complete a response is then sent back to your solution. This response may be something as simple as a result code to say that the request was completed successfully or it may contain thousands of rows of data – either way the process works the same.
Alongside the database file is a log file (or transaction log as it is also known). Each database has an option to allow various transaction logging levels. When a database is configured to log its transactions in the log file, it adds each successful request to the transaction log. This allows you to restore a database to a point-in-time from the last backup. Effectively the database restores to the last backup and then re-runs the commands within the transaction log until it reaches the point requested. This has its pros and cons and these would need to be evaluated on a per solution basis. Two things you need to keep in mind when deciding to use transaction logging is that if the database is modified a lot, the log file will get very big very fast. You also need to look at what state the database will be in if you had to restore to a specific point in time – would data be half way through a set of modifications for instance?
Unfortunately this is all we are going to cover in this particular area and hopefully it has given you an understanding of the differences in non-service and service based database solution and how a service based solution like SQL Server operates. I hope this post is helpful and as usual, constructive critisism and suggestions are welcome.