SQL Server 2005 and 2008 practical database size

Hi
We have 780gb (SQL Server 2005) database and users do not complain of
performance ))))))




wrote in message

> Hi all,
>
> I think that the following question is a recurring one but, based on
> you experience, what is the maximum practical size of an SQL Server
> 2005 or 2008 database ?
>
> Please don't answer me with the theoretical numbers given in MSDN
> (http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx) ! I'd
> like a return on your day to day experience. Tell me at which point
> you see a performance decrease that you could not solve, or only with
> great difficulty or complex trick.
>
> Thanks in advance for your help
>
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

Erland
My respect to people who administrates 10TB SQL Server database (personally
I have not met one), have you met?




"Erland Sommarskog" wrote in message

> ( ) writes:
>> I think that the following question is a recurring one but, based on
>> you experience, what is the maximum practical size of an SQL Server
>> 2005 or 2008 database ?
>>
>> Please don't answer me with the theoretical numbers given in MSDN
>> (http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx) ! I'd
>> like a return on your day to day experience. Tell me at which point
>> you see a performance decrease that you could not solve, or only with
>> great difficulty or complex trick.
>
> Performace is related to much more than just size. I'm sure that there
> are people out there who can say that they ran into unovercomable problems
> with a 10 GB database. And you can find other who report that their 10 TB
> datawarehouse runs along fine.
>
> But there is no denial: administering a 10 TB database requires more skill
> and planning than a 10 GB database. The most problematic issue is not
> likely
> to be query performance, but the management around it: hardware, backups,
> redudancy and all that.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

This is way too open-ended of a question. Hardware simply has to be
included here because there is easily 5 orders of magnitude (probably more)
performance capability differences between current low-end and extremely
high-end WinTel hardware and IO subsystems. Are we talking a $500 white-box
with a single drive and single CPU or an 8-figure Itanium or Unisys
super-box with a million+ IOPS SAN backing up the IO requests?

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


wrote in message

> Hi all,
>
> I think that the following question is a recurring one but, based on
> you experience, what is the maximum practical size of an SQL Server
> 2005 or 2008 database ?
>
> Please don't answer me with the theoretical numbers given in MSDN
> (http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx) ! I'd
> like a return on your day to day experience. Tell me at which point
> you see a performance decrease that you could not solve, or only with
> great difficulty or complex trick.
>
> Thanks in advance for your help
>
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

On 27 oct, 12:37, "Uri Dimant" wrote:
> Hi
> We have 780gb (SQL Server 2005) database and users do not complain of
> performance  ))))))
>
> wrote in message
>
>
>
>
>
> > Hi all,
>
> > I think that the following question is a recurring one but, based on
> > you experience, what is the maximum practical size of an SQL Server
> > 2005 or 2008 database ?
>
> > Please don't answer me with the theoretical numbers given in MSDN
> > (http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx) ! I'd
> > like a return on your day to day experience. Tell me at which point
> > you see a performance decrease that you could not solve, or only with
> > great difficulty or complex trick.
>
> > Thanks in advance for your help- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -

Thanks Uri.

And what is the largest number of rows in your table ? (on our side we
are expecting 300GB or more and up to 40 millions of rows) Are you
accessing your data through a front end (web application or other kind
of front end) and/or SSRS and or SSAS ?
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

Hi
--And what is the largest number of rows in your table ?
200 million rows

--Are you
accessing your data through a front end (web application or other kind
of front end) and/or SSRS and or SSAS ?
VB.NET application not SSAS




wrote in message

On 27 oct, 12:37, "Uri Dimant" wrote:
> Hi
> We have 780gb (SQL Server 2005) database and users do not complain of
> performance ))))))
>
> wrote in message
>
>
>
>
>
> > Hi all,
>
> > I think that the following question is a recurring one but, based on
> > you experience, what is the maximum practical size of an SQL Server
> > 2005 or 2008 database ?
>
> > Please don't answer me with the theoretical numbers given in MSDN
> > (http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx) ! I'd
> > like a return on your day to day experience. Tell me at which point
> > you see a performance decrease that you could not solve, or only with
> > great difficulty or complex trick.
>
> > Thanks in advance for your help- Masquer le texte des messages
> > précédents -
>
> - Afficher le texte des messages précédents -

Thanks Uri.

And what is the largest number of rows in your table ? (on our side we
are expecting 300GB or more and up to 40 millions of rows) Are you
accessing your data through a front end (web application or other kind
of front end) and/or SSRS and or SSAS ?
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

Hardware foreseen is :

- Intel Xeon processor, dual core 64 bit, 2.8 or 3.0 Ghz
- 4 GB RAM
- Used with SAN

On 27 oct, 23:31, "TheSQLGuru" wrote:
> This is way too open-ended of a question.  Hardware simply has to be
> included here because there is easily 5 orders of magnitude (probably more)
> performance capability differences between current low-end and extremely
> high-end WinTel hardware and IO subsystems.  Are we talking a $500 white-box
> with a single drive and single CPU or an 8-figure Itanium or Unisys
> super-box with a million+ IOPS SAN backing up the IO requests?
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> wrote in message
>
>
>
>
>
> > Hi all,
>
> > I think that the following question is a recurring one but, based on
> > you experience, what is the maximum practical size of an SQL Server
> > 2005 or 2008 database ?
>
> > Please don't answer me with the theoretical numbers given in MSDN
> > (http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx) ! I'd
> > like a return on your day to day experience. Tell me at which point
> > you see a performance decrease that you could not solve, or only with
> > great difficulty or complex trick.
>
> > Thanks in advance for your help- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

I have 3 databases exceeding 4 TB each.

--
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

> I have 3 databases exceeding 4 TB each.

oops should have read I know three SQL Server 2005 Enterprise databases
each exceeding 4 TB each. They are all administered by a single DBA.

As they tell me , it is not the performance which is the biggest issue,
it is the day to day management, backup and disaster recovery planning.
According to them - and I have no reasons to doubt them they tried
almost all software based backup solutions - and even while taking the
backup on SAN, they were taking in excess of 24 hours. They gave up and
now simply clone it on the SAN.

The biggest table has close to 3 billion rows.

Paras

--
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

Did they try hyperbac by any chance?

I have a number of clients that are doing SAN-based snapshotting as their
1st stage DR step.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Paras Kafley" wrote in message

>> I have 3 databases exceeding 4 TB each.
>
> oops should have read I know three SQL Server 2005 Enterprise databases
> each exceeding 4 TB each. They are all administered by a single DBA.
>
> As they tell me , it is not the performance which is the biggest issue,
> it is the day to day management, backup and disaster recovery planning.
> According to them - and I have no reasons to doubt them they tried
> almost all software based backup solutions - and even while taking the
> backup on SAN, they were taking in excess of 24 hours. They gave up and
> now simply clone it on the SAN.
>
> The biggest table has close to 3 billion rows.
>
> Paras
>
> --
>
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

4GB of RAM is a pittance these days, as is a single CPU with only 2 cores.
Even with a good SAN to back it up (not sure why you would have one of those
given the paltry server specs you mention) I would expect a few hundred GB
of semi-active data to be the limit for this hardware, perhaps as little as
50GB. That is mostly a guess however since I don't know of any company that
runs data on limited hardware like that. Well, that isn't completely true -
I know of some dev environments on that low end that do have 50-100GB on
them, but they run pretty poorly.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


wrote in message

Hardware foreseen is :

- Intel Xeon processor, dual core 64 bit, 2.8 or 3.0 Ghz
- 4 GB RAM
- Used with SAN

On 27 oct, 23:31, "TheSQLGuru" wrote:
> This is way too open-ended of a question. Hardware simply has to be
> included here because there is easily 5 orders of magnitude (probably
> more)
> performance capability differences between current low-end and extremely
> high-end WinTel hardware and IO subsystems. Are we talking a $500
> white-box
> with a single drive and single CPU or an 8-figure Itanium or Unisys
> super-box with a million+ IOPS SAN backing up the IO requests?
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> wrote in message
>
>
>
>
>
> > Hi all,
>
> > I think that the following question is a recurring one but, based on
> > you experience, what is the maximum practical size of an SQL Server
> > 2005 or 2008 database ?
>
> > Please don't answer me with the theoretical numbers given in MSDN
> > (http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx) ! I'd
> > like a return on your day to day experience. Tell me at which point
> > you see a performance decrease that you could not solve, or only with
> > great difficulty or complex trick.
>
> > Thanks in advance for your help- Masquer le texte des messages
> > précédents -
>
> - Afficher le texte des messages précédents -
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

TheSQLGuru wrote:

> Did they try hyperbac by any chance?
>

Kevin,
I will ask.

Paras

--
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

My guess was wrong. Hardware is :

- Two dual core Xeon processors
- 8 GB RAM but could be improved

On 29 oct, 02:14, "TheSQLGuru" wrote:
> 4GB of RAM is a pittance these days, as is a single CPU with only 2 cores..
> Even with a good SAN to back it up (not sure why you would have one of those
> given the paltry server specs you mention) I would expect a few hundred GB
> of semi-active data to be the limit for this hardware, perhaps as little as
> 50GB.  That is mostly a guess however since I don't know of any company that
> runs data on limited hardware like that.  Well, that isn't completely true -
> I know of some dev environments on that low end that do have 50-100GB on
> them, but they run pretty poorly.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> wrote in message
>
>
> Hardware foreseen is :
>
> - Intel Xeon processor, dual core 64 bit, 2.8 or 3.0 Ghz
> - 4 GB RAM
> - Used with SAN
>
> On 27 oct, 23:31, "TheSQLGuru" wrote:
>
>
>
> > This is way too open-ended of a question. Hardware simply has to be
> > included here because there is easily 5 orders of magnitude (probably
> > more)
> > performance capability differences between current low-end and extremely
> > high-end WinTel hardware and IO subsystems. Are we talking a $500
> > white-box
> > with a single drive and single CPU or an 8-figure Itanium or Unisys
> > super-box with a million+ IOPS SAN backing up the IO requests?
>
> > --
> > Kevin G. Boles
> > Indicium Resources, Inc.
> > SQL Server MVP
> > kgboles a earthlink dt net
>
> > wrote in message
>
> >
>
> > > Hi all,
>
> > > I think that the following question is a recurring one but, based on
> > > you experience, what is the maximumpracticalsize of an SQL Server
> > > 2005 or 2008 database ?
>
> > > Please don't answer me with the theoretical numbers given in MSDN
> > > (http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx) ! I'd
> > > like a return on your day to day experience. Tell me at which point
> > > you see a performance decrease that you could not solve, or only with
> > > great difficulty or complex trick.
>
> > > Thanks in advance for your help- Masquer le texte des messages
> > > précédents -
>
> > - Afficher le texte des messages précédents -- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -
 >> Stay informed about: SQL Server 2005 and 2008 practical database size 

Hi All

Quick update on this issue, if you have an exceptionally large database
to Backup you could try using third party tools such as quest Lightspeed
it backups 0.5TB in 1.5 hours
and restores the data at roughly the same rate

Hope this helps

Regards
Brian


*** Sent via Developersdex http://www.developersdex.com ***
 >> Stay informed about: SQL Server 2005 and 2008 practical database size