SQL Server Performance Forum – Threads Archive

Recommendations for max database file size in VLDB

We are about to build our first multi TB raster database on SQL Server 2000 Standard Edition. This will be on an IBM SAN made up of 250GB SATA drives w/ RAID 5 configuration. Right now we are thinking of doing 4 1TB LUNs or 2 2TB LUNs. Either way how does one determine the size of individual database file for the database? This database will be a very static readonly database storing orthophotos for our GIS system.

I suggest to review all post in this Forum (Hardware) or wait for Joe.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I have serious concerns about using SAN with SATA drives,
no direct experience on IBM SAN’s but with several other SAN vendors, the SATA drives on SAN had absolutely horrible performance.
i mean really bad, i strongly suggest you go with either a white box (build it yourself) with 24 SATA drives (this is the most a single chassis can hold) connected directly to the SATA RAID controller and not through a SAN.
be sure that both the drives and the controller support NCQ also consider SAS, while somewhat more expensive, and because it is newer, getting parts from a single source is a little difficult.

why wait for me?
its not like i know everything (please don’t tell my consulting customers)
also, the more beers you are buying, the more i will say

Just because I don’t know other member with more hardware experience like you.
With all respect for all members.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The SAN has been already been purchased. We are using fiber channel for regular data and only RASTER images will go on the SATA drives. The GIS company, ESRI, recommended the SATA drives to us as a means of lowering costs. So any thoughts on how large we could make the physical database files?

anybody recall the max file size for SQL Server?
i would think that the page pointer is 4 bytes, for a max of 4 billion values, each pointing to a 8K page, so the upper bound should be 32TB. i think there are so OS/HW issues that may occur at either 1 or 2TB,
you might consider a raw partition instead of a formatted file system at this size most places where i worked, the dba split the db into multiple 1TB files,
but i do not think this really matters, if your OS/HW will work with 2TB files, then go ahead.

The SATA drives (SCSI) will perform much faster than the SAN because of the current databus vis-a-vis fiber optic bandwidth difference. There is an overhead in the SAN. I agree with JOE to partition your files. From my experience, I suggest to the maximum size amount of the DLT tapes that you will use (or whatever is manageable size to you, that is, you don’t have to wait a long time in full restore/backup process). That is, less than or equal to 250GB because its much faster to backup/restore compared to a 500GB <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> and less tapes compared to smaller size (120GB). Use the SATA for frequently accessed data and SAN for archives. May the Almighty God bless us all! www.empoweredinformation.com

Your post confuses me cmdr. Actually, it just doesn’t make any sense at alll. 1. SATA drives are not generally going to perform better than a SAN. They might be drives that are part of the SAN. SATA drives are NOT SCSI drives. You would never use SATA for frequently accessed data, when you could use a SAN or better yet dedicated fiber enclosures with fast fiber U320 drives that run at 15k. 2. If you have this high-end of a system, you should invest in Quest LiteSpeed for backups. It will save you tons of money, backup the data files twice as fast, and compress the file sizes to about 30% of their original size. At that point, the issue you bring up could be quite a bit different. As a thought though, most people with this size environment have multi-drive, multi-tape libraries. Backing up a 32 terrabyte file isn’t really an issue. Besides, usually there’s a process to backup to disk first. Joe, the file size limit is 32TB for the log file and 32TB for the data file. In 7.0, the log file had a 4TB limit, which I never quite understood, but whatever. Since, theoretically, you can have 32,767 files per database, that’s a lot of space. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] What kind of a SAN is this kreuzk? I can see why they would want you to store the files on SATA. Make sure it’s SATA though. EMC likes to give people ATA. The speed on those things is horrible; and the quality is crap. Also, when you setup the array and access, watch out for the following: 1. Use a LOT of drives. Use as many as you can. Go RAID 10 if you can. 2. Have a LOT of paths to the SAN. This will force the data over multiple routes and increase IO, usage of the cache, and overall throughput to the SAN. Most SAN’s are good at random IO and suck at serial IO. Remember that. It could save your project. In the end, you might want to look at dedicated DAS. At a minimum, you might find yourself buying some good old fiber shelves with 15k drives in them. See how it goes though. The cache on the SAN makes a big difference if it’s managed correctly. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

The Serial Advanced Technology Attachment drive itself is not what I was trying to communicate but the configuration of the SATA connected to the raid controller/SCSI in a server. The link between the server to the SAN (fiber optic) compared to the raid controller (databus) speed makes a difference. To go back to the original question "how does one determine the size of individual database file for the database?", if you’re trying to do a capacity planning, it depends on your data. Check out the capacity planning techniques. If you’re trying to configure/placements of these data files, either you can put it all in one place or check out your business requirements (backup capability) etc. There are business/technical reasons that you may want to have a multiple tapes compared to one huge tape. But if you don’t have any issues, you can go ahead and put everything in one basket. Use SP_HELPDB in SQL Server to see the files associated with the database and their size.
May the Almighty God bless us all!
www.empoweredinformation.com

]]>