As per our business requirements, we may need to archive some historical data from large tables into a separate filegroup and make that filegroup read only. In this blog post, we will explore the options available for moving data in tables to a separate filegroup and discuss the steps for changing the filegroup option to read only.

When it comes to moving data between filegroups in SQL Server, we cannot directly move data files. However, we can achieve this by creating a clustered index on the new filegroup. The leaf level of a clustered index and the data pages are the same, so creating a clustered index on a different filegroup effectively moves the table to the new filegroup.

Let’s take a look at the steps involved in moving data to a separate filegroup:

1. Create a test database with multiple filegroups.
2. Create a table with a clustered index on the desired filegroup.
3. Verify the filegroup of the clustered index using the sp_helpindex system stored procedure.
4. Option 1 – Using SQL Server Management Studio (SSMS):
• Navigate to the table’s properties in SSMS.
• Select the Storage tab and choose the destination filegroup.
• Click OK to move the index to the new filegroup.
5. Option 2 – Using T-SQL scripts:
• Use the CREATE CLUSTERED INDEX statement to create the clustered index on the new filegroup.
• Specify the DROP_EXISTING=ON and Online=ON options to minimize user impact during index creation.
6. Verify the filegroup change using the sp_helpindex system stored procedure.

In addition to moving data between filegroups, it is important to consider unique or primary key constraints on the table. If the table has such constraints, the options outlined in Option 1 using SSMS may not be available. In this case, we can use a similar script as used in Option 2 to create the index on the new filegroup.

To determine which SQL Server objects reside in a particular filegroup, we can use a script that queries the sys.filegroups, sys.indexes, and sys.all_objects system views. This information can be useful for managing and organizing data across filegroups.

Finally, once the data has been moved to the desired filegroup, we can configure the filegroup as read only using the ALTER DATABASE statement. This ensures that the data remains unchanged and cannot be modified.

By following these steps, we can effectively move data to a separate filegroup and make it read only in SQL Server. This can be useful for archiving historical data or optimizing data storage and performance.

Remember to exercise caution when performing these operations on a production system and ensure that you have exclusive access to the database before making any changes.