SQL Server Performance Forum – Threads Archive
New Server Estimates/Backup RecommendationsHello all, I have spent the last week calculating the expected size of the database using SQL Server BOL. I have a few questions related to my calculations and hardware specifications. the following is the hard disk space (MB) estimates for the server: Data Space 48112
Index Space 176926.65
DB Overhead Space (85%)191282.85
Additional. Design Considerations (15%)62448.22
Risk Factor (20%)83264.30
DB Size Estimate562034.02
OS, MS SQL Server, Symantec reqs.: 1852 Total Disk Space Needed: 448 GB I calculated the data space correctly (according to BOL). I have some time constraints and didn’t have time to complete the index space calculation so I took an average of the % (index space/data space) for each table in the test database. This is a brand new VB app to support financial contract data over the 7 years. User base is 10-50 users. We’re going to use SQL Server Standard. The server specs are supposed to be good for a minimum of 3 years. I’ve been looking at a Dell PE 2650:
2 Xeon processors at 2.4 MHz, 512MHz FSB
2GB DDR, 2x1GB DIMMS
Windows Server 2000
RAID 5 (5BAY SCSI HD Backplane)
146GBx5 10K RPM Ultra 320 SCSI HARD Drive
Single Controller: PERC3-DI, 128MB Battery Backed Cache, 2 internal channels
1. In estimating the size of disk space needed, I’ve included at 85% factor for "overhead" because the total size of the database is not dataspace + indexspace.
When I run sp_spaceused @updateusage = ‘TRUE’ in Analyzer,
DB size (MB) – 90.63
unallocated space (MB)- 1.19
reserved (KB)- 14848
data (KB) – 12056
index size (KB)- 2488
unused (KB) – 304 Allocation of the 90MB?
Overhead74.9482.7% So it looks like their is a significant ammount of overhead… What is this attributed to?
What are your thoughts on this estimate? 2. RAID 5 in 5x146GB leaves me with 584 GB (20% parity taken out) At the estimate of 551 GB needed, this doesn’t leave me with much room. The machine only comes with 5 drive bays too… any recommendations on this. 3. Finally, backup solutions. I’m considering a failover clustering. Management wants pricing on tape backup solutions. To backup that amount of data (full backup on weekends, incremental on weekdays), we need a pretty powerful tape drive. Any recommendations on backup solutions are welcome. This is mission critical data. Loss cannot happen. Thanks very much. I am usually dedicated to software development; this is a new world to me. Any expertise is much appreciated. Ann
Ann, 1) I have a question: Really Index is bigger than Data?
2) If machine is only 5 bays, well there is no to much to do. From performance view RAID 5 is not the best, but give you max space.
3) Are you plannig to install DLT?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Luis, 1) I got the index size by running the following stored procedures http://corp.elegantsp.com/pipermail/remedy/2002-February/040875.html).
tbl_A with 49 Rows, 8KB dataspace, 16 KB index space. 16/8 = 200%
tble a has 1 clustered indexes; INT
3) Is DLT the same as Analysis Services? we are not going to use Analysis Services. Ann
Regardin 3): Checkhttp://www.google.com/url?q=http://…KnVgZqg7eDQye3Co__xpfrkTwCBApTYnH0HaVABEA8PAA Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
This thread is a bit old now, so maybe you’ve already made your purchase decisions… We’ve got three Dell 2650’s and various other Dell servers, so I know the range reasonably well. Assuming you really need that much space, which I doubt (the overhead % looks way too high)… 5x 147GB drives isn’t going to be enough for almost half terabyte of data + expansion room. NTFS really needs 15% to 20% free disk space to work efficiently, and you loose available space due to overheads like RAID virtual disks, partitions etc. I’d suggest you get a PERC 4DC Raid controller and a Powervault 220S Raid array, and put your data drives in there. The 220S can take 14 drives (13 if you use it for failover clustering, which you might want to look at in future). It will cost more, but give you more flexibility, capacity, and peformance. Keep Windows etc on internal drives (RAID1 will be Ok), and look at DLT, Ultrium LTO, or S-AIT for backup, according to how much data you need to store in a full backup. Ultrium 2(aka LTO 2) can do 200GB uncompressed. S-AIT can do 400GB. DLT goes from 20GB to 100GB- I think! For backup software, I’d recommend Veritas BackupExec.
As a rule of thumb, you should plan for having disk capacity for six times the size of the expected data in the database. This inclused only the data size and does not account for indexes. If you need I can give a detailed analysis of the same…. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Gaurav, Thank you. The detailed analysis would be most appreciated! Ann
May check this threadhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1726 HTH _________