Data Partitioning - Pro SQL Server Internals - page 327

Tiered Storage in Action
Table 15-6 shows the available online data movement options for different database objects based on the versions and
editions of SQL Server in use.

Table 15-6. Online data movement of database objects based on the SQL Server version and edition

Moving Partition to
Different Filegroup

Moving Table With
LOB Columns to
Different Filegroup

Moving Table
Without LOB Columns
to Different Filegroup

Moving Data to
Different Disk
Array

SQL Server 2012
- 2014 Enterprise
Edition

Straightforward
approach held
schema modification
(SCH-M) lock. Can
be implemented with
staging table and
partitioned view

Supported Supported Supported in

every edition

SQL Server
2005 - 2008R2
Enterprise Edition

Not Supported Supported

(Subject of LOB
column offline index
rebuild limitation in
SQL Server
2005-2008R2)

(Introduces
fragmentation and
overhead)

Non-Enterprise
Edition

N/A Not Supported Not Supported

As you can see, it is generally easier to implement online data movement using non-partitioned rather than
partitioned tables. This makes the approach that we discussed in the “Using Partitioned Tables and Views Together”
section of this chapter as one of the most optimal solutions. With such an approach, you are using non-partitioned
tables to store operational data, keeping the historical data in partitioned tables, as was shown in Figure 15-6 .
Let's look at the process of changing the operational period in more depth, assuming that you need to archive
May 2014 data and extend the operational period to July 2014.
In the first step shown in Figure 15-14 , you move the Orders2014_05 table from FASTSTORAGE to the FG2014
filegroup.