In our production database, we currently hold 1.75 million rows of blobs (on average these blobs are less than 1MB, so we have determined that blobs are the best way forward), which takes up just over 320GB of the database. The whole of the production database is 470GB, so it is taking up about two thirds of the database. The binary data is stored in a varbinary(max) column, in a separate table to the metadata of the table.
We have been having discussions about the best way to hold the blobs on our database, as we are wanting to tidy up some of the references to the table, and possibly re-define some of the metadata. As we are already considering putting work into this, we want to make sure that we store the blobs in the correct manner going forward.
Idea 1 - New database with varbinary column (similar to current implementation)
We are considering moving the blobs outside of the production database to a new database on the same server, so that the backup of the live data is faster, and we can complete a backup of the blobs separately to the rest of the data. This would keep a lot of how we access the data the same, and probably the least effort in changing our code to access this.
Idea 2 - Remote Blob Storage (RBS)
While reading up on blob storage solutions, we came across this article about potentially storing the blobs via filestream, where the blobs would be stored on a separate dedicated storage server. We have recently upgraded to SQL Server 2017 Standard, and the article states that:
if you use the supplied FILESTREAM provider, you can store the BLOBs themselves on SQL Server Standard
I believe that if it is possible to set this up, it would be the best solution for us. However, we are not sure about how we would go about setting up RBS on our server.
My questions are:
- Are we correct in thinking that RBS would be the best way forward? Are there other solutions we haven't found?
- Are we correct in assuming that storing our files in blobs is correct?
- Is there any recent literature/reading material about setting up RBS on SQL Server 2017?