locked
Read_Only and Read_Write File Groups in Same Database RRS feed

  • Question

  • We have fairly static reference data in a database we have set to Read_Only for a number of reasons and it has worked well in that state. I am being asked to change that now so we can load data daily into this database. I am thinking about creating a read_write filegroup in the database to do this and still allow us to have the original tables in the database on a read_only filegroup. I am wondering what issues may occur with this approach, concerned about taking this highly read database to a read_write state and causing issues. It appears the primary data file can't be set to read only, so I would need to create the read_only filegroup and move all the existing data/tables to it that file group. Anyone have comments/experience along these lines?
    Thursday, June 12, 2014 5:09 PM

Answers

  • Hello,

    A Read_Only filegroup don't have any performance advantage, so from this point it doesn't matter if its read only or writable.

    The advantage of a readonly file group is that no one can change the data, even not a SysAdmin and so you have to backup this filegroup only rarely.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sofiya Li Friday, June 13, 2014 8:35 AM
    • Marked as answer by Sofiya Li Thursday, June 19, 2014 1:30 AM
    Thursday, June 12, 2014 5:18 PM
  • are advantanges of RO file groups/db's regarding locking.
    Sure; the file + data are read only, no one can write to it, so no locks can occur.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sofiya Li Friday, June 13, 2014 8:35 AM
    • Marked as answer by Sofiya Li Thursday, June 19, 2014 1:30 AM
    Thursday, June 12, 2014 6:42 PM

All replies

  • Hello,

    A Read_Only filegroup don't have any performance advantage, so from this point it doesn't matter if its read only or writable.

    The advantage of a readonly file group is that no one can change the data, even not a SysAdmin and so you have to backup this filegroup only rarely.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sofiya Li Friday, June 13, 2014 8:35 AM
    • Marked as answer by Sofiya Li Thursday, June 19, 2014 1:30 AM
    Thursday, June 12, 2014 5:18 PM
  • Thanks for the reply, I have read where there are advantanges of RO file groups/db's regarding locking. Can you expand on your comment "A Read_Only filegroup don't have any performance advantage" thanks
    Thursday, June 12, 2014 6:31 PM
  • are advantanges of RO file groups/db's regarding locking.
    Sure; the file + data are read only, no one can write to it, so no locks can occur.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sofiya Li Friday, June 13, 2014 8:35 AM
    • Marked as answer by Sofiya Li Thursday, June 19, 2014 1:30 AM
    Thursday, June 12, 2014 6:42 PM
  • When a Filegroup is marked as Read-only, SQL Server will not bother with Page or Row locks on the tables or indexes contained in them. This reduces SQL Server overhead and improves performance. Since the data is not changing, index fragmentation does not occur so maintenance, such as rebuilding or reorganizing, is unnecessary. That saves time and effort also. Also,  in SQL Server 2008 and later, you can mark a Filegroup as Read-only without having exclusive access to the entire database....

    Raju Rasagounder Sr MSSQL DBA

    Thursday, June 12, 2014 10:37 PM