Reaching your SQL Server Express database capacity with picture or document storage?
Microsoft SQL Server Express is Microsoft’s entry level database server solution. It is available free of charge and as such has been widely adopted within many small to medium sized businesses. Microsoft have judged that offering a free solution will get their product in to the market with many users of the Express version inevitably graduating to the licensed model as their business grows. However, Microsoft SQL Server Express currently has a database limit of 10Gb along with a few other limitations. Many smaller businesses will never reach this 10Gb limit, so it provides a good, free, option for both clients and developers. The Standard version does not have this database size restriction but is significantly more expensive with a relatively complex licensing model.
It’s widely considered to be a bad idea to store large files such as photos and documents directly within the database as these types of files are considerably larger than the core data and will bloat the database. Nevertheless, because of the ease with which it can be implemented, we see this more often than you might imagine. If you have 3,000 photos at 2Mb each, this would contribute 6Gb to the 10Gb database limit. It’s easy to see how adopting SQL Server Express combined with storing files within the database can quickly cause problems. Eventually this scenario results in a critical issue as the systems which rely on the database grind to a halt as it reaches its capacity.
Microsoft have provided a database technology called Filestream which helps enormously with this very specific problem. The principal is that these large files are stored in the file system rather than within the database, but crucially they are still presented by the database as if they reside within it. Because the files remain external to the database they do not contribute to the 10Gb limit imposed by Microsoft SQL Server Express.
Fortunately this technology can also be retrospectively applied to an existing database. This can be a great help to those that are reaching their database capacity, who are otherwise facing the prospect of upgrading the database server with expensive licensing or hosting options, dumping data in order to solve the problem, or rewriting the system.
Recently we had a new client come to us with this very problem. With some careful planning and adjustments to ensure that the changes did not upset their existing software, we reduced the size of their database from 10Gb down to 50Mb using this technology – reducing the main database file to 1/200th of its original size. This represented a staggering 99.5% reduction. The client went from their database being at 100% capacity to a tiny 0.5% of capacity. This was achieved by moving nearly 10,000 pictures and documents from the main database in to the file system, without the existing software application noticing any changes. Not only did the client have a massive increase in available database capacity, they were able to retain the use of pictures and documents within their system, with all future pictures and documents also now being stored outside of the database. This represented a permanent fix to their capacity problem, resulting in one happy client.
If you have this specific problem, or if you think that we might be able to help with a software project or IT support, get in touch and we’ll be happy to help.
