Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>
|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>
|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
sql
Showing posts with label seperate. Show all posts
Showing posts with label seperate. Show all posts
Monday, March 26, 2012
Images and PDF's in the same DB or in another DB?
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertSame DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertSame DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
Images and PDF's in the same DB or in another DB?
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertThis is a multi-part message in MIME format.
--040906000104030808000509
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>
--040906000104030808000509
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Same DB.<br>
<br>
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).<br>
<br>
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up
& restore the image data separately to the data in all your other
tables, which may or may not be handy for you).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
robert madrian wrote:
<blockquote cite="miduJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
</pre>
</blockquote>
</body>
</html>
--040906000104030808000509--|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertThis is a multi-part message in MIME format.
--040906000104030808000509
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>
--040906000104030808000509
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Same DB.<br>
<br>
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).<br>
<br>
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up
& restore the image data separately to the data in all your other
tables, which may or may not be handy for you).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
robert madrian wrote:
<blockquote cite="miduJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
</pre>
</blockquote>
</body>
</html>
--040906000104030808000509--|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
Wednesday, March 21, 2012
Image Error In SQL
I have a seperate table that holds employees pictures in it and for some reason I am getting an error all the sudden when I try to insert a picture. I never got that before.. Can anyone help please??It would appear that your image size has grown roughly eight times what it was (or you've added replication, or possibly you changed to a new SQL Server).
The fix is easy, but PLEASE think about this before you make the change... This can have relatively far reaching implications to replication performance.
The problem is the default size limit for replicated data. Your machine appears to have the default setting of 65536 bytes per replicated column. This only affects TEXT and IMAGE columns, since nothing else can be large enough to have problems with that setting. IF you decide to change this setting, the code snippet you need is:EXECUTE sp_configure 'max text repl size (B)', 655360 -- or pick another size to suit you-PatP|||I had forgotten that our techs are moving SQL server and our scanned documents over to its own server and one of the databases I created is there.
Ok Now I understand thank you again
The fix is easy, but PLEASE think about this before you make the change... This can have relatively far reaching implications to replication performance.
The problem is the default size limit for replicated data. Your machine appears to have the default setting of 65536 bytes per replicated column. This only affects TEXT and IMAGE columns, since nothing else can be large enough to have problems with that setting. IF you decide to change this setting, the code snippet you need is:EXECUTE sp_configure 'max text repl size (B)', 655360 -- or pick another size to suit you-PatP|||I had forgotten that our techs are moving SQL server and our scanned documents over to its own server and one of the databases I created is there.
Ok Now I understand thank you again
Friday, March 9, 2012
IIS Virtual Directory for SQL
On a Windows 2000 Server (sp4) (Server A), I have SQL server 2000 (sql sp4)
and sqlxml 3.0 SP3 installed.
On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
Management for SQL Server Utility to set up a virtual directory pointing to
the northwind db on Server A.
The following query works - but only with basic authenticatiohn -
http://serverA:8080/nwind?sql=select * from categories for xml auto&root=data
With SQL authentication (same credentials) I get the following error:
Invalid connection settings: access denied
Any clues?
Thanks in advance
Any clues
Hi
Maybe
http://support.microsoft.com/default...b;en-us;820874
John
"drmcj" <drmcj@.discussions.microsoft.com> wrote in message
news:2AD2919F-18A8-4D41-9CFD-AB41485D557D@.microsoft.com...
> On a Windows 2000 Server (sp4) (Server A), I have SQL server 2000 (sql
> sp4)
> and sqlxml 3.0 SP3 installed.
> On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
> Management for SQL Server Utility to set up a virtual directory pointing
> to
> the northwind db on Server A.
> The following query works - but only with basic authenticatiohn -
> http://serverA:8080/nwind?sql=select * from categories for xml
> auto&root=data
> With SQL authentication (same credentials) I get the following error:
> Invalid connection settings: access denied
> Any clues?
> Thanks in advance
> Any clues
and sqlxml 3.0 SP3 installed.
On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
Management for SQL Server Utility to set up a virtual directory pointing to
the northwind db on Server A.
The following query works - but only with basic authenticatiohn -
http://serverA:8080/nwind?sql=select * from categories for xml auto&root=data
With SQL authentication (same credentials) I get the following error:
Invalid connection settings: access denied
Any clues?
Thanks in advance
Any clues
Hi
Maybe
http://support.microsoft.com/default...b;en-us;820874
John
"drmcj" <drmcj@.discussions.microsoft.com> wrote in message
news:2AD2919F-18A8-4D41-9CFD-AB41485D557D@.microsoft.com...
> On a Windows 2000 Server (sp4) (Server A), I have SQL server 2000 (sql
> sp4)
> and sqlxml 3.0 SP3 installed.
> On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
> Management for SQL Server Utility to set up a virtual directory pointing
> to
> the northwind db on Server A.
> The following query works - but only with basic authenticatiohn -
> http://serverA:8080/nwind?sql=select * from categories for xml
> auto&root=data
> With SQL authentication (same credentials) I get the following error:
> Invalid connection settings: access denied
> Any clues?
> Thanks in advance
> Any clues
IIS Virtual Directory for SQL
On a Windows 2000 Server (sp4) (Server A), I have SQL server 2000 (sql sp4)
and sqlxml 3.0 SP3 installed.
On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
Management for SQL Server Utility to set up a virtual directory pointing to
the northwind db on Server A.
The following query works - but only with basic authenticatiohn -
http://serverA:8080/nwind?sql=select * from categories for xml auto&root=data
With SQL authentication (same credentials) I get the following error:
Invalid connection settings: access denied
Any clues?
Thanks in advance
Any cluesHi
Maybe
http://support.microsoft.com/default.aspx?scid=kb;en-us;820874
John
"drmcj" <drmcj@.discussions.microsoft.com> wrote in message
news:2AD2919F-18A8-4D41-9CFD-AB41485D557D@.microsoft.com...
> On a Windows 2000 Server (sp4) (Server A), I have SQL server 2000 (sql
> sp4)
> and sqlxml 3.0 SP3 installed.
> On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
> Management for SQL Server Utility to set up a virtual directory pointing
> to
> the northwind db on Server A.
> The following query works - but only with basic authenticatiohn -
> http://serverA:8080/nwind?sql=select * from categories for xml
> auto&root=data
> With SQL authentication (same credentials) I get the following error:
> Invalid connection settings: access denied
> Any clues?
> Thanks in advance
> Any clues
and sqlxml 3.0 SP3 installed.
On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
Management for SQL Server Utility to set up a virtual directory pointing to
the northwind db on Server A.
The following query works - but only with basic authenticatiohn -
http://serverA:8080/nwind?sql=select * from categories for xml auto&root=data
With SQL authentication (same credentials) I get the following error:
Invalid connection settings: access denied
Any clues?
Thanks in advance
Any cluesHi
Maybe
http://support.microsoft.com/default.aspx?scid=kb;en-us;820874
John
"drmcj" <drmcj@.discussions.microsoft.com> wrote in message
news:2AD2919F-18A8-4D41-9CFD-AB41485D557D@.microsoft.com...
> On a Windows 2000 Server (sp4) (Server A), I have SQL server 2000 (sql
> sp4)
> and sqlxml 3.0 SP3 installed.
> On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
> Management for SQL Server Utility to set up a virtual directory pointing
> to
> the northwind db on Server A.
> The following query works - but only with basic authenticatiohn -
> http://serverA:8080/nwind?sql=select * from categories for xml
> auto&root=data
> With SQL authentication (same credentials) I get the following error:
> Invalid connection settings: access denied
> Any clues?
> Thanks in advance
> Any clues
IIS Virtual Directory for SQL
On a Windows 2000 Server (sp4) (Server A), I have SQL server 2000 (sql sp4)
and sqlxml 3.0 SP3 installed.
On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
Management for SQL Server Utility to set up a virtual directory pointing to
the northwind db on Server A.
The following query works - but only with basic authenticatiohn -
http://serverA:8080/nwind?sql=select * from categories for xml auto&root=dat
a
With SQL authentication (same credentials) I get the following error:
Invalid connection settings: access denied
Any clues?
Thanks in advance
Any cluesHi
Maybe
http://support.microsoft.com/defaul...kb;en-us;820874
John
"drmcj" <drmcj@.discussions.microsoft.com> wrote in message
news:2AD2919F-18A8-4D41-9CFD-AB41485D557D@.microsoft.com...
> On a Windows 2000 Server (sp4) (Server A), I have SQL server 2000 (sql
> sp4)
> and sqlxml 3.0 SP3 installed.
> On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
> Management for SQL Server Utility to set up a virtual directory pointing
> to
> the northwind db on Server A.
> The following query works - but only with basic authenticatiohn -
> http://serverA:8080/nwind?sql=select * from categories for xml
> auto&root=data
> With SQL authentication (same credentials) I get the following error:
> Invalid connection settings: access denied
> Any clues?
> Thanks in advance
> Any clues
and sqlxml 3.0 SP3 installed.
On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
Management for SQL Server Utility to set up a virtual directory pointing to
the northwind db on Server A.
The following query works - but only with basic authenticatiohn -
http://serverA:8080/nwind?sql=select * from categories for xml auto&root=dat
a
With SQL authentication (same credentials) I get the following error:
Invalid connection settings: access denied
Any clues?
Thanks in advance
Any cluesHi
Maybe
http://support.microsoft.com/defaul...kb;en-us;820874
John
"drmcj" <drmcj@.discussions.microsoft.com> wrote in message
news:2AD2919F-18A8-4D41-9CFD-AB41485D557D@.microsoft.com...
> On a Windows 2000 Server (sp4) (Server A), I have SQL server 2000 (sql
> sp4)
> and sqlxml 3.0 SP3 installed.
> On a seperate Windows 2003 (Server B) server I used IIS Virtual Directory
> Management for SQL Server Utility to set up a virtual directory pointing
> to
> the northwind db on Server A.
> The following query works - but only with basic authenticatiohn -
> http://serverA:8080/nwind?sql=select * from categories for xml
> auto&root=data
> With SQL authentication (same credentials) I get the following error:
> Invalid connection settings: access denied
> Any clues?
> Thanks in advance
> Any clues
Wednesday, March 7, 2012
IIS and SQL Server on separate machine
Hi,
Can anybody please help me how to set up a conncetion between IIS & SQL
Server on seperate machine.
1. Domain is same
2. I've tried as per the article 176380, but we are facing some problem on
IIS. So my Network manager said I need to try something else.
Please let me know the steps that I have to do.
Thanks in Advance.
Kavi
"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
news:B3583882-BE7C-4419-8269-F8F8CA1EF44C@.microsoft.com...
> Can anybody please help me how to set up a conncetion between IIS & SQL
> Server on seperate machine.
> 1. Domain is same
> 2. I've tried as per the article 176380, but we are facing some problem on
> IIS. So my Network manager said I need to try something else.
What's the problem? And, what have you tried so far? More detail helps!
Steve
|||Hi Steve,
OK the following steps I've done.
1. Created a user account on the Domain Controller called
IUSR_MY_WEB.
2. Added this user account to the Guest Group of both SQL
Server & the IIS Machine.
3. On the computer where IIS is running the aninymous
login, the user name is set up as IUSR_MY_WEB
OK, the following step, I don't understand (What the
article 176380 says)
"On the computer that is hosting SQL Server, use the
Security manager to map the IUSR_MY_WEB account to an
existing user logon on SQL Server. For more Info on how
to do this, consult the SQL Server Documentation"
I tried to find more info from the Documentation & I'm
not successful.
What I did was I created a USER LOGIN in SQL Server as
DOMAIN\IUSR_MY_WEB.
In the ASP Program, I've used the OLE-DB Provider as
follows:
Dim oConn: Set oConn=Server.CreateObject
("ADODB.Connection")
'oConn.Open "Driver={SQL
Server};Server=SQLSERVERMACHINENAME;Network=DBMSSO CN;Datab
ase=MYDBASE;Uid=DOMAIN\IUSR_MY_WEB;Pwd="
Even I tried with IP address also.
Hope this info will gives you a clear picture, what am I
doing wrong?
Thanks.
>--Original Message--
>"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
>news:B3583882-BE7C-4419-8269-
F8F8CA1EF44C@.microsoft.com...[vbcol=seagreen]
between IIS & SQL[vbcol=seagreen]
facing some problem on[vbcol=seagreen]
something else.
>What's the problem? And, what have you tried so far?
More detail helps!
>Steve
>
>.
>
Can anybody please help me how to set up a conncetion between IIS & SQL
Server on seperate machine.
1. Domain is same
2. I've tried as per the article 176380, but we are facing some problem on
IIS. So my Network manager said I need to try something else.
Please let me know the steps that I have to do.
Thanks in Advance.
Kavi
"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
news:B3583882-BE7C-4419-8269-F8F8CA1EF44C@.microsoft.com...
> Can anybody please help me how to set up a conncetion between IIS & SQL
> Server on seperate machine.
> 1. Domain is same
> 2. I've tried as per the article 176380, but we are facing some problem on
> IIS. So my Network manager said I need to try something else.
What's the problem? And, what have you tried so far? More detail helps!
Steve
|||Hi Steve,
OK the following steps I've done.
1. Created a user account on the Domain Controller called
IUSR_MY_WEB.
2. Added this user account to the Guest Group of both SQL
Server & the IIS Machine.
3. On the computer where IIS is running the aninymous
login, the user name is set up as IUSR_MY_WEB
OK, the following step, I don't understand (What the
article 176380 says)
"On the computer that is hosting SQL Server, use the
Security manager to map the IUSR_MY_WEB account to an
existing user logon on SQL Server. For more Info on how
to do this, consult the SQL Server Documentation"
I tried to find more info from the Documentation & I'm
not successful.
What I did was I created a USER LOGIN in SQL Server as
DOMAIN\IUSR_MY_WEB.
In the ASP Program, I've used the OLE-DB Provider as
follows:
Dim oConn: Set oConn=Server.CreateObject
("ADODB.Connection")
'oConn.Open "Driver={SQL
Server};Server=SQLSERVERMACHINENAME;Network=DBMSSO CN;Datab
ase=MYDBASE;Uid=DOMAIN\IUSR_MY_WEB;Pwd="
Even I tried with IP address also.
Hope this info will gives you a clear picture, what am I
doing wrong?
Thanks.
>--Original Message--
>"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
>news:B3583882-BE7C-4419-8269-
F8F8CA1EF44C@.microsoft.com...[vbcol=seagreen]
between IIS & SQL[vbcol=seagreen]
facing some problem on[vbcol=seagreen]
something else.
>What's the problem? And, what have you tried so far?
More detail helps!
>Steve
>
>.
>
Subscribe to:
Posts (Atom)