Groups > comp.databases.ms-sqlserver > #679

Re: BLOB storage options

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver, microsoft.public.sqlserver.server
Subject	Re: BLOB storage options
Date	Mon, 26 Sep 2011 21:45:24 +0200
Organization	Erland Sommarskog
Lines	44
Message-ID	<Xns9F6CDD526413FYazorman@127.0.0.1> (permalink)
References	<3ac80097-f67b-4e86-9062-a1cb4a51b80a@m37g2000yqc.googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx04.eternal-september.org; posting-host="nBFDv6s1VJQDuF1w6hpX2A"; logging-data="9069"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18xPqcPKzuhtMt6KAahzVzX"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:/WYU/TE1q56C+Kn9UFV5Sjlgne4=
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:679

Cross-posted to 2 groups.

Show key headers only | View raw

Bill E. Hollywood, FL (behrreich@compuserve.com) writes:
> 1. Store files in the server file system and include a path or partial
> path to the file in a varchar type column.
> 2. Store files in the database in a table that includes a
> varbinary(max) or other acceptable column type.  Options here include
> using filestream as well as using a separate filegroup for BLOB
> storage.
> 3. Using a separate database on the same SQL Server for the BLOBs.
> 4. Other options?
> 
> I have experience with 1 and 2 and I understand some of the benefits
> and drawbacks of each.  However, I have another requirement--that I be
> able to backup and restore the database WITHOUT the BLOBs and still
> have a working database that I can use for development.  Option 1
> gives me this but I'm not convinced that I can easily do that with
> option 2 even if BLOBs are in a separate filegroup and I do filegroup
> restores (please correct me if I'm wrong).

I certainly like option 3 better than option 1. BACKUP/RESTORE on two 
databases is easier than having to restore a database and a file system.
And with option 3, you could build the database on views, so it looks 
that the data is in one table, permitting you to merge the databases 
later on if you wish.

As for the second option, I don't what happens if you restore the database
without the BLOB filegroup. In the best of worlds, you should be able
to access the rest of the data, although I don't know whether it actually
works that way. So much is clear, if you try to access the BLOB data,
you will lose.

Then again, if you want database without the blobs for development, 
what will you have instead of the blobs? Just have a fixed set of documents 
and only work with these for your test cases?

How big do you expect the production database to be?

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

BLOB storage options "Bill E. Hollywood, FL" <behrreich@compuserve.com> - 2011-09-26 04:14 -0700
  Re: BLOB storage options Erland Sommarskog <esquel@sommarskog.se> - 2011-09-26 21:45 +0200
    Re: BLOB storage options "Bill E. Hollywood, FL" <behrreich@compuserve.com> - 2011-09-27 07:50 -0700
      Re: BLOB storage options Erland Sommarskog <esquel@sommarskog.se> - 2011-09-27 22:56 +0200
        Re: BLOB storage options "Bill E. Hollywood, FL" <behrreich@compuserve.com> - 2011-09-27 15:01 -0700

csiph-web