Making Read-Only Versions of Your SQL and MySQL Databases

Just a little bit of pre-lock planning ensures that a SQL or MySQL database you convert to read-only status performs as expected and is accessible by the right group of users. Doing so also helps guarantee the database can be safely unlocked when and if it should ever need to be updated or otherwise altered.

There's something about setting a database to read-only that is comforting for DBAs. It's almost as if the database is all grown up and ready to be kicked out of the house, er, I mean, sent out to make its own way in the world.

Of course, there are as many reasons to set a database to read-only — temporarily or permanently — as there are databases. Here's a rundown on the ways to lock the content of a SQL or MySQL database while allowing users to access its contents.

As Atif Shehzad explains on the MSSQLTips site, before you lock the database, you have to optimize it to ensure it's running at peak performance. You can't update the statistics of a read-only database, for example, nor can you create or defragment indexes. Also, you can't add extended properties to the database's objects, edit its permissions, or add/remove users.

Shehzad provides an eight-step pre-lock script to run through prior to converting a database to read-only. The checklist covers everything from creating a transaction log backup to modifying permissions and updating statistics.

An eight-step pre-lock checklist ensures your database is optimized and backed up prior to being switched to read-only. Source: MSSQLTips.

Once the database is optimized and backed up, use either the ALTER DATABASE [database name] SET READ_ONLY command or the system stored procedure sp_dboption (the former is recommended because the stored procedure has been removed from recent versions of SQL Server). Alternatively, you can right-click the database in SSMS, choose Properties > Options, and set the Database Read-Only state to True. The database icon and name will change in SSMS to indicate its read-only state.

Converting a MySQL Database to Read-Only — and Back Again

A primary reason for setting a MySQL database as read-only is to ensure no updates are lost during a backup. The MySQL Documentation Library provides instructions for backing up master and slave servers in a replication setup via a global read lock and manipulation of the read_only system variable.

The instructions assume a replication setup with a master server (M1), slave server (S1), and clients (C1 connected to M1, and C2 connected to S1). The statements that put the master in a read-only state and that restore it to a normal operational state once the backup is complete are shown below. (Note that in some versions, "ON" becomes "1" and "OFF" becomes "0".)

The first statements switch the database to read-only, and the second revert it to its normal state after completing the backup. Source: MySQL Documentation Library.

In its read-only state, the database can be queried but not updated. An August 23, 2013 post on StackOverflow explains how to revoke and then reinstate DML privileges for specific users, which is less likely to affect the performance of the entire database.