19 September,2019 by Tom Collins
A programmer asked me if he can include the a function to change the database status out of READ_ONLY , add some data and then put it back into READ_ONLY.
These are the basic commands
ALTER DATABASE database-name SET READ_ONLY
ALTER DATABASE database-name SET READ_WRITE
There are some considerations for deciding if a Developer should be able to include as part of an ETL process , the capacity to change the READ STATE of a SQL database
1) Requires ALTER
permission on the database. This is an elevated privilege - and may break the organisations sql server security policy
2) Is the developer on the hook for maintaining Production data? What is the developer's role in supporting production data ?
3) How sensitive is the data ? If there is an outage caused by the ALTER DATABASE process , is the developer part of a core team trusted with fixing the issue?
4) Impact on performance - This is a common reason given for developers to not have access to production systems. In my experience - these tend to be isolated instances. I'm not saying it doesn't happen - but it depends on the details.
5) UPDATE STATISTICS before changing the database to READ_ONLY. If a developer is changing the database state between READ_ONLY & READ_WRITE , and are probably changing data , then will they include a statistics management process? A conscientious DBA should be aware of UPDATE STATISTICS . Once the database changes into READ_ONLY and the Database Engine thinks some more sql statistics are required than the statistics will be creates in the tempdb database. Creating the statistics in the tempdb may have a storage and performance impact
6) Exclusive access is required to change to Tempdb from READ_WRITE.
Read more on database read status
Make a database read only – READ_ONLY (SQL Server DBA)
How to check read only status for SQL database
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |