mail@cbssolutions.co.uk+44 (0)1282 455 255Mon to Fri • 9am to 5pm
  • Home
  • About Us
  • Testimonials
  • Contact Us
  • Blog
Menu
 
CBS Solutions LTDCBS Solutions LTD
CBS Solutions LTD
Software Development Manchester
  • Software Development
    • Software Development
    • The Process
    • Aftercare & Support
    • Projects & Case Studies
  • IT Support
  • Hosting
Menu back  
July 19, 2016IT Support, Software DevelopmentBy Robert Tokeley
Microsoft SQL Server

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.

10GbCapacityDatabaseDocumentFilesFilestreamIT SupportLimitPhotoPictureSoftware DevelopmentSoftware ProjectSQL Server Express
Share this post
LinkedInTwitterFacebookGoogle+Pinterest
About the author

Robert Tokeley

Robert is a director at CBS Solutions LTD, has a HND in Computer Science and has been in the industry since 1990.

Recent Posts
  • Work From Home
    Web-based systems, remote working, and business resilience
    March 25, 2020
  • Using bespoke web-based software from a Raspberry Pi
    February 5, 2020
  • Windows Software
    Software longevity, support commitment, and web-based technology solutions
    April 23, 2019
  • Online Security
    HTTP, SSL, HTTPS, let’s talk acronyms and why you need one.
    August 30, 2018
  • Real-Time Updates & Popup Notifications
    May 31, 2018
  • RESTful
    The RESTful Approach to Software Development
    January 23, 2018
Categories
  • Google
  • IT Support
  • News
  • Software Development
Tags
10Gb AGILE Alerts App Backup Bespoke Calendar Capacity Client/Server Cloud CMS Custom Data Database Development Diary Document Files Filestream Google Hack Intranet IT Support Limit Manchester Mobile Monitoring Notifications Photo Picture Project Recovery Responsive RESTful Scheduling Search Slack Software Software Development Software Project SQL Server Express Web Web-based Website Wordpress
Sign up to our newsletter
Contact info
  • Telephone
    +44 (0)1282 455 255
  • Email
    mail@cbssolutions.co.uk
  • Address
    CBS Solutions LTD
    Lodge House
    Lodge Square
    Burnley
    BB11 1NW

Find us on:

TwitterLinkedinMailWebsite
Our focus
Software Development
Web Development
IT Support
IT Consultancy
Web Design
Website Hosting
What people say
  • “…CBS interpreted my needs extremely well to produce a simple, user friendly interface that caters for all the aspects of our business… It is proving invaluable; we are more efficient, mistakes have reduced, we have traceability of jobs and customers appreciate the investment made…”

    Trevon
    Director
  • “…I know it can take a lot of skill and hard graft to make something look simple, so I didn’t consider it an easy brief. The CBS guys did a brilliant job, and I’ve since had lots of compliments from site visitors who appreciate its ease of use. With that kind of feedback, you know you’ve got a good website.”

    Graham Barker
    A Better Mousetrap
    Proprietor
  • “My first contact with CBS was 5 years ago; they were recommended by the software house that had written our original software, but were withdrawing from that market sector. Their subsequent management of the transfer and development of the server based offering was beyond reproach…”

    Mike Bennett
    one2onetracker
    Principle Partner
Get in touch










CBS Solutions LTD

© 2018 CBS Solutions LTD

  • Home
  • About Us
  • Testimonials
  • Contact Us
  • Blog
  • Software Development
    • Software Development
    • The Process
    • Aftercare & Support
    • Projects & Case Studies
  • IT Support
  • Links
  • Hosting
  • Cookie Policy
  • Privacy Notice
  • GDPR
  • Data Processing Agreement
  • Sitemap
Navigation

Reg. No. 09114955

VAT No. 193 1138 16

We are registered with Information Commissioners Office (ICO) Data Protection Register - Reg. No. ZA478987

Site hand crafted in Manchester by... well... us!