SQL Server Performance Forum – Threads Archive

newbie – database design questions

Hello Forum,
I need to store about 400GB of Image data in the SQL Server database whose table schema will be maintained by a third party application server in the middle layer. The total data that we want to store in SQL Server will go up to 1 TB very soon. DB Server specs are : dual 3.4 GHZ processor, 2GB RAM, 2 TB of "hardware controlled" RAID 5. My question: which one is more efficient i)if I make one huge data file of size 500GB and does SQL Server allow such a huge datafile ? or ii) say some 15 GB of data file with max. increase set to unlimited or 1 TB?
In the past I have created a DB with 100GB of file size but it took forever for the new database to add it as a data file. Thank you very much for your help in advance

Yes SQL server will handle that size of data and size doesn’t matter at all.
For the performance basis I suggest Enterprise Edition to take advantage of extended memory support than using Standard edition whichis limited to 2GB only. On the database size issue leave the settings to Auto-grow and ensure the disk drives are configured with proper RAID levels in order to sustain the database growth. BTW by what method you want to stored the data to SQL server database?
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

By any chance, DON’T USE RAID 5 !!!
Why? Read this:http://www.baarf.com/ For BLOBS, you might find this interesting:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Thank you very much for your replies.
Let me make sure I understood you correctly Satya. So you suggest I start of with a data file size of 500GB, leave the "Auto grow" on, right? What kind of log file size should I be aiming at to accomdate such a huge data transactions?
All of my data is Geographical Information System (GIS) related – Spatial data and the front and middle layer engines are provided by ESRI (Environmental Society Research Institute, www.esri.com). SQL Server being the third layer. Frank – I didn’t get any part of your message. Are you saying RAID 5 is not the best way to store this kind of data? Thanks again.

Initially you can create a database with 10 GB and run(schedule) the ALTER DATABASE script to increase the size to 500GB. If the Transaction log file is stored on other drive then you can assign 10GB to the Tlog, again this depends on the recovery model of the database you choose and how often the database is updated. Also business concerns about database availablility about losing data during any issues. Previously I’ve followed the above process to create a 300gb database without any issues. As per the Frank’s reference its better to avoid RAID5 in this case. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yes, unless this is a pure OLAP system, avoid RAID 5. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>