Most if not all of the sql servers that I support are not on a SAN, and are
suffering from not having enough local space. This could be mitigated if we
were using some form of database dump compression. The optimum solution
would be one which compresses the data as it was being initially written to
the dump file.
( versus creating a full blown dump file and then running a second step to
compress it )
This compressed file would also benefit us in any subsequent copy steps,
whereby we would be copying the file to another server for stand-by purposes
.
( less network time/traffic )
If Microsoft Sql server does not have built in compression, what other
options are available? Are there any 3rd party plug-ins that interface with
sql server and provide a compressed dump file as it is being initially
written?I've listed some such 3:rd party apps at : http://www.karaszi.com/SQLServer/links.
asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JohnR" <JohnR@.discussions.microsoft.com> wrote in message
news:556E82D2-EA42-462F-BFDB-BA9A152AE2B0@.microsoft.com...
> Most if not all of the sql servers that I support are not on a SAN, and ar
e
> suffering from not having enough local space. This could be mitigated if
we
> were using some form of database dump compression. The optimum solution
> would be one which compresses the data as it was being initially written t
o
> the dump file.
> ( versus creating a full blown dump file and then running a second step to
> compress it )
> This compressed file would also benefit us in any subsequent copy steps,
> whereby we would be copying the file to another server for stand-by purpos
es.
> ( less network time/traffic )
> If Microsoft Sql server does not have built in compression, what other
> options are available? Are there any 3rd party plug-ins that interface wi
th
> sql server and provide a compressed dump file as it is being initially
> written?
Showing posts with label compression. Show all posts
Showing posts with label compression. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
compression of database backups -
Most if not all of the sql servers that I support are not on a SAN, and are
suffering from not having enough local space. This could be mitigated if we
were using some form of database dump compression. The optimum solution
would be one which compresses the data as it was being initially written to
the dump file.
( versus creating a full blown dump file and then running a second step to
compress it )
This compressed file would also benefit us in any subsequent copy steps,
whereby we would be copying the file to another server for stand-by purposes.
( less network time/traffic )
If Microsoft Sql server does not have built in compression, what other
options are available? Are there any 3rd party plug-ins that interface with
sql server and provide a compressed dump file as it is being initially
written?SQL Litespeed is an excellent product for backing-up and compressing
databases. Here is the link:
http://www.quest.com/litespeed_for_sql_server/
AndyP,
Sr. Database Administrator,
MCDBA 2003
"JohnR" wrote:
> Most if not all of the sql servers that I support are not on a SAN, and are
> suffering from not having enough local space. This could be mitigated if we
> were using some form of database dump compression. The optimum solution
> would be one which compresses the data as it was being initially written to
> the dump file.
> ( versus creating a full blown dump file and then running a second step to
> compress it )
> This compressed file would also benefit us in any subsequent copy steps,
> whereby we would be copying the file to another server for stand-by purposes.
> ( less network time/traffic )
> If Microsoft Sql server does not have built in compression, what other
> options are available? Are there any 3rd party plug-ins that interface with
> sql server and provide a compressed dump file as it is being initially
> written?
>
suffering from not having enough local space. This could be mitigated if we
were using some form of database dump compression. The optimum solution
would be one which compresses the data as it was being initially written to
the dump file.
( versus creating a full blown dump file and then running a second step to
compress it )
This compressed file would also benefit us in any subsequent copy steps,
whereby we would be copying the file to another server for stand-by purposes.
( less network time/traffic )
If Microsoft Sql server does not have built in compression, what other
options are available? Are there any 3rd party plug-ins that interface with
sql server and provide a compressed dump file as it is being initially
written?SQL Litespeed is an excellent product for backing-up and compressing
databases. Here is the link:
http://www.quest.com/litespeed_for_sql_server/
AndyP,
Sr. Database Administrator,
MCDBA 2003
"JohnR" wrote:
> Most if not all of the sql servers that I support are not on a SAN, and are
> suffering from not having enough local space. This could be mitigated if we
> were using some form of database dump compression. The optimum solution
> would be one which compresses the data as it was being initially written to
> the dump file.
> ( versus creating a full blown dump file and then running a second step to
> compress it )
> This compressed file would also benefit us in any subsequent copy steps,
> whereby we would be copying the file to another server for stand-by purposes.
> ( less network time/traffic )
> If Microsoft Sql server does not have built in compression, what other
> options are available? Are there any 3rd party plug-ins that interface with
> sql server and provide a compressed dump file as it is being initially
> written?
>
compression of database backups -
Most if not all of the sql servers that I support are not on a SAN, and are
suffering from not having enough local space. This could be mitigated if we
were using some form of database dump compression. The optimum solution
would be one which compresses the data as it was being initially written to
the dump file.
( versus creating a full blown dump file and then running a second step to
compress it )
This compressed file would also benefit us in any subsequent copy steps,
whereby we would be copying the file to another server for stand-by purposes.
( less network time/traffic )
If Microsoft Sql server does not have built in compression, what other
options are available? Are there any 3rd party plug-ins that interface with
sql server and provide a compressed dump file as it is being initially
written?
SQL Litespeed is an excellent product for backing-up and compressing
databases. Here is the link:
http://www.quest.com/litespeed_for_sql_server/
AndyP,
Sr. Database Administrator,
MCDBA 2003
"JohnR" wrote:
> Most if not all of the sql servers that I support are not on a SAN, and are
> suffering from not having enough local space. This could be mitigated if we
> were using some form of database dump compression. The optimum solution
> would be one which compresses the data as it was being initially written to
> the dump file.
> ( versus creating a full blown dump file and then running a second step to
> compress it )
> This compressed file would also benefit us in any subsequent copy steps,
> whereby we would be copying the file to another server for stand-by purposes.
> ( less network time/traffic )
> If Microsoft Sql server does not have built in compression, what other
> options are available? Are there any 3rd party plug-ins that interface with
> sql server and provide a compressed dump file as it is being initially
> written?
>
suffering from not having enough local space. This could be mitigated if we
were using some form of database dump compression. The optimum solution
would be one which compresses the data as it was being initially written to
the dump file.
( versus creating a full blown dump file and then running a second step to
compress it )
This compressed file would also benefit us in any subsequent copy steps,
whereby we would be copying the file to another server for stand-by purposes.
( less network time/traffic )
If Microsoft Sql server does not have built in compression, what other
options are available? Are there any 3rd party plug-ins that interface with
sql server and provide a compressed dump file as it is being initially
written?
SQL Litespeed is an excellent product for backing-up and compressing
databases. Here is the link:
http://www.quest.com/litespeed_for_sql_server/
AndyP,
Sr. Database Administrator,
MCDBA 2003
"JohnR" wrote:
> Most if not all of the sql servers that I support are not on a SAN, and are
> suffering from not having enough local space. This could be mitigated if we
> were using some form of database dump compression. The optimum solution
> would be one which compresses the data as it was being initially written to
> the dump file.
> ( versus creating a full blown dump file and then running a second step to
> compress it )
> This compressed file would also benefit us in any subsequent copy steps,
> whereby we would be copying the file to another server for stand-by purposes.
> ( less network time/traffic )
> If Microsoft Sql server does not have built in compression, what other
> options are available? Are there any 3rd party plug-ins that interface with
> sql server and provide a compressed dump file as it is being initially
> written?
>
compression of database backups -
Most if not all of the sql servers that I support are not on a SAN, and are
suffering from not having enough local space. This could be mitigated if we
were using some form of database dump compression. The optimum solution
would be one which compresses the data as it was being initially written to
the dump file.
( versus creating a full blown dump file and then running a second step to
compress it )
This compressed file would also benefit us in any subsequent copy steps,
whereby we would be copying the file to another server for stand-by purposes
.
( less network time/traffic )
If Microsoft Sql server does not have built in compression, what other
options are available? Are there any 3rd party plug-ins that interface with
sql server and provide a compressed dump file as it is being initially
written?SQL Litespeed is an excellent product for backing-up and compressing
databases. Here is the link:
http://www.quest.com/litespeed_for_sql_server/
AndyP,
Sr. Database Administrator,
MCDBA 2003
"JohnR" wrote:
> Most if not all of the sql servers that I support are not on a SAN, and ar
e
> suffering from not having enough local space. This could be mitigated if
we
> were using some form of database dump compression. The optimum solution
> would be one which compresses the data as it was being initially written t
o
> the dump file.
> ( versus creating a full blown dump file and then running a second step to
> compress it )
> This compressed file would also benefit us in any subsequent copy steps,
> whereby we would be copying the file to another server for stand-by purpos
es.
> ( less network time/traffic )
> If Microsoft Sql server does not have built in compression, what other
> options are available? Are there any 3rd party plug-ins that interface wi
th
> sql server and provide a compressed dump file as it is being initially
> written?
>sqlsql
suffering from not having enough local space. This could be mitigated if we
were using some form of database dump compression. The optimum solution
would be one which compresses the data as it was being initially written to
the dump file.
( versus creating a full blown dump file and then running a second step to
compress it )
This compressed file would also benefit us in any subsequent copy steps,
whereby we would be copying the file to another server for stand-by purposes
.
( less network time/traffic )
If Microsoft Sql server does not have built in compression, what other
options are available? Are there any 3rd party plug-ins that interface with
sql server and provide a compressed dump file as it is being initially
written?SQL Litespeed is an excellent product for backing-up and compressing
databases. Here is the link:
http://www.quest.com/litespeed_for_sql_server/
AndyP,
Sr. Database Administrator,
MCDBA 2003
"JohnR" wrote:
> Most if not all of the sql servers that I support are not on a SAN, and ar
e
> suffering from not having enough local space. This could be mitigated if
we
> were using some form of database dump compression. The optimum solution
> would be one which compresses the data as it was being initially written t
o
> the dump file.
> ( versus creating a full blown dump file and then running a second step to
> compress it )
> This compressed file would also benefit us in any subsequent copy steps,
> whereby we would be copying the file to another server for stand-by purpos
es.
> ( less network time/traffic )
> If Microsoft Sql server does not have built in compression, what other
> options are available? Are there any 3rd party plug-ins that interface wi
th
> sql server and provide a compressed dump file as it is being initially
> written?
>sqlsql
Compression in Replication
we are planning to use SQL Server 2000 replication to replicate BLOBs
stored in image, ntext datatypes. Since the size of these BLOBs will
vary and sometimes be quite large, is there any way in which
Replication does compression on the data ?
we are concerned about the bandwidth issues and would appreciate
any insights into this.
Thanks
TJ,
on the snapshot location tab, you can select to plact the snapshot files in
an alternative location. If you do this, there is the option to compress the
files. The compression creates a CAB file which is passed to the subscriber
then decompressed there by the distribution/merge agent before assing to the
subscription database. Apart from the snapshot, I don't know of any way of
compressing data, as it is stored in system tables for transactional and
merge. You might like to look at optimisation of the agent profile to help
with BLOB datatypes.
HTH,
Paul Ibison
|||No, there is no way to selectively compress columns in tables with
replication. There are issues with replicating text and image data type
columns.
Look at Planning for Transactional Replication in BOL for more information.
"TJ" <anonymous@.discussions.microsoft.com> wrote in message
news:E497769F-3B0A-40CA-9937-A314B359F681@.microsoft.com...
> we are planning to use SQL Server 2000 replication to replicate BLOBs
> stored in image, ntext datatypes. Since the size of these BLOBs will
> vary and sometimes be quite large, is there any way in which
> Replication does compression on the data ?
> we are concerned about the bandwidth issues and would appreciate
> any insights into this.
> Thanks
|||Thanks Hilary and Paul.
appreciate your inputs.
|||Normaly Windows to Windows PPP connection does data compression
up to 85%. In cases where we connect Windows servers over ISDN router
and for asome reason they cannot negotiate compression, we are using
OpenSSH + PUTTY , achieving up to 5 times better results than without
compression.
Happy greetings
Pagus
On Thu, 25 Mar 2004 07:01:08 -0800, "TJ"
<anonymous@.discussions.microsoft.com> wrote:
>we are planning to use SQL Server 2000 replication to replicate BLOBs
>stored in image, ntext datatypes. Since the size of these BLOBs will
>vary and sometimes be quite large, is there any way in which
>Replication does compression on the data ?
>we are concerned about the bandwidth issues and would appreciate
>any insights into this.
>Thanks
stored in image, ntext datatypes. Since the size of these BLOBs will
vary and sometimes be quite large, is there any way in which
Replication does compression on the data ?
we are concerned about the bandwidth issues and would appreciate
any insights into this.
Thanks
TJ,
on the snapshot location tab, you can select to plact the snapshot files in
an alternative location. If you do this, there is the option to compress the
files. The compression creates a CAB file which is passed to the subscriber
then decompressed there by the distribution/merge agent before assing to the
subscription database. Apart from the snapshot, I don't know of any way of
compressing data, as it is stored in system tables for transactional and
merge. You might like to look at optimisation of the agent profile to help
with BLOB datatypes.
HTH,
Paul Ibison
|||No, there is no way to selectively compress columns in tables with
replication. There are issues with replicating text and image data type
columns.
Look at Planning for Transactional Replication in BOL for more information.
"TJ" <anonymous@.discussions.microsoft.com> wrote in message
news:E497769F-3B0A-40CA-9937-A314B359F681@.microsoft.com...
> we are planning to use SQL Server 2000 replication to replicate BLOBs
> stored in image, ntext datatypes. Since the size of these BLOBs will
> vary and sometimes be quite large, is there any way in which
> Replication does compression on the data ?
> we are concerned about the bandwidth issues and would appreciate
> any insights into this.
> Thanks
|||Thanks Hilary and Paul.
appreciate your inputs.
|||Normaly Windows to Windows PPP connection does data compression
up to 85%. In cases where we connect Windows servers over ISDN router
and for asome reason they cannot negotiate compression, we are using
OpenSSH + PUTTY , achieving up to 5 times better results than without
compression.
Happy greetings
Pagus
On Thu, 25 Mar 2004 07:01:08 -0800, "TJ"
<anonymous@.discussions.microsoft.com> wrote:
>we are planning to use SQL Server 2000 replication to replicate BLOBs
>stored in image, ntext datatypes. Since the size of these BLOBs will
>vary and sometimes be quite large, is there any way in which
>Replication does compression on the data ?
>we are concerned about the bandwidth issues and would appreciate
>any insights into this.
>Thanks
compression for log shipping
Hi All
I am using the Simple Log Shipping in the SQL resource kit.
just wondering if there is any way I can compress the log during the log
shipping processing?
thanks
Justin
Nope. SQL Server does not support compressing backups. Why? I have
absolutely no idea at all. Want it in the product? Get a few thousand of
your friends to make it an issue to add the feature.
http://lab.msdn.microsoft.com/produc...k/Default.aspx
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Justin" <justin@.innocity.net> wrote in message
news:eDd$b91CGHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I am using the Simple Log Shipping in the SQL resource kit.
> just wondering if there is any way I can compress the log during the log
> shipping processing?
> thanks
> Justin
|||As Michael mentioned, the is no compressing support in the Sql2005 release.
This feature has been considered for the future release though.
Thanks
Yunwen
Disclaimer: This posting is provided “AS IS” with no warranties, and confers
no rights. You assume all risk for your use.
"Michael Hotek" wrote:
> Nope. SQL Server does not support compressing backups. Why? I have
> absolutely no idea at all. Want it in the product? Get a few thousand of
> your friends to make it an issue to add the feature.
> http://lab.msdn.microsoft.com/produc...k/Default.aspx
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Justin" <justin@.innocity.net> wrote in message
> news:eDd$b91CGHA.3140@.TK2MSFTNGP14.phx.gbl...
>
>
I am using the Simple Log Shipping in the SQL resource kit.
just wondering if there is any way I can compress the log during the log
shipping processing?
thanks
Justin
Nope. SQL Server does not support compressing backups. Why? I have
absolutely no idea at all. Want it in the product? Get a few thousand of
your friends to make it an issue to add the feature.
http://lab.msdn.microsoft.com/produc...k/Default.aspx
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Justin" <justin@.innocity.net> wrote in message
news:eDd$b91CGHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I am using the Simple Log Shipping in the SQL resource kit.
> just wondering if there is any way I can compress the log during the log
> shipping processing?
> thanks
> Justin
|||As Michael mentioned, the is no compressing support in the Sql2005 release.
This feature has been considered for the future release though.
Thanks
Yunwen
Disclaimer: This posting is provided “AS IS” with no warranties, and confers
no rights. You assume all risk for your use.
"Michael Hotek" wrote:
> Nope. SQL Server does not support compressing backups. Why? I have
> absolutely no idea at all. Want it in the product? Get a few thousand of
> your friends to make it an issue to add the feature.
> http://lab.msdn.microsoft.com/produc...k/Default.aspx
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Justin" <justin@.innocity.net> wrote in message
> news:eDd$b91CGHA.3140@.TK2MSFTNGP14.phx.gbl...
>
>
Compression feature, support for 64kb clusters?
Hi,
does the new (and future) compression feature included in katmai will support partitions formatted in 64kb?
the windows compression system required a 4kb cluster format and its not supported in other cluster size, this is a limitation when we use 64kb clusters for our database files.
Thanks.
Jerome.
The new compression feature will be done inside the SQL Server engine before data is written to disk, and is independent of the disk cluster size. So it will support 64kb formatted disk partitions.compression and the Primary XML index
Is SS08 compression going to help me with my XML. The Primary index is really just a table it appears, so I was hoping to compress that ... plesae?
It is definitely one of the areas that we are considering.
Thanks,
Compression
Hello,
I have been wanting to compress my database. I am not really sure how this is done. I was looking on Enterprise Mangr. and if you right click on the db and go to all tasks, there is an option to shrink database. Is this the way you would compress your database, or are there other ways of doing this?
Thanks for all the help.DUMP
DBCC SHRINKFILE|||Originally posted by Brett Kaiser
DUMP
DBCC SHRINKFILE
How would you do a shrink file?|||Do you have access to Books Online?
Go to the U=Index and look it up
Examples
This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO
Look up DBBC SHRINKDATABASE as well|||Originally posted by Brett Kaiser
Do you have access to Books Online?
Go to the U=Index and look it up
Examples
This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO
Look up DBBC SHRINKDATABASE as well
Thanks again for all your help.|||Shrinkdatabase May not work All the time . Try using Shrinkfiles too.
Also, it is not a good practice to leave Shrink_db option checked ..
I have been wanting to compress my database. I am not really sure how this is done. I was looking on Enterprise Mangr. and if you right click on the db and go to all tasks, there is an option to shrink database. Is this the way you would compress your database, or are there other ways of doing this?
Thanks for all the help.DUMP
DBCC SHRINKFILE|||Originally posted by Brett Kaiser
DUMP
DBCC SHRINKFILE
How would you do a shrink file?|||Do you have access to Books Online?
Go to the U=Index and look it up
Examples
This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO
Look up DBBC SHRINKDATABASE as well|||Originally posted by Brett Kaiser
Do you have access to Books Online?
Go to the U=Index and look it up
Examples
This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO
Look up DBBC SHRINKDATABASE as well
Thanks again for all your help.|||Shrinkdatabase May not work All the time . Try using Shrinkfiles too.
Also, it is not a good practice to leave Shrink_db option checked ..
Compression
Hi everyone,
I am developing a web app and wanting to store documents (doc, xls,
pdf, ppt...etc.) within the DB (MSSQL). Is it advisable in general (I
don't know exactly how big the docs are going to be) to compress the
files before putting them into the DB? How does this affect Full Text
Searching? On a wider note what's the attitude of people in terms of
storing documents in the DB versus on the file system. There seems to
be a lot of differing opinions. Any links to resources would be most
appreciated.
Thanks for your help in advance.
Jose
Jose,
First of all, can I assume that you are using SQL Server 2000? If so, could
you post the full output of: SELECT @.@.version -- as this is very help info
in understanding SQL FTS issues.
Secondly, what exactly do you mean by "compress[ing] the files before
putting them into the DB?" Do you mean to store them as ZIP files in columns
defined with the IMAGE datatype? or are you thinking about placing the FT
Catalog folder (SQL00060005) & files on a compressed drive. As for the
latter, I've not done any testing with compressed drive, but Hilary Cotter
has and from past postings on this subject, he's indicated that the
performance gains is mimumal. As for the former, you will need to purchase a
3rd Party IFilter for the WinZIP ZIP file format and then assuming you are
using SQL Server 2000, you can store the zip files in an IMAGE datatype and
define a "file extension" column that will have the value of "zip" and the
ZIP IFilter will be called by the MSSearch and the mssdmn (Search Filter
Daemon) processes to FT Index the contents of the zip file that contains MS
Word documents. You can purchase the ZIP IFilter from either of the
following two vendors:
http://www.4-share.com/content/products.htm
http://www.ifiltershop.com/zipfilter.html
I've personally have been using the 4-Share ZIP IFilter for over a year and
find it works very well for me.
As for "storing documents in the DB versus on the file system", this is very
much a open and very actively discussed subject, below is one past posting
I've made on this subject with links included...
"There are many issues related to storing the files on disk (faster access,
easy copy/move, etc.) vs. storing the files in the database (transaction
control, change control, audit of changes, etc) than just efficiency and
scalability, although, those are important points... There is a ASPFAQ
website that has a number of references
(http://www.aspfaq.com/show.asp?id=2149) and links to KB articles (I've not
checked them all), but one major reason for storing the files in a SQL
Server table's column defined with an IMAGE datatype, is that in SQL Server
2000 you can take advantage of the new Full-Text Search (FTS) feature to FT
Index the contents of supported file types, primarily MS Office, HTML and
3rd party IFilter's like Adobe's PDF files.
As this is one of those FAQ type questions that have been known to start
religious wars or at the very least a flame war, and while Sharon didn't ask
about other application issues that this question often generates as it is
often an open-ended question, i.e., one that never seems to be answered to
everyone's satisfactions as it usually "depends" upon the application or
upon how one defines the word "best"...
As for the Terra Server, checkout the "about page"
(http://terraserver-usa.com/about.aspx?n=AboutBody) on the site that
explains how MS and others did it and yes, they used the IMAGE datatype,
specifically
http://terraserver-usa.com/about.asp...utTechDbschema (The Imagery table
contains the "blob" [image] field where the imagery data is stored.
Jose, depending upon how many files you have, how frequently they change,
how large they are, what the app is, etc. you may want to review the Terra
Server web site and consider the following rule of thumb that they used:
< 1 million images or big images (> 1MB) put them in the file system.
> 1 million and < 1 MB images, put them in SQL Server.
Note, you can also use "Text-in-Row" if the files are >7000 bytes on avg.
For everything in between, either way will work, depending upon if you need
transactional control over your files. Additionally, if you store the files
in a TEXT or IMAGE column, you can also store related metadata about that
file in SQL Server as well for increased searching capabilities. Also, and
obviously with SQL Server you get built-in support for validating the
consistency of the database, indices, backup, restore, etc. As for loading
&/or extracting the files from SQL Server there are now many methods of
doing this via BCP, BULK INSERT as well as ADO Stream DTS too, if you need
to transform your files in some manner.
Hope this helps, as the primary consideration should always be what is best
for your application...
Regards,
John
"Jose Perez" <jlpv@.totalise.co.uk> wrote in message
news:3724a9d9.0404141212.480e1630@.posting.google.c om...
> Hi everyone,
> I am developing a web app and wanting to store documents (doc, xls,
> pdf, ppt...etc.) within the DB (MSSQL). Is it advisable in general (I
> don't know exactly how big the docs are going to be) to compress the
> files before putting them into the DB? How does this affect Full Text
> Searching? On a wider note what's the attitude of people in terms of
> storing documents in the DB versus on the file system. There seems to
> be a lot of differing opinions. Any links to resources would be most
> appreciated.
> Thanks for your help in advance.
> Jose
|||John,
Thanks a lot for the detailed reply which you gave - it is most
appreciated. In answer to your question, yes I am running SQL Server
2000 (Version Information - Microsoft SQL Server 2000 - 8.00.760
(Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft
Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)).
To clarify, I meant storing documents as "ZIP files in columns defined
with the IMAGE datatype". Thanks for the URLs regarding the ZIP
IFilter - I will investigate them. Your post was very helpful -
thanks.
Jose
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#qtcv8pIEHA.964@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Jose,
> First of all, can I assume that you are using SQL Server 2000? If so, could
> you post the full output of: SELECT @.@.version -- as this is very help info
> in understanding SQL FTS issues.
> Secondly, what exactly do you mean by "compress[ing] the files before
> putting them into the DB?" Do you mean to store them as ZIP files in columns
> defined with the IMAGE datatype? or are you thinking about placing the FT
> Catalog folder (SQL00060005) & files on a compressed drive. As for the
> latter, I've not done any testing with compressed drive, but Hilary Cotter
> has and from past postings on this subject, he's indicated that the
> performance gains is mimumal. As for the former, you will need to purchase a
> 3rd Party IFilter for the WinZIP ZIP file format and then assuming you are
> using SQL Server 2000, you can store the zip files in an IMAGE datatype and
> define a "file extension" column that will have the value of "zip" and the
> ZIP IFilter will be called by the MSSearch and the mssdmn (Search Filter
> Daemon) processes to FT Index the contents of the zip file that contains MS
> Word documents. You can purchase the ZIP IFilter from either of the
> following two vendors:
> http://www.4-share.com/content/products.htm
> http://www.ifiltershop.com/zipfilter.html
> I've personally have been using the 4-Share ZIP IFilter for over a year and
> find it works very well for me.
> As for "storing documents in the DB versus on the file system", this is very
> much a open and very actively discussed subject, below is one past posting
> I've made on this subject with links included...
> "There are many issues related to storing the files on disk (faster access,
> easy copy/move, etc.) vs. storing the files in the database (transaction
> control, change control, audit of changes, etc) than just efficiency and
> scalability, although, those are important points... There is a ASPFAQ
> website that has a number of references
> (http://www.aspfaq.com/show.asp?id=2149) and links to KB articles (I've not
> checked them all), but one major reason for storing the files in a SQL
> Server table's column defined with an IMAGE datatype, is that in SQL Server
> 2000 you can take advantage of the new Full-Text Search (FTS) feature to FT
> Index the contents of supported file types, primarily MS Office, HTML and
> 3rd party IFilter's like Adobe's PDF files.
> As this is one of those FAQ type questions that have been known to start
> religious wars or at the very least a flame war, and while Sharon didn't ask
> about other application issues that this question often generates as it is
> often an open-ended question, i.e., one that never seems to be answered to
> everyone's satisfactions as it usually "depends" upon the application or
> upon how one defines the word "best"...
> As for the Terra Server, checkout the "about page"
> (http://terraserver-usa.com/about.aspx?n=AboutBody) on the site that
> explains how MS and others did it and yes, they used the IMAGE datatype,
> specifically
> http://terraserver-usa.com/about.asp...utTechDbschema (The Imagery table
> contains the "blob" [image] field where the imagery data is stored.
> Jose, depending upon how many files you have, how frequently they change,
> how large they are, what the app is, etc. you may want to review the Terra
> Server web site and consider the following rule of thumb that they used:
> < 1 million images or big images (> 1MB) put them in the file system.
> Note, you can also use "Text-in-Row" if the files are >7000 bytes on avg.
> For everything in between, either way will work, depending upon if you need
> transactional control over your files. Additionally, if you store the files
> in a TEXT or IMAGE column, you can also store related metadata about that
> file in SQL Server as well for increased searching capabilities. Also, and
> obviously with SQL Server you get built-in support for validating the
> consistency of the database, indices, backup, restore, etc. As for loading
> &/or extracting the files from SQL Server there are now many methods of
> doing this via BCP, BULK INSERT as well as ADO Stream DTS too, if you need
> to transform your files in some manner.
> Hope this helps, as the primary consideration should always be what is best
> for your application...
> Regards,
> John
>
> "Jose Perez" <jlpv@.totalise.co.uk> wrote in message
> news:3724a9d9.0404141212.480e1630@.posting.google.c om...
I am developing a web app and wanting to store documents (doc, xls,
pdf, ppt...etc.) within the DB (MSSQL). Is it advisable in general (I
don't know exactly how big the docs are going to be) to compress the
files before putting them into the DB? How does this affect Full Text
Searching? On a wider note what's the attitude of people in terms of
storing documents in the DB versus on the file system. There seems to
be a lot of differing opinions. Any links to resources would be most
appreciated.
Thanks for your help in advance.
Jose
Jose,
First of all, can I assume that you are using SQL Server 2000? If so, could
you post the full output of: SELECT @.@.version -- as this is very help info
in understanding SQL FTS issues.
Secondly, what exactly do you mean by "compress[ing] the files before
putting them into the DB?" Do you mean to store them as ZIP files in columns
defined with the IMAGE datatype? or are you thinking about placing the FT
Catalog folder (SQL00060005) & files on a compressed drive. As for the
latter, I've not done any testing with compressed drive, but Hilary Cotter
has and from past postings on this subject, he's indicated that the
performance gains is mimumal. As for the former, you will need to purchase a
3rd Party IFilter for the WinZIP ZIP file format and then assuming you are
using SQL Server 2000, you can store the zip files in an IMAGE datatype and
define a "file extension" column that will have the value of "zip" and the
ZIP IFilter will be called by the MSSearch and the mssdmn (Search Filter
Daemon) processes to FT Index the contents of the zip file that contains MS
Word documents. You can purchase the ZIP IFilter from either of the
following two vendors:
http://www.4-share.com/content/products.htm
http://www.ifiltershop.com/zipfilter.html
I've personally have been using the 4-Share ZIP IFilter for over a year and
find it works very well for me.
As for "storing documents in the DB versus on the file system", this is very
much a open and very actively discussed subject, below is one past posting
I've made on this subject with links included...
"There are many issues related to storing the files on disk (faster access,
easy copy/move, etc.) vs. storing the files in the database (transaction
control, change control, audit of changes, etc) than just efficiency and
scalability, although, those are important points... There is a ASPFAQ
website that has a number of references
(http://www.aspfaq.com/show.asp?id=2149) and links to KB articles (I've not
checked them all), but one major reason for storing the files in a SQL
Server table's column defined with an IMAGE datatype, is that in SQL Server
2000 you can take advantage of the new Full-Text Search (FTS) feature to FT
Index the contents of supported file types, primarily MS Office, HTML and
3rd party IFilter's like Adobe's PDF files.
As this is one of those FAQ type questions that have been known to start
religious wars or at the very least a flame war, and while Sharon didn't ask
about other application issues that this question often generates as it is
often an open-ended question, i.e., one that never seems to be answered to
everyone's satisfactions as it usually "depends" upon the application or
upon how one defines the word "best"...
As for the Terra Server, checkout the "about page"
(http://terraserver-usa.com/about.aspx?n=AboutBody) on the site that
explains how MS and others did it and yes, they used the IMAGE datatype,
specifically
http://terraserver-usa.com/about.asp...utTechDbschema (The Imagery table
contains the "blob" [image] field where the imagery data is stored.
Jose, depending upon how many files you have, how frequently they change,
how large they are, what the app is, etc. you may want to review the Terra
Server web site and consider the following rule of thumb that they used:
< 1 million images or big images (> 1MB) put them in the file system.
> 1 million and < 1 MB images, put them in SQL Server.
Note, you can also use "Text-in-Row" if the files are >7000 bytes on avg.
For everything in between, either way will work, depending upon if you need
transactional control over your files. Additionally, if you store the files
in a TEXT or IMAGE column, you can also store related metadata about that
file in SQL Server as well for increased searching capabilities. Also, and
obviously with SQL Server you get built-in support for validating the
consistency of the database, indices, backup, restore, etc. As for loading
&/or extracting the files from SQL Server there are now many methods of
doing this via BCP, BULK INSERT as well as ADO Stream DTS too, if you need
to transform your files in some manner.
Hope this helps, as the primary consideration should always be what is best
for your application...
Regards,
John
"Jose Perez" <jlpv@.totalise.co.uk> wrote in message
news:3724a9d9.0404141212.480e1630@.posting.google.c om...
> Hi everyone,
> I am developing a web app and wanting to store documents (doc, xls,
> pdf, ppt...etc.) within the DB (MSSQL). Is it advisable in general (I
> don't know exactly how big the docs are going to be) to compress the
> files before putting them into the DB? How does this affect Full Text
> Searching? On a wider note what's the attitude of people in terms of
> storing documents in the DB versus on the file system. There seems to
> be a lot of differing opinions. Any links to resources would be most
> appreciated.
> Thanks for your help in advance.
> Jose
|||John,
Thanks a lot for the detailed reply which you gave - it is most
appreciated. In answer to your question, yes I am running SQL Server
2000 (Version Information - Microsoft SQL Server 2000 - 8.00.760
(Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft
Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)).
To clarify, I meant storing documents as "ZIP files in columns defined
with the IMAGE datatype". Thanks for the URLs regarding the ZIP
IFilter - I will investigate them. Your post was very helpful -
thanks.
Jose
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#qtcv8pIEHA.964@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Jose,
> First of all, can I assume that you are using SQL Server 2000? If so, could
> you post the full output of: SELECT @.@.version -- as this is very help info
> in understanding SQL FTS issues.
> Secondly, what exactly do you mean by "compress[ing] the files before
> putting them into the DB?" Do you mean to store them as ZIP files in columns
> defined with the IMAGE datatype? or are you thinking about placing the FT
> Catalog folder (SQL00060005) & files on a compressed drive. As for the
> latter, I've not done any testing with compressed drive, but Hilary Cotter
> has and from past postings on this subject, he's indicated that the
> performance gains is mimumal. As for the former, you will need to purchase a
> 3rd Party IFilter for the WinZIP ZIP file format and then assuming you are
> using SQL Server 2000, you can store the zip files in an IMAGE datatype and
> define a "file extension" column that will have the value of "zip" and the
> ZIP IFilter will be called by the MSSearch and the mssdmn (Search Filter
> Daemon) processes to FT Index the contents of the zip file that contains MS
> Word documents. You can purchase the ZIP IFilter from either of the
> following two vendors:
> http://www.4-share.com/content/products.htm
> http://www.ifiltershop.com/zipfilter.html
> I've personally have been using the 4-Share ZIP IFilter for over a year and
> find it works very well for me.
> As for "storing documents in the DB versus on the file system", this is very
> much a open and very actively discussed subject, below is one past posting
> I've made on this subject with links included...
> "There are many issues related to storing the files on disk (faster access,
> easy copy/move, etc.) vs. storing the files in the database (transaction
> control, change control, audit of changes, etc) than just efficiency and
> scalability, although, those are important points... There is a ASPFAQ
> website that has a number of references
> (http://www.aspfaq.com/show.asp?id=2149) and links to KB articles (I've not
> checked them all), but one major reason for storing the files in a SQL
> Server table's column defined with an IMAGE datatype, is that in SQL Server
> 2000 you can take advantage of the new Full-Text Search (FTS) feature to FT
> Index the contents of supported file types, primarily MS Office, HTML and
> 3rd party IFilter's like Adobe's PDF files.
> As this is one of those FAQ type questions that have been known to start
> religious wars or at the very least a flame war, and while Sharon didn't ask
> about other application issues that this question often generates as it is
> often an open-ended question, i.e., one that never seems to be answered to
> everyone's satisfactions as it usually "depends" upon the application or
> upon how one defines the word "best"...
> As for the Terra Server, checkout the "about page"
> (http://terraserver-usa.com/about.aspx?n=AboutBody) on the site that
> explains how MS and others did it and yes, they used the IMAGE datatype,
> specifically
> http://terraserver-usa.com/about.asp...utTechDbschema (The Imagery table
> contains the "blob" [image] field where the imagery data is stored.
> Jose, depending upon how many files you have, how frequently they change,
> how large they are, what the app is, etc. you may want to review the Terra
> Server web site and consider the following rule of thumb that they used:
> < 1 million images or big images (> 1MB) put them in the file system.
> Note, you can also use "Text-in-Row" if the files are >7000 bytes on avg.
> For everything in between, either way will work, depending upon if you need
> transactional control over your files. Additionally, if you store the files
> in a TEXT or IMAGE column, you can also store related metadata about that
> file in SQL Server as well for increased searching capabilities. Also, and
> obviously with SQL Server you get built-in support for validating the
> consistency of the database, indices, backup, restore, etc. As for loading
> &/or extracting the files from SQL Server there are now many methods of
> doing this via BCP, BULK INSERT as well as ADO Stream DTS too, if you need
> to transform your files in some manner.
> Hope this helps, as the primary consideration should always be what is best
> for your application...
> Regards,
> John
>
> "Jose Perez" <jlpv@.totalise.co.uk> wrote in message
> news:3724a9d9.0404141212.480e1630@.posting.google.c om...
compressed backups
Is there any way to automate compression of timed database backups from a
T-SQL script? I don't see any compression options for the BACKUP command in
BOL. Something analogous to the -Fc option for pg_dump in PostgreSQL.
A timed command-line batch file using a file compression program could be
set to execute after the script, but the large data file would have to be
created first.
Thanks,
David P. Lurie
David,
There's nothing inside SQL Server to do compression. You could execute a command after the backup that uses
ZIP or similar to do compression. Or use SQL Lite Speed (probably misspelled), which uses an extended stored
procedure to do backup, and this does compression.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David P. Lurie" <abc@.def.net> wrote in message news:%23IaE$vFKEHA.3628@.TK2MSFTNGP12.phx.gbl...
> Is there any way to automate compression of timed database backups from a
> T-SQL script? I don't see any compression options for the BACKUP command in
> BOL. Something analogous to the -Fc option for pg_dump in PostgreSQL.
> A timed command-line batch file using a file compression program could be
> set to execute after the script, but the large data file would have to be
> created first.
> Thanks,
> David P. Lurie
>
T-SQL script? I don't see any compression options for the BACKUP command in
BOL. Something analogous to the -Fc option for pg_dump in PostgreSQL.
A timed command-line batch file using a file compression program could be
set to execute after the script, but the large data file would have to be
created first.
Thanks,
David P. Lurie
David,
There's nothing inside SQL Server to do compression. You could execute a command after the backup that uses
ZIP or similar to do compression. Or use SQL Lite Speed (probably misspelled), which uses an extended stored
procedure to do backup, and this does compression.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David P. Lurie" <abc@.def.net> wrote in message news:%23IaE$vFKEHA.3628@.TK2MSFTNGP12.phx.gbl...
> Is there any way to automate compression of timed database backups from a
> T-SQL script? I don't see any compression options for the BACKUP command in
> BOL. Something analogous to the -Fc option for pg_dump in PostgreSQL.
> A timed command-line batch file using a file compression program could be
> set to execute after the script, but the large data file would have to be
> created first.
> Thanks,
> David P. Lurie
>
Subscribe to:
Posts (Atom)