My love for writing about ReadOnly databases have pushed me to explore more about this topic. Based on previous blogs, few readers did ask me that they heard about marking filegroups as ReadOnly and they wanted to know how this can be done. This blog post is in continuation to this journey where-in we will learn about the Readonly attribute.
TSQL Script to mark ReadOnly Filegroup
Let us create the database first.
CREATE DATABASE [ReadOnlyDB]
CONTAINMENT = NONE
( NAME = N'ReadOnlyDB', FILENAME = N'C:\Temp\ReadOnlyDB.mdf' , SIZE = 4024KB , FILEGROWTH = 1024KB )
( NAME = N'ReadOnlyDB_log', FILENAME = N'C:\Temp\ReadOnlyDB_log.ldf' , SIZE = 20480KB , FILEGROWTH = 10%)
Let us next create an Filegroup which will get marked as ReadOnly.
ALTER DATABASE ReadOnlyDB ADD FILEGROUP ReadOnlyDB_FG;
ALTER DATABASE ReadOnlyDB ADD FILE (
name = ReadOnlyDB_FG,
FILENAME = 'c:\temp\ReadOnlyDB_FG')
TO FILEGROUP ReadOnlyDB_FG
Next we would like to create a table on this specific filegroup. This will form the base for our experiment.
-- Creating our table
CREATE TABLE tbl_SQLAuth (id INT, Longname CHAR(8000))
Let us start our experiment in marking filegroups as ReadOnly. One of the important learning here is to understand, we cannot mark our PRIMARY filegroup as readonly.
-- Mark the PRIMARY filegroup read-only
ALTER DATABASE ReadOnlyDB MODIFY FILEGROUP [PRIMARY] READ_ONLY;
Msg 5047, Level 16, State 1, Line 29
Cannot change the READONLY property of the PRIMARY filegroup.
We will be presented with the above errors. Now, let us mark the other Filegroup next.
-- Mark the Other filegroup read-only
ALTER DATABASE ReadOnlyDB MODIFY FILEGROUP ReadOnlyDB_FG READ_ONLY;
The filegroup property ‘READ_ONLY’ has been set.
Once this succeeds, we cannot do anything with the filegroup. Let us do few tasks to check the errors.
-- Create a table on the Read_Only Filegroup
-- Explicitly call the FG where the table will be created.
CREATE TABLE tbl_SQLAuth_1 (id INT, Longname CHAR(8000))
Msg 1924, Level 16, State 2, Line 3
Filegroup ‘ReadOnlyDB_FG’ is read-only.
As you can see, we cannot create any tables on this Filegroup. If we try to insert any values into an table which is attached to this Filegroup, we will be presented with an error too.
INSERT INTO tbl_SQLAuth VALUES (1, 'SQLAuth');
Msg 652, Level 16, State 1, Line 4
The index “” for table “dbo.tbl_SQLAuth” (RowsetId 72057594040549376) resides on a read-only filegroup (“ReadOnlyDB_FG”), which cannot be modified.
As you can see, the Read_Only Filegroups are functioning just like our ReadOnly Databases which have been explained in our previous blogs. The best part of Read_Only filegroups is that, we get an flexibility to lock just a part of database files when compared to marking the whole database.
-- Clean up time
DROP DATABASE ReadOnlyDB
As I wrap up this blog, I would want to know how many of us out there have used Read_Only filegroups in our environments. What are those scenarios you made these changes? I would like to learn from your experience. Let me know via your comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
READONLY filegroups are great places to store tables which contain only static look-up data. Many systems have a set of static enumerations that come out of the box – They don’t ever change and hence they should be placed in their own separate filegroup.
The other scenario where READONLY filegroups are beneficial is when we want to archive historical data in an OLTP system and yet keep it accessible for reporting purposes. The very fact that it’s historical data means that it will never need to be updated and hence logically becomes an ideal candidate for READONLY filegroups.
Completely agree Nakul.
We had a DB kept growing in size but had a few static dimension tables. One view (transaction tables joined with those particular dimension tables and a few others) which was used daily took about 10 minutes to provide results to queries. I was asked to find a way to optimize this view so one of the things I did was to move tables to different file groups. All dim tables which were static were placed in the read only file group.
Fabrice – I also echo the scenario as described by Nakul. This is a classic usecase for using ReadOnly filegroups.
Regarding the archive scenario, I am assuming that to add more archived data then the process will need to set the file group back to read and write and set back to read? If this is the case then are there any performance gotchas with indexed tables?
If I make a filegroup Read_Only can I later on change the filegroup to Read_Write ?