Javascript required
Skip to content Skip to sidebar Skip to footer
Home / Mysql Put Database in Read Only Mode

Mysql Put Database in Read Only Mode

By:   |   Updated: 2010-01-14   |   Comments (four)   |   Related: More > DBA Best Practices


Problem

Databases whose architecture or data is non required to be updated should exist considered to exist set as READ ONLY databases. For one of my archival databases, I am looking to make information technology 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 nosotros would go through all-time practices for preparing a database for READ Just condition. 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 bear out all operations.

Preparing a database for READ Only state

In one case a database is changed to READ Just nothing will alter in your database.  And then based on this, certain changes should be made to optimize the performance of a READ Only database.

Consider these facts:

  • Statistics will non be automatically updated (nor required) and yous would not be able to update statistics of a READ ONLY database
  • READ Simply databases volition not shrink automatically or manually
  • You lot will not be able to create indexes
  • Y'all will not exist able to defragment indexes of a READ Just database
  • READ ONLY databases volition non allow you lot to add any extended backdrop on any of its objects
  • Permissions may not be edited and users may not be added or removed from a READ Just database

So it is required to consummate such tasks before we ready the database to READ Just style. The following script provides an outline to beginning with. Y'all 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 example and then you may skip step 1 and ii in the post-obit script

-- Script ane: Prepare database for READ Just status Utilize AdventureWorks Become
-- Step 1. Assuming that DB has recovery model FULL and has a prior total backup.  --Create transactional log backup BACKUP LOG AdventureWorks TO deejay = 'D:\AdventureWorksTLog' GO
-- Step 2. Assuming that DB has recovery model FULL. Fix Recovery model to Simple ALTER DATABASE AdventureWorks Gear up RECOVERY SIMPLE GO
-- Pace three. Shrink the database DBCC SHRINKDATABASE (AdventureWorks, TRUNCATEONLY) GO
-- Step 4. Create/rebuild/reorganize indexes where required -- Step 5. De-fragment indexes where required          
-- Footstep 6. Add together extended properties for database if required EXEC [AdventureWorks].sys.sp_addextendedproperty @name=North'Purpose ',  @value=Due north'DB fabricated READ Simply for testing purpose.'  Go
-- Step vii. Modify permissions if required and add together/remove users accordingly
-- Pace 8. Update all statistics EXEC sp_updatestats Get

Set up database to READ ONLY status

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

Using Change DATABASE command is recommended every bit sp_dboption may be excluded from newer versions of SQL Server.

The following script would fix AdventureWorks to READ Merely state

-- Script 2: Set AdventureWorks to READ ONLY condition -- Gear up DB to READ Just condition through ALTER DATABASE Alter DATABASE AdventureWorks SET READ_ONLY Become

The same job may be performed through SSMS. Correct click on the database and go to properties. Click on Options in left panel and roll to 'state' related options at terminate. Here you can manage the READ Simply property of the database.

Set database READ ONLY through SSMS

Verify the READ Merely state of database

As a database changes to READ But through SSMS, the color of the database folder in SSMS will instantly alter and it would expect as follows. If the database is inverse to READ ONLY through T-SQL so a refresh of your databases may exist required to show the colour change in SSMS.

Changed Color of READ ONLY DB in SSMS

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

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

Working with backups of READ Simply databases

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

  • You can create whatsoever type of fill-in (full, differential, log) of a database in READ simply state. Nevertheless considering the READ Only state you may want to take a different backup plan than that of a READ WRITE database. Consider using simple recovery mode along with only total backups.
  • A full backup of READ ONLY database would be recovered every bit a READ ONLY database. Recovered databases may be inverse 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 land, SQL Server would not have to bother with locks.
  • Y'all tin can create boosted indexes to optimize data retrieval without worrying almost degradation of data modifications and index maintenance.
  • Y'all tin copy data and log files of a READ ONLY database while the database is online

Change READ Merely database to READ WRITE

If in that location is a need to change a READ Only database to READ WRITE this task can exist performed both through T-SQL or SSMS.

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

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

Next Steps
  • Do non 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 than about shrinking database
  • Click hither to read more about using extended properties on SQL Server objects
  • Click hither to read more about working with recovery models for SQL Server databases or read this tip

Related Articles

Pop Articles

About the writer

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

View all my tips

Article Last Updated: 2010-01-14

Mysql Put Database in Read Only Mode

Source: https://www.mssqltips.com/sqlservertip/1921/best-practices-for-working-with-read-only-databases-in-sql-server/