Hey guys,
I was hoping some of you people could shed some light on something
that's been puzzling me this morning. I had a SQL 7 machine with a DB that
contained millions of images. It was on a dual 1GHz P3 box with 2 GB of ram
running Windows NT. This machine was really old and so mission critical no
one wanted to touch it. Anyway, the nightly backup of this DB was
approaching 15 hours and the size was approaching the 137gb limitation in
both NT and the BIOS. I convinced my work that to fix these 2 problems we
need to do 2 things.
1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
This will improve performance and upgrade a server that's in dire need of an
upgrade anyway and, most importantly, jump the 137gb hurdle.
2.) Move these 1 million plus images out of a DB structure and onto a
mirrored file structure. This will greatly reduce the backup time and the
actual size of the DB.
Well, I got the server...and I set it all up like I wanted except for
moving the images out of the DB. The DB backed up like normal last night
and I noticed that instead of 15 hours it successfully completed in 2.
Can the new servers speed really have been that effective in shortening
the backup time. I was expecting maybe a little faster...like from 15 hours
to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or should
I look into some problem with the backup? If this is correct I wouldn't
need to move the images into a file structure. From what I have read,
because these images are small and used on our web pages, a database
structure like we have currently would be the better choice.
If anyone can shed some light on why the huge difference in backup times
it would be much appreciated. Any suggestions on what I should do with
these images are welcome too.
Thanks,
--
-ScottHi
Windows 2003 would probably bring better IO performance as Microsoft did a
lot of work on it.
If your DB is not yet dived up into file groups, do so. Then you can backup
and restore a single file group. This will help a lot in a DR scenario.
I like to store Images in a DB. A file system needs complicated directory
structure to support than number of images. Keeping the DB and images in sync
is another issue too.
Microsoft has proven with the TerraServer project that SQL server is
efficient when storing images in a DB.
Regards
Mike
"Scott Elgram" wrote:
> Hey guys,
> I was hoping some of you people could shed some light on something
> that's been puzzling me this morning. I had a SQL 7 machine with a DB that
> contained millions of images. It was on a dual 1GHz P3 box with 2 GB of ram
> running Windows NT. This machine was really old and so mission critical no
> one wanted to touch it. Anyway, the nightly backup of this DB was
> approaching 15 hours and the size was approaching the 137gb limitation in
> both NT and the BIOS. I convinced my work that to fix these 2 problems we
> need to do 2 things.
> 1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
> This will improve performance and upgrade a server that's in dire need of an
> upgrade anyway and, most importantly, jump the 137gb hurdle.
> 2.) Move these 1 million plus images out of a DB structure and onto a
> mirrored file structure. This will greatly reduce the backup time and the
> actual size of the DB.
> Well, I got the server...and I set it all up like I wanted except for
> moving the images out of the DB. The DB backed up like normal last night
> and I noticed that instead of 15 hours it successfully completed in 2.
> Can the new servers speed really have been that effective in shortening
> the backup time. I was expecting maybe a little faster...like from 15 hours
> to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or should
> I look into some problem with the backup? If this is correct I wouldn't
> need to move the images into a file structure. From what I have read,
> because these images are small and used on our web pages, a database
> structure like we have currently would be the better choice.
> If anyone can shed some light on why the huge difference in backup times
> it would be much appreciated. Any suggestions on what I should do with
> these images are welcome too.
> Thanks,
> --
> -Scott
>
>|||Have you considered a third-party backup tool, e.g. SQL LiteSpeed? I think
you'll find even greater performance increases (although images may not
compress well depending on the format so that's hard to say)...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:OKM3nkcGFHA.3068@.tk2msftngp13.phx.gbl...
> Hey guys,
> I was hoping some of you people could shed some light on something
> that's been puzzling me this morning. I had a SQL 7 machine with a DB
that
> contained millions of images. It was on a dual 1GHz P3 box with 2 GB of
ram
> running Windows NT. This machine was really old and so mission critical
no
> one wanted to touch it. Anyway, the nightly backup of this DB was
> approaching 15 hours and the size was approaching the 137gb limitation in
> both NT and the BIOS. I convinced my work that to fix these 2 problems we
> need to do 2 things.
> 1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
> This will improve performance and upgrade a server that's in dire need of
an
> upgrade anyway and, most importantly, jump the 137gb hurdle.
> 2.) Move these 1 million plus images out of a DB structure and onto a
> mirrored file structure. This will greatly reduce the backup time and the
> actual size of the DB.
> Well, I got the server...and I set it all up like I wanted except for
> moving the images out of the DB. The DB backed up like normal last night
> and I noticed that instead of 15 hours it successfully completed in 2.
> Can the new servers speed really have been that effective in
shortening
> the backup time. I was expecting maybe a little faster...like from 15
hours
> to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or
should
> I look into some problem with the backup? If this is correct I wouldn't
> need to move the images into a file structure. From what I have read,
> because these images are small and used on our web pages, a database
> structure like we have currently would be the better choice.
> If anyone can shed some light on why the huge difference in backup
times
> it would be much appreciated. Any suggestions on what I should do with
> these images are welcome too.
> Thanks,
> --
> -Scott
>|||Hey thanks,
Yeah, I looked into windows 2003 but given the size of the company and
the cost of the new server they wanted to save money. We already had a
spare copy of windows 2000 server so I used that. I also wanted to upgrade
to SQL 2000 but like with server 2003 with all the licenses I'd need they
said it would be too expensive.
What do you mean by my DB being dived up into file groups? Are you
suggesting I divide the actual database into multiple databases, i.e. a 2003
database containing images from 2003 and so on?
So is it possible then that just the huge jump in speed between my old
server and the new one solved my problem of the 15 hour backup? I still
find it hard to believe that it would make 86% reduction in time.
-Scott
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7153294A-229F-4B97-95B1-D6A055450CC2@.microsoft.com...
> Hi
> Windows 2003 would probably bring better IO performance as Microsoft did a
> lot of work on it.
> If your DB is not yet dived up into file groups, do so. Then you can
backup
> and restore a single file group. This will help a lot in a DR scenario.
> I like to store Images in a DB. A file system needs complicated directory
> structure to support than number of images. Keeping the DB and images in
sync
> is another issue too.
> Microsoft has proven with the TerraServer project that SQL server is
> efficient when storing images in a DB.
> Regards
> Mike
> "Scott Elgram" wrote:
> > Hey guys,
> > I was hoping some of you people could shed some light on something
> > that's been puzzling me this morning. I had a SQL 7 machine with a DB
that
> > contained millions of images. It was on a dual 1GHz P3 box with 2 GB of
ram
> > running Windows NT. This machine was really old and so mission critical
no
> > one wanted to touch it. Anyway, the nightly backup of this DB was
> > approaching 15 hours and the size was approaching the 137gb limitation
in
> > both NT and the BIOS. I convinced my work that to fix these 2 problems
we
> > need to do 2 things.
> > 1.) Buy a new server, Dual 2.6GHz 2Gb RAM running windows 2000 server.
> > This will improve performance and upgrade a server that's in dire need
of an
> > upgrade anyway and, most importantly, jump the 137gb hurdle.
> > 2.) Move these 1 million plus images out of a DB structure and onto a
> > mirrored file structure. This will greatly reduce the backup time and
the
> > actual size of the DB.
> > Well, I got the server...and I set it all up like I wanted except
for
> > moving the images out of the DB. The DB backed up like normal last
night
> > and I noticed that instead of 15 hours it successfully completed in 2.
> > Can the new servers speed really have been that effective in
shortening
> > the backup time. I was expecting maybe a little faster...like from 15
hours
> > to maybe...at best 10, but, 2 hours. Wow!! Is this even possible or
should
> > I look into some problem with the backup? If this is correct I wouldn't
> > need to move the images into a file structure. From what I have read,
> > because these images are small and used on our web pages, a database
> > structure like we have currently would be the better choice.
> >
> > If anyone can shed some light on why the huge difference in backup
times
> > it would be much appreciated. Any suggestions on what I should do with
> > these images are welcome too.
> >
> > Thanks,
> > --
> > -Scott
> >
> >
> >
