Conventional wisdom about RAID doesn't apply well to SSDs. They don't really need striping (RAID0). They are prone to failures by-design, but RAID-1 is usually not the right answer for SSD for two reasons: a) is wasteful, halves the capacity of the SSD array (and they are pricey) and 2) SSDs failure characteristics leads towards both drives in the mirror to fail at very close intervals (ie. correlated failures) and thus render the entire array useless. See Differential RAID: Rethinking RAID for SSD Reliability for a lengthier discussion. Some have recommended using Raid-6 for SSDs.
Additionally, the conventional wisdom of SQL Server file layout doesn't apply to SSDs. I would recommend you watch SQL on SSDs: Hot and Crazy Love and go over the benchmark links in this answer.
File writes are distributed across files in the same file group proportionally based on the current size of each file in the file group. This is referred to as the "proportional fill algorithm" - look at http://sqlserver-performance-tuning.net/?p=2552 for some interesting details around that.
tempdb
can have only a single filegroup. If you attempt to create a filegroup in tempdb
you get the following:
Msg 1826, Level 16, State 1, Line 1
User-defined filegroups are not allowed on "tempdb".
If you have trace flag 1117 turned on, files within a group will autogrow simultaneously across files in the filegroup for each file that is not currently at its maximum size, and where space exists on the disk.
Does your instance have trace flag 1117 turned on? You'd probably want it off in this particular instance even though "best practices" often indicate having this turned on. There is an item on Microsoft Connect asking to have a setting like this that can be enabled/disabled on a per-database basis, here: https://connect.microsoft.com/SQLServer/feedback/details/781198/trace-flag-1117-autogrowth-of-data-files-is-instance-wide-would-like-a-flag-for-just-tempdb
Assuming the SSDs are devoted to tempdb, I agree with your assertion and suggest making the tempdb on the SSDs as big as possible (not 100% of the drive, perhaps leave 10% free). Make the tempdb files on disk as small as possible, say 1MB, with autogrowth, and maximum file size as big as you need. Monitor for tempdb filegrowth on the HDDs, and make the case for getting bigger SSDs if you think the company would benefit from it.
According to the documentation, SQL Server files can be created on raw partitions (partitions that haven't been formatted) by using just the drive letter of the partition in the ALTER DATABASE ... ADD FILE
syntax. This apparently removes the need to grow or shrink the file since it inherently uses the entire raw partition, as needed. I'm uncertain if this would help your situation or not; just thought I'd throw it out there as an interesting factoid. See "If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be put on each raw partition."
Best Answer
You can limit the tempdb autogrowth for the primary datafile (that you have on SSD) to 30GB and then you can create additional datafiles on the SAN.
The drawback will be that you will see imbalance in the tempdb datafile growth.
SQL Server allocates space in a round-robin fashion.
Important Trace flags when dealing with Tempdb :
-T1117
(helps keep data base files equally sized over their lifetime, by triggering all database files in a filegroup for autogrow at the same time.). Note that you wont need this trace flag as you will be eventually restricting the tempdb to NOT grow on SSD once you reach the threshold.-T1118
(this trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion.) for tempdb.Refer to Misconceptions around TF 1118 by Paul Randal and SQL Server 2008 Trace Flag -T 1117