Sql-server – Filter Data to different data files on SQL server

datafilesql-server-2008table

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'
)

Thoughts

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)

Thanks
Ste

Best Answer

Table By Table - You can relatively easily do this. You need to create a filegroup, then a file in that filegroup and then you can move the tables you want to the new filegroup. Use the CREATE INDEX syntax with DROP EXISTING=ON and specify the new filegroup.

The clustered index is the table so moving them this way moves the table.

Your alternatives are to use partitioning but that requires enterprise and you won't be able to control it the way you are discussing.

MSDN CREATE INDEX

Example for AdventureWorks2014 and the Person.Person table.

USE [master];

--Creates a new filegroup.
ALTER DATABASE [AdventureWorks2014] ADD FILEGROUP [SLOW]

--Adds a file to the filegroup...
ALTER DATABASE [AdventureWorks2014] ADD FILE ( NAME = N'AdventureWorks2014_SlowSAN'
                                , FILENAME = N'C:\SQLData\JDF2014\Data\AdventureWorks2014_DataSlow.MDF' 
                                , SIZE = 524288KB 
                                , FILEGROWTH = 131072KB ) TO FILEGROUP [SLOW];


USE AdventureWorks2014

--Move the clustered index (which is the table) to the new filegroup.
CREATE UNIQUE CLUSTERED INDEX PK_Person_BusinessEntityID
    ON Person.Person (BusinessEntityID)
WITH (DROP_EXISTING =  ON )
ON SLOW
GO
Related Question