Partition Switching An Archival Strategy

Blogs

Why columnar databases are better for analytics
August 26, 2024
Choosing the Right Encryption Technology for Azure SQL Database or SQL Server
August 29, 2024

Partition Switching An Archival Strategy

In today’s world, where every company, big or small is highly dependent on the data they generate and accumulate, its very important to maintain and organize your data in the right manner. With organizations generating and accumulating vast amounts of data daily it becomes increasingly challenging to manage and maintain performance across critical systems. This is where data archival becomes not just a strategy, but a necessity.

Data archival is nothing but a strategy to efficiently the data life cycle within the organization where the historical and inactive data is moved to long term storage These archival tables are not frequently used and are used to maintain the backup of the data. Data archival is the unsung hero that keeps the wheels of modern enterprises turning smoothly.

So, let’s say, your company has decided to archive the historical data from the Database and maintain only the active and frequently used data within the system. There are multiple ways that data can archived and moved from active tables to archival tables. One of the strategies that we will cover today is partition switching. It’s considered to be one of the most efficient and effective method. Let’s understand why.

Partition switching is a metadata-only operation. This means that SQL Server does not physically move the data rows from one table or partition to another. Instead, it simply updates the metadata to indicate that the rows previously associated with one partition are now associated with another partition.

Let’s consider the following example.

You have 2 tables. One is the Transactional table and other is the Archival table. Now the transactional table consists of huge amount of data, and you want to move the old/inactive data to the archival table, it can be done as follows.

  • One needs to first create a partition on both the transactional table and the Archival table. The partition function of both the tables should be identical. If monthly partitions are created on the transactional table, then monthly partitions should be created on the archival table as well. It important to ensure that the partitions are same.
  • Assuming you want to move the previous month data from the transactional table and maintain only the current month of data, you will have to mention the partition number which contains that month’s data and move it to the corresponding partition number of the archival table.
  • Both the source and target tables must have the same schema, including data types and constraints.
  • Syntax for partition switch is:
    ALTER TABLE Transaction_table  SWITCH PARTITION 1 to Archival_table PARTITION 1.
  • The ALTER TABLE … SWITCH operation in SQL Server requires that the source and target partitions be perfectly aligned, meaning the partitions you are switching between must cover the exact same range of values.

For eg: The partition 1 of Transaction_table defines the range to be between ‘2024-08-01’ and ‘2024-08-31’ i.e. for the month of august then the range defined for the partition of the Archival_table should also be the same.

Take the following example: I have a table which has 3 partitions, and I want to switch the data present in the first partition to another table.

Fig1: Partitions in the main table with the number of rows in each partition

Fig2: Partitions in the archival table with 0 rows in each partition

Now, when I Run the syntax for partition switching, within no time, the data will move from your main table to archival table.

Fig3: Time taken for the partition switching

 As you can see the data moved from the table to another within milliseconds. I have considered a very small dataset, but with even large data, the operation does not take much time.

Now, if you see the data is no longer present in the main table, it has been moved to the archival table.

Fig4: Data in partition 1 of main table is now 0

Fig5: Data is moved to partition 1 of archival table.

Hence, partition switching is a simple, yet effective way to move your data, without affecting the performance of the database. The advantages and disadvantages of partition switching are as follows:

Advantages of partition switching: –

  • Very efficient and fast for moving large amounts of data.
  • It scales well and can handle multiple tables effectively.
  • Partition switching is a metadata operation hence minimal impact on the performance of the main table.
  • As it is a metadata operation it avoids the overhead of row-level operations.
  • It happens almost instantaneously regardless of the size of the data, minimizing lock contention and resource usage.
  • Minimizes the creation of t-logs, hence avoiding the large growth and filling up of the logs.
  • Also provides the functionality to change the partition function. One can merge and split the function values based on the requirement.

Disadvantages of partition switching: –

  • Both the source and target tables must have the same schema, including data types and constraints. If there’s a mismatch, the operation will fail.
  • The target partition must be empty before you can switch data into it. If it contains data, the operation will fail, requiring manual cleanup.
  • Both tables must be partitioned using the same partition function with matching boundary values. This can limit flexibility in how partitions are structured.
  • Managing partitions, especially in environments with frequent switching, can become complex. It requires careful planning to ensure that partition boundaries align and that the tables are correctly maintained.

Hope with this article, you now have a clear understanding of what partition switching is and how it can be used effectively for data archival. Hoping this acts a guide for you when you want to apply partition switching in your environment.

Until next time!!

 

 

 

 


Yatika Sheth

Leave a Reply

Your email address will not be published. Required fields are marked *