I wanted to ask a question in terms of design (Performance, scalability, maintenance, flexibility etc.) is it better to refernce a document on the actual file system (on a file server) i.e. from a database link to the actual file at a disk location
e.g. in the database have something like \fileserver2006 est
ewtext.txt or C: est
ewtext.txt
or is it better to store all the files in a database as blobs?
The Question i would like to ask is more in the context of desigining a document management system?
I have a table with embedded files in it and also ftp server/user/pwd fields so that each record has all the data and information required and need to configure an SSIS package to to FTP them to the destination as specified in the record within the databaseAt the moment I have a data flow task which uses a Derived Column and an Export Column transformation which creates the files to a temp folder then it runs a ForEach loop container back on the control flow to FTP each file and to then move each file to an archive folder.
All I think I need to know how to do is to somehow control the FTP connection settings with the fields from the database within this foreach loop, but it is a for each file collection. I also need to update each record saying it has been FTP'd as well Do I need to have an initial query outside of this foreach loop to populate an array which can be used for the 4 FTP connection fields (server/user/pwd/directory), if so how?
Any ideas as to how to correctly finish this package.
It is obvious that putting multiple database files on different physical disk is better for performance, but what about splitting the data on different files on the same disk?
I have got a database of about 20GB and only a single data file. will I benefit from splitting this file to multiple files on the same disk?
I have a sql server 7 running on a machine with two disk partitions (D: and E:).
The data files xxx.mdf and xxx.ldf are stored in D:, which has very few space available. I want to copy these files to E: but I get an error saying that it is not possible to change the source file of a database. Is it possible to do it or do i have to create another data file in E: and keep the old one in D:?
Hello, I have been having a bit of trouble finding help on the safestway to move data files to a different disk on the same server. Mosthelp is about moving data files to a different sqlserver. I just wantto move the files to a different drive on the same server. Any helpwould be appreciated.Thanks,David
Hello -- I'm building an app that will allow users to create their own photo galleries. At this point, I'm planning on storing all photos as byte arrays in SQL server image fields. Besides the organizational benefit, is there a space benefit to doing this? That is, if I have 1MB of .jpg's, will those same images take up less than 1MB of file space within the database? One of the reasons I ask is that most hosting plans out there seem to offer more "normal" disk space than is allocated for the database, so I'm trying to make a best plan to accommodate what will probably end up being the biggest disk space consumer in my app (the photos, that is). Any other recommendations re: this scenario (hosting, best practices) are appreciated. TIA, Eric
I tried samples of FileTable in SQL Server 2012 to store files in database. I have following questions.
1. When I right click on FileTable, and say Explore File Table Directory, I was taken to a directory. But if I search the same directory in my server/machine, the directory is not available.
2. Can I use some external server/disk as a directory for FileTable?
3. How easy is to use FileTables using .Net web applications?
hello,all I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) . is it possible to do it ? if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ? thanks in advanced!
If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
-- Initialize Control Mechanism DECLARE@Drive TINYINT, @SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area DECLARE@Drives TABLE ( Drive CHAR(1), Info VARCHAR(80) )
WHILE @Drive <= 122 BEGIN SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives ( Info ) EXEC(@SQL)
UPDATE@Drives SETDrive = CHAR(@Drive) WHEREDrive IS NULL
SET@Drive = @Drive + 1 END
-- Show the expected output SELECTDrive, SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes, SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes, SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes FROM( SELECTDrive, Info FROM@Drives WHEREInfo LIKE 'Total # of %' ) AS d GROUP BYDrive ORDER BYDrive
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
I have searched high and low for this answer, but still to no avail...
Does anyone have a reliable way of storing serialized data into a table which does not use direct SQL on the client side? I am using Stored Procedures and simply need to be able to read out of the table the object written in.
Inserting appears fine, but reading only seems to come back with 16 bytes of data and I just can't figure out why. Whats the recommended way of doing this?
Hello to all, Please help me to get a right decision: storing BLOBs 5M in database or using URL. I saw a lot of articles about this matter and really cannot decide. The database size is about 35 M, not too much DDL operations. Advises?
Any one got any tricks on speeding up storing and retrieving image data? I have a typical 2MB image and I am trying to improve speed... it now takes 8 seconds to store the blob.
Help! Where building a document mgmt system that will store MSword documents, etc in SQL Server. When we BCP a table which contains image data to another copy of the table, the image data seems to get clobbered and is not longer valid. Has anyone had this problem
We have a blob in one table that is storing pdf files. I need towrite a select query that will grab that column and write those *.pdffiles out to a location on my hard drive. Does anyone know how thatcan be done? All suggestions would be greatly appreciated....
I have a DB where I store alot of Blobs, the blobs are tagged up with alot of id. My concern is deleting them automatically on a regular basis, too keep the DB size down. I will be deleting them based on based on age(date/time) and what category the blob is classed as.
- My concern is how to delete blobs sort of equally dispersed among the categories so no category ends up empty although it may have old pictures in it.
Any tips, links or code is much welcome.
(my app will be acessing the DB though ADO .NET 3.0 c++/cli)
I want to store message database in SQLEv. Each message contains fixed header and a body. Body is UTF-16 encoded text. Average size of the body is about 700 symbols. Standard deviation about 1000 symbols. Maximum size - 200K symbols. Total count of the messages is up to 2 millions.
The only type I can use for body is text or binary blob. But blob larger than 256 bytes store in separate pages. Because of most message bodies lager than 256 bytes, each message take avg. PageSize / 2 not used for storing data, but occupied space.
For 2M messages this space will be 4096 / 2 * 2M = 4G. But SQLEv has 4G limit for database size!
Is it any way to store such data in SQLEv more efficiently?
I'm trying to store files such as pdfs in my SQL Server as blobs for a particular .NET application and I'm noticing a rather large performance hit when compared to if the pdf was simply stored in the web server's file system.
I have a pdf of 7MB. If this is on a web server it takes roughly 9 seconds to download. When I store the same pdf in my database and then write it onto the page using the code below it takes roughly 40 seconds to display. Can anyone help me to improve this performance difference? Should I not be using this method to store large files?
Dim PdfCol As Integer = 0 ' the column # of the BLOB field Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.Read() Then Dim mypdf(dr.GetBytes(PdfCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte dr.GetBytes(PdfCol, 0, mypdf, 0, mypdf.Length) End If
I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.
So my options as I understand it are:
Get Enterprise edition
or
Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.
I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.
Does anyone know how much performance increase I will get from using two arrays as opposed to one?
Any other advice on this scenario would be greatly appreciated.
I'm clear about the use of a DELETE trigger to "move" your deleted record to a second database as a sort of recycle bin.
But SS7 has the limitation, and it's mentioned in BOL, that it cannot reference your TEXT, NTEXT or IMAGE fields in the DELETED table. It says to join the original table with DELETED to get at those fields.
The only problem is the original table's record has been deleted! Even though the transaction has not yet been COMMITTED.
Here's my Trigger:
CREATE TRIGGER AuditTest ON Activity FOR DELETE AS
INSERT AuditDB.dbo.Activity SELECT Activity.* FROM Activity INNER JOIN Deleted ON Activity.ActivityID = Deleted.ActivityID
And for discussion, here's my Table:
ActivityID uniqueidentifier OrgId uniqueidentifier Title varchar(600 Active bit Comments text LastUpdate datetime
Any suggestions? Has anyone been able to implement a DELETE Audit Trigger on a table with BLOBs?
Hey Guys I knwo this may sound impossible but lets say I have a number of fields one of which is a Long blob or long text
is there a way to have MYSQL search the blobs for keywords and then to extract them to other fields? basically what I am asking is it possible to parse a long text blob for keywords and then grab data before or after those keywords?/
I currently have a lot of XML blobs being stored in several of the production databases that I look after. They're there as a application "logging" feature, so that the developers can investigate issues if they ever occur.
I want to get these out of my OLTP databases asap and am currently looking at options. I know Brent Ozar recently posted an article about Content Addressable Storage systems [URL] ....
How do you handle point in time recovery? Any other ways of storing this data?
I've seen plenty of posts regarding the estimation of table size,usually in the processing of planning for server storage needs.Well, I've got a different problem. I need to know how much data eachof our Customers are using in a Database. (1 SQL DB stores multiplecustomers).Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:655 MB.So, how can I ask SQL Server how much data each Row in each table istaking up? I want to be able to calculate nightly the total size, so Iwould take each row in each table that belong to the customer, and addall the sizes together. I want to take into account blobs that arestoring images and PDF files also.Thanks in advance,Jesse Wolgamott
I am taking over a Database set up where we have 2 databases. One is a SQL SERVER 2000 db and the other is an Oracle 9i db.
THe previous DBA had/has a problem with transferring CLOB and Blob datatypes From the ORACLE db to SQL SERVER. He says that the OLEDB driver SQL SERVER provides has limitations particularly on the CLOB data because it truncates data over 4000 characters in length.
For the Blob issue: We basically want to store/move attachments (word documents etc) into image datatypes (SS) from the blob datatype (9i).
I figured I could do this using DTS or even using the Linked server method. I figured worst case scenario I would just store the documents on the server and then use SQL SERVER's textcopy utility to upload the documents into the image datatype column of the SQL SERVER table.
For the Clob Issue I have no idea what to do, because I cant find anything online that mentions SQL SERVER having issues with importing CLOB data over 4000 charcters in length, afterall the text datatype accomdates more than 4000 characters.
Does anyone have any suggestions for moving the Blob and most importantly the Clob datatypes from 9i to SQL SERVER without any truncation in the data????
Hi folks,I want to be able to store and retrieve UNSAVED Word documents as BLOBs. Igot all the info for storing them if they're already saved on the filesystem. But what if they're not already saved? I could save them to a tempfile first and then make it a blob, but I'd rather not put them on the filesystem at all. I could use Document.Contents to get the Range object for thewhole doc and then BLOB that, but I'm not sure that would be quite the sameas a .doc file, maybe things like Styles and the like would be missing.TIA!Dave
I got this error yesterday running an SSIS package:
"The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions."
This same package was working fine on a different server, so I compared the configurations of the two boxes. It turned out that the service account that runs the package was in the Windows Administrators group on one box but not the other. I added the service account to Administrators on the box that had the problem and the problem went away.
I have looked around and have not found a list of the necessary Windows permissions that an account must have in order to run an SSIS package. This list would include write permissions to the path specified in BLOBTempStoragePath. Has anyone out there configured an account with minimal permissions to run a package that has a Data Flow?
I'm trying to write SSIS packages to import DB2 6000 tables to SQL Server 2005 tables. The package vendor has defined CLOB and BLOB datatypes for columns in the DB2 tables, and going through the Data Flow tasks from OLE DB for DB2 to OLE DB for SQL Server, it tries to map CLOBs to TEXT columns, and BLOBs to IMAGE columns. The vendor has no conversion utility available, so I have to write the SSIS packages. However, the task generates a goodly number of errors just trying to open the OLE DB DB2 dataset. Errors like the following:
Error: 0xC0202009 at Set 5 1 2, DB2 vendor_master [7127]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC0208265 at Set 5 1 2, DB2 vendor_master [7127]: Failed to retrieve long data for column "COMPETITOR_COMMENT".
Error: 0xC020901C at Set 5 1 2, DB2 vendor_master [7127]: There was an error with output column "COMPETITOR_COMMENT" (8866) on output "OLE DB Source Output" (7138). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Set 5 1 2, DB2 vendor_master [7127]: The "output column "COMPETITOR_COMMENT" (8866)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "COMPETITOR_COMMENT" (8866)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047038 at Set 5 1 2, DTS.Pipeline: The PrimeOutput method on component "DB2 vendor_master" (7127) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Set 5 1 2, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Set 5 1 2, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Set 5 1 2, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Information: 0x40043008 at Set 5 1 2, DTS.Pipeline: Post Execute phase is beginning.
In this case, 'COMPTETITOR_COMMENT' is defined as a CLOB(2 M) column format in the DB2 database table, and the output format in the SQL Server 2005 table is defined as a TEXT column.
I've set up a development peer to peer replication between 2 SQL Server 2005 on 2 Windows XP SP2 systems.
The distribution agent is configured to use UseOledbStreaming. When saving smaller BLOBs of approx 10000 KB all works fine. When I try to replicate larger data I get the following error message in the Synchronisation status window and in the history of the distributor to the subscriber tab in the replication monitor:
Der Verteilungs-Agent verwendet die OLE DB-Streamingoptimierung für die Replikation von BLOB-Daten (Binary Large Objects) mit mehr als 16384 Bytes. Der Schwellenwert für die Optimierung kann über die Befehlszeile mithilfe des -OledbStreamThreshold-Parameters festgelegt werden. (Quelle: MSSQL_REPL, Fehlernummer: MSSQL_REPL21060) Hilfe abrufen: http://help/MSSQL_REPL21060
I also tried different values of OledbStreamThreshold.
I need to be able to replicate BLOBs in size of up to 1 GB.
I'm writing the BLOB data in chunks to the table DB_BLOBData , that isn't an article for replication and at the end copy it to the destination table MyBLOBTable which is an article. I do this, because I noticed that the transaction log on the subscriber database becomes incredibly huge (>60 GB for maybe 50 1000KB BLOBs written in blocks size of 65536 bytes) when directly updating the row using BLOB.Write(...). I think, that for each write at least the whole BLOB contents is written to the transaction log.
The replication uses Stored procedures, Update using SCALL syntax.
The code of the SP:
... IF @action = 1 BEGIN UPDATE MyBLOBTable SET DocContent = (SELECT BLOB from DB_BLOBData where GuidId = @id) , DocSize = (SELECT DATALENGTH(BLOB) from DB_BLOBData where GuidId = @id) WHERE DocId = @id DELETE FROM DB_BLOBData WHERE GuidId = @id END
IF @action = 0 BEGIN IF @Offset = 0 BEGIN INSERT DB_BLOBData( GuidId, BLOB ) VALUES ( @id, @value ) END ELSE BEGIN UPDATE DB_BLOBData SET BLOB.Write( @value , @Offset , @Length ) WHERE GuidId = @id END END
