Introduction

Usually, with any SQL application, let alone an ERP application, tables can be broken down into different types from an application designer’s point of view. There are at least these three types:

• Static

• Data that doesn’t change, or changes infrequently
• These tables tend to provide information for combo boxes in your applications, or help describe information for other parts of your SQL application, such as how a screen is designed, etc..
• Some applications call these types of tables Lookup Tables
• A great example inside Sage ERP X3 is a data dictionary tables such as ATABLE or ATEXTE

• Volatile

• Data storage tables for user transactions
• The volatility of these tables will vary based on the table’s intended purpose as well as the level of database normalization applied to the table at design time
• Volatile tables by definition experience inserts, updates, deletes or reads at a statistically significant rate each day
• The structure of the table doesn't necessarily change frequently but the data typically would

• Archive

• These types of tables tend to hold previous user transactions, but may not be experiencing new inserts, updates, deletes and reads on a regular basis
• For this reason, these types of tables are relatively static
• They can potentially hold large amounts of data but are not volatile in nature

You can further refine table types into things like work tables, staging tables and more. But, for the purpose of this discussion we will look at volatile tables, and how you might go about optimizing your system with your hardware. Great examples of volatile tables inside Sage ERP X3 are ALISTER, STOJOU and pick your GL table of choice.

What Is A Hot Spot?

A hot spot is a situation where frequent access to the storage sub-system (your disk drive(s) or your raid storage system) can create a bottleneck. So, if you had a massive amount of data requested from a certain location on your disk you might experience a hot spot in that area or some physical contention accessing the data from that section of the storage sub-system. Contention can increase if you are both reading data from and writing to the same area. In some cases, for any SQL application, but also true for ERP applications, more than one user can typically be found waiting if a hot spot is created. An example of a potential hot spot inside Sage ERP X3 in version 6 and below is the ALISTER table. When a user issues a new search operation for the requester it will run a delete to clear out the ALISTER table for that requester code and user code, then retrieve the results and populate ALISTER again. In this case, it is paramount that for large data sets you must align your hardware with your usage pattern, meaning you’d want the fastest access to these types of tables as possible.

So how do you align your hardware with your usage patterns? One way to do that is the use SQL Server Filegroups.

What Are SQL Server Filegroups?

Microsoft’s definition of the Files and Filegroups architecture is as follows:

“SQL Server maps a database over a set of operating-system files. Data and log information are never mixed in the same file, and individual files are used only by one database. Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.”

Great definition, what does that mean?

The graphic below shows a couple things:

1. You can have many databases on one SQL Server

2. Each database can have a different make up of files. The Sage ERP X3 database in this case has two data files and one log file

 

Since filegroups allow us to organize individual files, then, according to the graphic below we could put Data file 1 on one physical drive and Data file 2 on another physical drive (or a separate LUN if you are using a Storage Arena Network / SAN device). That’s the WIFM (What’s in it for me?). Meaning you can gain speed by organizing your workloads onto different physical media. 

 

How Do I Create A SQL Server Filegroup?

Using SQL Server Management Studio you can execute the following syntax:

ALTER DATABASE [x3v6] ADD FILEGROUP [TIER0]

Alternatively, you can right click on your database, choose properties and then click on the Filegroup page and add the new filegroup there:

How Do I Associate A File To The New Filegroup?

Once you’ve added the filegroup you must associate a file to it and choose a file type. You can do that using your TSQL skills like so:

USE [master]
GO
ALTER DATABASE [x3v6prm] 
ADD FILE 
( 
    NAME = N'TIER0', 
    FILENAME = N'E:\DATA\TIER0.ndf' , 
    SIZE = 3072KB , 
    FILEGROWTH = 1024KB 
) TO FILEGROUP [TIER0]

Alternatively, back in the database properties you click on the Files page and click add. Word to the wise: ensure your file type is set to “Rows Data” and that you’ve assigned it to the correct filegroup.

How Do I Alter My Sage ERP X3 Tables To Exist On Another Filegroup?

If you’ve ever been asked to move a table to another filegroup inside SQL Server you’ll remember that it depends on whether or not the table is a heap or not and there are a few steps to do it. Luckily inside Sage ERP X3 we’ve made it really easy. We’ll do the heavy SQL Server lifting for you. In order to tell Sage ERP X3 you’d like the table and all of its objects on a different filegroup perform the following steps:

1. Launch the table dictionary task (GESATB)

2. Bring up the table record, in this case we’ll use ALISTER

3. Navigate to the Index tab

4. In the Configuration File section add the following code

$MSSQL_ALISTER
{" On 'TIER0'"}
End

5. Force validate the table

Word to the wise, do not run a force validation on a table when the table is in use. The safest way to force validate the table is by placing the Sage ERP X3 system in Mono Mode (function = MONO, located at Development > Utilities > Folders > Mono mode) and ensuring that all users are out of the system. In addition, always have a backup of your system before making any changes in production. 

How Do I Check If The Table And Its Indexes Were Moved Successfully?

To check that you successfully moved over the table and all its objects you can run the following SQL statement inside SQL Server Management Studio:

SELECT s.name SchemaName, o.name ObjectName, i.type_desc, i.name IndexName, f.name FileGroupName, f.is_default, f.type_desc
FROM sys.all_objects o
    INNER JOIN sys.schemas s
        ON o.schema_id = s.schema_id
    INNER JOIN sys.indexes i
        ON o.object_id = i.object_id
    INNER JOIN sys.filegroups f
        ON i.data_space_id = f.data_space_id
WHERE 
    s.name  = 'NATRAINV6' AND o.name = 'ALISTER'
ORDER BY i.name

Keep in mind, you’ll need to change the Schema name to be the name of your folder. Your output would look something like this:

So What Is Tiered Storage And How Does It Relate To Filegroups?

Tiered storage is a way to organize your data storage needs by requirements, and it could be useful for some organizations that aren’t able to refresh their entire storage subsystem hardware, but can make small changes existing hardware by adding some tier 0 storage. Used in combination with filegroups one can relocate rapidly changing tables (volatile tables) onto a tier 0 array. Even if you can refresh your entire storage platform many companies still tend to make trade-offs between speed, durability and capacity, and thus are implementing tiered storage natively. Dell wrote an article about tiered storage titled Dell’s Enterprise Hard Drives, in which Dell gives the following tier classifications: 

Tier 0: Enterprise-class Solid State Disk Drives

• Advantage: Speed
• Disadvantage: Storage Capacity

Tier 1: SAS 10k or 15K Drives

• Advantage: An excellent balance between speed and storage capacity, and a high Mean Time Between Failure (MTBF)
• Disadvantage: Not going to keep up with the speed of a Solid State Disk

Tier 2: Enterprise-Class SATA Drives

• Advantage: Great cost per gigabyte
• Disadvantage: Speed is the slowest amongst all drive choices

For ERP applications, customers have typically chosen one tier of storage for all their needs, and it’s typically a TIER 1. It’s the most traditional way to solve your data storage requirements. The decision usually comes down to something like the graphic below. Note, Tier 2 storage is not typically a database administrator’s tier of choice for SQL Server implementations so I’ve left it out of the picture below. Some people argue it can be used for backups, and others argue that it would slow down a restore, so they don’t typically tend to use it.

So when would you want to leverage multiple tiers of storage? And is this useful for Sage ERP X3 specifically, if so how?

When Would You Use Tiered Storage?

How you organize your storage tiers is something your organization will want to consider carefully before you implement and even spend some time testing it with your enterprise applications before releasing it into production. In no way am I saying tiered storage is right for everyone. Tiered storage is something that does require some additional skills at the database administration and hardware levels, so there is a trade-off. I am saying that you can see some pretty significant benefits for your company if you leverage it in certain types of situations. 

For example, inside SQL Server the top two situations that come to mind immediately: tempDB and some types of tables. One table example is ALISTER. There are other tables that experience a large number of CRUD operations, but ALISTER doesn’t contain data transactional data, and it’s data can easily be repopulated. So, like the tempDB, ALISTER is a table that is not mission critical.

One potential way to organize it could be like the graphic shown below.

Wrap Up

Regardless of whether or not you use tiered storage, you can still isolate your volatile tables so that one table doesn’t dominate all the available disk time on your storage subsystem. You’d hate to have many people waiting because one guy wrote an multi-million record version 6 or below requester. Furthermore, knowing that tiered storage is an option for the cost conscious IT administrator, and knowing you can leverage it easily inside of Sage ERP X3 is a nice feature not often talked about. You don’t have to know all the TSQL required to process the move of the table to another storage location, Sage ERP X3 will do it for you with a simple instruction. Word to the wise, always test and have a backup plan before implementing any new change in your production server.