Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Best practices for working with read only databases in SQL Server

MSSQLTips author Atif Shehzad By:   |   Read Comments (4)   |   Related Tips: More > DBA Best Practices
Problem

Databases whose architecture or data is not required to be updated should be considered to be set as READ ONLY databases. For one of my archival databases, I am looking to make it READ ONLY and would like to know what steps I should take.  In this tip I go over some best practices before setting a database to READ ONLY.

Solution

In this section we would go through best practices for preparing a database for READ ONLY status. Databases can be set to READ ONLY mode and back using T-SQL and SSMS.  For our testing we will use the sample database AdventureWorks to carry out all operations.

Preparing a database for READ ONLY state

Once a database is changed to READ ONLY nothing will change in your database.  So based on this, certain changes should be made to optimize the performance of a READ ONLY database.

Consider these facts:

  • Statistics will not be automatically updated (nor required) and you would not be able to update statistics of a READ ONLY database
  • READ ONLY databases will not shrink automatically or manually
  • You will not be able to create indexes
  • You will not be able to defragment indexes of a READ ONLY database
  • READ ONLY databases will not allow you to add any extended properties on any of its objects
  • Permissions may not be edited and users may not be added or removed from a READ ONLY database

So it is required to complete such tasks before we set the database to READ ONLY mode. The following script provides an outline to start with. You may add or remove certain steps in the script according to your specific requirements. The script assumes that your database is in the FULL recovery mode and also you have a prior full backup as a base for transactional log backups. If that is not the case then you may skip step 1 and 2 in the following script

-- Script 1: Prepare database for READ ONLY status
Use AdventureWorks
GO
-- Step 1. Assuming that DB has recovery model FULL and has a prior full backup. 
--Create transactional log backup
BACKUP LOG AdventureWorks TO disk = 'D:\AdventureWorksTLog'
GO
-- Step 2. Assuming that DB has recovery model FULL. Set Recovery model to Simple
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
GO
-- Step 3. Shrink the database
DBCC SHRINKDATABASE (AdventureWorks, TRUNCATEONLY)
GO
-- Step 4. Create/rebuild/reorganize indexes where required
-- Step 5. De-fragment indexes where required
-- Step 6. Add extended properties for database if required
EXEC [AdventureWorks].sys.sp_addextendedproperty @name=N'Purpose ', 
@value=N'DB made READ ONLY for testing purpose.' 
GO
-- Step 7. Modify permissions if required and add/remove users appropriately
-- Step 8. Update all statistics
EXEC sp_updatestats
GO

Set database to READ ONLY status

Setting your database to READ ONLY itself is quite simple, but demands prior considerations and preparation. After completing tasks in script 1, we are ready to change the status of AdventureWorks to READ ONLY. This task may be accomplished through the system stored procedure sp_dboption or through and ALTER DATABASE command.

Using ALTER DATABASE command is recommended as sp_dboption may be excluded from newer versions of SQL Server.

The following script would set AdventureWorks to READ ONLY state

-- Script 2: Set AdventureWorks to READ ONLY status
-- Set DB to READ ONLY status through ALTER DATABASE
ALTER DATABASE AdventureWorks SET READ_ONLY
GO

The same task may be performed through SSMS. Right click on the database and go to properties. Click on Options in left panel and scroll to 'state' related options at end. Here you can manage the READ ONLY property of the database.

Set database READ ONLY through SSMS

 


Verify the READ ONLY state of database

As a database changes to READ ONLY through SSMS, the color of the database folder in SSMS will instantly change and it would look as follows. If the database is changed to READ ONLY through T-SQL then a refresh of your databases may be required to show the color change in SSMS.

Changed Color of READ ONLY DB in SSMS

You can also verify the READ ONLY property of a database using this T-SQL,

-- Script 3: Verify that DB is READ ONLY or not
-- A value of 1 corresponds to READ ONLY state
SELECT name, is_read_only 
FROM sys.databases 
WHERE name = 'AdventureWorks'
GO

Working with backups of READ ONLY databases

There are couple of important points that should be kept in view while working with backups of READ ONLY databases.

  • You can create any type of backup (full, differential, log) of a database in READ only state. However considering the READ ONLY state you may want to have a different backup plan than that of a READ WRITE database. Consider using simple recovery mode along with only full backups.
  • A full backup of READ ONLY database would be recovered as a READ ONLY database. Recovered databases may be changed to READ WRITE mode later.
  • A full backup of READ WRITE database over a READ ONLY database would make the target database READ WRITE, so you would need to change the state of the database again.

Performance benefits of READ ONLY state

  • As there would be no data modifications in a READ ONLY state, SQL Server would not have to bother with locks.
  • You can create additional indexes to optimize data retrieval without worrying about degradation of data modifications and index maintenance.
  • You can copy data and log files of a READ ONLY database while the database is online

Change READ ONLY database to READ WRITE

If there is a need to change a READ ONLY database to READ WRITE this task can be performed both through T-SQL or SSMS.

-- Script 4: Change state of READ ONLY database to READ WRITE
-- Set DB to READ WRITE status through ALTER DATABASE
ALTER DATABASE AdventureWorks SET READ_WRITE
GO

SSMS may also be used for changing database state in the same way as mentioned in first image above. If task is performed through T-SQL then you will need to refresh the databases in SSMS to see the color  change of the database folder back to normal.

Next Steps
  • Do not forget to disable application features that previously were being used to update the database
  • Click here to read more about updating statistics in SQL Server databases or read this tip
  • Click here to read more about index maintenance operations
  • Click here to read more about shrinking database
  • Click here to read more about using extended properties on SQL Server objects
  • Click here to read more about working with recovery models for SQL Server databases or read this tip


Last Update: 1/14/2010


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Monday, October 22, 2012 - 5:58:48 PM - Seth Delconte Read The Tip

Thanks sir.


Sunday, January 13, 2013 - 6:38:58 AM - DBA Read The Tip

Hi Atif,

Generally we do not prefer to go fro DBCC SHRINKDATABASE option ,so would it make any difference to make the database to read only without shrinking the database?


Monday, March 04, 2013 - 6:55:08 PM - Mirza Read The Tip

Hi,

Under the section "Consider these facts", you are saying that the indexes cannot be created.

 

Under the section "performance benefits of READ ONLY state" you are talking about creating indexes.

 

I assume you mean creating of indexes before putting the database in read only mode.

 

Mirza


Monday, March 04, 2013 - 10:52:29 PM - Atif Shehzad Read The Tip

Yes ofcourse, we have to create the indexes before putting the Db in READ ONLY mode. As there will be no DML operations on db in read only mode statistics will provide good performance for read operations.

Thanks

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.