AlexanderSmith avatar image
AlexanderSmith asked

Suggestion for storage hardware / scalability SQL Server 2012

Hi, We are exploring to move to SQL Server (from Oracle). The main reason behind this is pricing and to have more control over our solution (currently a lot is being out-sourced to third parties, including hardware configuration). If SQL Server offers the same or better scalability, it will help us to make the call. We would first use SQL Server 2012 and migrate to SQL Server 2016 one of the coming years. Our DBA has come up with the following requirement: Point A (hot user tables): -------------------------- There are 3 "hot" user tables with a volume of around 10GB. Each can each have their own file and can be stored on a separate disk. => 3 x SSD x 10GB (in the future, SQL Server 2016 these tables will be put in-memory) Point B (tempdb): ----------------- There are 8 tempdb data files (because we have 8 cores) and each file can grow up to 10GB. The shared log has a volume of around 70GB. => 8 x SSD x 10GB (data) + 1 x SSHD x 70GB drive (log) Point C (active user tables): ----------------------------- Aproximatly 100 active tables (=files) which will be inserted/updated/selected frequently the entire day. But not as frequent as the 3 hot tables in point A. These can be stored on one drive of about 500GB. => 1 x SSD x 500GB There is 1 very large active table which will be selected frequently for the entire day. There may be some updates/inserts, but not that many. The size of the table will be around 200GB => 1 x SSD x 200GB All the tables in point C can write to the same log drive. => 1 x SSHD x 1TB Point D (operating system + system databases): ---------------------------------------------- Rebooting the system is not that important. As we are expecting the system to be available 24h/7. If the system crashes, it will be taken over by an exact copy. As per my understanding, the system databases can also be stored on a slower drive. => 1 x HHD x 250GB Point E (frozen data + backup files): ------------------------------------- There is (old) data that will not be used, but must be available for compliance reasons. This data will be place in the same table, but in a different partition. The partition will be compressed and put on a separate disk. The volume of this disk must be large, but can be slower. In addition, a daily user database backup must be taken which can be stored on the same disk. => 1 x HHD x 4TB Point F (older backup files): ----------------------------- Backup files older than one week can be stored on a NAS or tape. Conclusion: ----------- We have the following storage requirement: 11 x SSD x 10GB 01 x SSHD x 70GB 01 x SSD x 500GB 01 x SSD x 200GB 01 x SSHD x 1TB 01 x HHD x 250GB 01 x HHD x 4TB Total disks: 17 physical disks + NAS Total disk space needed: 6 to 7 TB Questions: ---------- * Is it possible to have 17 physical hard drives connected to one server? What are the prerequisites? * What would be the best choice for 11 x SSD x 10GB drives?
sql server 2012tempdbstoragehardware
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What are the IO loads (both IO per second, and amount of data per second) associated with each point / table? That should be the driver for the architecture of the storage. And what about data growth? Is that planned for already? What about the transaction logs? He's not mentioned them. I guess that'll be another 100GB SSD, taking your drive bay requirement up to 18...
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What's the current environment hosted on? You say it's Oracle, but what's the hardware underneath? How big a leap is it to the hardware that your DBA has recommended?
0 Likes 0 ·

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.