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.

Solution: In this article, we will discuss the best practices for preparing a database for READ ONLY status. We will use the sample database AdventureWorks to demonstrate the steps.

Preparing a database for READ ONLY state

Before setting a database to READ ONLY, there are certain changes that should be made to optimize its performance:

• 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

It is important to complete these tasks before setting the database to READ ONLY mode. The following script provides an outline to start with:

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

Setting the database to READ ONLY status

Setting your database to READ ONLY is quite simple, but it requires prior considerations and preparation. After completing the tasks in the script above, you are ready to change the status of AdventureWorks to READ ONLY. This can be accomplished through the system stored procedure sp_dboption or through an 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:

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

The same task may be performed through SQL Server Management Studio (SSMS) by right-clicking on the database, going to properties, clicking on Options in the left panel, and scrolling to the ‘state’ related options at the end. Here you can manage the READ ONLY property of the database.

Verifying the READ ONLY state of the database

As a database changes to READ ONLY, the color of the database folder in SSMS will instantly change. You can also verify the READ ONLY property of a database using the following T-SQL:

-- 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 a couple of important points to keep 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 a 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 a 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 also copy data and log files of a READ ONLY database while the database is online.

Changing a 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:

-- 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 the database state in the same way as mentioned above. If the task is performed through T-SQL, you will need to refresh the databases in SSMS to see the color change of the database folder back to normal.