Database Reference
In-Depth Information
As you can see, such an approach dramatically reduces the number of tables as compared to a partitioned views
implementation, keeping the flexibility of partitioned views intact.
Tiered Storage
One of the key benefits of data partitioning is reducing storage costs in the system. You can achieve this in two
different ways. First, you can reduce the size of the data by using data compression on the historical part of the data.
Moreover, and more importantly, you can separate data between different storage arrays in the system.
It is very common to have different performance and availability requirements for different data in the system.
In our example, it is possible to have 99.99 percent availability and 20ms latency SLAs defined for operational data.
However, for the older historical data, the requirements could be quite different. For example, orders from 2012 must
be retained in the system without any performance requirements, and the availability SLA is much lower than it is for
operational data.
You can design a data layout and storage subsystem based on these requirements. Figure
15-7
illustrates one
possible solution. You can use a fast SSD-based RAID-10 array for the
FASTSTORAGE
filegroup, which contains
operational data. Data for January-April 2014 is relatively static, and it could be stored on the slower RAID-5 array
using 15,000-RPM disks. Finally, you can use slow and cheap 5,400-RPM disks in the RAID-1 array for the data from
the years 2013 and 2012.
Figure 15-7.
Tiered storage
Tiered storage
can significantly reduce the storage costs of the system. Finally, yet importantly, it is also much
easier to get an approved budget allocation to buy a lower-capacity fast disk array due to its lower cost.
The key question with tiered storage design is how to move data between different tiers when the operational period
changes, keeping the system online and available to customers. Let's look at the available options in greater detail.
Moving Non-Partitioned Tables Between Filegroups
You can move a non-partitioned table to another filegroup by rebuilding all of the indexes using the new filegroup
as the destination. This operation can be done online in the Enterprise Edition of SQL Server with the
CREATE INDEX
WITH (ONLINE=ON, DROP_EXISTING=ON)
command. Other sessions can access the table during the online index
rebuild. Therefore the system is available to customers.
