Every now and then I get asked to do something really fun, and out of my normal purview of technology. We had a very large table in our database, filled with blob data from how we store our documents, and this table was about 5/6 of our overall database! Because the mdf for our database was so large we decided to do something about it, as it was starting to impact our support tasks and the nightly restore was blowing out to midmorning!

We looked at a couple of options:

SQL Server Replication

Snapshot replication:
This was the closest fit to our existing process and at first seemed like the way to go. When I researched into it a little further though, I found that snapshot replication locks the database for the entire duration of the process. So due to the size of our db, and the fact that the current restore process was already blowing out during business hours, this was definitely off the cards!!

Transactional Replication:
This type of replication involves pushing each transaction that hits the db to another db. This is good for when the data doesn't change much on the replicated end, but you start to run into issues when changes are done on both ends. Plus there's an extra network performance hit as you are literally doing every transaction twice.

Filegroups

A non-replication option you can go with is splitting the relevant table to it's own file and file-group within the the same db. We ended up going with this option as it gives us a great deal of flexibility in terms of manipulation of the data files themselves as we can restore the smaller main chunk of data in a much quicker time and simply restore the blob table at a greater interval to take the strain off the network.

Everybody's happy :)

One caveat with the file-group option is any existing blob data columns (blob is any column data type of text, next, and image), cannot be moved off their current file-group, and onto the new one without recreating the table from scratch. Yuck, I know, but I believe worth the initial pain in the long run.

I will post the (sanitized, obviously) code over the next couple of days, as well as source links for my research, as I'm currently writing this from the after hours gp in subiaco on my iPod touch :)

Get well soon Fran!

Night all :)