A prudent measure of pre-lock planning ensures that a SQL or MySQL database, once converted to read-only status, functions as intended and remains accessible to the designated group of users. This foresight also guarantees the database can be safely unlocked when and if it requires updates or alterations in the future.
There’s a certain appeal to setting a database to read-only that resonates with DBAs. It’s as if the database has reached maturity and is ready to venture out into the world, unencumbered by the need for constant updates.
Of course, the reasons for setting a database to read-only — whether temporarily or permanently — are as diverse as the databases themselves. Here’s a comprehensive overview of the methods for locking the content of a SQL or MySQL database while allowing users to access its contents.
As Atif Shehzad astutely points out on the MSSQLTips site, prior to locking the database, it’s essential to fine-tune it to ensure peak performance. For instance, you cannot update statistics in a read-only database, nor can you create or defragment indexes. Additionally, you cannot add extended properties to the database’s objects, edit its permissions, or add/remove users.
Shehzad provides a meticulous eight-step pre-lock script to run through prior to converting a database to read-only. The checklist encompasses 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.
Transforming a MySQL Database into a Read-Only State — and Reversing the Process
A key motivation for configuring a MySQL database as read-only is to prevent data loss during the backup process. The MySQL Documentation Library provides comprehensive guidelines for backing up master and slave servers in a replication setup, utilizing a global read lock and manipulating the read_only system variable.
The replication setup guidelines envision a master server (M1), a slave server (S1), and clients (C1 connected to M1, and C2 connected to S1). The commands that facilitate the transition of the master to a read-only mode and restore it to normal operation upon completion of the backup are outlined below. (Note that in certain versions, “ENABLED” is synonymous with “1” and “DISABLED” is equivalent to “0”.)
The preliminary commands toggle the database to read-only, and the subsequent commands reinstate it to its normal state upon completing the backup. Source: MySQL Documentation Library.
In its read-only state, the database can be queried but not modified. A post dated August 23, 2013, on StackOverflow explains how to revoke and then reinstate DML privileges for specific users, a strategy less likely to impact the performance of the entire database.
Top comments (0)