CODEWITHSUNDEEP
sqldatabasesql-server-2008filestream
Mostafa Elmoghazi
Mostafa Elmoghazi

Reputation: 2154

SQL Server 2008 FILESTREAM Feature with VLDB

I have a bunch of xml files that is about 700 GB in size. I'm going to load the data within those files into a SQL Server 2008 database table(tabular data). In addition to the fields that will hold the data in a tabular format, the table will contain a field of SQL Server XML type that holds the xml data as a whole. I want to use the FILESTREAM feature of SQL Server 2008 instead of loading the whole xml into the field. I want to know the benefits the performance of the queries that will be made on such a very large-table will gain and the pros and cons of this feature.

Thank you in advance.

Upvotes: 0

Views: 388

Answers (1)

RC_Cleland
RC_Cleland

Reputation: 2294

I do not expect this will ever be marked as the answer because the true answer will only be discovered after an through study of available solutions.

BUT

The answer I have is really a question for you. How are you going to use this data? IF your are going to shread the xml to retrieve the reporting values and keep the complete xml for a reference then I would goto Filestream. If you are going to run reports directly from the xml then you will have to load the data into the database creating the needed indexes.

Loading all data into SQL Server as a combination of shreaded xml and an xml datatype

PRO

  • All data is avaiable all the time from one source
  • A single backup contains all data
  • Additional data from XML can be shreaded to enhance reports on server side

CON - Backup size - Backup time - Slow if data is in native XML

Loading values from XML into SQL Server and using Filestream

PRO

  • Data source (filestream) is tied to data values
  • Source data can be presented to client Con
  • Filestream content is not available directly from within query
  • Filestream and SQL backups to syncronize for disaster recovery

Be aware of your storage needs for backups and the maintenaince window need.

Upvotes: 1

Related Questions