Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using a reporting database which for all practical purposes is a read only database. It is created data and dynamic reports are generated and viewed on this database. I was thinking of making this database as read-only.

Some of things I want to ask regarding read-only databases are

  1. Is only the data read only ? Can we still create indexes or views on this database ?
  2. What are the performace benefits of using a read-only database ? like, select queries using shared locks ..
  3. Is there any disadvantage of using a read-only database ?
  4. Any best practicse while using read-only reporting database.

Please let me know, if you need any more information.

Thanks.

share|improve this question

2 Answers 2

up vote 9 down vote accepted
  1. You cannot make schema changes when the database is read only but you could put all your user tables on a new file group and mark that as read only.
  2. You can expect a modest performance benefit from absence of locking.
  3. Can't think of any.
  4. Before making it read only you might as well remove all logical fragmentation and make page density as high as possible. Any non default FILL_FACTOR settings will not be of benefit in a read only environment.
share|improve this answer
    
1 and 4, great ideas. –  Mark Storey-Smith Dec 8 '11 at 13:15
  1. The entire database is read only so you cannot add indexes or views.
  2. No locking required so no locks are taken for any query other than a shared database lock. Obviously means that no blocking will occur either.
  3. Not as such no.
  4. None that I can suggest.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.