Home > Articles, Database structure, News > Database Splitting into different File-Groups

Database Splitting into different File-Groups


During the process of database optimization database administrators quite often face with optimization of low-level I/O system. As a result, they try to split a database between different Disks, RAID-s, LUN-s, etc., in order to scale I/O workload out. This process is known also as “Database Splitting” between several logical I/O units.

Introduction

There are several essential scenarios to split a database which depend on database complexity and performance requirements which we want to achieve. I want to mention the most common scenarios are:

  1. Moving a particular table (tables) into different file-group.
  2. Moving all existed tables, indexes, LOB objects into different file-groups.
  3. Moving database entities between different file-groups using some more complex rules such as partitioning, archiving, etc.

The former scenario can be performed quite easy using basic syntax MOVE TO which has been described very well in BOL and many other articles. However, I want to mention that in my personal view it is not always comfortable to use this command in order to perform the last two scenarios thus they demand much more sophisticated operations.

From my observations, almost in all cases database administrators collate with necessity to move a large number of tables, indexes, and other entities into different file-groups at ones. It can be necessary, for instance, for large and long operated databases which have been grown intensively and have not been correctly optimized from the beginning.

Of cause, there are other methods to divide data between file-groups dealing with complex database structure tightly coupled with existed business logic, etc. Exactly in this case we prefer to use the last scenario utilizing individually developed solutions suitable just for a particular case.

Solution

Purpose

The main aim of this article is to present you a solution which can be used easily exactly in the case of necessity to perform the second scenario of splitting an entire database between 3 essential file-groups.

Low-level design

As can be seen from the picture below, I am proposing to use one of the essential features from “SQL Server Best practices” to move all database entities out of PRIMARY database file-group. It is always a good idea to keep meta information stable and safe consequently. As a result, we have got 4 database file-groups all together which store meta information, data, indexes and large binary objects. Each of mentioned file-groups except of PRIMARY may consists of several files to enhance I/O performance further but is is the part of the other article…

Existed basic syntax

It is known that we can use MOVE TO command in order to move data into another file-group. However, this command can be used only when you drop existed indexes or constraints. Moreover, it works just only with CLUSTERED indexes and constraints as can be seen on the next code example. Otherwise you cannot move the rest of your database entities which contain data  into different file-group such as NON-CLUSTERED indexes, tables without any indexes or Large Binary Objects.

<drop_clustered_index_option> ::=
 {
 MOVE TO { partition_scheme_name ( column_name )
         | filegroup_name
         | "default" }
 }
<drop_clustered_constraint_option> ::= 
 {
 MOVE TO { partition_scheme_name ( column_name )
         | filegroup_name
         | "default" }
 }
Main idea

The main ides of the proposed solution is grounded on using database schema in order to move all database entities in new file-groups applying bulk modifications on an entire database. Using another words, you do need to recreate all these entities manually specifying new file-groups.

However, in order to avoid a recreation of all table definitions I propose to use clustered indexes in order to relocate data stored in every single table. As you probably know, if table has a clustered index it automatically specify where table stores its data. As a result, we can use CLUSTERED indexes temporary with tables which do not have ones or just to recreate existed CLUSTERED indexes with tables which have them.

In majority of cases it works, except of tables which have LOB attributes. In this case we should recreate definition of such tables completely and repopulate them by existed data as well.

Description

As can be seen, the solution is universal because it utilizes database schema in order to perform any database modifications. Frankly speaking, it is not really INFORMATION_SCHEMA but it is the list of system views which quite stable between of SQL Server builds or even versions.  Additionally, it has been written as a pattern which can be easily adjusted to some particular conditions if it demands. For instance, you can exclude some particular tables, indexes or constraints using a mask with the list of queries which refer to system views, etc.

The entire code of the solution was divided into 4 different scripts in order to decrease complexity:

  1. Moving of CLUSTERED tables
  2. Moving of NON-CLUSTERED tables
  3. Moving tables with LOB attributes
  4. Moving tables without CLUSTERED indexes

In case of dealing with Large Binary Objects the solution can recreate correctly all essential database entities which are mentioned below:

  • tables
  • indexes
  • constraints
  • check constraints
  • triggers
  • foreign keys
  • defaults

Each script was wrapped into a large transaction which will definitely help to keep integrity of a database structure.

In the beginning of each script you can specify the list of file-groups which you are going to work with such as file-groups for data, indexes or large binary objects.

Sources

You can download T-SQL sources using the links below:

  1. Moving CLUSTERED indexes to another file-group.sql
  2. Moving NON-CLUSTERED indexes to another file-group.sql
  3. Moving LOB tables to another file-group.sql
  4. Moving NON-CLUSTERED tables to another file-group.sql

.

Source: Aleksey Fomchenko (https://sqlconsulting.wordpress.com)

Reference: Aleksey Fomchenko (https://sqlconsulting.wordpress.com)

.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: