Should Developers manage database read status

19 September,2019 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on Should Developers manage database read status


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer