I'm sure this is possible but I have no idea how or where to find the information on how to do it.
Scenario: I have a reporting database (on SQL2008R2) which is split up into multiple file groups and multiple files. We're reaching the data limit of the current SAN drive and have reclaimed space from another SAN to provide the space we need.
Problem: The 'new' SAN is on a 1GB connection whereas the current SAN is a 10GB connection, the intention is to keep all current data on the current SAN and put all old data or data for inactive users onto the slower SAN as it will not be used for anything other than very specific reports.
Pseudo Solution 1
ALTER DATABASE Reporting ADD FILE( NAME =DataFile2, FILENAME = E:\OldSan\Reporting\Data2.ndf, SIZE= 512000MB, MAXSIZE = 716800MB, FILEGROWTH = 2048MB, DATAQUERY ='SELECT ALL OLD/INACTIVEDATA' )
I know the above is not possible strictly like that and it would need to be done on a table by table basis of the data to move across. but not sure where to start or how to do it.
Theoretically(in my head) this would be done with splitting the clustered index on a filter across the filegroups (and the files just deal with it), there's options on a table properties for storage for filegroup or partition scheme, however on a clustered index this seems to always be greyed out so I'm not sure if I'm just clutching at straws there.
NOTE: it is potentially possible to upgrade from SQL 2008R2 to 2012 if that is absolutely necessary(but not desired)
EDIT: The query for this would ideally be based on data in another table, so DataToOldSAN = where userid in (select id from users where active =0)